requête 1

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

requête 2

mysql> SELECT t.name, COUNT(*) AS nb_player
    -> FROM player p
    -> JOIN team t ON t.id=p.team_id
    -> GROUP BY team_id 
    -> HAVING nb_player > 13;
+------------+-----------+
| name       | nb_player |
+------------+-----------+
| Gryffindor |        36 |
| Ravenclaw  |        15 |
| Slytherin  |        21 |
+------------+-----------+
3 rows in set (0.01 sec)

requête 3

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