/*Gruppel?rer(brnavn, emnekode, ?r, vh, antallgr)
S?knad(brnavn, ?r, vh, prioritet, emnekode, antallgr)
Fullf?rtEmne(fnr, brnavn, emnekode, karakter)
*/

--Oppgave 6
CREATE TABLE S?knad(
	brnavn VARCHAR(8),
	?r INTEGER,
	vh CHAR(4),
	prioritet INTEGER,
	emnekode VARCHAR(8) NOT NULL,
	antallgr INTEGER,
PRIMARY KEY (brnavn, prioritet, ?r, vh),
UNIQUE(brnavn, emnekode, ?r, vh),
CHECK(vh IN ('v?r', 'h?st'))
); 

--Oppgave 7-----------------------------------
/*
brnavn -> fnr
fnr -> brnavn

Det kan oppst? redundans av fnr og brnavn i 
relasjonen fullf?rtEmne. Relasjonen vil ikke 
kunne sikre at det alltid er det samme brnavn
gitt et fnr. For ? unng? dette m? relasjonen
dekomponeres (splittes opp).

*/
-----------------------------------------------

--Oppgave 8
/*List opp emner etter antall s?kere v?ren 2017. Listen skal
sorteres etter avtagende antall s?kere.*/

SELECT emnekode, COUNT(brnavn) as antall
FROM S?knad
WHERE ?r = '2017' AND vh = 'v?r'
GROUP BY emnekode
ORDER BY antall DESC;

--Oppgave 9
/*
Lag en oversikt over gruppel?rererfaring, der det for
hver person st?r hvor mange forskjellige emner vedkommende har v?rt
gruppel?rer i og hvor mange semestere personen har v?rt gruppel?rer. Sorter
f?rst p? antall forskjellige emner, dernest p? antall semestere.
*/

SELECT brnavn, COUNT(DISTINCT emnekode) as antEmner, COUNT(DISTINCT(?r, vh)) as antSemester
FROM Gruppel?rer
GROUP BY brnavn
ORDER BY antEmner, antSemester;

--Oppgave 10
/*Finn de som ikke har v?rt gruppel?rere f?r, og som har
s?kt gruppel?rerstilling i ett av f?rste?rsemnene INF1010 og INF1050 v?ren
2017.*/

SELECT brnavn
FROM S?knad s
WHERE emnekode IN('INF1050', 'INF1010') AND 
?r = 2017 AND vh = 'v?r' AND
brnavn NOT IN (SELECT brnavn FROM Gruppel?rer);

--Oppgave 11
/*Finn ut om det er noen som har v?rt gruppel?rer i et
emne mer enn tre ganger og som s?ker stilling i dette emnet ogs? v?ren 2017.*/

CREATE VIEW grlTreGangerIsammeEmne AS(
	SELECT brnavn, emnekode
	FROM Gruppel?rer
	GROUP BY brnavn, emnekode
	HAVING COUNT(emnekode) > 3
);
SELECT s.brnavn
FROM S?knad s NATURAL JOIN grlTreGanger g
WHERE ?r = 2017 AND vh = 'v?r';


--Oppgave 12
/* Finn ut om noen av s?kerne har s?kt emner de ikke har
selv, eller til emner der s?keren har karakter d?rligere enn C.*/

CREATE VIEW godeResultater AS(
	SELECT brnavn, emnekode
	FROM Fullf?rtEmne
	WHERE karakter IN ('A', 'B', 'C')
);
SELECT *
FROM S?knad
WHERE (brnavn, emnekode) NOT IN (SELECT * FROM godeResultater);

--Oppgave 13
/*List opp alle s?kere som ikke s?ker til emner utover
grunniv? (grunniv?emner er emner som har en emnekode med siffer som
begynner med tallet 1).*/

SELECT *
FROM S?knad
WHERE brnavn NOT IN 
	(SELECT brnavn 
	FROM S?knad 
	WHERE emnekode like 'INF1%');