-- create a table
CREATE TABLE Adresse(
  idAdress INTEGER PRIMARY KEY, ville TEXT, 
  rue TEXT, n INTEGER
);
CREATE TABLE Adherents(
  idAdherent INTEGER PRIMARY KEY, 
  nom TEXT, 
  prenom TEXT, 
  nbLivresEmpruntes INTEGER, 
  idAdress INTEGER, 
  CONSTRAINT fk_adress FOREIGN KEY(idAdress) REFERENCES Adresse(idAdress)
);
CREATE TABLE Genres(genre TEXT PRIMARY KEY);
CREATE TABLE Livre(
  ISBN TEXT PRIMARY KEY, 
  titre TEXT, 
  genre TEXT, 
  CONSTRAINT fk_genre FOREIGN KEY(genre) REFERENCES Genres(genre)
);
CREATE TABLE Auteur(
  ISBN TEXT, 
  nomAuteur TEXT, 
  prenomAuteur TEXT,
  PRIMARY KEY(ISBN, nomAuteur),
  CONSTRAINT fk_ISBN FOREIGN KEY(ISBN) REFERENCES Livre(ISBN)
);

CREATE TABLE Exemplaire(
  idLivre INTEGER, 
  ISBN TEXT, 
  statusLivre BOOLEAN,
  PRIMARY KEY(idLivre),
  CONSTRAINT fk_ISBNEx FOREIGN KEY(ISBN) REFERENCES Livre(ISBN)
);
CREATE TABLE Emprunt(
  idEmprunt INTEGER PRIMARY KEY, 
  idLivre INTEGER, 
  idAdherent INTEGER, 
  d_e INTEGER, 
  m_e INTEGER, 
  y_e INTEGER, 
  d_r INTEGER, 
  m_r INTEGER, 
  y_r INTEGER,
  retardEventuel BOOLEAN,
  CHECK (d_e != d_r),
  CONSTRAINT fk_idLivre FOREIGN KEY(idLivre) REFERENCES Exemplaire(idLivre),
  CONSTRAINT fk_idAdherent FOREIGN KEY(idAdherent) REFERENCES Adherents(idAdherent)
);
CREATE TABLE Commande(
  idCommande INTEGER PRIMARY KEY, 
  ISBNCommande TEXT, 
  idAdherent INTEGER, 
  status BOOLEAN, 
  y_c INTEGER, 
  m_c INTEGER, 
  d_c INTEGER, 
  CONSTRAINT fk_idAdherentC FOREIGN KEY(idAdherent) REFERENCES Adherents(idAdherent)
);
-- insert some values
insert into Adresse values (11201, 'Promyshlennaya', 'Memorial', '70');
insert into Adresse values (9927, 'Mandala', 'Rowland', '0');
insert into Adresse values (27812, 'Lichinga', 'Morningstar', '8198');
insert into Adresse values (26058, 'Tchamba', 'Nevada', '8');
insert into Adresse values (17554, 'Shaffa', 'School', '199');
insert into Adresse values (25579, 'Ravne', 'Northfield', '311');
insert into Adresse values (21720, 'Nangerang', 'Northport', '71259');
insert into Adresse values (1490, 'Nishiwaki', 'Mifflin', '9');
insert into Adresse values (22296, 'Toshloq', 'Truax', '31854');
insert into Adresse values (15031, 'Lhari', 'Briar Crest', '22963');
insert into Adresse values (22754, 'Kuandian', 'Killdeer', '27210');
insert into Adresse values (3418, 'Pora', 'Tennyson', '6869');
insert into Adresse values (11404, 'Dagkar', 'Duke', '2');
insert into Adresse values (8512, 'Macinhata da Seixa', 'Service', '83211');
insert into Adresse values (23714, 'Valença', 'Becker', '5');
insert into Adresse values (27446, 'Dún Laoghaire', 'Arkansas', '286');
insert into Adresse values (2486, 'Hikkaduwa', 'Pearson', '5406');
insert into Adresse values (27179, 'Mamu', 'Farragut', '7');
insert into Adresse values (5362, 'Huangtu', 'Cottonwood', '74991');
insert into Adresse values (15761, 'Ngrowo', 'Lighthouse Bay', '986');
insert into Adresse values (10314, 'Nakhodka', 'Kipling', '89678');
insert into Adresse values (24468, 'Nakajah', 'Trailsway', '92036');
insert into Adresse values (26557, 'Kannus', 'Bunker Hill', '271');
insert into Adresse values (13267, 'Qingfa', 'Darwin', '729');
insert into Adresse values (12259, 'Castêlo', 'Toban', '4');
insert into Adresse values (12546, 'Taiping', 'Mccormick', '77');
insert into Adresse values (18860, 'Ganggawang', 'Cascade', '472');
insert into Adresse values (24631, 'Gyōda', 'Crowley', '8425');
insert into Adresse values (21779, 'Kosmach', 'Arrowood', '593');
insert into Adresse values (9154, 'Zama', 'Blackbird', '07');
insert into Adresse values (12490, 'Gnosjö', 'Dexter', '67236');
insert into Adresse values (17833, 'Metapán', 'Florence', '94097');
insert into Adresse values (28200, 'Fuling', 'Havey', '0');
insert into Adresse values (22868, 'Puerto Princesa', 'Memorial', '955');
insert into Adresse values (9884, 'Matão', 'Nelson', '5');
insert into Adresse values (10998, 'Gnosjö', 'New Castle', '1');
insert into Adresse values (19598, 'Kawangkoan', 'Graceland', '13');
insert into Adresse values (3064, 'Nantang', 'Nobel', '38026');
insert into Adresse values (17548, 'Timezgadiouine', 'Miller', '946');
insert into Adresse values (11465, 'Kafr ash Shaykh', 'Susan', '36482');
insert into Adresse values (1966, 'Alvalade', 'Alpine', '7680');
insert into Adresse values (28939, 'Sandefjord', 'Shasta', '6628');
insert into Adresse values (28744, 'Lautaro', 'Di Loreto', '0');
insert into Adresse values (20235, 'Maesan', 'Bultman', '42');
insert into Adresse values (5055, 'Kolpashevo', 'Anniversary', '559');
insert into Adresse values (28507, 'Wuyang', 'Blue Bill Park', '544');
insert into Adresse values (14823, 'Feng’an', 'Mesta', '0');
insert into Adresse values (6694, 'Tilamuta', 'Summer Ridge', '98');
insert into Adresse values (29702, 'Baing', 'Mendota', '36255');
insert into Adresse values (5502, 'Staraya Ladoga', 'Pleasure', '694');
insert into Adresse values (34, 'Wangbuzhuang', 'Main', '216');
insert into Adresse values (28637, 'Acacías', 'Bunting', '09160');
insert into Adresse values (25171, 'Verd', 'Melody', '15');
insert into Adresse values (12390, 'Uchkulan', 'Jana', '13419');
insert into Adresse values (1046, 'Jiangwan', 'Cottonwood', '1104');
insert into Adresse values (19095, 'Burnham', 'Manufacturers', '31979');
insert into Adresse values (19778, 'Cicurug', 'Annamark', '651');
insert into Adresse values (14475, 'Soutocico', 'Del Mar', '91');
insert into Adresse values (17600, 'Lamadelaine', 'Declaration', '02');
insert into Adresse values (15956, 'Huangtan', 'Carey', '159');
insert into Adresse values (29560, 'Licupis', 'Acker', '672');
insert into Adresse values (1041, 'Caihe', 'Spenser', '97648');
insert into Adresse values (28026, 'Gaoping', 'Claremont', '82600');
insert into Adresse values (7728, 'Tres Arroyos', 'Westridge', '068');
insert into Adresse values (16389, 'Cheremnoye', 'Waubesa', '9');
insert into Adresse values (23923, 'Chumphon Buri', 'Katie', '72');
insert into Adresse values (6425, 'Xitou', 'Johnson', '435');
insert into Adresse values (21102, 'Marietta', 'Northridge', '33465');
insert into Adresse values (17615, 'Mabuttal East', 'Claremont', '5232');
insert into Adresse values (23918, 'Dřevohostice', 'West', '1583');
insert into Adresse values (2791, 'Valencia', 'Monterey', '740');
insert into Adresse values (23491, 'Sukadana', 'David', '97131');
insert into Adresse values (25253, 'Pilang', 'Welch', '462');
insert into Adresse values (11429, 'La Broquerie', 'Sutteridge', '4');
insert into Adresse values (29349, 'Holguín', 'Luster', '8804');
insert into Adresse values (17493, 'Monte Patria', 'Valley Edge', '67');
insert into Adresse values (3260, 'Teófilo Otoni', 'Superior', '9949');
insert into Adresse values (2709, 'Comapa', 'Logan', '63543');
insert into Adresse values (29012, 'Cikadondongdesa', 'Brentwood', '9180');
insert into Adresse values (8336, 'Alvito', 'Eagan', '0');
insert into Adresse values (15663, 'Landivisiau', 'Melby', '7');
insert into Adresse values (7829, 'Atsipópoulon', 'Gulseth', '3');
insert into Adresse values (21923, 'Umm Şalāl ‘Alī', 'Sullivan', '1494');
insert into Adresse values (5904, 'Susunan', 'Superior', '7904');
insert into Adresse values (13061, 'Avlónas', 'Sommers', '8228');
insert into Adresse values (2062, 'Tartu', 'Morrow', '01');
insert into Adresse values (19109, 'Sainte-Sophie', 'Golf', '44');
insert into Adresse values (2492, 'Nao', 'Sachs', '14');
insert into Adresse values (26845, 'Lokosovo', 'Acker', '4');
insert into Adresse values (17290, 'Korsakovo', 'Dixon', '97430');
insert into Adresse values (23304, 'Espumoso', 'Hoepker', '4');
insert into Adresse values (24739, 'Stamford', 'Heath', '000');
insert into Adresse values (22940, 'Sanjing', 'Hintze', '1');
insert into Adresse values (15820, 'Kanash', 'Loftsgordon', '54');
insert into Adresse values (6420, 'Sidaurip', 'North', '8884');
insert into Adresse values (3111, 'Tsagaanchuluut', 'Erie', '5361');
insert into Adresse values (27207, 'Fengjiang', 'Lerdahl', '625');
insert into Adresse values (28225, 'Oslo', 'Hanson', '9');
insert into Adresse values (4484, 'Spitsevka', 'Hazelcrest', '25');
insert into Adresse values (1584, 'Yanggu', 'Northland', '01');


