Chapitre 6 : Le langage SQL (Structured Query Language)

Dr. Salim Kebir

Maître de conférences en informatique

1 / 27
Processus de modélisation des données
 Base de données

Modélisation physique SQL

modélisation
Niveaux de

Algèbre relationnel
Modélisation logique
Modèle relationnelle
Modélisation conceptuelle Modèle Entité-Association

♂ Concepteur

, Réalité / Discours / Interview

2 / 27
SQL

SQL (Structured Query Language)

▶ Le langage de facto pour l’interrogation des bases de données
▶ Un langage spécifique (̸= générique)
▶ et déclaratif (̸= impératif)
▶ Deux principaux sous-langages :
▶ Langage de définition de données : création et suppression de tables
▶ Langage de manipulation de données : insertion, suppression, mise à jour et
recherche de données

3 / 27
Exemple/outils pour la suite du cours

Base de données [Link]

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

Capital référence [Link]
• city(ID, Name, Population, #CountryCode)
CountryCode référence [Link]
• countrylanguage(#CountryCode, Language)
CountryCode référence [Link]

Télécharger : [Link]

4 / 27
Exemple/outils pour la suite du cours
Outil utilisé
▶ DB Browser for SQLite : [Link]

5 / 27
Création d’une table

Syntaxe de l’instruction CREATE TABLE :

1 CREATE TABLE Nom de la table (

2 Colonne1 Type1 Contraintes ,
3 Colonne2 Type2 Contraintes ,
4 .
5 .
6 .
7 Colonnen Typen Contraintes ,
8 Éventuelles containtes multicolonnes
9 );

6 / 27
Types de données

Types Signification Exemples de valeurs

INTEGER Entiers relatifs 1 -45 10 0
REAL Nombres réels 1.25 -5.58 0.4
TEXT Chaînes de caractères ’Leon’ ’Oran’ ’Messaoud’

7 / 27
Contraintes d’intégrité

Une contrainte d’intégrité est un mécanisme qui s’assure que les valeurs d’une
colonne donnée soient toujours cohérentes.

8 / 27
Contraintes d’intégrité

Une contrainte d’intégrité est un mécanisme qui s’assure que les valeurs d’une
colonne donnée soient toujours cohérentes.
▶ PRIMARY KEY : Déclare la colonne comme étant la clé primaire de la table

8 / 27
Contraintes d’intégrité

Une contrainte d’intégrité est un mécanisme qui s’assure que les valeurs d’une
colonne donnée soient toujours cohérentes.
▶ PRIMARY KEY : Déclare la colonne comme étant la clé primaire de la table
▶ Utiliser PRIMARY KEY (Attr1, Attr2, ...) avant la parenthèse fermante de
CREATE TABLE s’il s’agit d’une contrainte d’intégrité multicolonne

8 / 27
Contraintes d’intégrité

Une contrainte d’intégrité est un mécanisme qui s’assure que les valeurs d’une
colonne donnée soient toujours cohérentes.
▶ PRIMARY KEY : Déclare la colonne comme étant la clé primaire de la table
▶ Utiliser PRIMARY KEY (Attr1, Attr2, ...) avant la parenthèse fermante de
CREATE TABLE s’il s’agit d’une contrainte d’intégrité multicolonne
▶ REFERENCES Tab(Col) : Déclare la colonne comme clé étrangère qui référence la
colonne Col de la table Tab

8 / 27
Contraintes d’intégrité

Une contrainte d’intégrité est un mécanisme qui s’assure que les valeurs d’une
colonne donnée soient toujours cohérentes.
▶ PRIMARY KEY : Déclare la colonne comme étant la clé primaire de la table
▶ Utiliser PRIMARY KEY (Attr1, Attr2, ...) avant la parenthèse fermante de
CREATE TABLE s’il s’agit d’une contrainte d’intégrité multicolonne
▶ REFERENCES Tab(Col) : Déclare la colonne comme clé étrangère qui référence la
colonne Col de la table Tab
▶ NOT NULL : S’assure que la colonne ne contienne pas de valeurs NULL

8 / 27
Contraintes d’intégrité

Une contrainte d’intégrité est un mécanisme qui s’assure que les valeurs d’une
colonne donnée soient toujours cohérentes.
▶ PRIMARY KEY : Déclare la colonne comme étant la clé primaire de la table
▶ Utiliser PRIMARY KEY (Attr1, Attr2, ...) avant la parenthèse fermante de
CREATE TABLE s’il s’agit d’une contrainte d’intégrité multicolonne
▶ REFERENCES Tab(Col) : Déclare la colonne comme clé étrangère qui référence la
colonne Col de la table Tab
▶ NOT NULL : S’assure que la colonne ne contienne pas de valeurs NULL
▶ CHECK(C) : S’assure que toutes les valeurs de la colonne satisfont la condition C

8 / 27
Contraintes d’intégrité

Une contrainte d’intégrité est un mécanisme qui s’assure que les valeurs d’une
colonne donnée soient toujours cohérentes.
▶ PRIMARY KEY : Déclare la colonne comme étant la clé primaire de la table
▶ Utiliser PRIMARY KEY (Attr1, Attr2, ...) avant la parenthèse fermante de
CREATE TABLE s’il s’agit d’une contrainte d’intégrité multicolonne
▶ REFERENCES Tab(Col) : Déclare la colonne comme clé étrangère qui référence la
colonne Col de la table Tab
▶ NOT NULL : S’assure que la colonne ne contienne pas de valeurs NULL
▶ CHECK(C) : S’assure que toutes les valeurs de la colonne satisfont la condition C
▶ Utiliser CHECK(C) avant la parenthèse fermante de CREATE TABLE s’il s’agit d’une
contrainte d’intégrité multicolonne

8 / 27
Exemple de création de la table country

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

Capital référence [Link]

1 CREATE TABLE country (

2 Code TEXT PRIMARY KEY ,
3 Name TEXT NOT NULL ,
4 Continent TEXT NOT NULL ,
5 SurfaceArea REAL NOT NULL CHECK ( SurfaceArea > 0) ,
6 Population INTEGER NOT NULL CHECK ( Population > 0) ,
7 HeadOfState TEXT NOT NULL ,
8 Capital INTEGER NOT NULL REFERENCES city ( ID )
9 );

9 / 27
Exemple de création de la table city

• city(ID, Name, Population, #CountryCode)

CountryCode référence [Link]

1 CREATE TABLE city (

2 ID INTEGER PRIMARY KEY ,
3 Name TEXT NOT NULL ,
4 Population INTEGER NOT NULL CHECK ( Population > 0) ,
5 CountryCode TEXT NOT NULL REFERENCES country ( Code )
6 );

10 / 27
Exemple de création de la table countrylanguage

• countrylanguage(#CountryCode, Language)
CountryCode référence [Link]

1 CREATE TABLE countrylanguage (

2 CountryCode TEXT NOT NULL REFERENCES country ( Code ) ,
3 Language TEXT NOT NULL ,
4 PRIMARY KEY ( CountryCode , Language )
5 );

11 / 27
Suppression d’une table

Syntaxe de l’instruction DROP TABLE :

1 DROP TABLE Nom de la table ;

Exemples :
1 DROP TABLE countrylanguage ;
2 DROP TABLE country ;
3 DROP TABLE city ;

12 / 27
Insertion d’un tuple dans une table
Syntaxe de l’instruction INSERT INTO :

1 INSERT INTO Nom de la table

2 VALUES (valeur1 , valeur2 , . . .) ;

Exemples :
1 INSERT INTO city
2 VALUES (35 , ’ Alger ’ , ’ DZA ’ , 2168000);
3
4 INSERT INTO country
5 VALUES ( ’ JPN ’ , ’ Japan ’ , ’ Asia ’ , ’ 377829.0 ’ , ’ 126714000 ’ , ’ Akihito ’ , 1532);
6
7 INSERT INTO countrylanguage
8 VALUES ( ’ RUS ’ , ’ Russian ’ );

13 / 27
Suppression d’un ou plusieurs tuples d’une table

Syntaxe de l’instruction DELETE FROM :

1 DELETE FROM Nom de la table

2 WHERE Condition ;

Fonctionnement : supprime tous les tuples de la table pour qui la condition du

WHERE est vraie

Exemple : Supprimer tous les pays d’afrique

1 DELETE FROM country
2 WHERE Continent = ’ Africa ’;

14 / 27
Mise à jour d’un ou plusieurs tuples d’une table
Syntaxe de l’instruction UPDATE :

1 UPDATE Nom de la table

2 SET Colonne = Valeur
3 WHERE Condition ;

Fonctionnement : effectue la mise à jour qui suit le mot-clé SET sur tous les tuples
de la table pour qui la condition du WHERE est vraie

Exemple : Donald Trump est le nouveau président des USA

1 UPDATE country
2 SET HeadOfState = ’ Donald ␣ Trump ’
3 WHERE Code = ’ USA ’;

15 / 27
Recherche de données
Syntaxe de l’instruction SELECT :

1 SELECT Colonne1 , Colonne2 , ...

2 FROM Table1 , Table2 , ...
3 WHERE Condition ;

Fonctionnement : retourne πColonne1 ,Colonne2 ,... (σCondition (Table1 × Table2 × ...))

▶ la projection (π) sur les colonnes qui suivent le mot-clé SELECT
▶ du produit cartésien (×) des tables qui suivent le mot-clé FROM
▶ et restreint (σ) le résultats aux tuples qui satisfont la condition qui suit le
mot-clé WHERE
Remarques :
▶ Le symbole * peut être utilisé après SELECT pour projeter sur toutes les colonnes
▶ Les opérateurs logique AND et OR peuvent être utilisés pour construire la condition
16 / 27
Exemples de recherche de données

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Donner le nom de chaque pays et le nom de son chef d’état

17 / 27
Exemples de recherche de données

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Donner le nom de chaque pays et le nom de son chef d’état

1 SELECT Name , HeadOfState
2 FROM country

17 / 27
Exemples de recherche de données

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Donner le nom des pays dont la superficie dépasse 1000000m2

18 / 27
Exemples de recherche de données

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Donner le nom des pays dont la superficie dépasse 1000000m2

1 SELECT Name
2 FROM country
3 WHERE SurfaceArea >1000000

18 / 27
Exemples de recherche de données

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Donner le nom de tous les pays d’Europe

19 / 27
Exemples de recherche de données

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Donner le nom de tous les pays d’Europe

1 SELECT Name
2 FROM country
3 WHERE Continent = ’ Europe ’

19 / 27
Exemples de recherche de données

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Donner le nom et la superficie des pays africains dont la superficie est inférieur
à 500000m2

20 / 27
Exemples de recherche de données

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Donner le nom et la superficie des pays africains dont la superficie est inférieur
à 500000m2
1 SELECT Name , SurfaceArea
2 FROM country
3 WHERE Continent = ’ Africa ’
4 AND SurfaceArea < 500000

20 / 27
Exemples de recherche de données

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Qui est le chef d’état du Zimbabwe ?

21 / 27
Exemples de recherche de données

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Qui est le chef d’état du Zimbabwe ?

1 SELECT HeadOfState
2 FROM country
3 WHERE Name = ’ Zimbabwe ’

21 / 27
Exemples de recherche de données

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Quelles sont les langues parlées en Suisse sachant que le code de ce pays est
CHE ?

22 / 27
Exemples de recherche de données

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Quelles sont les langues parlées en Suisse sachant que le code de ce pays est
CHE ?
1 SELECT Language
2 FROM countrylanguage
3 WHERE CountryCode = ’ CHE ’

22 / 27
Exemples de recherche de données

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Donner toutes les informations sur les villes japonaises sachant que le code de
ce pays est JPN

23 / 27
Exemples de recherche de données

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Donner toutes les informations sur les villes japonaises sachant que le code de
ce pays est JPN
1 SELECT *
2 FROM city
3 WHERE CountryCode = ’ JPN ’

23 / 27
Opérations ensemblistes
SQL offre les trois principales opérations ensemblistes :
▶ L’union ∪ (UNION)
▶ L’intersection ∩ (INTERSECT)
▶ La différence − (EXCEPT)
Syntaxe :
1 SELECT Colonne1 , Colonne2 , ...
2 FROM Table1 , Table2 , ...
3 WHERE Condition ;
4 O P E R A TI ON_E NSEM B L I S T E
5 SELECT Colonne1 , Colonne2 , ...
6 FROM Table1 , Table2 , ...
7 WHERE Condition ;
Remarque : comme en algèbre relationnelle, les deux opérandes doivent avoir les
mêmes colonnes
24 / 27
Exemples d’utilisation d’opérations ensemblistes

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Donner le nom et le continent des pays d’afrique et d’asie

25 / 27
Exemples d’utilisation d’opérations ensemblistes

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Donner le nom et le continent des pays d’afrique et d’asie

1 SELECT Name , Continent
2 FROM country
3 WHERE Continent = ’ Africa ’
4 UNION
5 SELECT Name , Continent
6 FROM country
7 WHERE Continent = ’ Asia ’

25 / 27
Exemples d’utilisation d’opérations ensemblistes

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Quelles sont les villes du royaume-uni (code GBR) qui ont des homonymes aux
états-unis (code USA) ?

26 / 27
Exemples d’utilisation d’opérations ensemblistes

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Quelles sont les villes du royaume-uni (code GBR) qui ont des homonymes aux
états-unis (code USA) ?
1 SELECT NAME
2 FROM city
3 WHERE CountryCode = ’ GBR ’
4 INTERSECT
5 SELECT NAME
6 FROM city
7 WHERE CountryCode = ’ USA ’

26 / 27
Exemples d’utilisation d’opérations ensemblistes

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Donner le code des pays qui parlent uniquement l’espagnol

27 / 27
Exemples d’utilisation d’opérations ensemblistes

• country(Code, Name, Continent, SurfaceArea, Population, HeadOfState, #Capital)

• city(ID, Name, Population, #CountryCode)
• countrylanguage(#CountryCode, Language)

Donner le code des pays qui parlent uniquement l’espagnol

1 SELECT CountryCode
2 FROM countrylanguage
3 WHERE Language = ’ Spanish ’
4 EXCEPT
5 SELECT CountryCode
6 FROM countrylanguage
7 WHERE Language != ’ Spanish ’

27 / 27