quete sql avancé

fichier.sql

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



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



SELECT CONCAT(lastname, " ", firstname) AS lundi_player FROM wizard
JOIN player ON wizard.id=player.wizard_id
JOIN team ON team.id=player.team_id
WHERE dayofweek(enrollment_date)=2 AND team.name="Gryffindor"
ORDER BY player.enrollment_date;