IN2090-ukesoppgaver: Uke 9
Avansert SQL
1)
Hvilke verdier forekommer i attributtet filmtype i relasjonen filmitem? Lag en oversikt over filmtypene og hvor mange filmer innen hver type (7).
L?sningsforslag
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)
Skriv ut serietittel, produksjons?r og antall episoder for de 15 eldste TV-seriene i filmdatabasen (sortert stigende etter produksjons?r).
L?sningsforslag
SELECT s.seriesid, maintitle, firstprodyear, count(e.episodeid)
FROM series AS s
INNER JOIN episode e ON s.seriesid=e.seriesid
GROUP BY s.seriesid, maintitle, firstprodyear
ORDER BY firstprodyear ASC
LIMIT 15;
/*
seriesid | maintitle | firstprodyear | count
----------+--------------------------+---------------+-------
685463 | Picture Page | 1936 | 3
14320602 | Starlight | 1936 | 9
14661946 | Theatre Parade | 1936 | 4
509297 | BBC TV Cricket | 1938 | 2
4573368 | Ann and Harold | 1938 | 3
370184 | Percy Ponsonby | 1939 | 1
1724755 | Piste aux étoiles, La | 1945 | 1
2151126 | Kaleidoscope | 1946 | 6
5203976 | For the Children | 1946 | 2
8710741 | Hour Glass | 1946 | 2
58347 | Kraft Television Theatre | 1947 | 507
79211 | Pantomime Quiz | 1947 | 55
4202437 | Mainly for Women | 1947 | 2
9193793 | Meet the Press | 1947 | 142
2763 | Actor's Studio | 1948 | 63
(15 rows)
*/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. (12 eller 26)
L?sningsforslag
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
...
Othello | 32
Romeo and Juliet | 31
Alone | 31
Escape | 31
(26 rows)
*/
-- Bare kinofilmer (12 rader)
SELECT title, COUNT(*) AS ant
FROM film INNER JOIN filmitem ON film.filmid = filmitem.filmid
WHERE filmitem.filmtype = 'C'
GROUP BY title
HAVING COUNT(*) > 30
ORDER BY ant DESC;
/*
title | ant
---------------------+-----
Popular Science | 45
Love | 42
Mother | 41
Hamlet | 39
Stranger, The | 37
Desire | 37
Unusual Occupations | 35
Trap, The | 34
Carmen | 34
Home | 33
Jealousy | 31
Destiny | 31
(12 rows)
*/4)
Finn de “Pirates of the Caribbean”-filmene som er med i flere enn 3 genre (4)
L?sningsforslag
SELECT title, count(*) as antall_genre
FROM film AS f JOIN filmgenre AS fg USING (filmid)
WHERE f.title LIKE 'Pirates of the Caribbean%'
GROUP BY f.filmid, title
HAVING count(*) > 3;
/*
title | antall_genre
--------------------------------------------------------+--------------
Pirates of the Caribbean: Dead Man's Chest | 4
Pirates of the Caribbean: The Curse of the Black Pearl | 4
Pirates of the Caribbean: At World's End | 4
Pirates of the Caribbean: The Legend of Jack Sparrow | 5
(4 rows)
*/5)
Hvilke verdier (fornavn) forekommer hyppigst i firstname-attributtet i tabellen Person? Finn alle fornavn, og sorter fallende etter antall forekomster. Ikke tell med forekomster der fornavn-verdien er tom. Begrens gjerne antall rader. (176029 rader, 16108 for flest fornavn)
L?sningsforslag
SELECT p.firstname, COUNT(*) AS sammeFornavn
FROM Person p
WHERE p.firstname != '' -- fjerner ogs? NULLs
GROUP BY p.firstname
ORDER BY count(*) DESC
LIMIT 20;
/* firstname | sammefornavn
---------------------------------------------------+--------------
John | 16108
David | 15009
Michael | 14184
Robert | 10020
.....
*/6)
Finn filmene som er med i flest genrer: Skriv ut filmid, tittel og antall genre, og sorter fallende etter antall genre. Du kan begrense resultatet til 25 rader.
L?sningsforslag
SELECT filmid, title, count(*)
FROM film JOIN filmgenre USING (filmid)
GROUP BY filmid, title
ORDER BY count(*) DESC
LIMIT 25;
/*
filmid | title | count
---------+------------------------------------------+-------
694579 | Pokémon Heroes | 9
434615 | Utopia's Redemption | 9
985057 | Matilda | 9
2060042 | Elder Scrolls III: Morrowind, The | 8
1554123 | Rupan sansei: Walther P38 | 8
1853731 | Vampires, Les | 8
1299610 | Chiquititas: Rincón de luz | 8
...
(25 rows)
*/7)
Lag en oversikt over regiss?rer som har regissert mer enn 5 norske filmer. (60)
L?sningsforslag
SELECT lastname || ', ' || firstname AS navn
FROM Filmcountry
JOIN Film USING (filmid)
JOIN Filmparticipation USING (filmid)
JOIN Person USING (personid)
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)
*/8)
Skriv ut serieid, serietittel og produksjons?r for TV-serier, sortert fallende etter produksjons?r. Begrens resultatet til 50 filmer. Tips: Ikke ta med serier der produksjons?ret er null.
L?sningsforslag
SELECT s.seriesid, maintitle, firstprodyear
FROM series AS s
WHERE firstprodyear IS NOT NULL
ORDER BY firstprodyear DESC
LIMIT 50;
/*
seriesid | maintitle | firstprodyear
----------+------------------------------+---------------
87425 | Saka no ue no kumo | 2009
2177832 | Last Horseman, The | 2009
78907 | Pacific War, The | 2009
7980888 | Pacific, The | 2009
7332328 | Untitled Star Wars TV Series | 2009
...
*/9)
Hva er gjennomsnittlig score (rank) for filmer med over 100 000 stemmer (votes)?
L?sningsforslag
SELECT avg(rank)
FROM filmrating
WHERE votes > 100000;
/*
avg
-----------------
8.4270270708445
(1 row)
*/10)
Hvilke filmer (tittel og score) med over 100 000 stemmer har en h?yere score enn snittet blant filmer med over 100 000 stemmer (subsp?rring!) (20).
L?sningsforslag
SELECT title, rank
FROM film INNER JOIN filmrating ON film.filmid = filmrating.filmid
WHERE votes > 100000 AND rank >= (
SELECT avg(rank)
FROM filmrating
WHERE votes > 100000
);
/*
title | rank
----------------------------------------------------+------
Silence of the Lambs, The | 8.6
Matrix, The | 8.6
American Beauty | 8.5
Goodfellas | 8.7
Lord of the Rings: The Fellowship of the Ring, The | 8.7
Lord of the Rings: The Return of the King, The | 8.8
Lord of the Rings: The Two Towers, The | 8.7
Pulp Fiction | 8.8
Memento | 8.6
Se7en | 8.5
Godfather: Part II, The | 9
Fight Club | 8.6
Raiders of the Lost Ark | 8.7
Godfather, The | 9.1
Star Wars | 8.8
Usual Suspects, The | 8.7
Star Wars: Episode V - The Empire Strikes Back | 8.8
One Flew Over the Cuckoo's Nest | 8.8
Shawshank Redemption, The | 9.2
Schindler's List | 8.8
(20 rows)
*/11)
Hvilke 100 verdier (fornavn) forekomer hyppigst i firstname-attributtet i tabellen Person?
L?sningsforslag
SELECT firstname, COUNT(*) AS sammeFornavn
FROM Person
WHERE firstname != ''
GROUP BY firstname
ORDER BY sammeFornavn DESC
LIMIT 100;
/*
firstname | sammefornavn
-------------+--------------
John | 16108
David | 15009
Michael | 14184
Robert | 10020
Paul | 9151
Peter | 9151
...
*/12)
Hvilke to fornavn forekommer mer enn 6000 ganger og akkurat like mange ganger? (Paul og Peter, vanskelig!)
L?sningsforslag
WITH
ant_fornavn AS (
SELECT firstname AS fornavn, COUNT(*) AS antall
FROM Person
GROUP BY firstname
HAVING COUNT(*) > 5999
)
SELECT A.fornavn, A.antall, B.fornavn, B.antall
FROM ant_fornavn AS A INNER JOIN ant_fornavn AS B
ON A.antall = B.antall AND A.fornavn != B.fornavn;
-- eller
SELECT A.fornavn, A.antall, B.fornavn, B.antall
FROM (
SELECT firstname AS fornavn, COUNT(*) AS antall
FROM Person
GROUP BY firstname
HAVING COUNT(*) > 5999) AS A
INNER JOIN (
SELECT firstname AS fornavn,