Cacher is the code snippet organizer for pro developers

1.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.

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



2.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


mysql> select lastname, firstname, role from wizard join player on wizard_id=wizard.id where player.role="seeker" order by lastname;
+------------+-----------+--------+
| lastname   | firstname | role   |
+------------+-----------+--------+
| Binns      | Cuthbert  | seeker |
| Corner     | Michael   | seeker |
| Creevey    | Colin     | seeker |
| Goyle      | Gregory   | seeker |
| Gryffindor | Godric    | seeker |
| Hufflepuff | Helga     | seeker |
| Lestrange  | Rabastan  | seeker |
| Malfoy     | Narcissa  | seeker |
| Ravenclaw  | Rowena    | seeker |
| Slytherin  | Salazar   | seeker |
| Trelawney  | Sybill    | seeker |
| Umbridge   | Dolores   | seeker |
| Vane       | Romilda   | seeker |
| Weasley    | William   | seeker |
| Weasley    | Ronald    | seeker |
| Weasley    | Fred      | seeker |
+------------+-----------+--------+
16 rows in set (0,00 sec)


3. Retourne la liste de tous les sorciers qui ne pratiquent pas le quidditch.

mysql> select * from wizard where id not in (select wizard_id from player);
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  9 | Terry     | Boot     |
| 15 | Crabbe    |          |
| 45 | Remus     | Lupin    |
| 53 | Padma     | Patil    |
| 54 | Parvati   | Patil    |
| 63 | Demelza   | Robins   |
| 65 | Horace    | Slughorn |
| 80 | Charles   | Weasley  |
| 84 | Molly     | Weasley  |
+----+-----------+----------+
9 rows in set (0,01 sec)