create table Etudiants(num_et Number(4) primary key,nom varchar(30),prenom varchar(30),genre varchar(1));

insert into Etudiants VALUES(0001,'BOTTER', 'Michel', 'M');
insert into Etudiants VALUES(0002,'CARMINATI', 'Veronique', 'F');
insert into Etudiants VALUES(0003,'FABRIQUE', 'Steve', 'M');
insert into Etudiants VALUES(0004,'BERARD', 'Gilles', 'M');
insert into Etudiants VALUES(0005,'DOMONT', 'Andre', 'M');
insert into Etudiants VALUES(0006,'PERDRIX', 'Rachid', 'M');
insert into Etudiants VALUES(0007,'CHOLOGIE', 'Psy', 'M');
insert into Etudiants VALUES(0008,'BETIER', 'Marie', 'F');
INSERT INTO Etudiants VALUES(0009,'BECKER', 'Alexandre','M');
INSERT INTO Etudiants VALUES(0010,'BERINER','Axel','M');
INSERT INTO Etudiants VALUES(0011,'BERET','Monique','F');
INSERT INTO Etudiants VALUES(0012,'BERTOUX','Florent','M');
INSERT INTO Etudiants VALUES(0013,'LAKANAL','Sacha','M');
INSERT INTO Etudiants VALUES(0014,'ENGLET','Gaetan','M');
INSERT INTO Etudiants VALUES(0015,'GENOVA','David','M');
INSERT INTO Etudiants VALUES(0016,'JOLIVETT','Pierre','M');
INSERT INTO Etudiants VALUES(0017,'KILT','Karine','F');
INSERT INTO Etudiants VALUES(0018,'DALLOS','Rebecca','F');
INSERT INTO Etudiants VALUES(0019,'DRIEUX','Anthony','M');
INSERT INTO Etudiants VALUES(0020,'GILLES','Camille','F');
INSERT INTO Etudiants VALUES(0021,'HERMET','Raoul','M');
INSERT INTO Etudiants VALUES(0022,'NAPOLI','Justine','F');
INSERT INTO Etudiants VALUES(0023,'SCHOLTES','Marie','F');
INSERT INTO Etudiants VALUES(0024,'WERNER','Jonathan','M');
INSERT INTO Etudiants VALUES(0025,'WALTER','Manon','F');
INSERT INTO Etudiants VALUES(0026,'BARDOT','Leanne','F');
INSERT INTO Etudiants VALUES(0027,'RICHARD','Arthur','M');
INSERT INTO Etudiants VALUES(0028,'LOUIS','Justine','F');
INSERT INTO Etudiants VALUES(0029,'COLIN','Laurent','M');
INSERT INTO Etudiants VALUES(0030,'ARMANET','Alain','M');
INSERT INTO Etudiants VALUES(0031,'POIRIER','Marc','M');
INSERT INTO Etudiants VALUES(0032,'CERVELLE','Guillaume','M');
INSERT INTO Etudiants VALUES(0033,'DUBOC','Oceane','F');
INSERT INTO Etudiants VALUES(0034,'FELTIN','Chloe','F');
INSERT INTO Etudiants VALUES(0035,'MAGRON','Charles','M');
INSERT INTO Etudiants VALUES(0036,'MASON','Pauline','F');
INSERT INTO Etudiants VALUES(0038,'PERRIN','Anais','F');
INSERT INTO Etudiants VALUES(0037,'BARBEI','Arthur','M');
INSERT INTO Etudiants VALUES(0039,'BETOUS','Cyrille','M');
INSERT INTO Etudiants VALUES(0040,'BONNEAUD','Remy','M');
INSERT INTO Etudiants VALUES(0041,'LEDRU','Alex','M');
INSERT INTO Etudiants VALUES(0042,'MAIRE','Paul','M');
INSERT INTO Etudiants VALUES(0043,'MALLET','Tanguy','M');
INSERT INTO Etudiants VALUES(0044,'MARTIN','Paul','M');
INSERT INTO Etudiants VALUES(0045,'RAMANANA','Liva','F');
INSERT INTO Etudiants VALUES(0046,'PALEO','Michel','M');
INSERT INTO Etudiants VALUES(0047,'POUILLE','Malik','M');
INSERT INTO Etudiants VALUES(0048,'PERRY','Maurice','M');
INSERT INTO Etudiants VALUES(0049,'PAMIER','Marc','M');
INSERT INTO Etudiants VALUES(0050,'PARIS','Marc','M');
INSERT INTO Etudiants VALUES(0051,'MAURICE','Pierre','M');
INSERT INTO Etudiants VALUES(0052,'MATELOT','Paul','M');
INSERT INTO Etudiants VALUES(0053,'MANGIN','Peter','M');
INSERT INTO Etudiants VALUES(0054,'MENARD','Pascal','M');
INSERT INTO Etudiants VALUES(0055,'MORISSOT','Piotr','M');
INSERT INTO Etudiants VALUES(0056,'MOUILLERON','Philippe','M');



