06 SQL Avancé

gistfile1.sql

SELECT t.name, COUNT(p.wizard_id) AS nb_player 
FROM player p 
LEFT JOIN team t ON t.id=p.team_id 
GROUP BY t.name 
ORDER BY nb_player DESC;


SELECT t.name, COUNT(p.wizard_id) AS nb_player 
FROM player p 
LEFT JOIN team t ON t.id=p.team_id 
GROUP BY t.name 
HAVING nb_player>=14 
ORDER BY nb_player DESC;

SELECT w.firstname, w.lastname, DATE_FORMAT(p.enrollment_date, "%a") AS day_date 
FROM player p 
LEFT JOIN wizard w ON w.id=p.wizard_id 
LEFT JOIN team t ON t.id=p.team_id 
WHERE t.name="Gryffindor" 
HAVING day_date="Mon";

results.txt

+------------+-----------+
| name       | nb_player |
+------------+-----------+
| Gryffindor |        36 |
| Slytherin  |        21 |
| Ravenclaw  |        15 |
| Hufflepuff |        12 |
+------------+-----------+

+------------+-----------+
| name       | nb_player |
+------------+-----------+
| Gryffindor |        36 |
| Slytherin  |        21 |
| Ravenclaw  |        15 |
+------------+-----------+

+-----------+------------+----------+
| firstname | lastname   | day_date |
+-----------+------------+----------+
| Sirius    | Black      | Mon      |
| Aberforth | Dumbledore | Mon      |
| Godric    | Gryffindor | Mon      |
| Alice     | Longbottom | Mon      |
| Augusta   | Longbottom | Mon      |
| George    | Weasley    | Mon      |
| Cadogan   |            | Mon      |
+-----------+------------+----------+