06 - SQL Avancé

1.sql

select team.name, COUNT(*) AS nb_wizard
from (wizard
JOIN player ON (player.wizard_id=wizard.id)
JOIN team ON (player.team_id=team.id))
GROUP BY team.id
ORDER BY nb_wizard DESC;


'Gryffindor', '36'
'Slytherin', '21'
'Ravenclaw', '15'
'Hufflepuff', '12'

2.sql

select team.name, COUNT(*) AS nb_wizard
from (wizard
JOIN player ON (player.wizard_id=wizard.id)
JOIN team ON (player.team_id=team.id))
GROUP BY team.id
HAVING nb_wizard >= 14
ORDER BY name ASC;

'Gryffindor', '36'
'Ravenclaw', '15'
'Slytherin', '21'

3.sql

select firstname, lastname, name Team_Name
from (wizard
JOIN player ON (player.wizard_id=wizard.id)
JOIN team ON (player.team_id=team.id))
WHERE DAYOFWEEK(enrollment_date) = 2
AND name = 'Gryffindor';

'Sirius', 'Black', 'Gryffindor'
'Aberforth', 'Dumbledore', 'Gryffindor'
'Godric', 'Gryffindor', 'Gryffindor'
'Alice', 'Longbottom', 'Gryffindor'
'Augusta', 'Longbottom', 'Gryffindor'
'George', 'Weasley', 'Gryffindor'
'Cadogan', '', 'Gryffindor'