/*
OPGAVESTILLING:
Et lille forlag beder dig om at lave en database, der skal kunne gemme information om forlagets bø-
ger, om de tilknyttede forfattere og redaktører, samt om forlagets kunder, der både kan være privat-
kunder og virksomheder. Der skal også gemmes informationer om hvilke bøger de forskellige kunder
har købt.

TABELOVERSIGT:
TABEL				KOLONNER
postnumre			postnummer (PK), bynavn
personer			person_id (PK), fornavn, mellemnavn, efternavn, vejnavn, husnummer, etage, postnummer_fk, email, telefon
forfattere			forfatter_id (PK), person_id_fk
redaktoerer			redaktoer_id (PK), person_id_fk
kunder				kunde_id (PK), person_id_fk, firmanavn, CVR
boeger				ISBN (PK), titel, udgivelsesdato, sider, pris, forfatter_id_fk, redaktoer_id_fk
genrer				genre_id (PK), genrenavn
boggenretildeling	ISBN_fk (PK), genre_id_fk (PK)
salg				ordrenummer (PK), kunde_id_fk, ISBN_fk, antal, rabatprocent, salgsdato, leveringsdato

STRUKTUR:
Scriptet består udover dette introducerende afsnit af følgende dele:
1) Databasen oprettes
2) Tabellerne, deres tilhørende stored procedures og triggers oprettes (rækkefølgen heri følger tabeloversigten ovenover)
3) Data fyldes i tabellerne (rækkefølgen heri følger tabeloversigten ovenover)
4) Løsning af obligatoriske opgaver
5) Løsning af selvstillede opgaver
6) Løsning af yderligere selvstillede opgaver (er IKKE en del af det afleverede script)
*/



#1) Databasen oprettes######################################################################################
CREATE DATABASE IF NOT EXISTS forlag;
USE forlag;



#2) Tabellerne, deres tilhørende stored procedures og triggers oprettes######################################################################################
CREATE TABLE postnumre
(postnummer				CHAR(4) NOT NULL PRIMARY KEY,
bynavn					VARCHAR(30) NOT NULL,
CONSTRAINT ligegyldigt_postnummer_check CHECK						#Constraints har desværre ingen indflydelse i MySQL, deraf navnet 'ligegyldigt_postnummer_check'
	(LENGTH(postnummer) = 4)
);

#Oprettelse af stored procedure til postnumre
DELIMITER $
CREATE PROCEDURE postnummer_check(IN postnummer CHAR(4))
BEGIN
    IF LENGTH(postnummer) <> 4 THEN
        SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'postnumre.postnummer skal bestå af præcis 4 cifre';
    END IF;
END$
DELIMITER ;

#Oprettelse af before insert-trigger til postnumre
DELIMITER $
CREATE TRIGGER postnumre_before_insert BEFORE INSERT ON postnumre
FOR EACH ROW
BEGIN
    CALL postnummer_check(new.postnummer);
END$
DELIMITER ;

#Oprettelse af before update-trigger til postnumre
DELIMITER $
CREATE TRIGGER postnumre_before_update BEFORE UPDATE ON postnumre
FOR EACH ROW
BEGIN
    CALL postnummer_check(new.postnummer);
END$
DELIMITER ;

/*
INSERT INTO postnumre(postnummer, bynavn) VALUES
("100", "København K");
*/

CREATE TABLE personer
(person_id				INT NOT NULL PRIMARY KEY,
fornavn					VARCHAR(30) NOT NULL,
mellemnavn				VARCHAR(30) NULL,
efternavn				VARCHAR(30) NOT NULL,
vejnavn					VARCHAR(40) NOT NULL,
husnummer				INT NOT NULL,
etage					VARCHAR(8) NULL,
postnummer_fk			CHAR(4) NOT NULL,							#Dennes værdi på præcis 4 karakterer sikres i postnumre-tabellen
email					VARCHAR(60) NULL,							#Nedenunder sikres bl.a., at enten email eller telefon angives
telefon					CHAR(8) NULL,								#Nedenunder sikres bl.a., at enten email eller telefon angives
FOREIGN KEY (postnummer_fk) REFERENCES postnumre(postnummer),
CONSTRAINT ligegyldigt_email_og_telefon_check CHECK					#Constraints har desværre ingen indflydelse i MySQL, deraf navnet 'ligegyldigt_email_og_telefon_check'
	((email IS NULL AND LENGTH(telefon) = 8)
    OR
    (email IS NOT NULL))
);

