------------------------------------------------------------------
/* 
Finn antall filmer i tabellen FilmCountry som ikke finnes i tabellen film, gruppert p? land.
*/ 


-- Finn alle filmer i tabellen filmCountry fordelt p? land.

-- Vi kan starte med ? finne antall filmer i tabellen FilmCountry.
SELECT fc.country, COUNT(*)
FROM Filmcountry fc
GROUP BY fc.country
ORDER BY COUNT(*) DESC;

-- Vi finner snittet, filmidene FilmCountry og Film deler.
SELECT fc.country, COUNT(*)
FROM Film f INNER JOIN Filmcountry fc ON f.filmid = fc.filmid
GROUP BY fc.country
ORDER BY COUNT(*) DESC;

-- Setter STEG 1 og 2 Sammen. STEG 1 blir den ytre sp?rringen og STEG 2 blir den indre sp?rringen 
-- som er det vi ?nsker ? fjerne fra resultatet.

-- Antallfilmer i tabellen FilmCountry som ikke er i film. 
SELECT country, COUNT(*)
FROM Filmcountry
WHERE NOT EXISTS (SELECT f.filmid
                 FROM film f
                 WHERE Filmcountry.filmid = f.filmid)
GROUP BY country
ORDER BY COUNT(*) DESC;

-- Kan ogs? l?ses med NOT IN, men da m? vi spefisere attributtet som ikke skal v?re der.

SELECT country, COUNT(*) 
FROM Filmcountry fc
WHERE fc.filmid NOT IN 
(
SELECT f.filmid
FROM film f
WHERE fc.filmid = f.filmid
)
GROUP BY country
ORDER BY COUNT(*) DESC;

/*
Fremgangsm?te: 
1. 	Hvor finner jeg informasjonen?
	~ country ligger i FilmCountry
	~ filmid ligger i b?de Film og FilmCountry
2. 	SELECT p? country, og count().
	~ Har en variabel i SELECT, samtidig som count() som er en aggregatfunksjon.
	~ M? bruke GROUP BY p? variabelen.
3.  Deler sp?rringen i 2 deler. Vi finner f?rst alle filmer i filmcountry og teller de opp.
    S? finner vi alle filmer som er i snittet mellom Film og Filmcountry. Deretter til slutt
	bruker vi NOT EXISTS eller IN til ? si at vi ikke skal ha filmene som er i snittet, og 
	vi sitter da igjen med alle filmene som finnes i FilmCountry men ikke er i film.
*/

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

/*
Finn antall filmer som har h?yere rating enn gjennomsnitts rating. Skriv ut tittel, 
filmid og ratingen. Sorter fra lavest til h?yest rating.
*/ 

-- Gjennomsnittsscoren i fra alle filmer, slik vi kan Bekrefte at dataen er riktig

-- Vi kan f?rste finne ut, hva er faktisk gjennomsnittsscoren? Vi m? joine med Film siden vi skal ha 
-- tittelen i hovedsp?rringen. Mengden av filmer i Film og Filmrating er ikke n?dvendigvis like, 
-- s? om vi bare finner AVG(rank) i Filmrating kan vi f? en annerledes verdi
SELECT AVG(rank)
FROM Film f INNER JOIN Filmrating fr ON f.filmid = fr.filmid;

-- Vi kan ogs? se gjennomsnittsverdien om vi bare har Filmrating med for ? sammenligne
SELECT AVG(rank)
FROM Filmrating fr;

-- Neste steg er ? Utvide sp?rringen med tittel, filmid og seleksjonsbetingelsen.

-- Med ALL

SELECT f.filmid, f.title, rank
FROM Filmrating fr INNER JOIN Film f ON fr.filmid = f.filmid
WHERE fr.rank > ALL 
(
SELECT AVG(rank)
FROM filmrating fr INNER JOIN film f ON f.filmid = fr.filmid
)
ORDER BY fr.rank ASC;

-- Men man kan jo like godt bare ha en SELECT AVG som en indresp?rring som er like enkelt.

SELECT f.title, f.filmid, fr.rank
FROM Film f INNER JOIN Filmrating fr ON f.filmid = fr.filmid
WHERE fr.rank > 
(
SELECT AVG(rank)
FROM Film f INNER JOIN Filmrating fr ON f.filmid = fr.filmid
)
ORDER BY rank ASC;

/*
Fremgangsm?te: 
1. 	Hvor finner jeg informasjonen?
	~ title og filmid finner vi i Film
	~ rank
2. 	SELECT p? country, count().
	~ Har en variabel i SELECT, samtidig som count() som er en aggregatfunksjon.
	~ M? bruke GROUP BY ettersom vi vil ha ut land navnet. 
	~ Vi trenger ogs? en indre selekt i WHERE betingelsen som finner AVG(rank)
3.  AVG er i denne sp?rringen en betingelse, vi vil bare ha de filmene som har h?yere enn AVG
	Derfor er dette en betingelse, og da m? dermed dette v?re i WHERE setningen. Vi vet allerede 
	at aggregatfunksjoner kun er tillatt i SELECT elller HAVING, s? her m? vi ha en indre select.
4. Vi starter med ? bekrefte hva gjennomsnittscoren ifra alle filmer er. Utvider sp?rringen med tittel, 
	filmid og seleksjonsbetingelse. Indre selekt setning enten ved ? bruke ALL er indre SELECT AVG.
*/
-------------------------------------------------------------------

/*
Hvilke regiss?rer har regissert mer enn 100 filmer, hvor i samtlige filmer den personen 
ikke har hatt noen andre roller.
*/ 

-- Alle regiss?rer som har regissert mer enn 100 filmer

SELECT p.personid, p.firstname, p.lastname, COUNT(*) AS antFilmer
FROM Person p INNER JOIN FilmParticipation fp ON fp.personid = p.personid
WHERE parttype = 'director'
GROUP BY p.personid, p.firstname, p.lastname
HAVING COUNT(*) > 100
ORDER BY COUNT(*) DESC;

-- Alle personer som ikke har rollen regiss?r
SELECT personid, parttype
FROM FilmParticipation
WHERE parttype <> 'director';

-- Hvordan sette de sammen? Her hadde det v?rt fint om vi kunne laget et view og sette 
-- sammen de to sp?rringene til en og samme.

CREATE VIEW MerEnn100 AS(
SELECT p.personid, p.firstname, p.lastname, COUNT(*) AS antFilmer
FROM Person p INNER JOIN FilmParticipation fp ON fp.personid = p.personid
WHERE parttype = 'director'
GROUP BY p.personid, p.firstname, p.lastname
HAVING COUNT(*) > 100);

-- View kan ikke brukes i databasen, s? vi m? heller bruk with

WITH MerEnn100 AS (
	SELECT p.personid, p.firstname, p.lastname, COUNT(*) AS antFilmer
	FROM Person p INNER JOIN FilmParticipation fp ON fp.personid = p.personid
	WHERE parttype = 'director'
	GROUP BY p.personid, p.firstname, p.lastname
	HAVING COUNT(*) > 100);

-- Alle regiss?rer som har regissert mer enn 100 filmer og ikke har hatt en annen rolle enn 
-- regiss?r i noen av filmene.

WITH MerEnn100 AS (SELECT personid, firstname, lastname, count(*) AS antFilmer
	  FROM filmparticipation NATURAL JOIN person
	  WHERE parttype = 'director'
	  GROUP BY personid, firstname, lastname
	  HAVING count(*) >= 100)
SELECT personid, firstname, lastname, antFilmer
FROM MerEnn100
WHERE MerEnn100.personid NOT IN (SELECT personid
								FROM MerEnn100 NATURAL JOIN Filmparticipation
								WHERE parttype <> 'director')
ORDER BY antFilmer ASC;

/*
Fremgangsm?te: 
1. 	Hvor finner jeg informasjonen?
	~ personid, firstname, lastname ligger i Person tabellen.
	~ filmid, parttype ifra Filmparticipation
2. 	SELECT p? personid, firstname, lastname og count() for ? finne antall filmer.
	~ Har en variabel i SELECT, samtidig som count() som er en aggregatfunksjon.
	~ M? bruke GROUP BY p? variabelen.
3. 	Deler oppgaven i to deler. Vi finner alle regiss?rer som har regissert mer enn 100 filmer. 
	Etterp? finner vi alle personider som har hatt andre roller enn regiss?r. Vi sjekker etterp? om de 
	regiss?rene som var i den f?rste listen er i den andre listen. Har de hatt en annen rolle, 
	s? fjerner vi den forekomsten.
*/

/*
1. 	Hvor finner vi informasjonen? 
- 	Informasjon om personer og deltagelse i filmer f?r vi ifra Person og Filmparticipation. 
- 	Trenger vi noen andre tabeller?
2. 	Hva skal skrives ut? Gir mening ? ta med personid, firstname, lastname, og antall filmer. 
-  	S? her trenger i vi i tillegg en count.
3. 	Kan sp?rringen l?ses uten subsp?rringer? Nei. Dette er et typisk eksempel p? en subsp?rringsoppgave, 
- 	s? vi m? dele den opp i 2 deler.
	For eksempel kan vi f?rst finne alle regiss?rer som har mer enn 100Filmer. Andre delen finner alle 
	som ikke har rollen director. S? finner vi alle person som ikke har rollen som regiss?r. 
	S? setter vi sammen sp?rringen. Blir mye av samme som oppgaven i filmCountry, samme prinsippet
	men oppgaven er litt st?rre s? det blir mer ? 
*/

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

/*
Hva er de 200 mest brukte film titlene, og hvor ofte er de brukt?
*/

SELECT title, count(*)
FROM film
GROUP BY title
ORDER BY count(*) DESC
LIMIT 200;

/*
Fremgangsm?te: 
1. 	Hvor finner jeg informasjonen?
	~ Titlene ligger i film tabellen.
2. 	SELECT p? title, og count().
	~ Har en variabel i SELECT, samtidig som count() som er en aggregatfunksjon.
	~ M? bruke GROUP BY p? variabelen.
3. 	Filtrerer ved ? sortere og bruke LIMIT.
*/

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

/*
Bruk den forrige sp?rringen til ? finne ut hvilke sjangre som er 
mest representert i de 200 mest brukte titlene.
*/

SELECT genre, count(*)
FROM film f 
	INNER JOIN filmgenre fg ON f.filmid = fg.filmid
WHERE title IN 
(
	SELECT title
	FROM film
	GROUP BY title
	ORDER BY count(*) DESC
	LIMIT 200
)
GROUP BY genre
ORDER BY count(*) DESC;

/*
Fremgangsm?te:
1. 	Hvor finner jeg informasjonen?
	~ I tillegg til det i den forrige oppgaven, 
	  s? ligger genre i filmgenre tabellen. JOIN'er de sammen p? 'filmid'. 
2. 	SELECT p? genre og count().
	~ Igjen GROUP BY. 
3. 	Da er det bare ? filtere filmene. 
	~ Bruker title "IN" med den forrige sp?rringen,
	  og sorterer etter count(), men det er ikke "en del av oppgaven"/n?dvendig.
*/

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

/*
Finn titlene til filmene som kommer fra landene som har laget f?rrest filmer.
*/

-- Del 1
-- Finne landene og hvor mange filmer de har laget. 

SELECT country, count(*) AS ant
FROM film f
	INNER JOIN filmcountry fc ON f.filmid = fc.filmid
GROUP BY country
ORDER BY count(*);

--~~~~
-- M? joines med film for ? f? riktig svar. 
SELECT country, count(*) AS ant
FROM filmcountry 
GROUP BY country
ORDER BY count(*);
--~~~~

/*
Fremgangsm?te:
1. 	Skal ta det litt steg for steg. F?rst finne landene og hvor mange filmer de har laget.
2.	Litt i samme stil som den f?rste oppgaven jeg gjorde, men med en JOIN.
	~ Henter 'country' fra filmcountry og JOIN'er med film.
	~ Samme, ORDER BY count() for ryddighet.
	~ GROUP BY fordi det er en variabel og en aggregatfunksjon i SELECT.
*/


-- Del 2
-- Indre SELECT for ? hente ut min(ant)

WITH land_ant AS 
(
SELECT country, count(*) AS ant
FROM film f 
	INNER JOIN filmcountry fc ON f.filmid = fc.filmid
GROUP BY country
ORDER BY count(*) DESC
)

SELECT country
FROM film f
	INNER JOIN filmcountry fc ON f.filmid = fc.filmid
GROUP BY country
HAVING count(*) = 
(
	SELECT min(ant)
	FROM land_ant
);

/*
Fremgangsm?te:
1. 	Setter Del 1 i en WITH, for bruk senere.
2. 	Standard SELECT setning. JOIN'er de to tabellene, og filtrerer ut i HAVING, 
	der hvor count(*) blir lik min(ant). 
*/

-- Del 3
-- Finishing touches. Finne titlene og landene ut i fra de andre del-sp?rringene.

WITH land_ant AS 
(
SELECT country, count(*) AS ant
FROM film f
	INNER JOIN filmcountry fc ON f.filmid = fc.filmid
GROUP BY country
ORDER BY count(*) DESC
)

SELECT title, country
FROM film f 
	INNER JOIN filmcountry fc ON f.filmid = fc.filmid
WHERE country IN 
(
	SELECT country
	FROM film f
		INNER JOIN filmcountry fc ON f.filmid = fc.filmid
	GROUP BY country
	HAVING count(*) = 
	(
		SELECT min(ant)
		FROM land_ant
	)
);

/*
Fremgangsm?te:
1. Siste innspurt. Henter title og country, med den samme JOIN betingelsen.
2. Filtrerer ut alle landene ved ? sp?rre om "IN" til country.
*/

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

/*
Navn p? regiss?r og filmtittel for filmer hvor mer enn 200 personer har deltatt, 
uansett hvilken funksjon de har hatt. Ta ikke med filmer som har hatt flere 
(mere enn en) regiss?rer.
*/

-- Filmer med bare en regiss?r.
SELECT filmid, count(*)
FROM filmparticipation
WHERE parttype = 'director'
GROUP BY filmid
HAVING count(*) = 1;

-- Filmer hvor mer enn 200 personer har deltatt.
SELECT filmid, count(*)
FROM filmparticipation
GROUP BY filmid
HAVING count(*) > 200;

-- Resultatet!
SELECT p.firstname || ' ' || p.lastname AS name, f.title
FROM film f 
	INNER JOIN filmparticipation fp ON f.filmid = fp.filmid
	INNER JOIN person p ON fp.personid = p.personid 
WHERE fp.filmid IN 
(
	SELECT filmid
	FROM filmparticipation
	WHERE parttype = 'director'
	GROUP BY filmid
	HAVING count(*) = 1
)
AND fp.filmid IN 
(
	SELECT filmid
	FROM filmparticipation
	GROUP BY filmid
	HAVING count(*) > 200
)
AND parttype = 'director';

/*
Fremgangsm?te:
1. 	Lager f?rst de to enkle sp?rringene ">200" og "=1 regiss?r".
2. 	Joiner deretter de tre tabellene det er snakk om. Film, Filmparticipation, Person.
3.	Filtrerer bort alle filmid'er som ikke er i BEGGE de to enkle sp?rringene.
4. 	Tar bare med direkt?ren sitt navn.
*/