-- ----------------------------------------------------
-- EXERCICE 1
-- ----------------------------------------------------

-- ----------------------------------------------------
-- A. Creation des tables
-- ----------------------------------------------------
DROP TABLE IF EXISTS f;
CREATE TABLE f (
	nf text NOT NULL,
	nomf text,
	categorie integer,
	ville text,
	PRIMARY KEY (nf)
);
DROP TABLE IF EXISTS p;
CREATE TABLE p (
	np text NOT NULL,
	nomp text,
	couleur text,
	poids integer,
	ville text,
	PRIMARY KEY (np)
);
DROP TABLE IF EXISTS j;
CREATE TABLE j (
	nj text NOT NULL,
	nomj text,
	ville text,
	PRIMARY KEY (nj)
);
DROP TABLE IF EXISTS fpj;
CREATE TABLE fpj (
	nf text,
	np text,
	nj text,
	qte integer,
	PRIMARY KEY (nf, np, nj),
	FOREIGN KEY (nf) REFERENCES f ON DELETE SET NULL,
	FOREIGN KEY (np) REFERENCES p ON DELETE RESTRICT,
	FOREIGN KEY (nj) REFERENCES j ON DELETE CASCADE
);

-- ----------------------------------------------------
-- B. Insertion des elements
-- ----------------------------------------------------
INSERT INTO f 	VALUES('F1',	'SMITH',	10,	'LONDRES');
INSERT INTO f 	VALUES('F2',	'DURAND',	10,	'PARIS');
INSERT INTO f 	VALUES('F3',	'DUPONT',	30,	'PARIS');
INSERT INTO f 	VALUES('F4',	'CLARK',	20,	'LONDRES');
INSERT INTO f 	VALUES('F5',	'KURT',		30,	'COLOGNE');

INSERT INTO p 	VALUES('P1',	'ECROU',	'ROUGE',	12,	'LONDRES');
INSERT INTO p 	VALUES('P2',	'BOULON',	'VERT',		17,	'PARIS');
INSERT INTO p 	VALUES('P3',	'VIS',		'BLEU',		17,	'ROME');
INSERT INTO p 	VALUES('P4',	'VIS',		'ROUGE',	14,	'LONDRES');
INSERT INTO p 	VALUES('P5',	'CAME',		'BLEU',		12,	'PARIS');
INSERT INTO p 	VALUES('P6',	'BAGUE',	'ROUGE',	19,	'LONDRES');

INSERT INTO j 	VALUES('J1',	'SPEED',		'PARIS');
INSERT INTO j 	VALUES('J2',	'PUNCH',		'ROME');
INSERT INTO j 	VALUES('J3',	'LECTEUR',		'COLOGNE');
INSERT INTO j 	VALUES('J4',	'CONSOLE',		'COLOGNE');
INSERT INTO j 	VALUES('J5',	'COLLECTEUR',	'LONDRES');
INSERT INTO j 	VALUES('J6',	'TERMINAL',		'OSLO');
INSERT INTO j 	VALUES('J7',	'PREMS',		'LONDRES');

INSERT INTO fpj VALUES('F1',	'P1',	'J1',	200);
INSERT INTO fpj VALUES('F1',	'P1',	'J4',	700);
INSERT INTO fpj VALUES('F2',	'P3',	'J1',	400);
INSERT INTO fpj VALUES('F2',	'P3',	'J2',	200);
INSERT INTO fpj VALUES('F2',	'P3',	'J3',	200);
INSERT INTO fpj VALUES('F2',	'P3',	'J4',	500);
INSERT INTO fpj VALUES('F2',	'P3',	'J5',	600);
INSERT INTO fpj VALUES('F2',	'P3',	'J6',	400);
INSERT INTO fpj VALUES('F2',	'P3',	'J7',	800);
INSERT INTO fpj VALUES('F2',	'P5',	'J2',	100);
INSERT INTO fpj VALUES('F3',	'P3',	'J1',	200);
INSERT INTO fpj VALUES('F3',	'P4',	'J2',	500);
INSERT INTO fpj VALUES('F4',	'P6',	'J3',	300);
INSERT INTO fpj VALUES('F4',	'P6',	'J7',	300);
INSERT INTO fpj VALUES('F5',	'P1',	'J4',	1000);
INSERT INTO fpj VALUES('F5',	'P2',	'J2',	200);
INSERT INTO fpj VALUES('F5',	'P2',	'J4',	100);
INSERT INTO fpj VALUES('F5',	'P3',	'J4',	1200);
INSERT INTO fpj VALUES('F5',	'P4',	'J4',	800);
INSERT INTO fpj VALUES('F5',	'P5',	'J4',	400);
INSERT INTO fpj VALUES('F5',	'P5',	'J5',	500);
INSERT INTO fpj VALUES('F5',	'P5',	'J7',	100);
INSERT INTO fpj VALUES('F5',	'P6',	'J2',	200);
INSERT INTO fpj VALUES('F5',	'P6',	'J4',	500);

