Wild SQL Quest 6

gistfile1.txt

mysql> SELECT team.name team, COUNT(*) AS nb_players                                                                                -> FROM player
    -> JOIN team ON player.team_id = team.id
    -> GROUP BY player.team_id
    -> ORDER BY nb_players DESC;
+------------+------------+
| team       | nb_players |
+------------+------------+
| Gryffindor |         36 |
| Slytherin  |         21 |
| Ravenclaw  |         15 |
| Hufflepuff |         12 |
+------------+------------+
4 rows in set (0.00 sec)

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

mysql> SELECT lastname, firstname
    -> FROM wizard
    -> JOIN player ON player.wizard_id = wizard.id
    -> JOIN team ON player.team_id = team.id
    -> WHERE team.name = "Gryffindor" AND WEEKDAY(player.enrollment_date) = 0
    -> ORDER BY player.enrollment_date;
+------------+-----------+
| lastname   | firstname |
+------------+-----------+
| Weasley    | George    |
| Longbottom | Alice     |
|            | Cadogan   |
| Gryffindor | Godric    |
| Black      | Sirius    |
| Dumbledore | Aberforth |
| Longbottom | Augusta   |
+------------+-----------+
7 rows in set (0.01 sec)