SQL AVANCE

sql avance

mysql> SELECT name, count(player.wizard_id) AS nb_player FROM team JOIN player ON player.team_id = team.id GROUP BY team.name ORDER BY nb_player DESC;
+------------+-----------+
| name       | nb_player |
+------------+-----------+
| Gryffindor |        36 |
| Slytherin  |        21 |
| Ravenclaw  |        15 |
| Hufflepuff |        12 |
+------------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT name FROM player JOIN team ON team.id = player.team_id GROUP BY team.name HAVING count(player.wizard_id) > 14 ORDER BY name ASC;
+------------+
| name       |
+------------+
| Gryffindor |
| Ravenclaw  |
| Slytherin  |
+------------+
3 rows in set (0.01 sec)

mysql> SELECT firstname, lastname, DAYOFWEEK(enrollment_date) FROM player JOIN wizard ON wizard.id = player.wizard_id JOIN team ON team.id=player.team_id WHERE
team.name = 'Gryffindor' AND DAYOFWEEK(enrollment_date) = 2;
+-----------+------------+----------------------------+
| firstname | lastname   | DAYOFWEEK(enrollment_date) |
+-----------+------------+----------------------------+
| Sirius    | Black      |                          2 |
| Aberforth | Dumbledore |                          2 |
| Godric    | Gryffindor |                          2 |
| Alice     | Longbottom |                          2 |
| Augusta   | Longbottom |                          2 |
| George    | Weasley    |                          2 |
| Cadogan   |            |                          2 |
+-----------+------------+----------------------------+
7 rows in set (0.01 sec)