06 SQL AVANCE

index.sql

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

SELECT t.name FROM player p JOIN team t ON t.id=p.team_id GROUP BY team_id HAVING COUNT(*)>=14 ORDER BY t.name;
+------------+
| name       |
+------------+
| Gryffindor |
| Ravenclaw  |
| Slytherin  |
+------------+
3 rows in set (0,00 sec)

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