EHTP - 2015 07/05/2018

Le langage SQL
Structured Query Language

Semestre 2 – 2017/2018
B. EL HATIMI

SQL – Structured Query language

 Le langage SQL est un langage de définition
et de manipulation des bases de données.
 En tant que langage de manipulation des
données, sa syntaxe relève des langages
prédicatifs (comme le calcul de tupples et le
calcul de domaines).
 Dans son implémentation, le langage SQL est
basé sur l’algèbre relationnelle (opérateurs
algébriques).
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 1

EHTP - 2015 07/05/2018

Deux langages en un
 SQL est :
 Un langage de description de
données LDD (Data Definition
Language)
 Un langage de manipulation de

données LMD (Data Manipulation

Language)

[Link] HATIMI - SQL

Un peu d’histoire …
 1974 : IBM lance une réflexion autour
d’un langage de données relationnel
 1982 : IBM82 Première version SQL
commercialisée
 1989 : SQL1 ou SQL-89 (norme ISO89)
 1992 : SQL2 ou SQL-92 (norme ISO92)
 SQL3 (évolution vers l’objet)

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 2

EHTP - 2015 07/05/2018

SQL : Les versions

 SQL1 [ISO89] : 110 pages.
 SQL2 [ISO92]: 580 pages.
 SQL3 : >2000 pages.

[Link] HATIMI - SQL

Pourquoi SQL ?
 SQL est la seule norme (norme ANSI et
ISO) existante pour les LDD/LMD
relationnels.
 Tous les SGBDR (soit > 90% du marché
des bases de données) utilisent une version
de SQL comme LMD/LDD.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 3

EHTP - 2015 07/05/2018

Compatibilité des SGDBR avec la

norme SQL
 Chaque éditeur de SGBDR a sa propre version de la
syntaxe SQL  Parfois des incompatibilités entre eux.
 Dans une optique d’interopérabilité il faut vérifier
systématiquement la compatibilité de la syntaxe de
votre script avec la norme SQL.
 Exemples à partir de la documentation PostgreSQL :
1. There is no REINDEX command in the SQL standard.
2. This CREATE TYPE command is a PostgreSQL extension. There is a
CREATE TYPE statement in the SQL standard that is rather different
in detail.
3. The CREATE TABLE command conforms to the SQL standard, with
exceptions listed below.
4. The command CREATE DOMAIN conforms to the SQL standard.

[Link] HATIMI - SQL

Langage SQL

Partie 1 : SQL – Langage de

description de données

B. EL HATIMI - Langage SQL 4

EHTP - 2015 07/05/2018

Cycle de vie d’une base de

données Modèle entité
association

Cahier
Schéma
Réalité Analyse de Conception conceptuel
charges
Modèle
relationnel
SQL LMD Choix du SGDBR
: Oracle, MySQL
SQL LDD

Schéma
Schéma
BD SGBD relationnel
physique
De la BD
[Link] HATIMI - SQL

SQL - LDD
 Définition : « Un langage de description
de données est un langage supportant
un modèle et permettant de décrire les
données d’une base d’une manière
assimilable par une machine »
 But : Définir le schéma de la base de
données.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 5

EHTP - 2015 07/05/2018

Création du schéma
 SQL permet de créer une base de données
composées de plusieurs schémas.
 Mot clé : CREATE
 Création de la base de données
create database BD1
 Création du schéma de la base de données
create schema GESTION_SCOL
[Link] HATIMI - SQL

Script généré par PostgreSQL à la

création d’une base de données
CREATE DATABASE "Candidats"
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'French_France.1252'
LC_CTYPE = 'French_France.1252'
CONNECTION LIMIT = -1;
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 6

EHTP - 2015 07/05/2018

Remarque
 La syntaxe SQL n’est pas sensible à la
casse (nom des tables, des attributs …).
Les valeurs des attributs par contre le
sont.

[Link] HATIMI - SQL

Démarche d’exécution d’une

requête SQL
1. Analyse syntaxique;
2. Optimisation de la requête;
3. Réécriture de la requête;
4. Générer un plan d’exécution (arbre avec
des opérateurs);
5. Exécution de la requête.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 7

EHTP - 2015 07/05/2018

Création d’une table

 Un schéma est composé de tables.
 Une table est créée comme suit :

create table ETUDIANT (CNEchar (10) primary key,

Nom varchar (30),
Prenom varchar (40),
DateNais date);
 Le SGBDR interprète ETUDIANT comme:
DB.GESTION_SCOL.ETUDIANT
 La référence compléte d’une table est sous la
forme : [Nom_BD].[Nom_Schéma].Nom_Table
[Link] HATIMI - SQL

Types de données
 Chaque SGBDR fournit un ensemble de
types de données plus ou moins riche.
 Les types de données ne sont pas
normalisées  Risque d’incompatibilités
entre SGBDR.
 Il faut vérifier le type de données par
rapport au SGBDR utilisé.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 8

EHTP - 2015 07/05/2018

Types de données
 SMALLINT
 INTEGER (ou INT)
 DECIMAL(p,q)
 FLOAT(p) (ou REAL)
 CHAR(p)
 VARCHAR(p)
 DATE
 BLOB (Binary large Object) : contenant générique
pouvant accueillir des chaînes de bits de grande
taille (images, séquences vidéo…) …
[Link] HATIMI - SQL

Domaine de données
 Le langage SQL offre la possibilité de définir de
nouveaux types de données.
 Mot clé domain
 Exemple :
create domain MONTANT as decimal (9,2)
 Le domaine est alors utilisé comme un type de
données standard :
create table ACHAT (Code char(5),
Valeur MONTANT)
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 9

EHTP - 2015 07/05/2018

Domaine - Exemple
 CREATE DOMAIN couleur AS text
CHECK (VALUE IN (‘Blanc’, ‘Rouge’,
‘Noir’));

 CREATE TABLE produit (id integer

primary key, colo couleur);

Exemple (Documentation
PostgreSQL)
 CREATE DOMAIN us_postal_code AS TEXT
CHECK ( VALUE ~ '^\d{5}$‘
OR VALUE ~ '^\d{5}-\d{4}$' );
Create domain code as text check (VALUE ~
‘char(1)\\int\\int\\int’)
 CREATE TABLE us_snail_addy ( address_id
SERIAL PRIMARY KEY, street1 TEXT NOT NULL,
street2 TEXT, street3 TEXT, city TEXT NOT
NULL, postal us_postal_code NOT NULL );
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 10

EHTP - 2015 07/05/2018

Exercice
CANDIDAT (Code_CNC clé primaire, Nom,
Prenom, Adresse, date_naissance)
Créer un domaine pour le code CNC
Format : XX000Y (XX : ville, 000 : numéro
d’ordre, Y : filière M, P, T, B)
Ex : SL028M (SL : Salé, 028 Ordre, M : MP)
Saisir des enregistrements pour tester

[Link] HATIMI - SQL

Clé primaire
 Mot clé : primary key
 create table ETUD

(CNE char (10) primary key,

Nom char (30),
Prenom char (40),
DateNais date
Moyenne decimal(2,2))

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 11

EHTP - 2015 07/05/2018

Clé primaire
Les deux syntaxes suivantes sont équivalentes :
create table ETUD1
(CNE char (10) primary key,
Moyenne decimal(2,2))

create table ETUD1

