Home > Articles > Data > SQL

  • Print
  • + Share This
This chapter is from the book

10.13 Answers

10.1

SELECT   JOINED
FROM     PLAYERS
GROUP BY JOINED

10.2

SELECT   JOINED, COUNT(*)
FROM     PLAYERS
GROUP BY JOINED

10.3

SELECT   PLAYERNO, AVG(AMOUNT), COUNT(*)
FROM     PENALTIES
GROUP BY PLAYERNO

10.4

SELECT   TEAMNO, COUNT(*), SUM(WON)
FROM     MATCHES
WHERE    TEAMNO IN
         (SELECT  TEAMNO
         FROM     TEAMS
         WHERE    DIVISION = 'first')
GROUP BY TEAMNO

10.5

SELECT   WON, LOST, COUNT(*)
FROM     MATCHES
WHERE    WON > LOST
GROUP BY WON, LOST
ORDER BY 1, 2

10.6

SELECT   YEAR(BEGIN_DATE), MONTH(BEGIN_DATE), COUNT(*)
FROM     COMMITTEE_MEMBERS
GROUP BY YEAR(BEGIN_DATE), MONTH(BEGIN_DATE)
ORDER BY 1, 2

10.7

SELECT   P.NAME, T.DIVISION, SUM(WON)
FROM     (MATCHES AS M INNER JOIN PLAYERS AS P
         ON M.PLAYERNO = P.PLAYERNO)
         INNER JOIN TEAMS AS T
         ON M.TEAMNO = T.TEAMNO
GROUP BY P.NAME, T.DIVISION
ORDER BY 1

10.8

SELECT   NAME, INITIALS, COUNT(*)
FROM     PLAYERS AS P INNER JOIN PENALTIES AS PEN
         ON P.PLAYERNO = PEN.PLAYERNO
WHERE    P.TOWN = 'Inglewood'
GROUP BY P.PLAYERNO, NAME, INITIALS

10.9

SELECT   T.TEAMNO, DIVISION, SUM(WON)
FROM     TEAMS AS T, MATCHES AS M
WHERE    T.TEAMNO = M.TEAMNO
GROUP BY T.TEAMNO, DIVISION

10.10

SELECT   LENGTH(RTRIM(NAME)), COUNT(*)
FROM     PLAYERS
GROUP BY LENGTH(RTRIM(NAME))

10.11

SELECT   ABS(WON - LOST), COUNT(*)
FROM     MATCHES
GROUP BY ABS(WON - LOST)

10.12

  1. The result of the DIVISION column has not been grouped, while this column appears in the SELECT clause.
  2. The NAME column cannot appear like this in the SELECT clause because the result has not been grouped on the full NAME column.
  3. The PLAYERNO column appears in the SELECT clause, while the result has not been grouped; furthermore, the column does not appear as parameter of an aggregation function.

10.13

Superfluous.

Not superfluous.

Superfluous.

10.14

SELECT  AVG(NUMBERS)
FROM   (SELECT  COUNT(*) AS NUMBERS
        FROM     PLAYERS
        GROUP BY TOWN) AS TOWNS

10.15

SELECT   TEAMS.TEAMNO, DIVISION, NUMBER_PLAYERS
FROM     TEAMS LEFT OUTER JOIN
        (SELECT   TEAMNO, COUNT(*) AS NUMBER_PLAYERS
         FROM      MATCHES
         GROUP BY  TEAMNO) AS M
         ON (TEAMS.TEAMNO = M.TEAMNO)

10.16

SELECT   PLAYERS.PLAYERNO, NAME, SUM_AMOUNT,
         NUMBER_TEAMS
FROM    (PLAYERS LEFT OUTER JOIN
        (SELECT   PLAYERNO, SUM(AMOUNT) AS SUM_AMOUNT
         FROM      PENALTIES
         GROUP BY  PLAYERNO) AS TOTALS
        ON (PLAYERS.PLAYERNO = TOTALS.PLAYERNO))
           LEFT OUTER JOIN
          (SELECT  PLAYERNO, COUNT(*) AS NUMBER_TEAMS
           FROM     TEAMS
           WHERE    DIVISION = 'first'
           GROUP BY PLAYERNO) AS NUMBERS
           ON (PLAYERS.PLAYERNO = NUMBERS.PLAYERNO)

10.17

SELECT   TEAMNO, COUNT(DISTINCT PLAYERNO)
FROM     MATCHES
WHERE    TEAMNO IN
        (SELECT  TEAMNO
         FROM     PLAYERS AS P INNER JOIN TEAMS AS T
                  ON P.PLAYERNO = T.PLAYERNO
         AND      TOWN = 'Stratford')
AND      WON > LOST
GROUP BY TEAMNO

10.18

SELECT  PLAYERNO, NAME, JOINED - AVERAGE
FROM    PLAYERS,
       (SELECT  AVG(JOINED) AS AVERAGE
        FROM    PLAYERS) AS T

10.19

SELECT  PLAYERNO, NAME, JOINED - AVERAGE
FROM    PLAYERS,
       (SELECT  TOWN, AVG(JOINED) AS AVERAGE
        FROM     PLAYERS
        GROUP BY TOWN) AS TOWNS
WHERE   PLAYERS.TOWN = TOWNS.TOWN

10.20

SELECT   TEAMNO, COUNT(*)
FROM     MATCHES
GROUP BY TEAMNO WITH ROLLUP

10.21

SELECT   P.NAME, T.DIVISION, SUM(WON)
FROM    (MATCHES AS M INNER JOIN PLAYERS AS P
         ON M.PLAYERNO = P.PLAYERNO)
         INNER JOIN TEAMS AS T
         ON M.TEAMNO = T.TEAMNO
GROUP BY P.NAME, T.DIVISION WITH ROLLUP

10.22

The WITH ROLLUP specification calculates all levels of aggregation; at the bottom is a grouping based upon the expressions specified. The WITH CUBE specification returns much more data. For every possible combination of expressions specified, groupings are performed.

10.23

SELECT   ROW_NUMBER() OVER () AS SEQNO,
         TEAMNO,  PLAYERNO, WON, COUNT(*)
FROM     MATCHES
GROUP BY TEAMNO, PLAYERNO, WON WITH CUBE
ORDER BY 2, 3

10.24

SELECT   COUNT(*)
FROM     MATCHES
GROUP BY GROUPING SETS (())

10.25

SELECT   TEAMNO, PLAYERNO, COUNT(*)
FROM     MATCHES
GROUP BY GROUPING SETS ((TEAMNO, PLAYERNO), (TEAMNO), ())
ORDER BY 1, 2

10.26

  1. [][E1][E2]
  2. [E1][E2, E3][E3, E4, E5]
  3. [E1, E2][][E3]

10.27

SELECT   TEAMNO, PLAYERNO, COUNT(*)
FROM     MATCHES
WHERE    WON > LOST
GROUP BY ROLLUP (TEAMNO, PLAYERNO)
ORDER BY 1, 2

10.28

SELECT   P.TOWN, P.SEX, M.TEAMNO, COUNT(*)
FROM     MATCHES AS M INNER JOIN PLAYERS AS P
         ON M.PLAYERNO = P.PLAYERNO
GROUP BY CUBE (P.TOWN, P.SEX, M.TEAMNO)
ORDER BY 1, 2, 3
  • + Share This
  • 🔖 Save To Your Account