#Oprettelse af stored procedure til personer
DELIMITER $
CREATE PROCEDURE email_og_telefon_check(IN email VARCHAR(60), IN telefon CHAR(8))
BEGIN
    IF email IS NULL AND LENGTH(telefon) <> 8 THEN
        SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'personer.telefon skal bestå af præcis 8 cifre';
    END IF;
    
    IF email IS NULL AND telefon IS NULL THEN
        SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'personer.email og personer.telefon må ikke begge være tomme';
    END IF;
    
END$
DELIMITER ;

#Oprettelse af before insert-trigger til personer
DELIMITER $
CREATE TRIGGER personer_before_insert BEFORE INSERT ON personer
FOR EACH ROW
BEGIN
    CALL email_og_telefon_check(new.email, new.telefon);
END$
DELIMITER ;

#Oprettelse af before update-trigger til personer
DELIMITER $
CREATE TRIGGER personer_before_update BEFORE UPDATE ON personer
FOR EACH ROW
BEGIN
    CALL email_og_telefon_check(new.email, new.telefon);
END$   
DELIMITER ;


CREATE TABLE forfattere
(forfatter_id			INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
person_id_fk			INT NOT NULL,
FOREIGN KEY (person_id_fk) REFERENCES personer(person_id));


CREATE TABLE redaktoerer
(redaktoer_id			INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
person_id_fk			INT NOT NULL,
FOREIGN KEY (person_id_fk) REFERENCES personer(person_id));


CREATE TABLE kunder
(kunde_id				INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
person_id_fk			INT NOT NULL,
firmanavn				VARCHAR(50) NULL,
CVR						CHAR(8) NULL,								#Her kunne man godt vælge INT, men det vil fjerne foranstående nuller, og derudover skal vi jo ikke udregne noget vha. tallet
FOREIGN KEY (person_id_fk) REFERENCES personer(person_id),
CONSTRAINT ligegyldigt_firmanavn_og_CVR_check CHECK					#Constraints har desværre ingen indflydelse i MySQL, deraf navnet 'ligegyldigt_firmanavn_og_CVR_check'
	((firmanavn IS NULL AND CVR IS NULL)
    OR
    (firmanavn IS NOT NULL AND CVR IS NOT NULL 
    AND LENGTH(CVR) = 8))
);

#Oprettelse af stored procedure til kunder
DELIMITER $
CREATE PROCEDURE firmanavn_og_CVR_check(IN firmanavn VARCHAR(50), IN CVR CHAR(8))
BEGIN
	IF (firmanavn IS NULL AND CVR IS NOT NULL) OR (firmanavn IS NOT NULL AND CVR IS NULL) THEN
        SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'kunder.firmanavn og kunder.CVR skal enten begge være tomme eller begge være udfyldt';
    END IF;

    IF firmanavn IS NOT NULL AND CVR IS NOT NULL AND LENGTH(CVR) <> 8 THEN
        SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'kunder.CVR skal bestå af præcis 8 cifre';
    END IF;
END$
DELIMITER ;

#Oprettelse af before insert-trigger til kunder
DELIMITER $
CREATE TRIGGER kunder_before_insert BEFORE INSERT ON kunder
FOR EACH ROW
BEGIN
    CALL firmanavn_og_CVR_check(new.firmanavn, new.CVR);
END$
DELIMITER ;

#Oprettelse af before update-trigger til kunder
DELIMITER $
CREATE TRIGGER kunder_before_update BEFORE UPDATE ON kunder
FOR EACH ROW
BEGIN
    CALL firmanavn_og_CVR_check(new.firmanavn, new.CVR);
END$   
DELIMITER ;


