/* 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) */ /* Diverse oppgaver mot film databasen*/ -- 1) /* Hvilke verdier forekommer i attributtet filmtype i relasjonen filmitem? (7) */ SELECT DISTINCT filmtype FROM filmitem; -- eller SELECT filmtype FROM filmitem GROUP BY filmtype; /* filmtype ---------- VG TV C E V TVS mini (7 rows) */ /* Lag en oversikt over filmtypene og hvor mange filmer innen hver type. (7)*/ SELECT filmtype, COUNT(*) ant FROM filmitem GROUP BY filmtype ORDER BY ant DESC; /* filmtype | ant ----------+-------- C | 549782 E | 446402 TV | 74725 V | 61050 TVS | 52098 VG | 6804 mini | 6769 (7 rows) */ -- 2) /* Om attributtet filmdescr i Filmdescription st?r det at informasjonen som fins, b?rer noe preg av ? v?re lite enhetlig og relativt tilfeldig. Unders?k hvilke verdier som finne si dette attributtet. (Hint det er 94 forskjellige verdier). */ --To be done. -- 3) /* Mange titler har v?rt brukt i flere filmer. Skriv ut en oversikt over titler som har v?rt brukt i mer enn 30 filmer. Bak hver tittel skriv antall ganger den er brukt. Ordne linjene med hyppigst forekommende tittel f?rst. (26 eller 18) */ SELECT title, COUNT(*) AS ant FROM film GROUP BY title HAVING COUNT(*) > 30 ORDER BY ant DESC; /* title | ant ------------------------------+----- Hamlet | 67 Carmen | 55 Eurovision Song Contest, The | 52 Macbeth | 46 Popular Science | 45 (26 rows) */ -- eller om du velger ? gruppere p? filmtype i tillegg. Merk at dette vil gi forskjellige resultat, bare 18 rader i denne. SELECT title, filmtype, COUNT(*) AS ant FROM film NATURAL JOIN filmitem GROUP BY title, filmtype HAVING COUNT(*) > 30 ORDER BY ant DESC; /* title | filmtype | ant ------------------------------+----------+----- Eurovision Song Contest, The | TV | 52 Popular Science | C | 45 Melodi grand prix | TV | 44 Nationaal songfestival | TV | 43 Love | C | 42 ... (18 rows) -- 4) /* Hvilken verdi (fornavn) forekomer hyppigst i firstname-attributtet i tabellen Person? (176030 rader, 40340 eller 16108 for flest fornavn) */ SELECT firstname, COUNT(*) AS sammeFornavn FROM Person GROUP BY firstname ORDER BY sammeFornavn DESC; /* firstname | sammefornavn ---------------------------------------------------+-------------- | 40340 John | 16108 David | 15009 Michael | 14184 Robert | 10020 */ -- ?nsker du ? fjerne de som har tom linje som fornavn s? blir det: SELECT firstname, COUNT(*) AS sammeFornavn FROM Person WHERE firstname <> '' GROUP BY firstname ORDER BY sammeFornavn DESC; firstname | sammefornavn ---------------------------------------------------+-------------- John | 16108 David | 15009 Michael | 14184 Robert | 10020 -- 5) /* Hvilke to fornavn forekommer mer enn 6000 ganger og akkurat like mange ganger? (2 rader) */ -- Flere m?ter ? l?se p? SELECT A.fornavn, A.antall, B.fornavn, B.antall FROM (SELECT firstname fornavn, COUNT(*) antall FROM Person GROUP BY firstname HAVING COUNT(*) > 5999) AS A INNER JOIN (SELECT firstname fornavn, COUNT(*) antall FROM Person GROUP BY firstname HAVING COUNT(*) > 5999) AS B ON A.antall = B.antall AND A.fornavn <> B.fornavn ; /* fornavn | antall | fornavn | antall ---------+--------+---------+-------- Peter | 9151 | Paul | 9151 Paul | 9151 | Peter | 9151 */ -- Eller, som gir akkurat samme resultat SELECT names1.firstname, names1.occurences FROM (SELECT p2.firstname, COUNT(p2.firstname) AS occurences FROM Person p2 GROUP BY p2.firstname HAVING COUNT(p2.firstname) > 6000) AS names1, (SELECT p2.firstname, COUNT(p2.firstname) AS occurences FROM person p2 GROUP BY p2.firstname HAVING COUNT(p2.firstname) > 6000) AS names2 WHERE names1.occurences = names2.occurences AND names1.firstname != names2.firstname; -- 6) /* Hvor mange filmer i filmdatabasen er norske? (1 rad, 2245 verdi) */ SELECT COUNT(*) AS antallNorskeFilmer FROM Filmcountry WHERE country = 'Norway'; /* antallnorskefilmer -------------------- 2245 (1 row) */ -- 7) /* Skriv ut tittel og regiss?r for norske filmer som er produsert f?r 1960. (269) */ SELECT title AS tittel, lastname, firstname FROM Filmcountry NATURAL JOIN Film NATURAL JOIN Filmparticipation NATURAL JOIN Person WHERE country = 'Norway' AND parttype = 'director' AND prodyear < 1960; /* tittel | lastname | firstname ------------------------------------------------------------------+--------------------+---------------- Portrettet | Aabel | Per Portrettet | Skaugen | Borgwall Jeppe p? bjerget | Aabel | Per Jeppe p? bjerget | Ivarson | Harry Storfolk og sm?folk | Ibsen | Tancred Kvinnan bakom allt | Faustman | Hampe ... (269 rows) */ -- 8) /* Hvor mange filmer har Tancred Ibsen regissert? (24) */ SELECT COUNT (DISTINCT filmid) AS tancredIbsenFilmer FROM Filmparticipation NATURAL JOIN Person WHERE lastname = 'Ibsen' AND firstname = 'Tancred' AND parttype = 'director'; /* tancredibsenfilmer -------------------- 24 (1 row) */ -- 9) /* Lag en oversikt over regiss?rer som har regissert mer enn 5 norske filmer. (60) */ SELECT lastname || ', ' || firstname AS navn FROM Filmcountry NATURAL JOIN Film NATURAL JOIN Filmparticipation NATURAL JOIN Person WHERE country = 'Norway' AND parttype = 'director' GROUP BY lastname, firstname HAVING COUNT(*) > 5; /* navn ----------------------------- Andersen, Knut Sand?, Toralf Holst, Marius Heggedal, Jon Breien, Anja Venner?d, Petter ... (60 rows) */ -- 10) /* Lag en oversikt (filmtittel) over norske filmer med mer enn ¨¦n regiss?r. */ SELECT filmid, title FROM Filmcountry NATURAL JOIN Film NATURAL JOIN Filmparticipation NATURAL JOIN Person WHERE country = 'Norway' AND parttype = 'director' GROUP BY filmid, title HAVING COUNT(*) > 1; -- 11) /* Regiss?rer som har regissert alene mer enn 5 norske filmer. (49) */ SELECT lastname || ', ' || firstname AS navn, COUNT(*) AS antall FROM Filmcountry NATURAL JOIN Film NATURAL JOIN Filmparticipation NATURAL JOIN Person WHERE country = 'Norway' AND parttype = 'director' AND filmid NOT IN ( SELECT filmid FROM Filmcountry NATURAL JOIN Film NATURAL JOIN Filmparticipation NATURAL JOIN Person WHERE country = 'Norway' AND parttype = 'director' GROUP BY filmid, title HAVING COUNT(*) > 1 ) GROUP BY lastname, firstname HAVING COUNT(*) > 5 ORDER BY antall DESC; /* navn | antall -----------------------------+-------- Bronken, Per | 23 M¨¹ller, Nils R. | 22 Bohwim, Knut | 18 Skouen, Arne | 16 Caprino, Ivo | 15 */ -- uten den indre selectsetningen SELECT lastname || ', ' || firstname AS navn, COUNT(*) AS antall FROM Filmcountry NATURAL JOIN Film NATURAL JOIN Filmparticipation NATURAL JOIN Person WHERE country = 'Norway' AND parttype = 'director' GROUP BY lastname, firstname HAVING COUNT(*) > 5 ORDER BY antall DESC; /* Skriv selectsetninger som skriver ut en tabell med... */ /* * F?lgende tabeller er benyttet i oppgavene nedenfor: * * film(filmid, title, prodyear) * filmitem(filmid, filmtype) * filmparticipation(partid, personid, filmid, parttype) * person(personid, lastname, firstname, gender) * series(seriesid, maintitle, firstprodyear) **/ -- 1) /* fullstendig tittel og produksjons?r for alle filmer i kampsport -og spenningsfilmserien Rush Hour (1). */ 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) */ -- 2) /* tittel, produksjons?r og filmtype for alle kinofilmer som ble produsert i ?ret 1893 (1). */ SELECT f.title, f.prodyear, fi.filmtype FROM film f NATURAL JOIN filmitem fi WHERE f.prodyear = 1893; /* title | prodyear | filmtype ---------------------+----------+---------- Blacksmith Scene | 1893 | C Blacksmith Scene #1 | 1893 | C Blacksmithing Scene | 1893 | C Horse Shoeing | 1893 | C (4 rows) */ -- 3) /* navn p? alle skuespillere (cast) i filmen Baile Perfumado (2). */ SELECT DISTINCT p.firstname || ' ' || p.lastname AS name FROM film f NATURAL JOIN filmparticipation fp NATURAL JOIN person p WHERE fp.parttype LIKE 'cast' AND f.title LIKE 'Baile Perfumado'; /* name ------------------------- Chico D¨ªaz Roger de Renor Germano Haiut Luiz Carlos Vasconcelos John Donovan Cl¨¢udio Mamberti Daniela Mastroianni Duda Mamberti Rut¨ªlio Oliveira Giovanna Gold Geninha da Rosa Borges Manoel Constantino Aramis Trindade Jofre Soares (14 rows) */ -- 4) /* tittel og produksjons?r for alle filmene som Ingmar Bergman har v?rt regiss?r (director) for. Sorter tuplene kronologisk etter produksjons?r (2). */ SELECT f.title, f.prodyear FROM film f NATURAL JOIN filmparticipation fp NATURAL JOIN person p WHERE p.lastname LIKE 'Bergman' AND p.firstname LIKE 'Ingmar' AND fp.parttype LIKE 'director' ORDER BY f.prodyear DESC; /* title | prodyear -----------------------------------------+---------- Saraband | 2003 Bildmakarna | 2000 Larmar och g?r sig till | 1997 --- Skepp till India land | 1947 Det regnar p? v?r k?rlek | 1946 Kris | 1946 (62 rows) */ -- 5) /* produksjons?r for f?rste og siste film Ingmar Bergman regisserte (3). */ SELECT MIN(f.prodyear) AS first, MAX(f.prodyear) AS last FROM film f NATURAL JOIN filmparticipation fp NATURAL JOIN person p WHERE fp.parttype LIKE 'director' AND p.lastname LIKE 'Bergman' AND p.firstname LIKE 'Ingmar'; /* first | last -------+------ 1946 | 2003 (1 row) */ --6) /* ?r og antall tv-serier som ble produsert f?rste gang i 2008 og 2009. En linje for hvert ?r (3). */ SELECT firstprodyear, COUNT(*) FROM series WHERE firstprodyear IN(2008, 2009) GROUP BY firstprodyear; /* firstprodyear | count ---------------+------- 2008 | 70 2009 | 6 (2 rows) */ -- 7) /* tittel og produksjons?r for de filmene hvor mer enn 300 personer har deltatt, uansett hvilken funksjon de har hatt (3). */ SELECT f.title, f.prodyear, COUNT(*) AS participants FROM film f NATURAL JOIN filmparticipation fp GROUP BY f.title, f.prodyear HAVING COUNT(DISTINCT fp.personid) > 300 ORDER BY participants DESC; -- Antatt her at vi ikke trenger ? filtrere bort VG delen av filmtype. -- (GTA: San Andreas er jo tross alt et videospill og ikke en film) /* title | prodyear | participants ---------------------------------+----------+-------------- Around the World in Eighty Days | 1956 | 1312 Stuck on You | 2003 | 452 50 y m¨¢s | 2005 | 382 Ten Commandments, The | 1956 | 381 Malcolm X | 1992 | 356 Grand Theft Auto: San Andreas | 2004 | 348 Producers, The | 2005 | 336 40 ans de la 2, Les | 2004 | 317 3000 sc¨¦narios contre un virus | 1994 | 314 Televisi¨®n cumple contigo, La | 2006 | 308 Live 8 | 2005 | 308 (11 rows) */ -- 8) /* oversikt over regiss?rer som har regissert kinofilmer over et stort tidsspenn. I tillegg til navn, ta med antall kinofilmer og f?rste og siste ?r (prodyear) personen hadde regi. Skriv ut alle som har et tidsintervall p? mer enn 49 ?r mellom f?rste og siste film og sorter dem etter lengden p? dette tidsintervallet. de lengste f?rst. (5). */ SELECT p.firstname || ' ' || p.lastname AS name, COUNT(*) AS produced, MIN(f.prodyear) AS first, MAX(f.prodyear) AS last, MAX(f.prodyear) - MIN(f.prodyear) AS periode FROM film f NATURAL JOIN filmparticipation fp NATURAL JOIN person p WHERE fp.parttype LIKE 'director' GROUP BY p.personid, name HAVING (MAX(f.prodyear) - MIN(f.prodyear) > 49) ORDER BY periode DESC; -- Trenger ? gruppere p? personid i tillegg siden to regiss?rer kan ha samme navn. /* Svar */ /* name | produced | first | last | periode ------------------------------+----------+-------+------+--------- Wladyslaw Starewicz | 35 | 1910 | 2003 | 93 Raoul Walsh | 139 | 1912 | 2003 | 91 Yakov Poselsky | 4 | 1917 | 1996 | 79 --- Ruy Guerra | 16 | 1954 | 2004 | 50 Robert F. Hill | 115 | 1916 | 1966 | 50 Michael Anderson | 39 | 1949 | 1999 | 50 (230 rows) */