Le langage SQL : Le langage de manipulation de données (LMD)

Introduction

Le programme se limite au sous-ensemble LMD. Nous serons tout de même obligés d’utiliser l’ordre de création de table CREATE TABLE.

SQLite

Dans ce cours nous utiliserons SQLite depuis l’interface de Repl.it .

SQLite est un moteur de base de données relationnelle (sous-ensemble d’un SGBD) accessible par le langage SQL. Contrairement aux serveurs de bases de données traditionnels, comme MySQL ou PostgreSQL, sa particularité est de ne pas reproduire le schéma habituel client-serveur mais de fonctionner sans serveur (on parle de base de données « standalone » ou « embarquée »). L’intégralité de la base de données (déclarations, tables, index et données) est stockée dans un fichier indépendant de la plateforme.

Remarque

On peut accéder à SQLite depuis de nombreux langages de programmation. Nous utiliserons dans un premier temps une interface en ligne de commande (CLI) et par la suite Python.

SQLite est le moteur de base de données le plus utilisé au monde.

Création d’une première base de données

On possède un fichier tableur contenant des informations sur quelques acteurs, parmi les plus célèbres.

On souhaite transformer ce fichier en une base de données contenant, pour l’instant, une seule relation.

1. Réaliser une copie de l’ environnement de travail sur repl.it

L’instruction de création de la table (relation) est

1
2
3
4
5
6
7

CREATE TABLE  IF NOT EXISTS "Celebrites" (
    Nom varchar(40),
    Prenom varchar(40),
    Sexe varchar(6),
    Pays varchar(15),
    Age int
);

2. Indiquer la signification de chaque ligne de la commande.

3. Incorporer la commande au fichier celebrites.sql et l’exécuter.

4. Afin de vérifier que la commande a bien créé une table dans la base de données qui réside pour l’instant dans la RAM, ajouter la commande suivante au fichier main.sql

1
2

-- Énumérations des tables
.tables

Relancer l’exécution des commandes et observer le résultat dans la console.

5. Afin d’examiner la structure de la table Celebrites, ajouter l’une des commandes suivantes au fichier main.sql.

1
2

-- Structure de la table Celebrites
.schema 'Celebrites'

ou

1
2

-- Structure de la table Celebrites
pragma table_info('Celebrites');

La commande permettant d’intégrer le tuple ("Hanks", "Tom", "Homme", "USA", 64) à la relation Celebrites est :

1

INSERT INTO Celebrites VALUES ("Hanks", "Tom", "Homme", "USA", 64);

6. Intégrer la commande précédente au fichier celebrites.sql. Exécuter cette commande. La sortie fait-elle apparaître le résultat de cette commande ?

7. Afin de vérifier que les informations ont bien été ajoutées à la base de données, ajouter la commande suivante dans le fichier main.sql

1

SELECT * FROM Celebrites;

Exécuter cette commande.

8. Ajouter maintenant au fichier celebrites.sql toutes les commandes permettant de peupler la base de données.
   Exécuter ces commandes.

9. La base de données réside toujours en mémoire et n’a, pour l’instant, pas été sauvegardée dans un fichier. Ajouter la commande suivante à la fin du fichier celebrites.sql

1

.save "cinema.sqlite"

Vérifier que le fichier cinema.sqlite a bien été créé.

1

.open "cinema.sqlite"

Premières requêtes : Projection

10. La commande SELECT * FROM Celebrites; est une requête. Que signifie le caractère * dans cette commande ?

11. Adapter la commande précédente de façon à obtenir tous les noms des acteurs présents dans la base de données.

1
2

SELECT Nom 
FROM Celebrites;

12. Adapter la commande précédente de façon à obtenir tous les noms et sexes des acteurs présents dans la base de données.

1
2

SELECT Nom, Sexe 
FROM Celebrites;

Premières requêtes : Sélection

Sélections simples

13. Écrire la commande qui retourne toutes les informations relatives aux femmes présentes dans la base de données.

1
2
3

SELECT * 
FROM Celebrites
WHERE Sexe = "Femme";

14. Adapter la commande précédente de façon à obtenir tous les noms et prénoms des femmes présentes dans la base de données.

1
2
3

SELECT Nom, prenom 
FROM Celebrites
WHERE Sexe = "Femme";

Opérateurs de comparaison

15. Quels sont les noms de tous les acteurs (sans distinction de sexe) qui n’ont pas pour pays d’origine les USA.

1
2
3

SELECT Nom
FROM Celebrites
WHERE Pays != "USA";