CREATE TABLE boeger
(ISBN					CHAR(13) NOT NULL PRIMARY KEY,					#Her kan BIGINT også bruges, men det vil fjerne foranstående nuller, og derudover skal vi jo ikke udregne noget vha. tallet. VARCHAR kan også bruges, men vi kender længden.
titel					VARCHAR(100) NOT NULL,
udgivelsesdato			DATE NOT NULL,
sider					INT	NOT NULL,
pris					DECIMAL(6,2) NOT NULL,
forfatter_id_fk			INT	NOT NULL,
redaktoer_id_fk			INT	NOT NULL,
FOREIGN KEY (redaktoer_id_fk) REFERENCES redaktoerer(redaktoer_id),
FOREIGN KEY (forfatter_id_fk) REFERENCES forfattere(forfatter_id),
CONSTRAINT ligegyldigt_ISBN_check CHECK									#Constraints har desværre ingen indflydelse i MySQL, deraf navnet 'ligegyldigt_ISBN_check'
	(LENGTH(ISBN) = 13)
);

#Oprettelse af stored procedure til boeger
DELIMITER $
CREATE PROCEDURE ISBN_check(IN ISBN CHAR(13))
BEGIN
    IF LENGTH(ISBN) <> 13 THEN
        SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'boeger.ISBN skal bestå af præcis 13 cifre';
    END IF;
END$
DELIMITER ;

#Oprettelse af before insert-trigger til boeger
DELIMITER $
CREATE TRIGGER boeger_before_insert BEFORE INSERT ON boeger
FOR EACH ROW
BEGIN
    CALL ISBN_check(new.ISBN);
END$   
DELIMITER ;

#Oprettelse af before update-trigger til boeger
DELIMITER $
CREATE TRIGGER boeger_before_update BEFORE UPDATE ON boeger
FOR EACH ROW
BEGIN
    CALL ISBN_check(new.ISBN);
END$   
DELIMITER ;


CREATE TABLE genrer
(genre_id				INT NOT NULL PRIMARY KEY,
genrenavn				VARCHAR(30) NOT NULL);


CREATE TABLE boggenretildeling
(ISBN_fk				CHAR(13) NOT NULL,
genre_id_fk				INT NOT NULL,
PRIMARY KEY (ISBN_fk, genre_id_fk),
FOREIGN KEY (ISBN_fk) REFERENCES boeger(ISBN),
FOREIGN KEY (genre_id_fk) REFERENCES genrer(genre_id));


CREATE TABLE salg
(ordrenummer			INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
kunde_id_fk				INT NOT NULL,
ISBN_fk					CHAR(13) NOT NULL,							#At denne består af præcis 13 karakterer, sikres i boeger
antal					INT NOT NULL,
rabatprocent			INT NOT NULL,
salgsdato				DATE NOT NULL,
leveringsdato			DATE NULL,									#En ordre må gerne oprettes, førend den er blevet leveret
FOREIGN KEY (kunde_id_fk) REFERENCES kunder(kunde_id),
FOREIGN KEY (ISBN_fk) REFERENCES boeger(ISBN));



#3) Data fyldes i tabellerne#####################################################################################
INSERT INTO postnumre(postnummer, bynavn) VALUES
("1001", "København K"),
("1264", "København K"),
("2000", "Frederiksberg"),
("2500", "Valby"),
("4970", "Rødby"),
("6093", "Sjølund"),
("6580", "Vamdrup"),
("6818", "Årre"),
("7190", "Billund"),
("8000", "Aarhus C");

