Langage SQL et SGBD

Introduction

Dans ce TP on utilisera le SGBD SQLite, et son client graphique "DB Browser for SQLite" à télécharger sur https://sqlitebrowser.org/. Nous allons voir comment utiliser ces logiciels pour créer et manipuler la base de donnée vue en cours.

Une fois le logiciel téléchargé, installé, puis exécuté, on obtient la fenêtre suivante.

Cliquer sur le bouton "Nouvelle base de données", et créer la base de donnée intitulée "bibliothèque", stockée dans votre répertoire de travail. Fermer la fenêtre "Éditer la définition de la table" qui se présente automatiquement à vous (nous y reviendrons plus tard).

Cliquer sur l'onglet "Exécuter le SQL", et y coller l'ordre suivant, puis cliquer sur le bouton "Exécuter" (icône en forme de flèche). On utilisera comme clé primaire l'attribut id, un entier unique à chaque livre.

-- Création de la table Livre
CREATE TABLE Livre (
    id INT PRIMARY KEY,
    titre TEXT,
    auteur TEXT,
    annee_publi INT
);

1. Que se passe-t-il si vous cliquez une seconde fois sur le bouton "Exécuter" ?
2. Comment peut-on expliquer ce comportement ?

On souhaite ajouter le livre Fondation, publié en 1951 par Isaac Asimov, ainsi que le livre 1984, écrit en 1949 par George Orwell. Ouvrir un nouvel onglet et y exécuter le code SQL ci-dessous.

INSERT INTO Livre 
VALUES (1, 'Fondation', 'Isaac Asimov', 1951),
    (2, '1984', 'George Orwell', 1949);

1. Que se passe-t-il si vous cliquez une seconde fois sur le bouton "Exécuter" ?

2. Ouvrir un nouvel onglet puis exécuter les ordres SQL permettant d'insérer dans la table Livre les livres suivants :

   • Le Seigneur des Anneaux, écrit en 1954 par J.R.R. Tolkien
   • Guerre et Paix, écrit en 1869 par Léon Tolstoï
   • Orgueil et Préjugés, écrit en 1813 par Jane Austen
   • L'Étranger, écrit en 1942 par Albert Camus
   • Le Petit Prince, écrit en 1943 par Antoine de Saint-Exupéry
   • Crime et Châtiment, écrit en 1866 par Fiodor Dostoïevski
   • Le Maître et Marguerite, écrit en 1967 par Mikhaïl Boulgakov
   • Harry Potter à l'école des sorciers, écrit en 1997 par J.K. Rowling

3. Cliquer sur l'onglet "Parcourir les données". À quoi cet onglet sert-il ?

La relation Usager

1. Donner et exécuter le code SQL permettant de créer la relation Usager dont le schéma est :

   Usager( id Int, nom String, prenom String)

2. Donner le code SQL qui permet d'ajouter les usagers Jean Dupont, Sophie Martin, Pierre Dubois, Alfred Dubois, Marie Lefebvre et Thomas Moreau à la relation Usager.

3. Vérifier à l'aide de l'onglet "Parcourir les données" que les données ont été correctement insérées dans la table Usager.

4. Exécuter l'ordre SQL suivant :

   INSERT INTO Usager 
   VALUES (5, 'Michel', 'Justine');
   

   Commenter.

5. Sophie se marie avec Jean. Son nom devient donc Dupont-Martin. Exécuter l'ordre SQL suivant :

   UPDATE Usager
   SET nom = 'Dupont-Martin' 
   WHERE prenom = 'Sophie' AND nom = 'Martin'; 
   

6. On souhaite supprimer Alfred de la base de données. Exécuter l'ordre SQL suivant :

   DELETE FROM Usager
   WHERE prenom = 'Alfred';
   

   Que se serait-il passé si on avait utilisé le nom au lieu du prénom pour supprimer Alfred Dubois de la base de données ?

La relation Emprunt

Créer la table Emprunt à l'aide de l'ordre SQL suivant :

-- Création de la table Emprunt
CREATE TABLE Emprunt (
    id_emprunteur INT,
    date_emprunt DATE,
    date_retour DATE,
    id_livre INT,
    PRIMARY KEY (date_emprunt, id_livre),
    FOREIGN KEY (id_emprunteur) REFERENCES Usager(id),
    FOREIGN KEY (id_livre) REFERENCES Livre(id)
);

1. Quelles sont les différences avec le code SQL de création de la relation Livre ?

2. Exécuter l'ordre SQL suivant :

   INSERT INTO Emprunt
   VALUES (3, '2022-11-07', '2022-11-03', 1);
   

   Quelle information cet enregistrement de la table Emprunt représente-t-il ?

3. Exécuter le code SQL permettant de stocker l'information : Pierre Dubois a emprunté le livre L'étranger d'Albert Camus le 7 novembre 2023 et l'a rendu le 14 novembre 2023. On écrira la date entre apostrophe au format 'AAAA-MM-JJ'.

4. Exécuter l'ordre SQL suivant :

   INSERT INTO Emprunt 
   VALUES (3, '2023-12-10', '2023-12-17', 2),  
       (2, '2023-11-12', '2023-11-19', 8),  
       (4, '2023-11-15', '2023-11-22', 5),  
       (4, '2023-09-15', '2023-09-22', 6),  
       (4, '2023-12-05', '2023-12-12', 3),  
       (5, '2023-11-10', '2023-11-17', 10), 
       (5, '2023-11-21', '2023-11-28', 4),  
       (1, '2023-11-09', '2023-11-15', 6),  
       (1, '2023-11-19', '2023-11-26', 9),  
       (4, '2023-11-10', '2023-11-17', 2);  
   

   1. Quels livres a emprunté Pierre ?

   2. Qui a emprunté le plus de livres ?

   3. Quel livre a été emprunté le plus de fois ?

5. Sophie Dupont-Martin souhaite se désinscrire de la bibliothèque. On utilise pour cela l'ordre SQL (remplacer X par l'id de Sophie Dupont-Martin dans votre base de donnée).

   DELETE FROM Usager
   WHERE id = X;
   

   1. Commenter l'erreur obtenue.

   2. Supprimer tous les emprunts de Sophie de la table Emprunt, puis exécuter à nouveau le code précédent. Que constate-t-on ?

Recherche simple dans une base de données

1. On souhaite obtenir la liste des livres (ainsi que leurs auteurs) dont la première publication a eu lieu après 1950. Exécuter l'ordre SQL ci-dessous :

   SELECT titre, auteur
   FROM Livre
   WHERE annee_publi >= 1950;
   

2. Écrire l'ordre SQL permettant de donner l'année de publication du livre Guerre et Paix, écrit par Léon Tolstoï.