Système d’Information

et Gestion en Réseau

SQL-2

Université Paris 2
L3 Economie-Gestion option Sciences du Management
Dominique Tachat
 Les fonctions ensemblistes
MAX
MAX fournit la valeur maximale d ’un attribut
• L’attribut peut être un nombre ou une chaine
de caractères
 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Durée maximale d’un

vol Paris -San Francisco

avion
id_Avion Type_Avion Nbre_Passagers
 Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

Durée maximale d’un vol Paris -San Francisco

Expr1000
SELECT MAX(v.Duree_Vol) 690
FROM vol AS v
WHERE v.Ville_Depart='Paris'
AND v.Ville_Arrivee='San Francisco';
SELECT MAX(v.Duree_Vol) AS Maximum Maximum
FROM vol AS v 690
WHERE v.Ville_Depart='Paris'
AND v.Ville_Arrivee='San Francisco';
 Les fonctions ensemblistes
MIN
MIN fournit la valeur minimale d ’un attribut
• L’attribut peut être un nombre ou une chaîne
de caractères
 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Date à laquelle le premier

pilote a été embauché
avion
id_Avion Type_Avion Nbre_Passagers
 Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

Date à laquelle le premier pilote a été embauché

SELECT MIN(p.Date_Emb) Expr1000

26/01/2014
FROM pilote AS p;
 Les fonctions ensemblistes
COUNT

COUNT permet de compter le nombre d’occurrences

 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Nombre d’avions ayant

décollé à 10:00

avion
id_Avion Type_Avion Nbre_Passagers
 Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

Nombre d’avions ayant décollé à 10:00

SELECT COUNT(v.id_Vol) SELECT COUNT(*)

FROM vol AS v FROM vol AS v
WHERE v.Heure_Decollage='10:00' ; WHERE v.Heure_Decollage='10:00';

Expr1000
2
 Les fonctions ensemblistes
SUM

SUM permet d ’additionner les valeurs d’un même

attribut
• L’attribut doit être numérique
 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Nombre total de passagers

transportés par le pilote nommé
Simon
avion
id_Avion Type_Avion Nbre_Passagers
 Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

id_Avion Type_Avion Nbre_Passagers

id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Nombre total de passagers transportés par le pilote

nommé Simon
SELECT SUM(a.Nbre_Passagers)
Expr1000
FROM (vol AS v INNER JOIN avion AS a
688
ON v.Code_Avion=a.id_Avion)
INNER JOIN pilote AS p
ON v.Code_Pilote=p.id_Pilote
WHERE p.Nom_Pilote='Simon';
V01 Paris San Francisco A01 P02 25/09/2020 10:00 690 900 A01 AirBus 380 538 P02 Simon Georges 15/04/2015 15000
V07 Paris Bruxelles A03 P02 26/09/2020 18:00 55 190 A03 AirBus 320 150 P02 Simon Georges 15/04/2015 15000
 Les fonctions ensemblistes
AVG

AVG permet de calculer la moyenne des valeurs d’un

même attribut
 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Moyenne des salaires des

pilotes embauchés avant le
1er janvier 2017
avion
id_Avion Type_Avion Nbre_Passagers
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Moyenne des salaires des pilotes embauchés avant

le 1er janvier 2017

SELECT AVG(p.Salaire_Mensuel) Expr1000

FROM pilote AS p 15250
WHERE p.Date_Emb<#2017-01-01#;
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel
P02 Simon Georges 15/04/2015 15000
P03 François Luc 26/01/2014 15500
 Possibilité de faire des calculs
dans SELECT
Recette globale pour le vol V01
 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Recette globale pour

le vol V01.
avion
id_Avion Type_Avion Nbre_Passagers
 Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

id_Avion Type_Avion Nbre_Passagers

Recette globale pour le vol V01