insert into Adherents values (6595, 'Kinze', 'Gawen', 2884, 11201);
insert into Adherents values (8147, 'Beavington', 'Silvie', 936, 9927);
insert into Adherents values (938, 'Barrowcliff', 'Marlyn', 6645, 27812);
insert into Adherents values (619, 'Titterington', 'Lemmy', 7904, 26058);
insert into Adherents values (5625, 'Desorts', 'Igor', 7307, 17554);
insert into Adherents values (6114, 'Whittock', 'Evered', 5446, 25579);
insert into Adherents values (5880, 'Dorricott', 'Arin', 2997, 21720);
insert into Adherents values (5653, 'Hearnden', 'Alistair', 1992, 1490);
insert into Adherents values (6991, 'Kneel', 'Mehetabel', 4011, 22296);
insert into Adherents values (4521, 'Glayzer', 'Webb', 2003, 15031);
insert into Adherents values (1566, 'Fripp', 'Luca', 3871, 22754);
insert into Adherents values (8295, 'Fronzek', 'Omar', 2944, 3418);
insert into Adherents values (3865, 'O''Kennedy', 'Devina', 8141, 11404);
insert into Adherents values (8548, 'Renad', 'Claudie', 4018, 8512);
insert into Adherents values (355, 'Mellish', 'Sigfried', 1413, 23714);
insert into Adherents values (5719, 'Hazeup', 'Phillip', 6474, 27446);
insert into Adherents values (9563, 'Gosz', 'Arlene', 3747, 2486);
insert into Adherents values (9622, 'Penny', 'Merry', 8241, 27179);
insert into Adherents values (4871, 'Hearne', 'Parry', 7532, 5362);
insert into Adherents values (7770, 'Skinley', 'Mommy', 4263, 15761);
insert into Adherents values (7118, 'Rosindill', 'Roosevelt', 6026, 10314);
insert into Adherents values (8622, 'Kennerknecht', 'Melva', 6664, 24468);
insert into Adherents values (3805, 'Hovy', 'Calypso', 3146, 26557);
insert into Adherents values (7541, 'Tilsley', 'Waiter', 6083, 13267);
insert into Adherents values (9803, 'Caizley', 'Tess', 1923, 12259);
insert into Adherents values (6731, 'Dunkerley', 'Blayne', 7944, 12546);
insert into Adherents values (9106, 'Lenthall', 'Jorge', 3010, 18860);
insert into Adherents values (7445, 'Broadway', 'Kory', 7055, 24631);
insert into Adherents values (7241, 'Rowaszkiewicz', 'Molly', 9444, 21779);
insert into Adherents values (8312, 'McKissack', 'Any', 565, 9154);
insert into Adherents values (4469, 'Kelf', 'Joleen', 9918, 12490);
insert into Adherents values (7273, 'Haddrell', 'Fabiano', 194, 17833);
insert into Adherents values (2736, 'Crammy', 'Jorrie', 2918, 28200);
insert into Adherents values (9989, 'Torr', 'Ximenez', 8962, 22868);
insert into Adherents values (5845, 'Burtwhistle', 'Harrie', 954, 9884);
insert into Adherents values (771, 'Duffyn', 'Shayne', 3269, 10998);
insert into Adherents values (596, 'Fulks', 'Lynn', 3188, 19598);
insert into Adherents values (1472, 'Welds', 'Wesley', 2452, 3064);
insert into Adherents values (3605, 'Purvey', 'Patrick', 4520, 17548);
insert into Adherents values (3986, 'Burditt', 'Pieter', 3590, 11465);
insert into Adherents values (9061, 'Tunnick', 'Ashlan', 9029, 1966);
insert into Adherents values (5053, 'Gadaud', 'Alecia', 3, 28939);
insert into Adherents values (3355, 'Stothard', 'Stephen', 731, 28744);
insert into Adherents values (3378, 'Farrent', 'Ianthe', 1146, 20235);
insert into Adherents values (863, 'Coste', 'Lennie', 4053, 5055);
insert into Adherents values (2892, 'Knox', 'Felizio', 3512, 28507);
insert into Adherents values (3265, 'Corrao', 'Heath', 8273, 14823);
insert into Adherents values (5296, 'Ferencowicz', 'Alissa', 3965, 6694);
insert into Adherents values (1557, 'Franckton', 'Ryun', 3260, 29702);
insert into Adherents values (6995, 'Stiger', 'Antoine', 8584, 5502);
insert into Adherents values (5058, 'Juanes', 'Malcolm', 4116, 34);
insert into Adherents values (6280, 'Robus', 'Arliene', 9053, 28637);
insert into Adherents values (5438, 'Danett', 'Reeta', 2575, 25171);
insert into Adherents values (7245, 'Ricardin', 'Quill', 9223, 12390);
insert into Adherents values (2884, 'Curlis', 'Jerrilee', 235, 1046);
insert into Adherents values (3648, 'Rustidge', 'Mark', 3516, 19095);
insert into Adherents values (3335, 'ducarme', 'Thayne', 2391, 19778);
insert into Adherents values (3495, 'Giovannelli', 'Alisha', 2171, 14475);
insert into Adherents values (9740, 'Jepensen', 'Myer', 7461, 17600);
insert into Adherents values (6848, 'Lorrimer', 'Nanci', 2492, 15956);
insert into Adherents values (5961, 'Andres', 'Dorena', 3574, 29560);
insert into Adherents values (5078, 'Goldring', 'Pennie', 1561, 1041);
insert into Adherents values (8459, 'Emer', 'Sallie', 8605, 28026);
insert into Adherents values (5675, 'Raysdale', 'Viv', 227, 7728);
insert into Adherents values (4070, 'Lingner', 'Carena', 7103, 16389);
insert into Adherents values (4213, 'Garret', 'Anne', 4950, 23923);
insert into Adherents values (9715, 'Vautier', 'Manya', 2710, 6425);
insert into Adherents values (1025, 'Tumber', 'Domingo', 7341, 21102);
insert into Adherents values (7285, 'Pettigree', 'Rubi', 182, 17615);
insert into Adherents values (2200, 'Lodford', 'Johan', 9049, 23918);
insert into Adherents values (2871, 'Skill', 'Timothea', 5151, 2791);
insert into Adherents values (3041, 'Screen', 'Kamilah', 628, 23491);
insert into Adherents values (4275, 'Arnatt', 'Julina', 2929, 25253);
insert into Adherents values (1784, 'Sinden', 'Vernen', 3576, 11429);
insert into Adherents values (8044, 'Limbourne', 'Matthias', 7763, 29349);
insert into Adherents values (3673, 'Reihill', 'Rosaleen', 7602, 17493);
insert into Adherents values (8260, 'Isacoff', 'Guenevere', 5094, 3260);
insert into Adherents values (1944, 'Spiteri', 'Christian', 3248, 2709);
insert into Adherents values (6935, 'Kunzelmann', 'Killie', 5853, 29012);
insert into Adherents values (1071, 'Scrooby', 'Rufe', 6763, 8336);
insert into Adherents values (218, 'Hastilow', 'Cyrille', 328, 15663);
insert into Adherents values (4822, 'Rehme', 'Sadella', 2641, 7829);
insert into Adherents values (5211, 'Eltringham', 'Madalyn', 6777, 21923);
insert into Adherents values (7319, 'Gamlyn', 'Tadd', 3312, 5904);
insert into Adherents values (7090, 'Gainsburgh', 'Gloriana', 4260, 13061);
insert into Adherents values (2781, 'Aronowicz', 'Kinna', 2779, 2062);
insert into Adherents values (6192, 'Jurek', 'Hobey', 8613, 19109);
insert into Adherents values (5842, 'Mountlow', 'Frederich', 9470, 2492);
insert into Adherents values (144, 'Terbrug', 'Elaina', 2091, 26845);
insert into Adherents values (6157, 'Lawty', 'Sylvia', 9963, 17290);
insert into Adherents values (489, 'Whitcomb', 'Frants', 4585, 23304);
insert into Adherents values (2572, 'Wedlock', 'Bill', 9025, 24739);
insert into Adherents values (1268, 'Stempe', 'Mariejeanne', 8602, 22940);
insert into Adherents values (5949, 'Kordas', 'Nichole', 8911, 15820);
insert into Adherents values (6781, 'Pirrone', 'Joel', 1199, 6420);
insert into Adherents values (5758, 'Mossom', 'Lauren', 361, 3111);
insert into Adherents values (4725, 'Barczewski', 'Eleanor', 3173, 27207);
insert into Adherents values (5220, 'Woolley', 'Jerri', 6912, 28225);
insert into Adherents values (7489, 'Tidey', 'Floris', 4821, 4484);
insert into Adherents values (9785, 'Marsden', 'Jade', 933, 1584);

insert INTO Genres VALUES ('Drame');
INSERT INTO Genres VALUES ('Romance');
INSERT INTO Genres VALUES ('Action');
INSERT INTO Genres VALUES ('Historique');
INSERT INTO Genres VALUES ('Crime');
INSERT INTO Genres VALUES ('Sci-fi');
INSERT INTO Genres VALUES ('Comedie');
INSERT INTO Genres VALUES ('Tragedie');

insert into Livre values ('501829351-9', 'Extreme Days', 'Action');
insert into Livre values ('223221139-8', 'Pain & Gain', 'Action');
insert into Livre values ('810981100-0', 'Other Side of Bollywood, The', 'Historique');
insert into Livre values ('834263647-X', 'Joyeux Noël (Merry Christmas)', 'Drame');
insert into Livre values ('063411885-4', 'Hidden (a.k.a. Cache) (Caché)', 'Drame');
insert into Livre values ('235884331-8', 'Desperate Measures', 'Crime');
insert into Livre values ('465189689-8', 'Jerk, The', 'Comedie');
insert into Livre values ('747180041-0', 'Small Town Murder Songs', 'Crime');
insert into Livre values ('934867259-2', 'Hollywood Shuffle', 'Comedie');
insert into Livre values ('359716146-4', 'Screamers: The Hunting', 'Sci-Fi');
insert into Livre values ('776967000-4', 'Sting II, The', 'Comedie');
insert into Livre values ('845781550-4', 'Terrible Kids (Enfants terribles, Les) (Strange Ones, The)', 'Drame');
insert into Livre values ('632732359-6', 'Glenn Killing på Berns', 'Comedie');
insert into Livre values ('587206897-2', 'Sidekicks', 'Action');
insert into Livre values ('574881473-0', 'Death Race 2', 'Action');
insert into Livre values ('709690209-0', 'Seven Year Itch, The', 'Comedie');
insert into Livre values ('063894685-9', 'Raven, The', 'Comedie');
insert into Livre values ('678392507-2', 'Blood: The Last Vampire', 'Action');
insert into Livre values ('203769702-3', 'Passion of Ayn Rand, The', 'Historique');
insert into Livre values ('126538301-4', 'Alexander Nevsky (Aleksandr Nevskiy)', 'Action');
insert into Livre values ('731598169-1', 'Nobody''s Fool', 'Romance');
insert into Livre values ('380126912-4', 'Hitch Hikers Guide to the Galaxy, The', 'Sci-Fi');
insert into Livre values ('824960953-0', 'Puppet Master: The Legacy (Puppet Master 8)', 'Drame');
insert into Livre values ('701440639-6', 'Diary of a Shinjuku Thief (Shinjuku dorobo nikki)', 'Drame');
insert into Livre values ('982072862-2', 'The D Train', 'Comedie');
insert into Livre values ('637303016-4', 'Moment to Remember, A (Nae meorisokui jiwoogae)', 'Romance');
insert into Livre values ('070498457-1', 'Unrest', 'Tragedie');
insert into Livre values ('682477670-X', 'Brighton Rock', 'Crime');
insert into Livre values ('095631149-0', 'Loves of Pharaoh, The (Das Weib des Pharao)', 'Tragedie');
insert into Livre values ('186495818-9', 'Joneses, The', 'Comedie');
insert into Livre values ('636460656-3', 'Ishaqzaade', 'Action');
insert into Livre values ('015385441-3', 'Clint Eastwood: Out of the Shadows', 'Historique');
insert into Livre values ('421502790-X', 'Son of the White Mare', 'Romance');
insert into Livre values ('092191003-7', 'Secrets', 'Romance');
insert into Livre values ('733294870-2', 'CoTragedieds Bend the Knee (a.k.a. The Blue Hands)', 'Romance');
insert into Livre values ('412179175-4', 'Black Sea', 'Thriller');
insert into Livre values ('017325984-7', 'SherryBaby', 'Drame');
insert into Livre values ('675732987-5', 'Wicker Man, The', 'Drame');
insert into Livre values ('399884797-9', 'Vääpeli Körmy ja marsalkan sauva', 'Comedie');
insert into Livre values ('151008662-5', 'Bass AckTragedieds', 'Drame');
insert into Livre values ('803395034-X', 'Kind of Loving, A', 'Drame');
insert into Livre values ('742861754-5', 'Drive', 'Crime');
insert into Livre values ('964872382-6', 'Hard to Kill', 'Drame');
insert into Livre values ('689449469-X', 'Lake of Fire', 'Historique');
insert into Livre values ('520350915-8', 'Happy Endings', 'Drame');
insert into Livre values ('310168710-1', 'DMT: The Spirit Molecule', 'Historique');
insert into Livre values ('358408346-X', 'Monty Python Live at the Hollywood Bowl', 'Comedie');
insert into Livre values ('866880135-X', 'Runaway Train', 'Action');
insert into Livre values ('838915803-5', 'Train Ride to Hollywood', 'Comedie');
insert into Livre values ('906142178-0', 'Shag', 'Comedie');
insert into Livre values ('596998938-X', '3096 Days', 'Drame');
insert into Livre values ('338890890-7', 'Long Ride Home, The', 'Romance');
insert into Livre values ('615667303-2', 'Giant of Marathon, The (Battaglia di Maratona, La)', 'Action');
insert into Livre values ('507627370-3', 'Red Beard (Akahige)', 'Drame');
insert into Livre values ('362763430-6', 'Jet Lag (Décalage horaire)', 'Comedie');
insert into Livre values ('820660663-7', 'Romeo and Juliet', 'Drame');
insert into Livre values ('853324072-4', 'In Your Dreams (Dans tes rêves)', 'Drame');
insert into Livre values ('583289540-1', 'Bitter Creek', 'Romance');
insert into Livre values ('894982290-3', 'Northwest', 'Drame');
insert into Livre values ('449114863-5', 'Cyberjack (Virtual Assassin)', 'Sci-Fi');
insert into Livre values ('492583151-8', 'Brave, The', 'Drame');
insert into Livre values ('628156655-6', 'Uninvited, The', 'Romance');
insert into Livre values ('586234690-2', 'Hitler''s Stealth Fighter', 'Historique');
insert into Livre values ('580735208-4', 'Keys to Tulsa', 'Crime');
insert into Livre values ('935773578-X', '***** Ride', 'Action');
insert into Livre values ('968206774-X', 'Angel Heart', 'Action');
insert into Livre values ('954810697-3', 'Red Firecracker, Green Firecracker (Pao Da Shuang Deng)', 'Drame');
insert into Livre values ('265515997-7', 'Cut', 'Comedie');
insert into Livre values ('871381468-0', 'Ace Ventura: Pet Detective', 'Comedie');
insert into Livre values ('365033079-2', 'Ricky Rapper (Risto Räppääjä)', 'Comedie');

