Jointure

Jointure

oriane@oriane-HP-Pavilion-Gaming-Laptop-15-cx0xxx:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.17-0ubuntu2 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE db5
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM wizard;
+----+-------------+-----------------+
| id | firstname   | lastname        |
+----+-------------+-----------------+
|  1 | Hannah      | Abbott          |
|  2 | Katie       | Bell            |
|  3 | Cuthbert    | Binns           |
|  4 | Phineas     | Nigellus        |
|  5 | Regulus     | Black           |
|  6 | Sirius      | Black           |
|  7 | Amelia      | Bones           |
|  8 | Susan       | Bones           |
|  9 | Terry       | Boot            |
| 10 | Lavender    | Brown           |
| 11 | Millicent   | Bulstrode       |
| 12 | Cho         | Chang           |
| 13 | Penelope    | Clearwater      |
| 14 | Michael     | Corner          |
| 15 | Crabbe      |                 |
| 16 | Vincent     | Crabbe          |
| 17 | Colin       | Creevey         |
| 18 | Dennis      | Creevey         |
| 19 | Cedric      | Diggory         |
| 20 | Aberforth   | Dumbledore      |
| 21 | Albus       | Dumbledore      |
| 22 | Marietta    | Edgecombe       |
| 23 | Justin      | Finch-Fletchley |
| 24 | Seamus      | Finnigan        |
| 25 | Marcus      | Flint           |
| 26 | Filius      | Flitwick        |
| 27 | Anthony     | Goldstein       |
| 28 | Gregory     | Goyle           |
| 29 | Hermione    | Granger         |
| 30 | Godric      | Gryffindor      |
| 31 | Rubeus      | Hagrid          |
| 32 | Helga       | Hufflepuff      |
| 33 | Angelina    | Johnson         |
| 34 | Lee         | Jordan          |
| 35 | Bellatrix   | Lestrange       |
| 36 | Rabastan    | Lestrange       |
| 37 | Rodolphus   | Lestrange       |
| 38 | Gilderoy    | Lockhart        |
| 39 | Alice       | Longbottom      |
| 40 | Frank       | Longbottom      |
| 41 | Augusta     | Longbottom      |
| 42 | Neville     | Longbottom      |
| 43 | Luna        | Lovegood        |
| 44 | Xenophilius | Lovegood        |
| 45 | Remus       | Lupin           |
| 46 | Draco       | Malfoy          |
| 47 | Lucius      | Malfoy          |
| 48 | Narcissa    | Malfoy          |
| 49 | Minerva     | McGonagall      |
| 50 | Theodore    | Nott            |
| 51 | Garrick     | Ollivander      |
| 52 | Pansy       | Parkinson       |
| 53 | Padma       | Patil           |
| 54 | Parvati     | Patil           |
| 55 | Peter       | Pettigrew       |
| 56 | Harry       | Potter          |
| 57 | James       | Potter          |
| 59 | Quirinus    | Quirrell        |
| 60 | Helena      | Ravenclaw       |
| 61 | Rowena      | Ravenclaw       |
| 62 | Tom         | Riddle          |
| 63 | Demelza     | Robins          |
| 64 | Newton      | Scamander       |
| 65 | Horace      | Slughorn        |
| 66 | Salazar     | Slytherin       |
| 67 | Hepzibah    | Smith           |
| 68 | Zacharias   | Smith           |
| 69 | Severus     | Snape           |
| 70 | Alicia      | Spinnet         |
| 71 | Pomona      | Sprout          |
| 72 | Dean        | Thomas          |
| 73 | Andromeda   | Tonks           |
| 74 | Nymphadora  | Tonks           |
| 75 | Sybill      | Trelawney       |
| 76 | Dolores     | Umbridge        |
| 77 | Romilda     | Vane            |
| 78 | Arthur      | Weasley         |
| 79 | William     | Weasley         |
| 80 | Charles     | Weasley         |
| 81 | Fred        | Weasley         |
| 82 | George      | Weasley         |
| 83 | Ginevra     | Weasley         |
| 84 | Molly       | Weasley         |
| 85 | Percy       | Weasley         |
| 86 | Ronald      | Weasley         |
| 87 | Oliver      | Wood            |
| 88 | Blaise      | Zabini          |
| 89 | Bloody      | Baron           |
| 90 | Cadogan     |                 |
| 91 | Fat         | Friar           |
| 92 | Myrtle      | Warren          |
| 93 | Nicholas    | de              |
+----+-------------+-----------------+
92 rows in set (0.00 sec)

mysql> SELECT firstname, lastname, role, name
    -> FROM player
    -> JOIN wizard ON wizard.id=player.wizard_id
    -> JOIN team ON team.id=player.team_id
    -> ORDER BY name ASC, role ASC, lastname ASC, firstname ASC;
+-------------+-----------------+--------+------------+
| 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 |
| 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  |
+-------------+-----------------+--------+------------+
83 rows in set (0.00 sec)

mysql> SELECT firstname, lastname, role
    -> FROM player 
    -> JOIN wizard ON wizard.id=player.wizard_id
    -> JOIN team ON team.id=player.team_id
    -> WHERE role LIKE 's%';
+-----------+------------+--------+
| 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 |
| William   | Weasley    | seeker |
| Fred      | Weasley    | seeker |
| Ronald    | Weasley    | seeker |
+-----------+------------+--------+
16 rows in set (0.00 sec)

mysql> SELECT firstname, lastname, role
    -> FROM player
    -> RIGHT JOIN wizard ON wizard.id=player.wizard_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)