SELECT a.Nbre_Passagers*v.Prix_Billet
Expr1000
FROM vol AS v INNER JOIN avion AS a
484200
ON v.Code_Avion=a.id_Avion
WHERE v.id_Vol='V01';
Recette globale pour tous les vols
 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Recette globale pour tous les

vols.

avion
id_Avion Type_Avion Nbre_Passagers
id_ Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_ id_ Type_ Nbre_
Vol Depart Arrivee Avion Pilote Decollage Decollage Vol Billet Avion Avion Passagers
V01 Paris San A01 P02 25/09/2020 10:00 690 900 A01 AirBus 538
Francisco 380

V02 Londres Moscou A01 P01 25/09/2020 10:30 240 600 A01 AirBus 538
380
V08 New York Paris A01 P03 27/09/2020 03:00 480 750 A01 AirBus 538
380
V09 Paris San A01 P05 28/09/2020 09:30 650 850 A01 AirBus 538
Francisco 380

V03 Berlin Madrid A02 P03 25/09/2020 11:15 180 250 A02 AirBus 240
350
V07 Paris Bruxelles A03 P02 26/09/2020 18:00 55 190 A03 AirBus 150
320
V04 Londres Madrid A04 P06 26/09/2020 06:20 150 200 A04 Boeing 550
777
V06 Berlin Amsterda A05 P01 26/09/2020 14:30 85 275 A05 AirBus 300
m 350
V05 Bruxelles Rome A06 P05 26/09/2020 10:00 120 300 A06 Boeing 220
747

Table vol Table avion

 Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

id_Avion Type_Avion Nbre_Passagers

Recette globale pour tous les vols

SELECT SUM(a.Nbre_Passagers*v.Prix_Billet) Expr1000
FROM vol AS v INNER JOIN avion AS a 2014800
ON v.Code_Avion=a.id_Avion;
 Autres fonctions
DISTINCT
DISTINCT permet de n ’obtenir qu’une seule fois
chaque occurrence
Type d ’avions Sous-entendu les différents types d’avion

Avion
id_Avion Type_Avion Nbre_Passagers
A01 AirBus 380 538
A02 AirBus 350 240
A03 AirBus 320 150
A04 Boeing 777 550
A05 AirBus 350 300
A06 Boeing 747 220
 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Type d ’avions

avion
id_Avion Type_Avion Nbre_Passagers
 id_Avion Type_Avion Nbre_Passagers

Type d ’avions

Type_Avion
SELECT DISTINCT(a.Type_Avion) AirBus 320
FROM avion AS a; AirBus 350
AirBus 380
Boeing 747
SELECT DISTINCT a.Type_Avion Boeing 777
FROM avion AS a;
 Autres fonctions
ORDER BY

ORDER BY permet d ’ordonner par ordre

croissant ou décroissant (DESC)
 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Type des avions des vols au départ de Londres

et nombre de passagers par type croissant
(ordre alphabétique) Sous-entendu les
avion différents types d’avion
id_Avion Type_Avion Nbre_Passagers
 Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

id_Avion Type_Avion Nbre_Passagers

Type des avions des vols au départ de Londres et nombre de passagers

par type croissant (ordre alphabétique)

SELECT DISTINCT a.Type_Avion, a.Nbre_Passagers

FROM avion AS a INNER JOIN vol AS v
ON a.id_Avion=v.Code_Avion
WHERE v.Ville_Depart='Londres'
ORDER BY a.Type_Avion;
Type_Avion Nbre_Passagers
AirBus 380 538
Boeing 777 550
 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Nom et date d’embauche des

pilotes par ordre décroissant
des dates d’embauche
avion
id_Avion Type_Avion Nbre_Passagers
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Nom et date d’embauche des pilotes par ordre

décroissant des dates d’embauche

Nom_Pilote Date_Emb
SELECT p.Nom_Pilote, p.Date_Emb André 15/09/2019
FROM pilote AS p Dupuis 25/03/2018
ORDER BY p.Date_Emb DESC; Arthur 14/02/2018
Mathieu 15/06/2017
Simon 15/04/2015
François 26/01/2014
Ville de départ (ordre décroissant) et durée des vols
(ordre croissant)

