WCS SQL quest 6

mysql> use wild_db_quest_2
Database changed
mysql> SELECT name AS teamname, COUNT(name) as num_players
    -> FROM wizard
    -> JOIN player ON player.wizard_id = wizard.id
    -> JOIN team ON player.team_id = team.id
    -> GROUP BY name
    -> ORDER BY num_players DESC;
+------------+-------------+
| teamname   | num_players |
+------------+-------------+
| Gryffindor |          36 |
| Slytherin  |          21 |
| Ravenclaw  |          15 |
| Hufflepuff |          12 |
+------------+-------------+
4 rows in set (0.00 sec)

mysql> SELECT name AS teamname
    -> FROM wizard
    -> JOIN player ON player.wizard_id = wizard.id
    -> JOIN team ON player.team_id = team.id
    -> GROUP BY name
    -> HAVING COUNT(name) >= 14
    -> ORDER BY name;
+------------+
| teamname   |
+------------+
| Gryffindor |
| Ravenclaw  |
| Slytherin  |
+------------+
3 rows in set (0.00 sec)

mysql> SELECT lastname, firstname, role,  name AS teamname, enrollment_date
    -> FROM wizard
    -> JOIN player ON player.wizard_id = wizard.id
    -> JOIN team ON player.team_id = team.id
    -> WHERE name = "Gryffindor" AND DAYOFWEEK(enrollment_date) = 2
    -> ORDER BY enrollment_date;
+------------+-----------+--------+------------+-----------------+
| lastname   | firstname | role   | teamname   | enrollment_date |
+------------+-----------+--------+------------+-----------------+
| Weasley    | George    | chaser | Gryffindor | 1991-08-26      |
| Longbottom | Alice     | beater | Gryffindor | 1992-02-17      |
|            | Cadogan   | keeper | Gryffindor | 1993-01-04      |
| Gryffindor | Godric    | seeker | Gryffindor | 1993-08-30      |
| Black      | Sirius    | beater | Gryffindor | 1994-01-10      |
| Dumbledore | Aberforth | keeper | Gryffindor | 1995-04-24      |
| Longbottom | Augusta   | chaser | Gryffindor | 1999-10-25      |
+------------+-----------+--------+------------+-----------------+
7 rows in set (0.00 sec)

mysql>