mysql> USE wild;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_wild |
+----------------+
| player         |
| team           |
| wizard         |
+----------------+
3 rows in set (0.00 sec)

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

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

mysql> SELECT firstname, lastname
    -> FROM player
    -> JOIN team
    -> ON team.id=player.team_id
    -> JOIN wizard
    -> ON wizard.id=player.wizard_id
    -> WHERE name="Gryffindor"
    -> AND DAYOFWEEK(enrollment_date)=2;
+-----------+------------+
| firstname | lastname   |
+-----------+------------+
| Sirius    | Black      |
| Aberforth | Dumbledore |
| Godric    | Gryffindor |
| Alice     | Longbottom |
| Augusta   | Longbottom |
| George    | Weasley    |
| Cadogan   |            |
+-----------+------------+
7 rows in set (0.00 sec)

mysql>