Requete.sql

DROP TABLE IF EXISTS COMPO;
DROP TABLE IF EXISTS EMPRUNT;
DROP TABLE IF EXISTS LIVRE;
DROP TABLE IF EXISTS CLIENT;

CREATE TABLE CLIENT (
  ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  NOM VARCHAR(50) NOT NULL,
  PRENOM VARCHAR(50) NOT NULL
);

CREATE TABLE LIVRE (
  ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  TITRE VARCHAR(255) NOT NULL,
  AUTEUR VARCHAR(50) NOT NULL
);

CREATE TABLE EMPRUNT (
  ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  DATE_DEBUT DATETIME NOT NULL,
  DATE_FIN DATETIME NULL,
  DELAI INT,
  ID_CLIENT INT NOT NULL
);

CREATE TABLE COMPO (
  ID_LIV INT NOT NULL,
  ID_EMP INT NOT NULL
);

INSERT INTO CLIENT (NOM, PRENOM) VALUES ('Brigand', 'Pierre');
INSERT INTO CLIENT (NOM, PRENOM) VALUES ('YU', 'Cheng');
INSERT INTO CLIENT (NOM, PRENOM) VALUES ('BERRAD', 'Hicham');

INSERT INTO LIVRE (TITRE, AUTEUR) VALUES ('Vingt mille lieues sous les mers', 'Jules Verne');
INSERT INTO LIVRE (TITRE, AUTEUR) VALUES ('Germinal', 'Emile Zola');
INSERT INTO LIVRE (TITRE, AUTEUR) VALUES ('Guerre et paix', 'Léon Tolstoï');
INSERT INTO LIVRE (TITRE, AUTEUR) VALUES ('Apprendre à parler aux animaux', 'Gaston Pouet');
INSERT INTO LIVRE (TITRE, AUTEUR) VALUES ('1001 recettes de Cuisine', 'Jean-Pierre Coffe');

INSERT INTO EMPRUNT (DATE_DEBUT, DATE_FIN, DELAI, ID_CLIENT)
VALUES ('2017-11-12 00:00:00', '2017-11-18 00:00:00', 15, 1);

INSERT INTO EMPRUNT (DATE_DEBUT, DATE_FIN, DELAI, ID_CLIENT)
VALUES ('2017-12-08 00:00:00', '2017-12-23 00:00:00', 30, 2);

INSERT INTO EMPRUNT (DATE_DEBUT, DATE_FIN, DELAI, ID_CLIENT)
VALUES ('2017-12-09 00:00:00', '2018-01-04 00:00:00', 30, 3);

INSERT INTO EMPRUNT (DATE_DEBUT, DATE_FIN, DELAI, ID_CLIENT)
VALUES ('2018-01-03 00:00:00', NULL, 21, 1);

INSERT INTO EMPRUNT (DATE_DEBUT, DATE_FIN, DELAI, ID_CLIENT)
VALUES ('2018-01-13 00:00:00', NULL, 21, 3);

INSERT INTO COMPO (ID_LIV, ID_EMP) VALUES (1, 1);
INSERT INTO COMPO (ID_LIV, ID_EMP) VALUES (4, 1);
INSERT INTO COMPO (ID_LIV, ID_EMP) VALUES (2, 2);
INSERT INTO COMPO (ID_LIV, ID_EMP) VALUES (3, 2);
INSERT INTO COMPO (ID_LIV, ID_EMP) VALUES (1, 3);
INSERT INTO COMPO (ID_LIV, ID_EMP) VALUES (5, 4);
INSERT INTO COMPO (ID_LIV, ID_EMP) VALUES (2, 4);
INSERT INTO COMPO (ID_LIV, ID_EMP) VALUES (3, 5);