insert into Auteur values ('501829351-9', 'Punt', 'Marget');
insert into Auteur values ('223221139-8', 'Geeraert', 'Ingeborg');
insert into Auteur values ('810981100-0', 'Challicum', 'Bartholemy');
insert into Auteur values ('834263647-X', 'Thormann', 'Jarrod');
insert into Auteur values ('063411885-4', 'Goudard', 'Cristy');
insert into Auteur values ('235884331-8', 'Patel', 'Tildy');
insert into Auteur values ('465189689-8', 'Messom', 'Beck');
insert into Auteur values ('747180041-0', 'Jaquet', 'Danni');
insert into Auteur values ('934867259-2', 'Blencoe', 'Dav');
insert into Auteur values ('359716146-4', '*****', 'Had');
insert into Auteur values ('776967000-4', 'Semeniuk', 'Fleur');
insert into Auteur values ('845781550-4', 'Ovendale', 'Ethelred');
insert into Auteur values ('632732359-6', 'Tuer', 'Jessie');
insert into Auteur values ('587206897-2', 'Sellens', 'Jacob');
insert into Auteur values ('574881473-0', 'McGivena', 'Joe');
insert into Auteur values ('709690209-0', 'Berks', 'Tracey');
insert into Auteur values ('063894685-9', 'MacQuaker', 'Jard');
insert into Auteur values ('678392507-2', 'Asbrey', 'Carrissa');
insert into Auteur values ('203769702-3', 'Massy', 'Morgan');
insert into Auteur values ('126538301-4', 'Rudledge', 'Edeline');
insert into Auteur values ('731598169-1', 'Kenner', 'Malvin');
insert into Auteur values ('380126912-4', 'Venart', 'Chickie');
insert into Auteur values ('824960953-0', 'Abrey', 'Pincus');
insert into Auteur values ('701440639-6', 'Gaylord', 'Fidel');
insert into Auteur values ('982072862-2', 'McCulley', 'Westley');
insert into Auteur values ('637303016-4', 'Vivers', 'Amelina');
insert into Auteur values ('070498457-1', 'Mara', 'Sancho');
insert into Auteur values ('682477670-X', 'Yerborn', 'Robbin');
insert into Auteur values ('095631149-0', 'Wahner', 'Susette');
insert into Auteur values ('186495818-9', 'Refford', 'Alfonse');
insert into Auteur values ('636460656-3', 'Proven', 'Cammy');
insert into Auteur values ('015385441-3', 'Touzey', 'Goldarina');
insert into Auteur values ('421502790-X', 'Slym', 'Elayne');
insert into Auteur values ('092191003-7', 'Swyne', 'Dacey');
insert into Auteur values ('733294870-2', 'Carolan', 'Andree');
insert into Auteur values ('412179175-4', 'Carolan', 'Andree');
insert into Auteur values ('017325984-7', 'Carolan', 'Andree');
insert into Auteur values ('675732987-5', 'Carolan', 'Andree');
insert into Auteur values ('399884797-9', 'McGarel', 'Gillian');
insert into Auteur values ('151008662-5', 'Maffi', 'Faina');
insert into Auteur values ('803395034-X', 'Ohlsen', 'Nickolaus');
insert into Auteur values ('742861754-5', 'Brewitt', 'Anatol');
insert into Auteur values ('964872382-6', 'Duiged', 'Gratiana');
insert into Auteur values ('689449469-X', 'Lawrenz', 'Cyrillus');
insert into Auteur values ('520350915-8', 'Hazeley', 'Tyson');
insert into Auteur values ('310168710-1', 'Hollow', 'Gilli');
insert into Auteur values ('358408346-X', 'Bigmore', 'Thatcher');
insert into Auteur values ('866880135-X', 'Emmot', 'Ertha');
insert into Auteur values ('838915803-5', 'Cawson', 'Layney');
insert into Auteur values ('906142178-0', 'Cobbe', 'Adelbert');
insert into Auteur values ('596998938-X', 'Christmas', 'Gifford');
insert into Auteur values ('338890890-7', 'Christmas', 'Gifford');
insert into Auteur values ('615667303-2', 'Christmas', 'Gifford');
insert into Auteur values ('507627370-3', 'Christmas', 'Gifford');
insert into Auteur values ('362763430-6', 'Worboy', 'Anderea');
insert into Auteur values ('820660663-7', 'Parkman', 'Mortimer');
insert into Auteur values ('853324072-4', 'Blewitt', 'Kamilah');
insert into Auteur values ('583289540-1', 'Antoszczyk', 'Grove');
insert into Auteur values ('894982290-3', 'Latliff', 'Kaycee');
insert into Auteur values ('449114863-5', 'Boyda', 'Lorilyn');
insert into Auteur values ('492583151-8', 'M''Quharg', 'Elisabetta');
insert into Auteur values ('492583151-8', 'Jeans', 'Elsey');
insert into Auteur values ('628156655-6', 'Cree', 'Aldon');
insert into Auteur values ('586234690-2', 'Cree', 'Aldon');
insert into Auteur values ('580735208-4', 'Cree', 'Aldon');
insert into Auteur values ('935773578-X', 'Lauxmann', 'Clarence');
insert into Auteur values ('968206774-X', 'Ancell', 'Bald');
insert into Auteur values ('954810697-3', 'Korba', 'Immanuel');
insert into Auteur values ('265515997-7', 'Puddle', 'Bobette');
insert into Auteur values ('871381468-0', 'Puddle', 'Bobette');
insert into Auteur values ('365033079-2', 'Puddle', 'Bobette');

