gistfile1.sql

SELECT team.name, COUNT(*) as nb_player FROM team INNER JOIN player ON player.team_id=team.id GROUP BY team.name ORDER BY nb_player DESC;
+------------+-----------+
| name       | nb_player |
+------------+-----------+
| Gryffindor |        36 |
| Slytherin  |        21 |
| Ravenclaw  |        15 |
| Hufflepuff |        12 |
+------------+-----------+

SELECT team.name, COUNT(*) as nb_player FROM team INNER JOIN player ON player.team_id=team.id GROUP BY team.name HAVING nb_player > 13;
+------------+-----------+
| name       | nb_player |
+------------+-----------+
| Gryffindor |        36 |
| Ravenclaw  |        15 |
| Slytherin  |        21 |
+------------+-----------+

SELECT CONCAT(firstname, ' ', lastname) AS fullname, enrollment_date FROM wizard
    -> INNER JOIN player ON player.wizard_id=wizard.id
    -> INNER JOIN team ON team.id=player.team_id
    -> WHERE team.name = "Gryffindor"
    -> HAVING DAYOFWEEK(player.enrollment_date) = 2
    -> ORDER BY enrollment_date;
+----------------------+-----------------+
| fullname             | enrollment_date |
+----------------------+-----------------+
| George Weasley       | 1991-08-26      |
| Alice Longbottom     | 1992-02-17      |
| Cadogan              | 1993-01-04      |
| Godric Gryffindor    | 1993-08-30      |
| Sirius Black         | 1994-01-10      |
| Aberforth Dumbledore | 1995-04-24      |
| Augusta Longbottom   | 1999-10-25      |
+----------------------+-----------------+