Transcript Aucun titre de diapositive

Approfondissement Bases de Données
NFA011
Cédric du Mouza
(d’après des supports d’Elisabeth Métais)
Cédric du Mouza – NFA011
1
SQL, le langage d'interrogation

Rappels

Algèbre relationnelle - les opérateurs

Expression des requêtes à l'aide de l'algèbre relationnelle

Projection et restriction

Fonctionnalités diverses

Requêtes imbriquées

La jointure

Opérateurs ensemblistes

La division

Groupement (GROUP BY)
Cédric du Mouza – NFA011
2
Le modèle relationnel
Une base de données est constituée d’un ensemble de relations
 Une relation possède un schéma, constitué d’un nom de relation
et d’un ensemble d’attributs
 Un attribut d’une relation se caractérise par un nom unique et
prend ses valeurs dans un domaine donné (ex.: chaîne de
caractères, entier, date, etc)

Exemple:
AVION(Numav:entier,Capacité:entier,Type:string,Entrepot:string)
PILOTE(Matricule:entier,Nom:string,Ville:string,Age:entier,Salaire:
entier)
Cédric du Mouza – NFA011
3
Schéma relationnel

Une relation peut être représentée sous forme de table où:



Chaque colonne correspond à un attribut
Chaque ligne (appelée aussi nuplet) correspond à un élément de l’ensemble de la
relation
L’ordre des lignes n’a pas d’importance contrairement aux colonnes
AVION
Numav
14
345
PILOTE Matricule
1
2
Capacite
25
75
Type
A400
B200
Nom
Ville
Figue
Cannes
Lavande Touquet
Cédric du Mouza – NFA011
Entrepot
Garches
Maubeuge
Age
45
24
4
Salaire
28004
11758
Langage d’interrogation
Rappels
Cédric du Mouza – NFA011
5
SQL (Structured Query Language)
SQL permet:

La définition et la modification
du schéma de la base de
donnée,

La manipulation des données

L'interrogation des données,

Le contrôle des accès à la base.
Cédric du Mouza – NFA011
6
Généalogie du langage SQL
Heure_départ
Caen
{V.Heure_Départ/ Vol (V)
Numvol
DEPART
VOL
Autre
Algèbre
relationnelle
et V.Ville_arrivée = 'Caen'
et $ D / Départ (D)
et D.Numvol = V.Numvol
et D.Date = '19-12-95'}
Calcul de
tuples
SQL
Cédric du Mouza – NFA011
7
Compagnie Aerienne (1)
AVION
Numav
14
345
Capacite
25
75
Type
A400
B200
PILOTE Matricule
1
2
PASSAGER
Numab
1
8
Entrepot
Garches
Maubeuge
Nom
Ville
Figue
Cannes
Lavande Touquet
Nomab
Nifance
Téarice
Cédric du Mouza – NFA011
8
Age
45
24
Salaire
28004
11758
Compagnie Aerienne (2)
VOL
Numvol Heure_départ Heure_arrivée Ville _départ Ville_arrivée
AL12
AF8
08-18
11-20
DEPART
09-12
23-54
Numvol
AL12
AL12
RESERVATION
Date_dep
31-12-95
19-12-95
Numab
Numvol
Date_dep
1
2
AL12
AL12
31-12-95
31-12-95
Cédric du Mouza – NFA011
Paris
Vaux
Numav
14
345
9
Lilles
Rio
Matricule
1
2
Langage d’interrogation
Algèbre relationnelle - les opérateurs
Cédric du Mouza – NFA011
10
Opérateurs de l'algèbre relationnelle
Interrogation de la base de données à travers des
opérateurs de type algébrique, c'est-à-dire qui peuvent se
composer pour obtenir le résultat.
5 opérations de base pour exprimer toutes les requêtes.
2
Opérations unaires:
Restriction
Projection
Cédric du Mouza – NFA011
11
Opérateurs de l'algèbre relationnelle
3
opérations binaires
Union
Différence
Produit
 Autres
cartésien
opérations dérivées
Jointure
Intersection
Division
Cédric du Mouza – NFA011
12
La restriction
La restriction (ou sélection) d'une relation R selon un
critère Q donne une relation de même schéma, avec pour
tuples résultats ceux de R qui satisfont le critère Q.
Elle est notée
σQ(R)
Q est une expression composée de connecteurs (ET ^,
OU v) et de prédicats (<, >, ≤ , ≥ , ≠ , =) portant sur les
attributs, évaluables à Vrai ou à Faux pour un tuple
donné.
Cédric du Mouza – NFA011
13
La restriction
AVION
NUMAV
CAPAC.
TYPE
ENTREP.
1
150
B707
Cannes
2
100
A500
Cannes
3
45
C2000
Rio
4
200
B707
Nice
Critère de restriction:
Entrepôt = 'Cannes'
Cédric du Mouza – NFA011
14
La projection
La projection d'une relation R sur un ensemble d'attribut C
donne une relation ayant pour schéma cet ensemble
d'attributs.
Elle est notée
πC(R)
Ses tuples sont ceux de la relation R, réduits aux attributs de
la projection.
Cédric du Mouza – NFA011
15
La projection
AVION
Attributs de projection:
capacité, type
NUMAV
CAPAC.
TYPE
ENTREP.
1
150
B707
Cannes
2
100
A500
Cannes
3
45
C2000
Rio
4
200
B707
Nice
Cédric du Mouza – NFA011
16
Combinaison des deux opérations
AVION
Les opérateurs de restriction
et de projection peuvent se
combiner pour sélectionner
un résultat:
NUMAV
CAPAC.
TYPE
ENTREP.
1
150
B707
Cannes
2
100
A500
Cannes
3
45
C2000
Rio
4
200
B707
Nice
Cédric du Mouza – NFA011
17
UNION, DIFFERENCE ET INTERSECTION
R1
R1
R1
R2
R2
UNION
R2
R2
DIFFERENCE
INTERSECTION
Cédric du Mouza – NFA011
18
Sur l'exemple:
AVION_1
AVION_2
NUMAV
3
CAPAC.
TYPE
NUMAV
C2000
Nice
2
100
A500
Cannes
4
200
B707
Nice
Cédric du Mouza – NFA011
TYPE
ENTREP.
1
150
B707
Cannes
2
100
A500
Cannes
ENTREP.
45
CAPAC.
19
Sur l'exemple: UNION
AVION_1  AVION_2
NUMAV
CAPAC.
TYPE
ENTREP.
1
150
B707
Cannes
2
100
A500
Cannes
3
45
C2000
Nice
4
200
B707
Nice
Cédric du Mouza – NFA011
20
Sur l'exemple: INTERSECTION
AVION_1  AVION_2
NUMAV
CAPAC.
2
100
Cédric du Mouza – NFA011
21
TYPE
ENTREP.
A500
Cannes
Sur l'exemple: DIFFERENCE
AVION_1 - AVION_2
NUMAV
CAPAC.
TYPE
ENTREP.
1
150
B707
Cannes
Cédric du Mouza – NFA011
22
Le produit cartésien
Le produit cartésien de deux relations R1 et R2 est une relation
dont le schéma est la concaténation des schémas de R1 et R2.
 Il est noté R1 x R2
 Ses tuples sont ceux de R1, concaténés chacun à tous les