Create table Livres(titre varchar(100) primary key,auteur varchar(20),nb_pages Number(4));

INSERT INTO Livres VALUES('Le Cid','Corneille',192);
INSERT INTO Livres VALUES('Les hommes qui n''aimaient pas les femmes','Larsson',574);
INSERT INTO Livres VALUES('La fille qui revait d''un bidon d''essence et d''une allumette','Larsson',652);
INSERT INTO Livres VALUES('La reine dans le palais des courants d''air','Larsson',711);
INSERT INTO Livres VALUES('L''informatique pour les 1ere S curieux','Carmin',150);
INSERT INTO Livres VALUES('La peinture c''est le pied','Picasso',150);
INSERT INTO Livres VALUES('SQL les fondamentaux du langage','Godoc',430);
INSERT INTO Livres VALUES('Le petit Prince','Saint-Exupery',115);
INSERT INTO Livres VALUES('Si c''est un homme','Levi',213);
INSERT INTO Livres VALUES('La loi c''est pas moi','Anonyme',28);
INSERT INTO Livres VALUES('Autant en emporte l''event','Baleine',100);
INSERT INTO Livres VALUES('Carnage sous les tongs','Molto',12345);

Create TABLE Programme(filiere varchar(30), niveau varchar(2), matiere varchar(20), nb_heures Number(2));

INSERT INTO Programme VALUES('Informatique','L1','Algorithmique',4);
INSERT INTO Programme VALUES('Informatique','L2','Bases de Donnees',2);
INSERT INTO Programme VALUES('Informatique','L3','Programmation orientee objets',3);
INSERT INTO Programme VALUES('Informatique','M1','Systemes distribues',2);
INSERT INTO Programme VALUES('Informatique','M2','Verification algorithmique',2);
INSERT INTO Programme VALUES('Sciences pour l''ingenieur','L1','Physique',4);
INSERT INTO Programme VALUES('Sciences pour l''ingenieur','L2','Mecanique du point',3);
INSERT INTO Programme VALUES('Sciences pour l''ingenieur','L3','Anglais',1);
INSERT INTO Programme VALUES('Sciences pour l''ingenieur','L1','Mathematiques',2);
INSERT INTO Programme VALUES('Sciences du langage','L1','Grammaire constructive',2);
INSERT INTO Programme VALUES('Sciences du langage','L2','Dynamique du larynx',2);
INSERT INTO Programme VALUES('Sciences de l''education','L1','Pedagogie appliquee',2);
INSERT INTO Programme VALUES('Sciences de l''education','L2','Faire un cours de L1',1);

Create TABLE Inscriptions(num_et Number(4), annee Number(4), filiere Varchar(30), niveau Varchar(2), constraint i_pk primary key (num_et,annee));

