Gagnasafnsfræði, haust 2011
[ Dagskrá | Námsefni | Verkefni | Dæmatímar | Orðalisti | Námsmat | Kennslubók ]Verkefni 3 - SQL
Skiladæmi:
- Sýnið SQL skipun sem sækir lista yfir nöfn allra kvikmynda þar sem Al Pacino og Robert De Niro hafa leikið saman. Sýnið einnig niðurstöðurnar.
Lausn:
SELECT m.title FROM movie m, casting c, actor a WHERE m.id=c.movieid AND c.actorid=a.id AND a.name='Al Pacino' INTERSECT SELECT m.title FROM movie m, casting c, actor a WHERE m.id=c.movieid AND c.actorid=a.id AND a.name='Robert De Niro'
Útkoma:
title ------------------------- Godfather: Part II, The Heat (2 rows) - Sýnið SQL skipun sem sækir lista yfir nöfn allra leikara sem hafa leikið í kvikmynd með Kevin Bacon. Ef leikari hefur leikið í fleiri en einni mynd með Kevin Bacon þá á nafnið hans samt bara að koma fram einusinni í niðurstöðunum.
Lausn:
Sækjum actorid fyrir Kevin Bacon:
SELECT * FROM actor WHERE name='Kevin Bacon'; id | name ----+------------- 46 | Kevin Bacon (1 row)
Í hvaða myndum hefur Kevin Bacon leikið?
SELECT * FROM casting WHERE actorid=46; movieid | actorid | ord ---------+---------+----- 525 | 46 | 3 571 | 46 | 2 587 | 46 | 1 466 | 46 | 5 533 | 46 | 2 229 | 46 | 1 134 | 46 | 4 253 | 46 | 1 53 | 46 | 3 1680 | 46 | 4 1668 | 46 | 1 1431 | 46 | 3 1176 | 46 | 12 1367 | 46 | 4 904 | 46 | 1 1048 | 46 | 2 1067 | 46 | 5 (17 rows)Allir sem hafa leikið í t.d. movieid=525 hafa því leikið í kvikmynd með Kevin Bacon:
SELECT * FROM casting WHERE movieid=525; movieid | actorid | ord ---------+---------+----- 525 | 613 | 4 525 | 40 | 2 525 | 46 | 3 525 | 202 | 5 525 | 224 | 1 525 | 1943 | 11 525 | 3011 | 9 525 | 3257 | 7 525 | 3440 | 6 (9 rows)Gætum þá notað IN og talið upp allar myndir:
en það er alltof mikil handavinna. Í staðinn er hægt að nota IN með subquery sem sækir listann yfir allar kvikmyndir Kevin Bacons:SELECT * FROM casting WHERE movieid IN (525,571,587, ... );
Hreinsum þetta upp, tengjum við actor töfluna og sækjum nöfn allra leikara:SELECT * FROM casting WHERE movieid IN (SELECT movieid FROM casting WHERE actorid=46); movieid | actorid | ord ---------+---------+----- 466 | 14 | 1 466 | 46 | 5 466 | 180 | 2 466 | 341 | 4 466 | 1214 | 6 [..]SELECT DISTINCT a.name FROM actor a, casting c WHERE a.id=c.actorid AND c.movieid IN (SELECT movieid FROM casting WHERE actorid=46) ORDER BY a.name ASC; name ----------------------- Adrienne King Aeryk Egan Alec Baldwin Ariana Richards Benjamin Bratt Betsy Palmer Bibi Besch Bill Murray [..] - Sýnið SQL skipun sem sækir lista yfir nöfn allra kvikmynda og fyrir hverja kvikmynd, fjölda leikara sem hún á sameiginlega með Star Wars frá 1977 (þeas. fjölda leikara sem leika í báðum myndunum). Ef kvikmyndin hefur engan leikara sameiginlegan með Star Wars þá á hún ekki að birtast. Raðið niðurstöðunum eftir fjölda sameiginlegra leikara í lækkandi röð. Sýnið fyrstu 10 niðurstöðurnar.
Lausn:
Sækjum lista yfir alla leikara í Star Wars frá 1977 (sem hefur movieid=1):
SELECT * FROM casting WHERE movieid=1; movieid | actorid | ord ---------+---------+----- 1 | 6 | 2 1 | 462 | 3 1 | 552 | 1 1 | 925 | 5 1 | 1655 | 6 1 | 2881 | 7 1 | 3178 | 8 1 | 3234 | 4 1 | 3707 | 9 1 | 5788 | 12 (10 rows)Gætum þá sótt lista yfir allar kvikmyndir fyrir leikara með actorid=6 (sem er Harrison Ford):
SELECT * FROM casting WHERE actorid=6; movieid | actorid | ord ---------+---------+----- 482 | 6 | 1 429 | 6 | 1 700 | 6 | 9 436 | 6 | 1 371 | 6 | 12 553 | 6 | 1 662 | 6 | 1 [..]Gætum útvíkkað fyrri fyrirspurn með IN og subquery til að sækja allar kvikmyndir fyrir öll actorid sem komu úr fyrstu fyrirspurninni (þeas. alla leikara í Star Wars frá 1977):
SELECT * FROM casting WHERE actorid IN (SELECT actorid FROM casting WHERE movieid=1); movieid | actorid | ord ---------+---------+----- 482 | 6 | 1 429 | 6 | 1 700 | 6 | 9 436 | 6 | 1 371 | 6 | 12 553 | 6 | 1 [..]Þá vantar okkur bara að telja hversu oft hver kvikmynd kemur fyrir í töflunni:
SELECT movieid, COUNT(*) FROM casting WHERE actorid IN (SELECT actorid FROM casting WHERE movieid=1) GROUP BY movieid; movieid | count ---------+------- 842 | 1 574 | 1 13 | 8 891 | 1 [..]Tengjum nú við movie töfluna til að sækja nöfn kvikmynda og röðum eftir fjölda sameiginlegra leikara:
SELECT m.title, COUNT(*) FROM movie m, casting c WHERE m.id=c.movieid AND c.actorid IN (SELECT actorid FROM casting WHERE movieid=1) GROUP BY m.id, m.title ORDER BY COUNT(*) DESC; title | count ------------------------------------------------+------- Star Wars | 10 Star Wars: Episode V - The Empire Strikes Back | 9 Star Wars: Episode VI - Return of the Jedi | 8 Time Bandits | 2 Star Wars: Episode I - The Phantom Menace | 2 Six Days Seven Nights | 1 [..] - Sýnið SQL skipun sem sækir lista yfir allar kvikmyndir (A, B, n) þar sem A og B eru nöfn á kvikmyndum (og A != B) og n er fjöldi sameiginlegra leikara í A og B. Raðið niðurstöðum eftir fjölda sameiginlegra leikara í lækkandi röð. Sýnið einnig fyrstu 10 niðurstöðurnar.
Lausn:
Hvernig skilgreinum við "sameiginlegan leikara" ?
Ef við höfum tvær raðir í casting, segjum A og B, þar sem A.actorid=B.actorid þá eiga myndirnar A.movieid og B.movieid einn sameiginlegan leikara (þeas. actorid, sem er það sama í báðum röðunum).
Lausnin okkar byggist á því að vinna alltaf með tvær raðir í einu úr casting og para þær saman skv. ofangreindum skilyrðum. Til að vinna með tvær raðir í einu úr casting þá sækjum við tvisvar úr casting, þeas. við notum self join.
Sækjum tvær raðir samtímis úr casting með því að sækja casting × casting (allt venslað við allt):
CROSS JOIN er jafngilt venjulegu INNER JOIN án skilyrða:SELECT * FROM casting c1 CROSS JOIN casting c2; movieid | actorid | ord | movieid | actorid | ord ---------+---------+-----+---------+---------+----- 972 | 588 | 1 | 972 | 588 | 1 972 | 588 | 1 | 849 | 588 | 2 972 | 588 | 1 | 1575 | 588 | 3 972 | 588 | 1 | 1835 | 588 | 1 972 | 588 | 1 | 47 | 590 | 4 972 | 588 | 1 | 334 | 590 | 5 972 | 588 | 1 | 368 | 590 | 2 972 | 588 | 1 | 549 | 590 | 4 972 | 588 | 1 | 886 | 591 | 5 972 | 588 | 1 | 882 | 591 | 4 [..]SELECT * FROM casting c1, casting c2; movieid | actorid | ord | movieid | actorid | ord ---------+---------+-----+---------+---------+----- 972 | 588 | 1 | 972 | 588 | 1 972 | 588 | 1 | 849 | 588 | 2 972 | 588 | 1 | 1575 | 588 | 3 972 | 588 | 1 | 1835 | 588 | 1 972 | 588 | 1 | 47 | 590 | 4 972 | 588 | 1 | 334 | 590 | 5 972 | 588 | 1 | 368 | 590 | 2 972 | 588 | 1 | 549 | 590 | 4 972 | 588 | 1 | 886 | 591 | 5 972 | 588 | 1 | 882 | 591 | 4 [..]Bætum nú við skilyrði sem tengir saman raðir í c1 og c2 þar sem leikarinn í báðum röðum er sá sami:
SELECT * FROM casting c1, casting c2 WHERE c1.actorid=c2.actorid movieid | actorid | ord | movieid | actorid | ord ---------+---------+-----+---------+---------+----- 724 | 1 | 8 | 724 | 1 | 8 642 | 1 | 1 | 724 | 1 | 8 705 | 1 | 9 | 724 | 1 | 8 254 | 1 | 1 | 724 | 1 | 8 208 | 1 | 1 | 724 | 1 | 8 [..]Bætum við auka skilyrði og hreinsum út þær raðir þar sem kvikmyndin í báðum röðum er sú sama (augljóslega hefur kvikmynd alla leikara sameiginlega með sjálfri sér, viljum ekki telja það tilfelli með):
SELECT * FROM casting c1, casting c2 WHERE c1.actorid=c2.actorid AND c1.movieid != c2.movieid movieid | actorid | ord | movieid | actorid | ord ---------+---------+-----+---------+---------+----- 642 | 1 | 1 | 724 | 1 | 8 705 | 1 | 9 | 724 | 1 | 8 254 | 1 | 1 | 724 | 1 | 8 208 | 1 | 1 | 724 | 1 | 8 1559 | 1 | 1 | 724 | 1 | 8 1770 | 1 | 1 | 724 | 1 | 8 [..]Núna erum við að fá tvöfaldar niðurstöður. Fyrir sérhvern leikara L sem kvikmyndir A og B eiga sameiginlegan fáum við tvær raðir: (A, B, L) og (B, A, L).
Skoðum til dæmis sameiginlega leikara í movieid=1 og movieid=13:
SELECT * FROM casting c1, casting c2 WHERE c1.actorid=c2.actorid AND c1.movieid != c2.movieid AND c1.movieid IN (1,13) AND c2.movieid IN (1,13); movieid | actorid | ord | movieid | actorid | ord ---------+---------+-----+---------+---------+----- 13 | 6 | 2 | 1 | 6 | 2 -- actor 6 í 13 og 1 1 | 6 | 2 | 13 | 6 | 2 -- actor 6 í 1 og 13 13 | 462 | 3 | 1 | 462 | 3 1 | 462 | 3 | 13 | 462 | 3 1 | 552 | 1 | 13 | 552 | 1 13 | 552 | 1 | 1 | 552 | 1 13 | 925 | 12 | 1 | 925 | 5 1 | 925 | 5 | 13 | 925 | 12 13 | 1655 | 5 | 1 | 1655 | 6 1 | 1655 | 6 | 13 | 1655 | 5 13 | 2881 | 13 | 1 | 2881 | 7 1 | 2881 | 7 | 13 | 2881 | 13 13 | 3178 | 6 | 1 | 3178 | 8 1 | 3178 | 8 | 13 | 3178 | 6 13 | 3707 | 11 | 1 | 3707 | 9 1 | 3707 | 9 | 13 | 3707 | 11 (16 rows)Ef við bætum við skilyrðinu c1.movieid < c2.movieid þá fáum við bara aðra röðina en ekki báðar.
Dæmi:
Hver röð er einn sameiginlegur leikari hjá c1.movieid og c2.movieid.SELECT * FROM casting c1, casting c2 WHERE c1.actorid=c2.actorid AND c1.movieid < c2.movieid; movieid | actorid | ord | movieid | actorid | ord ---------+---------+-----+---------+---------+----- 972 | 588 | 1 | 1835 | 588 | 1 972 | 588 | 1 | 1575 | 588 | 3 849 | 588 | 2 | 1835 | 588 | 1 849 | 588 | 2 | 1575 | 588 | 3 849 | 588 | 2 | 972 | 588 | 1 1575 | 588 | 3 | 1835 | 588 | 1 47 | 590 | 4 | 549 | 590 | 4 47 | 590 | 4 | 368 | 590 | 2 47 | 590 | 4 | 334 | 590 | 5 [..]Nú skulum við telja hversu oft hvert (c1.movieid, c2.movieid) par kemur fyrir, þeas. hversu marga leikara c1.movieid og c2.movieid hafa sameiginlega:
Tengjum nú við movie töfluna til að sækja nöfn kvikmyndanna og röðum eftir fjölda sameiginlegra leikara.SELECT c1.movieid, c2.movieid, COUNT(*) FROM casting c1, casting c2 WHERE c1.actorid=c2.actorid AND c1.movieid < c2.movieid GROUP BY c1.movieid, c2.movieid; movieid | movieid | count ---------+---------+------- 1 | 5 | 9 1 | 11 | 1 1 | 13 | 8 1 | 14 | 1 1 | 17 | 2 [..](Augljóslega þarf að tengja tvisvar við movie, þeas. tengja c1 og c2 í sitthvoru lagi við movie)
SELECT m1.title, m2.title, COUNT(*) FROM casting c1, casting c2, movie m1, movie m2 WHERE c1.actorid=c2.actorid AND c1.movieid=m1.id AND c2.movieid=m2.id AND c1.movieid < c2.movieid GROUP BY c1.movieid, c2.movieid, m1.title, m2.title ORDER BY COUNT(*) DESC; title | title | count ------------------------------------------------+------------------------------------------------+------- Cocoon | Cocoon: The Return | 10 Police Academy 4: Citizens on Patrol | Police Academy 3: Back in Training | 10 Police Academy 2: Their First Assignment | Police Academy 3: Back in Training | 10 Star Wars: Episode V - The Empire Strikes Back | Star Wars: Episode VI - Return of the Jedi | 10 Toy Story | Toy Story 2 | 9 [..] - Sýnið SQL skipun sem sækir lista yfir alla leikara (A, B, n) þar sem A og B eru nöfn á leikurum (og A != B) og n er fjöldi sameiginlegra kvikmynda sem báðir hafa leikið í. Raðið niðurstöðum eftir fjölda sameiginlegra kvikmynda í lækkandi röð. Sýnið einnig fyrstu 10 niðurstöðurnar.
Lausn:
Þetta dæmi er leyst á svipaðan hátt og síðasta dæmi.
Við skilgreinum "sameiginleg kvikmynd" á eftirfarandi hátt:
Ef við höfum tvær raðir í casting, segjum A og B, þar sem A.movieid=B.movieid þá eiga leikararnir A.actorid og B.actorid eina sameiginlega kvikmynd (þeas. movieid, sem er það sama í báðum röðunum).
Ef við tökum fyrirspurnina sem við enduðum með í dæmi 4 og breytum henni lítillega þá endum við með eftirfarandi fyrirspurn.
SELECT a1.name, a2.name, COUNT(*) FROM casting c1, casting c2, actor a1, actor a2 WHERE c1.movieid=c2.movieid AND c1.actorid=a1.id AND c2.actorid=a2.id AND c1.actorid < c2.actorid GROUP BY c1.actorid, c2.actorid, a1.name, a2.name ORDER BY COUNT(*) DESC; name | name | count --------------------------------+--------------------------------+------- Bernard Lee (I) | Lois Maxwell | 8 Desmond Llewelyn | Lois Maxwell | 8 Woody Allen | Mia Farrow | 7 James Doohan | Walter Koenig | 7 David Graf | George Gaynes | 7 David Graf | Michael Winslow | 7 William Shatner | Walter Koenig | 7 Michael Winslow | George Gaynes | 7 William Shatner | James Doohan | 7 Sean Connery | Bernard Lee (I) | 6 [..]