tuples de R2.

RESERVATION
NUMAB
NOM
19-DEC-95
1
Amandier
AI200
23-DEC-95
2
Lavande
AL 500
24-DEC-95
NUMAB
NUMVOL
DATE_DEP
1
AI 100
2
2
Cédric du Mouza – NFA011
PASSAGER
23
Le produit cartésien
Réservation X Passager
R.NUMAB
NUMVOL
DATE_DEP
P.NUMAB
NOM
1
AI 100
19-DEC-95
1
Amandier
2
AI200
23-DEC-95
1
Amandier
2
AL 500
24-DEC-95
1
Amandier
1
AI 100
19-DEC-95
2
Lavande
2
AI200
23-DEC-95
2
Lavande
2
AL 500
24-DEC-95
2
Lavande
Cédric du Mouza – NFA011
24
La jointure
Une jointure est un produit cartésien suivi d'une
restriction sur un critère.
Réservation X Passager
Critère:
RESERVATION.NUMAB
= PASSAGER.NUMAB
R.NUMAB NUMVOL
DATE_DEP
P.NUMAB
NOM
1
AI 100
19-DEC-95
1
Amandier
2
AI200
23-DEC-95
1
Amandier
2
AL 500
24-DEC-95
1
Amandier
1
AI 100
19-DEC-95
2
Lavande
2
AI200
23-DEC-95
2
Lavande
2
AL 500
24-DEC-95
2
Lavande
Cédric du Mouza – NFA011
25
Résultat de la jointure
RESULTAT:
Jointure de la relation RESERVATION et de la relation PASSAGER
sur le critère RESERVATION.NUMAB = PASSAGER.NUMAB
RESERVATION
PASSAGER
numab
R.NUMAB NUMVOL
DATE_DEP
P.NUMAB
NOM
1
AI 100
19-DEC-95
1
Amandier
2
AI200
23-DEC-95
2
Lavande
2
AL 500
24-DEC-95
2
Lavande
Cédric du Mouza – NFA011
26
La division
Le résultat de la division d'une relation R par une relation S est une relation Q telle
que:
(1) Le schéma de Q est constitué des attributs de R n'appartenant pas à S.
(2) Les tuples de Q sont ceux qui, concaténés à n'importe quel tuple de S donnent un
tuple de R.
R
MATRICULE
S
NUMAV
1
100
2
100
2
101
NUMAV
:
100
101
=
Q
Cédric du Mouza – NFA011
MATRICULE
2
27
Langage d’interrogation
Expression des requêtes à l'aide de l'algèbre
relationnelle
Cédric du Mouza – NFA011
28
Exemple 1
REQUETE:
Quels sont les numéros, types et capacités des avions ayant une
capacité supérieure à 100 ?
Cédric du Mouza – NFA011
29
Exemple 1
Résultat
REQUETE:
Quels sont les numéros,
types et capacités des avions
ayant une capacité
supérieure à 100 ?
NUMAV, TYPE,
CAPACITE
CAPACITE > 100
AVION
Cédric du Mouza – NFA011
30
Exemple 1
2 opérations de restriction sont-elles commutatives?
σA(σB(R))=σB(σA(R)) ??
2 opérations de projection sont-elles commutatives?
πA(πB(R)) = πB(πA(R)) ??
La restriction et la projection sont-elles des opérations
commutatives?
πA(σB(R)) = σB(πA(R)) ??
Cédric du Mouza – NFA011
31
Exemple 2
REQUETE:
Quels sont les noms des passagers voyageant sur le vol
AF105 du 26 mars 1996?
Cédric du Mouza – NFA011
32
Exemple 2
REQUETE:
NOMAB
Quels sont les noms
des passagers
voyageant sur le vol
AF105 du 26 mars
1996?
Numab = Numab
NUMVOL = 'AF105'
AND DATE_DEP =
'26-03-96'
PASSAGER
Cédric du Mouza – NFA011
33
RESERVATION
Exemple 2
Une opération de sélection et de jointure sont-elles
commutatives?
σA(R∞S) = σA(R)∞S
Une opération de projection et de jointure sont-elles
commutatives?
πA(R∞S) = πA(R)∞S
Cédric du Mouza – NFA011
34
Exemple 3
REQUETE:
Quels sont les pilotes habitant à Deauville et partant pour Las
Vegas ?
Cédric du Mouza – NFA011
35
Exemple 3
REQUETE:
Nom
Quels sont les
pilotes habitant à
Deauville et partant
pour Las Vegas ?
Matricule
Numvol
Ville = 'Deauville'
Ville_arrivée =
'Las Vegas'
VOL
Cédric du Mouza – NFA011
DEPART
36
PILOTE
Exemple 3
2 jointures sont-elles commutatives?
R∞(S∞T) = (R∞S)∞T ??
Cédric du Mouza – NFA011
37
Exemple 3
D’après les règles identifiées, proposer d’autres expressions sous
forme d’arbre pour la requête de l’exemple 3.
Conclusion?
Cédric du Mouza – NFA011
38
Exemple 4
REQUETE:
Quels sont les noms des pilotes effectuant le vol
AL123 ?
Cédric du Mouza – NFA011
39
Exemple 4
REQUETE:
Nom
Quels sont les noms des
pilotes effectuant le vol
AL123 ?
Matricule = Matricule
Numvol = 'AL123
DEPART
PILOTE
Cédric du Mouza – NFA011
40
Exemple 5
REQUETE:
Quels sont les noms des passagers pour des vols partant pour Dakar ?
Cédric du Mouza – NFA011
41
Exemple 5
REQUETE:
NOMAB
Quels sont les noms des
passagers pour des vols partant
pour Dakar ?
Numab
Numvol
Ville_arrivée =
'Dakar'
VOL
Cédric du Mouza – NFA011
RESERVATION
42
PASSAGER
Langage d’interrogation
Projection et restriction
Cédric du Mouza – NFA011
43
Projection, Restriction
Le
principe
SELECT AVION.numav
FROM AVION
WHERE AVION.capacité > 200
Projection seule:
Restriction seule:
SELECT AVION.numav
FROM AVION
SELECT AVION.*
FROM AVION
WHERE AVION.capacité > 200
Cédric du Mouza – NFA011
44
Exercices
Exercice n° 1: Quel est l'âge du pilote de matricule 15 ?
Exercice n° 2: Quels sont les pilotes de cette compagnie
aérienne ?
(leur matricule, leur nom, leur ville, leur âge et leur salaire).
Cédric du Mouza – NFA011
45
Correction de l'exercice n° 1
Exercice n° 1: Quel est l'âge du pilote de matricule 15 ?
SELECT PILOTE.AGE
FROM PILOTE
WHERE MATRICULE = 15
Cédric du Mouza – NFA011
46
Correction de l'exercice n° 2
Exercice n° 2: Quels sont les pilotes de cette compagnie
aérienne (leur matricule, leur nom, leur ville, leur âge et leur
salaire).
SELECT *
FROM PILOTE
Cédric du Mouza – NFA011
47
Langage d’interrogation
Fonctionnalités diverses
Cédric du Mouza – NFA011
48
Tri, doublons, et alias
(a) Tri
(c) Omission du préfixe
SELECT AVION.numav
FROM AVION
WHERE AVION.CAPACITE > 200
ORDER BY AVION.numav DESC
(ou ASC)
SELECT numav
FROM AVION
WHERE CAPACITE > 200
(d) Emploi d ’alias
(b) Suppression des doublons
SELECT DISTINCT AVION.numav
FROM AVION
WHERE AVION.CAPACITE > 200
(ou ALL)
Cédric du Mouza – NFA011
SELECT A.numav
FROM AVION A
WHERE A.CAPACITE > 200
49
Exercices
Exercice n° 3: Quels sont les types d'avions entreposés à
Tarascon, avec leur capacité, par ordre de capacités croissantes ?
Cédric du Mouza – NFA011
50
Correction de l'exercices n° 3
Exercice n° 3: Quels sont les types d'avions entreposés à
Tarascon, avec leur capacité, par ordre de capacités croissantes ?
SELECT DISTINCT TYPE, CAPACITE
FROM AVION
WHERE ENTREPOT = 'Tarascon'
ORDER BY CAPACITE ASC
Cédric du Mouza – NFA011
51
Prédicats dans le critère de sélection
= != > < >= <=
NOT OR AND
IN BETWEEN x AND y
LIKE
SELECT NUMAV
FROM AVION
WHERE CAPACITE > 200
AND ((ENTREPOT= 'Saint nom la Bretèche') OR (ENTREPOT= 'Mantes la Jolie'))
AND NUMAV != 300
SELECT NUMAV
FROM AVION
WHERE ENTREPOT IN ('Saint nom la Bretèche' , 'Mantes la Jolie')
AND CAPACITE BETWEEN 200 AND 210
AND TYPE LIKE 'B_EING%'
Cédric du Mouza – NFA011
52
Opérateurs logiques et valeurs nulles
NOT
TRUE
FALSE
NULL
FALSE
TRUE
NULL
AND
TRUE
FALSE
NULL
TRUE
TRUE
FALSE
NULL
FALSE
FALSE
FALSE
FALSE
NULL
NULL
FALSE
NULL
OR
TRUE
FALSE
NULL
TRUE
TRUE
TRUE
TRUE
FALSE
TRUE
FALSE
NULL
NULL
TRUE
NULL
NULL
Cédric du Mouza – NFA011
53
Exercices
Exercice n° 4: Quelles sont les villes ayant un nom
commençant par 'L' et où un pilote de moins de 20 ans gagne
plus de 200000F ?
Exercice n° 5: Quels sont les numéros d'avions, avec leur
entrepôt, ne résidant pas à Marolles-en-Hurepoix, ayant une
capacité supérieure à 200 ?
(donnés par ordre de numéros décroissants).
Cédric du Mouza – NFA011
54
Correction de l'exercice n° 4
Exercice n° 4: Quelles sont les villes ayant un nom commençant
par 'L' et où un pilote de moins de 20 ans gagne plus de 200000F ?
SELECT DISTINCT VILLE
FROM PILOTE
WHERE AGE < 20
AND SALAIRE > 200000
AND VILLE LIKE 'L%'
Cédric du Mouza – NFA011
55
Correction de l'exercice n° 5
Exercice n° 5: Quels sont les numéros d'avions, avec leur
entrepôt, ne résidant pas à Marolles-en-Hurepoix, ayant une
capacité supérieure à 200 ?
(donnés par ordre de numéros décroissants).
SELECT NUMAV, ENTREPOT
FROM AVION
WHERE ENTREPOT != 'Marolles-en-Hurepoix'
AND CAPACITE > 200
ORDER BY NUMAV DESC
Cédric du Mouza – NFA011
56
Les fonctions de date
Fonction
Desc
Norme
Access
MySQL
Sql Srv
Oracle
Current_date
Date courante
O
N
O
N
N
Current_time
Heure courante
O
N
O
N
N
Getdate
Heure et date courante
N
N
N
O
N
Now
Heure et date courante
N
O
O
O
O
Sysdate
Date et heure courante
N
N
O
N
O
Day/month/year
Sélectionne le jour/mois/an
N
O
O
O
N
To_char(f1,f2)
Conversion de date ou
numérique en string
N
N
N
N
O
Cédric du Mouza – NFA011
57
Les fonctions sur chaînes de caractères
Fonction
Desc
Norme
Access
MySQL
Sql Srv
Oracle
Lower/Upper
Mise en minuscules/majusc
O
N
O
O
O
Substring
Extraction sous-chaîne
O
N
O
N
N
Substr
Extraction sous-chaîne
N
N
N
N
O
Position
Position d’une chaîne dans
une autre
O
N
O
N
N
Locate
Position d’une chaîne dans
une autre
N
O
O
O
O
Cédric du Mouza – NFA011
58
Les fonctions numériques
Fonction
Desc
Norme
Access
MySQL
Sql Srv
Oracle
Abs
Valeur absolue
N
O
O
O
O
Ceiling
Valeur approchée haute
N
O
O
O
N
Ceil
Valeur approchée haute
N
N
N
N
O
Floor
Valeur approchée basse
N
O
O
O
O
Cos, sin, tan,
exp, log,mod,
power, sqrt
Opérations diverses
N
O
O
O
O
Cédric du Mouza – NFA011
59
Les fonctions
Lower(a), Soundex (a), Abs(a), Sqrt(n), Length(a), etc...
SELECT NUMAV
FROM AVION
WHERE LOWER (ENTREPOT) = 'le vésiney'
SELECT MATRICULE, NOM
FROM PILOTE
WHERE SOUNDEX (NOM) = SOUNDEX ('SINTEXUPERI')
SELECT UPPER (NOM)
FROM PILOTE
WHERE ADRESSE = 'Cordillère des Andes'
Cédric du Mouza – NFA011
60
Exercices
Exercice n° 6: Quelles sont les villes d'où part un vol pour Ajaccio?
(donner les résultats en majuscule).
Cédric du Mouza – NFA011
61
Correction de l'exercice n° 6
Exercice n° 6: Quelles sont les villes d'où part un vol pour Ajaccio?
(donner les résultats en majuscule).
SELECT DISTINCT UPPER (VILLE_DEPART)
FROM VOL
WHERE VILLE_ARRIVEE = 'Ajaccio'
Cédric du Mouza – NFA011
62
Valeurs nulles

