/*
Alle oppgavene er skrevet for ? kj?res mot filmdatabasen!

SQL exercises are about reordering and structuring the data in a good way.
*/

------------------------------------------------------------------------

/* 
Finn tittel og produksjons?r for filmer laget i Gr?nland.
*/

SELECT f.title, f.prodyear
FROM film f INNER JOIN filmcountry fc ON f.filmid = fc.filmid
WHERE fc.country = 'Greenland';
--WHERE fc.country LIKE 'Greenland';

/*
Fremgangsm?te:
1. 	Hvor finner jeg informasjonen?
	~ Ingen av tabellene dekker all informasjonen alene.
	~ Title og Prodyear ligger i Film.
	~ Country ligger i Filmcountry.
	~ Film og Filmcountry deler Filmid attributten. 
2. 	SELECT'er p? de variablene oppgaven ber om: Title og Prodyear.
3. 	Siste er ? filtrere p? 'country'. 
	~ Siden dette ber meg om ? lete etter en spesifikk string s? 
	g?r det fint ? bruke '='.

*/

------------------------------------------------------------------------

/* 
Utvid listen over med filmer fra Saudi Arabia og Sudan. 

('Greenland', 'Saudi Arabia', 'Sudan')
*/

SELECT f.title, f.prodyear, fc.country
FROM film f INNER JOIN filmcountry fc ON f.filmid = fc.filmid
WHERE fc.country IN ('Greenland', 'Saudi Arabia', 'Sudan');

/*
Fremgangsm?te:
1. 	F?rste er s? klart ? kopiere koden fra forrige oppgave. 
2. 	Letteste m?ten ? endre sp?rringen til ? ta flere land p? er ? bruke 'IN'.
	~ Lage en liten liste med alle landene i og be PSQL sjekke om landet 
	  eksisterer inni listen.
*/

------------------------------------------------------------------------

/*
Hva er tittelen p? de 5 h?yest rangerte filmene?
Ta kun med filmer med over 5000 stemmer.
*/

SELECT f.title, fr.rank, fr.votes
FROM film f INNER JOIN filmrating fr ON f.filmid = fr.filmid
WHERE fr.votes > 5000
ORDER BY fr.rank DESC
LIMIT 5;

/*
Fremgangsm?te:
1. 	Hvor finner jeg informasjonen?
	~ Ingen av tabellene dekker all informasjonen alene.
	~ Filmrating har 'rank' og 'votes' som oppgaven ber om.
	~ Film har 'title' som oppgaven ber om.
	~ Begge har 'filmid' som en felles attributt. 
	~ JOIN'er tabellene p? den felles attributten. 
2. 	Hvilken informasjon trenger jeg? Tittel, Rank & Votes.
3. 	Da mangler jeg bare filteret p? stemmer. 
	~ Kan bruke WHERE siden det ikke er noen aggregatfunksjoner i bildet.
4. 	Siste er ? sortere bort alle som ikke er top 5.
	~ Sorterer p? 'rank' og legger p? LIMIT 5 :)
*/


------------------------------------------------------------------------

/*
Hvilket ?r ble det produsert flest filmer?
*/

SELECT p1.prodyear, count(p1.prodyear) AS occurences
FROM film p1
GROUP BY p1.prodyear
ORDER BY count(p1.prodyear) DESC
LIMIT 1;

/*
Fremgangsm?te:
1. 	Hvor finner jeg informasjonen? Film tabellen.
2. 	SELECT: Hvilken informasjon trenger jeg? Prodyear, Count.
3. 	OK, jeg skal ha mere enn bare aggregatfunksjonen i SELECT. Da m? jeg ha GROUP BY.
	~ GROUP BY p? attributten som ikke er i aggregatfunksjonen.
4. 	Da mangler jeg bare filtreringen. 
	~ Letteste er ? sortere p? COUNT() og bruke LIMIT.
	~ DESC for Descending. ASC er default verdi, og trenger aldri skrives.
	~ LIMIT 1 :)
*/

------------------------------------------------------------------------

/*
Hva er navnene til filmene Quentin Tarantino har regissert?
*/
-- her bruker vi || for konkatenering av strenge (samme som + i Java)
SELECT f.title, p.firstname || ' ' || p.lastname AS name, fp.parttype
FROM person p INNER JOIN filmparticipation fp ON p.personid = fp.personid
INNER JOIN film f ON f.filmid = fp.filmid
WHERE p.firstname = 'Quentin'
	AND p.lastname = 'Tarantino'
	AND fp.parttype = 'director';

