database 6

quidditch2.sql

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

SELECT t.name AS team FROM team t JOIN player p ON p.team_id = t.id GROUP BY 1 HAVING COUNT(p.id) > 14 ORDER BY 1 ;
+------------+
| team       |
+------------+
| Gryffindor |
| Ravenclaw  |
| Slytherin  |
+------------+
3 rows in set (0.00 sec)

SELECT DATE_FORMAT(p.enrollment_date,'%a') FROM player

SELECT w.lastname, w.firstname FROM player p 
JOIN team t ON p.team_id = t.id 
JOIN wizard w ON p.wizard_id = w.id 
WHERE t.name = "Gryffindor" 
GROUP BY w.firstname, w.lastname, p.enrollment_date 
HAVING DATE_FORMAT(p.enrollment_date,'%a') = "Mon" 
ORDER BY p.enrollment_date;
+------------+-----------+
| lastname   | firstname |
+------------+-----------+
| Weasley    | George    |
| Longbottom | Alice     |
|            | Cadogan   |
| Gryffindor | Godric    |
| Black      | Sirius    |
| Dumbledore | Aberforth |
| Longbottom | Augusta   |
+------------+-----------+
7 rows in set (0.00 sec)