06-SQL_avancé

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)