gistfile1.txt

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

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

mysql> SELECT CONCAT(w.firstname, ' ', w.lastname) AS monday_players 
FROM player p 
JOIN wizard w ON w.id = p.wizard_id 
WHERE DAYOFWEEK(enrollment_date) = 2 AND team_id = 1 
ORDER BY enrollment_date ASC;
+----------------------+
| monday_players       |
+----------------------+
| George Weasley       |
| Alice Longbottom     |
| Cadogan              |
| Godric Gryffindor    |
| Sirius Black         |
| Aberforth Dumbledore |
| Augusta Longbottom   |
+----------------------+
7 rows in set (0,00 sec)