Created
December 3, 2019 13:31
-
-
Save 51enra/a83b9f88bdf888cd56158241f8324c1f to your computer and use it in GitHub Desktop.
Wild SQL Quest 6
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> SELECT team.name team, COUNT(*) AS nb_players -> FROM player | |
-> JOIN team ON player.team_id = team.id | |
-> GROUP BY player.team_id | |
-> ORDER BY nb_players DESC; | |
+------------+------------+ | |
| team | nb_players | | |
+------------+------------+ | |
| Gryffindor | 36 | | |
| Slytherin | 21 | | |
| Ravenclaw | 15 | | |
| Hufflepuff | 12 | | |
+------------+------------+ | |
4 rows in set (0.00 sec) | |
mysql> SELECT team.name team | |
-> FROM player | |
-> JOIN team ON player.team_id = team.id | |
-> GROUP BY player.team_id | |
-> HAVING COUNT(*) > 13 | |
-> ORDER BY team.name; | |
+------------+ | |
| team | | |
+------------+ | |
| Gryffindor | | |
| Ravenclaw | | |
| Slytherin | | |
+------------+ | |
3 rows in set (0.00 sec) | |
mysql> SELECT lastname, firstname | |
-> FROM wizard | |
-> JOIN player ON player.wizard_id = wizard.id | |
-> JOIN team ON player.team_id = team.id | |
-> WHERE team.name = "Gryffindor" AND WEEKDAY(player.enrollment_date) = 0 | |
-> ORDER BY player.enrollment_date; | |
+------------+-----------+ | |
| lastname | firstname | | |
+------------+-----------+ | |
| Weasley | George | | |
| Longbottom | Alice | | |
| | Cadogan | | |
| Gryffindor | Godric | | |
| Black | Sirius | | |
| Dumbledore | Aberforth | | |
| Longbottom | Augusta | | |
+------------+-----------+ | |
7 rows in set (0.01 sec) | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment