SQL Lab 2

Le but de cette section est d'entraîner les concepts vus dans les sections Bases de données relationnelles et Langage SQL - Notions avancées.

Exercice 1

Nous souhaitons créer une base de données pour une bibliothèque communale. Pour emprunter un livre, Bob doit scanner son code-barre personnel et celui du livre. Lors de l'emprunt, la date de retour est fixée. Chaque livre a un titre, un auteur, un éditeur, un ISBN qui est unique et une année de sortie. Un usager a un nom, un prénom, une adresse, un code postal, une ville, un email et un code-barre personnel. De chaque auteur, nous connaissons le nom et le prénom.

1. Déterminer les différentes tables nécessaires.

2. Déterminer les colonnes de chaque table.

3. Déterminer les clés primaires et étrangères.

4. Dessiner le schéma relationnel.

Solution

Exercice 2

Créer les différentes tables: usager, livre, auteur, auteur_de et emprunt. Ne pas oublier d'indiquer le type et les attributs (primary key, not null, etc.) quand c'est nécessaire.

Pour tester que la table usager est correcte:

select * from usager;

Pour tester que la table livre est correcte:

select * from livre;

Pour tester que la table auteur est correcte:

select * from auteur;

Pour tester que la table auteur_de est correcte:

select * from auteur_de;

Pour tester que la table emprunt est correcte:

select * from emprunt;

Solution

create table usager (
    nom text not null,
    prenom text not null,
    adresse text,
    code_postal char(4),
    ville text,
    email text not null,
    code_barre char(15) primary key not null
);

create table livre (
    titre text not null,
    editeur text,
    annee int,
    isbn char(14) primary key not null
);

create table auteur (
    a_id int primary key not null,
    nom text not null,
    prenom text not null
);

create table auteur_de (
    a_id int not null,
    isbn char(14) not null,
    primary key (a_id, isbn)
);

create table emprunt (
    code_barre char(15) not null,
    isbn char(14) primary key not null,
    retour date not null
);

Exercice 3

Toutes les requêtes de cet exercice se font dans une seule table.

insert into livre values ('Les Aventures de Huckleberry Finn', 'Flammarion', '2020', '978-2081509511');
insert into auteur values (0, 'Twain', 'Mark');
insert into auteur_de values (0, '978-2081509511');
insert into livre values ('Fondation et Empire', 'Editions Denoël', '1999', '978-2207249123');
insert into auteur values (1, 'Asimov', 'Isaac');
insert into auteur_de values (1, '978-2207249123');
insert into livre values ('Akira', 'Glénat', '2000', '978-2723428262');
insert into auteur values (2, 'Ōtomo', 'Katsuhiro');
insert into auteur_de values (2, '978-2723428262');
insert into livre values ('Les Robots', 'Editions Milan', '2017', '978-2745989857');
insert into auteur values (3, 'Martelle', 'Myriam');
insert into auteur_de values (3, '978-2745989857');
insert into auteur values (4, 'Touache', 'Sébastien');
insert into auteur_de values (4, '978-2745989857');
insert into livre values ('Astérix chez les Pictes', 'Editions Albert René', '2013', '978-2864972662');
insert into auteur values (5, 'Goscinny', 'René');
insert into auteur_de values (5, '978-2864972662');
insert into auteur values (6, 'Ferri', 'Jean-Yves');
insert into auteur_de values (6, '978-2864972662');
insert into auteur values (7, 'Uderzo', 'Albert');
insert into auteur_de values (7, '978-2864972662');
insert into auteur values (8, 'Conrad', 'Didier');
insert into auteur_de values (8, '978-2864972662');
insert into livre values ('Les Monades urbaines', 'Robert Laffont', '2016', '978-2221197691');
insert into auteur values (9, 'SILVERBERG', 'Robert');
insert into auteur_de values (9, '978-2221197691');
insert into livre values ('Les Voyages de Gulliver', 'Primento', '2015', '978-2335008586');
insert into auteur values (10, 'Swift', 'Jonathan');
insert into auteur_de values (10, '978-2335008586');
insert into auteur values (11, 'Ligaran', '');
insert into auteur_de values (11, '978-2335008586');
insert into livre values ('Lolita', 'Penguin UK', '2012', '978-0141391601');
insert into auteur values (12, 'Nabokov', 'Vladimir');
insert into auteur_de values (12, '978-0141391601');
insert into livre values ('La Nuit des temps', 'Presses de la Cité', '2014', '978-2258116429');
insert into auteur values (13, 'BARJAVEL', 'René');
insert into auteur_de values (13, '978-2258116429');
insert into livre values ('Ravage', 'Editions Gallimard', '2014', '978-2072534911');
insert into auteur values (14, 'Barjavel', 'René');
insert into auteur_de values (14, '978-2072534911');
insert into livre values ('Les Lauriers de César', 'Educa Books', '2008', '978-2012101500');
insert into auteur_de values (5, '978-2012101500');
insert into auteur_de values (7, '978-2012101500');
insert into livre values ('Niourk', 'French Pulp éditions', '2018', '979-1025100639');
insert into auteur values (15, 'Wul', 'Stefan');
insert into auteur_de values (15, '979-1025100639');
insert into livre values ('Le Meilleur des mondes', 'Plon', '2013', '978-2259221702');
insert into auteur values (16, 'HUXLEY', 'Aldous');
insert into auteur_de values (16, '978-2259221702');
insert into livre values ('Berlin Alexanderplatz', 'Editions Gallimard', '1933', '978-2070219292');
insert into auteur values (17, 'Döblin', 'Alfred');
insert into auteur_de values (17, '978-2070219292');
insert into livre values ('Fahrenheit 451', 'Simon and Schuster', '2011', '978-1439142677');
insert into auteur values (18, 'Bradbury', 'Ray');
insert into auteur_de values (18, '978-1439142677');
insert into livre values ('La Mort d''Ivan Ilitch', 'Flammarion', '2015', '978-2081364509');
insert into auteur values (19, 'Tolstoï', 'Léon');
insert into auteur_de values (19, '978-2081364509');
insert into livre values ('Croisière sans escale', 'Editions Denoël', '1990', '978-2207500293');
insert into auteur values (20, 'Aldiss', 'Brian Wilson');
insert into auteur_de values (20, '978-2207500293');
insert into livre values ('Le Vieil Homme et la Mer', 'Editions Gallimard', '2018', '978-2072762093');
insert into auteur values (21, 'Hemingway', 'Ernest');
insert into auteur_de values (21, '978-2072762093');
insert into livre values ('Mrs Dalloway', 'Flammarion', '2015', '978-2081358881');
insert into auteur values (22, 'Woolf', 'Virginia');
insert into auteur_de values (22, '978-2081358881');
insert into livre values ('L''Idiot', 'Les Editions de Londres', '2019', '978-1911572909');
insert into auteur values (23, 'Fiodor', 'Dostoïevski');
insert into auteur_de values (23, '978-1911572909');
insert into livre values ('Le Carnet d''or', 'Le Livre de poche', '1980', '978-2253025320');
insert into auteur values (24, 'Lessing', 'Doris');
insert into auteur_de values (24, '978-2253025320');
insert into livre values ('Les Grandes Espérances', 'BoD - Books on Demand', '2019', '978-2322185801');
insert into auteur values (25, 'Dickens', 'Charles');
insert into auteur_de values (25, '978-2322185801');
insert into livre values ('Astérix et Cléopâtre', 'Dargaud', '1999', '978-2012100060');
insert into auteur_de values (5, '978-2012100060');
insert into auteur_de values (7, '978-2012100060');
insert into livre values ('Madame Bovary', 'UPblisher', '2016', '978-2759902293');
insert into auteur values (26, 'Flaubert', 'Gustave');
insert into auteur_de values (26, '978-2759902293');
insert into livre values ('Les Frères Karamazov', 'Les éditions Pulsio', '2016', '978-2371131118');
insert into auteur values (27, 'Dostoïevski', 'Fiodor');
insert into auteur_de values (27, '978-2371131118');
insert into livre values ('Moby Dick', 'Campfire Graphic Novels', '2010', '978-8190732673');
insert into auteur values (28, 'Melville', 'Herman');
insert into auteur_de values (28, '978-8190732673');
insert into livre values ('Demain les chiens', 'J''ai Lu', '2015', '978-2290112168');
insert into auteur values (29, 'Simak', 'Clifford D.');
insert into auteur_de values (29, '978-2290112168');
insert into livre values ('Le Tour de Gaule d''Astérix', 'Educa Books', '2007', '978-2012101685');
insert into auteur_de values (5, '978-2012101685');
insert into auteur_de values (7, '978-2012101685');
insert into livre values ('1984', 'Houghton Mifflin Harcourt', '1983', '978-0547249643');
insert into auteur values (30, 'Orwell', 'George');
insert into auteur_de values (30, '978-0547249643');
insert into livre values ('Don Quichotte', 'Les éditions Pulsio', '2016', '978-2371130418');
insert into auteur values (31, 'Cervantes', 'Miguel de');
insert into auteur_de values (31, '978-2371130418');
insert into livre values ('Le Château de Lord Valentin', 'Robert Laffont', '2017', '978-2221216361');
insert into auteur_de values (9, '978-2221216361');
insert into livre values ('Le Père Goriot', 'Primento', '2012', '978-2806231697');
insert into auteur values (32, 'Balzac', 'Honoré de');
insert into auteur_de values (32, '978-2806231697');
insert into livre values ('Le Procès', 'Flammarion', '2014', '978-2081351981');
insert into auteur values (33, 'Kafka', 'Franz');
insert into auteur_de values (33, '978-2081351981');
insert into livre values ('L''Homme qui rétrécit', 'Editions Gallimard', '2017', '978-2072457340');
insert into auteur values (34, 'Matheson', 'Richard');
insert into auteur_de values (34, '978-2072457340');
insert into livre values ('Chroniques martiennes', 'Editions Gallimard', '2016', '978-2072455162');
insert into auteur_de values (18, '978-2072455162');
insert into livre values ('Le Roi Lear', 'Éditions Actes Sud', '2015', '978-2330052768');
insert into auteur values (35, 'Py', 'Olivier');
insert into auteur_de values (35, '978-2330052768');
insert into auteur values (36, 'Shakespeare', 'William');
insert into auteur_de values (36, '978-2330052768');
insert into livre values ('Le Cadeau de César', 'Educa Books', '2005', '978-2012101531');
insert into auteur_de values (5, '978-2012101531');
insert into auteur_de values (7, '978-2012101531');
insert into livre values ('La Planète des singes', 'Julliard', '2011', '978-2260019183');
insert into auteur values (37, 'BOULLE', 'Pierre');
insert into auteur_de values (37, '978-2260019183');
insert into livre values ('Orgueil et Préjugés', 'Fleurus', '2015', '978-2215130475');
insert into auteur values (38, 'Austen', 'Jane');
insert into auteur_de values (38, '978-2215130475');
insert into livre values ('Une maison de poupée', 'Éditions Actes Sud', '2016', '978-2330068349');
insert into auteur values (39, 'Ibsen', 'Henrik');
insert into auteur_de values (39, '978-2330068349');
insert into livre values ('Vermilion Sands', 'Carroll & Graf Pub', '1988', '978-0881844221');
insert into auteur values (40, 'Ballard', 'J. G.');
insert into auteur_de values (40, '978-0881844221');
insert into livre values ('La Grande Traversée', 'Seuil Jeunesse', '2014', '979-1023500448');
insert into auteur values (41, 'Demois', 'Agathe');
insert into auteur_de values (41, '979-1023500448');
insert into auteur values (42, 'Godeau', 'Vincent');
insert into auteur_de values (42, '979-1023500448');
insert into livre values ('L''Étranger', 'Editions Gallimard', '2012', '978-2072376429');
insert into auteur values (43, 'Camus', 'Albert');
insert into auteur_de values (43, '978-2072376429');
insert into livre values ('L''Île des morts', 'POL Editeur', '2010', '978-2846825573');
insert into auteur values (44, 'Frémon', 'Jean');
insert into auteur_de values (44, '978-2846825573');
insert into livre values ('Par-delà le mur du sommeil', 'République des Lettres', '2018', '978-2824904269');
insert into auteur values (45, 'Lovecraft', 'H. P.');
insert into auteur_de values (45, '978-2824904269');
insert into livre values ('Le Papyrus de César', 'Editions Albert René', '2015', '978-2864972716');
insert into auteur_de values (6, '978-2864972716');
insert into livre values ('La Main de Zeï', 'Bragelonne Classic', '2016', '978-2820511034');
insert into auteur values (46, 'Camp', 'Lyon Sprague de');
insert into auteur_de values (46, '978-2820511034');
insert into livre values ('Beloved', 'Christian Bourgois', '2015', '978-2267028133');
insert into auteur values (47, 'Morrison', 'Toni');
insert into auteur_de values (47, '978-2267028133');
insert into livre values ('La Conscience de Zeno', 'République des Lettres', '2015', '978-2824902371');
insert into auteur values (48, 'Svevo', 'Italo');
insert into auteur_de values (48, '978-2824902371');
insert into livre values ('Delirium Circus', 'Bragelonne', '2013', '978-2820508935');
insert into auteur values (49, 'Pelot', 'Pierre');
insert into auteur_de values (49, '978-2820508935');
insert into livre values ('Médée', 'Hatier', '2013', '978-2218972324');
insert into auteur values (50, 'Corneille', '');
insert into auteur_de values (50, '978-2218972324');
insert into auteur values (51, 'Faerber', 'Johan');
insert into auteur_de values (51, '978-2218972324');
insert into livre values ('Nostromo', 'Oxford University Press', '2009', '978-0199555918');
insert into auteur values (52, 'Conrad', 'Joseph');
insert into auteur_de values (52, '978-0199555918');
insert into livre values ('Au carrefour des étoiles', 'J''ai Lu', '1997', '978-2277118473');
insert into auteur_de values (29, '978-2277118473');
insert into auteur values (53, 'Deutsch', 'Michel');
insert into auteur_de values (53, '978-2277118473');
insert into livre values ('Le Vagabond', 'BnF collection ebooks', '2016', '978-2346014453');
insert into auteur values (54, 'Maupassant', 'Guy de');
insert into auteur_de values (54, '978-2346014453');
insert into livre values ('Les Buddenbrook', 'LGF/Le Livre de Poche', '1993', '978-2253063193');
insert into auteur values (55, 'Mann', 'Thomas');
insert into auteur_de values (55, '978-2253063193');
insert into livre values ('Les Métamorphoses', 'Le Livre de Poche', '2011', '978-2253158677');
insert into auteur values (56, 'Ovide', '');
insert into auteur_de values (56, '978-2253158677');
insert into livre values ('Jack Barron et l''Éternité', 'J''ai Lu', '2016', '978-2290105504');
insert into auteur values (57, 'Spinrad', 'Norman');
insert into auteur_de values (57, '978-2290105504');
insert into livre values ('Hacker''s Delight', 'Addison-Wesley Professional', '2003', '978-0201914658');
insert into auteur values (58, 'Warren', 'Henry S.');
insert into auteur_de values (58, '978-0201914658');
insert into livre values ('Astérix et les Normands', 'Dargaud', '2005', '978-2012101418');
insert into auteur_de values (5, '978-2012101418');
insert into auteur_de values (7, '978-2012101418');
insert into livre values ('Le Temps incertain', 'Robert Laffont', '2011', '978-2221119709');
insert into auteur values (59, 'JEURY', 'Michel');
insert into auteur_de values (59, '978-2221119709');
insert into livre values ('Astérix en Corse', 'Dargaud', '2005', '978-2012101524');
insert into auteur values (60, 'Goscinny', '');
insert into auteur_de values (60, '978-2012101524');
insert into auteur_de values (7, '978-2012101524');
insert into livre values ('Les Fils de la Médina', 'Arles [France] : Actes sud', '2003', '978-2742744824');
insert into auteur values (61, 'Maḥfūẓ', 'Naǧīb');
insert into auteur_de values (61, '978-2742744824');
insert into livre values ('Le Grand secret', 'Presses de la Cité', '2014', '978-2258116405');
insert into auteur_de values (13, '978-2258116405');
insert into livre values ('Le Devin', 'Educa Books', '2010', '978-2012101517');
insert into auteur_de values (5, '978-2012101517');
insert into auteur_de values (7, '978-2012101517');
insert into livre values ('Le Noir Dessein', 'Livre de poche', '1998', '978-2253062820');
insert into auteur values (62, 'Farmer', 'Philip José');
insert into auteur_de values (62, '978-2253062820');
insert into livre values ('Astérix légionnaire', 'Educa Books', '2011', '978-2012101784');
insert into auteur_de values (5, '978-2012101784');
insert into auteur_de values (7, '978-2012101784');
insert into livre values ('Romancero gitano', 'Greenbooks editore', '2020', '978-8832957402');
insert into auteur values (63, 'Lorca', 'Federico Garcia');
insert into auteur_de values (63, '978-8832957402');
insert into livre values ('The Practice of Programming', 'Addison-Wesley Professional', '1999', '978-0201615869');
insert into auteur values (64, 'Kernighan', 'Brian W.');
insert into auteur_de values (64, '978-0201615869');
insert into auteur values (65, 'Pike', 'Rob');
insert into auteur_de values (65, '978-0201615869');
insert into livre values ('Crime et Châtiment', 'Editions Humanis', '2012', '979-1021900486');
insert into auteur values (66, 'Dostoïevski', 'Fédor Mikhaïlovitch');
insert into auteur_de values (66, '979-1021900486');
insert into livre values ('La Promenade au phare', 'LGF/Le Livre de Poche', '1983', '978-2253031536');
insert into auteur_de values (22, '978-2253031536');
insert into livre values ('L''Homme sans qualités', 'Contemporary French Fiction', '2011', '978-2757803691');
insert into auteur values (67, 'Musil', 'Robert');
insert into auteur_de values (67, '978-2757803691');
insert into livre values ('Le Bruit et la Fureur', 'Gallimard Education', '1972', '978-2070361625');
insert into auteur values (68, 'Faulkner', 'William');
insert into auteur_de values (68, '978-2070361625');
insert into livre values ('Les Plus qu''humains', 'adsaa', '1999', '000-0000000162');
insert into auteur values (69, 'Fransa', 'France');
insert into auteur_de values (69, '000-0000000162');
insert into livre values ('La Main gauche de la nuit', 'Robert Laffont', '2012', '978-2221128121');
insert into auteur values (70, 'GUIN', 'Ursula LE');
insert into auteur_de values (70, '978-2221128121');
insert into livre values ('Mémoires d''Hadrien', 'Gallimard Education', '1974', '978-2070369218');
insert into auteur values (71, 'Yourcenar', 'Marguerite');
insert into auteur_de values (71, '978-2070369218');
insert into livre values ('Contes de l''absurde', 'Presses Pocket', '1978', '978-2266006095');
insert into auteur values (72, 'Boulle', 'Pierre');
insert into auteur_de values (72, '978-2266006095');
insert into livre values ('Astérix et la Transitalique', 'Editions Albert René', '2017', '978-2864973270');
insert into auteur_de values (6, '978-2864973270');
insert into livre values ('L''Odyssée d''Astérix', 'Educa Books', '2008', '978-2864972051');
insert into auteur values (73, 'Gascony', 'Rene');
insert into auteur_de values (73, '978-2864972051');
insert into auteur_de values (7, '978-2864972051');
insert into livre values ('Les Singes du temps', 'Robert Laffont', '2011', '978-2221119693');
insert into auteur_de values (59, '978-2221119693');
insert into livre values ('Les Contes de Canterbury', 'Gallimard Education', '2000', '978-2070406340');
insert into auteur values (74, 'Chaucer', 'Geoffrey');
insert into auteur_de values (74, '978-2070406340');
insert into livre values ('Sécheresse', 'La Cheminante', '2014', '978-2371270060');
insert into auteur values (75, 'Chenet', 'Gérard');
insert into auteur_de values (75, '978-2371270060');
insert into livre values ('The Art of Computer Programming', 'Addison-Wesley Professional', '1997', '978-0321635747');
insert into auteur values (76, 'Knuth', 'Donald E.');
insert into auteur_de values (76, '978-0321635747');
insert into livre values ('L''Aveuglement', 'Contemporary French Fiction', '2000', '978-2020403436');
insert into auteur values (77, 'Saramago', 'José');
insert into auteur_de values (77, '978-2020403436');
insert into livre values ('Le Berceau du chat', 'Contemporary French Fiction', '2010', '978-2757820919');
insert into auteur values (78, 'Vonnegut', 'Kurt');
insert into auteur_de values (78, '978-2757820919');
insert into livre values ('Anna Karénine', 'Bibliothèque russe et slave', '2018', '978-2371240087');
insert into auteur_de values (19, '978-2371240087');
insert into livre values ('La Montagne magique', 'Fayard', '2016', '978-2213703848');
insert into auteur_de values (55, '978-2213703848');
insert into livre values ('Le Domaine des dieux', 'French & European Publications', '1992', '978-0785909903');
insert into auteur values (79, 'Goscinny', 'Rene');
insert into auteur_de values (79, '978-0785909903');
insert into auteur_de values (7, '978-0785909903');
insert into livre values ('Cent ans de solitude', 'Contemporary French Fiction', '1995', '978-2020238113');
insert into auteur values (80, 'Márquez', 'Gabriel García');
insert into auteur_de values (80, '978-2020238113');
insert into livre values ('Gargantua et Pantagruel', 'Livre de Poche Jeunesse', '2009', '978-2013230827');
insert into auteur values (81, 'Rabelais', 'François');
insert into auteur_de values (81, '978-2013230827');
insert into auteur values (82, 'Laporte', 'Michel');
insert into auteur_de values (82, '978-2013230827');
insert into auteur values (83, 'Nadel', 'Olivier-Marc');
insert into auteur_de values (83, '978-2013230827');
insert into livre values ('Contes', 'J''ai Lu', '2015', '978-2290117965');
insert into auteur values (84, 'Grimm', 'Jacob');
insert into auteur_de values (84, '978-2290117965');
insert into auteur values (85, 'Grimm', 'Wilhelm');
insert into auteur_de values (85, '978-2290117965');
insert into livre values ('Guerre et Paix', 'Archipoche', '2016', '978-2352879183');
insert into auteur_de values (19, '978-2352879183');
insert into livre values ('Énéide', 'Belles Lettres', '1993', '978-2251013039');
insert into auteur values (86, 'Vergilius', 'Publius Maro');
insert into auteur_de values (86, '978-2251013039');
insert into auteur values (87, 'Virgile', '');
insert into auteur_de values (87, '978-2251013039');
insert into livre values ('Seconde Fondation', 'adsaa', '1979', '000-0000000097');
insert into auteur_de values (1, '000-0000000097');
insert into livre values ('Les Jeux de l''esprit', 'FeniXX', '1971', '978-2402281775');
insert into auteur_de values (72, '978-2402281775');
insert into livre values ('Middlemarch', 'Wordsworth Editions', '1994', '978-1853262371');
insert into auteur values (88, 'Eliot', 'George');
insert into auteur_de values (88, '978-1853262371');
insert into livre values ('Oedipe roi', 'J''ai Lu', '2013', '978-2290080207');
insert into auteur values (89, 'Sophocle', '');
insert into auteur_de values (89, '978-2290080207');
insert into livre values ('L''Amour aux temps du choléra', 'Grasset', '2009', '978-2246819554');
insert into auteur values (90, 'Márquez', 'Gabriel Garcia');
insert into auteur_de values (90, '978-2246819554');
insert into livre values ('Fictions', 'Gallimard Education', '1974', '978-2070366149');
insert into auteur values (91, 'Borges', 'Jorge Luis');
insert into auteur_de values (91, '978-2070366149');
insert into livre values ('Astérix chez les Bretons', 'Dargaud', '2002', '978-2012100084');
insert into auteur_de values (5, '978-2012100084');
insert into auteur_de values (7, '978-2012100084');
insert into livre values ('Le Château', 'Points', '2011', '978-2757827413');
insert into auteur_de values (33, '978-2757827413');
insert into livre values ('Le Voyageur imprudent', 'Editions Gallimard', '2014', '978-2072535031');
insert into auteur_de values (14, '978-2072535031');
insert into livre values ('Je suis une légende', 'Editions Gallimard', '2013', '978-2072457388');
insert into auteur_de values (34, '978-2072457388');
insert into livre values ('Le Maître du Haut Château', 'J''ai Lu', '2017', '978-2290157268');
insert into auteur values (92, 'Dick', 'Philip K.');
insert into auteur_de values (92, '978-2290157268');
insert into livre values ('Les Âmes mortes', 'Bibliothèque russe et slave', '2018', '978-2371240001');
insert into auteur values (93, 'Gogol', 'Nikolaï');
insert into auteur_de values (93, '978-2371240001');
insert into livre values ('Le Tambour', 'Contemporary French Fiction', '1997', '978-2020314305');
insert into auteur values (94, 'Grass', 'Günter');
insert into auteur_de values (94, '978-2020314305');
insert into livre values ('Polymath', 'iMinds Pty Ltd', '2014', '978-1921746864');
insert into auteur values (95, 'iMinds', '');
insert into auteur_de values (95, '978-1921746864');
insert into livre values ('Seigneur de lumière', 'Editions Gallimard', '2014', '978-2072487958');
insert into auteur values (96, 'Zelazny', 'Roger');
insert into auteur_de values (96, '978-2072487958');
insert into livre values ('Ulysse', 'Nathan', '2012', '978-2092532195');
insert into auteur values (97, 'Montardre', 'Hélène');
insert into auteur_de values (97, '978-2092532195');
insert into livre values ('Pedro Páramo', 'New York : Grove Press', '1959', '000-0000000069');
insert into auteur values (98, 'Rulfo', 'Juan');
insert into auteur_de values (98, '000-0000000069');
insert into livre values ('Ubik', 'Houghton Mifflin Harcourt', '2012', '978-0547728247');
insert into auteur_de values (92, '978-0547728247');
insert into livre values ('Algorithms', 'Addison-Wesley Professional', '2011', '978-0132762564');
insert into auteur values (99, 'Sedgewick', 'Robert');
insert into auteur_de values (99, '978-0132762564');
insert into auteur values (100, 'Wayne', 'Kevin');
insert into auteur_de values (100, '978-0132762564');
insert into livre values ('Fifi Brindacier', 'Hachette Romans', '2013', '978-2011179043');
insert into auteur values (101, 'Lindgren', 'Astrid');
insert into auteur_de values (101, '978-2011179043');
insert into livre values ('Le monde s''effondre', 'Editions Présence Africaine', '1972', '978-2708701915');
insert into auteur values (102, 'Achebe', 'Chinua');
insert into auteur_de values (102, '978-2708701915');
insert into livre values ('La Naissance des dieux', 'Glénat BD', '2017', '978-2331035531');
insert into auteur values (103, 'Bruneau', 'Clotilde');
insert into auteur_de values (103, '978-2331035531');
insert into auteur values (104, 'D.', 'Dim');
insert into auteur_de values (104, '978-2331035531');
insert into auteur values (105, 'Santagati', 'Federico');
insert into auteur_de values (105, '978-2331035531');
insert into auteur values (106, 'Ferry', 'Luc');
insert into auteur_de values (106, '978-2331035531');
insert into auteur values (107, 'Poli', 'Didier');
insert into auteur_de values (107, '978-2331035531');
insert into livre values ('Hamlet', 'Primento', '2012', '978-2806240187');
insert into auteur_de values (36, '978-2806240187');
insert into livre values ('Les Enfants de minuit', 'Gallimard Education', '2010', '978-2070402632');
insert into auteur values (108, 'Rushdie', 'Salman');
insert into auteur_de values (108, '978-2070402632');
insert into livre values ('Dune', 'Penguin', '2003', '978-1101658055');
insert into auteur values (109, 'Herbert', 'Frank');
insert into auteur_de values (109, '978-1101658055');
insert into livre values ('La Couleur tombée du ciel', 'Tiers Livre Éditeur', '2014', '978-2814510012');
insert into auteur values (110, 'Lovecraft', 'Howard Phillips');
insert into auteur_de values (110, '978-2814510012');
insert into livre values ('L''Éducation sentimentale', 'FeniXX', '1990', '978-2402282697');
insert into auteur values (111, 'Rincé', 'Dominique');
insert into auteur_de values (111, '978-2402282697');
insert into livre values ('Obélix et Compagnie', 'Educa Books', '2008', '978-2012101555');
insert into auteur_de values (5, '978-2012101555');
insert into auteur_de values (7, '978-2012101555');
insert into livre values ('Le Journal d''un fou', 'Bibebook', '2013', '978-2824709420');
insert into auteur values (112, 'Gogol', 'Nikolai');
insert into auteur_de values (112, '978-2824709420');
insert into livre values ('Les Hauts de Hurlevent', 'Le Livre de Poche', '2012', '978-2253174561');
insert into auteur values (113, 'Brontë', 'Emily');
insert into auteur_de values (113, '978-2253174561');
insert into livre values ('La Plaie', 'FeniXX', '1967', '978-2402255462');
insert into auteur values (114, 'Fall', 'Malick');
insert into auteur_de values (114, '978-2402255462');
insert into livre values ('Astérix chez les Belges', 'Dargaud', '1979', '978-2012101562');
insert into auteur_de values (5, '978-2012101562');
insert into auteur_de values (7, '978-2012101562');
insert into livre values ('Le Rouge et le Noir', 'Les Éditions de l''Ebook malin', '1971', '978-2367881171');
insert into auteur values (115, 'Stendhal', '');
insert into auteur_de values (115, '978-2367881171');
insert into livre values ('À la recherche du temps perdu', 'Books LLC, Wiki Series', '2010', '978-1153611725');
insert into auteur values (116, 'Wikipedia', 'Source');
insert into auteur_de values (116, '978-1153611725');
insert into livre values ('La storia', 'Editions Gallimard', '2004', '978-2070315017');
insert into auteur values (117, 'Morante', 'Elsa');
insert into auteur_de values (117, '978-2070315017');
insert into livre values ('L''Homme total', 'Presses Universitaires de France - PUF', '2011', '978-2130592150');
insert into auteur values (118, 'Karsenti', 'Bruno');
insert into auteur_de values (118, '978-2130592150');
insert into usager values('Michel', 'Valérie', 'Rue de Lausanne 86', '1700', 'Fribourg', 'vmichel5@monmail.com', '199614051174633');
insert into usager values('Durand', 'Julien', 'Route du Petit-Moncor 1B', '1752', 'Villars-sur-Glâne', 'jdurand9@email.fr', '782124241492509');
insert into usager values('Moreau', 'Alain', 'Boulevard de Pérolles 5', '1700', 'Fribourg', 'amoreau1@abc.de', '421921003090881');
insert into usager values('Dubois', 'Philippe', 'Route de Villars 7', '1700', 'Fribourg', 'pdubois5@chezmoi.net', '137332830764072');
insert into usager values('Robert', 'Pascal', 'Route de Chantemerle 58', '1763', 'Granges-Paccot', 'probert9@monmail.com', '533299198788609');
insert into usager values('Fournier', 'David', 'Avenue de la Gare 12', '1700', 'Fribourg', 'dfournier4@abc.de', '612978231522917');
insert into usager values('Laurent', 'Françoise', 'Chemin des Dailles 3', '1752', 'Villars-sur-Glâne', 'flaurent3@monmail.com', '917547585216771');
insert into usager values('Simon', 'Sandrine', 'Route de la Chassotte 23', '1763', 'Granges-Paccot', 'ssimon2@abc.de', '654834075188732');
insert into usager values('Petit', 'Sébastien', 'Route du Grand-Pré 21', '1700', 'Fribourg', 'spetit4@email.fr', '934701281931582');
insert into usager values('Bernard', 'Stéphane', 'Route de la Fonderie 13', '1700', 'Fribourg', 'sbernard2@chezmoi.net', '035184062854281');
insert into emprunt values ('421921003090881', '978-2081358881', '2024-11-28');
insert into emprunt values ('421921003090881', '978-2207249123', '2024-11-28');
insert into emprunt values ('421921003090881', '978-2824709420', '2024-11-28');
insert into emprunt values ('137332830764072', '978-2352879183', '2024-12-20');
insert into emprunt values ('137332830764072', '978-2335008586', '2024-12-20');
insert into emprunt values ('137332830764072', '978-2013230827', '2024-12-20');
insert into emprunt values ('533299198788609', '978-2253174561', '2024-12-28');
insert into emprunt values ('533299198788609', '978-2251013039', '2024-12-28');
insert into emprunt values ('917547585216771', '978-2290105504', '2024-12-07');
insert into emprunt values ('654834075188732', '978-2864973270', '2025-01-17');
insert into emprunt values ('654834075188732', '978-2070406340', '2025-01-17');
insert into emprunt values ('654834075188732', '978-2806231697', '2025-01-17');
insert into emprunt values ('934701281931582', '978-2260019183', '2025-01-01');
insert into emprunt values ('934701281931582', '978-2371240087', '2025-01-01');
insert into emprunt values ('035184062854281', '978-2745989857', '2024-12-18');
insert into emprunt values ('035184062854281', '978-2072762093', '2024-12-18');
insert into emprunt values ('035184062854281', '978-2742744824', '2024-12-18');

