/* Oppgave 1:
Skriv SQL-setninger som oppretter tabellene i skjemaet. 
Du trenger ikke ? tenke p? eventuelle totale roller og mengdeskranker 
som var i den originale modellen, men de skrankene som er angitt i 
grupperingen skal v?re med.
*/

CREATE TABLE kunde (
	kundenummer INT,
	kundenavn VARCHAR(50),
	kundeadresse VARCHAR(50),
	kundefakturaadresse VARCHAR(50),

	PRIMARY KEY (kundenummer)
);

CREATE TABLE ansatt (
	ansattnr INT,
	navn VARCHAR(50),
	f?dselsdato DATE,
	ansattDato DATE,

	PRIMARY KEY (ansattnr)
);

CREATE TABLE prosjekt (
	prosjektnummer INT,
	prosjektleder INT,
	prosjektnavn VARCHAR(50),
	kundenummer INT,

	PRIMARY KEY (prosjektnummer),
	FOREIGN KEY (kundenummer) REFERENCES kunde (kundenummer),
	FOREIGN KEY (prosjektleder) REFERENCES ansatt (ansattnr)
);

CREATE TABLE ansattDeltarIProsjekt (
	ansattnr INT,
	prosjektnummer INT,

	PRIMARY KEY (ansattnr, prosjektnummer),
	FOREIGN KEY (ansattnr) REFERENCES ansatt (ansattnr),
	FOREIGN KEY (prosjektnummer) REFERENCES prosjekt (prosjektnummer)
);


/* Oppgave 2:
Fyll tabellene med data. 
Skriv insert values-setninger som gj?r det mulig ? teste noen 
av select-setningene som skal skrives i neste oppgave.
*/

INSERT INTO kunde VALUES
(123, 'Donald', '1313 Webfoot Walk', 'Duckburg, Calisota'),
(124, 'Batman', 'Wayne Manor', 'Gotham City'),
(125, 'Flanders', '740 Evergreen Terrace', 'Springfield'),
(126, 'Paddington Bear', '32 Windsor Gardens', 'London'),
(127, 'Spongebob SquarePants', '124 Conch Street', 'Bikini Bottom');

INSERT INTO ansatt VALUES
(321, 'Lois Lane', '1962-01-01', '1962-08-16'),
(320, 'Sirius Black', '1980-12-08', '2001-11-30'),
(319, 'Lord Emsworth', '1969-9-26', '1968-11-22'),
(318, 'Sherlock Holmes', '1965-7-29', '1964-12-4'),
(317, 'Monica Geller', '1962-10-5', '1963-02-16');

INSERT INTO prosjekt VALUES
(981, 321, 'Manhatten Project', 124),
(980, 320, 'Development of Windows Vista', 123);

INSERT INTO ansattDeltarIProsjekt VALUES
(319, 981),
(319, 980),
(317, 980),
(318, 981);

/* Oppgave 3:
Skriv SQL-sp?rringer som henter ut f?lgende informasjon:

1. 	En liste over alle kunder. 
	Listen skal inneholde kundenummer, kundenavn og kundeadresse.
*/

SELECT k.kundenummer, k.kundenavn, k.kundeadresse
FROM kunde k;

/*
2. 	Navn p? alle prosjektledere. 
	Dersom en ansatt er prosjektleder for flere prosjekter skal 
	navnet kun forekomme en gang.
*/

SELECT DISTINCT a.navn
FROM prosjekt p INNER JOIN ansatt a ON p.prosjektleder = a.ansattnr;

/*
3. 	Alle ansattnummerene som er knyttet til prosjektet med prosjektnavn ?Ruter app?.
*/

SELECT adip.ansattnr
FROM ansattDeltarIProsjekt adip INNER JOIN prosjekt p ON adip.prosjektnummer = p.prosjektnummer
WHERE p.prosjektnavn = 'Manhatten Project'; --'Ruter App';
-- Samme type oppgave, mer eksplosivt prosjekt.

/*
4. En liste over alle ansatte som er knyttet til prosjekter som har oppdragsgiver kunden
med navn ?NSB?.
*/

SELECT a.*
FROM kunde k INNER JOIN prosjekt p ON p.kundenummer = k.kundenummer
	INNER JOIN ansattDeltarIProsjekt adip ON p.prosjektnummer = adip.prosjektnummer
	INNER JOIn ansatt a ON adip.ansattnr = a.ansattnr
WHERE k.kundenavn = 'Batman'; -- 'NSB';
-- Samme type oppgave, flere flaggermuser.

/* Oppgave 4: 
Vi tar utgangspunkt tabellene Person, Film og Filmparticipation fra Filmdatabasen. 
(Se create table-setningene for disse tabellene i dokumentet Filmdatabasen under INF1300 Ressurser). 
Skriv select-setninger som:

1. skriver ut alle fornavn (firstname) i Person-tabellen som bestar av n?yaktig 2 tegn. 
Like fornavn skal bare skrives ut en gang.
*/

SELECT DISTINCT p.firstname
FROM person p
WHERE p.firstname LIKE '__';

/*
2. navn i alfabetisk orden pa? alle personer som har deltatt i filmen med filmid 35649
*/

SELECT p.firstname || ' ' || p.lastname AS name
FROM filmparticipation fp INNER JOIN person p ON fp.personid = p.personid
WHERE fp.filmid = 35649
ORDER BY p.firstname || ' ' || p.lastname;

/*
3. tittel p? filmen med filmid 35649.
*/

SELECT f.title
FROM film f
WHERE filmid = 35649;

/*
4. navn i alfabetisk orden pa? alle personer som har deltatt i filmen med tittelen Na!
*/

SELECT p.lastname || ', ' || p.firstname AS name
FROM filmparticipation fp INNER JOIN person p ON fp.personid = p.personid
	INNER JOIN film f ON fp.filmid = f.filmid
WHERE f.title = 'Na!'
ORDER BY p.lastname || ', ' || p.firstname;