Skip to content

Instantly share code, notes, and snippets.

@Rooarii

Rooarii/gist:9ee7f951e24603b4b38d355cc324758a

Created December 5, 2020 13:06
Show Gist options
  • Save Rooarii/9ee7f951e24603b4b38d355cc324758a to your computer and use it in GitHub Desktop.
Save Rooarii/9ee7f951e24603b4b38d355cc324758a to your computer and use it in GitHub Desktop.
Download ZIP
05-les_jointures
Raw
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>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment