-- SQL pour les tables Fournisseur, Produit et Stock

BEGIN TRANSACTION;



DROP TABLE IF EXISTS etudiant;

CREATE TABLE etudiant
  (idEtud	INTEGER	PRIMARY KEY,
   nomEtud	TEXT		NOT NULL
  );

INSERT INTO etudiant VALUES(1,'Alice');
INSERT INTO etudiant VALUES(2,'Bob');
INSERT INTO etudiant VALUES(4,'Charlie');
INSERT INTO etudiant VALUES(5,'David');
INSERT INTO etudiant VALUES(6,'Elsa');
INSERT INTO etudiant VALUES(7,'Frank');
INSERT INTO etudiant VALUES(8,'Gary');
INSERT INTO etudiant VALUES(9,'Henry');
INSERT INTO etudiant VALUES(10,'Indiana');
INSERT INTO etudiant VALUES(11,'Joe');
INSERT INTO etudiant VALUES(12,'Kylie');
INSERT INTO etudiant VALUES(13,'Luke');
INSERT INTO etudiant VALUES(14,'Mike');



DROP TABLE IF EXISTS theme;

CREATE TABLE theme
  (idTheme	INTEGER	PRIMARY KEY,
   nomTheme	TEXT		NOT NULL
  );

INSERT INTO theme VALUES(1,'informatique');
INSERT INTO theme VALUES(2,'réseau');
INSERT INTO theme VALUES(3,'téléphonie');
INSERT INTO theme VALUES(4,'sécurité');



DROP TABLE IF EXISTS ville;

CREATE TABLE ville
  (idVille	INTEGER	PRIMARY KEY,
   nomVille	TEXT		NOT NULL
  );

INSERT INTO ville VALUES(1,'Bordeaux');
INSERT INTO ville VALUES(2,'Pau');
INSERT INTO ville VALUES(3,'Toulouse');
INSERT INTO ville VALUES(4,'Biarritz');
INSERT INTO ville VALUES(5,'Tarbes');
INSERT INTO ville VALUES(6,'Agen');



DROP TABLE IF EXISTS stage;

CREATE TABLE stage
  (idEtud	INTEGER	NOT NULL,
   idTheme	INTEGER	NOT NULL,
   idVille	INTEGER,
   note	REAL,
   PRIMARY KEY (idEtud,idTheme)
  );

INSERT INTO stage VALUES(1,2,3,15);
INSERT INTO stage VALUES(3,1,3,18);
INSERT INTO stage VALUES(4,1,2,12);
INSERT INTO stage VALUES(5,3,3,null);
INSERT INTO stage VALUES(7,1,1,null);
INSERT INTO stage VALUES(8,4,3,14);
INSERT INTO stage VALUES(9,1,5,15);
INSERT INTO stage VALUES(10,2,6,null);
INSERT INTO stage VALUES(11,4,1,19);
INSERT INTO stage VALUES(12,2,3,15);
INSERT INTO stage VALUES(13,4,4,null);
INSERT INTO stage VALUES(14,1,5,13);



COMMIT;

