-- SQL pour les tables du projets: cours, salle, prof, planning

BEGIN TRANSACTION;

-- Table cours

DROP TABLE IF EXISTS cours;

CREATE TABLE cours
  (numcours	INTEGER	PRIMARY KEY,
   idcours	TEXT		NOT NULL,
   libcours	TEXT		NULL
  );

INSERT INTO cours VALUES(1,'R201','Technologie de l''Internet');
INSERT INTO cours VALUES(2,'R204','Initiation à la téléphonie d''entreprise');
INSERT INTO cours VALUES(3,'SAE 21','SAE 21');
INSERT INTO cours VALUES(4,'R213','Mathématiques des systèmes numériques');
INSERT INTO cours VALUES(5,'R209','Initiation au développement Web');
INSERT INTO cours VALUES(6,'R214','Analyse mathématiques des signaux');
INSERT INTO cours VALUES(7,'R211','Expression, Culture, Communication');

INSERT INTO cours VALUES(501,'réunion','Présentation parcours BUT');

-- Table salle

DROP TABLE IF EXISTS salle;

CREATE TABLE salle
  (numsalle	INTEGER	PRIMARY KEY,
   descsalle	TEXT		NOT NULL
  );

INSERT INTO salle VALUES( 1,'Amphi BIO');
INSERT INTO salle VALUES( 2,'Salle de cours');
INSERT INTO salle VALUES( 3,'Téléformation');
INSERT INTO salle VALUES( 4,'TD6');
INSERT INTO salle VALUES( 5,'Réseaux');
INSERT INTO salle VALUES( 6,'Salle PC');
INSERT INTO salle VALUES( 7,'TD4');
INSERT INTO salle VALUES( 8,'Très Haut Débit');
INSERT INTO salle VALUES( 9,'Station');
INSERT INTO salle VALUES(10,'Projets');
INSERT INTO salle VALUES(11,'Réseaux optiques');
INSERT INTO salle VALUES(12,'TD Info');
INSERT INTO salle VALUES(13,'Téléphonie');

-- Table prof

DROP TABLE IF EXISTS prof;

CREATE TABLE prof
  (numprof	INTEGER	PRIMARY KEY,
   nomprof	TEXT		NOT NULL
  );

INSERT INTO prof VALUES(0,'accès libre');
INSERT INTO prof VALUES(1,'JJB');
INSERT INTO prof VALUES(2,'AA');
INSERT INTO prof VALUES(3,'PA');
INSERT INTO prof VALUES(4,'SS');
INSERT INTO prof VALUES(5,'SM');
INSERT INTO prof VALUES(6,'MM');
INSERT INTO prof VALUES(7,'FK');

-- Table planning

DROP TABLE IF EXISTS planning;

CREATE TABLE planning
  (numcreneau	INTEGER	PRIMARY KEY,
   debut	DATE		NOT NULL,
   fin		DATE		NOT NULL,
   numcours	INTEGER	NULL,
   numsalle	INTEGER	NULL,
   numprof	INTEGER	NULL
  );

INSERT INTO planning VALUES( 1,'2022-04-04 08:00:00','2022-04-04 09:30:00',1,1,1);
INSERT INTO planning VALUES( 2,'2022-04-04 09:30:00','2022-04-04 11:00:00',2,2,2);
INSERT INTO planning VALUES( 3,'2022-04-04 11:00:00','2022-04-04 12:30:00',3,3,3);
INSERT INTO planning VALUES( 4,'2022-04-04 13:30:00','2022-04-04 15:00:00',2,4,2);
INSERT INTO planning VALUES( 5,'2022-04-04 15:00:00','2022-04-04 18:00:00',1,5,1);
INSERT INTO planning VALUES( 6,'2022-04-04 18:00:00','2022-04-04 19:30:00',501,2,4);

INSERT INTO planning VALUES( 7,'2022-04-05 08:00:00','2022-04-05 11:00:00',5,6,5);
INSERT INTO planning VALUES( 8,'2022-04-05 11:00:00','2022-04-05 12:30:00',2,7,2);
INSERT INTO planning VALUES( 9,'2022-04-05 13:30:00','2022-04-05 15:00:00',3,8,0);
INSERT INTO planning VALUES(10,'2022-04-05 15:00:00','2022-04-05 18:00:00',5,9,6);

INSERT INTO planning VALUES(11,'2022-04-06 08:00:00','2022-04-06 09:30:00',3,10,0);
INSERT INTO planning VALUES(12,'2022-04-06 09:00:00','2022-04-06 11:00:00',3,11,0);
INSERT INTO planning VALUES(13,'2022-04-06 11:00:00','2022-04-06 12:30:00',6,12,5);
INSERT INTO planning VALUES(14,'2022-04-06 13:30:00','2022-04-06 15:00:00',7,3,7);
INSERT INTO planning VALUES(15,'2022-04-06 15:00:00','2022-04-06 18:00:00',2,13,2);

INSERT INTO planning VALUES(16,'2022-04-07 08:00:00','2022-04-07 09:30:00',6,2,5);
INSERT INTO planning VALUES(17,'2022-04-07 09:30:00','2022-04-07 11:00:00',5,12,6);
INSERT INTO planning VALUES(18,'2022-04-07 11:00:00','2022-04-07 12:30:00',4,12,5);

-- Validation de la transaction

COMMIT;