insert into Exemplaire values (22236, '501829351-9', 1);
insert into Exemplaire values (16402, '223221139-8', 1);
insert into Exemplaire values (23075, '810981100-0', 1);
insert into Exemplaire values (20000, '810981100-0', 1);
insert into Exemplaire values (22962, '834263647-X', 0);
insert into Exemplaire values (16264, '063411885-4', 1);
insert into Exemplaire values (28918, '235884331-8', 1);
insert into Exemplaire values (20939, '465189689-8', 1);
insert into Exemplaire values (11309, '747180041-0', 1);
insert into Exemplaire values (9660, '934867259-2', 0);
insert into Exemplaire values (10139, '359716146-4', 1);
insert into Exemplaire values (29961, '776967000-4', 1);
insert into Exemplaire values (1111, '845781550-4', 1);
insert into Exemplaire values (27482, '632732359-6', 1);
insert into Exemplaire values (4508, '587206897-2', 1);
insert into Exemplaire values (15658, '574881473-0', 0);
insert into Exemplaire values (22294, '709690209-0', 1);
insert into Exemplaire values (19948, '063894685-9', 1);
insert into Exemplaire values (28193, '678392507-2', 1);
insert into Exemplaire values (4064, '203769702-3', 1);
insert into Exemplaire values (28098, '126538301-4', 0);
insert into Exemplaire values (2418, '731598169-1', 1);
insert into Exemplaire values (25334, '380126912-4', 1);
insert into Exemplaire values (26362, '824960953-0', 1);
insert into Exemplaire values (23792, '701440639-6', 1);
insert into Exemplaire values (2658, '982072862-2', 1);
insert into Exemplaire values (18730, '637303016-4', 1);
insert into Exemplaire values (1787, '070498457-1', 0);
insert into Exemplaire values (21059, '682477670-X', 1);
insert into Exemplaire values (888, '095631149-0', 1);
insert into Exemplaire values (20460, '186495818-9', 1);
insert into Exemplaire values (12194, '636460656-3', 1);
insert into Exemplaire values (1246, '015385441-3', 1);
insert into Exemplaire values (27078, '421502790-X', 1);
insert into Exemplaire values (16930, '092191003-7', 1);
insert into Exemplaire values (17302, '733294870-2', 1);
insert into Exemplaire values (18159, '412179175-4', 1);
insert into Exemplaire values (23648, '017325984-7', 1);
insert into Exemplaire values (17905, '675732987-5', 1);
insert into Exemplaire values (770, '399884797-9', 1);
insert into Exemplaire values (26459, '151008662-5', 1);
insert into Exemplaire values (21753, '803395034-X', 1);
insert into Exemplaire values (11944, '742861754-5', 0);
insert into Exemplaire values (8847, '964872382-6', 1);
insert into Exemplaire values (2081, '689449469-X', 1);
insert into Exemplaire values (40, '520350915-8', 1);
insert into Exemplaire values (5413, '310168710-1', 1);
insert into Exemplaire values (1240, '358408346-X', 1);
insert into Exemplaire values (28956, '866880135-X', 1);
insert into Exemplaire values (1472, '838915803-5', 1);
insert into Exemplaire values (29666, '906142178-0', 1);
insert into Exemplaire values (13218, '596998938-X', 1);
insert into Exemplaire values (7867, '338890890-7', 0);
insert into Exemplaire values (27474, '615667303-2', 1);
insert into Exemplaire values (29035, '507627370-3', 1);
insert into Exemplaire values (10821, '362763430-6', 1);
insert into Exemplaire values (21330, '820660663-7', 0);
insert into Exemplaire values (22343, '853324072-4', 1);
insert into Exemplaire values (20495, '583289540-1', 1);
insert into Exemplaire values (29442, '894982290-3', 1);
insert into Exemplaire values (5970, '449114863-5', 1);
insert into Exemplaire values (59700, '449114863-5', 0);
insert into Exemplaire values (10753, '492583151-8', 1);
insert into Exemplaire values (24209, '628156655-6', 1);
insert into Exemplaire values (2111, '586234690-2', 1);
insert into Exemplaire values (15110, '580735208-4', 1);
insert into Exemplaire values (3229, '935773578-X', 0);
insert into Exemplaire values (25659, '968206774-X', 0);
insert into Exemplaire values (25249, '954810697-3', 0);
insert into Exemplaire values (12805, '265515997-7', 0);
insert into Exemplaire values (13198, '871381468-0', 0);
insert into Exemplaire values (15491, '365033079-2', 0);

