WCS - SQL 05 - JOINS

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

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

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

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

SELECT w.firstname, w.lastname FROM wizard w 
LEFT JOIN player p ON p.wizard_id = w.id 
WHERE p.role='seeker' 
ORDER BY w.lastname;
+-----------+------------+
| 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       |
| Ronald    | Weasley    |
| Fred      | Weasley    |
| William   | Weasley    |
+-----------+------------+
16 rows in set (0.01 sec)

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

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