/* L?sningsforslag INF1300 Ukesoppgavesett 5 */ /* Enkle SELECT setninger */ -- 1) /* Alle filmider og titler i tabellen film. (692361) */ SELECT f.filmid, f.title FROM film f; /* filmid | title ----------+---------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------- 17 | E.R. Sluts 18 | Tarantos, Los 19 | Privilege 20 | Jag en kvinna, II - ?ktenskapet 21 | Jack l'谷ventreur 23 | Brooklyn Connection, The .................................................................................................................................. (692361 rows) */ -- 2) /* Filmid og tittel for alle filmer utgitt i 1892 (12) */ SELECT f.filmid, f.title FROM film f WHERE prodyear = 1892; -- 3) /* Alle filmider og titler i tabellen film hvor filmid er mellom 2000 og 2030 (14) */ SELECT f.filmid, f.title FROM film 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) /* Alle etternavn(hvert etternavn kun listet en gang) i tabellen Person i fallende / motsatt rekekf?lge. Bruk limit til ? begrense til 200 rader (200) */ SELECT DISTINCT p.lastname FROM Person p ORDER BY p.lastname desc LIMIT 200; /* lastname ---------------------- ?r芍insson 迆r k車r B迆sta?akirkju 谷s a Borz 芍 Neystab? 角 B?le ?邦k邦r ?車r車lfsd車ttir ?車r?arson ... (200 rows) */ -- 5) /* Tittel og filmid p? alle filmer med 'Star Wars' i navnet. (129) */ SELECT f.title, f.filmid FROM Film f WHERE 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) */ -- 6) /* Fornavn og etternavn til personid: 465221 (1) */ SELECT p.firstname, p.lastname FROM Person p WHERE p.personid = 465221; /* firstname | lastname -----------+---------- Johnny | Depp (1 row) */ -- 7) /* Alle unike rolletyper(parttype) i tabellen filmparticipation (7) */ SELECT DISTINCT parttype FROM filmparticipation /* parttype ------------------ writer costume designer director editor cast composer producer (7 rows) */ /* Setninger som krever JOIN med 2 tabeller */ -- 1) /* Tittel produksjons?r og filmtype for alle filmer som ble produsert i 1894 (82) */ SELECT f.title, f.prodyear, fi.filmtype FROM Film f INNER JOIN Filmitem 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) */ -- 2) /* Tittel og produksjons?r for alle filmer som inneholder ordene "Rush Hour" (82) (ingen joins) */ SELECT f.title, f.prodyear FROM film 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) */ -- 3) /*Alle kvinnelige skuespillere(cast) i filmen med filmid 357076. Skriv ut navn og pa skuespillerene og filmid BONUS: Hva er tittelen p? denne filmen? BONUS: Hva er tittelen? Legg til en ekstra kolonne med tittelen (krever join med enda en tabell). */ SELECT DISTINCT p.firstname, p.lastname, fp.filmid FROM Person p NATURAL JOIN Filmparticipation fp WHERE gender = 'F' AND filmid = 357076; /* firstname | lastname | filmid -----------+-------------+-------- Sabine | Crossen | 357076 Sarah | McLeod | 357076 Megan | Edwards | 357076 Elizabeth | Moody | 357076 Philippa | Boyens | 357076 Kate | O'Rourke | 357076 Katie | Jackson | 357076 Fran | Walsh | 357076 Ngila | Dickson | 357076 Lori | Dungey | 357076 Victoria | Beynon-Cole | 357076 Liv | Tyler | 357076 Cate | Blanchett | 357076 Taea | Hartwell | 357076 (14 rows) */ /* Sp?rringer som krever JOIN med 3 tabeller */ -- 1) /* 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 eller 91) */ SELECT DISTINCT p.firstname, p.lastname, fp.parttype, f.title FROM Person p NATURAL JOIN Filmparticipation fp NATURAL JOIN film f WHERE title = 'Harry Potter and the Goblet of Fire' AND parttype = 'cast'; /* firstname | lastname | parrtype | title -------------+--------------+----------+------------------------------------- Geraldine | Somerville| cast | Harry Potter and the Goblet of Fire Ralph | Fiennes | cast | Harry Potter and the Goblet of Fire Robert | Wilfort | cast | Harry Potter and the Goblet of Fire Jason | Isaacs | cast | Harry Potter and the Goblet of Fire William | Melling | cast | Harry Potter and the Goblet of Fire Harper | Marshall | cast | Harry Potter and the Goblet of Fire Ian | Whyte | cast | Harry Potter and the Goblet of Fire Margery | Mason | cast | Harry Potter and the Goblet of Fire Eric | Sykes | cast | Harry Potter and the Goblet of Fire Christophe | Whitting | cast | Harry Potter and the Goblet of Fire Sam | Hazeldine | cast | Harry Potter and the Goblet of Fire Timothy | Spall | cast | Harry Potter and the Goblet of Fire Angelica | Mandy | cast | Harry Potter and the Goblet of Fire Tiana | Benjamin | cast | Harry Potter and the Goblet of Fire Sheila | Allen | cast | Harry Potter and the Goblet of Fire Anne | Lacy | cast | Harry Potter and the Goblet of Fire Robert | Hardy | cast | Harry Potter and the Goblet of Fire Jimmy | Song | cast | Harry Potter and the Goblet of Fire Predrag | Bjelac | cast | Harry Potter and the Goblet of Fire Paschal | Friel | cast | Harry Potter and the Goblet of Fire Robert | Pattinson | cast | Harry Potter and the Goblet of Fire Duncan | Wisbey | cast | Harry Potter and the Goblet of Fire Jack | Bagley | cast | Harry Potter and the Goblet of Fire Cl谷mence | Po谷sy | cast | Harry Potter and the Goblet of Fire Steve | Mackey | cast | Harry Potter and the Goblet of Fire Emma | Watson | cast | Harry Potter and the Goblet of Fire ....*/ -- 2) /* navn og fornavn p? alle personer som har deltatt i tv serien South Park. (person, filmparticipation, series) */ /* a) L?s oppgaven med INNER JOIN (21) */ SELECT DISTINCT p.personid, p.lastname, p.firstname, s.maintitle FROM Person p INNER JOIN Filmparticipation fp ON p.personid = fp.personid INNER JOIN Series s ON s.seriesid = fp.filmid WHERE s.maintitle LIKE 'South Park'; /* personid | lastname | firstname | maintitle ----------+-----------+-------------+------------ 1057256 | Graden | Brian | South Park 76515 | Berry | Adam | South Park 321512 | Brady | Pam | South Park 2033944 | Nixon | Trisha | South Park 2396648 | Rosenblum | John Frank | South Park 2200296 | Pimental | Nancy | South Park 1708279 | Liebling | Deborah | South Park 1823848 | McCulloch | Kyle | South Park 3111191 | White | David Niles | South Park 1874056 | Michaeli | Dani | South Park 367942 | Ganziano | Gian | South Park 731720 | Dornetto | Karey | South Park 2221112 | Polsky | Dave | South Park 225910 | Cruz | Brandon | South Park 1816421 | Stone | Matt | South Park 2681400 | Sterling | Dan | South Park 457475 | Kimmel | Jonathan | South Park 2748392 | Talbott | Tim | South Park 2236504 | Powell | Rachel | South Park 631542 | List | David | South Park 2355528 | Rivinoja | Erica | South Park (21 rows) */ /* b) L?s oppgaven med IMPLICIT JOIN (21) */ SELECT DISTINCT p.personid, p.lastname, p.firstname, s.maintitle FROM Person p, Filmparticipation fp, Series s WHERE s.seriesid = fp.filmid AND p.personid = fp.personid AND maintitle LIKE 'South Park'; /* SAMME SOM FORRIGE/* /* c) L?s oppgaven med NATURAL JOIN */ SELECT DISTINCT p.personid, p.lastname, p.firstname, s.maintitle FROM Person p NATURAL JOIN Filmparticipation fp NATURAL JOIN Series WHERE s.maintitle LIKE 'South Park'; /* GROUP BY OG AGGREGAT FUNKSJONER (Kan inneholde JOIN) */ -- 1) /* Antall filmer i tabellen film. (ingen group by) (692361) */ SELECT COUNT(filmid) AS antallFilmer FROM Film; /* antallfilmer -------------- 692361 (1 row) */ -- 2) /* Antall filmer med Star Wars i navnet. (ingen titler eller filmid skal skrives ut, trenger ikke GROUP BY) (129) */ SELECT COUNT(filmid) AS antallFilmer FROM film WHERE title LIKE '%Star Wars%'; /* antallfilmer -------------- 129 (1 row) /* -- 3) /* Antall filmer produsert i ?ret 2003(ingen group by) 21139 */ SELECT COUNT(*) AS antallFilmer2003 FROM film WHERE prodyear = 2003; /* antallfilmer2003 ------------------ 21139 (1 row) */ -- 4) /* Antall filmer produsert for hvert ?r og skriv ogs? ut ?ret. Sorter fra lavest til h?yest ?R, ikke antall. (125 rader) */ SELECT prodyear, COUNT(filmid) AS antallFilmer FROM film GROUP BY prodyear ORDER BY prodyear ASC; /* prodyear | antallfilmer -----------+-------------- 1888 | 2 1889 | 1 1890 | 3 1891 | 7 1892 | 12 1893 | 4 1894 | 82 1895 | 137 1896 | 496 1897 | 893 1898 | 1092 1899 | 942 ... (125 rows) /* -- 5) /* Den nyeste filmen i filmdatabasen, skriv ut tittel og prodyear(hint: h?yest ?r verdi) */ SELECT f.title, f.prodyear FROM film f WHERE prodyear = (SELECT MAX(prodyear) FROM film); /* title | prodyear ---------------+---------- Hart's Lagoon | 2011 (1 row) */ -- 6) /* Den eldste filmen i filmdatabasen(hint: lavest ?r verdi) 2 rader */ SELECT f.title, f.prodyear FROM film f WHERE prodyear = (SELECT MIN(prodyear) FROM film); /* title | prodyear -------------------------------+---------- Roundhay Garden Scene | 1888 Traffic Crossing Leeds Bridge | 1888 (2 rows) */ -- 7) /* Antall filmer for hver sjanger (28 rader) */ SELECT genre, COUNT(filmid) AS antallFilmerPerSjanger FROM Filmgenre GROUP BY genre; /* genre | antallfilmerpersjanger ---------------+------------------------ Drama | 114529 Horror | 11623 Comedy | 92070 Fantasy | 8903 Biography | 4426 Thriller | 16830 Film-Noir | 440 Talk-Show | 2093 Crime | 18420 Western | 10089 Music | 11757 Musical | 9248 War | 6009 Reality-TV | 2399 Sport | 4310 Game-Show | 2159 Short | 138289 History | 3463 News | 1642 Adventure | 15431 Sci-Fi | 8241 Adult | 33999 Action | 22370 Documentary | 69950 Romance | 21551 Animation | 24356 Mystery | 7966 Family | 19170 (28 rows) */ -- 8) /* Alle sjangere som har flere enn 30,000 filmer (5) */ SELECT genre, COUNT(filmid) AS antallFilmerPerSjanger FROM Filmgenre GROUP BY genre HAVING COUNT(filmid) > 30000; /* genre antallFilmerPerSjanger ---------------+------------------------ Drama | 114529 Comedy | 92070 Short | 138289 Adult | 33999 Documentary | 69950 (5 rows) */ -- 9) /* Navn(personen),Filmid, tittel og rolle p? filmer Tom Hanks har deltatt i bortsett fra de filmene rolletypen er 'cast'(mulitple joins) (31) */ SELECT p.firstname, p.lastname, f.filmid, f.title, fp.parttype FROM Person p NATURAL JOIN Filmparticipation fp NATURAL JOIN film f WHERE parttype <> 'cast' AND firstname = 'Tom' AND lastname = 'Hanks'; /* filmid | title | parttype ---------+------------------------------------------------+---------- 58436 | That Thing You Do! | director 2230709 | Vault of Horror I | director 3836917 | Wilderness of Monkeys, A | director 31130 | 1776 | producer 136087 | Ant Bully, The | producer 928154 | Boone's Lick | producer 249889 | Cast Away | producer 152370 | Charlie Wilson's War | producer 1351530 | City of Ember | producer 213393 | Connie and Carla | producer 51751 | Great Buck Howard, The | producer 3165626 | How Starbucks Saved My Life | producer 917697 | Magnificent Desolation: Walking on the Moon | producer 84211 | Mamma Mia! | producer 261668 | My Big Fat Greek Wedding | producer 4467239 | My Life in Ruins | producer 1284667 | Neil Young: Heart of Gold | producer 682100 | Polar Express, The | producer 1774691 | Risk Pool, The | producer 986980 | Starter for Ten | producer 833877 | Surfer Dude | producer 4467399 | Talk of the Town | producer 2209207 | They Marched Into Sunlight | producer 4467447 | Untitled Deep Throat Project | producer 1215281 | We Stand Alone Together | producer 1147656 | We're with the Band | producer 1421700 | Where the Wild Things Are | producer 3836917 | Wilderness of Monkeys, A | producer 3836917 | Wilderness of Monkeys, A | producer 917697 | Magnificent Desolation: Walking on the Moon 3D| writer 58436 | That Thing You Do! | writer (31 rows) */