La valeur de certains attributs peut ne pas être connue, on parle
alors de valeurs nulles (mot-clé NULL)

NULL n’est pas une valeur mais une absence de valeur! Les
opérations ou les comparaisons ne peuvent lui être appliqué

Toute opération appliquée à NULL donne NULL

Toute comparaison avec NULL donne ni vrai ni faux, mais
UNKNOW
Cédric du Mouza – NFA011
63
Valeurs nulles
On associe à TRUE la valeur 1, à FALSE la valeur 0 et à
UNKNOW la valeur ½
 x AND y = min ( x , y )
 x OR y = max ( x , y )
 NOT x = 1 – x


Les conditions dans la clause WHERE sont évaluées pour
chaque tuple et seuls ceux pour lesquels on obtient 1 (TRUE)
sont gardés.
Cédric du Mouza – NFA011
64
Valeurs nulles
PILOTE Matricule
1
2
Nom
Ville
Figue
Cannes
Lavande Touquet
Age
Salaire
28004
24
SELECT Nom
SELECT Nom
SELECT Nom
From PILOTE
From PILOTE
From PILOTE
Where Age < 40
Where Age < 40
Where Age < 40
AND Salaire > 10000
OR Salaire < 10000
SELECT Nom From PILOTE
Where Age ≤ 40 OR Age ≥ 40
Cédric du Mouza – NFA011
65
Agrégations
 Jusqu’à
présent on a vu des requêtes comme suite
d’opérations tuple à tuple
 Les fonctions d’agrégation permettent d’exprimer
des conditions/faire des opérations sur des groupes
de tuples
 Avec SQL nous pouvons:
 Partitionner une relation en groupes
 Exprimer des relations sur des groupes
 Agréger des valeurs sur les groupes
 Il existe un groupe par défaut: la relation entière
Cédric du Mouza – NFA011
66
Fonctions d’agrégation
 S’appliquent
en générale à des valeurs numériques
 5 principales, appliquées sur chaque groupe




COUNT qui compte le nombre de valeurs non nulles
AVG qui calcule la moyenne des valeurs sur une colonne choisie
SUM idem mais fait la somme
MIN/MAX retourne la valeur MIN/MAX des valeurs d’une colonne choisie
 Les
opérateurs d’agrégation ne peuvent être utilisé
que dans un SELECT (ou HAVING, voir plus loin)
 Ne peuvent être utilisé dans un SELECT avec
d’autres attributs (sauf si GROUP BY, voir plus loin)
Cédric du Mouza – NFA011
67
Les agrégats
Avg, Count, Max, Min, Sum, Stddev, Variance
Exemple:
Quel est le nombre d'avions ayant une capacité supérieure à 200?
SELECT COUNT (NUMAV)
FROM AVION
WHERE CAPACITE > 200
SELECT COUNT (DISTINCT NUMAV)
FROM AVION
WHERE CAPACITE > 200
(ou ALL)
Cédric du Mouza – NFA011
68
Exercices
Exercice n° 7: Quel est le nombre d'avions en services (i.e. dans
la relation DEPART), le 19 décembre 1995 ?
Exercice n° 8: Quelle est la capacité moyenne des avions
entreposés à Pogo Togo ?
Cédric du Mouza – NFA011
69
Correction de l'exercice n° 7
Exercice n° 7: Quel est le nombre d'avions en services (i.e.
dans la relation DEPART), le 19 décembre 1995 ?
SELECT COUNT (DISTINCT NUMAV)
FROM DEPART
WHERE DATE_DEP = '19-12-95'
Cédric du Mouza – NFA011
70
Correction de l'exercice n° 8
Exercice n° 8 : Quelle est la capacité moyenne des avions
entreposés à Pogo Togo ?
SELECT AVG (ALL CAPACITE)
FROM AVION
WHERE AVION.ENTREPOT = 'Pogo Togo'
Cédric du Mouza – NFA011
71
Langage d’interrogation
Requêtes imbriquées
Cédric du Mouza – NFA011
72
Prédicat d’existence
SELECT
FROM
WHERE EXISTS ( SELECT
FROM
WHERE
);
Exemple:
Quels sont les pilotes qui habitent dans une ville où n'est basé aucun avion ?
SELECT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE
WHERE NOT EXISTS ( SELECT AVION.*
FROM AVION
WHERE AVION.ENTREPOT = PILOTE.VILLE)
Cédric du Mouza – NFA011
73
Requête à l'intérieur de IN
Exemple: Quels sont les noms des pilotes (avec leurs matricules) qui habitent
dans une ville où sont localisés des avions de capacité supérieure à 250 ?
SELECT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE
WHERE VILLE IN ( SELECT ENTREPOT
FROM AVION
WHERE CAPACITE > 250)
Cédric du Mouza – NFA011
74
Exercices
Exercice n° 9: Exprimer la requête suivante avec un "EXISTS":
Quels sont les noms des pilotes (avec leur matricule) qui habitent
dans une ville où sont localisés des avions de capacité supérieure à
250 ?
Exercice n° 10:
Exprimer la requête suivante avec un "IN":
Quels sont les noms des passagers (avec leur numéro d'abonné)
ayant réservé pour le 1er avril 1996 ?
Cédric du Mouza – NFA011
75
Correction de l'exercice n° 9
Exercice n° 9: Exprimer la requête suivante avec un "EXISTS":
Quels sont les noms des pilotes (avec leur matricule) qui habitent
dans une ville où sont localisés des avions de capacité supérieure
à 250 ?
SELECT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE
WHERE EXISTS ( SELECT *
FROM AVION
WHERE CAPACITE > 250
AND AVION.ENTREPOT = PILOTE.VILLE)
Cédric du Mouza – NFA011
76
Correction de l'exercice n° 10
Exercice n° 10: Quels sont les noms des passagers (avec leur
numéro d'abonné) ayant réservé pour le 1er avril 1996 ?
(avec un "IN").
SELECT PASSAGER. NUMAB, PASSAGER. NOMAB
FROM PASSAGER
WHERE NUMAB IN (SELECT RESERVATION.NUMAB
FROM RESERVATION
WHERE RESERVATION.DATE_DEP = '01-04-96')
Cédric du Mouza – NFA011
77
Langage d’interrogation
La jointure
Cédric du Mouza – NFA011
78
Exemple
Quels sont les noms des pilotes (avec leur matricule) qui assurent un vol le
vendredi 13 octobre 1995 ?
SELECT DISTINCT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE, DEPART
WHERE PILOTE.MATRICULE = DEPART.MATRICULE
AND DEPART.DATE_DEP = '13-10-95'
Cédric du Mouza – NFA011
79
Expression d'une jointure à l'aide de requêtes
imbriquées
Avec EXISTS SELECT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE
WHERE EXISTS ( SELECT *
FROM DEPART
WHERE PILOTE.MATRICULE = DEPART.MATRICULE
AND DEPART.DATE_DEP = '13-10-95')
Avec IN
SELECT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE
WHERE MATRICULE IN ( SELECT DEPART.MATRICULE
FROM DEPART
WHERE DATE_DEP ='13-10-95')
Cédric du Mouza – NFA011
80
Exercice
Exercice n° 11:
Quels sont les noms des pilotes (avec leur matricule) qui assurent
un vol le vendredi 13 octobre 1995, sur un avion de capacité
supérieure à 250 ?
a) de manière prédicative,
b) à l'aide de requêtes imbriquées.
Cédric du Mouza – NFA011
81
Correction de l'exercice n° 11
Exercice n° 11:
Quels sont les noms des pilotes (avec leur matricule) qui assurent
un vol le vendredi 13 octobre 1995, sur un avion de capacité
supérieure à 250 ?
a) de manière prédicative,
SELECT DISTINCT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE, DEPART, AVION
WHERE PILOTE.MATRICULE = DEPART.MATRICULE
AND DEPART.DATE_DEP = '13-10-95'
AND DEPART.NUMAV = AVION.NUMAV
AND AVION.CAPACITE > 250
Cédric du Mouza – NFA011
82
Correction de l'exercice n° 11 (suite)
Exercice n° 11:
Quels sont les noms des pilotes (avec leur matricule) qui assurent
un vol le vendredi 13 octobre 1995, sur un avion de capacité
supérieure à 250 ?
b) à l'aide de requêtes imbriquées
SELECT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE
WHERE MATRICULE IN ( SELECT DEPART.MATRICULE
FROM DEPART
WHERE DATE_DEP ='13-10-95'
AND NUMAV IN (SELECT AVION.NUMAV
FROM AVION
WHERE CAPACITE > 250))
Cédric du Mouza – NFA011
83
Exercices
Exercice n° 12: Quelles sont les paires de pilotes (matricules)
habitant dans la même ville ?
Exercice n° 13: Quels sont les noms des pilotes (avec leur
matricule) qui conduisent l'avion de numéro '666' ?
Exercice n° 14: Quels sont les noms des pilotes (avec leur
matricule) qui habitent dans la ville où est entreposé l'avion '666' ?
Cédric du Mouza – NFA011
84
Correction de l'exercice n° 12
Exercice n° 12: Quelles sont les paires de pilotes (matricules)
habitant dans la même ville ?