/*
Fremgangsm?te:
1. Hvor finner jeg informasjonen?
	~ Ingen av tabellene dekker all informasjonen alene.
	~ Person har Firstname og Lastname.
	~ Film har Title.
	~ Filmparticipation har Parttype. 
	~ Film og Filmparticipation deler filmid.
	~ FFilmparticipation og Person deler personid.
2. M? hente Title fra Film, Navn(x2) fra Person og Parttype fra Filmparticipation.
3. WHERE filtrering p? 'director', 'Quentin' og 'Tarantino'. Case sensitive!
*/


------------------------------------------------------------------------

/*
Vis land og gjennomsnitts rank for filmer med over 10000 stemmer. Grupper p? land. 
Vis bare land med over 10 filmer.
*/

SELECT fc.country, avg(fr.rank)
FROM filmcountry fc INNER JOIN filmrating fr ON fc.filmid = fr.filmid
WHERE votes > 10000
GROUP BY fc.country
HAVING count(fr.filmid) > 10;

/*
Fremgangsm?te:
1. 	Hvor er informasjonen? 
	~ Filmrating har 'rank'.
	~ Filmcountry har 'country'.
	~ Begge deler har 'filmid' som kan JOIN'es p?.
2. 	SELECT setningen trenger da hvilket land, og gjennomsnittet p? rank'en. 
	~ Kan bruke AVG() funksjonen p? 'rank' for ? f? til gjennomsnittet.
3. 	Ser at 'country' er i SELECT setningen, men ikke i en aggregatfunksjon.
	~ Setter det i en GROUP BY setning.
4. 	Siste er ? sjekke at vi bare tar med land som har laget minst 10 filmer.
	~ Det blir et 'filter' med en COUNT() funksjon.
	~ Vi vet at aggregatfunksjoner ikke kan st? i WHERE, 
	  s? da setter vi det i HAVING.

*/


-- Hvis du skulle ?nske ? ta med "ANDRE" land. 
-- "Andre" land.

(SELECT fc.country, avg(fr.rank)
FROM filmcountry fc INNER JOIN filmrating fr ON fc.filmid = fr.filmid
WHERE votes > 10000
GROUP BY fc.country
HAVING count(fr.filmid) > 10)
UNION
(SELECT 'andre' as country, sum(total)/sum(cnt) as avg
	FROM (SELECT fc.country, count(fr.rank) as cnt, sum(fr.rank) as total
	FROM filmcountry fc INNER JOIN filmrating fr ON fc.filmid = fr.filmid
	WHERE votes > 10000
	GROUP BY fc.country
	HAVING count(fr.filmid) <= 10) i
);

------------------------------------------------------------------------

/*
Hvilke to fornavn forekommer mer enn 6000 ganger og akkurat like mange ganger?
*/

-- Med WITH (D)
WITH namecounts AS(
	SELECT p1.firstname, count(p1.firstname) AS occurences
	FROM person p1
	GROUP BY p1.firstname
	HAVING count(p1.firstname) > 6000
)
SELECT nc1.firstname, nc2.occurences
FROM namecounts nc1 INNER JOIN namecounts nc2 ON nc1.occurences = nc2.occurences
WHERE nc1.firstname <> nc2.firstname;

-- Uten WITH (M)
SELECT p.firstname, count(p.firstname)
FROM person p
GROUP BY p.firstname
HAVING count(*) = (
    SELECT occurances FROM
    (
        SELECT p.firstname, count(p.firstname) as occurances
        FROM person p
        GROUP BY p.firstname
        HAVING count(p.firstname) > 6000
    ) a
    GROUP BY occurances
    HAVING count(*) > 1
);



