SQL 6 Jointures

gistfile1.txt

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

mysql> SELECT w.lastname, w.firstname
    -> FROM wild_db_quest.player p
    -> JOIN wild_db_quest.wizard w
    -> ON w.id = p.wizard_id
    -> WHERE p.role = 'seeker'
    -> ORDER BY lastname ASC, firstname ASC;
+------------+-----------+
| lastname   | firstname |
+------------+-----------+
| Binns      | Cuthbert  |
| Corner     | Michael   |
| Creevey    | Colin     |
| Goyle      | Gregory   |
| Gryffindor | Godric    |
| Hufflepuff | Helga     |
| Lestrange  | Rabastan  |
| Malfoy     | Narcissa  |
| Ravenclaw  | Rowena    |
| Slytherin  | Salazar   |
| Trelawney  | Sybill    |
| Umbridge   | Dolores   |
| Vane       | Romilda   |
| Weasley    | Fred      |
| Weasley    | Ronald    |
| Weasley    | William   |
+------------+-----------+
16 rows in set (0,17 sec)

mysql> SELECT *
    -> FROM player AS p
    -> RIGHT JOIN wizard AS w
    -> ON p.wizard_id = w.id
    -> WHERE enrollment_date IS NULL;
+------+-----------+---------+------+-----------------+----+-----------+----------+
| id   | wizard_id | team_id | role | enrollment_date | id | firstname | lastname |
+------+-----------+---------+------+-----------------+----+-----------+----------+
| NULL |      NULL |    NULL | NULL | NULL            |  9 | Terry     | Boot     |
| 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            | 65 | Horace    | Slughorn |
| NULL |      NULL |    NULL | NULL | NULL            | 80 | Charles   | Weasley  |
| NULL |      NULL |    NULL | NULL | NULL            | 84 | Molly     | Weasley  |
+------+-----------+---------+------+-----------------+----+-----------+----------+
9 rows in set (0,00 sec)