insert into Emprunt values (1, 15491, 3041, 6, 9, 2022, 20, 9, 2022, 0);
insert into Emprunt values (2, 13198, 6848, 1, 6, 2022, 15, 6, 2022, 0);
insert into Emprunt values (3, 12805, 4070, 18, 4, 2022, 2, 5, 2022, 0);
insert into Emprunt values (4, 25249, 8260, 24, 10, 2022, 8, 11, 2022, 0);
insert into Emprunt values (5, 25659, 5653, 19, 1, 2022, 2, 2, 2022, 0);
insert into Emprunt values (6, 3229, 5719, 3, 7, 2022, 17, 7, 2022, 0);
insert into Emprunt values (7, 11944, 4871, 13, 6, 2022, 27, 6, 2022, 0);
insert into Emprunt values (8, 7867, 9106, 14, 9, 2022, 28, 9, 2022, 0);
insert into Emprunt values (9, 15658, 1025, 9, 2, 2022, 23, 2, 2022, 0);
insert into Emprunt values (10, 22962, 2871, 4, 9, 2022, 18, 9, 2022, 0);
insert into Emprunt values (11, 9660, 7245, 3, 2, 2022, 17, 2, 2022, 0);
insert into Emprunt values (12, 1787, 7245, 5, 11, 2022, 19, 11, 2022, 0);
insert into Emprunt values (13, 21330, 7245, 3, 10, 2022, 17, 10, 2022, 0);
insert into Emprunt values (14, 59700, 4213, 28, 11, 2022, 11, 12, 2022, 0);
insert into Emprunt values (15, 28098, 4213, 10, 10, 2022, 24, 10, 2022, 0);