1. Écrire une requête SQL qui retourne tous les livres.

   Solution

   select * from livre;
   

2. Écrire une requête SQL qui retourne tous les noms des usagers.

   Solution

   select nom from usager;
   

3. Écrire une requête SQL qui retourne tous les éditeurs sans doublons.

   Solution

   select distinct editeur from livre;
   

4. Écrire une requête SQL qui retourne le titre et l'année des livres publiés avant 1980.

   Solution

   select titre, annee from livre where annee < 1980;
   

5. Écrire une requête SQL qui retourne le titre des livres dont le titre commence par le mot "Astérix".

   Solution

   select titre from livre where titre like 'Astérix%';
   

6. Écrire une requête SQL qui retourne le titre des livres dont le titre contient le mot "Astérix".

   Solution

   select titre from livre where titre like '%Astérix%';
   

7. Écrire une requête SQL qui retourne l'isbn des livres à rendre avant le 31 décembre 2024.

   Astuce

   Les dates se notent entre guillemets simples.

   Solution

   select isbn from emprunt where retour < '2024-12-31';
   

8. Écrire une requête SQL qui retourne le nom et le prénom de tous les auteurs triés par ordre alphabétique.

   Solution

   select nom, prenom from auteur order by nom asc;
   

9. Écrire une requête SQL qui retourne le nom, le prénom et l'adresse des usagers vivant à Fribourg.

   Solution

   select nom, prenom, adresse from usager where ville = 'Fribourg';
   