Il s'agit d'une auto-jointure
SELECT P1.MATRICULE, P2.MATRICULE
FROM PILOTE P1, PILOTE P2
WHERE P1.VILLE = P2.VILLE
AND P1.MATRICULE > P2.MATRICULE
Cédric du Mouza – NFA011
85
Correction de l'exercice n° 13
Exercice n° 13: Quels sont les noms des pilotes (avec leur
matricule) qui conduisent l' avion de numéro '666' ?
SELECT DISTINCT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE, DEPART
WHERE PILOTE.MATRICULE = DEPART. MATRICULE
AND DEPART.NUMAV = '666'
Cédric du Mouza – NFA011
86
Correction de l'exercice n° 14
Exercice n° 14: Quels sont les noms des pilotes (avec leur
matricule) qui habitent dans la ville où est entreposé l'avion '666' ?
SELECT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE, AVION
WHERE PILOTE.VILLE = AVION.ENTREPOT
AND AVION.NUMAV = '666'
Cédric du Mouza – NFA011
87
Autres types de jointure

LEFT/RIGHT OUTER JOIN : retourne le résultat de la jointure
plus tous les enregistrements de la table de gauche/droite
n’ayant pas joint (complétés avec des valeurs NULL)

FULL OUTER JOIN : fonctionne comme si on faisait à la fois une
LEFT et RIGHT OUTER JOIN

A INNER JOIN B ON… : équi-jointure (notation explicite)

A NATURAL JOIN B : jointure naturelle

A CROSS JOIN B : produit cartésien (notation explicite)
Cédric du Mouza – NFA011
88
Langage d’interrogation
Opérateurs ensemblistes
Cédric du Mouza – NFA011
89
L’union dans SQL
Quels sont les pilotes (matricule et nom) qui conduisent l'avion de numéro '666' OU
qui habitent dans la ville ou est entreposé l'avion '666' ?
SELECT DISTINCT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE, DEPART
WHERE PILOTE.MATRICULE = DEPART. MATRICULE
AND DEPART.NUMAV = '666'
UNION
SELECT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE, AVION
WHERE PILOTE.VILLE = AVION.ENTREPOT
AND AVION.NUMAV = '666'
Cédric du Mouza – NFA011
90
L’intersection dans SQL
Quels sont les pilotes (matricule et nom) qui conduisent l'avion de numéro '666'
ET qui habitent dans la ville ou est entreposé l'avion '666' ?
SELECT DISTINCT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE, DEPART
WHERE PILOTE.MATRICULE = DEPART. MATRICULE
AND DEPART.NUMAV = '666'
INTERSECT
SELECT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE, AVION
WHERE PILOTE.VILLE = AVION.ENTREPOT
AND AVION.NUMAV = '666'
Cédric du Mouza – NFA011
91
La différence dans SQL
Quels sont les pilotes (matricule et nom) qui conduisent l'avion de numéro
'666' SAUF ceux qui habitent dans la ville ou est entreposé l'avion '666' ?
SELECT DISTINCT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE, DEPART
WHERE PILOTE.MATRICULE = DEPART. MATRICULE
AND DEPART.NUMAV = '666'
MINUS
SELECT PILOTE.MATRICULE, PILOTE.NOM
FROM PILOTE, AVION
WHERE PILOTE.VILLE = AVION.ENTREPOT
AND AVION.NUMAV = '666'
Cédric du Mouza – NFA011
92
Langage d’interrogation
La division
Cédric du Mouza – NFA011
93
Expression de la division dans SQL
Quels sont les pilotes (leur matricule et leur nom) qui conduisent tous les avions?
<=> Quels sont les pilotes tels que, quel que soit l'avion, ils le conduisent ?
<=> Quels sont les pilotes tels que, quel que soit l'avion, il existe un départ assuré
par ce pilote sur cet avion ?
<=> Quels sont les pilotes tels qu'il n'existe pas d'avion tel qu'il n'existe pas de
départ de ce pilote sur cet avion ?
SELECT MATRICULE
FROM PILOTE
WHERE NOT EXISTS( SELECT *
FROM AVION
WHERE NOT EXISTS ( SELECT *
FROM DEPART
WHERE DEPART.MATRICULE = PILOTE.MATRICULE
AND DEPART.NUMAV = AVION.NUMAV))
Cédric du Mouza – NFA011
94
Exercices
Exercice n° 15: Quels sont les passagers qui ont réservé sur
tous les vols ?
Exercice n° 16: Quel est le nombre de pilotes qui conduisent
des avions en service
(i.e. dans la relation départ)?
Cédric du Mouza – NFA011
95
Correction de l'exercice n° 15
Exercice n° 15: Quels sont les passagers qui ont réservé sur
tous les vols ?
SELECT NUMAB
FROM PASSAGER P
WHERE NOT EXISTS ( SELECT *
FROM VOL V
WHERE NOT EXISTS (SELECT *
FROM RESERVATION R
WHERE R.NUMAB = P.NUMAB
AND R.Numvol = V.NUMVOL))
Cédric du Mouza – NFA011
96
Correction de l'exercice n° 16
Exercice n° 16: Quel est le nombre de pilotes qui conduisent
des avions en service
(i.e. dans la relation départ)?
SELECT COUNT (DISTINCT DEPART.MATRICULE)
FROM DEPART;
Cédric du Mouza – NFA011
97
Langage d’interrogation
Groupement (GROUP BY)
Cédric du Mouza – NFA011
98
GROUP BY
 L'opérateur

"GROUP BY" partitionne la table
on construit les groupes en associant les tuples
partageant la même valeur pour un ou plusieurs
attributs
 On
effectue le SELECT…FROM…WHERE, puis on
partitionne le résultat, puis on effectue les
fonctions d’agrégation
 Dans
le select on ne peut avoir que des fonctions
d’agrégation ou des attributs du GROUP BY
Cédric du Mouza – NFA011
99
GROUP BY
EXEMPLE: Quel
est le nombre de pilotes qui conduisent
des avions en service, par avion ?
SELECT NUMAV, COUNT (DISTINCT DEPART.MATRICULE)
FROM DEPART
GROUP BY NUMAV;
EXEMPLE: Quel
est l’heure maximale des vols
commençant par la lettre ‘A’ par trajet ?
SELECT MAX (Heure_départ)
FROM VOL
WHERE Numvol LIKE ‘A%’
GROUP BY Ville_départ, Ville_arrivée
Cédric du Mouza – NFA011
100
Exercice
Exercice n° 17: Quelle est la capacité moyenne des avions, par
entrepôt et par type ?
Cédric du Mouza – NFA011
101
Correction de l'exercice n° 17
Exercice n° 17: Quelle est la capacité moyenne des avions, par
entrepôt et par type ?
SELECT ENTREPOT, TYPE, AVG (CAPACITE)
FROM AVION
GROUP BY ENTREPOT,TYPE;
Cédric du Mouza – NFA011
102
GROUP BY ... HAVING

L'opérateur HAVING permet d'introduire une qualification sur
les sous-tables issues du GROUP BY:
Le HAVING est au GROUP BY ce que le WHERE est au FROM.
EXEMPLE:
Quels sont les avions qui
assurent plus de 3
départs ?
SELECT DEPART.NUMAV
FROM DEPART
GROUP BY NUMAV
HAVING COUNT (*) > 3;
Cédric du Mouza – NFA011
103
Exercice
Exercice n° 18: Quelles sont les villes où sont entreposés plus de
2 avions de type 'ICBM' ?
Cédric du Mouza – NFA011
104
Correction de l'exercice n° 18
Exercice n° 18: Quelles sont les villes où sont entreposés plus de
2 avions de type ''ICBM' ?
SELECT DISTINCT ENTREPOT
FROM AVION
WHERE TYPE = 'ICBM'
GROUP BY ENTREPOT
HAVING COUNT (*) > 2;
Cédric du Mouza – NFA011
105
CONCLUSION
SQL est un langage
d'interrogation très lisible,
par tous les utilisateurs
 SQL est un langage dans
lequel il est relativement
facile d'écrire, pour un
programmeur, à condition
de bien visualiser l'ordre d'
application
des
instructions:

Cédric du Mouza – NFA011
(1)
(2)
(3)
(4)
(5)
(6)
106
From
Where
Group by
Having
Select
Order by
BIBLIOGRAPHIE
:
"L'art des bases de données",
tome 3: "Comprendre et évaluer SQL"
Serge Miranda. Editions Eyrolles.
Cédric du Mouza – NFA011
107
Exercices supplémentaires
S1 Quels sont les noms des pilotes (avec leur matricule) âgés de
moins de 30 ans avec un salaire supérieur à celui d’un pilote
quelconque ayant plus que 40 ans?
S2 Quels sont les noms des pilotes (avec leur matricule) ayant
moins de 30 ans avec un salaire supérieur à ceux de tous les
pilotes ayant plus de 40 ans?
S3 Proposer une requête capable de donner les tuples qui sont en
doublons par rapport aux deux colonnes de la table Pilote :
Nom et Ville
S4 Proposer une requête capable de supprimer les tuples qui sont
en doublons par rapport aux deux colonnes Nom, Ville de la
table Pilote en laissant un seul tuple par ensemble de doublons
Cédric du Mouza – NFA011
108
Correction exercice S1
S1 Quels sont les noms des pilotes (avec leur matricule) ayant
moins de 30 ans avec un salaire supérieur à celui d’un
pilote quelconque ayant plus de 40 ans?
SELECT Matricule
FROM Pilote
WHERE Age < 30
AND Salaire >= ANY (
SELECT Salaire
FROM Pilote
WHERE Age > 40)
Cédric du Mouza – NFA011
109
Correction exercice S2
S2 Quels sont les matricules des pilotes ayant moins de 30 ans
avec un salaire supérieur à celui de tous les pilotes ayant
plus de 40 ans?
SELECT Matricule
FROM Pilote
WHERE Age < 30
AND Salaire >= ALL (
SELECT Salaire
FROM Pilote
WHERE Age > 40)
Cédric du Mouza – NFA011
110
Correction exercice S3
S3 Proposer une requête capable de donner les tuples qui sont
en doublons par rapport aux deux colonnes de la table
Pilote : Nom et Ville
SELECT Nom, Ville
FROM Pilote
GROUP BY Nom, Ville
HAVING COUNT(*) > 1
Cédric du Mouza – NFA011
111
Correction exercice S4
S4 Proposer une requête capable de supprimer les tuples qui sont
en doublons par rapport aux deux colonnes Nom, Ville de la
table Pilote tout en laissant un seul tuple par ensemble de
doublons
DELETE Pilote P1
WHERE EXISTS
(
SELECT *
FROM Pilote P2
WHERE P1.Ville = P2.Ville
AND P1.Nom = P2.Nom
AND P1.Matricule > P2.Matricule)
Cédric du Mouza – NFA011
112
SQL comme Langage de Définition des Données
Cédric du Mouza – NFA011
113
Types SQL
2
types d’attributs numériques:
 Numériques exacts (INTEGER, DECIMAL, …)
 Numériques flottants (FLOAT, DOUBLE, …)
 INTEGER stocke des entiers sur 4 octets (en
général) avec 2 variantes suivant la taille (SMALLINT
et BIGINT)
 DECIMAL(M,D) (ou NUMERIC) est un numérique de
taille max. M, avec un nombre de décimales fixé à D
 Numériques exacts servent aux valeurs dont la
précision est connue
Cédric du Mouza – NFA011
114
Types SQL
 Types
flottants:
 Utilisation semblable à celle des langages de
programmation
 S’appuie sur la représentation des num. flottants
propre à la machine
 FLOAT correspond aux flottants en simple précision
 DOUBLE PRECISION (ou DOUBLE ou REAL) est un
flottant en double précision
Cédric du Mouza – NFA011
115
Types SQL
Type
Description
Taille
INTEGER
Entier relatif
4 octets
SMALLINT
Idem
2 octets
BIGINT
Idem
8 octets
FLOAT
Flottant simple précision
4 octets
DOUBLE PRECISION
DOUBLE/REAL
NUMERIC(M,D)
Flottant double précision
8 octets
Numérique avec D décimales
M octets
DECIMAL
Numérique avec D décimales
M octets
Cédric du Mouza – NFA011
116
Types SQL
 Chaînes
de caractères:
 CHAR(M) chaîne d’exactement M caractères
(complète avec des blancs si besoin)
 VARCHAR(M) chaîne de taille variable, au plus M
 Pour des chaînes très longues, type BIT VARYING
(ou BLOB, ou LONG)
 Dates
 DATE stocke le jour, mois, année (sur 4 chiffres)
 TIME stocke heure, minute et seconde
(HH:MM:SS)
 DATETIME combine les 2 (AAAA-MM-JJ
HH:MM:SS)
Cédric du Mouza – NFA011
117
Types SQL
Type
Description
Taille
CHAR(M)
Chaîne de longueur fixe
M octets
VARCHAR(M)
Chaîne de longueur variable
m+1, m≤M
BIT VARYING(ou BLOB) Chaîne d’octets
Longueur de la
chaîne
4 octets
DATE
Date (jour, mois, année)
TIME
4 octets
DATETIME
Heure (heure, minute,
seconde)
Date et heure
YEAR
Année
2 octets
Cédric du Mouza – NFA011
8 octets
118
Types SQL
 Exercice
