06 - SQL Advanced - Wild Code School

request.sql

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

-----------------------------------------------------------------------------------------------------------------------------


mysql> SELECT firstname, lastname 
    -> FROM wizard 
    -> JOIN player ON player.wizard_id = wizard.id 
    -> JOIN team ON player.team_id = team.id 
    -> WHERE team.id = 1 
    -> AND DAYOFWEEK(player.enrollment_date) = 2
    -> ORDER BY player.enrollment_date ASC;
    
+-----------+------------+
| firstname | lastname   |
+-----------+------------+
| George    | Weasley    |
| Alice     | Longbottom |
| Cadogan   |            |
| Godric    | Gryffindor |
| Sirius    | Black      |
| Aberforth | Dumbledore |
| Augusta   | Longbottom |
+-----------+------------+

7 rows in set (0.00 sec)