06 - SQL Avancé

challenge.txt

mysql> SELECT t.name,COUNT(*) as players FROM player p 
  JOIN team t 
  ON t.id=p.team_id 
  GROUP BY t.name;
+------------+---------+
| name       | players |
+------------+---------+
| Gryffindor |      36 |
| Hufflepuff |      12 |
| Ravenclaw  |      15 |
| Slytherin  |      21 |
+------------+---------+
4 rows in set (0.00 sec)


mysql> SELECT t.name,COUNT(*) as players 
  FROM player p 
  JOIN team t ON t.id=p.team_id 
  GROUP BY t.name 
  HAVING players>=14;
+------------+---------+
| name       | players |
+------------+---------+
| Gryffindor |      36 |
| Ravenclaw  |      15 |
| Slytherin  |      21 |
+------------+---------+
3 rows in set (0.00 sec)


mysql> SELECT firstname,lastname, name, enrollement_date FROM player p 
JOIN wizard w ON w.id = wizard_id 
JOIN team t ON t.id= team_id 
WHERE t.name = "Gryffindor" AND DAYNAME(enrollement_date) = "monday";
+-----------+------------+------------+------------------+
| firstname | lastname   | name       | enrollement_date |
+-----------+------------+------------+------------------+
| Sirius    | Black      | Gryffindor | 1994-01-10       |
| Aberforth | Dumbledore | Gryffindor | 1995-04-24       |
| Godric    | Gryffindor | Gryffindor | 1993-08-30       |
| Alice     | Longbottom | Gryffindor | 1992-02-17       |
| Augusta   | Longbottom | Gryffindor | 1999-10-25       |
| George    | Weasley    | Gryffindor | 1991-08-26       |
| Cadogan   |            | Gryffindor | 1993-01-04       |
+-----------+------------+------------+------------------+
7 rows in set (0.00 sec)