16. Quels sont les noms des acteurs (sans distinction de sexe) qui ont plus de 40 ans ?

1
2
3

SELECT Nom
FROM Celebrites
WHERE Age > 40;

17. Quels sont les noms et prénoms des acteurs (sans distinction de sexe) qui ont plus de 40 ans ?

1
2
3

SELECT Nom, Prenom
FROM Celebrites
WHERE Age > 40;

18. Quels sont les noms des acteurs (sans distinction de sexe) qui ont au plus 36 ans ?

1
2
3

SELECT Nom
FROM Celebrites
WHERE Age <= 36;

19. Quels sont les noms des actrices françaises ?

1
2
3

SELECT Nom
FROM Celebrites
WHERE Sexe = "Femme" AND Pays = "France";

20. Quels sont les ages des acteurs américains hommes dont le prénom est Tom ?

1
2
3

SELECT Age
FROM Celebrites
WHERE Sexe = "Homme" AND Prenom = "Tom" AND Pays = "USA";

21. Quels sont les prénoms des acteurs (sans distinction de sexe) australiens ou de plus de 40 ans ?

1
2
3

SELECT Prenom
FROM Celebrites
WHERE Age > 40 OR Pays = "Australie";

Exercices d’application

Les sportifs stars

Écrire toutes les commandes correspondant au requêtes ci-dessous pour la base de donnée se trouvant à cette adresse

1. Donner le schéma relationnel de la base de données.
2. Afficher les noms de tous les sportifs.
3. Afficher les noms et prénoms de tous les sportifs.
4. Afficher les noms, prénoms et sports de tous les sportifs.
5. Afficher le nom des sportifs nés au mois de décembre.
6. Afficher les noms et prénoms des sportifs nés en juin.
7. Afficher les noms et prénoms des sportifs nés en 1981.
8. Afficher les noms et prénoms des sportifs pratiquant le football.

Instagram

Écrire toutes les commandes correspondant au requêtes ci-dessous pour la base de donnée se trouvant à cette adresse

1. Donner le schéma relationnel de la base de données.
2. Afficher toutes les informations relatives aux utilisateurs qui ne sont pas des footballeurs.
3. Afficher toutes les informations relatives aux utilisateurs qui ont plus de 200 million followers.
4. Afficher toutes les informations relatives des utilisateurs qui ont au plus 200 million followers.
5. Afficher toutes les informations relatives des utilisateurs qui ont au moins 200 million followers.
6. Afficher les prénoms, noms et « Instagram handle » des comptes qui ont au maximum 155 millions de followers.
7. Afficher toutes les informations pour les comptes qui correspondent à des chanteurs et qui ont plus de 200 millions de followers.
8. Afficher toutes les informations pour les comptes qui correspondent à des footballeurs ou qui ont moins de 150 millions de followers.

Première requêtes : Ordres SQL

22. Rechercher toutes les informations sur les acteurs, triées par age croissant.

1
2
3

SELECT *
FROM Celebrites
ORDER BY Age;

23. Rechercher toutes les informations sur les acteurs, triées par pays d’origine (par ordre décroissant).

1
2
3

SELECT *
FROM Celebrites
ORDER BY Pays DESC;

24. Rechercher les noms et prénoms des acteurs triés par age et nom croissants.

1
2
3

SELECT Nom, Prénom
FROM Celebrites
ORDER BY Age, Nom;

25. Rechercher les noms et prénoms des acteurs triés par age décroissant et nom croissant.

1
2
3

SELECT Nom, Prénom
FROM Celebrites
ORDER BY Age DESC, Nom ASC;

26. Rechercher les ages et sexes des acteurs, triés par age croissant.

1
2
3

SELECT Age, Sexe
FROM Celebrites
ORDER BY Age;

27. Écrire une requête qui affiche toutes les informations sur les artistes dont les ages sont compris entre 30 et 60 ans.

1
2
3

SELECT Age, Sexe
FROM Celebrites
WHERE Age BETWEEN 30 AND 60;

Première requêtes : recherche de motifs lors d’une sélection

28. Rechercher les prénoms des acteurs dont le nom commence par la lettre j.

1
2
3

SELECT Prenom
FROM Celebrites
WHERE Nom LIKE 'J%';

29. Rechercher les noms et prénoms des acteurs dont le sexe commence par la lettre h, trié par nom croissant.

1
2
3
4

SELECT Nom, Prenom
FROM Celebrites
WHERE Sexe LIKE 'h%'
ORDER BY Nom;

30. Rechercher les noms et prénoms des acteurs dont le prénom se termine par la lettre t.

1
2
3

SELECT Nom, Prenom
FROM Celebrites
WHERE Prenom LIKE '%t';

31. Rechercher les noms et prénoms des acteurs dont le prénom contient la lettre a.

1
2
3

SELECT Nom, Prenom
FROM Celebrites
WHERE Prenom LIKE '%a%';

32. Rechercher les noms et prénoms des acteurs dont le pays d’origine commence par un a.

1
2
3

SELECT Nom, Prenom
FROM Celebrites
WHERE Pays LIKE 'a%';

33. Rechercher les noms et prénoms des acteurs dont le pays d’origine se termine par un l.

1
2
3

SELECT Nom, Prenom
FROM Celebrites
WHERE Pays LIKE '%l';

34. Rechercher les noms et prénoms des acteurs dont le pays d’origine comporte un a.

1
2
3

SELECT Nom, Prenom
FROM Celebrites
WHERE Pays LIKE '%a%';

Exercice d’application

Écrire toutes les commandes correspondant au requêtes ci-dessous pour la base de donnée se trouvant à cette adresse

1. Rechercher toutes les informations sur les animaux dont le nom commence par la lettre p.

2. Rechercher toutes les informations sur les animaux dont le nom se termine par la lettre e et qui sont En danger.

3. Rechercher toutes les informations sur les animaux dont le nom comporte la lettre a et qui sont Vulnérable. Le résultat doit être trié par nom (ascendant).

4. Afficher le nom de tous les animaux et leur état de préservation.

5. Afficher le nom de tous les animaux vulnérables par ordre décroissant de la vitesse de déplacement.

6. Afficher le nom et la durée de vie moyenne des animaux dont le nom comporte un e. L’affichage doit être effectué par durée de vie moyenne croissante.

7. Afficher les nom, durée de vie moyenne et vitesse de déplacement des animaux dont le nom comporte un o, par vitesse de déplacement décroissante.

Modification des entrées d’une base de données

35. Faire en sorte que l’age de l’actrice Margot Robbie soit égal à 29 ans.

1
2
3

UPDATE Celebrites
SET Age = 29
WHERE Nom = "Robbie";

Vérifier que la modification a bien été effectuée.

36. Modifier le fichier contenant la base de données de façon à ce que la modification, uniquement effectuée en mémoire vive pour l’instant, soit enregistrée de façon permanente.

37. Le vrai nom de Tom Cruise est Thomas Cruise Mapother IV. Modifier l’entrée dans la base de données.

1
2
3

UPDATE Celebrites
SET Nom = "Cruise Mapother IV", prenom = "Thomas"
WHERE Nom = "Cruise";

38. Supprimer l’acteur Tom Holland de la table Celebrites.

1
2

DELETE FROM Celebrites
WHERE Nom = "Holland";

Exercice d’application

Écrire toutes les commandes correspondant au requêtes ci-dessous pour la base de donnée se trouvant à cette adresse

1. Écrire une requête qui affiche tous les enregistrements de la table « Superheroes and Villains ».

2. Écrire une requête qui affiche la structure de la table « Superheroes and Villains ».

3. Écrire une requête qui affiche les noms des superheroes femmes.

4. Écrire une requête qui affiche les noms et types (gentil ou méchant) des films X-Men ou Guardians of the Galaxy.

5. Écrire une requête qui affiche tous les noms des personnages par ordre alphabétique.

6. Écrire une requête qui affiche les types des personnages et leurs noms par ordre alphabétique croissant pour les types mais ordre alphabétique décroissant pour les noms.

7. Écrire une requête qui affiche les noms des personnages commençant par la lettre M.

8. Écrire une requête qui affiche les noms des personnages se terminant par la lettre N.

9. Écrire une requête qui affiche les noms des personnages contenant la lettre I.

10. Écrire une requête qui modifie le nom du personnage Groot. Le nouveau nom est Baby Groot. Afficher le résultat de cette requête.

11. Écrire une requête qui supprime l’enregistrement dont le nom est Queen of Hearts. Afficher la table pour vérifier le résultat de la requête.

Exercice d’application à partir de données réelles et conséquentes

La base de donnée contient tous les indicateurs des lycées français présentés sur le site de l’éducation nationale et dont les données sont en accès “libre” sur data.gouv.fr . La notice descriptive est fournie.

• Écrire 10 requêtes qui vous semblerons utiliser au mieux cette base de données.