(CNE char (10),
Moyenne decimal(2,2),
primary key (CNE))

Dans la première la clé primaire est une contrainte associée à

la colonne et dans la deuxième elle est associée à la table.
[Link] HATIMI - SQL

Code généré par PostgreSQL

CREATE TABLE etud1
( cne character(10) NOT NULL,
moyenne numeric(2,2),
CONSTRAINT etud1_pkey PRIMARY KEY (cne))
WITH ( OIDS=FALSE );
ALTER TABLE etud1
OWNER TO postgres;

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 12

EHTP - 2015 07/05/2018

Clé primaire composée

 On peut créer une clé primaire composée de
plusieurs attributs.
 create table ETUD
(Nom char (30),
Prenom char (40),
DateNais date,
Moyenne decimal(2,2),
primary key (Nom,Prenom))

[Link] HATIMI - SQL

Contrainte d’unicité
 La contrainte d’unicité est précisée par le mot-clé
unique.

 create table ETUDIANT

(CNE char (10) primary key,
CIN char (8) unique,
Nom char (30),
Prenom char (40),
DateNais date)

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 13

EHTP - 2015 07/05/2018

Contrainte d’unicité
 La contrainte d’unicité peut s’appliquer sur un
ensemble d’attributs :

 create table ETUDIANT

(CNE char (10) primary key,
Nom char (30),
Prenom char (40),
DateNais date,
unique (Nom,Prenom))

[Link] HATIMI - SQL

Clé secondaire
 Le langage SQL ne permet pas de définir
formellement une clé secondaire.
 On peut donc utiliser UNIQUE pour
préciser les clés secondaires.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 14

EHTP - 2015 07/05/2018

Attribut facultatif / obligatoire

 Tout attribut est facultatif par défaut  accepte la
valeur NULL
 La valeur NULL est une valeur conventionnelle pour
représenter une information inconnue ou
inapplicable.
 Pour imposer le caractère obligatoire on ajoute la
clause not null lors de la création de l’attribut.
 La valeur NULL n’enfreint pas la contrainte d’unicité.

[Link] HATIMI - SQL

Attribut facultatif / obligatoire

 create table ETUDIANT
(CNE char (10) primary key,
CIN char (8) unique not null,
Nom char (30) not null,
Prenom char (40),
DateNais date)

 Remarque : En pratique, les SGBDR considèrent la

contrainte primary key comme équivalente à unique
et not null.
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 15

EHTP - 2015 07/05/2018

Valeur par défaut

 Mot Clé default
 A la création de la table on peut associer une
valeur par défaut à une colonne :
create table PRODUIT (… CAT char(1) default ‘P’ …)
 A la création du domaine on peut associer une
valeur par défaut à celui-ci :
create domain MONTANT decimal (9,2) default 0.0

[Link] HATIMI - SQL

Exercice : Table U
 Soit le schéma relationnel de la table u
décrit comme suit :
 u (nu : Entier, nomu : Chaine(100), ville
: Chaine(50))
 nu est la clé primaire
 nomu est obligatoire
 Ville a comme valeur par défaut
‘Casablanca’
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 16

EHTP - 2015 07/05/2018

Solution
CREATE TABLE u (nu integer primary key,
nomu character varying (100) not null,
Ville character varying(50) default
‘Casablanca’);

[Link] HATIMI - SQL

Exercice : Table F
 Soit le schéma relationnel de la table F
décrit comme suit :
 F (NF : Entier, NomF : Chaine(50), Ville
: Chaine(30))
 NF est la clé primaire
 NomF est obligatoire

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 17

EHTP - 2015 07/05/2018

Exercice : Table F
 CREATE TABLE F (NF integer
PRIMARY KEY, NomF Varchar (50)
NOT NULL, Ville Varchar (30));

[Link] HATIMI - SQL

Passage des contraintes du

schéma en SQL
 Que faire dans le cas des contraintes
comportementales ?

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 18

EHTP - 2015 07/05/2018

Contrainte générale : CHECK

 La clause CHECK permet de contraindre la valeur
d’une colonne à vérifier un prédicat. Le prédicat
correspond à une expression logique. Elle peut
aussi vérifier un prédicat sur la valeur de deux
colonnes.
 Exemple :
CREATE TABLE products
( product_no integer primary key,
name text,
price numeric CHECK (price > 0) );

[Link] HATIMI - SQL

Contrainte générale : CHECK

CREATE TABLE FILM
(titre VARCHAR (50) NOT NULL,
annee INTEGER CHECK (annee BETWEEN 1890 AND 2000)
NOT NULL,
genre VARCHAR (10) CHECK (genre IN
(’Histoire’,’Western’,’Drame’)),
idMES INTEGER,
codePays INTEGER,
PRIMARY KEY (titre),
FOREIGN KEY (idMES) REFERENCES Artiste,
FOREIGN KEY (codePays) REFERENCES Pays);

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 19

EHTP - 2015 07/05/2018

CHECK sur deux colonnes

 Dans cet exemple la clause CHECK vérifie pour
chaque enregistrement que la valeur de son
attribut price est supérieur à la valeur de son
attribut discounted_price
 CREATE TABLE products
( product_no integer primary key,
name text,
price numeric CHECK (price > 0),
discounted_price numeric CHECK
(discounted_price > 0),
CHECK (price > discounted_price) );
[Link] HATIMI - SQL

Exemple : Table P
 Table des produits P (NP : Entier, NomP
: Chaine(50), Couleur : Chaine(30),
Poids : Entier)
 NP est la clé primaire
 NomP est obligatoire
 Couleur peut prendre les valeurs
(‘Blanc’, ‘Rouge’, ‘Noir’)
 Poids > 0
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 20

EHTP - 2015 07/05/2018

Exemple : Table P
CREATE TABLE P
(NP integer PRIMARY KEY,
NomP Character Varying (50) NOT NULL,
Couleur Character Varying (30) CHECK
(Couleur IN (‘Blanc’, ‘Rouge’, ‘Noir’)),
Poids integer CHECK (Poids > 0));

[Link] HATIMI - SQL

Contrainte implicite Vs
Contrainte explicite
 Le code suivant crée une contrainte d’entité
implicite sur la colonne code
 CREATE TABLE films (code character(5) PRIMARY
KEY,…)
 On peut créer la contrainte de maniére explicite,
en lui associant un nom, avec le mot clé
CONSTRAINT. La syntaxe deviant :
 CREATE TABLE films (code character(5) NOT
NULL,…, CONSTRAINT films_pkey PRIMARY KEY
(code))
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 21

EHTP - 2015 07/05/2018

Pseudo-type SERIAL
 Sous PostgreSQL, le pseudo-type SERIAL permet de
créer une séquence puis de l’associer aux valeurs d’une
colonne.
 CREATE TABLE etudiant (id serial primary key,…);
 Il existe trois types SERIAL différents selon leur taille :
smallserial sur 2 octets / serial sur 4 / bigserial sur
8.
 Le pseudo-type SERIAL est équivalent au mot-clé
AUTO-INCREMENT de certains SGBD.

[Link] HATIMI - SQL

Clé étrangère
 Mots clés: FOREIGN KEY / REFERENCES
 Syntaxe avec contrainte de table :

create table ACHAT (

Code char(10) primary key,
Prod char(8) not null,
Quantité smallint,
foreign key (Prod) references PRODUIT )
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 22

EHTP - 2015 07/05/2018

