SQL

July 27, 2011, 10:28 a.m.
posted by novinick

Answers




Answers

8.1

SELECT   PAYMENTNO
FROM     PENALTIES
WHERE    AMOUNT > 60

or 

SELECT   PAYMENTNO
FROM     PENALTIES
WHERE    60 < AMOUNT

or 

SELECT   PAYMENTNO
FROM     PENALTIES
WHERE    AMOUNT - 60 > 0

8.2

SELECT   TEAMNO
FROM     TEAMS
WHERE    PLAYERNO <> 27

8.3

No row in the PLAYERS table satisfies the condition. No row in which the LEAGUENO column has a value satisfies the condition because the condition is false. In addition, each row in which the LEAGUENO column has no value (and thus contains the NULL value) is not returned.

8.4

SELECT   DISTINCT PLAYERNO
FROM     MATCHES
WHERE    WON > LOST

8.5

SELECT   DISTINCT PLAYERNO
FROM     MATCHES
WHERE    WON + LOST = 5

8.6

SELECT   PLAYERNO, NAME, INITIALS
FROM     PLAYERS
WHERE    PLAYERNO =
        (SELECT   PLAYERNO
         FROM     PENALTIES
         WHERE    PAYMENTNO = 4)

8.7

SELECT   PLAYERNO, NAME, INITIALS
FROM     PLAYERS
WHERE    PLAYERNO =
        (SELECT   PLAYERNO
         FROM     TEAMS
         WHERE    TEAMNO =
                 (SELECT   TEAMNO
                  FROM     MATCHES
                  WHERE    MATCHNO = 2))

8.8

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    BIRTH_DATE =
        (SELECT     BIRTH_DATE
         FROM       PLAYERS
         WHERE      NAME = 'Parmenter'
         AND        INITIALS = 'R')
AND      NOT (NAME = 'Parmenter'
              AND INITIALS = 'R')

8.9

SELECT   MATCHNO
FROM     MATCHES
WHERE    WON =
        (SELECT   WON
         FROM     MATCHES
         WHERE    MATCHNO = 6)
AND      MATCHNO <> 6
AND      TEAMNO = 2

8.10

SELECT   MATCHNO
FROM     MATCHES
WHERE   (WON, LOST) =
       ((SELECT   WON
         FROM     MATCHES
         WHERE    MATCHNO = 2),
        (SELECT   LOST
         FROM     MATCHES
         WHERE    MATCHNO = 8))

8.11

SELECT   PLAYERNO, TOWN, STREET, HOUSENO
FROM     PLAYERS
WHERE   (TOWN, STREET, HOUSENO) <
        (SELECT   TOWN, STREET, HOUSENO
         FROM     PLAYERS
         WHERE    PLAYERNO = 100)
ORDER BY TOWN, STREET, HOUSENO

8.12

SELECT   PAYMENTNO
FROM     PENALTIES
WHERE    1965 <
        (SELECT   YEAR(BIRTH_DATE)
         FROM     PLAYERS
         WHERE    PLAYERS.PLAYERNO = PENALTIES.PLAYERNO)

8.13

SELECT   PAYMENTNO, PLAYERNO
FROM     PENALTIES
WHERE    PLAYERNO =
        (SELECT   PLAYERNO
         FROM     TEAMS
         WHERE    TEAMS.PLAYERNO = PENALTIES.PLAYERNO)

8.14

SELECT   PLAYERNO, NAME, TOWN
FROM     PLAYERS
WHERE    SEX = 'F'
AND      TOWN <> 'Stratford'

or 

SELECT   PLAYERNO, NAME, TOWN
FROM     PLAYERS
WHERE    SEX = 'F'
AND      NOT (TOWN = 'Stratford')

8.15

SELECT   PLAYERNO
FROM     PLAYERS
WHERE    JOINED >= 1970
AND      JOINED <= 1980

or 

SELECT   PLAYERNO
FROM     PLAYERS
WHERE    NOT (JOINED < 1970 OR JOINED > 1980)

8.16

SELECT   PLAYERNO, NAME, BIRTH_DATE
FROM     PLAYERS
WHERE    MOD(YEAR(BIRTH_DATE), 400) = 0
OR      (MOD(YEAR(BIRTH_DATE), 4) = 0
         AND NOT(MOD(YEAR(BIRTH_DATE), 100) = 0))

8.17

SELECT   MATCHNO, NAME, INITIALS, DIVISION
FROM     MATCHES AS M, PLAYERS AS P, TEAMS AS T
WHERE    M.PLAYERNO = P.PLAYERNO
AND      M.TEAMNO = T.TEAMNO
AND      YEAR(BIRTH_DATE) > 1965
AND      WON > LOST

8.18

