CREATE SCHEMA db_kerhir_tls;
use db_kerhir_tls;

CREATE TABLE langage
(
langage_id INT AUTO_INCREMENT PRIMARY KEY,
langage_name VARCHAR(255)
);

CREATE TABLE ecole
(
ecole_id INT AUTO_INCREMENT PRIMARY KEY,
ecole_ville VARCHAR(255)
);

CREATE TABLE eleve
(
eleve_id INT AUTO_INCREMENT PRIMARY KEY,
eleve_nom VARCHAR(255),
eleve_prenom VARCHAR(255),
langage_eleve INT NOT NULL,
ecole_eleve INT NOT NULL,
FOREIGN KEY(ecole_eleve) REFERENCES ecole(ecole_id),
FOREIGN KEY(langage_eleve) REFERENCES langage(langage_id)
);

INSERT INTO langage VALUES (null, "PHP");
INSERT INTO langage VALUES (null, "Java");
INSERT INTO langage VALUES (null, "JavaScript");

INSERT INTO ecole VALUES (null, "Toulouse");
INSERT INTO ecole VALUES (null, "Lyon");
INSERT INTO ecole VALUES (null, "Bordeaux");
INSERT INTO ecole VALUES (null, "Biarritz");


INSERT INTO eleve VALUES (null, "Google", "Copain", 1, 2);
INSERT INTO eleve VALUES (null, "LAURIE", "LAURIE", 1, 4);
INSERT INTO eleve VALUES (null, "Ta", "Maman", 3, 1);
INSERT INTO eleve VALUES (null, "I'm", "Batman", 2, 1);
INSERT INTO eleve VALUES (null, "No you're", "not", 2, 3);

/*Quelques requêtes pour aider à la correction :-) */

SELECT * FROM eleve;
SELECT * FROM langage;
SELECT * FROM ecole;

SELECT eleve.eleve_id, eleve.eleve_nom, eleve.eleve_prenom, langage.langage_name, ecole.ecole_ville
FROM eleve
INNER JOIN langage ON eleve.langage_eleve = langage.langage_id
INNER JOIN ecole ON eleve.ecole_eleve = ecole.ecole_id;