INSERT INTO Inscriptions VALUES(7,2012,'Informatique','L1');
INSERT INTO Inscriptions VALUES(1,2010,'Informatique','L2');
INSERT INTO Inscriptions VALUES(2,2011,'Informatique','L3');
INSERT INTO Inscriptions VALUES(4,2009,'Informatique','L1');
INSERT INTO Inscriptions VALUES(6,2012,'Informatique','L2');
INSERT INTO Inscriptions VALUES(5,2011,'Informatique','L3');
INSERT INTO Inscriptions VALUES(3,2011,'Informatique','L1');
INSERT INTO Inscriptions VALUES(7,2011,'Informatique','L1');
INSERT INTO Inscriptions VALUES(1,2009,'Informatique','L1');
INSERT INTO Inscriptions VALUES(2,2010,'Informatique','L3');
INSERT INTO Inscriptions VALUES(6,2011,'Informatique','L1');
INSERT INTO Inscriptions VALUES(5,2010,'Informatique','L2');
INSERT INTO Inscriptions VALUES(8,2012,'Sciences pour l''ingenieur','L3');
INSERT INTO Inscriptions VALUES(9,2011,'Sciences pour l''ingenieur','L2');
INSERT INTO Inscriptions VALUES(10,2010,'Sciences pour l''ingenieur','L1');
INSERT INTO Inscriptions VALUES(11,2009,'Sciences pour l''ingenieur','L2');
INSERT INTO Inscriptions VALUES(12,2011,'Sciences pour l''ingenieur','L2');
INSERT INTO Inscriptions VALUES(13,2011,'Sciences pour l''ingenieur','L3');
INSERT INTO Inscriptions VALUES(14,2010,'Sciences pour l''ingenieur','L3');
INSERT INTO Inscriptions VALUES(15,2012,'Sciences pour l''ingenieur','L1');
INSERT INTO Inscriptions VALUES(16,2012,'Sciences pour l''ingenieur','L1');
INSERT INTO Inscriptions VALUES(17,2010,'Sciences pour l''ingenieur','L2');
INSERT INTO Inscriptions VALUES(18,2007,'Sciences pour l''ingenieur','L3');
INSERT INTO Inscriptions VALUES(19,2008,'Sciences pour l''ingenieur','L2');
INSERT INTO Inscriptions VALUES(20,2010,'Sciences pour l''ingenieur','L1');
INSERT INTO Inscriptions VALUES(21,2010,'Sciences de l''education','L1');
INSERT INTO Inscriptions VALUES(22,2010,'Sciences de l''education','L2');
INSERT INTO Inscriptions VALUES(24,2010,'Sciences pour l''ingenieur','L1');
INSERT INTO Inscriptions VALUES(23,2010,'Sciences de l''education','L2');
INSERT INTO Inscriptions VALUES(25,2010,'Sciences de l''education','L1');
INSERT INTO Inscriptions VALUES(26,2010,'Sciences de l''education','L3');
INSERT INTO Inscriptions VALUES(27,2010,'Sciences de l''education','L3');
INSERT INTO Inscriptions VALUES(28,2010,'Sciences de l''education','L1');
INSERT INTO Inscriptions VALUES(29,2010,'Sciences de l''education','L1');
INSERT INTO Inscriptions VALUES(30,2010,'Sciences de l''education','L3');
INSERT INTO Inscriptions VALUES(31,2010,'Sciences de l''education','L1');
INSERT INTO Inscriptions VALUES(32,2010,'Sciences de l''education','L2');
INSERT INTO Inscriptions VALUES(33,2010,'Sciences de l''education','L1');
INSERT INTO Inscriptions VALUES(34,2010,'Sciences du langage','L1');
INSERT INTO Inscriptions VALUES(35,2010,'Sciences du langage','L3');
INSERT INTO Inscriptions VALUES(36,2010,'Sciences du langage','L1');
INSERT INTO Inscriptions VALUES(37,2010,'Sciences du langage','L2');
INSERT INTO Inscriptions VALUES(38,2010,'Sciences du langage','L1');
INSERT INTO Inscriptions VALUES(39,2010,'Sciences du langage','L2');
INSERT INTO Inscriptions VALUES(40,2010,'Sciences du langage','L1');
INSERT INTO Inscriptions VALUES(41,2010,'Sciences du langage','L3');
INSERT INTO Inscriptions VALUES(42,2010,'Sciences du langage','L3');
INSERT INTO Inscriptions VALUES(43,2010,'Sciences du langage','L1');
INSERT INTO Inscriptions VALUES(44,2010,'Sciences du langage','L2');

INSERT INTO Inscriptions VALUES(46,2012,'Sciences de l''education','L1');
INSERT INTO Inscriptions VALUES(48,2012,'Sciences de l''education','L1');
INSERT INTO Inscriptions VALUES(51,2012,'Sciences de l''education','L1');
INSERT INTO Inscriptions VALUES(52,2013,'Sciences de l''education','L1');
INSERT INTO Inscriptions VALUES(56,2014,'Sciences de l''education','L1');
INSERT INTO Inscriptions VALUES(54,2012,'Sciences de l''education','L1');
INSERT INTO Inscriptions VALUES(47,2013,'Sciences du langage','L1');
INSERT INTO Inscriptions VALUES(49,2012,'Sciences du langage','L1');
INSERT INTO Inscriptions VALUES(50,2013,'Sciences du langage','L1');
INSERT INTO Inscriptions VALUES(53,2012,'Sciences du langage','L1');
INSERT INTO Inscriptions VALUES(55,2012,'Sciences du langage','L1');


Create table Diplomes (num_diplome Number(5) primary key, num_et Number(4), date_d date, filiere Varchar(30), niveau Varchar(2), mention Varchar(2));

INSERT INTO Diplomes VALUES(1,1,'2010-06-25','Informatique','L2','P');
INSERT INTO Diplomes VALUES(2,2,'2013-06-12','Informatique','L3','B');
INSERT INTO Diplomes VALUES(3,6,'2012-06-21','Informatique','L2','AB');
INSERT INTO Diplomes VALUES(4,5,'2011-06-12','Informatique','L3','P');
INSERT INTO Diplomes VALUES(5,8,'2012-06-21','Sciences pour l''ingenieur','L3','AB');
INSERT INTO Diplomes VALUES(12,9,'2011-06-12','Sciences pour l''ingenieur','L2','P');
INSERT INTO Diplomes VALUES(13,11,'2009-06-22','Sciences pour l''ingenieur','L2','P');
INSERT INTO Diplomes VALUES(14,12,'2011-06-12','Sciences pour l''ingenieur','L2','AB');
INSERT INTO Diplomes VALUES(15,13,'2011-06-12','Sciences pour l''ingenieur','L3','P');
INSERT INTO Diplomes VALUES(16,14,'2012-06-21','Sciences pour l''ingenieur','L3','AB');
INSERT INTO Diplomes VALUES(6,17,'2013-06-20','Sciences pour l''ingenieur','L2','AB');
INSERT INTO Diplomes VALUES(7,18,'2012-06-21','Sciences pour l''ingenieur','L3','B');
INSERT INTO Diplomes VALUES(8,19,'2008-05-30','Sciences pour l''ingenieur','L2','P');
INSERT INTO Diplomes VALUES(9,22,'2010-06-20','Sciences de l''education','L2','P');
INSERT INTO Diplomes VALUES(10,23,'2014-06-20','Sciences de l''education','L1','B');
INSERT INTO Diplomes VALUES(11,24,'2013-06-20','Sciences de l''education','L2','TB');
INSERT INTO Diplomes VALUES(17,25,'2010-06-20','Sciences de l''education','L1','AB');
INSERT INTO Diplomes VALUES(18,26,'2013-06-20','Sciences de l''education','L3','P');
INSERT INTO Diplomes VALUES(19,27,'2014-02-20','Sciences de l''education','L3','P');
INSERT INTO Diplomes VALUES(20,30,'2012-06-21','Sciences de l''education','L3','AB');
INSERT INTO Diplomes VALUES(21,31,'2013-06-20','Sciences de l''education','L1','P');
INSERT INTO Diplomes VALUES(22,32,'2010-06-20','Sciences de l''education','L2','B');
INSERT INTO Diplomes VALUES(23,34,'2010-06-20','Sciences du langage','L1','AB');
INSERT INTO Diplomes VALUES(24,35,'2010-06-20','Sciences du langage','L3','AB');
INSERT INTO Diplomes VALUES(25,38,'2014-02-20','Sciences du langage','L1','P');
INSERT INTO Diplomes VALUES(26,39,'2010-06-20','Sciences du langage','L2','B');
INSERT INTO Diplomes VALUES(27,0,'2014-02-20','Sciences du langage','L1','P');
INSERT INTO Diplomes VALUES(28,41,'2010-06-20','Sciences du langage','L3','B');
INSERT INTO Diplomes VALUES(29,42,'2013-06-20','Sciences du langage','L3','P');
INSERT INTO Diplomes VALUES(33,44,'2013-06-20','Sciences du langage','L3','P');
INSERT INTO Diplomes VALUES(30,32,'2012-06-20','Sciences de l''education','L3','P');
INSERT INTO Diplomes VALUES(31,29,'2013-06-20','Sciences de l''education','L3','P');
INSERT INTO Diplomes VALUES(34,46,'2013-06-20','Sciences de l''education','L1','P');
INSERT INTO Diplomes VALUES(35,48,'2013-06-20','Sciences de l''education','L1','P');
INSERT INTO Diplomes VALUES(36,51,'2013-06-20','Sciences de l''education','L1','P');
INSERT INTO Diplomes VALUES(37,52,'2013-06-20','Sciences de l''education','L1','P');
INSERT INTO Diplomes VALUES(38,56,'2013-06-20','Sciences de l''education','L1','P');
INSERT INTO Diplomes VALUES(39,54,'2013-06-20','Sciences de l''education','L1','P');
INSERT INTO Diplomes VALUES(39,47,'2013-06-20','Sciences du langage','L1','P');
INSERT INTO Diplomes VALUES(40,49,'2013-06-20','Sciences du langage','L1','P');
INSERT INTO Diplomes VALUES(41,50,'2013-06-20','Sciences du langage','L1','P');
INSERT INTO Diplomes VALUES(42,53,'2013-06-20','Sciences du langage','L1','P');
INSERT INTO Diplomes VALUES(43,55,'2013-06-20','Sciences du langage','L1','P');

