05 - les jointures

gistfile1.sql

select firstname, lastname
from wizard
LEFT JOIN player ON player.wizard_id=wizard.id
where player.role is null;

'Terry', 'Boot'
'Crabbe', ''
'Remus', 'Lupin'
'Padma', 'Patil'
'Parvati', 'Patil'
'Demelza', 'Robins'
'Horace', 'Slughorn'
'Charles', 'Weasley'
'Molly', 'Weasley'

sql.sql

select firstname, lastname, role, name
from (wizard
JOIN player ON (player.wizard_id=wizard.id)
LEFT JOIN team ON (player.team_id=team.id))
ORDER BY 
name ASC,
lastname ASC,
firstname ASC;

'Cadogan', '', 'keeper', 'Gryffindor'
'Katie', 'Bell', 'chaser', 'Gryffindor'
'Cuthbert', 'Binns', 'seeker', 'Gryffindor'
'Sirius', 'Black', 'beater', 'Gryffindor'
'Lavender', 'Brown', 'beater', 'Gryffindor'
'Colin', 'Creevey', 'seeker', 'Gryffindor'
'Dennis', 'Creevey', 'keeper', 'Gryffindor'
'Nicholas', 'de', 'keeper', 'Gryffindor'
'Aberforth', 'Dumbledore', 'keeper', 'Gryffindor'
'Albus', 'Dumbledore', 'chaser', 'Gryffindor'
'Seamus', 'Finnigan', 'beater', 'Gryffindor'
'Hermione', 'Granger', 'chaser', 'Gryffindor'
'Godric', 'Gryffindor', 'seeker', 'Gryffindor'
'Rubeus', 'Hagrid', 'beater', 'Gryffindor'
'Lily', 'J.', 'chaser', 'Gryffindor'
'Angelina', 'Johnson', 'keeper', 'Gryffindor'
'Lee', 'Jordan', 'chaser', 'Gryffindor'
'Alice', 'Longbottom', 'beater', 'Gryffindor'
'Augusta', 'Longbottom', 'chaser', 'Gryffindor'
'Frank', 'Longbottom', 'chaser', 'Gryffindor'
'Neville', 'Longbottom', 'chaser', 'Gryffindor'
'Minerva', 'McGonagall', 'beater', 'Gryffindor'
'Peter', 'Pettigrew', 'chaser', 'Gryffindor'
'Harry', 'Potter', 'beater', 'Gryffindor'
'James', 'Potter', 'beater', 'Gryffindor'
'Alicia', 'Spinnet', 'chaser', 'Gryffindor'
'Dean', 'Thomas', 'beater', 'Gryffindor'
'Romilda', 'Vane', 'seeker', 'Gryffindor'
'Arthur', 'Weasley', 'beater', 'Gryffindor'
'Fred', 'Weasley', 'seeker', 'Gryffindor'
'George', 'Weasley', 'chaser', 'Gryffindor'
'Ginevra', 'Weasley', 'keeper', 'Gryffindor'
'Percy', 'Weasley', 'beater', 'Gryffindor'
'Ronald', 'Weasley', 'seeker', 'Gryffindor'
'William', 'Weasley', 'seeker', 'Gryffindor'
'Oliver', 'Wood', 'chaser', 'Gryffindor'
'Hannah', 'Abbott', 'beater', 'Hufflepuff'
'Amelia', 'Bones', 'chaser', 'Hufflepuff'
'Susan', 'Bones', 'keeper', 'Hufflepuff'
'Cedric', 'Diggory', 'keeper', 'Hufflepuff'
'Justin', 'Finch-Fletchley', 'beater', 'Hufflepuff'
'Fat', 'Friar', 'beater', 'Hufflepuff'
'Helga', 'Hufflepuff', 'seeker', 'Hufflepuff'
'Newton', 'Scamander', 'chaser', 'Hufflepuff'
'Hepzibah', 'Smith', 'beater', 'Hufflepuff'
'Zacharias', 'Smith', 'keeper', 'Hufflepuff'
'Pomona', 'Sprout', 'beater', 'Hufflepuff'
'Nymphadora', 'Tonks', 'beater', 'Hufflepuff'
'Cho', 'Chang', 'chaser', 'Ravenclaw'
'Penelope', 'Clearwater', 'beater', 'Ravenclaw'
'Michael', 'Corner', 'seeker', 'Ravenclaw'
'Marietta', 'Edgecombe', 'chaser', 'Ravenclaw'
'Filius', 'Flitwick', 'chaser', 'Ravenclaw'
'Anthony', 'Goldstein', 'chaser', 'Ravenclaw'
'Gilderoy', 'Lockhart', 'chaser', 'Ravenclaw'
'Luna', 'Lovegood', 'chaser', 'Ravenclaw'
'Xenophilius', 'Lovegood', 'chaser', 'Ravenclaw'
'Garrick', 'Ollivander', 'chaser', 'Ravenclaw'
'Quirinus', 'Quirrell', 'beater', 'Ravenclaw'
'Helena', 'Ravenclaw', 'beater', 'Ravenclaw'
'Rowena', 'Ravenclaw', 'seeker', 'Ravenclaw'
'Sybill', 'Trelawney', 'seeker', 'Ravenclaw'
'Myrtle', 'Warren', 'beater', 'Ravenclaw'
'Bloody', 'Baron', 'chaser', 'Slytherin'
'Regulus', 'Black', 'keeper', 'Slytherin'
'Millicent', 'Bulstrode', 'beater', 'Slytherin'
'Vincent', 'Crabbe', 'beater', 'Slytherin'
'Marcus', 'Flint', 'beater', 'Slytherin'
'Gregory', 'Goyle', 'seeker', 'Slytherin'
'Bellatrix', 'Lestrange', 'chaser', 'Slytherin'
'Rabastan', 'Lestrange', 'seeker', 'Slytherin'
'Rodolphus', 'Lestrange', 'chaser', 'Slytherin'
'Draco', 'Malfoy', 'chaser', 'Slytherin'
'Lucius', 'Malfoy', 'chaser', 'Slytherin'
'Narcissa', 'Malfoy', 'seeker', 'Slytherin'
'Phineas', 'Nigellus', 'chaser', 'Slytherin'
'Theodore', 'Nott', 'chaser', 'Slytherin'
'Pansy', 'Parkinson', 'beater', 'Slytherin'
'Tom', 'Riddle', 'chaser', 'Slytherin'
'Salazar', 'Slytherin', 'seeker', 'Slytherin'
'Severus', 'Snape', 'beater', 'Slytherin'
'Andromeda', 'Tonks', 'chaser', 'Slytherin'
'Dolores', 'Umbridge', 'seeker', 'Slytherin'
'Blaise', 'Zabini', 'beater', 'Slytherin'

