justinehell/join.sql

## join.sql
// Retourne les noms, prénoms, rôle et équipe de tous les joueurs, classés dans l’ordre alphabétique par équipe,
// puis par rôle dans l’équipe, puis par nom de famille, puis par prénom.

SELECT lastname, firstname, role, name
FROM wizard
JOIN player ON wizard.id=player.wizard_id
JOIN team ON team.id=player.team_id
ORDER BY name ASC, role ASC, lastname ASC, firstname ASC;

+-----------------+-------------+--------+------------+
| 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.00 sec)

// Retourne uniquement les prénom et nom des joueurs ayant le rôle de seeker (attrapeur),
// classés par ordre alphabétique de nom puis prénom

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


// Retourne la liste de tous les sorciers qui ne pratiquent pas le quidditch.

SELECT firstname, lastname, role
FROM wizard
LEFT JOIN player 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)
	// Retourne les noms, prénoms, rôle et équipe de tous les joueurs, classés dans l’ordre alphabétique par équipe,
	// puis par rôle dans l’équipe, puis par nom de famille, puis par prénom.

	SELECT lastname, firstname, role, name
	FROM wizard
	JOIN player ON wizard.id=player.wizard_id
	JOIN team ON team.id=player.team_id
	ORDER BY name ASC, role ASC, lastname ASC, firstname ASC;

	+-----------------+-------------+--------+------------+
	\| 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.00 sec)

	// Retourne uniquement les prénom et nom des joueurs ayant le rôle de seeker (attrapeur),
	// classés par ordre alphabétique de nom puis prénom

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


	// Retourne la liste de tous les sorciers qui ne pratiquent pas le quidditch.

	SELECT firstname, lastname, role
	FROM wizard
	LEFT JOIN player 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)