10. Écrire une requête SQL qui retourne l'année et le titre des livres publiés entre 2012 et 2015.

    Solution

    select annee, titre from livre where annee between 2012 and 2015;
    

Exercice 4

Formuler en français ce que nous cherchons avec les requêtes suivantes:

1. select * from livre where titre like '%Robot%';
   

   Solution

   Affiche tous les livres dont le titre contient "Robot".

2. select nom, prenom from usager where ville = 'Granges-Paccots';
   

   Solution

   Affiche le nom et le prénom des usagers habitant Granges-Paccots.

3. select usager.nom, usager.prenom from usager
     join emprunt on usager.code_barre = emprunt.code_barre
     where retour < '2024-12-01';
   

   Solution

   Affiche le nom et le prénom des usagers qui doivent rendre un livre avant le premier décembre.

Exercice 5

Pour cet exercice, il faudra utiliser des jointures de tables.

1. Écrire une requête SQL qui retourne le titre des livres empruntés.

   Solution

   select livre.titre from livre
     join emprunt on livre.isbn = emprunt.isbn;
   

2. Écrire une requête SQL qui retourne le titre des livres empruntés à rendre après le 15 décembre.

   Solution

   select livre.titre from livre
     join emprunt on livre.isbn = emprunt.isbn
     where retour > '2024-12-15';
   

3. Écrire une requête SQL qui retourne le nom et le prénom des usagers qui ont emprunté des livres.

   Solution

   select usager.nom, usager.prenom from usager
     join emprunt on usager.code_barre = emprunt.code_barre;
   

   Les mêmes noms et prénoms apparaissent plusieurs fois, supprimer les doublons.

   Solution

   select distinct usager.nom, usager.prenom from usager
     join emprunt on usager.code_barre = emprunt.code_barre;
   

4. Écrire une requête SQL qui retourne le nom et le prénom de l'auteur du livre "La Mort d'Ivan Ilitch".

   Solution

   select auteur.nom, auteur.prenom from auteur
     join auteur_de on auteur.a_id = auteur_de.a_id
     join livre on auteur_de.isbn = livre.isbn
     where titre = 'La Mort d''Ivan Ilitch';
   

5. Écrire une requête SQL qui retourne le nom et le prénom de l'usager qui a emprunté "Jack Barron et l'Éternité".

   Solution

   select usager.nom, usager.prenom from usager
     join emprunt on usager.code_barre = emprunt.code_barre
     join livre on emprunt.isbn = livre.isbn
     where titre = 'Jack Barron et l''Éternité';
   

6. Écrire une requête SQL qui retourne le titre et la date de retour des livres empruntés par Philippe Dubois.

   Solution

   select livre.titre, emprunt.retour from livre
     join emprunt on livre.isbn = emprunt.isbn
     join usager on emprunt.code_barre = usager.code_barre
     where usager.nom = 'Dubois' and usager.prenom = 'Philippe';
   

7. Écrire une requête SQL qui retourne le titre des livres publiés avant "Astérix chez les Bretons".

   Solution

   select livre.titre from livre
     where annee < (select annee from livre
                    where titre = 'Astérix chez les Bretons');
   

8. Challenge: Écrire une requête SQL qui retourne le nom et le prénom des auteurs des livres de la question précédente sans doublons.

   Solution

   select distinct auteur.nom, auteur.prenom from auteur
     join auteur_de on auteur.a_id = auteur_de.a_id
     join livre on livre.isbn = auteur_de.isbn
     where annee < (select annee from livre
                    where titre = 'Astérix chez les Bretons');