373 lines
13 KiB
Markdown
373 lines
13 KiB
Markdown
# TP : Introduction au langage SQL avec DB Browser for SQLite
|
||
|
||
Ce TP vous guidera à travers la création et la manipulation d'une base de données en utilisant le logiciel **DB Browser for SQLite**. Vous apprendrez à créer des tables, insérer des données et exécuter des requêtes SQL pour interroger la base de données.
|
||
|
||
> **Prérequis** : Avoir lu le cours [README.md](README.md) avant de commencer ce TP.
|
||
|
||
## Prérequis
|
||
|
||
- Télécharger et installer [DB Browser for SQLite](https://sqlitebrowser.org/).
|
||
|
||
## Activité 3.1 : Création d'une base de données
|
||
|
||
1. **Lancer DB Browser for SQLite**. Vous devriez obtenir l'écran suivant :
|
||
|
||
|
||
|
||
2. **Créer une nouvelle base de données** :
|
||
|
||
- Cliquez sur "Nouvelle base de données".
|
||
- Nommez votre base de données, par exemple `db_livres.db`.
|
||
- Vous verrez la fenêtre suivante :
|
||
|
||
|
||
|
||
- Cliquez sur "Annuler" pour fermer cette fenêtre.
|
||
|
||
3. **Vérifier la création de la base de données** :
|
||
- Votre base de données est créée mais ne contient pas encore de tables.
|
||
|
||
|
||
|
||
## Activité 3.2 : Création d'une table
|
||
|
||
1. **Accéder à l'onglet "Exécuter le SQL"** :
|
||
|
||
- Cliquez sur l'onglet "Exécuter le SQL".
|
||
|
||
2. **Créer la table `LIVRES`** :
|
||
- Dans la fenêtre "SQL 1", saisissez la requête suivante :
|
||
|
||
```sql
|
||
CREATE TABLE LIVRES (
|
||
id INT PRIMARY KEY,
|
||
titre TEXT,
|
||
auteur TEXT,
|
||
ann_publi INT,
|
||
note INT
|
||
);
|
||
```
|
||
|
||
- Cliquez sur le bouton ▶ (ou appuyez sur F5) pour exécuter la requête.
|
||
- Un message "Requête exécutée avec succès" devrait apparaître.
|
||
|
||
|
||
|
||
## Activité 3.3 : Insertion de données
|
||
|
||
1. **Insérer des enregistrements dans la table `LIVRES`** :
|
||
|
||
- Toujours dans l'onglet "Exécuter le SQL", saisissez la requête suivante :
|
||
|
||
```sql
|
||
INSERT INTO LIVRES (id, titre, auteur, ann_publi, note) VALUES
|
||
(1, '1984', 'Orwell', 1949, 10),
|
||
(2, 'Dune', 'Herbert', 1965, 8),
|
||
(3, 'Fondation', 'Asimov', 1951, 9),
|
||
(4, 'Le meilleur des mondes', 'Huxley', 1931, 7),
|
||
(5, 'Fahrenheit 451', 'Bradbury', 1953, 7),
|
||
(6, 'Ubik', 'K.Dick', 1969, 9),
|
||
(7, 'Chroniques martiennes', 'Bradbury', 1950, 8),
|
||
(8, 'La nuit des temps', 'Barjavel', 1968, 7),
|
||
(9, 'Blade Runner', 'K.Dick', 1968, 8),
|
||
(10, 'Les Robots', 'Asimov', 1950, 9),
|
||
(11, 'La Planète des singes', 'Boulle', 1963, 8),
|
||
(12, 'Ravage', 'Barjavel', 1943, 8),
|
||
(13, 'Le Maître du Haut Château', 'K.Dick', 1962, 8),
|
||
(14, 'Le monde des Ā', 'Van Vogt', 1945, 7),
|
||
(15, 'La Fin de l’éternité', 'Asimov', 1955, 8),
|
||
(16, 'De la Terre à la Lune', 'Verne', 1865, 10);
|
||
```
|
||
|
||
- Exécutez la requête en cliquant sur le bouton ▶ ou en appuyant sur F5.
|
||
- Un message de confirmation devrait apparaître.
|
||
|
||
|
||
|
||
2. **Vérifier les données insérées** :
|
||
- Cliquez sur l'onglet "Parcourir les données" pour voir les enregistrements ajoutés.
|
||
|
||
|
||
|
||
## Activité 3.4 : Exécution de requêtes SQL
|
||
|
||
1. **Sélectionner tous les enregistrements** :
|
||
- Dans l'onglet "Exécuter le SQL", saisissez la requête suivante :
|
||
|
||
```sql
|
||
SELECT id, titre, auteur, ann_publi, note FROM LIVRES;
|
||
```
|
||
|
||
- Exécutez la requête. Vous devriez obtenir tous les enregistrements de la table `LIVRES`.
|
||
|
||
|
||
|
||
2. **Sélectionner des colonnes spécifiques** :
|
||
- Pour obtenir uniquement les titres et auteurs des livres, utilisez la requête :
|
||
|
||
```sql
|
||
SELECT titre, auteur FROM LIVRES;
|
||
```
|
||
|
||
3. **Filtrer les enregistrements** :
|
||
- Pour obtenir les livres écrits par Asimov, utilisez :
|
||
|
||
```sql
|
||
SELECT titre, ann_publi FROM LIVRES WHERE auteur = 'Asimov';
|
||
```
|
||
|
||
4. **Combiner des conditions** :
|
||
- Pour obtenir les livres d'Asimov publiés après 1953 :
|
||
|
||
```sql
|
||
SELECT titre, ann_publi FROM LIVRES WHERE auteur = 'Asimov' AND ann_publi > 1953;
|
||
```
|
||
|
||
5. **Trier les résultats** :
|
||
- Pour trier les livres de K.Dick du plus ancien au plus récent :
|
||
|
||
```sql
|
||
SELECT titre FROM LIVRES WHERE auteur = 'K.Dick' ORDER BY ann_publi;
|
||
```
|
||
|
||
|
||
|
||
6. Écrivez et testez une requête permettant d'obtenir uniquement les titres des livres écrits par Philip K.Dick.
|
||
|
||
7. Saisissez et testez la requête SQL suivante :
|
||
|
||
```sql
|
||
SELECT titre, ann_publi
|
||
FROM LIVRES
|
||
WHERE auteur='Asimov' AND ann_publi>1953
|
||
```
|
||
|
||
Vérifiez que vous obtenez bien les livres écrits par Asimov publiés après 1953.
|
||
|
||
8. Écrivez une requête permettant d'obtenir les titres des livres publiés après 1945 qui ont une note supérieure ou égale à 9.
|
||
|
||
9. Écrivez une requête SQL permettant d'obtenir les livres de K.Dick classés du plus ancien au plus récent.
|
||
|
||
10. Créez une nouvelle base de données que vous nommerez par exemple db_livres_auteurs.db, puis créez une table AUTEURS à l'aide de la requête SQL suivante :
|
||
|
||
```sql
|
||
CREATE TABLE AUTEURS
|
||
(id INT, nom TEXT, prenom TEXT, ann_naissance INT, langue_ecriture TEXT, PRIMARY KEY (id));
|
||
```
|
||
|
||
|
||
Créez ensuite une deuxième table (LIVRES) :
|
||
|
||
```
|
||
CREATE TABLE LIVRES
|
||
(id INT, titre TEXT, id_auteur INT, ann_publi INT, note INT, PRIMARY KEY (id), FOREIGN KEY (id_auteur) REFERENCES AUTEURS(id));
|
||
```
|
||
|
||
Comme vous l'avez sans doute remarqué nous avons précisé dans notre requête que l'attribut "id_auteur" jouera le rôle de clé étrangère : liaison entre "id_auteur" de la table LIVRES et "id" de la table AUTEURS (FOREIGN KEY (id_auteur) REFERENCES AUTEURS(id)).
|
||
|
||
Ajoutez des données à la table AUTEURS à l'aide de la requête SQL suivante :
|
||
|
||
```sql
|
||
INSERT INTO AUTEURS
|
||
(id,nom,prenom,ann_naissance,langue_ecriture)
|
||
VALUES
|
||
(1,'Orwell','George',1903,'anglais'),
|
||
(2,'Herbert','Frank',1920,'anglais'),
|
||
(3,'Asimov','Isaac',1920,'anglais'),
|
||
(4,'Huxley','Aldous',1894,'anglais'),
|
||
(5,'Bradbury','Ray',1920,'anglais'),
|
||
(6,'K.Dick','Philip',1928,'anglais'),
|
||
(7,'Barjavel','René',1911,'français'),
|
||
(8,'Boulle','Pierre',1912,'français'),
|
||
(9,'Van Vogt','Alfred Elton',1912,'anglais'),
|
||
(10,'Verne','Jules',1828,'français');
|
||
```
|
||
|
||
Ajoutez des données à la table LIVRES à l'aide de la requête SQL suivante :
|
||
|
||
```sql
|
||
INSERT INTO LIVRES
|
||
(id,titre,id_auteur,ann_publi,note)
|
||
VALUES
|
||
(1,'1984',1,1949,10),
|
||
(2,'Dune',2,1965,8),
|
||
(3,'Fondation',3,1951,9),
|
||
(4,'Le meilleur des mondes',4,1931,7),
|
||
(5,'Fahrenheit 451',5,1953,7),
|
||
(6,'Ubik',6,1969,9),
|
||
(7,'Chroniques martiennes',5,1950,8),
|
||
(8,'La nuit des temps',7,1968,7),
|
||
(9,'Blade Runner',6,1968,8),
|
||
(10,'Les Robots',3,1950,9),
|
||
(11,'La Planète des singes',8,1963,8),
|
||
(12,'Ravage',7,1943,8),
|
||
(13,'Le Maître du Haut Château',6,1962,8),
|
||
(14,'Le monde des Ā',9,1945,7),
|
||
(15,'La Fin de l’éternité',3,1955,8),
|
||
(16,'De la Terre à la Lune',10,1865,10);
|
||
```
|
||
|
||
11. Saisissez et testez la requête SQL suivante :
|
||
|
||
```sql
|
||
SELECT titre,nom, prenom
|
||
FROM LIVRES
|
||
INNER JOIN AUTEURS ON LIVRES.id_auteur = AUTEURS.id
|
||
```
|
||
|
||
Rappel : attention, si un même nom d'attribut est présent dans les 2 tables (par exemple ici l'attribut id), il est nécessaire d'ajouter le nom de la table devant afin de pouvoir les distinguer (AUTEURS.id et LIVRES.id).
|
||
|
||
12. Écrivez une requête SQL permettant d'obtenir les titres des livres publiés après 1945 ainsi que le nom de leurs auteurs.
|
||
|
||
13. Nous allons repartir avec une nouvelle base de données. Créez une nouvelle base de données nommée "db_livres.db".
|
||
|
||
Créez ensuite une table LIVRES à l'aide de la requête suivante :
|
||
|
||
```sql
|
||
CREATE TABLE LIVRES
|
||
(id INT, titre TEXT, auteur TEXT, ann_publi INT, note INT, PRIMARY KEY (id));
|
||
```
|
||
|
||
Ajoutez des données à la table LIVRES à l'aide de la requête SQL suivante :
|
||
|
||
```sql
|
||
INSERT INTO LIVRES
|
||
(id,titre,auteur,ann_publi,note)
|
||
VALUES
|
||
(1,'1984','Orwell',1949,10),
|
||
(2,'Dune','Herbert',1965,8),
|
||
(3,'Fondation','Asimov',1951,9),
|
||
(4,'Le meilleur des mondes','Huxley',1931,7),
|
||
(5,'Fahrenheit 451','Bradbury',1953,7),
|
||
(6,'Ubik','K.Dick',1969,9),
|
||
(7,'Chroniques martiennes','Bradbury',1950,8),
|
||
(8,'La nuit des temps','Barjavel',1968,7),
|
||
(9,'Blade Runner','K.Dick',1968,8),
|
||
(10,'Les Robots','Asimov',1950,9),
|
||
(11,'La Planète des singes','Boulle',1963,8),
|
||
(12,'Ravage','Barjavel',1943,8),
|
||
(13,'Le Maître du Haut Château','K.Dick',1962,8),
|
||
(14,'Le monde des Ā','Van Vogt',1945,7),
|
||
(15,'La Fin de l’éternité','Asimov',1955,8),
|
||
(16,'De la Terre à la Lune','Verne',1865,10);
|
||
```
|
||
|
||
14. À l'aide d'une requête SQL, ajoutez à la table LIVRES le livre suivant :
|
||
- id : 17
|
||
- titre : "2001 : L'Odyssée de l'espace"
|
||
- auteur : "Clarke"
|
||
- année de publication : 1968
|
||
- note : 7
|
||
|
||
|
||
|
||
15. Écrivez une requête permettant d'attribuer la note de 10 à tous les livres écrits par Asimov publiés après 1950. Testez cette requête.
|
||
16. Écrivez une requête permettant de supprimer les livres publiés avant 1945. Testez cette requête.
|
||
|
||
## Activité 3.5 : Fonctions d'agrégation
|
||
|
||
> **Prérequis** : utiliser la base `db_livres.db` créée à l'activité 3.4 (avec la table LIVRES simple, avant les suppressions de la question 16 - si vous avez supprimé des lignes, réinsérez les données avec le `INSERT INTO` de l'activité 3.4).
|
||
|
||
Les fonctions d'agrégation effectuent un calcul sur un ensemble de lignes et renvoient **une seule valeur**.
|
||
|
||
### 1. Compter des enregistrements : COUNT
|
||
|
||
Exécutez les requêtes suivantes et notez les résultats :
|
||
|
||
```sql
|
||
-- Nombre total de livres
|
||
SELECT COUNT(*) FROM LIVRES;
|
||
|
||
-- Nombre de livres écrits par Asimov
|
||
SELECT COUNT(*) FROM LIVRES WHERE auteur = 'Asimov';
|
||
```
|
||
|
||
> `COUNT(*)` compte toutes les lignes. La clause `WHERE` s'applique **avant** le comptage.
|
||
|
||
### 2. Calculer une moyenne, un min, un max : AVG, MIN, MAX
|
||
|
||
```sql
|
||
-- Note moyenne de tous les livres
|
||
SELECT AVG(note) FROM LIVRES;
|
||
|
||
-- Note la plus basse et la plus haute
|
||
SELECT MIN(note), MAX(note) FROM LIVRES;
|
||
|
||
-- Livre le plus ancien
|
||
SELECT MIN(ann_publi) FROM LIVRES;
|
||
```
|
||
|
||
> Les alias permettent de nommer le résultat :
|
||
> ```sql
|
||
> SELECT AVG(note) AS note_moyenne FROM LIVRES;
|
||
> ```
|
||
|
||
17. Écrivez une requête donnant la note moyenne des livres de Bradbury.
|
||
|
||
18. Écrivez une requête donnant l'année de publication du livre le plus récent dans la base.
|
||
|
||
### 3. Regrouper les résultats : GROUP BY
|
||
|
||
`GROUP BY` divise les lignes en groupes selon les valeurs d'une colonne, et applique la fonction d'agrégation à chaque groupe séparément.
|
||
|
||
```sql
|
||
-- Nombre de livres par auteur
|
||
SELECT auteur, COUNT(*) AS nb_livres
|
||
FROM LIVRES
|
||
GROUP BY auteur;
|
||
```
|
||
|
||
Exécutez cette requête. Vous devriez obtenir une ligne par auteur avec son nombre de livres.
|
||
|
||
```sql
|
||
-- Note moyenne par auteur
|
||
SELECT auteur, AVG(note) AS note_moyenne
|
||
FROM LIVRES
|
||
GROUP BY auteur
|
||
ORDER BY note_moyenne DESC;
|
||
```
|
||
|
||
19. Écrivez une requête donnant, pour chaque auteur, l'année de son livre le plus ancien.
|
||
|
||
### 4. Filtrer les groupes : HAVING
|
||
|
||
`WHERE` filtre les lignes **avant** le regroupement. Pour filtrer les groupes **après**, on utilise `HAVING`.
|
||
|
||
```sql
|
||
-- Auteurs ayant écrit au moins 2 livres dans la base
|
||
SELECT auteur, COUNT(*) AS nb_livres
|
||
FROM LIVRES
|
||
GROUP BY auteur
|
||
HAVING COUNT(*) >= 2;
|
||
```
|
||
|
||
> **À retenir :** `WHERE` porte sur les lignes individuelles, `HAVING` porte sur les groupes.
|
||
|
||
```sql
|
||
-- Auteurs dont la note moyenne dépasse 8
|
||
SELECT auteur, AVG(note) AS note_moyenne
|
||
FROM LIVRES
|
||
GROUP BY auteur
|
||
HAVING AVG(note) > 8;
|
||
```
|
||
|
||
20. Écrivez une requête listant les auteurs ayant publié des livres dans **deux décennies différentes** au minimum.
|
||
*(Indice : `MIN(ann_publi)` et `MAX(ann_publi)` dans le même GROUP BY, puis filtrer avec HAVING sur la différence)*
|
||
|
||
21. Écrivez une requête donnant, parmi les auteurs ayant au moins 2 livres, celui dont la note moyenne est la plus élevée. Triez de façon appropriée et limitez à 1 résultat.
|
||
|
||
--------
|
||
|
||
Source :
|
||
|
||
- [Pixees, site de David Roche](https://informatique-lycee.forge.apps.education.fr/terminale_nsi/cours-terminale/c3a/)
|
||
|
||
---
|
||
|
||
Auteur : Florian Mathieu
|
||
|
||
Licence CC BY NC
|
||
|
||
<a rel="license" href="http://creativecommons.org/licenses/by-nc-sa/4.0/"><img alt="Licence Creative Commons" style="border-width:0" src="https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png" /></a> <br />Ce cours est mis à disposition selon les termes de la <a rel="license" href="http://creativecommons.org/licenses/by-nc-sa/4.0/">Licence Creative Commons Attribution - Pas d'Utilisation Commerciale - Partage dans les Mêmes Conditions 4.0 International</a>.
|
||
|