mySqlAdvanced.sql

SELECT team.name, COUNT(wizard.id) AS total FROM player
JOIN wizard ON player.wizard_id = wizard.id
JOIN team ON player.team_id=team.id
GROUP BY team.name
ORDER BY COUNT(wizard.id) DESC;


SELECT team.name FROM player
JOIN team ON player.team_id=team.id
GROUP BY team.name
HAVING COUNT(player.wizard_id)>=14;


SELECT wizard.firstname FROM player
JOIN wizard ON player.wizard_id=wizard.id
JOIN team ON player.team_id=team.id
WHERE DATE_FORMAT(player.enrollment_date, "%a")="Mon"
AND team.name="Gryffindor"
ORDER BY player.enrollment_date ASC;