• La 1ère clé de tri est l’attribut Ville_Depart (ordre décroissant)

• La 2ème clé de tri est Duree_Vol

• Pour une même valeur de Ville_Depart, les occurrences

seront triées selon l’attribut Duree_Vol (ordre croissant)
Vol
Date_ Heure_
id_Vol Ville_Depart Ville_Arrivee Code_Avion Code_Pilote Duree_Vol Prix_Billet
Decollage Decollage
V01 Paris San Francisco A01 P02 25/09/2019 10:00 690 900
V02 Londres Moscou A01 P01 25/09/2019 10:30 240 600
V03 Berlin Madrid A02 P03 25/09/2019 11:15 180 250
V04 Londres Madrid A04 P06 26/09/2019 06:20 150 200
V05 Bruxelles Rome A06 P05 26/09/2019 10:00 120 300
V06 Berlin Amsterdam A05 P01 26/09/2019 14:30 85 275
V07 Paris Bruxelles A03 P02 26/09/2019 18:00 55 190
V08 New York Paris A01 P03 27/09/2019 03:00 480 750
V09 Paris San Francisco A01 P05 28/09/2019 09:30 650 850
 Ville de départ (ordre décroissant) et durée des vols
(ordre croissant)
Ville_Depart Duree_Vol
SELECT v.Ville_Depart, v.Duree_Vol Paris 55
FROM vol AS v Paris 650
ORDER BY v.Ville_Depart DESC, v.Duree_Vol; Paris 690
New York 480
Londres 150
Londres 240
Bruxelles 120
Berlin 85
Berlin 180
 Autres fonctions
GROUP BY

GROUP BY permet de faire des regroupements

 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Nombre total de passagers

transportés sur chaque type
d’avion
avion
id_Avion Type_Avion Nbre_Passagers
 Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

id_Avion Type_Avion Nbre_Passagers

Nombre total de passagers transportés sur

chaque type d’avion
Type_Avion Expr1001
AirBus 320 150 150
SELECT a.Type_Avion,SUM(a.Nbre_Passagers) AirBus 350 540 240+300

FROM vol AS v INNER JOIN avion AS a AirBus 380 2152 538+538+

538+538
ON v.Code_Avion=a.id_Avion Boeing 747 220 220
GROUP BY a.Type_Avion ;
Boeing 777 550 550
 Autres fonctions
GROUP BY

GROUP BY est souvent utilisé avec la clause HAVING qui

permet de spécifier des caractéristiques du groupement
 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Nombre total de passagers pour

chaque ville de départ telle que le
1er vol soit après 9:00
avion
id_Avion Type_Avion Nbre_Passagers
 Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

id_Avion Type_Avion Nbre_Passagers

Nombre total de passagers pour chaque ville de

départ telle que le 1er vol soit après 9:00
SELECT v.Ville_Depart, SUM(a.Nbre_Passagers)
FROM vol AS v INNER JOIN avion AS a
ON v.Code_Avion=a.id_Avion
GROUP BY v.Ville_Depart
HAVING MIN(v.Heure_Decollage) >'09:00';

Ville_Depart Expr1001

Berlin 540 240+300

Bruxelles 220 220
Paris 1226 538+150+538
Il n’est pas obligatoire d’utiliser la clause HAVING
quand la caractéristique du groupement peut
s’exprimer avec une condition simple.

Prix moyen des billets pour chaque trajet. Il faudra

exclure les trajets à destination de Madrid
 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Prix moyen des billets pour chaque

trajet. Il faudra exclure les trajets à
destination de Madrid
avion
id_Avion Type_Avion Nbre_Passagers
 Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

Prix moyen des billets pour chaque trajet. Il faudra

