Potterquid

gistfile1.txt

mysql> SELECT t.name, count(*) AS nb_student FROM team t JOIN player p ON t.id = p.team_id  GROUP BY 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 name HAVING count(*) >= 14 ORDER BY name;
+------------+
| name       |
+------------+
| Gryffindor |
| Ravenclaw  |
| Slytherin  |
+------------+
3 rows in set (0.00 sec)

mysql> select w.lastname, w.firstname from wizard w
    -> join player p
    -> on w.id = p.wizard_id
    -> join team t
    -> on t.id = p.team_id
    -> where t.name ="Gryffindor" and dayofweek(p.enrollment_date) = 2                                                                                                  
    -> order by p.enrollment_date;       
+------------+-----------+
| lastname   | firstname |
+------------+-----------+
| Weasley    | George    |
| Longbottom | Alice     |
|            | Cadogan   |
| Gryffindor | Godric    |
| Black      | Sirius    |
| Dumbledore | Aberforth |
| Longbottom | Augusta   |
+------------+-----------+
7 rows in set (0.00 sec)