SQL Avancé

index.txt

SELECT name, COUNT(name) as nb_joueur FROM player
INNER JOIN team ON player.team_id = team.id GROUP BY name ORDER BY nb_joueur DESC;
+------------+-----------+
| name       | nb_joueur |
+------------+-----------+
| Gryffindor |        36 |
| Slytherin  |        21 |
| Ravenclaw  |        15 |
| Hufflepuff |        12 |
+------------+-----------+

SELECT name FROM player
INNER JOIN team ON player.team_id = team.id GROUP BY name HAVING COUNT(name) > 14 ORDER BY name ASC;
+------------+
| name       |
+------------+
| Gryffindor |
| Ravenclaw  |
| Slytherin  |
+------------+

SELECT firstname, lastname, name, enrollment_date FROM player
INNER JOIN team ON player.team_id = team.id
INNER JOIN wizard ON player.wizard_id = wizard.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      |
+-----------+------------+------------+-----------------+