sql 06 quête mehdi avancé

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

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

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