gistfile1.txt

Queries:
SELECT w.lastname, w.firstname, p.role, t.name
FROM player AS p
  JOIN wizard AS w ON p.wizard_id=w.id
  JOIN team AS t ON p.team_id=t.id
ORDER BY t.name, p.role, w.lastname, w.firstname;

SELECT w.lastname, w.firstname
FROM player AS p
  JOIN wizard AS w ON p.wizard_id=w.id
  JOIN team AS t ON p.team_id=t.id
WHERE p.role='seeker'
ORDER BY w.lastname, w.firstname;

SELECT w.*
FROM player AS p
  RIGHT JOIN wizard AS w ON p.wizard_id=w.id
WHERE p.role IS NULL;

Result:
+-------------+-----------+--------+------------+
| lastname    | firstname | role   | name       |
+-------------+-----------+--------+------------+
| malefoy     | drago     | beater | Gryffindor |
| weasley     | fred      | beater | Gryffindor |
| granger     | hermione  | chaser | Gryffindor |
| grindelwald | gellert   | keeper | Gryffindor |
| krum        | viktor    | seeker | Gryffindor |
| potter      | lily      | seeker | Gryffindor |
| potter      | harry     | beater | Hufflepuff |
| weasley     | george    | chaser | Hufflepuff |
| akingbade   | babajide  | keeper | Hufflepuff |
| weasley     | arthur    | keeper | Hufflepuff |
| jédusor     | tom       | beater | Ravenclaw  |
| rogue       | severus   | chaser | Ravenclaw  |
| dursley     | dudley    | seeker | Ravenclaw  |
| delacour    | gabrielle | beater | Slytherin  |
| dumbledore  | albus     | beater | Slytherin  |
| weasley     | ron       | chaser | Slytherin  |
| weasley     | ginny     | keeper | Slytherin  |
+-------------+-----------+--------+------------+
17 rows in set (0,00 sec)

+----------+-----------+
| lastname | firstname |
+----------+-----------+
| dursley  | dudley    |
| krum     | viktor    |
| potter   | lily      |
+----------+-----------+
3 rows in set (0,00 sec)

+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  9 | molly     | weasley  |
| 15 | fleur     | delacour |
+----+-----------+----------+
2 rows in set (0,00 sec)