05-les_jointures

gistfile1.txt

QUERY_1

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


QUERY 2

mysql> SELECT firstname, lastname FROM wizard JOIN player ON player.wizard_id=wizard.id JOIN team ON player.team_id=team.id WHERE role="seeker" ORDER BY lastname ASC, firstname ASC;
+-----------+------------+
| 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.01 sec)

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

QUERY 3

mysql> SELECT firstname, lastname, role FROM wizard LEFT JOIN player ON player.wizard_id=wizard.id LEFT JOIN team ON player.team_id=team.id WHERE ROLE IS NULL ;
+-----------+----------+------+
| firstname | lastname | role |
+-----------+----------+------+
| Terry     | Boot     | NULL |
| Crabbe    |          | NULL |
| Remus     | Lupin    | NULL |
| Padma     | Patil    | NULL |
| Parvati   | Patil    | NULL |
| Demelza   | Robins   | NULL |
| Horace    | Slughorn | NULL |
| Charles   | Weasley  | NULL |
| Molly     | Weasley  | NULL |
+-----------+----------+------+
9 rows in set (0.00 sec)

mysql>