CREATE TABLE Emprunts (titre Varchar(100), num_et Number(4), date_emp date, date_ret date,constraint Emp_pk primary key (titre,num_et));

INSERT INTO Emprunts VALUES ('Le Cid',31,'2012-12-26','2013-01-12');
INSERT INTO Emprunts (titre,num_et,date_emp) VALUES ('Le Cid',32,'2013-03-26');
INSERT INTO Emprunts VALUES ('Le Cid',12,'2012-11-14','2012-11-25');
INSERT INTO Emprunts VALUES ('SQL les fondamentaux du langage',32,'2013-03-26','2013-04-08');
INSERT INTO Emprunts VALUES ('SQL les fondamentaux du langage',11,'2012-12-28','2013-01-12');
INSERT INTO Emprunts (titre,num_et,date_emp) VALUES ('SQL les fondamentaux du langage',17,'2013-04-08');
INSERT INTO Emprunts VALUES ('Carnage sous les tong',15,'2012-11-26','2012-12-01');
INSERT INTO Emprunts (titre,num_et,date_emp) VALUES ('Carnage sous les tong',3,'2013-02-26');
INSERT INTO Emprunts VALUES ('Carnage sous les tong',27,'2012-10-26','2012-10-27');
INSERT INTO Emprunts VALUES ('Carnage sous les tong',19,'2012-09-26','2012-10-04');
INSERT INTO Emprunts VALUES ('La peinture c''est le pied',19,'2012-09-26','2012-11-04');
INSERT INTO Emprunts VALUES ('La peinture c''est le pied',22,'2013-01-26','2013-02-04');
INSERT INTO Emprunts VALUES ('L''informatique pour les 1ere S curieux',7,'2012-11-17','2012-11-18');
INSERT INTO Emprunts VALUES ('L''informatique pour les 1ere S curieux',16,'2013-01-22','2013-02-04');
INSERT INTO Emprunts VALUES ('Le Cid',24,'2010-04-23','2011-05-25');
INSERT INTO Emprunts VALUES ('Le Cid',1,'2012-06-14','2011-07-01');
INSERT INTO Emprunts VALUES ('Le Cid',2,'2010-06-14','2011-07-01');
INSERT INTO Emprunts VALUES ('Le Cid',3,'2009-06-14','2011-07-01');
INSERT INTO Emprunts VALUES ('Le Cid',4,'2013-06-14','2011-07-01');
INSERT INTO Emprunts VALUES ('Le Cid',30,'2011-07-14','2011-08-01');
INSERT INTO Emprunts VALUES ('Le Cid',5,'2011-08-14','2011-09-01');
INSERT INTO Emprunts VALUES ('Le Cid',6,'2011-09-14','2011-10-01');

