sql advanced

mysql> select name, count(player.id) as members_numbers from team join player on player.team_id=team.id group by team.id order by members_numbers desc;
+------------+-----------------+
| name       | members_numbers |
+------------+-----------------+
| Gryffindor |              36 |
| Slytherin  |              21 |
| Ravenclaw  |              15 |
| Hufflepuff |              12 |
+------------+-----------------+
4 rows in set (0.00 sec)


mysql> select name, count(player.id) as members_numbers from team join player on player.team_id=team.id group by team.id having members_numbers > 14 order by name desc;
+------------+-----------------+
| name       | members_numbers |
+------------+-----------------+
| Slytherin  |              21 |
| Ravenclaw  |              15 |
| Gryffindor |              36 |
+------------+-----------------+
3 rows in set (0.01 sec)

mysql> select concat(lastname, ' ', firstname) as fullname, enrollment_date from player join team on player.team_id=team.id join wizard on wizard.id=player.wizard_id where name="Gryffindor" and weekday(player.enrollm
+----------------------+-----------------+
| fullname             | enrollment_date |
+----------------------+-----------------+
| Weasley George       | 1991-08-26      |
| Longbottom Alice     | 1992-02-17      |
|  Cadogan             | 1993-01-04      |
| Gryffindor Godric    | 1993-08-30      |
| Black Sirius         | 1994-01-10      |
| Dumbledore Aberforth | 1995-04-24      |
| Longbottom Augusta   | 1999-10-25      |
+----------------------+-----------------+
7 rows in set (0.00 sec)