SQL advanced wild !

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

mysql>

mysql> select t.name, COUNT(p.team_id) AS nb_players FROM player p JOIN team t ON t.id=p.team_id GROUP BY team_id HAVING nb_players > 14 ORDER BY t.name;
+------------+------------+
| name       | nb_players |
+------------+------------+
| Gryffindor |         36 |
| Ravenclaw  |         15 |
| Slytherin  |         21 |
+------------+------------+
3 rows in set (0.00 sec)

mysql>


mysql> select w.lastname, w.firstname FROM player p JOIN wizard w ON w.id=p.wizard_id WHERE DAYOFWEEK(enrollment_date) = 2 GROUP BY wizard_id;
+------------+-------------+
| lastname   | firstname   |
+------------+-------------+
| Abbott     | Hannah      |
| Black      | Sirius      |
| Chang      | Cho         |
| Dumbledore | Aberforth   |
| Flint      | Marcus      |
| Gryffindor | Godric      |
| Longbottom | Alice       |
| Longbottom | Augusta     |
| Lovegood   | Xenophilius |
| Nott       | Theodore    |
| Weasley    | George      |
| Zabini     | Blaise      |
|            | Cadogan     |
+------------+-------------+
13 rows in set (0.00 sec)

mysql> select w.lastname, w.firstname FROM player p JOIN wizard w ON w.id=p.wizard_id JOIN team t ON t.id=p.team_id WHERE DAYOFWEEK(enrollment_date) = 2 AND t.name='Gryffindor' GROUP BY p.id ORDER BY p.enrollment_date;
+------------+-----------+
| lastname   | firstname |
+------------+-----------+
| Weasley    | George    |
| Longbottom | Alice     |
|            | Cadogan   |
| Gryffindor | Godric    |
| Black      | Sirius    |
| Dumbledore | Aberforth |
| Longbottom | Augusta   |
+------------+-----------+
7 rows in set (0.00 sec)

mysql>