SELECT   PAYMENTNO
FROM     PENALTIES
WHERE    AMOUNT IN (50, 75, 100)

8.19

SELECT   PLAYERNO
FROM     PLAYERS
WHERE    TOWN NOT IN ('Stratford', 'Douglas')

or 

SELECT   PLAYERNO
FROM     PLAYERS
WHERE    NOT (TOWN IN ('Stratford', 'Douglas'))

or 

SELECT   PLAYERNO
FROM     PLAYERS
WHERE    TOWN <> 'Stratford'
AND      TOWN <> 'Douglas'

8.20

SELECT   PAYMENTNO
FROM     PENALTIES
WHERE    AMOUNT IN
        (100, PAYMENTNO * 5,
        (SELECT   AMOUNT
         FROM     PENALTIES
         WHERE    PAYMENTNO = 2))

8.21

SELECT   PLAYERNO, TOWN, STREET
FROM     PLAYERS
WHERE   (TOWN, STREET) IN
       (('Stratford','Haseltine Lane'),
        ('Stratford',' Edgecombe Way'))

8.22

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     PENALTIES)

8.23

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     PENALTIES
         WHERE    AMOUNT > 50)

8.24

SELECT   TEAMNO, PLAYERNO
FROM     TEAMS
WHERE    DIVISION = 'first'
AND      PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     PLAYERS
         WHERE    TOWN = 'Stratford')

8.25

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     PENALTIES)
AND      PLAYERNO NOT IN
        (SELECT   PLAYERNO
         FROM     TEAMS
         WHERE    DIVISION = 'first')

or 

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     PENALTIES
         WHERE    PLAYERNO NOT IN
                 (SELECT   PLAYERNO
                  FROM     TEAMS
                  WHERE    DIVISION = 'first'))

8.26

The result is empty.

8.27

SELECT   MATCHNO, PLAYERNO
FROM     MATCHES
WHERE   (WON, LOST) IN
        (SELECT   WON, LOST
         FROM     MATCHES
         WHERE    TEAMNO IN
                 (SELECT   TEAMNO
                  FROM     TEAMS
                  WHERE    DIVISION = 'second'))

8.28

SELECT   PLAYERNO, NAME
FROM     PLAYERS AS P1
WHERE   (TOWN, STREET, HOUSENO, POSTCODE) IN
        (SELECT   TOWN, STREET, HOUSENO, POSTCODE
         FROM     PLAYERS AS P2
         WHERE    P1.PLAYERNO <> P2.PLAYERNO)

8.29

SELECT   PAYMENTNO
FROM     PENALTIES
WHERE    AMOUNT BETWEEN 50 AND 100

8.30

SELECT   PAYMENTNO
FROM     PENALTIES
WHERE    NOT (AMOUNT BETWEEN 50 AND 100)

or 

SELECT   PAYMENTNO
FROM     PENALTIES
WHERE    AMOUNT NOT BETWEEN 50 AND 100

or 

SELECT   PAYMENTNO
FROM     PENALTIES
WHERE    AMOUNT < 50
OR       AMOUNT > 100

8.31

SELECT   PLAYERNO
FROM     PLAYERS
WHERE    JOINED BETWEEN
         YEAR(BIRTH_DATE + INTERVAL 16 YEAR + INTERVAL 1 DAY)
         AND YEAR(BIRTH_DATE + INTERVAL 40 YEAR -
                  INTERVAL 1 DAY)

8.32

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    NAME LIKE '%is%'

8.33

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    NAME LIKE '______'

8.34

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    NAME LIKE '______%'

or 

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    NAME LIKE '%______'

or 

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    NAME LIKE '%______%'

or 

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    LENGTH(RTRIM(NAME)) > 6

8.35

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    NAME LIKE '_r%r_'

8.36

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    TOWN LIKE '_@%%@%_' ESCAPE '@'

8.37

SELECT   PLAYERNO
FROM     PLAYERS
WHERE    LEAGUENO IS NULL

8.38

The NAME column has been defined as NOT NULL. Therefore, the column will never contain a NULL value, which is why the condition is false for each row.

8.39

SELECT   NAME, INITIALS
FROM     PLAYERS
WHERE    EXISTS
        (SELECT   *
         FROM     TEAMS
         WHERE    PLAYERNO = PLAYERS.PLAYERNO)

8.40

SELECT   NAME, INITIALS
FROM     PLAYERS AS P
WHERE    NOT EXISTS
        (SELECT   *
         FROM     TEAMS AS T
         WHERE    T.PLAYERNO = P.PLAYERNO
         AND      EXISTS
                 (SELECT   *
                  FROM     MATCHES AS M
                  WHERE    M.TEAMNO = T.TEAMNO
                   AND     M.PLAYERNO = 112))

