IN2090-ukesoppgaver: Uke 5
Grunnleggende SQL
Alle oppgavene for denne uken l?ses ved bruk av filmdatabasen (se guiden p? semestersiden for hvordan du kobler deg til den). Tallene som er i parentes indikerer forventet antall rader fra sp?rringen.
Oppgave 1 - Enkle SELECT-setninger
Skriv en sp?rring som finner:
- Alle sjangere i tabellen Genre (28)
- Filmid og tittel for alle filmer utgitt i 1892 (12)
- Filmid og tittel for alle filmer der filmid er mellom 2000 og 2030 (14)
- Filmid og tittel p? alle filmer med Star Wars i navnet (129)
- Fornavn og etternavn til personid 465221 (1)
- Alle unike rolletyper (parttype) i tabellen Filmparticipation (7)
- Tittel og produksjons?r for alle filmer som inneholder ordene ?Rush Hour? (15)
- Vis filmid, navn og produksjons?r for filmer som inneholder ordet ?Norge? (27)
- Vis filmid for kinofilmer som har filmtittelen Love (kinofilmer har filmtype ?C?) (42)
- Hvor mange filmer i filmdatabasen er norske?
L?sningsforslag
1
SELECT *
FROM Genre;
/*
genre
-------------
Action
Adult
Adventure
Animation
Biography
Comedy
Crime
Documentary
Drama
Family
Fantasy
Film-Noir
Game-Show
History
Horror
Music
Musical
Mystery
News
Reality-TV
Romance
Sci-Fi
Short
Sport
Talk-Show
Thriller
War
Western
(28 rows)
*/2
SELECT f.filmid, f.title
FROM film AS f
WHERE f.prodyear = 1892;
/*
filmid | title
---------+-------------------------
436251 | Clown et ses chiens, Le
700124 | Clown and His Dogs, The
954426 | Boxing
1394843 | Pauvre Pierrot
1874971 | Un bon bock
2286076 | Poor Pierrot
2320346 | Fencing
2887002 | Hand Shake, A
3086012 | Good Beer, A
3111303 | Man on Parallel Bars
3111751 | Wrestling
5500730 | Prince de Galles, Le
(12 rows)
*/3
SELECT f.filmid, f.title
FROM film AS f
WHERE f.filmid >= 2000 AND f.filmid <= 2030;
/*
filmid | title
-------+--------------------------------------------------------
2001 | Bowling Balls
2003 | Musta rakkaus
2004 | Espantalho
2006 | Northern Lights
2010 | 'Cannibal Apocalypse' Redux
2017 | Chronicles of the Dark Carnival, The
2018 | Oral Majority 3
2019 | Nainen on valttia
2020 | Cidade Oculta
2021 | Highlander: Endgame
2022 | Animales devoradores: El hombre
2023 | Blood Red Planet
2024 | Pura vida Ibiza
2026 | 'Capulina contra las momias' (El terror de Guanajuato)
(14 rows)
*/4
SELECT f.title, f.filmid
FROM Film AS f
WHERE f.title LIKE '%Star Wars%';
/*
title | filmid
----------------------------------------------------------------+---------
'Star Wars' Holiday Special, The | 7450
'Star Wars': A Musical Journey | 7466
'Star Wars': Feel the Force | 7482
Star Wars: Rebel Assault | 47911
Star Wars: Revelations | 62417
Star Wars: Jedi Knight - Dark Forces II | 127428
Star Wars: Episode I - The Phantom Menace | 127652
...
(129 rows)
*/5
SELECT p.firstname, p.lastname
FROM Person AS p
WHERE p.personid = 465221;
/*
firstname | lastname
----------+----------
Johnny | Depp
(1 row)
*/6
SELECT DISTINCT parttype
FROM filmparticipation;
/*
parttype
------------------
writer
costume designer
director
editor
cast
composer
producer
(7 rows)
*/7
SELECT f.title, f.prodyear
FROM film AS f
WHERE f.title LIKE '%Rush Hour%';
/*
title | prodyear
----------------------------------------------------------+----------
Rush Hour | 1941
Rush Hour | 1998
Reel Comedy: Rush Hour 2 | 2001
Rush Hour 3 | 2007
Rush Hour | 2006
Rush Hour 2 | 2001
Rush Hour, The | 1974
Sto?zeit - Rush Hour | 1970
Rush Hour, The | 1928
SimCity 4: Rush Hour | 2003
Rush Hour - Due mine vaganti | 2000
Colpo grosso al drago rosso - Rush Hour 2 | 2002
Piece of the Action: Behind the Scenes of 'Rush Hour', A | 1999
Rush Hour | 1970
Reel Comedy: Rush Hour | 1998
(15 rows)
*/8
SELECT filmid, title, prodyear
FROM film
WHERE title LIKE '%Norge%';9
SELECT fi.filmid
FROM filmitem fi
INNER JOIN film f ON f.filmid = fi.filmid
WHERE fi.filmtype = 'C' AND f.title = 'Love';10
SELECT COUNT(*) AS antallNorskeFilmer
FROM Filmcountry
WHERE country = 'Norway';Oppgave 2 - Nestede setninger:
Skriv en sp?rring som bruker nestede-sp?rringer for ? finne:
- Filmid og filmtype (fra Filmitem) for alle filmer som ble produsert i 1894 (82)
- Navn p? alle kvinnelige skuespillere (cast) i filmen med filmid 357076 (11)
L?sningsforslag
1
SELECT *
FROM Filmitem AS fi
WHERE fi.filmid IN (
SELECT f.filmid
FROM Film AS f
WHERE f.prodyear = 1894
);
-- eller
SELECT *
FROM Filmitem AS fi
INNER JOIN Film AS f ON (f.filmid = fi.filmid)
WHERE f.prodyear = 1894;
/*
filmid | filmtype
---------+----------
105825 | C
265931 | C
286394 | C
365419 | C
377434 | C
377514 | C
380074 | C
413004 | C
432746 | C
438139 | C
457691 | C
492331 | C
492890 | C
...
(82 rows)
*/2
SELECT p.firstname, p.lastname
FROM Person AS p
WHERE p.gender = 'F' AND p.personid IN (
SELECT fp.personid
FROM Filmparticipation AS fp
WHERE fp.filmid = 357076 AND fp.parttype = 'cast'
)
ORDER BY p.lastname;
/*
firstname | lastname
-----------+-------------
Victoria | Beynon-Cole
Cate | Blanchett
Sabine | Crossen
Lori | Dungey
Megan | Edwards
Taea | Hartwell
Katie | Jackson
Sarah | McLeod
Elizabeth | Moody
Kate | O'Rourke
Liv | Tyler
(11 rows)
*/Oppgave 3 - Setninger med ulike typer JOIN:
Skriv en sp?rring som finner.
- Alle sjangere (eng.: genres) til filmen ‘Pirates of the Caribbean: The Legend of Jack Sparrow’ (5)
- Alle sjangere for filmen med filmid 985057 (9)
- Tittel, produksjons?r og filmtype for alle filmer som ble produsert i 1894 (82)
- Alle kvinnelige skuespillere(cast) i filmen med filmid 357076. Skriv ut navn og p? skuespillerene og filmid (11)
- BONUS: Hva er tittelen? Legg til en ekstra kolonne med tittelen (krever join med enda en tabell) (11)
- Finn fornavn og etternavn p? alle personer som har deltatt i TV-serien South Park. Bruk tabellene Person, Filmparticipation og Series, og l?s det med:
- INNER JOIN (21)
- Implisitt join (21)
- NATURAL JOIN
- Hvorfor gir NATURAL JOIN ulikt resultat fra INNER JOIN og implisitt join? Forklar.
- Finn navn p? alle skuespillere (cast) i filmen, deres rolle (parttype) i filmen ?Harry Potter and the Goblet of Fire? (v?r presis med staving), f? med tittelen til filmen ogs? (90)
- Finn navn p? alle skuespillere (cast) i filmen Baile Perfumado (14)
- Skriv ut tittel og regiss?r for norske filmer produsert f?r 1960 (269)
L?sningsforslag
1
SELECT f.title, fg.genre
FROM film AS f
INNER JOIN filmgenre AS fg ON f.filmid = fg.filmid
WHERE f.title = 'Pirates of the Caribbean: The Legend of Jack Sparrow';
/*
title | genre
------------------------------------------------------+-----------
Pirates of the Caribbean: The Legend of Jack Sparrow | Action
Pirates of the Caribbean: The Legend of Jack Sparrow | Adventure
Pirates of the Caribbean: The Legend of Jack Sparrow | Comedy
Pirates of the Caribbean: The Legend of Jack Sparrow | Drama
Pirates of the Caribbean: The Legend of Jack Sparrow | Thriller
(5 rows)
*/2
SELECT *
FROM film AS f
INNER JOIN filmgenre AS fg USING (filmid)
WHERE filmid = 985057;
/*
filmid | title | prodyear | genre
--------+---------+----------+-----------
985057 | Matilda | 1996 | Action
985057 | Matilda | 1996 | Adventure
985057 | Matilda | 1996 | Comedy
985057 | Matilda | 1996 | Crime
985057 | Matilda | 1996 | Drama
985057 | Matilda | 1996 | Family
985057 | Matilda | 1996 | Fantasy
985057 | Matilda | 1996 | Mystery
985057 | Matilda | 1996 | Thriller
(9 rows)
*/3
SELECT f.title, f.prodyear, fi.filmtype
FROM Film AS f, Filmitem AS fi
WHERE f.prodyear = 1894 AND f.filmid = fi.filmid;
-- eller
SELECT f.title, f.prodyear, fi.filmtype
FROM Film AS f NATURAL JOIN Filmitem AS fi
WHERE f.prodyear = 1894;
-- eller
SELECT f.title, f.prodyear, fi.filmtype
FROM Film AS f INNER JOIN Filmitem AS fi ON f.filmid = fi.filmid
WHERE f.prodyear = 1894;
/*
title | prodyear | filmtype
------------------------------------------------+----------+----------
Fancy Club Swinger | 1894 | C
Barbershop, The | 1894 | C
Amateur Gymnast, No. 2 | 1894 | C
Caicedo (with Pole) | 1894 | C
Annabelle Butterfly Dance | 1894 | C
Annabelle Sun Dance | 1894 | C
Annie Oakley | 1894 | C
Barber Shop, The | 1894 | C
Armand D'Ary | 1894 | C
Cock Fight, The | 1894 | C
....
(82 rows)
*/4
SELECT DISTINCT p.firstname, p.lastname, fp.filmid
FROM Person AS p, Filmparticipation AS fp
WHERE p.gender = 'F'
AND fp.filmid = 357076
AND fp.parttype = 'cast'
AND p.personid = fp.personid;
-- eller
SELECT DISTINCT p.firstname, p.lastname, fp.filmid
FROM Person AS p NATURAL JOIN Filmparticipation AS fp
WHERE p.gender = 'F'
AND fp.filmid = 357076
AND fp.parttype = 'cast';
-- eller
SELECT DISTINCT p.firstname, p.lastname, fp.filmid
FROM Person AS p INNER JOIN Filmparticipation AS fp ON p.personid = fp.personid
WHERE p.gender = 'F'
AND fp.filmid = 357076
AND fp.parttype = 'cast';
/*
firstname | lastname | filmid
-----------+-------------+--------
Cate | Blanchett | 357076
Elizabeth | Moody | 357076
Kate | O'Rourke | 357076
Katie | Jackson | 357076
Liv | Tyler | 357076
Lori | Dungey | 357076
Megan | Edwards | 357076
Sabine | Crossen | 357076
Sarah | McLeod | 357076
Taea | Hartwell | 357076
Victoria | Beynon-Cole | 357076
(11 rows)
*/
-- BONUS:
SELECT DISTINCT p.firstname, p.lastname, fp.filmid, f.title
FROM Person AS p, Filmparticipation AS fp, Film AS f
WHERE p.gender = 'F'
AND fp.filmid = 357076
AND fp.parttype = 'cast'
AND p.personid = fp.personid
AND fp.filmid = f.filmid;
-- eller
SELECT DISTINCT p.firstname, p.lastname, fp.filmid, f.title
FROM Person AS p NATURAL JOIN Filmparticipation AS fp NATURAL JOIN Film AS f
WHERE p.gender = 'F'
AND fp.filmid = 357076
AND fp.parttype = 'cast';
-- eller
SELECT DISTINCT p.firstname, p.lastname, fp.filmid, f.title
FROM Person AS p INNER JOIN Filmparticipation AS fp ON p.personid = fp.personid
INNER JOIN Film AS f ON fp.filmid = f.filmid
WHERE p.gender = 'F'
AND fp.filmid = 357076
AND fp.parttype = 'cast';
/*
firstname | lastname | filmid | title
-----------+-------------+--------+----------------------------------------------------
Cate | Blanchett | 357076 | Lord of the Rings: The Fellowship of the Ring, The
Elizabeth | Moody | 357076 | Lord of the Rings: The Fellowship of the Ring, The
Kate | O'Rourke | 357076 | Lord of the Rings: The Fellowship of the Ring, The
Katie | Jackson | 357076 | Lord of the Rings: The Fellowship of the Ring, The
Liv | Tyler | 357076 | Lord of the Rings: The Fellowship of the Ring, The
Lori | Dungey | 357076 | Lord of the Rings: The Fellowship of the Ring, The
Megan | Edwards | 357076 | Lord of the Rings: The Fellowship of the Ring, The
Sabine | Crossen | 357076 | Lord of the Rings: The Fellowship of the Ring, The
Sarah | McLeod | 357076 | Lord of the Rings: The Fellowship of the Ring, The
Taea | Hartwell | 357076 | Lord of the Rings: The Fellowship of the Ring, The
Victoria | Beynon-Cole | 357076 | Lord of the Rings: The Fellowship of the Ring, The
(11 rows)
*/5
-- a. INNER JOIN
SELECT DISTINCT p.personid, p.lastname, p.firstname, s.maintitle
FROM Person p
INNER JOIN Filmparticipation AS fp ON p.personid = fp.personid
INNER JOIN Series AS s ON s.seriesid = fp.filmid
WHERE s.maintitle = 'South Park';
-- b. Implisitt join
SELECT DISTINCT p.personid, p.lastname, p.firstname, s.maintitle
FROM Person AS p, Filmparticipation AS fp, Series AS s
WHERE s.seriesid = fp.filmid
AND p.personid = fp.personid
AND maintitle = 'South Park';
-- c. NATURAL JOIN
SELECT DISTINCT p.personid, p.lastname, p.firstname, s.maintitle
FROM Person AS p
NATURAL JOIN Filmparticipation AS fp
NATURAL JOIN Series AS s
WHERE s.maintitle LIKE 'South Park';Svar p? d):
NATURAL JOIN joiner “automatisk” p? attributtene som har samme navn. Dette fungerer i join-operasjonen mellom tabellen Person og Filmparticipation fordi begge har attributtet personid som det joines p?. Men mellom Filmparticipation og Series er det ingen attributter med felles navn: vi m? joine p? filmparticipation.filmid og series.seriesid, og det g?r ikke med NATURAL JOIN. Vi m? derfor bruke en annen join-metode (som i a eller b).
6
SELECT DISTINCT p.firstname, p.lastname, fp.parttype, f.title
FROM Person AS p
INNER JOIN Filmparticipation AS fp USING (personid)
INNER JOIN film AS f USING (filmid)
WHERE title = 'Harry Potter and the Goblet of Fire' AND parttype = 'cast';7
SELECT DISTINCT p.firstname || ' ' || p.lastname AS name
FROM film AS f
INNER JOIN filmparticipation AS fp USING (filmid)
INNER JOIN person AS p USING (personid)
WHERE fp.parttype = 'cast'
AND f.title = 'Baile Perfumado';8
SELECT film.title, person.firstname || ' ' || person.lastname AS fullname
FROM filmcountry
INNER JOIN film USING (filmid)
INNER JOIN filmparticipation USING (filmid)
INNER JOIN person USING (personid)
WHERE filmcountry.country = 'Norway'
AND parttype = 'director'
AND prodyear < 1960;