INSERT INTO personer(person_id, fornavn, mellemnavn, efternavn, vejnavn, husnummer, etage, postnummer_fk, email, telefon) VALUES
(1, "Helle", NULL, "Helle", "Nakskov landevej", 3, NULL, "4970", "hellehelle@supermail.com", "36475869"),
(2, "Søren", "Sigurd", "Barrett", "Paradisæblevej", 56, "st. th.", "2500", "sigurd@supermail.com", "89223342"),
(3, "Hanne-Vibeke", NULL, "Holst", "Drosselvej", 25, NULL, "2000", "hv_holst@supermail.com", "45737327"),
(4, "Niels", "Jørgen", "Cappelørn", "Herningvej", 12, "2. tv.", "8000", "njc@supermail.com", "64738291"),
(5, "Inger", NULL, "Kristensen", "Nyvang", 7, NULL, "6818", "ingerkristensen@ordinarymail.com", NULL),
(6, "Lars", "Baumspieler", "Jørgensen", "Danmarksgade", 10, "tv.", "6580", NULL, "65655656"),
(7, "Peter", NULL, "Larsen", "Langgade", 1000, NULL, "1264", "pl@okmail.com", NULL),
(8, "Lis", NULL, "Kromutter", "Ølgade", 2, "10. tv.", "1001", NULL, "75572211"),
(9, "Mikael", "Hurtig", "Viktorsen", "Ryesgade", 24, "3. th.", "8000", "hurtig@mail.dk", "22443535"),
(10, "Jürgen", "Vig", "Knudstop", "Åstvej", 1, NULL, "7190", "lego@legomail.com", "10203040");

INSERT INTO forfattere(person_id_fk) VALUES
(1), (2), (3);

INSERT INTO redaktoerer(person_id_fk) VALUES
(1), (4), (5);

INSERT INTO kunder(person_id_fk, firmanavn, CVR) VALUES
(3, NULL, NULL),
(5, NULL, NULL),
(6, NULL, NULL),
(7, NULL, NULL),
(8, NULL, NULL),
(9, NULL, NULL),
(9, "Klogeskolen", "12345678"),
(10, "LEGO", "46372819");

INSERT INTO boeger(ISBN, titel, udgivelsesdato, sider, pris, forfatter_id_fk, redaktoer_id_fk) VALUES
("9788763821711", "Rødby-Puttgarden", "2011-10-13", 174, 149.95, 1, 3),
("9788763851312", "Rester", "2017-09-28", 118, 169.95, 1, 1),
("9788763857628", "de", "2018-04-13", 156, 259.95, 1, 2),
("9788756799331", "Sigurd fortæller Danmarkshistorie", "2016-10-10", 500, 399.95, 2, 2),
("9788740043488", "Sigurds lille skole", "2019-04-15", 192, 299.95, 2, 2),
("9788702266412", "Som pesten", "2018-06-12", 692, 149.95, 3, 3),
("9788702063165", "Til sommer", "2007-11-06", 131, 59.95, 3, 1);

INSERT INTO genrer(genre_id, genrenavn) VALUES
(1, "Skønlitteratur"),
(2, "Religion"),
(3, "Filosofi"),
(4, "Fantasy"),
(5, "Børnelitteratur"),
(6, "Biografi"),
(7, "Faglitteratur"),
(8, "Roman"),
(9, "Novelle"),
(10, "Historie");

INSERT INTO boggenretildeling(ISBN_fk, genre_id_fk) VALUES
("9788763821711", 1), ("9788763821711", 8),
("9788763851312", 1), ("9788763851312", 8),
("9788763857628", 1), ("9788763857628", 9),
("9788756799331", 5), ("9788756799331", 10),
("9788740043488", 5),
("9788702266412", 1), ("9788702266412", 10),
("9788702063165", 1), ("9788702063165", 8);

INSERT INTO salg(kunde_id_fk, ISBN_fk, antal, rabatprocent, salgsdato, leveringsdato) VALUES
(1, "9788763821711", 1, 50, "2012-06-01", "2012-06-03"),
(2, "9788763851312", 1, 25, "2018-04-15", "2018-04-20"),
(3, "9788763821711", 1, 0, "2018-08-25", "2018-09-15"),
(3, "9788763851312", 1, 0, "2018-09-25", "2018-09-29"),
(3, "9788763857628", 1, 0, "2018-10-25", "2018-10-27"),
(3, "9788702063165", 1, 0, "2018-11-25", "2018-11-30"),
(4, "9788756799331", 1, 20, "2016-10-10", "2016-10-12"),
(4, "9788740043488", 1, 10, "2019-05-22", NULL),
(5, "9788702063165", 3, 25, "2019-01-01", "2019-01-20"),
(6, "9788702266412", 1, 75, "2019-04-15", "2019-04-18"),
(6, "9788702063165", 1, 75, "2019-04-16", "2019-04-18"),
(7, "9788740043488", 57, 15, "2019-04-15", "2019-04-19"),
(7, "9788756799331", 5, 28, "2019-05-23", NULL),
(8, "9788756799331", 13, 7, "2017-01-01", "2017-01-02");



