Skip to content

Instantly share code, notes, and snippets.

@Rooarii

Rooarii/gist:fe3ed408ced355aa94e7274468db94d2

Created December 5, 2020 21:03
Show Gist options
  • Save Rooarii/fe3ed408ced355aa94e7274468db94d2 to your computer and use it in GitHub Desktop.
Save Rooarii/fe3ed408ced355aa94e7274468db94d2 to your computer and use it in GitHub Desktop.
Download ZIP
06-SQL_avancé
Raw
gistfile1.txt
QUERY 1
mysql> SELECT name, COUNT(*) AS player_number FROM wizard w JOIN player p ON p.wizard_id=w.id JOIN team t ON p.team_id=t.id GROUP BY name ORDER BY player_number DESC;
+------------+---------------+
| name | player_number |
+------------+---------------+
| Gryffindor | 36 |
| Slytherin | 21 |
| Ravenclaw | 15 |
| Hufflepuff | 12 |
+------------+---------------+
4 rows in set (0.00 sec)
QUERY 2
mysql> SELECT name, COUNT(*) AS player_number FROM wizard w JOIN player p ON p.wizard_id=w.id JOIN team t ON p.team_id=t.id GROUP BY name HAVING player_number >= 14 ORDER BY name ASC;
+------------+---------------+
| name | player_number |
+------------+---------------+
| Gryffindor | 36 |
| Ravenclaw | 15 |
| Slytherin | 21 |
+------------+---------------+
3 rows in set (0.00 sec)
QUERY 3
mysql> SELECT name, firstname, lastname, enrollment_date, DAYNAME(enrollment_date) AS enrollment_day FROM wizard w JOIN player p ON p.wizard_id=w.id JOIN team t ON p.team_id=t.id WHERE name="Gryffindor" AN
D DAYNAME(enrollm
+------------+-----------+------------+-----------------+----------------+
| name | firstname | lastname | enrollment_date | enrollment_day |
+------------+-----------+------------+-----------------+----------------+
| Gryffindor | George | Weasley | 1991-08-26 | Monday |
| Gryffindor | Alice | Longbottom | 1992-02-17 | Monday |
| Gryffindor | Cadogan | | 1993-01-04 | Monday |
| Gryffindor | Godric | Gryffindor | 1993-08-30 | Monday |
| Gryffindor | Sirius | Black | 1994-01-10 | Monday |
| Gryffindor | Aberforth | Dumbledore | 1995-04-24 | Monday |
| Gryffindor | Augusta | Longbottom | 1999-10-25 | Monday |
+------------+-----------+------------+-----------------+----------------+
7 rows in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment