CREATE TABLE etudiant (
	id INT NOT NULL AUTO_INCREMENT, 
	nom VARCHAR(30) NOT NULL,
	prenom VARCHAR(30) NOT NULL,
	date_naissance DATE NOT NULL,
	ine VARCHAR(15) NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE cours (
	id INT NOT NULL AUTO_INCREMENT, 
	nom VARCHAR(30) NOT NULL,
	nb_cm INT NOT NULL,
	nb_td INT NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE inscription (
	id INT NOT NULL AUTO_INCREMENT, 
	id_etudiant INT NOT NULL,
	id_cours INT NOT NULL,
	note REAL NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (id_etudiant) REFERENCES etudiant(id),
	FOREIGN KEY (id_cours) REFERENCES cours(id)
);

INSERT INTO etudiant (nom, prenom, date_naissance, ine) 
VALUES ('dupont', 'paul', '19900101', '1abcdefg');
INSERT INTO etudiant (nom, prenom, date_naissance, ine) 
VALUES ('dubois', 'jean', '19900201', '2hijklmn');
INSERT INTO etudiant (nom, prenom, date_naissance, ine) 
VALUES ('deschamps', 'luc', '19900301', '3opqrst');

INSERT INTO cours (nom, nb_cm, nb_td) 
VALUES ('reseaux', 30, 30);
INSERT INTO cours (nom, nb_cm, nb_td) 
VALUES ('administration systeme', 20, 20);

INSERT INTO inscription (id_etudiant, id_cours, note) 
VALUES (1,1,15);
INSERT INTO inscription (id_etudiant, id_cours, note) 
VALUES (1,2,12);
INSERT INTO inscription (id_etudiant, id_cours, note) 
VALUES (2,2,16);
INSERT INTO inscription (id_etudiant, id_cours, note) 
VALUES (3,1,15);