-- ----------------------------------------------------
-- C. Requetes SQL
-- ----------------------------------------------------
select * from j;
select * from j where ville = 'LONDRES';
select distinct nf from fpj where nj = 'J1';
select nf, np, nj from fpj where qte between 300 and 750;
select distinct nomj from j where nj in (select distinct nj from fpj where nf = 'F1');
select distinct couleur from p where np in (select distinct np from fpj where nf = 'F1');
(select distinct nf from fpj where nj = 'J1') intersect (select distinct nf from fpj where nj = 'J2');
select distinct nf from fpj where nj = 'J1' and np in (select np from p where couleur = 'ROUGE');
select distinct np from fpj where nj in (select nj from j where ville = 'LONDRES');
select distinct nf from fpj where nj in (select distinct nj from j where ville in ('PARIS','LONDRES')) and np in (select np from p where couleur = 'ROUGE');
select distinct np from fpj where nf in(select distinct nf from (f join j on f.ville = j.ville));
select distinct nj from (fpj join f on fpj.nf = f.nf) as pdistantfj group by nj having count(distinct ville) > 1;
select distinct nj from fpj where nf in (select distinct nf from f where ville <> 'LONDRES') and np in (select np from p where couleur = 'ROUGE');
select distinct nf from fpj where np in (select np from fpj where nf in (select distinct nf from fpj where np in (select np from p where couleur = 'ROUGE')));
select distinct f.ville as ville_de_f,np,j.ville as ville_de_j from j,f,fpj where f.ville <> j.ville and fpj.nf = f.nf and fpj.nj = j.nj;
select distinct nf from (select * from fpj where np in (select distinct np from fpj where nj = all (select distinct nj from fpj))) as fpallj group by nf,np having count(distinct nj) = (select count(distinct nj) from fpj);
-- (select distinct np from fpj) except select np from (select distinct np, nj from ((select np from fpj) as nnp cross join (select nj from fpj) as nnj) except (select np, nj from fpj where nf = 'F1')) as n; ou
select nj from fpj where not exists (select np from fpj where nf <> 'F1');
select distinct np from fpj where nj = all (select nj from j where ville = 'LONDRES');
select distinct nj from fpj where np = all (select np from fpj where nf = 'F1');
select distinct nj from fpj where not exists (select np from fpj where nf <> 'F2');
select count(nj) from fpj where nf = 'F1';
select sum(qte) from fpj where nf = 'F1' and np = 'P1';
select np,nj,sum(qte) from fpj group by nj,np;
select distinct np from fpj group by nj,np having avg(qte) > 350;
select distinct nf from fpj join (select nj,avg(qte) as average from fpj where np = 'P1' group by nj) as avgP1byProject on fpj.nj = avgP1byProject.nj  where qte > average;

-- --------------------------------------------------------------------------
-- EXERCICE 2
-- --------------------------------------------------------------------------

-- ---------------------------------------------------------------------------
-- A. Creation de enseignement et affectation, et de la reference exterieure
-- ---------------------------------------------------------------------------
DROP TABLE IF EXISTS enseignement CASCADE;
CREATE TABLE enseignement(
professeur text NOT NULL,
matiere text,
PRIMARY KEY  (professeur)
);

DROP TABLE IF EXISTS affectation;
CREATE TABLE affectation(
classe text NOT NULL,
professeur text NOT NULL,
PRIMARY KEY (classe, professeur),
-- Contrainte a)
FOREIGN KEY (professeur) REFERENCES enseignement (professeur) ON DELETE RESTRICT
);

-- ----------------------------------------------------
-- B. Creation du trigger et de la fonction associee
-- ----------------------------------------------------
DROP FUNCTION IF EXISTS verify_classe () CASCADE;
CREATE FUNCTION verify_classe () RETURNS TRIGGER AS $$
	DECLARE
		mat text;
	BEGIN
		select into mat matiere
		from (
			select distinct matiere from enseignement where professeur in
				(select professeur from affectation where classe = NEW.classe)
		) as matClasse
		where matiere = (select matiere from enseignement where professeur = NEW.professeur) 
		;
		IF FOUND THEN
			RAISE EXCEPTION 'Un professeur enseigne déjà la matière % à la classe %', mat, NEW.classe;
		END IF;
		RETURN NEW;
	END;
$$ LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS trig_bef_ins_affectation ON affectation RESTRICT;
CREATE TRIGGER trig_bef_ins_affectation BEFORE INSERT ON affectation
	FOR EACH ROW
	EXECUTE PROCEDURE verify_classe();

INSERT INTO enseignement (professeur, matiere) VALUES ('tata', 'maths');
INSERT INTO enseignement (professeur, matiere) VALUES ('titi', 'bio');
INSERT INTO enseignement (professeur, matiere) VALUES ('toto', 'info');
INSERT INTO enseignement (professeur, matiere) VALUES ('tutu', 'info');

INSERT INTO affectation (classe, professeur) VALUES ('PC', 'toto');
INSERT INTO affectation (classe, professeur) VALUES ('PC', 'tata');
INSERT INTO affectation (classe, professeur) VALUES ('NIX', 'tata');
--Insert contredisant la contraite a)
INSERT INTO affectation (classe, professeur) VALUES ('NIX', 'trax');
--Insert contredisant la contrainte b)
INSERT INTO affectation (classe, professeur) VALUES ('PC', 'tutu');