#4) Løsning af obligatoriske opgaver#####################################################################################
#OBLIGATORISK STORED PROCEDURE NR. 1: Alle bøger købt af bestemt kunde
#Opgaveløsningen kan ikke kun indbefatte at få udskrevet titlerne på bøgerne, da kunden
#kan have købt mere end et eksemplar af bogen, hvormed alle bøgerne i så tilfælde ikke
#ville være med. Derfor benyttes en forespørgsel, der udskriver både titlerne og antal
#eksemplarer af hver titel, i dette tilfælde for kunden med kunde_id = 3.
#I forespørgslen kræves, at ISBN-numrene stemmer overens, og at salg.kunde_id_fk = 3 (og dermed at
#kunder.kunde_id = 3):

#Med implicit equijoin:
SELECT b.titel, s.antal
FROM boeger b, salg s
WHERE b.ISBN = s.ISBN_fk AND s.kunde_id_fk = 3;

#Med eksplicit equijoin:
SELECT b.titel, s.antal
FROM boeger b
	INNER JOIN salg s
    ON b.ISBN = s.ISBN_fk AND s.kunde_id_fk = 3;

#Herunder kommer så den endelige løsning, hvor ovenstående query oprettes som stored procedure
#med den forskel, at man ved brugen af den skal medgive kunde_id på den kunde, hvis bogkøb
#man ønsker at få fremvist:
DELIMITER //
CREATE PROCEDURE boeger_koebt_af_kunde
(IN kunde_id INT)
	BEGIN
		SELECT b.titel, s.antal
		FROM boeger b
			INNER JOIN salg s
			ON b.ISBN = s.ISBN_fk AND s.kunde_id_fk = kunde_id;
	END //
DELIMITER ;

#Her kaldes den netop oprettede stored procedure med 3 forskellige argumenter, der
#henter bogtitlerne og antal eksemplarer deraf købt af kunderne med kunde_id på hhv. 1, 3 og 7
CALL boeger_koebt_af_kunde(1);
CALL boeger_koebt_af_kunde(3);
CALL boeger_koebt_af_kunde(7);


#OBLIGATORISK STORED PROCEDURE NR. 2: Alle kunder, der har købt en bestemt bog
#I forespørgslen kræves, at kunde_id'erne, person_id'erne og ISBN-numrene stemmer
#overens, og at titlen i dette tilfælde er "Sigurds lille skole":
SELECT p.person_id, k.kunde_id, p.fornavn, p.mellemnavn, p.efternavn
FROM personer p, kunder k, salg s, boeger b
WHERE s.kunde_id_fk = k.kunde_id
    AND k.person_id_fk = p.person_id
    AND s.ISBN_fk = b.ISBN
    AND b.titel = "Sigurds lille skole";
    
#Omsat til en stored procedure med variabelt titel-input kommer løsningen til at
#sådan her ud:
DELIMITER //
CREATE PROCEDURE kunder_koebt_bog
(IN titel VARCHAR(100))
	BEGIN
		SELECT p.person_id, k.kunde_id, p.fornavn, p.mellemnavn, p.efternavn
		FROM personer p, kunder k, salg s, boeger b
		WHERE s.kunde_id_fk = k.kunde_id
			AND k.person_id_fk = p.person_id
			AND s.ISBN_fk = b.ISBN
			AND b.titel = titel;
	END //
DELIMITER ;

#Her kaldes den netop oprettede stored procedure 2 gange med forskellige argumenter, der
#henter oplysninger om de kunder, der har købt hhv. "Som pesten" og "Sigurd fortæller
#Danmarkshistorie":
CALL kunder_koebt_bog("Som pesten");
CALL kunder_koebt_bog("Sigurd fortæller Danmarkshistorie");