Clé étrangère
 Mots clés: FOREIGN KEY / REFERENCES
 Syntaxe avec contrainte de colonne :

create table ACHAT (

Code char(10) primary key,
Prod char(8) not null references PRODUIT,
Quantité smallint)

[Link] HATIMI - SQL

Contrainte référentielle
 La clé étrangère précise une contrainte référentielle
sur un ou plusieurs attributs de la table vers un ou
plusieurs attributs d’une autre table qui sont soient
une clé primaire ou une clé secondaire.
 Syntaxe complète :

FOREIGN KEY ( column [, ... ] ) REFERENCES reftable [

( refcolumn [, ... ] ) ]
 Si refcolumn est omis, la contrainte référentielle
pointe sur la clé primaire.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 23

EHTP - 2015 07/05/2018

Clé étrangère composée

 Une clé étrangère peut être composée de
plusieurs colonnes :
create table TAB1 (
Att1 Type1 primary key,
Att2 Type2,
Att3 Type3,
foreign key (Att2,Att3) references TAB2
(Att4,Att5))
[Link] HATIMI - SQL

Exercice : Table PUF

 Table des achats : PUF (NP : Entier, NU :
Entier, NF : Entier, Quantité : Entier)
 (NU, NP, NF) Clé primaire
 NP référence [Link]
 NU référence [Link]
 NF référence [Link]

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 24

EHTP - 2015 07/05/2018

Exercice : Table PUF

 CREATE TABLE PUF (NP integer
references P, NU integer references
U, NF integer references F, Quantite
integer, PRIMARY KEY(NP, NU, NF));

[Link] HATIMI - SQL

CREATE TABLE puf

( np integer NOT NULL,
nu integer NOT NULL,
nf integer NOT NULL,
quantite integer,
CONSTRAINT puf_pkey PRIMARY KEY (np, nu, nf),
CONSTRAINT puf_nf_fkey FOREIGN KEY (nf)
REFERENCES f (nf) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT puf_np_fkey FOREIGN KEY (np)
REFERENCES p (np) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT puf_nu_fkey FOREIGN KEY (nu)
REFERENCES u (nu) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION);

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 25

EHTP - 2015 07/05/2018

Contrainte référentielle
 Que se passe t-il en cas de modification ou de
suppression des données en référence ?  Risque de
violation de la contrainte référentielle.
 Afin d’assurer l’intégrité de la base de données, le
langage SQL propose cinq actions possibles
 Syntaxe :

FOREIGN KEY ( column [, ... ] ) REFERENCES reftable [

( refcolumn [, ... ] ) ] [ ON DELETE action ] [ ON
UPDATE action ]

[Link] HATIMI - SQL

Contrainte référentielle
 NO ACTION : Génère une erreur indiquant la
violation de la contrainte référentielle. C’est
l’action par défaut.
 RESTRICT : Génère une erreur indiquant la
violation de la contrainte référentielle. La
modification ou la suppression de la donnée en
référence est interdite.
 La différence ? (voir documentation)
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 26

EHTP - 2015 07/05/2018

Contrainte référentielle
 CASCADE : En cas de suppression de la valeur
référencée, supprime les lignes contenant une
référence à celle-ci. En cas de modification de
la valeur référencée, modifie la valeur de la clé
étrangère dans les lignes contenant une
référence à la valeur modifiée.

[Link] HATIMI - SQL

Contrainte référentielle
 SET NULL : Remplace la valeur référencée par
NULL dans la clé étrangère.
 SET DEFAULT : Remplace la valeur référencée
par la valeur par défaut (si elle existe) dans la
clé étrangère. Il faut que la valeur par défaut
existe dans la table de référence.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 27

EHTP - 2015 07/05/2018

Contrainte référentielle
 Exemple :
create table ACHAT (
Code char(10) primary key,
Prod char(8) not null references PRODUIT on
update cascade on delete no action,
Quantité smallint)

[Link] HATIMI - SQL

Contrainte référentielle
1. Il est interdit de supprimer 1. ON DELETE NO ACTION
un produit s’il a été acheté.  Ne rien écrire
2. Même si on supprime le
produit on veut garder les 2. ON DELETE SET NULL /
achats correspondants. SET DEFAULT
3. Un produit acheté ne peut
pas être modifié. 3. ON UPDATE NO ACTION
4. On change souvent le code  Ne rien écrire
de nos produits, dans ce 4. ON UPDATE CASCADE
cas il faut changer le code
dans les achats 5. ON DELETE CASCADE
correspondants.
5. Si on supprime les produits
on ne veut pas garder les
achats.

B. EL HATIMI - Langage SQL 28

EHTP - 2015 07/05/2018

Exercice : Table PUF (Version 2)

 Table des achats : PUF (NP : Entier, NU : Entier, NF :
Entier, Quantité : Entier)
 (NU, NP, NF) Clé primaire
 NP référence [Link] / NU référence [Link] / NF
référence [Link]
 Si un produit est supprimé on veut supprimer les
achats.
 Si l’identifiant d’une usine est modifié alors on modifie
la clé étrangère dans les achats.
[Link] HATIMI - SQL

Exercice : Table PUF (Version 2)

 CREATE TABLE PUF (NP integer
references P ON DELETE CASCADE,
NU integer references U ON UPDATE
CASCADE, NF integer references F,
Quantite integer, PRIMARY KEY(NP,
NU, NF));

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 29

EHTP - 2015 07/05/2018

Contrainte référentielle
 Remarque : Certains SGBD acceptent la
création d’une clé étrangère vers une
table qui n’est pas encore créée
(référence en avant)
 Si ce n’est pas possible, il faudra ajouter
la contrainte après la création de la table
de référence (en utilisant ALTER TABLE).
[Link] HATIMI - SQL

Passage des contraintes du

schéma en SQL
CONTRAINTE EQUIVALENT SQL
RELATIONNELLE
Contrainte d’entité / de clé PRIMARY KEY
Contrainte d’unicité UNIQUE
Contrainte de non nullité NOT NULL
Contrainte référentielle FOREIGN KEY /
REFERENCES
Contrainte de domaine Nom_Colonne
Type_Domaine
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 30

EHTP - 2015 07/05/2018

Exercice 1
 Schéma de la BD Gestion_ACHAT :
1. CLIENT (NCLI, Nom, [Adresse], [Ville])
2. COMMANDE (NCOM, Client, DateCommande,
[DateLivraison])
3. PRODUIT (NPRO, Libelle, Prix, [Stock])
4. DETAIL (Commande, Produit, Quant)
 Client référence NCLI
 Commande référence NCOM
 Produit référence NPRO
[Link] HATIMI - SQL

Exercice 2
 Des éditeurs se réunissent pour créer une Base de Données
sur leurs publications scientifiques. Dans de telles
publications, plusieurs auteurs se regroupent pour écrire un
livre en se répartissant les chapitres à rédiger. Après
discussion, voici le schéma obtenu :
1. LIVRE (titreLivre, année, éditeur, chiffreAffaire)
2. CHAPITRE (titreLivre, titreChapitre, nbPages)
3. AUTEUR (nomAuteur, prénom, annéeNaissance)
4. REDACTION (nomAuteur, titreLivre, titreChapitre)
 Donnez les ordres CREATE TABLE pour le schéma, en
spécifiant soigneusement clés primaires et étrangères
 Précisez les stratégies de modification et de suppression sur
