TNSI : TD SQLite⚓︎

Dans ce TD¹, nous allons travailler avec le langage SQLite qui peut s'utiliser directement sans démarrer un serveur : la base de données est entièrement représentée par un fichier .db dans le logiciel utilisant SQLite : dans notre cas, DB Browser for SQLite.

La Base de Données `livres.db`⚓︎

Pré-requis : Téléchargez la base de données livres.db modélisée par :

erDiagram livre { int isbn PK varchar_300 titre varchar_90 editeur int annee FK } auteur { int a_id PK varchar_90 nom varchar_90 prenom } auteur_de { int a_id PK char_14 isbn PK } usager { char_15 code_barre PK varchar_90 nom varchar_90 prenom varchar_300 adresse varchar_5 cp varchar_60 ville varchar_60 email } emprunt { char_14 isbn PK char_15 code_barre "NULL" date retour }

Différents moyens d'interroger la base de données⚓︎

1. En ligne avec sqliteonline.com

Rendez vous sur https://sqliteonline.com/
Par File -> Open DB, ouvrez le fichier livres.db précédemment téléchargé.
Écrivez votre requête plus exécutez-là:
cliquez sur Run ou
Shift+Enter.

2. Au sein d'un notebook Jupyter

Si nécessaire, installez via le terminal les paquets suivants :

sudo pip3 install jupyter-sql
sudo pip3 install ipython-sql
sudo apt install python3-sql

Dans un notebook Jupyter, votre première cellule doit être
```
%load_ext sql
%sql sqlite:///livres.db
```
en ayant bien pris soin de mettre le fichier livres.db dans le même répertoire que votre fichier Jupyter.

Ensuite, chaque requête devra être précédée de la ligne %% sql.

3. Avec un logiciel externe : DB Browser for SQLite

Installez DB Browser for SQLite, téléchargeable à l'adresse https://sqlitebrowser.org/
Ouvrez le fichier livres.db avec Fichier -> Ouvrir une Base de Données... (Ctrl+O)
Sélectionner l'Onglet Exécuter le SQL (un peu caché à droite)

Sélection de données⚓︎

Exemple 1⚓︎

Requête basique : SELECT, FROM, WHERE

Commande :

SELECT titre FROM livre WHERE annee >= 1990;

Traduction :

On veut les titres de la table «livre» qui sont parus après (ou en ) 1990;

Résultat :

Exemple 2⚓︎

Requête avec booléen : AND

Commande :

SELECT titre FROM livre WHERE   annee >= 1970 AND
                                annee <= 1980 AND
                                editeur = 'Dargaud';

Traduction :

On veut les titres de la table «livre» qui sont parus entre 1970 et 1980 chez l'éditeur Dargaud;

Résultat :

Exemple 3⚓︎

Requête approchée : LIKE

Commande :

SELECT titre FROM livre WHERE titre LIKE '%Astérix%';

Traduction :

On veut les titres de la table «livre» dont le titre contient la chaîne de caractères "Astérix".
Le symbole % est un joker qui peut symboliser n'importe quelle chaîne de caractères.

Résultat :

Exemple 4⚓︎

Plusieurs colonnes

Commande :

SELECT titre, isbn FROM livre WHERE annee >= 1990;

Traduction :

On veut les titres et les ISBN de la table «livre» qui sont parus après 1990.

Résultat :

Exemple 5⚓︎

Toutes les colonnes : *

Commande :

SELECT * FROM livre WHERE annee >= 1990;

Traduction :

On veut toutes les colonnes disponibles de la table «livre» pour les livres qui sont parus après 1990.
L'astérisque * est un joker (wildcard en anglais).

Résultat :

Exemple 6⚓︎

Renommer les colonnes : AS

Commande :

SELECT titre AS titre_du_livre FROM livre WHERE annee >= 1990;

Traduction :

Lors de l'affichage du résulats et dans la suite de la requête (important), la colonne "titre" est renommée "titre_du_livre".

Résultat :

Opérations sur les données : sélection avec agrégation⚓︎

Les requêtes effectuées jusqu'ici ont juste sélectionné des données grâce à différents filtres : aucune action à partir de ces données n'a été effectuée.
Nous allons maintenant effectuer des opérations à partir des données sélectionnées. On appelle ces opérations des opérations d'agrégation.

Exemple 7⚓︎

Compter : COUNT

Commande :

SELECT COUNT(*) AS total FROM livre
                            WHERE titre LIKE "%Astérix%";

Traduction :

On veut compter le nombre d'enregistrements de la tables livres comportant le mot "Astérix". Le résultat sera le seul élément d'une colonne nommée «total».

Résultat :

Exemple 8⚓︎

Additionner : SUM

Commande :

SELECT SUM(annee) AS somme FROM livre
                            WHERE titre LIKE "%Astérix%";

Traduction :

On veut additionner les années des livres de la tables livres comportant le mot "Astérix". Le résultat sera le seul élément d'une colonne nommée «somme». Attention : dans notre cas précis, ce calcul n'a aucun sens...

Résultat :

Exemple 9⚓︎

Faire une moyenne : AVG

Commande :

SELECT AVG(annee) AS moyenne FROM livre
                            WHERE titre LIKE "%Astérix%";

Traduction :

On veut calculer la moyenne des années de parution des livres de la table livres comportant le mot "Astérix". Le résultat sera le seul élément d'une colonne nommée «moyenne».

Résultat :

Exemple 10⚓︎

Trouver les extremums : MIN, MAX

Commande :

SELECT MIN(annee) AS minimum FROM livre
                            WHERE titre LIKE "%Astérix%";

Traduction :

On veut trouver la pus petite valeur de la colonne «annee» parmi les livres de la tables livre comportant le mot "Astérix". Le résultat sera le seul élément d'une colonne nommée minimum. Le fonctionnement est identique avec MAX pour la recherche du maximum.

Résultat :

Exemple 11⚓︎

Classer des valeurs : ORDER BY, ASC, DESC

Commande :

SELECT titre, annee FROM livre
                WHERE titre LIKE "%Astérix%"
                ORDER BY annee DESC;

Traduction :

On veut afficher tous les albums d'Astérix, et leur année de parution, classés par année décroissante.

Résultat :

Comportement par défaut : Si le paramètre ASC ou DESC est omis, le classement se fait par ordre croissant (donc ASC est le paramètre par défaut).

Exemple 12⚓︎

Suppression des doublons : DISTINCT

Commande :
```
SELECT DISTINCT editeur FROM livre;
```
Traduction :

On veut la liste de tous les éditeurs. Sans le mot-clé DISTINCT, beaucoup de doublons apparaîtraient.

Résultat :

Jointures : Des recherches croisées sur les tables⚓︎

Observons le contenu de la table «emprunt» :

SELECT * FROM emprunt;

Le contenu est peu lisible : qui a emprunté quel livre ?
Souvenons-nous du diagramme de la base de données.

Pour que la table «emprunt» soit lisible, il faudrait (dans un premier temps) que l'on affiche à la place de l'ISBN le titre de l'ouvrage. Or ce titre est disponible dans la table «livres». On va donc procéder à une jointure de ces deux tables.

Exemple 13⚓︎

Jointure de 2 tables : JOIN

Commande :

SELECT livre.titre, emprunt.code_barre, emprunt.retour FROM emprunt
        JOIN livre ON emprunt.isbn = livre.isbn;

Traduction : Comme plusieurs tables sont appelées, nous préfixons chaque colonne avec le nom de la table. Nous demandons ici l'affichage de la table «emprunt», mais où on aura remplacé l'ISBN (peu lisible) par le titre du livre.

L'expression

JOIN livre ON emprunt.isbn = livre.isbn

doit se comprendre comme ceci : on «invite» la table «livres» (dont on va afficher la colonne «titre»). La correspondance entre la table «livres» et la table «emprunt» doit se faire sur l'attribut ISBN, qui est la clé primaire de «livres» et une clé étrangère d'«emprunts».
Il est donc très important de spécifier ce sur quoi les deux tables vont se retrouver (ici, l'ISBN)

Résultat :

Exemple 14⚓︎

Le résultat précédemment a permis d'améliorer la visibilité de la table «emprunt», mais il reste la colonne «code_barre» qui est peu lisible. Nous pouvons la remplacer par le titre du livre, en faisant une nouvelle jointure, en invitant maintenant les deux tables «livre» et «usager».

Jointure de 3 tables : JOIN

Commande :

SELECT u.nom, u.prenom, l.titre, e.retour FROM emprunt AS e
        JOIN livre AS l ON e.isbn = l.isbn
        JOIN usager AS u ON e.code_barre = u.code_barre;

Traduction : Il faut bien comprendre que la table principale qui nous intéresse ici est «emprunts», mais qu'on modifie les valeurs affichées en allant chercher des correspondances dans deux autres tables. Notez ici que des alias sont donnés aux tables (par AS) afin de faciliter l'écriture.
Résultat :

Exercice d'application : The SQL Murder Mystery⚓︎

Cet exercice en ligne est proposé le Knight Lab de l'université américaine Northwerstern University.

Le point de départ de l'histoire : un meurtre a été commis dans la ville de SQL City le 15 janvier 2018.

À partir de ce point de départ et d'une base de données dont le Diagramme Entité-Association (Diagramme ERD) est donné ci-dessous, il s'agit de trouver le meurtrier.

erDiagram get_fit_now_check_in ||--o{ get_fit_now_member : check_in interview ||--|{ person : gives get_fit_now_member ||--|| person : member facebook_event_checkin o{--o{ person : member person ||--|| drivers_license : has get_fit_now_check_in { string membership_id int check_in_date int check_in_time int check_out_time } get_fit_now_member { string id PK int person_id FK string name int membership_start_date string membership_status } interview { int person_id FK string transcript } facebook_event_checkin { int person_id FK int event_id string event_name int date } person { int id PK string name int license_id FK int adress_number string address_street_name int ssn } drivers_license { int id PK int age int height string eye_color string hair_color string gender string plate_number string car_make string car_model } crime_scene { int date string type string description string city } income { int ssn PK int annual_income } solution { int user string value }

Rendez-vous sur cette page, et bonne enquête à coups de requêtes !

Vous pouvez travailler en ligne ou bien dans votre SGBD préféré, avec la base sql-murder-mystery.db. Attention pour valider votre réponse, il faudra vous rendre en bas de la page officielle.
Vous pouvez trouver des éléments de correction ici.

Création et modification d'une base de données⚓︎

L'objectif est de créer la table suivante :

Id	Nom	Maths	Anglais	NSI
1	Alice	16	11	17
2	Bob	12	15	10
3	Charles	9	11	18

Exemple 15⚓︎

La création d'une table n'est pas explicitement au programme de NSI.

Création d'une table : CREATE TABLE

Commande :

CREATE TABLE Table_notes (
        Id INTEGER PRIMARY KEY,
        Nom TEXT,
        Maths INTEGER,
        Anglais INTEGER,
        NSI INTEGER
        );

Remarques :

C'est l'utilisateur qui spécifie, éventuellement, quel attribut sera une clé primaire.

Résultat :
Dans DB Browser, il faut avoir au préalable créé une nouvelle base de données.

Exemple 16⚓︎

Insertion de valeurs : INSERT INTO, VALUES

Commande :

INSERT INTO Table_notes VALUES  (1, 'Alice', 16, 11, 17),
                        (2, 'Bob', 12, 15, 10),
                        (3, 'Charles', 9, 11, 18);

Résultat :

Exemple 17 : Intérêt de la clé primaire⚓︎

Essayons d'insérer un 4ème enregistrement ayant le même id qu'un autre élève.

Commande :

INSERT INTO Table_notes VALUES  (3, 'Denis', 18, 10, 12);

Résultat :
La contrainte de relation est violée : le SGBD «protège» la base de données en n'acceptant pas la proposition d'insertion. La base de données n'est pas modifiée.

Remarque : Il est possible de «déléguer» la gestion des valeurs de la clé primaire avec l'instruction AUTOINCREMENT. La déclaration de la table et l'insertion des valeurs serait :

CREATE TABLE Table_notes (
        Id INTEGER PRIMARY KEY AUTOINCREMENT,
        Nom TEXT,
        Maths INTEGER,
        Anglais INTEGER,
        NSI INTEGER
        );

INSERT INTO Table_notes (Nom, Maths, Anglais, NSI) VALUES
    ('Alice', 16, 11, 17),
    ('Bob', 12, 15, 10),
    ('Charles', 9, 11, 18);

et le résultat serait :

L'attribut id est donc géré automatiquement par le SGBD.

Exemple 18⚓︎

Modification d'une valeur UPDATE, SET

Pour modifier la note de Maths d'Alice :

Commande :

UPDATE Table_notes SET Maths = 18 WHERE Nom = 'Alice';

Exemple 19⚓︎

Suppression d'un enregistrement : DELETE

Pour supprimer totalement la ligne concernant Charles :

Commande :

DELETE FROM Table_notes WHERE Nom = 'Charles';

Si une autre table contient par exemple l'attribut id comme clé étrangère, et si l'id de Charles fait partie de cette table, le SGBD refusera de supprimer cette ligne, afin de ne pas violer la contrainte de référence.

Exemple 20⚓︎

Suppression totale d'une table : DROP TABLE

Pour supprimer totalement et défitivement la table :

Commande :
```
DROP TABLE Table_notes;
```

Là encore, si une autre table est reliée à Table_notes par une clé étrangère, la suppression sera bloquée par le SGBD.

Références⚓︎

Ce TD est directement tiré du site de Gilles Lassus ↩

TNSI : TD SQLite⚓︎

La Base de Données livres.db⚓︎

Différents moyens d'interroger la base de données⚓︎

Sélection de données⚓︎

Exemple 1⚓︎

Exemple 2⚓︎

Exemple 3⚓︎

Exemple 4⚓︎

Exemple 5⚓︎

Exemple 6⚓︎

Opérations sur les données : sélection avec agrégation⚓︎

Exemple 7⚓︎

Exemple 8⚓︎

Exemple 9⚓︎

Exemple 10⚓︎

Exemple 11⚓︎

Exemple 12⚓︎

Jointures : Des recherches croisées sur les tables⚓︎

Exemple 13⚓︎

Exemple 14⚓︎

Exercice d'application : The SQL Murder Mystery⚓︎

Création et modification d'une base de données⚓︎

Exemple 15⚓︎

Exemple 16⚓︎

Exemple 17 : Intérêt de la clé primaire⚓︎

Exemple 18⚓︎

Exemple 19⚓︎

Exemple 20⚓︎

Références⚓︎

La Base de Données `livres.db`⚓︎