8.41

SELECT   PLAYERNO
FROM     PLAYERS
WHERE    BIRTH_DATE <= ALL
        (SELECT   BIRTH_DATE
         FROM     PLAYERS
         WHERE    TOWN = 'Stratford')
AND      TOWN = 'Stratford'

8.42

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    PLAYERNO = ANY
        (SELECT   PLAYERNO
         FROM     PENALTIES)

8.43

SELECT   PAYMENTNO, AMOUNT, PAYMENT_DATE
FROM     PENALTIES AS PEN1
WHERE    AMOUNT >= ALL
        (SELECT   AMOUNT
         FROM     PENALTIES AS PEN2
         WHERE    YEAR(PEN1.PAYMENT_DATE) =
                  YEAR(PEN2.PAYMENT_DATE))

8.44

SELECT  (SELECT   PLAYERNO
         FROM     PLAYERS
         WHERE    PLAYERNO <= ALL
                 (SELECT   PLAYERNO
                  FROM     PLAYERS)),
        (SELECT   PLAYERNO
         FROM     PLAYERS
         WHERE    PLAYERNO >= ALL
                 (SELECT   PLAYERNO
                  FROM     PLAYERS))

8.45

  1. C1: S1, S2, S3, S4, S5

  2. C1: S2, S3, S4

  3. C1: S3

  4. C1: S4

  5. C1: S5

8.46

SELECT   NAME, INITIALS
FROM     PLAYERS
WHERE    PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     MATCHES
         WHERE    TEAMNO IN
                 (SELECT   TEAMNO
                  FROM     TEAMS
                  WHERE    DIVISION = 'first'))
AND      PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     MATCHES
         WHERE    WON > LOST)
AND      PLAYERNO NOT IN
        (SELECT   PLAYERNO
         FROM     PENALTIES)

8.47

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     MATCHES
         WHERE    TEAMNO = 1)
AND      PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     MATCHES
         WHERE    TEAMNO = 2)

8.48

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    EXISTS
        (SELECT   *
         FROM     PENALTIES
         WHERE    PLAYERNO = PLAYERS.PLAYERNO)

8.49

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    PLAYERNO IN
        (SELECT   PLAYERNO
         FROM     MATCHES AS M1
         WHERE    WON > LOST
         AND      EXISTS
                 (SELECT   *
                  FROM     MATCHES AS M2
                  WHERE    M1.PLAYERNO = M2.PLAYERNO
                  AND      WON > LOST
                  AND      M1.MATCHNO <> M2.MATCHNO))

or

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    1 < (SELECT   COUNT(*)
              FROM     MATCHES
              WHERE    WON > LOST
              AND      PLAYERS.PLAYERNO = PLAYERNO)

8.50

SELECT   P.PLAYERNO, P.NAME
FROM     PLAYERS AS P, MATCHES AS M1
WHERE    P.PLAYERNO = M1.PLAYERNO
GROUP BY P.PLAYERNO, P.NAME
HAVING   SUM(WON) >
        (SELECT   SUM(LOST)
         FROM     MATCHES AS M2
         WHERE    M2.PLAYERNO = P.PLAYERNO
         GROUP BY M2.PLAYERNO)

8.51

SELECT   NAME, INITIALS
FROM     PLAYERS
WHERE    NOT EXISTS
        (SELECT   *
         FROM     PENALTIES
         WHERE    PLAYERS.PLAYERNO = PLAYERNO
         AND      PAYMENT_DATE BETWEEN '1980-01-01'
                  AND '1980-12-31')

8.52

SELECT   DISTINCT PLAYERNO
FROM     PENALTIES AS PEN1
WHERE    EXISTS
        (SELECT   *
         FROM     PENALTIES AS PEN2
         WHERE    PEN1.AMOUNT = PEN2.AMOUNT
         AND      PEN1.PAYMENTNO <> PEN2.PAYMENTNO)

8.53

SELECT   PLAYERNO
FROM     PLAYERS
WHERE    PLAYERNO NOT IN
        (SELECT   PLAYERNO
         FROM     MATCHES WHERE WON = 3)

8.54

SELECT   TEAMNO, DIVISION
FROM     TEAMS
WHERE    TEAMNO NOT IN
        (SELECT   TEAMNO
         FROM     MATCHES
         WHERE    PLAYERNO = 6)

8.55

SELECT   DISTINCT PLAYERNO
FROM     MATCHES
WHERE    PLAYERNO NOT IN
        (SELECT   PLAYERNO
         FROM     MATCHES
         WHERE    TEAMNO IN
                 (SELECT   TEAMNO
                  FROM     MATCHES
                  WHERE    PLAYERNO = 57))


 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows