Le langage SQL

Activité 1 - Un petit jeu pour commencer

Le langage SQL (Structured Query Language) est un langage de requête permettant de manipuler des bases de données relationnelles.

Rendez vous ici et mener l’enquête.

Requêtes d'interrogation

Construction d'une requête simple avec SELECT...FROM

L’instruction de base pour l’interrogation d’une base de données en SQL est constituée du mot-clé SELECT suivi du mot-clé FROM.

• SELECT permet de sélectionner les attributs dont il faut afficher les valeurs. Le caractère \(*\) permet d’afficher les valeurs de tous les attributs.
• FROM permet de sélectionner la relation à explorer.

Une requête SQL finit toujours par un point-virgule ;

Remarque

Le langage SQL n’est pas sensible à la casse. néanmoins, il est coutumier d’écrire les mots-clés en majuscule.

Exemple

On considère la relation LIVRES ( id , titre, auteur, ann_publi)

Elle contient les éléments suivants :

• Affichage de tous les titres présents dans la relation LIVRES

SELECT  titre 
FROM  livres;

renvoie l’affichage :

1984
Dune
Fondation
Farenheit  451
Chroniques  martiennes

• Affichage de tous les titres et années de publication présents dans la relation LIVRES

SELECT  titre,  ann_publi 
FROM  livres;

renvoie l’affichage :

1984,  1949
Dune,  1965
Fondation,  1951
Farenheit  451,  1953
Chroniques  martiennes,  1950

• Affichage de tous les attributs présents dans la relation LIVRES

SELECT  *
FROM  livres;

renvoie l’affichage :

1,  1984,  Orwel,  1949
2,  Dune,  Herbert,  1965
3,  Fondation,  Asimov,  1951
4,  Farenheit  451,  Bradbury  1953
5,  Chroniques  martiennes,  Bradbury,  1950

Rejet des doublons avec DISTINCT

Le mot-clé DISTINCT permet d’éviter l’affichage de doublons.

Exemple

• Affichage de tous les auteurs dans la relation LIVRES

SELECT  auteur 
FROM  livres;

renvoie l’affichage :

Orwell
Herbert
Asimov
Bradbury
Bradbury

• Affichage de tous les auteurs dans la relation LIVRES sans les doublons

SELECT  DISTINCT  auteur 
FROM  livres;

renvoie l’affichage :

Orwell
Herbert
Asimov
Bradbury

Le tri avec ORDER BY

Le mot-clé ORDER BY permet de trier les résultats par ordre croissant.
Pour trier par ordre décroissant, il faut ajouter l’option DESC.

Exemple

• Affichage de tous les auteurs et titre dans la relation LIVRES par ordre croissant de l’attribut auteur

SELECT  titre,  auteur 
FROM  livres 
ORDER  BY  auteur;

renvoie l’affichage :

Fondation,  Asimov
Farenheit  451,  Bradbury
Chroniques  martiennes,  Bradbury
Dune,  Herbert
1984,  Orwell

• Affichage de tous les auteurs et titre dans la relation LIVRES par ordre décroissant de l’attribut ann_publi

SELECT  DISTINCT  auteur,  titre 
FROM  livres
ORDER  BY ann_publi  DESC;

renvoie l’affichage :

Dune,  Herbert
Chroniques  martiennes,  Bradbury
Fondation,  Asimov
Farenheit  451,  Bradbury
1984,  Orwell

Activité 2 - Relation Communes

On considère la relation COMMUNES dont une partie du contenu est représenté ci-dessous :

1. Quelle requête permet d’afficher tous les noms de commune?
2. Quelle requête permet d’afficher tous les noms de commune et leur population?
3. Quelle requête permet d’afficher tous les noms de commune et leur population, classé par ordre croissant de population?
4. Quelle requête permet d’afficher tous les noms de commune et leur population, classé par ordre décroissant d’élus municipaux?

Réponse

1.

SELECT Nom_ville
FROM communes;

2.

SELECT Nom_ville,Population
FROM communes;

3.

SELECT Nom_ville,Population
FROM communes  
ORDER BY Population;

4.

SELECT Nom_ville,Population
FROM communes  
ORDER BY Nb_elus_municipaux DESC;

Requête avec une ou plusieurs restrictions

Une restriction est une sélection de lignes d’une relation, sur la base d’une condition à respecter, définie à la suite du terme WHERE . Cette condition peut être une combinaison de comparaisons à l’aide de AND , de OR et de NOT (attention donc aux parenthèses dans ce cas).

Remarque

Nous disposons de tous les opérateurs classiques de comparaison : \(=, <>, >, >=, <, <=\).

Exemple

Reprenons la relation définie dans l’exemple sur la relation LIVRES.

• Affichage de tous les auteurs et titres dont le titre du livre est « 1984 »

SELECT  auteur,  titre 
FROM  livres 
WHERE  titre  =  '1984';

renvoie l’affichage :

Orwell,  1984

Affichage de tous les auteurs et titres dont le titre du livre n’est pas « 1984 »

SELECT  auteur,  titre 
FROM  livres 
WHERE  titre  <>  '1984';

renvoie l’affichage :

Herbert,  Dune
Asimov,  Fondation
Bradbury,  Farenheit  452
Bradbury,  Chroniques  martiennes

• Affichage de tous les auteurs et titres dont l’année de publication est inférieure ou égale à 1950

SELECT  auteur,  titre 
FROM  livres 
WHERE  ann_publi  <=  1950;

renvoie l’affichage :

Orwell,  1984
Bradbury,  Chroniques  martiennes

Remarque

Pour les comparaisons de chaînes de caractères, il est important de faire attention à la casse. Par définition, un "a" est donc différent d’un "A". Pour remédier à ce problème, il existe les fonction UPPER() et LOWER() pour transformer une chaîne en respectivement majuscule et minuscule.

SELECT  auteur,  titre 
FROM  livres 
WHERE  UPPER(titre)  =  "DUNE";

Activité 3 - Recherche conditionnelle de communes #1

On reprend la relation COMMUNES précédente.

1. Quelle requête permet d’afficher le nom des communes dont le nombre d’élus municipaux est strictement inférieur à 10?
2. Quelle requête permet d’afficher le nom des communes dont le nombre d’élus municipaux est strictement inférieur à 10 ET dont la population est inférieure ou égale à 100?
3. Quelle requête permet d’afficher le nombre d’élus municipaux et la population de la ville de « Cressin-Rochefort »? (NB : affranchissez vous de la casse)

Réponse

1.

SELECT Nom_ville
FROM communes 
WHERE Nb_elus_municipaux <10;

2.

SELECT Nom_ville
FROM communes 
WHERE Nb_elus_municipaux <10 AND Population<=100;

3.

SELECT Nb_elus_municipaux, Population
FROM communes 
WHERE LOWER(Nom_ville) = « cressin-rochefort »;

Opérateur IS NULL

Une donnée manquante en SQL est repérée par un NULL. Il y a plusieurs raisons, bonnes ou mauvaises, pour avoir des données manquantes, et il est parfois utile de tester leur présence. Pour cela, nous allons utiliser le terme IS NULL comme condition.

Au contraire, si l’on veut uniquement les employés pour lesquels l’information est présente, nous devrons utiliser la négation avec IS NOT NULL.

Exemple

Affichage de tous les auteurs et titres dont l’année de publication est connue

SELECT  auteur,  titre 
FROM  livres 
WHERE  ann_publi  IS  NOT  NULL;

renvoie l’affichage :

Orwell,  1984
Herbert,  Dune
Asimov,  Fondation
Bradbury,  Farenheit  451
Bradbury,  Chroniques  martiennes

Activité 4 - Recherche conditionnelle de communes #2

On reprend la relation COMMUNES précédente.

1. Quelle requête permet d’afficher le nom des communes dans lesquelles il y a eu un deuxième tour?
2. Quelle requête permet d’afficher le nom des communes dont la population est supérieure à 5 000 habitants et dans lesquelles il y a eu un deuxième tour?

Réponse

1.

SELECT Nom_villes
FROM communes 
WHERE exprime_deuxieme_tour IS NOT NULL ;

2.

SELECT Nom_villes
FROM communes 
WHERE Population > 5000 AND  exprime_deuxieme_tour IS NOT NULL;

Opérateur LIKE

L’opérateur LIKE permet de rechercher les valeurs contenant une partie seulement de la chaîne de caractères. Le caractère \(\%\) représente une suite de caractères, éventuellement nulle.

Exemple

Affichage de tous les auteurs et titres dont le titre contient le mot « Chronique » (remarquez l’absence du s final)

SELECT  auteur,  titre 
FROM  livres 
WHERE  titre  LIKE  "%Chronique%";

renvoie l’affichage :

Bradbury,  Chroniques  martiennes

Activité 5 - Recherche de chaînes de caractères

On reprend la relation COMMUNES précédente.

1. Quelle requête permet d’afficher le nom des communes dont le nom commence par Roncherolles?
2. Quelle requête permet d’afficher le nom des communes dont le nom contient le mot « sur »?

Réponse

1.

SELECT Nom_villes
FROM communes 
WHERE Nom_villes LIKE "Roncherolles %";

2.

SELECT Nom_villes
FROM communes 
WHERE Nom_villes LIKE "%sur%";

Fonctions d'agrégation

La fonction d’agrégation COUNT() permet de compter le nombre d’enregistrement dans une table.

Exemples

Reprenons la relation définie dans le premier exemple.

• On compte tous les enregistrements présents dans la relation LIVRES.

SELECT  COUNT(*) 
FROM  livres;

renvoie l’affichage :

5

• On compte tous les enregistrements qui ont l’attribut ann_publi renseigné

SELECT  COUNT(ann_publi) 
FROM  livres;

renvoie l’affichage :

5

• On compte le nombre d’auteurs différents enregistrés

SELECT  COUNT(DISTINCT  auteur) 
FROM  livres;

renvoie l’affichage :

4

• On compte le nombre d’enregistrements pour lesquels l’année de publication est inférerieure ou égale à 1950

SELECT  COUNT(*) 
FROM  livres 
WHERE  ann_publi  <=  1950;

renvoie l’affichage :

2

Définition

• La fonction SUM(attribut) permet donc de faire la somme des valeurs non nulles de l’attribut passé en paramètre.
• La fonction AVG(attribut) permet de faire la moyenne des valeurs de l’attribut passé en paramètre.
• La fonction MEDIAN(attribut) permet de faire la médiane des valeurs de l’attribut passé en paramètre.
• La fonction MIN(attribut) permet d’afficher la valeur minimale de l’attribut passé en paramètre.
• La fonction MAX(attribut) permet d’afficher la valeur maximale de l’attribut passé en paramètre.

Exemple

Reprenons la relation définie dans le premier exemple.

On affiche l’année de publication la plus petite :

SELECT  MIN(ann_publi) 
FROM  livres;

renvoie l’affichage :

1949

Activité 6 - Agrégations de communes

On reprend la relation COMMUNES précédente.

1. Quelle requête permet d’afficher le nombre total d’élus de toutes les communes?
2. Quelle requête permet d’afficher la moyenne du nombre d’habitants dans les communes?
3. Quelle requête permet d’afficher le nom de la ville avec la population la plus grande pour laquelle il n’y a pas de deuxième tour?

Réponse

1.

SELECT SUM(Nb_elus_municipaux)
FROM communes;

2.

SELECT AVG(Population)
FROM communes;

3.

SELECT Nom_villes, Max(Population)
FROM communes 
WHERE  exprime_deuxieme_tour IS NULL;

Requêtes avec jointure

On a vu dans la partie consacrée au modèle relationnel, qu’une clé étrangère référence une clé primaire venant d’une autre table, suivant l’exemple ci-dessous :

Pour représenter le lien entre la clé étrangère et la clé primaire, on réalise une jointure .

Une jointure est l’opération consistant à rapprocher selon une condition les clé primaire et clé étrangère de deux relations.

La syntaxe est :

relation1
JOIN  relation2  ON  relation1.attribut1  = relation2.attribut2;

La jointure se positionne après les clauses SELECT et FROM

SELECT  attribut 
FROM  relation1 
JOIN  relation2  ON  realtion1.attribut1  =  realtion2.attribut2;

Exemple

Suivant le schéma ci-dessus,on écrirait:

SELECT livres
FROM Livres
JOIN  Auteurs  ON  livres.idAuteur  =  auteurs.id;

Activité 7 - Recherche de livres

On considère les deux relations représentés ci-dessous :

1. Quelle requête permet d’afficher l’auteur du livre dont le titre est « 1984 »?
2. Quelle requête permet d’afficher tous les auteurs qui ont publié un livre avant 1960 (strictement)?
3. Quelle requête permet d’afficher tous les titres des livres publiés par Pierre Boulle?
4. Quelle requête permet d’afficher l’auteur et le titre de tous les livres publiés avant 1960 (strictement)?

Réponse

1.

SELECT nom
FROM livres
JOIN auteurs ON livres.idAuteur = auteurs.id 
WHERE titre = "1984";

2.

SELECT nom
FROM livres
JOIN auteurs ON livres.idAuteur = auteurs.id 
WHERE ann_publi <1960;

3.

SELECT titre
FROM livres
JOIN auteurs ON livres.idAuteur = auteurs.id 
WHERE UPPER(nom)= boulle;

4.

SELECT nom,titre
FROM livres
JOIN auteurs ON livres.idAuteur = auteurs.id 
WHERE ann_publi < 1960;

Requêtes de manipulation de données

Insertion de données

L’instruction de base pour l’insertion de données dans une base est constituée du mot-clé INSERT INTO suivi du mot-clé VALUES .

• INSERT INTO permet de sélectionner la relation dans laquelle on insère les données.
• VALUES indique les valeurs qui doivent être insérées. Elles sont indiquées entre deux parenthèses.

La syntaxe est :

INSERT  INTO  relation 
VALUES  (attribut1  =  valeur1,  attribut2  =  valeur2) 
WHERE  condition;

Exemple

INSERT  INTO  Livres VALUES  (4,  "Les  Furtifs",  "Damasio",  2019);

• On insère des valeurs dans la relation Livres ;
• l’attribut id prend la valeur 4 ;
• l’attribut titre prend la valeur Les Furtifs ;
• l’attribut auteur prend la valeur Damasio ;
• l’attribut ann_publi prend la valeur 2019 .

Activité 8 - Ajout de livres

Au vu du diagramme relationnel suivant, indiquez les deux requêtes pour insérer le livre d’Alain Damasio intitulé "La Horde du Contrevent", paru en 2004 et publié en langue française.

Réponse

INSERT INTO auteurs VALUES (9, "Damasio", "Alain", français);
INSERT  INTO  livres VALUES  (15,  "La Horde du Contrevent",  9,  2004);

ATTENTION : l’ordre des requêtes est important !!!

Suppression de données

L’instruction de base pour l’insertion de données dans une base est constituée du mot-clé DELETE FROM.

La syntaxe est :

DELETE  FROM  relation WHERE  condition

Activité 9 - Suppression de livres

1. Indiquez la requête permettant de supprimer le livre "Fondation" de la relation Livres .
2. Indiquez la requête permettant de supprimer tous les livres d’Alain Damasio de la relation Livres .
3. Indiquez la requête permettant de supprimer tous les livres écrits avant 1945 de la relation Livres .

Réponse

1.

DELETE FROM livres
WHERE titre= "Fondation";

2.

DELETE FROM livres
WHERE idAuteur = 9;

3.

DELETE FROM livres
WHERE annPubli ≤ 1945;

Modification de données

L’instruction de base pour la modification de données dans une base est constituée du mot-clé UPDATE , suivi du mot-clé SET .

• UPDATE permet de sélectionner la relation dans laquelle on insère les données.
• SET indique les attributs qui doivent être modifiés, et les valeurs correspondantes.

La syntaxe est :

UPDATE  relation
SET  attribut  =  valeur 
WHERE  condition;

Activité 10 - Renommer des livres

1. Indiquez la requête permettant de renommer le livre "Fondation" en "Fondation - Tome 1" dans la relation Livres .
2. Le bibliothécaire de Gonneville-la-Mallet a inscrit par erreur que le livre d’Alain Damasio "La Zone du Dehors" a été publié en 2007, or sa première édition date de 1999. Indiquez la requête permettant de corriger cette erreur dans la relation Livres .

Réponse

1.

UPDATE livres SET titre = "Fondation – Tome 1"
WHERE titre = "Fondation";

2.

UPDATE livres SET annPubli = 1999
WHERE titre = "La Zone du Dehors";

Activité 11 - Festivals

Soit la base de données d’un festival de musique : dans une représentation peut participer un ou plusieurs musiciens. Un musicien ne peut participer qu’à une seule représentation.

• Representation (numRep , titreRep , lieu)
• Musicien (numMus , nom , #numRep)
• Programmer (Date , #numRep, tarif)

Indiquez les requêtes qui permettent d’obtenir :

1. La liste des titres des représentations.
2. La liste des titres des représentations ayant lieu au "théâtre allissa".
3. La liste des noms des musiciens et des titres des représentations auxquelles ils participent.
4. La liste des titres des représentations, les lieux et les tarifs du 25/07/2008.
5. Le nombre des musiciens qui participent à la représentations n°20.
6. Les représentations et leurs dates dont le tarif ne dépasse pas 20 euros.

Réponse

1.

SELECT titreRep
FROM representation; 

2.

SELECT titreRep
FROM representation  
WHERE lieu = "théâtre allissa";

3.

SELECT nom, titreRep
FROM representation
JOIN musicien ON representation.numRep = musicien.numRep;

4.

SELECT titreRep, lieu, tarif
FROM representation
JOIN programmer ON representation.numRep = programmer.numRep 
WHERE date= "25/07/2008";

5.

SELECT COUNT(*)
FROM musicien where numRep = 20 ;

6.

SELECT titreRep, date
FROM representation
JOIN programmer ON  representation.numRep = programmer.numRep 
WHERE tarif ≤ 20;

Activité 12 - Films

Soit le modèle relationnel suivant relatif à la gestion des locations de films :

• Clients (codecli, prenomcli, nomcli, ruecli, cpcli, villecli)
• Films (codefilm, nomfilm)
• Locations (# codecli, # codefilm, datedebut, duree)

Indiquez les requêtes qui permettent d’obtenir :

1. L’insertion du film "The Raid" avec le code numéro 12.
2. L’insertion de client numéro 124 qui s’appelle Jean Talu (les autres informations sur ce client ne sont pas connues).
3. La suppression du film "Dans la peau de John Malkovitch".
4. Le renommage du film "Boulevard de la Mort" en "Death Proof".

Réponse

1.

INSERT  INTO  films 
VALUES  (12, "The Raid");

2.

INSERT  INTO  clients 
VALUES  (124, "Jean", « Talu »,NULL,NULL,NULL);

3.

DELETE FROM films
WHERE nomfilm = "Dans la peau de John Malkovitch";

4.

UPDATE films SET nomfilm= « Death Proof »
WHERE nomfilm = "Boulevard de la Mort";

Activité 13 - Hôtels

Soit la Base de données hôtel qui contient 3 relations :

• Chambre (numChambre, prix, nbrLit, nbrPers, confort, equipement)
• Client (numClient, nom, prenom, adresse)
• Reservation (# numClient, # numChambre, dateArr, dateDep)

Le contenu de chaque relation est :

Indiquez les requêtes qui permettent d’obtenir :

1. Les numéros de chambres avec TV.
2. Les numéros de chambres et leurs capacités.
3. La capacité théorique d’accueil de l’hôtel.
4. Le prix par personne des chambres avec TV.
5. Les numéros des chambres et le numéro des clients ayant réservé des chambres pour le 09/02/2004.
6. Les numéros des chambres coûtant au maximum 80 Euro, ou ayant un bain et valant au maximum 120 Euro.
7. Les Nom, Prénoms et adresses des clients dans le noms commencent par « D ».
8. Le nombre de chambres dont le prix est entre 85 et 120 Euro.
9. Les noms des clients n’ayant pas fixé la date de départ.

Réponse

1.

SELECT numChambre
FROM chambre 
WHERE equipement = "TV";

2.

SELECT numChambre, nbrPers
FROM chambre;

3.

SELECT SUM(nbrPers)
FROM chambre;

4.

SELECT prix/nbrpers
FROM chambre 
WHERE equipement = "TV";

5.

SELECT numChambre, numClient
FROM reservation 
WHERE dateArr = "09/02/2004";

6.

SELECT numChambre
FROM chambre 
WHERE prix ≤ 80 OR (confort = "Bain" AND prix ≤ 120);

7.

SELECT nom, prenom, adresse
FROM client 
WHERE nom = "D%";

8.

SELECT COUNT(*)
FROM chambre 
WHERE 85 ≤ prix ≤ 120;

9.

SELECT nom
FROM client
JOIN reservation ON client.numcClient = reservation.numClient 
WHERE dateDep IS NULL;