-- Primary Key
ALTER TABLE classe ADD CONSTRAINT pk_classe PRIMARY KEY (numClasse);
ALTER TABLE eleve ADD CONSTRAINT pk_eleve PRIMARY KEY (numEleve);
ALTER TABLE matiere ADD CONSTRAINT pk_matiere PRIMARY KEY (numMatiere);
ALTER TABLE note ADD CONSTRAINT pk_note PRIMARY KEY (numEleve, numMatiere, dateControle);
ALTER TABLE appreciation ADD CONSTRAINT pk_apprreciation PRIMARY KEY (libelleapprreciation);

-- FOREIGN KEY
ALTER TABLE eleve ADD CONSTRAINT fk_eleve FOREIGN KEY (numClasse) REFERENCES classe(numClasse);
ALTER TABLE note ADD CONSTRAINT fk_note FOREIGN KEY (numEleve) REFERENCES eleve(numEleve);
ALTER TABLE note ADD CONSTRAINT fk_note_matiere FOREIGN KEY (numMatiere) REFERENCES matiere(numMatiere);

-- INSERT INTO
Insert Into appreciation values('Très Bien','15','20');
Insert Into appreciation values('Bien','12','14');
Insert Into appreciation values('Moyen','10','11');
Insert Into appreciation values('Insuffisant','8','9');
Insert Into appreciation values('Très Insuffisant','0','7');

Insert into classe values('1','SIO1','o','0.70','0.80');
Insert into classe values('2','SIOSLAM','o','0.70','0.80');
Insert into classe values('3','SIOSIR','o','0.70','0.80');
Insert into classe values('4','GSI','n','0.80','0.90');
Insert into classe values('5','TS','n','0.50','0.60');

Insert Into eleve values('1','Moulin','23','Béziers','1');
Insert Into eleve values('2','Mermoz','20','Montpellier','1');
Insert Into eleve values('3','Lurcat','19','Béziers','1');
Insert Into eleve values('4','Linux','23','Paris','2');
Insert Into eleve values('5','Unix','25',NULL,'2');
Insert Into eleve values('6','Daspet','20','Béziers','2');
Insert Into eleve values('7','Webig','19','Montpellier','3');
Insert Into eleve values('8','Azerty','18','Béziers','4');
Insert Into eleve values('9','Dupont','18','Béziers','4');
Insert Into eleve values('10','Dupert','17','Béziers','4');
Insert Into eleve values('11','Dupire',NULL,'Béziers','4');
Insert Into eleve values('12','Dupol',NULL,'Béziers','3');

Insert into matiere values('1','SI1');
Insert into matiere values('2','SI3');
Insert into matiere values('3','SLAM1');
Insert into matiere values('4','SISR1');
INSERT INTO matiere values('5', 'Surf sur internet');

Insert into note values('2','1','30/05/2011','18');
Insert into note values('2','1','30/12/2011','7');
Insert into note values('2','2','30/03/2011','12');
Insert into note values('2','3','30/04/2011','11');
Insert into note values('2','3','30/06/2011','10');
Insert into note values('2','4','30/07/2011','13');
Insert into note values('3','1','30/01/2011','11');
Insert into note values('3','1','31/01/2011','13');
Insert into note values('3','1','28/02/2011','11');
Insert into note values('3','2','21/01/2011','5');
Insert into note values('3','2','30/01/2011','8');
Insert into note values('4','1','30/01/2011','19');
Insert into note values('4','2','30/01/2011','2');
Insert into note values('4','2','30/11/2011','16');
Insert into note values('4','4','30/11/2011','18');
Insert into note values('4','4','30/12/2011','13');
Insert into note values('5','1','30/01/2011','10');
Insert into note values('5','1','30/06/2011','1');

-- INSERT
INSERT INTO matiere values('5', 'Surf sur internet');
INSERT into classe (numClasse, nomClasse, PostBac) values ('6', 'BTS TOURISME', '0')

-- DELETE
delete from eleve where numClasse IN (SELECT numClasse from classe where nomClasse='GSI');
DELETE FROM ELEVE where ageEleve < 15;
DELETE FROM ELEVE  where NumClasse = 4;
DELETE FROM ELEVE where numClasse = 1 AND numClasse = 2 AND numClasse = 3;


DELETE numEleve FROM note;
select * from eleve;

-- MODIFICATION

UPDATE APPRECIATION SET noteMinimum =
-- select * FROM appreciation;


ALTER table note add constraint fk_note_eleve foreign key(numEleve) references eleve(numEleve) ON DELETE CASCADE;


UPDATE appreciation SET noteMinimum +=1 WHERE libelleappreciation='bien';

select *from appreciation UPDATE appreciation

 Create View eleve_classe_beziers AS
	SELECT NomEleve,NomClasse,PostBac
	FROM eleve E, classe C
	where E.numClasse = C.numClasse AND VilleEleve='Béziers';

	select * from eleve_classe_beziers;

	SELECT NomEleve,NomClasse,PostBac
	FROM