insert into Commande values (1, '134970500-4', 5880, 1, 2023, 5, 18);
insert into Commande values (2, '991355370-9', 5880, 1, 2022, 11, 13);
insert into Commande values (3, '893362393-0', 7118, 1, 2022, 6, 15);
insert into Commande values (4, '088092379-2', 7118, 1, 2023, 6, 5);
insert into Commande values (5, '441018449-0', 7118, 1, 2023, 8, 27);
insert into Commande values (6, '492160372-3', 2572, 1, 2022, 8, 18);
insert into Commande values (7, '523209203-0', 4070, 1, 2023, 8, 2);
insert into Commande values (8, '663305487-4', 863, 1, 2023, 1, 2);
insert into Commande values (9, '569995527-5', 2736, 1, 2023, 1, 8);
insert into Commande values (10, '903136555-6', 6935, 1, 2022, 7, 28);
insert into Commande values (11, '643853832-9', 3041, 1, 2022, 10, 21);
insert into Commande values (12, '269884191-5', 3041, 1, 2022, 4, 1);
insert into Commande values (13, '170756526-0', 144, 1, 2023, 5, 10);
insert into Commande values (14, '786303807-3', 9563, 1, 2022, 5, 22);
insert into Commande values (15, '975359434-8', 5220, 1, 2023, 3, 15);
insert into Commande values (16, '010128806-9', 7285, 1, 2022, 4, 8);
insert into Commande values (17, '700471132-3', 8295, 1, 2023, 10, 3);
insert into Commande values (18, '479074582-1', 6731, 1, 2022, 12, 18);
insert into Commande values (19, '475783803-4', 5675, 1, 2022, 2, 12);
insert into Commande values (20, '393360891-0', 4469, 1, 2023, 7, 3);

SELECT idLivre, idAdherent, count(idAdherent) AS v FROM Emprunt GROUP BY idAdherent HAVING v > 2;

WITH r1 AS (SELECT idLivre, idAdherent, count(idAdherent) AS v FROM Emprunt GROUP BY idAdherent HAVING v > 2),
r2 AS (SELECT ISBN, idAdherent FROM Exemplaire JOINT r1),
r3 AS (SELECT ISBN, idAdherent FROM Livre WHERE count(DISTINCT genre AND idAdherent)>3 JOINT r2),
r4 AS (SELECT nom, prenom, idAdherent FROM Auteur JOINT r3 WHERE count(nom, prenom)>3)
SELECT idAdherent FROM r4