Home > Articles

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

10.11 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 WON, LOST
10.6
SELECT   P.TOWN, 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.TOWN, T.DIVISION
ORDER BY P.TOWN
10.7
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.8
SELECT   T.TEAMNO, DIVISION, SUM(WON)
FROM     TEAMS AS T, MATCHES AS M
WHERE    T.TEAMNO = M.TEAMNO
GROUP BY T.TEAMNO, DIVISION
10.9
SELECT   LENGTH(RTRIM(NAME)), COUNT(*)
FROM     PLAYERS
GROUP BY LENGTH(RTRIM(NAME))
10.10
SELECT   ABS(WON - LOST), COUNT(*)
FROM     MATCHES
GROUP BY ABS(WON - LOST)
10.11
SELECT   YEAR(BEGIN_DATE), MONTH(BEGIN_DATE), COUNT(*)
FROM     COMMITTEE_MEMBERS
GROUP BY YEAR(BEGIN_DATE), MONTH(BEGIN_DATE)
ORDER BY YEAR(BEGIN_DATE), MONTH(BEGIN_DATE)
10.12
  1. Although this column appears in the SELECT clause, the result of the DIVISION column has not been grouped.
  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, although the result has not been grouped; furthermore, the column does not appear as a parameter of an aggregation function.
10.13
  1. Superfluous
  2. Not superfluous
  3. 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
  • + Share This
  • 🔖 Save To Your Account