-- SQL pour les tables Etudiant, Cours et Suit

BEGIN TRANSACTION;



DROP TABLE IF EXISTS etudiant;

CREATE TABLE etudiant
  (numetud	TEXT	PRIMARY KEY,
   nom		TEXT	NOT NULL,
   prenom	TEXT	NOT NULL,
   adresse	TEXT	NULL,
   datenais	DATE	NULL,
   sexe	TEXT	NULL
  );

INSERT INTO etudiant VALUES('e1','Cousy','Cécile',null,null,'F');
INSERT INTO etudiant VALUES('e2','Pourcel','Mathieu',null,null,'M');
INSERT INTO etudiant VALUES('e3','Burgy','Laurent',null,null,'M');



DROP TABLE IF EXISTS cours;

CREATE TABLE cours
  (numcours	TEXT	PRIMARY KEY,
   nomcours	TEXT	NOT NULL,
   nomprof	TEXT	NOT NULL,
   nbhc	REAL	NULL,
   nbhtd	REAL	NULL,
   nbhtp	REAL	NULL
  );

INSERT INTO cours VALUES('c1','Java','Munier',12,10.5,15);
INSERT INTO cours VALUES('c2','Réseau','Bascou',24,30,30);
INSERT INTO cours VALUES('c3','TransNum','Baillot',12,18,24);



DROP TABLE IF EXISTS suit;

CREATE TABLE suit
  (numetud	TEXT	NOT NULL,
   numcours	TEXT	NOT NULL,
   note	REAL	NOT NULL,
   PRIMARY KEY (numetud,numcours)
  );

INSERT INTO suit VALUES('e1','c1',18.5);
INSERT INTO suit VALUES('e1','c2',15);
INSERT INTO suit VALUES('e2','c1',9.5);



COMMIT;

