Skip to content

Instantly share code, notes, and snippets.

@Lio-code

Lio-code/gist:8f7440370b5b6122ff4b25b56f1e41a1

Created June 7, 2020 16:37
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Lio-code/8f7440370b5b6122ff4b25b56f1e41a1 to your computer and use it in GitHub Desktop.

Select an option

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