#OBLIGATORISK STORED PROCEDURE NR. 3: Alle bøger og deres salgstal, mest solgte bog først
#Følgende forespørgsel er tæt på at være tilfredsstillende. Den udskriver ISBN-numrene og det
#tilhørende antal solgte eksemplarer i faldende rækkefølge:
SELECT ISBN_fk, SUM(antal)
FROM salg
GROUP BY ISBN_fk
ORDER BY SUM(antal) DESC;

#Denne udskriver titlerne i stedet for ISBN-numrene og sættes derfor ind i en stored procedure
#og kaldes efterfølgende:
SELECT b.titel, SUM(s.antal)
FROM salg s, boeger b
WHERE s.ISBN_fk = b.ISBN
GROUP BY s.ISBN_fk
ORDER BY SUM(s.antal) DESC;

DELIMITER //
CREATE PROCEDURE salgstal_prioriteret()
	BEGIN
		SELECT b.titel AS "Titel", SUM(s.antal) AS "Antal solgt"
		FROM salg s, boeger b
		WHERE s.ISBN_fk = b.ISBN
		GROUP BY s.ISBN_fk
		ORDER BY SUM(s.antal) DESC;
	END //
DELIMITER ;

CALL salgstal_prioriteret;


#OBLIGATORISK STORED PROCEDURE NR. 4: Alle kunder og antallet af bøger, de har købt, største køber først
#Denne henter kunde_id_fk'erne og deres individuelt summerede antal køb:
SELECT kunde_id_fk, SUM(antal)
FROM salg
GROUP BY kunde_id_fk
ORDER BY SUM(antal) DESC;

#Her er outputtet i stedet for kunde_id_fk en masse oplysninger om kunden:
SELECT p.person_id, k.kunde_id, p.fornavn, p.mellemnavn, p.efternavn, SUM(s.antal) AS "antal eksemplarer købt"
FROM salg s, personer p, kunder k
WHERE s.kunde_id_fk = k.kunde_id AND k.person_id_fk = p.person_id
GROUP BY s.kunde_id_fk
ORDER BY SUM(s.antal) DESC;

#Ovenstående danner udgangspunkt for følgende stored procedure, som kaldes nedenunder:
DELIMITER //
CREATE PROCEDURE kunder_prioriteret()
	BEGIN
		SELECT p.person_id, k.kunde_id, p.fornavn, p.mellemnavn, p.efternavn, SUM(s.antal) AS "antal eksemplarer købt"
		FROM salg s, personer p, kunder k
		WHERE s.kunde_id_fk = k.kunde_id AND k.person_id_fk = p.person_id
		GROUP BY s.kunde_id_fk
		ORDER BY SUM(s.antal) DESC;
	END //
DELIMITER ;

CALL kunder_prioriteret;



#5) Løsning af selvstillede opgaver#####################################################################################
#SELVSTILLET OPGAVE NR. 1: Udskriv faktura på ønsket ordrenummer (inkl. samlet pris, adresse osv.)
#Henter oplysninger om ordrenummer = 1:
SELECT ordrenummer, kunde_id_fk, ISBN_fk, antal, rabatprocent
FROM salg
WHERE ordrenummer = 1;

#Henter oplysninger om bogen på ordrenummer 1 (da den på baggrund af foregående query vides at have
#dette ISBN-nummer):
SELECT ISBN, titel, pris
FROM boeger
WHERE ISBN = "9788763821711";

#Henter alle oplysninger om selve købet på ordrenummer 2:
SELECT s.ordrenummer, b.titel, s.antal, b.pris AS "Pris/stk.", s.rabatprocent, ROUND(b.pris*s.antal*(100-s.rabatprocent)/100, 2) AS "Samlet pris"
FROM salg s, boeger b
WHERE s.ISBN_fk = b.ISBN AND s.ordrenummer = 2;

#Henter alle oplysninger om både købet og kunden på ordrenummer 12:
SELECT s.ordrenummer, b.titel, s.antal, b.pris AS "Pris/stk.", s.rabatprocent, ROUND(b.pris*s.antal*(100-s.rabatprocent)/100, 2) AS "Samlet pris",
	k.kunde_id, p.fornavn, p.mellemnavn, p.efternavn, p.vejnavn, p.husnummer, p.etage, post.postnummer, post.bynavn, p.email, p.telefon, k.firmanavn, k.CVR