1: Proposer les types associés aux
attributs de la table PILOTE. Quel serait alors la taille
d’un enregistrement?
 Exercice
2: Idem avec la table VOL
Cédric du Mouza – NFA011
119
Création de tables

On doit préciser le schéma de la relation en entier: nom de la
relation, nom et type de chaque attribut. On peut ajouter des
contraintes.

Exemple:
CREATE TABLE AVION(Numav INTEGER,
Capacité SMALLINT,
Type VARCHAR(6),
Entrepôt VARCHAR(25));
Cédric du Mouza – NFA011
120
Contraintes
 Un
attribut peut être forcé à être renseigné avec NOT
NULL
 Clé primaire: il est important de donner une clé
primaire lors de la création d’une table, afin de
pouvoir désigner de manière unique un nuplet dans
d’autres tables
 Se déclare après la déclaration des attributs à l’aide
de PRIMARY KEY
 Tous les attributs d’une clé doivent être déclarés
comme NOT NULL
Cédric du Mouza – NFA011
121
Contraintes
 On
peut spécifier que la valeur d’un attribut est
unique pour toute la colonne avec UNIQUE
 Attention, la contrainte UNIQUE autorise que la
valeur ne soit pas renseignée (NULL)
 On peut préciser les clés étrangères à l’aide de
FOREIGN KEY, et préciser la table (clé primaire)
référencée par REFERENCES
 On peut exprimer une condition sur un attribut à
l’aide de CHECK
 On peut aussi donner des valeurs par défaut avec
DEFAULT
Cédric du Mouza – NFA011
122
Contraintes - exemples
CREATE TABLE AVION(Numav INTEGER NOT NULL,
Capacité SMALLINT,
Type VARCHAR(6),
Entrepôt VARCHAR(25),
PRIMARY KEY(Numav),
CHECK capacité < 500,
CHECK Type IN (‘A380’,’B200’,’A330’));
CREATE TABLE DEPART(Numvol INTEGER NOT NULL,
Date_dep DATE,
Numav INTEGER,
Matricule SMALLINT DEFAULT 1,
FOREIGN KEY(Numav) REFERENCES AVION, …
Cédric du Mouza – NFA011
123
Contraintes - exercices
 Essayer
de créer la table vol avec ses contraintes
 Compléter
la création de la table DEPART
Cédric du Mouza – NFA011
124
Contraintes
On peut lors de la création spécifier des règles liées
aux clés étrangères:
 Événements surveillés: suppression et mise-à-jour
 Si le nuplet avec clé primaire supprimé/modifié on
peut supprimer/modifier les nuplets avec clés
étrangères correspondants: ON DELETE
CASCADE/ON UPDATE CASCADE
 Lors d’une suppression d’un nuplet avec une clé
primaire on peut choisir de mettre la clé étrangère à
NULL dans les nuplets correspondants: ON DELETE
SET NULL
Cédric du Mouza – NFA011
125
Insertions
On réalise une insertion à l’aide de la commande
INSERT INTO… VALUES
INSERT INTO AVION VALUES (14,300,’A380’,’Nantes’)
INSERT INTO AVION(Numav,Type) VALUES (18,’A250’)
Mais attention, lors d’une insertion les contraintes (unicité, clé
primaire référencée existante, les CHECK, non-nullité, etc)
doivent être vérifiées pour que l’insertion ait lieu
Cédric du Mouza – NFA011
126
Modification du schéma

Ajouter d’une colonne DateAchat à AVION
ALTER TABLE AVION ADD DateAchat DATE;
Lors de l’ajout d’une nouvelle colonne, ses valeurs sont mises à
NULL ou si elle existe, à une valeur par défaut

Modifier la colonne Entrepôt (chaîne plus longue et non nulle)
ALTER TABLE AVION MODIFY Entrepôt VARCHAR(50) NOT NULL
Attention lors d’une modification on peut avoir des résultats
impossibles et/ou imprévisibles (varchar devenant integer, etc)
Cédric du Mouza – NFA011
127
Modification du schéma

Modifier la colonne en ajoutant une valeur par défaut
ALTER TABLE AVION ALTER Type SET DEFAULT ‘A380’

Ajouter une contrainte CHECK
ALTER TABLE AVION ADD CONSTRAINT verifieCapacité
CHECK(Capacité < 600)

Supprimer une contrainte (nommée)
ALTER TABLE AVION DROP CONSTRAINT verifieCapacité
Cédric du Mouza – NFA011
128
Modification du schéma

Changer le nom d’une colonne
ALTER TABLE AVION RENAME COLUMN Type TO Modèle

Supprimer une colonne
ALTER TABLE AVION DROP COLUMN DateAchat

Créer un index
CREATE INDEX monIndex ON AVION(Capacité)
Cédric du Mouza – NFA011
129
Créer une table à partir de tables existantes
SELECT Numav, Type INTO LISTE_AVION FROM AVION
WHERE Entrepôt = ‘Maubeuge’
SELECT Numvol, Date_dep, Ville_arrivée
INTO LISTE_VOL_DE_PARIS
FROM VOL, DEPART
WHERE VOL.Numvol = DEPART.Numvol
AND Ville_départ = ‘Paris’
Cédric du Mouza – NFA011
130
Exercices
Créer une table avec les numéros de ville de départ, ville
d’arrivée, nom et âge des pilotes gagnant plus de 4000 euros.
 Ajouter les contraintes suivantes: une clé primaire sur le nom,
un test pour interdire les pilotes ayant plus de 55 ans
 Renommer la colonne ‘nom’ en ‘nom de famille’
 Ajouter une colonne ‘ancienneté’ et créer un index sur celle-ci
 Supprimer l’index sur ‘ancienneté’
 Supprimer la colonne ‘ancienneté’
 Supprimer la contrainte sur l’âge
 Supprimer la colonne ‘ancienneté’

Cédric du Mouza – NFA011
131