les clés étrangères?
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 31

EHTP - 2015 07/05/2018

Exercice 3
PERSONNE (ID entier, [CIN] chaine(10), nom chaine(50), prenom
chaine(50), naissance date, sexe, [deces] date, [pere] entier,
[mere] entier, [nationalite] chaine(30))
• ID est une clé primaire
• CIN est unique
• [Link] référence [Link]
• [Link] référence [Link]
• Interdire la suppression d’une personne qui a des enfants
• En cas de modification de l’ID d’un parent, répercuter la modification sur les
enfants
• nationalite a comme valeur par défaut ‘MAROCAINE’
• La date de décès est supérieure à la date de naissance
• sexe prend deux valeurs possibles (M, F)
[Link] HATIMI - SQL

Solution Exercice 3
CREATE TABLE personne (
ID integer primary key,
Cin varchar(10) unique,
Nom varchar(50) not null,
Prenom varchar(50) not null,
Naissance date not null,
Deces date,
Sexe char check (sexe IN (‘M’, ‘F’)),
Pere integer references personne on update cascade,
Mere integer references personne on update cascade,
Nationalite varchar(50) default ‘MAROCAINE’,
Check (naissance < deces));
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 32

EHTP - 2015 07/05/2018

Modification du schéma
 Après la création du schéma de base de
données (clause CREATE) on peut le
modifier en supprimant ou en modifiant
des tables, des colonnes, des contraintes
... avec les clauses ALTER et DROP.

[Link] HATIMI - SQL

Modification du schéma
 Suppression des objets de la base de données :
TABLE, DOMAIN, INDEX, FUNCTION…  DROP
 Modification du schéma d’une table (ALTER) :
 Renommer la table
 Renommer une colonne
 Ajouter/Supprimer une colonne
 Modifier le type d’une colonne
 Ajouter / Modifier / Supprimer une valeur par défaut
 Ajouter / Modifier / Supprimer une contrainte : Clé
primaire, Clé étrangère, Check …
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 33

EHTP - 2015 07/05/2018

Suppression d’un domaine

 Commande DROP DOMAIN
 DROP DOMAIN nom_domaine [CASCADE |
RESTRICT]
 RESTRICT : Option par défaut. Interdit la
suppression tant que le domaine est utilisé.
 CASCADE : Force la suppression du domaine et
les colonnes qui en dépendent sont
supprimées.

[Link] HATIMI - SQL

Suppression d’un domaine

 Soit le script suivant :
CREATE DOMAIN couleur AS text CHECK (VALUE IN
(‘Blanc’, ‘Rouge’, ‘Noir’));
CREATE TABLE produit (id integer primary key, colo
couleur);
 Que fait cette commande ?

DROP DOMAIN couleur;

 Que fait cette commande ?

DROP DOMAIN couleur CASCADE;

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 34

EHTP - 2015 07/05/2018

Suppression d’une table

 Commande DROP TABLE
 DROP TABLE nom_table [CASCADE |
RESTRICT]
 RESTRICT : Option par défaut. Interdit la
suppression de la table tant qu’il y a des objets
qui en dépendent (ex: clés étrangères).
 CASCADE : Forcer la suppression de la table et
les objets qui en dépendent sont supprimés.

[Link] HATIMI - SQL

Exemple
 Exécuter le script suivant :
 CREATE TABLE tab1 (id1 integer primary key);
 CREATE TABLE tab2 (id2 integer primary key,
col1 integer references tab1);
 Insérer quelques lignes dans tab1 et tab2.
 Tester la commande DROP TABLE tab1;
 Tester la commande DROP TABLE tab1
CASCADE;
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 35

EHTP - 2015 07/05/2018

Renommer une table

 Syntaxe:
ALTER TABLE old_name RENAME TO new_name

Remarque : Dans PostgreSQL le renommage est

impacté (en cascade) au niveau des tables en
référence.

[Link] HATIMI - SQL

Renommer une colonne

Syntaxe PostgreSQL :
ALTER TABLE [ IF EXISTS ] [ ONLY ] name
[ * ] RENAME [ COLUMN ] column_name
TO new_column_name

Exemple :
ALTER TABLE p RENAME COLUMN np TO
numero
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 36

EHTP - 2015 07/05/2018

Insertion d’une colonne

 Syntaxe :
 ALTER TABLE produit ADD COLUMN
description TEXT;
 ADD [ COLUMN ] column_name data_type
[ COLLATE collation ] [ column_constraint [
... ] ]
 La colonne est insérée avec la valeur
NULL pour les enregistrements existant
dans la table,
[Link] HATIMI - SQL

Suppression d’une colonne

 Syntaxe :
 ALTER TABLE produit DROP COLUMN prix;
 La colonne est supprimée avec toutes les
contraintes associées, sauf si la colonne
est référencée par d’autres objets,
 Exemple : Clé primaire : La clé primaire
est supprimée sauf si cette dernière est
référencée par une clé étrangére,
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 37

EHTP - 2015 07/05/2018

Modifier le type d’une colonne

 Syntaxe :
 ALTER TABLE nom_table ALTER [ COLUMN ]
nom_colonne TYPE type
 ALTER TABLE puf ALTER qte TYPE numeric;

 Les valeurs déjà existantes seront converties si

possible. Si les valeurs existantes ne peuvent pas
être converties, l’utilisateur doit spécifier la
méthode de conversion,
 Remarque : Il est préférable de supprimer toutes
les contraintes liées à une colonne avant de
modifier son type puis de les réécrire après la
modification. [Link] HATIMI - SQL

Contrainte explicite
 On peut donner un nom aux contraintes avec
le mot-clé CONSTRAINT.
 Exemple :
create table CLIENT
( IdCli char(3),
Nom char(30),
Adresse char(100),
Ville char(20),
constraint C2 primary
[Link] HATIMIkey
- SQL (IdCli))

B. EL HATIMI - Langage SQL 38

EHTP - 2015 07/05/2018

Contrainte explicite
 Ajouter une contrainte avec un nom. Exemple:
alter table CLIENT
add constraint CS1 unique (Nom,Adresse,Ville)
 Supprimer une contrainte par son nom :
alter table CLIENT
drop constraint CS1

[Link] HATIMI - SQL

Supprimer une contrainte clé

primaire
 Syntaxe :
 ALTER TABLE produit DROP CONSTRAINT
produit_pkey
 produit_pkey est le nom de la contrainte clé
étrangére
 Conséquences : La table ne peut plus être
éditée et donc on ne peut plus insérer de
nouveaux enregistrements,
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 39

EHTP - 2015 07/05/2018

Ajouter une contrainte clé

primaire
 Syntaxes :
 ALTER TABLE prodduit ADD PRIMARY KEY
(nproduit);
 ALTER TABLE produit ADD CONSTRAINT PK1
PRIMARY KEY (nproduit);
 Conditions : La colonne (ou les colonnes)
choisie comme clé primaire ne doivent
pas contenir de valeur dupliquées ou
NULL.
[Link] HATIMI - SQL

Supprimer une contrainte

référentielle
 Syntaxe :
 ALTER TABLE puf DROP FOREIGN KEY
(nu); ????
 ALTER TABLE puf DROP CONSTRAINT
puf_nu_fkey;

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 40

EHTP - 2015 07/05/2018

Ajouter une contrainte