FROM salg s, boeger b, kunder k, personer p, postnumre post
WHERE s.ISBN_fk = b.ISBN AND s.kunde_id_fk = k.kunde_id AND k.person_id_fk = p.person_id AND p.postnummer_fk = post.postnummer AND s.ordrenummer = 12;

#Som stored procedure med ordrenummeret som parameter, her er emailen og telefonnummeret fjernet, da det ikke skal være på fakturaen:
DELIMITER //
CREATE PROCEDURE faktura_til_ordrenummer(IN ordrenummer INT)
	BEGIN
		SELECT s.ordrenummer, b.titel, s.antal, b.pris AS "Pris/stk.", s.rabatprocent, ROUND(b.pris*s.antal*(100-s.rabatprocent)/100, 2) AS "Samlet pris",
			k.kunde_id, p.fornavn, p.mellemnavn, p.efternavn, p.vejnavn, p.husnummer, p.etage, post.postnummer, post.bynavn, k.firmanavn, k.CVR
		FROM salg s, boeger b, kunder k, personer p, postnumre post
		WHERE s.ISBN_fk = b.ISBN AND s.kunde_id_fk = k.kunde_id AND k.person_id_fk = p.person_id AND p.postnummer_fk = post.postnummer AND s.ordrenummer = ordrenummer;
	END //
DELIMITER ;

#faktura-proceduren kaldes med 3 forskellige argumenter:
CALL faktura_til_ordrenummer(1);
CALL faktura_til_ordrenummer(5);
CALL faktura_til_ordrenummer(13);


#SELVSTILLET OPGAVE NR. 2: Udskriv faktura(/-er) på ønsket kunde_id (inkl. samlet pris, adresse osv.)
#Ift. foregående stored procedure, så er det nu ikke længere ordrenummeret men kunde_id'et, der er afgørende:
SELECT s.ordrenummer, b.titel, s.antal, b.pris AS "Pris/stk.", s.rabatprocent, ROUND(b.pris*s.antal*(100-s.rabatprocent)/100, 2) AS "Samlet pris",
			k.kunde_id, p.fornavn, p.mellemnavn, p.efternavn, p.vejnavn, p.husnummer, p.etage, post.postnummer, post.bynavn, k.firmanavn, k.CVR
		FROM salg s, boeger b, kunder k, personer p, postnumre post
		WHERE s.ISBN_fk = b.ISBN AND s.kunde_id_fk = k.kunde_id AND k.person_id_fk = p.person_id AND p.postnummer_fk = post.postnummer AND s.kunde_id_fk = 4;

#Som stored procedure:
DELIMITER //
CREATE PROCEDURE fakturaer_til_kunde_id(IN kunde_id INT)
	BEGIN
		SELECT s.ordrenummer, b.titel, s.antal, b.pris AS "Pris/stk.", s.rabatprocent, ROUND(b.pris*s.antal*(100-s.rabatprocent)/100, 2) AS "Samlet pris",
			k.kunde_id, p.fornavn, p.mellemnavn, p.efternavn, p.vejnavn, p.husnummer, p.etage, post.postnummer, post.bynavn, k.firmanavn, k.CVR
		FROM salg s, boeger b, kunder k, personer p, postnumre post
		WHERE s.ISBN_fk = b.ISBN AND s.kunde_id_fk = k.kunde_id AND k.person_id_fk = p.person_id AND p.postnummer_fk = post.postnummer AND s.kunde_id_fk = kunde_id;
	END //
DELIMITER ;

CALL fakturaer_til_kunde_id(4);
CALL fakturaer_til_kunde_id(6);
CALL fakturaer_til_kunde_id(7);