sql2.sql

select firstname, lastname, role, name
from (wizard
JOIN player ON (player.wizard_id=wizard.id)
LEFT JOIN team ON (player.team_id=team.id))
WHERE role = 'seeker'
ORDER BY 
name ASC,
lastname ASC,
firstname ASC;

'Fred', 'Weasley', 'seeker', 'Gryffindor'
'Ronald', 'Weasley', 'seeker', 'Gryffindor'
'William', 'Weasley', 'seeker', 'Gryffindor'
'Romilda', 'Vane', 'seeker', 'Gryffindor'
'Dolores', 'Umbridge', 'seeker', 'Slytherin'
'Sybill', 'Trelawney', 'seeker', 'Ravenclaw'
'Salazar', 'Slytherin', 'seeker', 'Slytherin'
'Rowena', 'Ravenclaw', 'seeker', 'Ravenclaw'
'Narcissa', 'Malfoy', 'seeker', 'Slytherin'
'Rabastan', 'Lestrange', 'seeker', 'Slytherin'
'Helga', 'Hufflepuff', 'seeker', 'Hufflepuff'
'Godric', 'Gryffindor', 'seeker', 'Gryffindor'
'Gregory', 'Goyle', 'seeker', 'Slytherin'
'Colin', 'Creevey', 'seeker', 'Gryffindor'
'Michael', 'Corner', 'seeker', 'Ravenclaw'
'Cuthbert', 'Binns', 'seeker', 'Gryffindor'