/*
Fremgangsm?te (/m WITH):
1. 	Ser at oppgaven vil kreve indre select setninger. 
	~ Velger ? dele det opp ved ? hente fra en mindre liste.
2. 	Navn som forekommer mer enn 6000 ganger, en COUNT() funksjon i HAVING.
3. 	FROM: Hvor skal informasjonen hentes fra? Person tabellen. 
4. 	SELECT: Hvilken informasjon trenger jeg? Firstname, Count.
5. 	OK, jeg skal ha mere enn bare aggregatfunksjonen i SELECT. Da m? jeg ha GROUP BY.
6. 	Tester og setter alt inn i en WITH for lettere bruk.
	~ F?r ut alle navn over 6000, med antall ganger. 

7. 	S? m? jeg filtrere ut de som ikke har like mange forekomster. 
	~ Joiner denne nye relasjonen med seg selv med en INNER JOIN p? 'occurences'.
	~ SELECT'er p? samme kolonnene. 
	~ Ser at Peter og Paul med 9151 forekommer to ganger. 
8. 	Filtrerer bort ekstraene ved ? si at de kan ikke ha samme 'firstname'.
	* Kan gj?res i WHERE og AND.
*/

------------------------------------------------------------------------

/* 
Flere TV-serier har delt f?rsteplass for h?yest rangerte serie.
Hva er navnet p? TV-seriene som deler den h?yeste rangeringen,
blant serier med over 1000 stemmer?
*/

SELECT s.maintitle, fr.rank
FROM series s INNER JOIN filmrating fr ON s.seriesid = fr.filmid
WHERE fr.votes > 1000
AND fr.rank = 
	(SELECT max(fr.rank)
	 FROM series s INNER JOIN filmrating fr ON s.seriesid = fr.filmid
	 WHERE fr.votes > 1000);

/*
Fremgangsm?te: 
1. 	Hvor ligger informasjonen? 
	~ Series har 'Maintitle'.
	~ Filmrating har 'Rank' og 'Votes'. 
	~ Series of Filmrating har ingen felles kolonner, men hvis du ser p?
	  Series sin 'seriesid' og filmrating sin 'filmid' s? er de ekvivalente.
	~ NATURAL JOIN hadde g?tt d?rlig her.
2. 	SELECT'er p? maintitle og rank (den siste for ? dobbeltsjekke).
3. 	Filtrerer ut alle som har mindre enn 1000 votes i WHERE setningen.
4. 	Neste er ? finne ut hva som er den beste rangeringen. 
	~ Letteste er ? bruke MAX() funksjonen i en subsp?rring.
	~ Kopierer samme sp?rringen, og bare erstatter alt som er i SELECT med MAX().
	~ Setter det inn i WHERE delen av forrige sp?rring.  
*/

------------------------------------------------------------------------

/*
Hvilke filmer som Quentin Tarantino har regisert har h?yere rating enn
Michael Bay's film med h?yest rating?
*/

SELECT f.title, fr.rank
FROM person p INNER JOIN filmparticipation fp ON p.personid = fp.personid
	INNER JOIN filmrating fr ON fr.filmid = fp.filmid
	INNER JOIN film f ON fr.filmid = f.filmid
WHERE p.firstname = 'Quentin'
	AND p.lastname = 'Tarantino'
	AND fp.parttype = 'director'
	AND fr.rank > 
	(SELECT max(fr.rank)
	FROM person p INNER JOIN filmparticipation fp ON p.personid = fp.personid
		INNER JOIN filmrating fr ON fr.filmid = fp.filmid
		INNER JOIN film f ON fr.filmid = f.filmid
	WHERE p.firstname = 'Michael'
		AND p.lastname = 'Bay'
		AND fp.parttype = 'director');

/*
Fremgangsm?te: 
1. 	Hvor finner jeg infoen?
	~ Her ligger ting ganske spredd.
	~ Person har alt med person ? gj?re.
	~ Filmrating har rank.
	~ Film har titlene.
	~ Filmparticipation har rolletypen.
	~ Linkene er litt ville-vesten her, men rekkef?lgen du joiner p? er ikke viktig.
2. 	SELECT p? title og rank. Husk ? nevne hvilken tabell du tar ting fra.
3. 	WHERE! Samme som over med Quentin, Tarantino & director.
	~ Siste er ? fjerne alt som er lavere en Michael Bay sin beste film.
	~ Tenker litt som forrige oppgave. 
	~ Bruker samme sp?rring i en indre select setning, med MAX().
	~ Copy/Paste, endre SELECT til max, og first/lastname til M-Bay.
	~ 'Rank' st?rre enn M-Bay setningen.
*/