#6) Løsning af yderligere selvstillede opgaver (er IKKE en del af det afleverede script)#####################################################################################
#SELVSTILLET OPGAVE NR. 3: Indtægt pr. titel rangeret
SELECT b.titel, ROUND(SUM(b.pris*s.antal*(100-s.rabatprocent)/100), 2) AS "Indtægt"
FROM boeger b, salg s
WHERE s.ISBN_fk = b.ISBN
GROUP BY s.ISBN_fk
ORDER BY SUM(b.pris*s.antal*(100-s.rabatprocent)/100) DESC;

#SELVSTILLET OPGAVE NR. 4: Indtægt pr. forfatter rangeret
SELECT p.fornavn, p.mellemnavn, p.efternavn, ROUND(SUM(b.pris*s.antal*(100-s.rabatprocent)/100), 2) AS "Har solgt for"
FROM boeger b, salg s, personer p, forfattere f
WHERE p.person_id = f.person_id_fk AND b.forfatter_id_fk = f.forfatter_id AND b.ISBN = s.ISBN_fk
GROUP BY f.forfatter_id
ORDER BY SUM(b.pris*s.antal*(100-s.rabatprocent)/100) DESC;

#SELVSTILLET OPGAVE NR. 5: Indtægt pr. postnummer rangeret
SELECT p.postnummer_fk, ROUND(SUM(b.pris*s.antal*(100-s.rabatprocent)/100), 2) AS "Indtægt"
FROM personer p, boeger b, salg s, kunder k
WHERE p.person_id = k.person_id_fk AND k.kunde_id = s.kunde_id_fk
GROUP BY p.postnummer_fk
ORDER BY SUM(b.pris*s.antal*(100-s.rabatprocent)/100) DESC;

#SELVSTILLET OPGAVE NR. 6: Indtægt pr. postnummer, hvor postnumre, der ikke er blevet solgt til, medtages
#Her laves en UNION med de postnumre, der ikke er i SALG-tabellen
SELECT p.postnummer_fk AS "Postnummer", ROUND(SUM(b.pris*s.antal*(100-s.rabatprocent)/100), 2) AS "Indtægt"
FROM personer p, boeger b, salg s, kunder k
WHERE p.person_id = k.person_id_fk AND k.kunde_id = s.kunde_id_fk
GROUP BY p.postnummer_fk
UNION
SELECT post.postnummer, 0 AS "Indtægt"
FROM postnumre post
WHERE post.postnummer NOT IN (
	SELECT p.postnummer_fk
    FROM personer p, salg s, kunder k
    WHERE p.person_id = k.person_id_fk AND k.kunde_id = s.kunde_id_fk
);

#SELVSTILLET OPGAVE NR. 7: Antal bøger solgt til postnummer i rangorden
SELECT p.postnummer_fk, SUM(s.antal)
FROM salg s, personer p, kunder k
WHERE p.person_id = k.person_id_fk AND k.kunde_id = s.kunde_id_fk
GROUP BY p.postnummer_fk
ORDER BY SUM(s.antal) DESC;

#SELVSTILLET OPGAVE NR. 8.1: Udskriv alle personer, der også er kunder (dvs. har et kunde_id)
SELECT p.person_id, p.fornavn, p.mellemnavn, p.efternavn, k.kunde_id
FROM personer p, kunder k
WHERE p.person_id = k.person_id_fk;

#SELVSTILLET OPGAVE NR. 8.2: Udskriv alle personer, der også er kunder, og medtag i øvrigt alle personer
SELECT p.person_id, p.fornavn, p.mellemnavn, p.efternavn, k.kunde_id
FROM personer p
	LEFT OUTER JOIN kunder k
	ON p.person_id = k.person_id_fk;

#SELVSTILLET OPGAVE NR. 9.1: Hyppighed af genrer i forlagets bøger
SELECT bgt.genre_id_fk, COUNT(*)
FROM boggenretildeling bgt
GROUP BY bgt.genre_id_fk;

#SELVSTILLET OPGAVE NR. 9.2: Hyppigste 3 genrer i forlagets bøger
SELECT bgt.genre_id_fk, g.genrenavn,  COUNT(*)
FROM boggenretildeling bgt, genrer g
WHERE bgt.genre_id_fk = g.genre_id
GROUP BY bgt.genre_id_fk
ORDER BY COUNT(*) DESC
LIMIT 3;