TP 3 « Petite application SQL sous SQLite3 »

Nous allons travailler sur l’exemple ‘bibliothèque’ vu en cours. La base de données à manipuler est relative à l’emprunt de livres.

Le schéma relationnel de la base est le suivant :

• Membre (IdMembre, nomMembre, adrMembre, cpMembre)
• Biblio ( IdBiblio, nomBiblio, adrBiblio, cpBiblio)
• Livre (IdLivre,titreLivre, auteurLivre, categLivre, IdBiblio)
• Emprunt (IdLivre, IdMembre, dateEmprunt, dureeEmprunt)

avec :

• IdMembre : numéro du membre
• nomMembre : nom du membre
• adrMembre : adresse du membre
• cpMembre : code postal associé à l’adresse du membre
• IdBiblio : code de la bibliothèque
• nomBiblio : nom de la bibliothèque
• adrBiblio : adresse de la bibliothèque
• cpBiblio : code postal associé à l’adresse de la bibliothèque
• IdLivre : numéro du livre
• titreLivre : titre du livre
• auteurLivre : auteur du livre
• categLivre : catégorie du livre
• dateEmprunt: date de début de l’emprunt
• dureeEmprunt : durée de l’emprunt (en nombre de jours)

Les attributs ou groupes d’attributs soulignés (resp. en italique) correspondent aux clés primaires (resp. étrangères) des relations.

Type des attributs :

• IdMembre, categLivre, dureeEmprunt, IdBiblio: entier
• categLivre : smallint
• dateEmprunt: date
• prixLivreJour : réel
• nomMembre, adrMembre, cpMembre, nomBiblio, adrBiblio, cpBiblio, IdLivre, titreLivre, auteurLivre, IdLivre: chaînes de caractères

Pour travailler sur sqlite3 et y créer une base de données biblio.db , il suffit de taper dans un terminal :

sqlite3 biblio.db 

et à chaque fois que l’on voudra travailler sur cette base il faudra réitérer cette opération.

Il est donc demandé de créer un dossier BD, de se positionner à l’intérieur de celui-ci et de lancer :

sqlite3 biblio.db

Ensuite taper la commande .tables :

sqlite>.tables
sqlite>

Vous remarquerez qu’il n’y a pas de réponse car il n’y a pas encore de table dans la base de données. Vous pouvez essayer de créer la table Essai en ligne.

sqlite> CREATE TABLE Essai(at1 INTEGER, at2 CHAR(20)) ;

maintenant :

sqlite>.tables
essai
sqlite>SELECT * FROM Essai ;
sqlite> 

pas de réponse car nous n’avons aucun tuple dans le table essai. Maintenant ajoutez un tuple :

sqlite> INSERT INTO Essai VALUES (12, 'TOTO') ;

vérifiez l’insertion :

sqlite>  SELECT * FROM Essai ;
12|TOTO

ensuite sortez de sqlite3 en tapant Ctrl-D.

Dans le fichier creat_biblio.sql se trouvent les commandes SQL pour créer et remplir la base de données.

Il vous est demandé de récupérer ce fichier, de le mettre dans votre dossier BD.

Ouvrez-le sous geany pour regarder son contenu. Il commence par 4 commandes DROP TABLE sur chacune des tables. Nous avons vu dans le cours que DROP TABLE permet de supprimer une table, DROP TABLE IF EXISTS permet de supprimer une table si elle existe. Cela nous permet de relancer le fichier en exécution même si on l’avait déjà lancé sans déclencher d’erreur.

Ensuite nous avons les 4 CREATE TABLE puis différentes insertions dans ces tables.

Pour faire exécuter les commandes de ce fichier :

Lancer sqlite3 et taper .read creat_biblio.sql

Si tout se passe bien, sans erreur :

sqlite>.read creat_biblio.sql
sqlite>

Maintenant vous pouvez verifier que les tables ont été créées avec .tables et leur contenu avec la requête SELECT * FROM Livre ;

Que se passe-t-il si on a d’abord lancé la commande .header ON ou bien la commande .header OFF, ou bien la commande .mode line ou .mode column?

.width permet de donner une taille d’affichage à chaque colonne

.output resultats.txt permet de ne pas faire un affichage écran mais de recupérer les résultats dans le fichier « resultats.txt »

.output stdout permet de revenir au mode écran.

Corrigez les erreurs d’insertion suivantes :

INSERT INTO Membre VALUES (2, 'Topeck', 'Marseille', '13012');

INSERT INTO Livre VALUES ('056G667X','La planète des sages', 'Jul&Pepin', 6, 208);

INSERT INTO Livre VALUES ('0056561U','Mort sur le Nil', 'Agatha Christie', 1, 77);

INSERT INTO Biblio VALUES (77, NULL, 'Marseille', '13005');

Pour démarrer le travail sur les requêtes, regardons les catégories de livres. Dans la base, les romans sont référencés par le code 1, les nouvelles par le code 2, la poésie par le code 3, la bande dessinée par le code 4, et le théâtre par le code 5.

Pour afficher tous les romans, il faut donc écrire :

SELECT * 
FROM Livre
WHERE categLivre = 1; 

Pour les compter:

SELECT COUNT(*) 
FROM Livre
WHERE categLivre = 1; 

Comptage par catégorie :

SELECT categLivre, COUNT(*) 
FROM Livre
GROUP BY categLivre; 

Améliorons maintenant l’affichage avec un ‘CASE’

SELECT    CASE categLivre 
      WHEN 1 THEN 'Romans' 
      WHEN 2 THEN 'Nouvelles' 
      WHEN 3 THEN 'Poésie' 
      WHEN 4 THEN  'Bandes dessinées' 
      WHEN 5 THEN  'Théâtre' 
END, COUNT(*) 
FROM Livre
GROUP BY categLivre; 

Il est recommandé de mettre cette requête dans un fichier requetes.sql puis de lancer son exécution par .read requetes.sql

Il vous reste maintenant à trouver quelques requêtes.

1. Liste des auteurs présents dans la base.

2. Liste des livres de Victor Hugo.

3. Liste des livres de Victor Hugo appartenant à la catégorie 5 (Théâtre).

4. Liste des livres de Jacques Prévert ou de Gilbert Sinoué.

5. Liste des membres n’habitant pas à Marseille.

6. Liste des livres (titre, auteur) empruntés au moins une fois.

7. Liste des livres jamais empruntés.

8. Liste des membres qui ont emprunté Ruy Blas.

9. Liste des livres empruntés par des membres habitant dans le 8ème arrondissement de Marseille.

10. Quels sont les livres qui ont été empruntés au cours de la période allant du 16/01/10 au 15/06/10?

11. Donner la liste des livres se trouvant à St Charles ou à République.

12. Quels sont les numéros des membres qui ont emprunté au moins un livre à Marseille pour une durée supérieure ou égale à 7 jours ?

13. Liste des membres qui ont emprunté à la fois ‘Contes pour les enfants pas sages’ et ‘Avicenne’

14. Quels sont les numéros des livres qui ont été empruntés au moins deux fois?

15. Quels sont les numéros des livres qui ont été empruntés au moins deux fois au cours de la période allant du 16/01/10 au 15/03/10?

16. Pour chaque catégorie de livre, donner le nombre de livres disponibles.

17. Pour chaque catégorie de livre, donner le nombre de livres empruntés au moins une fois.

18. Donner les noms des membres qui ont emprunté plus de deux livres.

19. Quels sont les numéro et noms des membres qui ont emprunté au moins un livre écrit par Victor Hugo mais qui n’ont jamais emprunté de livre écrit par Jacques Prévert ?

20. Quels sont les nom et adresse complète des membres qui ont empruntés des livres de tous les auteurs répertoriés ?