Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ## Retourne les noms, prénoms, rôle et équipe de tous les joueurs, classés dans l’ordre alphabétique par équipe, puis par rôle dans l’équipe, puis par nom de famille, puis par prénom.
- -> SELECT w.firstname, w.lastname, p.role, t.name
- -> FROM wizard w
- -> INNER JOIN player p ON w.id = p.wizard_id
- -> INNER JOIN team t ON t.id = p.team_id
- -> ORDER BY t.name, p.role, w.lastname, w.firstname;
- +-------------+-----------------+--------+------------+
- | firstname | lastname | role | name |
- +-------------+-----------------+--------+------------+
- | Sirius | Black | beater | Gryffindor |
- | Lavender | Brown | beater | Gryffindor |
- | Seamus | Finnigan | beater | Gryffindor |
- | Rubeus | Hagrid | beater | Gryffindor |
- | Alice | Longbottom | beater | Gryffindor |
- | Minerva | McGonagall | beater | Gryffindor |
- | Harry | Potter | beater | Gryffindor |
- | James | Potter | beater | Gryffindor |
- | Dean | Thomas | beater | Gryffindor |
- | Arthur | Weasley | beater | Gryffindor |
- | Percy | Weasley | beater | Gryffindor |
- | Katie | Bell | chaser | Gryffindor |
- | Albus | Dumbledore | chaser | Gryffindor |
- | Hermione | Granger | chaser | Gryffindor |
- | Lily | J. | chaser | Gryffindor |
- | Lee | Jordan | chaser | Gryffindor |
- | Augusta | Longbottom | chaser | Gryffindor |
- | Frank | Longbottom | chaser | Gryffindor |
- | Neville | Longbottom | chaser | Gryffindor |
- | Peter | Pettigrew | chaser | Gryffindor |
- | Alicia | Spinnet | chaser | Gryffindor |
- | George | Weasley | chaser | Gryffindor |
- | Oliver | Wood | chaser | Gryffindor |
- | Cadogan | | keeper | Gryffindor |
- | Dennis | Creevey | keeper | Gryffindor |
- | Nicholas | de | keeper | Gryffindor |
- | Aberforth | Dumbledore | keeper | Gryffindor |
- | Angelina | Johnson | keeper | Gryffindor |
- | Ginevra | Weasley | keeper | Gryffindor |
- | Cuthbert | Binns | seeker | Gryffindor |
- | Colin | Creevey | seeker | Gryffindor |
- | Godric | Gryffindor | seeker | Gryffindor |
- | Romilda | Vane | seeker | Gryffindor |
- | Fred | Weasley | seeker | Gryffindor |
- | Ronald | Weasley | seeker | Gryffindor |
- | William | Weasley | seeker | Gryffindor |
- | Hannah | Abbott | beater | Hufflepuff |
- | Justin | Finch-Fletchley | beater | Hufflepuff |
- | Fat | Friar | beater | Hufflepuff |
- | Hepzibah | Smith | beater | Hufflepuff |
- | Pomona | Sprout | beater | Hufflepuff |
- | Nymphadora | Tonks | beater | Hufflepuff |
- | Amelia | Bones | chaser | Hufflepuff |
- | Newton | Scamander | chaser | Hufflepuff |
- | Susan | Bones | keeper | Hufflepuff |
- | Cedric | Diggory | keeper | Hufflepuff |
- | Zacharias | Smith | keeper | Hufflepuff |
- | Helga | Hufflepuff | seeker | Hufflepuff |
- | Penelope | Clearwater | beater | Ravenclaw |
- | Quirinus | Quirrell | beater | Ravenclaw |
- | Helena | Ravenclaw | beater | Ravenclaw |
- | Myrtle | Warren | beater | Ravenclaw |
- | Cho | Chang | chaser | 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 |
- | Michael | Corner | seeker | Ravenclaw |
- | Rowena | Ravenclaw | seeker | Ravenclaw |
- | Sybill | Trelawney | seeker | Ravenclaw |
- | Millicent | Bulstrode | beater | Slytherin |
- | Vincent | Crabbe | beater | Slytherin |
- | Marcus | Flint | beater | Slytherin |
- | Pansy | Parkinson | beater | Slytherin |
- | Severus | Snape | beater | Slytherin |
- | Blaise | Zabini | beater | Slytherin |
- | Bloody | Baron | chaser | Slytherin |
- | Bellatrix | Lestrange | chaser | Slytherin |
- | Rodolphus | Lestrange | chaser | Slytherin |
- | Draco | Malfoy | chaser | Slytherin |
- | Lucius | Malfoy | chaser | Slytherin |
- | Phineas | Nigellus | chaser | Slytherin |
- | Theodore | Nott | chaser | Slytherin |
- | Tom | Riddle | chaser | Slytherin |
- | Andromeda | Tonks | chaser | Slytherin |
- | Regulus | Black | keeper | Slytherin |
- | Gregory | Goyle | seeker | Slytherin |
- | Rabastan | Lestrange | seeker | Slytherin |
- | Narcissa | Malfoy | seeker | Slytherin |
- | Salazar | Slytherin | seeker | Slytherin |
- | Dolores | Umbridge | seeker | Slytherin |
- +-------------+-----------------+--------+------------+
- 84 rows in set (0.00 sec)
- ## Retourne uniquement les prénom et nom des joueurs ayant le rôle de seeker (attrapeur), classés par ordre alphabétique de nom puis prénom
- -> SELECT w.firstname, w.lastname
- -> FROM wizard w
- -> INNER JOIN player p ON w.id = p.wizard_id
- -> WHERE p.role = 'seeker'
- -> ORDER BY w.lastname, w.firstname;
- +-----------+------------+
- | firstname | lastname |
- +-----------+------------+
- | Cuthbert | Binns |
- | Michael | Corner |
- | Colin | Creevey |
- | Gregory | Goyle |
- | Godric | Gryffindor |
- | Helga | Hufflepuff |
- | Rabastan | Lestrange |
- | Narcissa | Malfoy |
- | Rowena | Ravenclaw |
- | Salazar | Slytherin |
- | Sybill | Trelawney |
- | Dolores | Umbridge |
- | Romilda | Vane |
- | Fred | Weasley |
- | Ronald | Weasley |
- | William | Weasley |
- +-----------+------------+
- 16 rows in set (0.00 sec)
- ## Retourne la liste de tous les sorciers qui ne pratiquent pas le quidditch.
- -> SELECT w.firstname, w.lastname
- -> FROM wizard w
- -> LEFT JOIN player p ON w.id = p.wizard_id
- -> WHERE p.wizard_id IS NULL;
- +-----------+----------+
- | firstname | lastname |
- +-----------+----------+
- | Terry | Boot |
- | Crabbe | |
- | Remus | Lupin |
- | Padma | Patil |
- | Parvati | Patil |
- | Demelza | Robins |
- | Horace | Slughorn |
- | Charles | Weasley |
- | Molly | Weasley |
- +-----------+----------+
- 9 rows in set (0.00 sec)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement