272 lines
9.1 KiB
Markdown
272 lines
9.1 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
|
||
|
||
- 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 triangle vert (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 triangle bleu 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 nous obtenons bien le livre écrit par Asimov publié 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 : "Clarcke"
|
||
- 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.
|
||
|
||
--------
|
||
|
||
Source :
|
||
|
||
- [Pixees, site de David Roche](https://informatique-lycee.forge.apps.education.fr/terminale_nsi/cours-terminale/c3a/)
|
||
|
||
|