WCS quest // SQL avancé

gistfile1.txt

mysql> select t.name, count(*) as nb_student 
    -> from player p 
    -> join team t on t.id=p.team_id 
    -> group by t.name 
    -> order by nb_student desc;
+------------+------------+
| name       | nb_student |
+------------+------------+
| Gryffindor |         36 |
| Slytherin  |         21 |
| Ravenclaw  |         15 |
| Hufflepuff |         12 |
+------------+------------+
4 rows in set (0,00 sec)

mysql> select t.name 
    -> from team t 
    -> join player p on t.id=p.team_id 
    -> group by t.name 
    -> having count(p.id)>13 
    ->  order by count(p.id) desc;
+------------+
| name       |
+------------+
| Gryffindor |
| Slytherin  |
| Ravenclaw  |
+------------+
3 rows in set (0,00 sec)

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