exclure les trajets à destination de Madrid
SELECT v.Ville_Depart,v.Ville_Arrivee, AVG(v.Prix_Billet)
FROM vol AS v
WHERE v.Ville_Arrivee <>'Madrid'
GROUP BY v.Ville_Depart,v.Ville_Arrivee;

Ville_Depart Ville_Arrivee Expr1002

Berlin Amsterdam 275
Bruxelles Rome 300
Londres Moscou 600
New York Paris 750
Paris Bruxelles 190
Paris San Francisco 875
 Autres fonctions
BETWEEN
BETWEEN a AND b permet de tester l’appartenance à
un intervalle (bornes comprises)
 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Ville de départ et ville d’arrivée des vols dont la

durée est comprise entre 50 et 200 mn(bornes
comprises)
avion
id_Avion Type_Avion Nbre_Passagers

On ne souhaite pas conserver les doublons des vols

(même ville de départ et même ville d’arrivée)
 Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

Ville de départ et ville d’arrivée des vols dont la durée est

comprise entre 50 et 200 mn(bornes comprises)

SELECT DISTINCT v.Ville_Depart,v.Ville_Arrivee

FROM vol AS v
WHERE v.Duree_Vol BETWEEN 50 AND 200;

Ville_Depart Ville_Arrivee
Berlin Madrid
Londres Madrid
Bruxelles Rome
Berlin Amsterdam
Paris Bruxelles
 Autres fonctions
LIKE
LIKE permet d’utiliser des jokers dans des chaînes de
caractères

Le joker _ représente un unique caractère

Le joker % représente une chaîne de caractères de
longueur quelconque
Sous Access 2016 selon le paramétrage
Le joker ? représente un unique caractère
Le joker * représente une chaîne de caractères de
longueur quelconque
 Autres fonctions
LIKE
Numéro des vols effectués sur un avion de type AirBus

Avion
id_Avion Type_Avion Nbre_Passagers
A01 AirBus 380 538
A02 AirBus 350 240
A03 AirBus 320 150
A04 Boeing 777 550
A05 AirBus 350 300
A06 Boeing 747 220
 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Numéro des vols effectués sur

un avion de type AirBus

avion
id_Avion Type_Avion Nbre_Passagers
 Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

id_Avion Type_Avion Nbre_Passagers

Numéro des vols effectués sur un avion de type AirBus

SELECT v.id_Vol id_Vol

FROM vol AS v INNER JOIN avion AS a V01
ON v.Code_Avion=a.id_Avion V02

WHERE a.Type_Avion LIKE 'AirBus%'; V08

V09
V03
V07
V06
 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Numéro des vols qui ont décollé à xx:00

L’attribut Heure_Décollage est une chaîne de
caractères
avion
id_Avion Type_Avion Nbre_Passagers
 Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

Numéro des vols qui ont décollé à xx:00

id_Vol
SELECT v.id_Vol V01
FROM vol AS v V05
WHERE v.Heure_Decollage LIKE '_ _:00'; V07
V08
 Autres fonctions
IS NULL IS NOT NULL

IS NULL et IS NOT NULL permettent de vérifier si

l’attribut est renseigné ou pas
Nom des pilotes dont on ne connaît pas la date
d’embauche.

Pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel
P01 Dupuis Antoine 25/03/2018 12000
P02 Simon Georges 15/04/2015 15000
P03 François Luc 26/01/2014 15500
P04 André Georges 10000
P05 Arthur Louis 14/02/2018 12000
P06 Mathieu François 15/06/2017 13500
 Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet

pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Nom des pilotes dont

on ne connaît pas la
date d’embauche.
avion
id_Avion Type_Avion Nbre_Passagers
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel

Nom des pilotes dont on ne connaît pas la date

d’embauche.

SELECT p.Nom_Pilote
Nom_Pilote
FROM pilote AS p
André
WHERE p.Date_Emb IS NULL;