référentielle
 Syntaxe :
 ALTER TABLE puf ADD FOREIGN KEY (nu)
REFERENCES u;
 ALTER TABLE puf ADD CONSTRAINT
ma_contrainte FOREIGN KEY (nu) REFERENCES
u;
 Conditions : Il faut que toutes les valeurs
dans la colonne existent dans la colonne de
référence.
[Link] HATIMI - SQL

Ajout / Suppression d’une valeur

par défaut
 Ajout ou changement de la valeur par défaut. Seuls les
nouveaux enregistrements seront concernés :

ALTER [ COLUMN ] column SET DEFAULT expression

 Enlever la valeur par défaut. Seuls les nouveaux

enregistrements seront concernés :

ALTER [ COLUMN ] column DROP DEFAULT

 Exemple :
alter table ETUDIANT
alter column FILIERE set default ‘SIG’
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 41

EHTP - 2015 07/05/2018

Colonne obligatoire/facultative
 Rendre une colonne obligatoire. Ceci n’est
possible que si aucune valeur NULL n’existe
dans la colonne :
ALTER TABLE distributors ALTER COLUMN street
SET NOT NULL
 Pour rendre une colonne facultative :

ALTER TABLE distributors ALTER COLUMN street

DROP NOT NULL

[Link] HATIMI - SQL

Exercice : Ajout d’une clé

artificielle
 On veut modifier la table PUF en remplaçant la clé
naturelle (NU, NP, NF) par une clé artificielle ID.
1. Ajouter à la table PUF un attribut ID de type
SERIAL. A quoi correspond ce type ?
2. Déclarer ID comme la nouvelle clé primaire.
3. Que se passe t-il si vous aviez dupliqué une
valeur, avant de déclarer ID comme clé primaire ?

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 42

EHTP - 2015 07/05/2018

Exercice : Ajout d’une clé

artificielle
 Alter table puf add column id serial;
 Alter table puf drop constraint puf_pkey;
 Alter table puf add primary key (id);
 Si on veut garder la contrainte d’unicité sur
(NP, NU, NF) on ajoute la commande :
 ALTER TABLE products ADD CONSTRAINT
unicite_npnunf UNIQUE (np, nu, nf);

[Link] HATIMI - SQL

Pseudo-type SERIAL
 SERIAL est un pseudo-type entier
permettant au SGBD de créer une séquence
puis d’associer à la colonne des valeurs
issues de la séquence.
 Syntaxe: CREATE TABLE tablename (
colname SERIAL);
 Selon la taille voulue on peut utiliser
SMALLSERIAL, SERIAL ou BIGSERIAL.
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 43

EHTP - 2015 07/05/2018

Les séquences
 Une séquence permet de créer un générateur de
valeurs entiéres sous forme d’une énumération
de valeurs uniques à partir d’une valeur de
départ (par défaut 1) et d’un pas
d’incrémentation (par défaut 1).
 Syntaxe : CREATE SEQUENCE seq START 101;
 La fonction nextval permet de récupérer la valeur
courante de l’énumération :
 select nextval('seq');
[Link] HATIMI - SQL

SERIAL Vs SEQUENCE
CREATE TABLE etudiant (id serial
primary key, nom varchar(50), prenom
varchar(50));

CREATE SEQUENCE seq;

CREATE TABLE etudiant (id integer
default nextval(‘seq’) primary key, nom
varchar(50), prenom varchar(50));
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 44

EHTP - 2015 07/05/2018

Langage SQL

Partie 2 : SQL – Langage de

manipulation de données

Manipulation des données

 Insérer des enregistrements : INSERT
 Modifier des enregistrements : UPDATE
 Supprimer des enregistrements :
DELETE
 Sélectionner des enregistrements :
SELECT

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 45

EHTP - 2015 07/05/2018

INSERT
 En respectant l’ordre des colonnes de la
table, on peut ajouter des enregistrements :
INSERT INTO NomTable VALUES (Val1, Val2,
Val3…)
 On peut changer l’ordre d’entrée des

colonnes :
INSERT INTO NomTable (Col1, Col2, Col3)
VALUES (Val1, Val2, Val3)
[Link] HATIMI - SQL

INSERT
 DETAIL (Commande, Produit, Quant)
 insert into DETAIL values (‘22547’, ‘P147’,
25)
 Cas de l’insertion d’une valeur NULL :
 insert into DETAIL values (‘22548’, ‘P130’,

NULL)  insert into DETAIL (Commande,

Produit) values (‘22548’, ‘P130’)
 insert into DETAIL values (‘22549’, ‘P160’,)
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 46

EHTP - 2015 07/05/2018

INSERT
 On peut ne pas introduire toutes les valeurs.
Dans ce cas il faut spécifier les colonnes
voulues :
 CLIENT (NCLI, Nom, Adresse, Ville, [Type],
Compte)
insert into CLIENT (Nom, NCLI, Ville, Compte)
values (‘CASALUX’, ‘C312’, ‘Casablanca’, 2000)

[Link] HATIMI - SQL

INSERT
 On peut ajouter à une table existante des
données extraites d’une autre table :
insert into CLIENT_TANGER
select NCLI, Nom, Adresse
from CLIENT
where Ville = ‘Tanger’

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 47

EHTP - 2015 07/05/2018

Commande COPY
 Lorsqu’on veut insérer un grand nombre de lignes
ou importer des données à partir d’un fichier texte,
il est préférable d’utiliser la commande COPY.
 La commande COPY ne fait pas partie du standard
SQL. C’est une commande propre à PostgreSQL.

[Link] HATIMI - SQL

DELETE
 Les enregistrements ne sont pas référencés
individuellement aussi n’y a-t-il pas de commande
pour supprimer un enregistrement en particulier.
 Par contre, on peut supprimer toutes les lignes
d’une table :
DELETE FROM films;
 Ou on peut supprimer les lignes répondant à une
condition :
DELETE FROM films WHERE genre = ‘Horreur'
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 48

EHTP - 2015 07/05/2018

DELETE
 Suppression de lignes : delete
 Exemple on veut supprimer les détails de
commande qui spécifient des produits en
rupture de stock :
delete from DETAIL
where Produit in ( select NPRO
from PRODUIT
where Qstock = 0)

[Link] HATIMI - SQL

TRUNCATE
 TRUNCATE quickly removes all rows
from a set of tables. It has the same
effect as an unqualified DELETE on
each table, but since it does not
actually scan the tables it is faster.
Furthermore, it reclaims disk space
immediately, rather than requiring a
subsequent VACUUM operation. This is
most useful on large tables.
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 49

EHTP - 2015 07/05/2018

VACUUM
 VACUUM reclaims storage occupied by
dead tuples. In normal PostgreSQL
operation, tuples that are deleted or
obsoleted by an update are not
physically removed from their table;
they remain present until a VACUUM is
done. Therefore it's necessary to do
VACUUM periodically, especially on
frequently-updated tables.
[Link] HATIMI - SQL

UPDATE
 Modifier les valeurs : update
update CLIENT
set Ville = ‘Casablanca’
where Ville = ‘Casa’

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 50

EHTP - 2015 07/05/2018

Exemple
 Schéma de la BD Gestion_ACHAT :
 CLIENT (NCLI, Nom, Adresse, Ville, [Type], Compte)
 COMMANDE (NCOM, Client, Date)
 PRODUIT (NPRO, Libelle, Prix, QStock)
 DETAIL (Commande, Produit, Quant)
 Client référence NCLI
 Commande référence NCOM
 Produit référence NPRO

