requête 5

gistfile1.txt

mysql> select * from wizard w left join player p on p.wizard_id = w.id order by  role is null desc limit 9;
+----+-----------+----------+------+-----------+---------+------+-----------------+
| id | firstname | lastname | id   | wizard_id | team_id | role | enrollment_date |
+----+-----------+----------+------+-----------+---------+------+-----------------+
| 65 | Horace    | Slughorn | NULL |      NULL |    NULL | NULL | NULL            |
| 80 | Charles   | Weasley  | NULL |      NULL |    NULL | NULL | NULL            |
|  9 | Terry     | Boot     | NULL |      NULL |    NULL | NULL | NULL            |
| 84 | Molly     | Weasley  | NULL |      NULL |    NULL | NULL | NULL            |
| 15 | Crabbe    |          | NULL |      NULL |    NULL | NULL | NULL            |
| 45 | Remus     | Lupin    | NULL |      NULL |    NULL | NULL | NULL            |
| 53 | Padma     | Patil    | NULL |      NULL |    NULL | NULL | NULL            |
| 54 | Parvati   | Patil    | NULL |      NULL |    NULL | NULL | NULL            |
| 63 | Demelza   | Robins   | NULL |      NULL |    NULL | NULL | NULL            |
+----+-----------+----------+------+-----------+---------+------+-----------------+

mysql> select * from wizard w left join player p on p.wizard_id = w.id order by role desc limit 10;;
+----+-----------+------------+------+-----------+---------+--------+-----------------+
| id | firstname | lastname   | id   | wizard_id | team_id | role   | enrollment_date |
+----+-----------+------------+------+-----------+---------+--------+-----------------+
| 30 | Godric    | Gryffindor |   30 |        30 |       1 | seeker | 1993-08-30      |
| 75 | Sybill    | Trelawney  |   75 |        75 |       2 | seeker | 1991-12-28      |
| 14 | Michael   | Corner     |   14 |        14 |       2 | seeker | 1995-08-17      |
| 48 | Narcissa  | Malfoy     |   48 |        48 |       3 | seeker | 1993-03-17      |
| 76 | Dolores   | Umbridge   |   76 |        76 |       3 | seeker | 1993-10-23      |
| 86 | Ronald    | Weasley    |   86 |        86 |       1 | seeker | 1997-06-22      |
| 32 | Helga     | Hufflepuff |   32 |        32 |       4 | seeker | 1992-08-14      |
| 77 | Romilda   | Vane       |   77 |        77 |       1 | seeker | 1990-07-31      |
| 17 | Colin     | Creevey    |   17 |        17 |       1 | seeker | 1993-07-07      |
| 61 | Rowena    | Ravenclaw  |   61 |        61 |       2 | seeker | 1992-10-09      |
+----+-----------+------------+------+-----------+---------+--------+-----------------+

mysql> select w.firstname, w.lastname, p.role, t.name from player p join wizard w on w.id=p.wizard_id join team t on t.id=p.team_id order by name, role, lastname, 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  |