Cours SQL LMD :
Langage de manipulation de données
Les Requêtes SELECT
Base de données exemple pour
le cours (1/2)
Client (noClient, nom, prénom, ddn, rue, CP, ville)
Produit (noProduit, libellé, prixUnitaire, noFournisseur)
Fournisseur (noFournisseur, raisonSociale)
Commande (noClient, noProduit, dateCommande,
quantité)
Clés primaires
Clés étrangères
Base de données exemple pour le cours (2/2)
Client
Commande Produit
noClient
nom noProduit
noClient
prénom libellé
noProduit
ddn pu
dateCommande
rue noFournisseur
quantité
CP
ville
Fournisseur
noFournisseur
raisonSociale
Syntaxe générale de la commande SELECT
SELECT *|{[ALL|DISTINCT]
expression [[AS]nomColonne]
[,expression [[AS] nomColonne]]… }
FROM relation [alias] [,relation [alias] …]
[WHERE condition]
[GROUP BY nomColonne [,nomColonne]…]
[HAVING condition]
[ORDER BY nomColonne [ASC|DESC]
[,nomColonne [ASC|DESC]…]
Si CdeSelect est une commande SELECT :
CdeSelect {UNION | INTERSECT | EXCEPT} CdeSelect
Oracle-SQL et la casse
des caractères
(majuscule/minuscule)
• Il n’y a pas de différence entre des noms (ou
identifiants) écrits en majuscules ou en minuscules
– ex. noms d’attributs, de tables, de contraintes…
• Seul cas où la casse est prise en compte : la
comparaison de chaînes de caractères
SELECT * Select *
FROM Client From CLIENT
WHERE nom = ’Dupont’ where NOM = ’Dupont’
Projection d'une relation et la clause DISTINCT
• Trouver les noClient et dateCommande de toutes les
Commandes
SELECT noClient, dateCommande
FROM Commande
noClient dateCommande
100 04/05/2003
200 12/4/2003
Commande SQL
100 5/1/2004 équivalente
300 25/2/2004
400 30/1/2004
400 30/1/2004
SELECT ALL noClient, dateCommande
FROM Commande
La clause DISTINCT
• Trouver les noClient et dateCommande de toutes les Commandes
SELECT DISTINCT noClient, dateCommande
FROM Commande
noClient dateCommande
100 04/05/2003
200 12/4/2003
Expression algébrique
100 5/1/2004 équivalente
300 25/2/2004
400 30/1/2004
{noClient, dateCommande}
(Commande)
Restriction d’une relation
sur une condition
• Sélectionner les Articles dont le prix est inférieur à 20€
et le numéro est supérieur à 30
SELECT *
FROM Article
WHERE prixUnitaire < 20 AND noArticle >
30
noArticle libellé prixUnitaire
31 Brosse 12 Exp. algéb.
équivalente
40 Tableau 9.99
50 Visseuse 19.99
prixUnitaire < 20 noArticle > 30 (Article)
Restriction : syntaxe d'une
condition
SELECT * FROM nom-relation
WHERE condition
• Syntaxe de condition
conditionSimple | (condition) | NOT (condition) |
condition {AND|OR} condition
• Syntaxe de conditionSimple
expression {= | <|>|<=|>=|<>|!=} expression |
expression [NOT]BETWEEN expression AND expression|
expression IS [NOT] NULL
expression [NOT] IN listeConstantes|
expression [NOT]LIKE patron
Restriction : Exemples
• Produits dont le prix est compris entre 50 et 100€
SELECT *
FROM Produit
WHERE prixUnitaire >= 50 AND prixUnitaire <= 100
SELECT *
FROM Produit
WHERE prixUnitaire BETWEEN 50 AND 100
• Produits dont le prix est inférieur à 50€ ou
supérieur à 100 €
SELECT *
FROM Produit
WHERE prixUnitaire < 50 OR prixUnitaire > 100
Restriction : Opérateurs IS NULL
et LIKE
• Commandes en quantité indéterminée (null)
SELECT *
FROM Commande
WHERE quantité IS NULL
• Clients dont le nom commence par B, se termine par B
et contient au moins 3 caractères
SELECT *
FROM Client
WHEREnom LIKE ‘B_%B’
LIKE recherche des chaînes de caractères correspondant à un
patron où :
% : désigne une suite de zéro à n caractères quelconques
_ : désigne un et un seul caractère quelconque
Restriction : Opérateur IN
• Clients dont le nom est Dupont, Durant ou Martin
SELECT *
FROM Client
WHERE nom IN (‘Dupond’, ‘Durant’, ‘Martin’)
• Clients dont le nom n'est pas dans l'ensemble
{Dupont, Durant, Martin}
SELECT *
FROM Client
WHERE nom NOT IN (‘Dupond’, ‘Durant’,
‘Martin’)
Restriction et projection
• Produire les noClient et dateCommande des
Commandes dont la date est postérieure au
01/01/2004
SELECT noClient, dateCommande
FROM Commande
WHERE dateCommande > ’01/01/2004’
noClient dateCommande
100 5/1/2004
Exp. Algébr.
300 25/2/2004 équivalente
400 30/1/2004
{noClient, dateCommande} ( dateCommande>’01/01/2004’ ( Commande))
Produit cartésien
SELECT *
FROM relation, relation
Ex. Produire toutes les combinaisons possibles de Client
et de Commande
SELECT *
FROM Client, Commande
Exp. Algébr.
équivalente
Client X Commande
Jointure
SELECT attribut1 [,attribut2, …]
FROM relation1,relation2
[,relation3,…]
WHERE condition
Exp. Algébr.
équivalente
Cette commande SELECT combine produit
cartésien, restriction et projection
{attribut1, attribut2…} ( condition ( (relation1 x relation2) x relation3)
…)
N.B. Nécessité de préfixer le nom d’un attribut par sa relation
en cas d’ambiguïté
Jointure : exemple de requête
projection
nom,[Link]…
ex. Liste des commandes avec
le nom du client
restriction
[Link] =
[Link]
SELECT nom, [Link],
noProduit,dateCommande,quantité
FROM Commande, Client x
WHERE [Link] =
[Link]
Commande Client
Arbre algébrique canonique
correspondant à l’expression SQL
(SGBDR)
Jointure : autre exemple de
requête
ex. Produitscommandés en quantité [Link],libellé…
supérieure à 100 et dont le prix
dépasse 1000 €. Afficher les numéros
de produit, leur libellé , leur prix quantité > 100 AND
prixUnitaire >1000
unitaire ainsi que la date de la
AND
commande. [Link] =
[Link]
SELECT [Link], libellé,
prixUnitaire, date
FROM Produit, Commande
x
WHERE quantité > 100
AND prixUnitaire >1000 Produit Commande
AND [Link] =
[Link] Arbre algébrique canonique
Jointure : utilisation
d'alias
Utilisation d’alias pour alléger l’écriture d'une requête incluant
des jointures
ex. Liste des commandes avec le nom et le numéro du client
SELECT [Link], nom, date, quantité
FROM Commande C1 , Client C2
[Link] = [Link]
Commande alias C1
Client alias C2
Jointure externe :
OUTER JOIN
Trouver les information au sujet des Clients et de leurs
commandes en incluant les clients n’ayant pas passé de
commande
Client = Commande
SELECT *
FROM Client NATURAL LEFT OUTER JOIN Commande
Autre syntaxe spécifique à Oracle : + après une colonne pour inclure
NULL
SELECT *
FROM Client, Commande
WHERE [Link]=
[Link] (+)
Opérations ensemblistes
(UNION, INTERSECT, EXCEPT)
• Trouver les noms et prénoms des employés qui sont aussi des passagers
Employé Passager
noEmployé nomEmp prénomEmp noPassager nomPass prénomPass
10 Henry John 4 Harry Peter
15 Conrad James 78 Conrad James
35 Jenqua Jessica 9 Land Robert
46 Leconte Jean 466 Leconte Jean
(SELECT nomEmp as nom, prénomEmp as prénom
FROM Employé) nom prénom
INTERSECT
(SELECT nomPass as nom, prénomPass as prénom Conrad James
FROM Passager) Leconte Jean
Expression de calcul sur les colonnes
dans la liste de projection
• Liste des noArticle avec le prixUnitaire avant et après
inclusion d’une taxe de 15%.
SELECT noArticle, prixUnitaire,
prixUnitaire*1.15 as prixTTC
FROM Article
noArticle prixUnitaire prixTTC
10 10.99 12.64
20 12.99 14.94
40 25.99 29.89
50 22.99 26.44
60 15.99 18.39
70 10.99 12.64
80 26.99 31.04
81 25.99 29.89
90 25.99 29.89
95 15.99 18.39
Expression de calcul sur les colonnes
dans la condition (du WHERE)
• Une condition peut comporter une expression de calcul
ex:Liste des noArticle dont le prix toutes taxes comprises (TTC) dépasse
40€
noArticle
SELECT noArticle 40
FROM Article
50
WHERE prixUnitaire*1.15 > 40
80
81
Une expression peut aussi faire appel à des fonctions 90
ex: Liste des commandes de la journée
SELECT *
FROM Commande
WHERE dateCommande = CURRENT_DATE
Notion de valeur indéfinie (NULL)
• Valeur d’un attribut inconnue ou indéfinie
dénotée NULL
• Seules opérations permises sur la valeur NULL
IS NULL
IS NOT NULL
• Expression arithmétique : Si un opérande est
NULL, le résultat est NULL
• Expression de comparaison : Si un opérande
est NULL, le résultat est NULL (UNKNOWN)
Notion de valeur indéfinie (NULL) : Extension des tables de
vérité traditionnelles
a b a AND b a OR b NOT a
TRUE TRUE TRUE TRUE FALSE
TRUE NULL NULL TRUE FALSE
TRUE FALSE FALSE TRUE FALSE
FALSE TRUE FALSE TRUE TRUE
FALSE NULL FALSE NULL TRUE
FALSE FALSE FALSE FALSE TRUE
NULL TRUE NULL TRUE NULL
NULL NULL NULL NULL NULL
NULL FALSE FALSE NULL NULL
a, b : expressions booléennes (à valeur logique)
Fonctions d’agrégation
Une fonction d'agrégation opère sur un groupe de valeurs d’attributs et produit
une valeur résultat unique (extension de l’algèbre relationnelle)
SELECT fctAgrégation
FROM relation(s) [WHERE condition]
fctAgrégation opère sur les lignes de la relation résultat :
COUNT(*) : retourne le nombre de lignes de la relation résultat
COUNT([distinct]expr) : nombre de valeurs non NULL
(distinctes) de expr
MAX(n) : valeur maximum de n
MIN(n) : valeur minimum de n
SUM(n) : somme des valeurs de n (ignore les valeurs NULL)
AVG(n) : valeur moyenne de n (ignore les valeurs NULL)
Où n est une expression numérique et expr une expression quelconque
Fonctions d’agrégation
Nombre total d’articles et prix unitaire moyen
SELECT COUNT(*) AS nbArticles, nbArticles prixMoyen
AVG (prixUnitaire) AS prixMoyen
FROM Article 15 9.50
Nombre de prixUnitaires non null
SELECT COUNT(prixUnitaire) AS nbPrixNonNull nbPrixNonNull
FROM Article 15
Nombre de prixUnitaires non null différents
nbPrix
SELECT COUNT(distinct prixUnitaire) AS nbPrix
FROM Article 8
Fonctions d’agrégation :
Contraintes d’utilisation
• Une fonction d’agrégation doit être utilisée dans une clause
SELECT sans résultats individuels
SELECT noProduit, max(prixUnitaire) Faux !!
FROM Produit
Requête invalide puisque plusieurs noProduit et un seul maximum.
• Une fonction d’agrégation peut être utilisée dans une sous-requête
sélection de résultats individuels dans la requête englobante
SELECT noProduit, libellé
FROM Produit
WHERE prixUnitaire =
(SELECT max (prixUnitaire)
FROM Produit)
Partition de relations (GROUP BY)
ex. Nombre de produits commandés par client
SELECT noClient, COUNT(*) AS totalProduits
FROM Commande
GROUP BY noClient
1) Les commandes sont groupées par numéro de client
2) pour chaque groupe, afficher le numéro du client concerné
par le groupe et le nombre de commandes.
N.B. : chaque expression du SELECT doit avoir une valeur
unique par groupe.
Partition de relations (GROUP
BY)
ex. Nombre de produits commandés par client
SELECT noClient, COUNT(*) AS totalProduits
FROM Commande
GROUP BY noClient
noProduit dateComma noClien
nde t
noClie totalProdui
4 5/1/2003 10
nt ts
5 5/1/2003 10
10 2
20 14/5/2003 12
12 3
28 15/8/2003 12
15 1
68 15/8/2003 12
59 20/9/2003 15
Partition de relations
(GROUP BY)
ex. Quantité totale de produits commandés par client en dehors du
produit F565
SELECT noClient, SUM(quantité)
FROM Commande
WHERE noProduit <> ‘F565’
GROUP BY noClient
1) Les tuples de Commande ne vérifiant pas la condition sont exclus
2) Les commandes restantes sont groupées par numéro de client
3) pour chaque groupe, afficher le numéro du client concerné par le
groupe et la somme des quantités.
Une clause HAVING permet de restreindre les groupes
Partition de relations (GROUP BY)
ex. Quantité moyenne commandée par produit pour les produits
ayant fait l’objet de plus de 3 commandes. Ignorer les
commandes concernant le client C47.
SELECT noProduit, AVG(quantité)
FROM Commande
WHERE noClient != ‘C47’
GROUP BY noProduit
HAVING COUNT(*) > 3
1) Les tuples de Commande ne vérifiant pas la condition WHERE sont
exclus
2) Les commandes restantes sont groupées par numéro de produit
3) pour chaque groupe, compter le nombre d’éléments et éliminer les
groupes à moins de 3 éléments.
4) pour les groupes restants, afficher le numéro de produit et la quantité
moyenne.
N.B. : La clause HAVING ne s’utilise qu’avec un GROUP BY.
Partition de relations (GROUP BY)
Il est possible de partitionner sur plusieurs colonnes (attributs)
ex. Nombre de produits commandés par client et par date
SELECT noClient, dateCommmande,
COUNT(noProduit) AS nbProduits
FROM Commande
GROUP BY noClient, dateCommande
noProdu dateComma noClien
it nde t noClie dateComma nbProdui
nt nde ts
4 5/1/2003 10
10 5/1/2003 2
5 5/1/2003 10
12 14/5/2003 1
20 14/5/2003 12
12 15/8/2003 2
28 15/8/2003 12
15 20/9/2003 1
68 15/8/2003 12
59 20/9/2003 15
Tri du résultat d’une requête
(ORDER BY)
• Possibilité de trier les résultats d’une requête par rapport à
une ou plusieurs de ses colonnes
SELECT colonne(s)
FROM relation(s) [WHERE condition]
ORDER BY colonne [ASC|DESC], [,colonne ASC|DESC]..]
Où
ASC : ordre ascendant (par défaut)
DESC : ordre descendant
ex. liste des commandes par ordre croissant du numéro de client et
par ordre chronologique inverse de la date
SELECT *
FROM Commande
ORDER BY noClient, dateCommande desc
Requêtes imbriquées
• Le résultat d’une requête peut être utilisé dans
une condition de la clause WHERE d’une
commande SELECT
- sous-requête /requête imbriquée
SELECT colonne(s)
FROM relation(s)
WHERE
expression [NOT]IN (sous-requête) |
{EXISTS | NOT EXISTS} (sous-requête)
Requêtes imbriquées : opérateur
IN / NOT IN (test d’appartenance
à un ensemble)
Nom des clients ayant passé commande le
24/10/2000
SELECT nom
FROM Client
WHERE noClient IN (SELECT noClient
FROM Commande
WHERE dateCommande=’24/10/2000’)
Requêtes imbriquées : opérateur
EXISTS / NOT EXISTS (test
d’ensemble vide)
Clients ayant passé au moins une commande
SELECT *
FROM Client C1
WHERE EXISTS (SELECT *
FROM Commande C2
WHERE [Link]=[Link])
Clients n’ayant passé aucune commande
SELECT *
FROM Client C1
WHERE NOT EXISTS (SELECT *
FROM Commande C2
WHERE [Link]=[Link])
Quelques règles de
nommage …
om d’une colonne dans la relation résultat :
- par défaut, nom de l’attribut ou de l’expression dont
elle est issue : noProdui prixUnitair
libellé
t e
SELECT * FROM Produit
AVG(prixUnitaire)
SELECT AVG(prixUnitaire)FROM Produit
- renommage possible :
Numéro produit
SELECT noProduit AS "Numéro produit"
FROM Produit