[Link] HATIMI - SQL

Consultation et extraction de données

 Syntaxe de la requête : select … from … [where]

…
 Select : précise les nom des colonnes qui constituent
chaque ligne du résultat.
 From : précise les tables desquelles sont extraites les
données.
 Where : donne les conditions de sélection des lignes cibles.

 Le résultat d’une requête est une table temporaire.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 51

EHTP - 2015 07/05/2018

Extraction simple
 Requête :
select NCLI, Nom, Ville
from CLIENT

 Equivalent en algèbre relationnelle à une

projection :
∏ [NCLI, Nom,Ville] CLIENT
[Link] HATIMI - SQL

Extraction simple
 Select * permet l’extraction de toutes les
colonnes des tables cibles.
 Exemple, si on veut afficher tous les
enregistrements de la table CLIENT :
select *
from CLIENT

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 52

EHTP - 2015 07/05/2018

Extraction de lignes sélectionnées

 La clause WHERE permet d’inclure un prédicat
lors de la sélection des enregistrements :
select NCLI, Nom
from CLIENT
where Ville = `Tanger`
 Equivalent en algébre relationnel à :

∏ [NCLI,Nom] σ [ Ville = ‘Tanger`] CLIENT

[Link] HATIMI - SQL

Extraction de lignes sélectionnées

 Si une clé entière n’est pas reprise dans la
clause SELECT le résultat peut contenir des
lignes identiques.
Ville
Tanger
select Ville
Casablanca
from CLIENT 
where Type = ‘C1’ Rabat
Casablanca
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 53

EHTP - 2015 07/05/2018

Extraction de lignes sélectionnées

 On pourra éliminer les lignes en double par la
clause distinct :
Ville
select distinct Ville
Casablanca
from CLIENT 
Tanger
where Type = ‘C1’
Rabat

[Link] HATIMI - SQL

Extraction de lignes sélectionnées

Remarque importante :
 Si la clause SELECT cite toutes les colonnes

d’une table ou contient une clé entière

(primaire ou secondaire), l’unicité des lignes
est garantie.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 54

EHTP - 2015 07/05/2018

Prédicat de sélection
 Le prédicat de sélection a la forme d’une
expression logique. Exemples :
 WHERE Valeur = 12.5
 WHERE Nom = ‘InfoNord’
 WHERE date > ’10/08/2006’
 Syntaxe : WHERE [Colonne/Constante]
{=,<,>,<=,>=,<>} [Colonne/Constante]

[Link] HATIMI - SQL

Prédicat de sélection
 Un prédicat peut combiner plusieurs
conditions.
 Soient P1, P2 et P3 des expressions logiques :
 Where P1 and P2
 Where P1 or P2
 Where not P1
 Where P1 and (P2 or P3)

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 55

EHTP - 2015 07/05/2018

Prédicat de sélection
 Pour afficher Nom, Adresse et Compte des
clients de Casablanca ayant un compte
négatif.

Select Nom, Adresse, Compte

From CLIENT
Where Ville = ‘Casablanca’ and Compte <= 0

[Link] HATIMI - SQL

Condition sur la valeur NULL

 La clause SELECT peut inclure un prédicat sur
la valeur NULL.
 Syntaxe : WHERE NomColonne [is null / is not
null]
 Exemple :
Select Nom, Adresse, Compte
From CLIENT
Where Ville is null
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 56

EHTP - 2015 07/05/2018

Autres prédicats de sélection

 Condition sur l’appartenance à une liste :
Type in (‘C1’,’B2’,’A2’)
Ville not in (‘Casablanca’,Rabat’,’Tanger’)
 Condition sur l’appartenance à un intervalle de
valeurs :
Compte between 10000 and 50000
Date not between ’01/01/2001’ and ’31/12/2005’
[Link] HATIMI - SQL

Autres prédicats de sélection

 Condition sur les chaînes de caractères :
Type like ‘_1’  _ remplace un caractère
Nom not like ‘%sarl%’  % remplace une chaîne de
caractères
 Si on veut inclure les caractères % et _ dans les
valeurs :
Nom like ‘%$_GEO%’ escape ‘$’
Rq. : on peut utiliser un autre caractère spécial que $.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 57

EHTP - 2015 07/05/2018

Ordre des lignes du résultat

 La clause ORDER BY permet de trier le
résultat d’une requête selon une ou
plusieurs colonnes.
 La clause ORDER BY vient après la clause
WHERE.
 Exemple : Afficher les clients triés par ville.
select * from CLIENT
order by Ville

[Link] HATIMI - SQL

Ordre des lignes du résultat

 Le tri peut se faire selon plusieurs
colonnes :
select *
from CLIENT
order by Ville, Nom
 Les clients seront triés par ville et pour
une même ville par nom.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 58

EHTP - 2015 07/05/2018

Ordre des lignes du résultat

 Par défaut le tri se fait par ordre croissant des
valeurs. On peut spécifier l’ordre explicitement
avec les mots clés asc (croissant) et desc
(décroissant).
 Donner les clients triés par ordre décroissant de
nom :
Select *
from CLIENT
order by Nom desc

[Link] HATIMI - SQL

Clause LIMIT
 LIMIT { count | ALL } OFFSET start
 count specifies the maximum number of rows to
return, while start specifies the number of rows to
skip before starting to return rows. When both are
specified, start rows are skipped before starting to
count the count rows to be returned.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 59

EHTP - 2015 07/05/2018

Données dérivées
 Pour les sélections simples, les données
extraites proviennent directement des tables.
 On peut aussi spécifier dans la clause SELECT
des données dérivées c’est-à-dire issues d’un
calcul ou des constantes.

[Link] HATIMI - SQL

Données dérivées
 Exemple : Donner le montant de la TVA
des produits en stock dont la quantité en
stock est supérieure à 500.

Select ‘TVA de`, NPRO, ‘=‘, 0.2*Prix*QStock

From PRODUIT
Where QStock > 500
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 60

EHTP - 2015 07/05/2018

Données dérivées

TVA de NPRO = 0.2*Prix*QStock

TVA de P402S = 66520.00
TVA de P384T = 12478.00
TVA de P55T = 110540.00
TVA de P121R = 87456.00

[Link] HATIMI - SQL

Alias de colonne
 Lors de l’affichage du résultat, les colonnes
reçoivent un nom qui est celui indiquée
dans la clause SELECT.
 Le nom peut être encombrant et peu
significatif. On peut alors définir
explicitement un nom de colonne qui
apparaîtra à l’affichage : alias de colonne.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 61

EHTP - 2015 07/05/2018

Alias de colonne
Select NPRO as Produit, 0.2*Prix*QStock as
Valeur_TVA
From PRODUIT
Where QStock > 500

[Link] HATIMI - SQL

Alias de colonne

Produit Valeur_TVA
P402S 66520.00
P384T 12478.00
P55T 110540.00
P121R 87456.00

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 62

EHTP - 2015 07/05/2018

Fonctions et opérateurs SQL

 Le langage SQL offre un grand nombre de
fonctions et d’opérateurs. Exemple, les
fonctions mathématiques (COS(x), SQRT(x)
...) ou les opérateurs arithmétiques : *, +,
/,...
 L’appel à ces fonctions et à ces opérateurs
se fait dans les clauses SELECT ou WHERE.
Les arguments sont des noms de colonnes,
des constantes ou des expressions.
[Link] HATIMI - SQL

Fonctions et opérateurs SQL

 Fonctions sur les chaînes de caractères :
 Longueur de la chaîne : char_length(ch)
 Concaténation : ch1 || ch2
 Transformation en minuscules : lower(ch)
 Transformation en majuscules : upper(ch)
 ….

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 63

EHTP - 2015 07/05/2018

Fonctions SQL
 Exemple : Pour concaténer le nom en
majuscules de la société et son adresse en
minuscules :
 select upper(Nom) || ‘@’ || lower(Adresse)
as Identification
from Client

[Link] HATIMI - SQL

Données agrégées et fonctions

agrégatives
 Les fonctions simples retournent un résultat par
ligne sélectionnée.
 Les fonctions dites agrégatives retournent une
seule valeur agrégée calculée à partir de toutes les
lignes sélectionnées.
 Exemple 1 : la fonction Count(*) retourne le
nombre de lignes trouvées par une requête de
sélection.
 Exemple 2 : la fonction Min(NomColonne) retourne
le minimum des valeurs de la colonne.
 Remarque : les fonctions agrégatives ignorent les
valeurs NULL.
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 64

EHTP - 2015 07/05/2018

Fonctions agrégatives
 Principales fonctions agrégatives :
 Count(*) : nombre de lignes trouvées.
 Count (nom_col) : nombre de lignes avec valeur
de la colonne nom_col non nulle.
 Avg(nom_col) : moyenne des valeurs de la
colonne.
 Sum(nom_col) : somme des valeurs de la
colonne.
 Min(nom_col) : minimum des valeurs de la
colonne.
 Max(nom_col) : maximum des valeurs de la
colonne. [Link] HATIMI - SQL

Fonctions agrégatives
 Donner le nombre et la moyenne des comptes
des clients de Casablanca ?
Select count (*) as Nombre, avg (Compte) as
Moyenne
From CLIENT
Where Ville = ‘Casablanca’

Nombre Moyenne
32 4355.13
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 65

EHTP - 2015 07/05/2018

Fonctions agrégatives
 Donner la valeur totale du stock ?
select sum (Qstock*Prix) as Valeur_Stock
from PRODUIT

Valeur_Stock

82500.00

[Link] HATIMI - SQL

Fonctions agrégatives
select sum (Qstock) as Total_Stock, Libelle
from PRODUIT

 FAUX !!! On ne peut pas utiliser une sélection

simple sur une colonne avec une fonction
agrégative.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 66

EHTP - 2015 07/05/2018

Fonctions agrégatives
 Attention aux valeurs dupliquées !
 La requête suivante, ne donne pas le
nombre de clients ayant fait au moins une
commande mais le nombre de commandes
où Client n’est pas NULL :
select count (Client)
from COMMANDE

[Link] HATIMI - SQL

Fonctions agrégatives
 Pour avoir le nombre de clients ayant fait au
moins une commande on utilisera :
Select count (distinct Client)
From COMMANDE
 A ne pas confondre avec :

Select distinct count (Client)

From COMMANDE

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 67

EHTP - 2015 07/05/2018

Fonctions agrégatives
 On veut sélectionner les produits ayant comme
prix le prix maximal de tous les produits.
 La requête 1 est fausse car on ne peut pas utiliser
une fonction agrégative dans la clause WHERE :
Select NPRO
From Produit
Where Prix = Max(Prix)
 Solution utiliser une sous-requête :

Select NPRO
From Produit
Where Prix = (Select Max(Prix) From Produit)
[Link] HATIMI - SQL

Données groupées (group by)

 Mot clé : group by
 Donner pour chaque ville le nombre de ses
clients et la moyenne de leurs comptes ?
select Ville,
count(*) as NombCli,
avg(Compte) as MoyCompte
from CLIENT
group by Ville

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 68

EHTP - 2015 07/05/2018

Données groupées (group by)

 Remarque :
 PostgreSQL prend en compte la valeur
NULL lors d’un GROUP BY.

[Link] HATIMI - SQL

Données groupées
 On ne peut spécifier dans la clause
SELECT que des noms de colonnes et des
fonctions dont le résultat est une valeur
unique par groupe : critère de
groupement, fonctions agrégatives,
constantes…

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 69

EHTP - 2015 07/05/2018

Sélection sur des données groupées

 Clause having au lieu de where (sélection
sur des lignes).
 Sur la même requête précédente on ne
veut garder que les villes ayant plus de
trois clients.

[Link] HATIMI - SQL

Sélection sur des données groupées

select Ville,
count(*) as NombCli,
avg(Compte) as MoyCompte
from CLIENT
where compte > 50000
group by Ville
having count(*) >= 3
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 70

EHTP - 2015 07/05/2018

Extraction de données de
plusieurs tables
 On a traité des requêtes simples dans les
exemples précédents avec extraction de
données à partir d’une seule table ?
 Comment extraire des requêtes à partir de
plusieurs tables ?
 Sous-requêtes
 Jointures implicites ou explicites

[Link] HATIMI - SQL

Sous-requêtes
 Donner les commandes des clients de
Casablanca.
 On commence par trouver les clients de
Casablanca avec la requête :
Select NCLI
From CLIENT
Where Ville = ‘Casablanca’
 Qui donne les clients {‘C201’, ‘C805’, ‘C47’,
‘C214’}.
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 71

EHTP - 2015 07/05/2018

Sous-requêtes
 On peut alors trouver leur commande
avec la requête :
Select NCOM, Date
From COMMANDE
Where Client in (‘C201’, ‘C805’, ‘C47’,
‘C214’)
 Méthode non satisfaisante !!!
[Link] HATIMI - SQL

Sous-requêtes
 On écrira :
Select NCOM, Date
From COMMANDE
Where Client in (Select NCLI
From CLIENT
Where Ville = ‘Casablanca’)
 La requête incluse dans la clause Where est
appelée sous-requête.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 72

EHTP - 2015 07/05/2018

Sous-requêtes
 Donner les produits qui ont été
commandés par au moins un client de
Casablanca ?
 CLIENT (NCLI, Nom, Adresse, Ville, [Type],
Compte)
 COMMANDE (NCOM, Client, Date)
 PRODUIT (NPRO, Libelle, Prix, QStock)
 DETAIL (Commande, Produit, Quant)
[Link] HATIMI - SQL

Sous-requêtes
 On écrira :
Select Produit From DETAIL
Where Commande in
(Select NCOM From COMMANDE
Where Client in (Select NCLI
From CLIENT
Where Ville = ‘Casablanca’))
 On peut avoir plusieurs niveaux de sous-
requêtes.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 73

EHTP - 2015 07/05/2018

Sous-requêtes : Références à une

même table
 Quels sont les clients habitant dans la
même ville que le client ‘C805’ ?

 Requête :
select * from CLIENT
where Ville = ( Select Ville from CLIENT
where NCLI = ‘C805’)
[Link] HATIMI - SQL

Sous-requêtes
 Si la sous-requête renvoie une seule
ligne, on peut utiliser les opérateurs de
comparaison.
 Exemple :

select * from CLIENT

where Compte > ( select Compte
from CLIENT
where NCLI = ‘C805’)
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 74

EHTP - 2015 07/05/2018

Sous-requêtes
 Exercice : Ecrire la requête qui donne les
commandes avec une quantité commandée du
produit ‘P001’ inférieure à celle de la
commande ‘CO001’ (pour le même produit) ?
 CLIENT (NCLI, Nom, Adresse, Ville, [Type],
Compte)
 COMMANDE (NCOM, Client, Date)
 PRODUIT (NPRO, Libelle, Prix, QStock)
 DETAIL (Commande, Produit, Quant)
[Link] HATIMI - SQL

Sous-requêtes : Confusion sur les

références
 En règle général, la référence se rapporte
à la sous-requête la plus emboîtée dont
une table contient ce nom de colonne.
 En cas de confusion on peut créer un
alias de table :
select * from PRODUIT as P
where [Link] > 200
 Remarque : Le mot-clé as est facultatif.
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 75

EHTP - 2015 07/05/2018

Sous-requêtes : Confusion sur les

références
 Exemple : Trouver les clients dont le
compte est supérieur à la moyenne des
comptes des clients de la même ville.
 Select * from CLIENT AS C
where Compte > ( select avg(Compte)
from CLIENT
where Ville = [Link] )
[Link] HATIMI - SQL

Recherche avec condition sur les

ensembles
 exists / not exists : Teste si l’ensemble
des lignes retournées est vide ou pas.
 Quels sont les produits qui n’ont pas été
commandés ?
select * from PRODUIT
where not exists ( select *
from DETAIL
where Produit = NPRO)
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 76

EHTP - 2015 07/05/2018

Sous-requêtes avec quantificateurs

 all / any / some : permettent de comparer une
valeur avec celles d’un ensemble défini par une
sous-requête.
 Syntaxe :

<valeur attribut> <opérateur de comparaison> < all

/ any / some > <ensemble>
 Donner les détails de la (ou des) commande
avec la quantité commandée minimale pour le
produit ‘PP21’ ?
[Link] HATIMI - SQL

Sous-requêtes avec quantificateurs

select *
from DETAIL
where Quant <= all
( select Quant
from DETAIL
where Produit = ‘PP21’)
and Produit = ‘PP21’
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 77

EHTP - 2015 07/05/2018

Sous-requêtes avec quantificateurs

 any / some : ce sont des synonymes. Ils
testent s’il existe dans un ensemble au
moins un élément qui satisfait une
condition:
 Ensemble des numéros des fournisseurs de
produits rouges :
SELECT NF FROM PUF
WHERE NP = ANY ( SELECT NP FROM P
WHERE couleur = ‘rouge’ )
[Link] HATIMI - SQL

Sous-requêtes avec quantificateurs

 Remarque :
 IN  = ANY
 NOT IN  <> ANY

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 78

EHTP - 2015 07/05/2018

Jointures
 Une jointure contrairement aux sous-
requêtes permet d’extraire simultanément
des données de plusieurs tables.

[Link] HATIMI - SQL

Jointures
 Exemple : Compléter les commandes
avec les informations du client :
select NCOM, NCLI, DateCommande,
Nom, Adresse, Ville
from COMMANDE, CLIENT
where Client = NCLI

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 79

EHTP - 2015 07/05/2018

Jointures
 La condition NCLI = Client est dite
condition de jointure.
 Elle est dans ce cas sous la forme de Clé
étrangère = Clé primaire  Equi-jointure

[Link] HATIMI - SQL

Jointures
select NCOM, [Link], Date, Nom,
Adresse, Ville
from COMMANDE, CLIENT
where NCLI = Client
and CAT = ‘C1’
and Date < ’01/01/2005’

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 80

EHTP - 2015 07/05/2018

Jointures sans conditions :

select NCOM, [Link], Date, Nom,
Adresse, Ville
from COMMANDE, CLIENT
 C’est le produit cartésien des relations
CLIENT et COMMANDE.

[Link] HATIMI - SQL

Jointure implicite Vs Jointure

explicite
 La structure de jointure précédente est
dite jointure implicite car l’utilisateur ne
donne pas explicitement le type de
jointure à utiliser lors de l’exécution de
la requête.
 On parlera de jointure explicite lorsque
la requête indique le type de jointure en
utilisant le mot-clé JOIN.
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 81

EHTP - 2015 07/05/2018

Types de jointure explicite

 Il existe plusieurs types de jointure
explicite. Dans le cas de PostgreSQL on
a les types suivants :
 Produit cartésien.
 Jointure interne.
 Jointure naturelle.
 Jointure externe.

[Link] HATIMI - SQL

Jointure explicite – Produit

cartésien
 Mot-clé :
 CROSS JOIN
 Exemple :
 SELECT * FROM U CROSS JOIN F

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 82

EHTP - 2015 07/05/2018

Jointure explicite – Jointure

interne
 Mot-clé :
 INNER JOIN
 Exemple :
 SELECT * FROM U INNER JOIN F ON
[Link] = [Link];
 SELECT * FROM U INNER JOIN F USING
(Ville);

[Link] HATIMI - SQL

Jointure explicite – Jointure

naturelle
 Mot-clé :
 NATURAL INNER JOIN
 Remarque : C’est un cas particulier de
la jointure interne.
 Exemple :
 SELECT * FROM U NATURAL INNER JOIN
F;

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 83

EHTP - 2015 07/05/2018

Jointure explicite – Jointure

externe
 Mot-clé : [RIGHT | LEFT | FULL] OUTER JOIN
 Remarques : Il existe trois sous-types de jointure
externe : droite, gauche, totale.
 Exemples :
 SELECT * FROM U LEFT OUTER JOIN F ON [Link] =
[Link];
 SELECT * FROM U RIGHT OUTER JOIN F ON [Link] =
[Link];
 SELECT * FROM U FULL OUTER JOIN F ON [Link] =
[Link];
[Link] HATIMI - SQL

Vues (VIEW)
 Définition : « Une vue est une table
virtuelle dont le schéma et les tuples sont
dérivés de la base de données réelle à
partir d’une requête. »
 Syntaxe :

CREATE VIEW ma_vue AS Requête

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 84

EHTP - 2015 07/05/2018

Gestion des vues

CREATE VIEW Commande_Casa AS
SELECT NCom, Date
FROM Client, Commande
WHERE NCLI = Client
And Ville = ‘Casablanca’

[Link] HATIMI - SQL

Gestion des vues

 Une vue peut être utilisée (presque)
partout où une table peut être utilisée.

SELECT * FROM Commande_Casa

 Il est possible de construire une vue à

partir d’autres vues.

[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 85

EHTP - 2015 07/05/2018

Gestion des vues

 Supprimer une vue :
DROP VIEW ma_vue
 Modifier une vue (renommer, modifier le
schéma…) :
ALTER VIEW ma_vue [OPTIONS]

[Link] HATIMI - SQL

Opérateurs ensemblistes
 UNION / EXCEPT (ou MINUS) / INTERSECT
 Syntaxe : Requete1 <Opérateur> Requete2 avec
Requete1 et Requete2 retournant le même
schéma.
1. Requete1 UNION Requete2  Union des
résultats des deux requêtes.
2. Requete1 EXCEPT Requete2  Différence entre
les résultats des deux requêtes.
3. Requete1 INTERSECT Requete2  Intersection
des résultats des deux requêtes.
[Link] HATIMI - SQL

B. EL HATIMI - Langage SQL 86