Transcript Document

Requêtes SQL
par l’exemple
Frédéric Gava (MCF)
[email protected]
LACL, bâtiment P2 du CMC, bureau 223
Université de Paris XII Val-de-Marne
61 avenue du Général de Gaulle
94010 Créteil cedex
Généralités
Cette présentation ne se veut pas exhaustif
SQL comprend de nombreux autres choses non décrites ici
(manque temps ou trop technique)
Pour plus de détails, je vous conseil les références suivantes
« SQL pour les nuls »
cours sur internet
livres
SQL comprend
Un langage de définition des données (LDD)
Un langage de manipulation des données (LMD)
SQL = « Structured Query Language »
Il existe une version graphique des requêtes SQL sous Microsoft
Access. N’étant pas « standard », elle ne sera pas présenté ici.
Vous trouverez néanmoins un cours par un anonyme sur ma page
2/47
web
Le langage de
définition des données
Les tables et types
Nous souhaitons créer les tables suivantes :
Client(NumCl, NomCl, AdresseCl, CodePost, Ville, Tél)
Article(NumAr, Designation, PrixUnité, QteStock)
Vendeur(NomVd, Qualité, Salaire, Commission)
Commande(NumCom, NumCl, NumVd, DateCom)
LigneCom(NumCom,NumLg, NumAr, QteCom)
Nous utiliserons les types de données suivants :
CHAR(N) = chaîne de caractère de longueur maxi N
INTEGER = entier
NUMBER(N) = nombre de longueur (en chiffre) maxi N
FLOAT = réel (0.934 ou 14.3434 etc.)
DATE = une date (format dépend du SGBD et du pays)
NULL = valeur nulle
4/47
Retour sur les clés
Une clé primaire est une clé d’une table qui est composée d’une
ou plusieurs (dit clé composée) attributs (colonnes) de la table et
est utilisée pour identifier chaque ligne de manière unique. La
clé primaire ne doit pas contenir de colonne valeur NULL.
Une clé unique possède les mêmes propriétés qu’une clé
primaire à l’exception qu’une colonne définie comme clé unique
peut être NULL. Elle contrôle l’unicité de la valeur
Une clé étrangère représente une relation entre les tables. Elle
est composée d’une ou plusieurs attributs (colonnes) dont les
valeurs dépendent de clés primaires (ou uniques) d’autres tables
On parle d’intégrité référentielle quand on veut signifier que les
relations représentées par les clés sont maintenues. Elle assure la
cohérence des données.
5/47
Création de tables
CREATE TABLE Client (
NumCl INTEGER,
NomCl CHAR(20),
AdresseCL CHAR(20),
CodePost NUMBER(5),
Ville CHAR(20),
Tél NUMBER(8))
Création simple car pas de contraintes d’intégrités (CI)
Nous rappelons qu’une CI est définie pour contrôler la validité des
valeurs à insérer
Remarque : nous écrirons en rouge et majuscule les mots clés en
SQL. Cela n’est pas obligatoire et ne sert que pour ces transparents.
CREATE TABLE permet donc de créer un nouvelle table en
6/47
explicitant la liste des attributs de la table
Les contraintes
Les contraintes les plus importantes sont les suivantes :
UNIQUE = interdit d’avoir deux lignes ayant la même valeur pour
cette colonne et les valeurs NULL sont autorisées
PRIMARY KEY = spécifie que le colonne est utilisée comme clé
primaire. Cela a donc le même effet que UNIQUE mais les valeurs
ne peuvent être NULL
CHECK = est un mot associé à une condition qui doit être vérifiée
pour chaque valeur insérée
REFERENCES = décrit une contraintes référentielle (clé
étrangère) par rapport à une clé primaire d’une autre table
PRIMARY KEY = UNIQUE NOT NULL
En fait, CONSTRAINT est optionnel. Par contre, il permet de
donner un nom à chaque contrainte qui sera sauvegardé dans le
dictionnaire des données : CONSTRAINT nom NOT NULL
Nous pouvons donc définir les tables…
7/47
Exemples (1)
CREATE TABLE Client (
NumCl NUMBER CONSTRAINT PRIMARY KEY,
NomCl CHAR(20) CONSTRAINT NOT NULL,
CodePost NUMBER(5) CHECK (CodePost BETWEEN 100000
AND 95999),
etc.)
BETWEEN permet d’expliciter les bornes de la valeur
DEFAULT permet de donner une valeur par défaut qui n’est pas
NULL quand on oublie d’ajouter une valeur pour cette attribut
CREATE TABLE Article (
NumAr NUMBER PRIMARY KEY,
Designation CHAR(40) CONSTRAINT ades NOT NULL,
PrixUnité NUMBER(7),
8/47
QteStock NUMBER DEFAULT 0)
Exemples (2)
CREATE TABLE Vendeur (
NumVd NUMBER PRIMARY KEY,
NomVd, CHAR(20) NOT NULL,
Qualité CHAR(2),
Salaire NUMBER(8) NOT NULL,
Com NUMBER(2) CHECK (Com BETWEEN 0 AND 100))
CREATE TABLE Commande (
NumCom NUMBER,
NumCl NUMBER REFERENCES Client(NumClient),
NumVd NUMBER REFERENCES Vendeur(NumVd)
DateCom DATE)
9/47
Exemples (3)
On peut aussi ajouter quelle est la clé (notamment quand
elle est multiple) à la fin de la création. Exemple :
CREATE TABLE LigneComm (
NumCom INTEGER,
Nligne INTEGER,
NumArticle NUMBER CONSTRAINT nlaid REFERENCE
Article(NumArticle),
QteCom NUMBER NOT NULL CHECK (QteCom>0),
PRIMARY KEY (NumCom, Nligne))
10/47
Retour sur les « vues »
Une vue est une perception logique sur les données
d’une ou plusieurs tables (ou vues). Elle est définie à
partir d’une requête d’interrogation du LDD et
hérite les mêmes caractéristique que les objets
auquel elle se réfère (type, contraintes…)
Les vues sont définies pour fournir un niveau de
sécurité supplémentaire sur les données d’une table
(un avocat ne lit pas les données d’un procureur…)
Une vue ne nécessite aucune allocation mémoire
pour obtenir les données, contrairement à une table.
Sa consommation en ressources consiste seulement
en sa définition dans le dictionnaire des données
11/47
Les vues en SQL
CREATE VIEW ClientParis (NumCl, NomCl,
TélCl) AS SELECT NumCl, NomCl, TélCl
FROM Client WHERE ville="Paris"
Suppression d’une vue : DROP VIEW ClientParis
COMMIT pour que le SGBD prennent en compte
les
modification
physiquement
(vues
et
modifications des tables de la suite de ce cours)
12/47
Le langage de
manipulation des données
Insertion de valeurs
Il faut maintenant insérer des données dans les tables
Forme général : INSERT INTO Table VALUES (données)
Exemple :
INSERT INTO Client VALUES (1,"Delacroix", "45, rue Royal",
75008, "Paris", 43151678)
Autre forme : INSERT INTO Client (liste des champs)
VALUES (valeurs dans l’ordre des champs données)
Exemple :
INSERT INTO Client (NumCl, NomCl, AdresseCl, CodePost,
Ville, Tél) VALUES (1,"Delacroix", "45, rue Royal", 75008,
"Paris", 43151678)
14/47
Manipulation de la BD (1)
Effacer une table de la base :
Forme général : DROP TABLE nomtable
Exemple : DROP TABLE Client
Modifier une table :
Première forme, ajouter un attribut : ALTER TABLE Client ADD
Remarque CHAR(80)
Seconde forme, modifier le type d’un attribut : ALTER TABLE
Client MODIFY Ville CHAR(30)
Remarques :
on peut faire plusieurs modifications à la fois.
on ne peut modifier une table que pour l’augmenter !
15/47
Manipulation de la BD (2)
Mettre à jour une table. On utilise la clé pour choisir la
bonne ligne de la table. Exemple :
UPDATE Client SET adresse="74 Avenue De Gaulle",
ville="Grenoble",
CodePost="38500",
Tél=76581011
WHERE NumCl=1
UPDATE Article SET Prix=Prix*0.3
Effacer toutes les données d’une table. Exemple :
Suppression physique (plus sur les disques)
DELETE FROM Client
DELETE FROM Client WHERE ville="Paris"
Suppression logique (permet de garder l’espace alloué pour
le profit de la même table) :
TRUNCATE TABLE Article REUSE STORAGE
16/47
Requêtes de données
Les requêtes SQL
Maintenant que nous avons définit les tables et entrées les
données, il est naturel de vouloir extraire certaines de ses
données
Pour cela nous allons voir comment écrire des requêtes
SQL
Cela va nous permettre de :
lire complètement une table
lire une partie d’une table
faire des jointures de différentes tables
tables complètes
ou qu’avec des sous-parties
faire des sous-requêtes pour créer des tables temporaires et en
faire des jointures
etc.
18/47
Les requêtes simples
Afficher toutes les données de la table Client :
SELECT * FROM Client
De Paris : SELECT * FROM Client WHERE Ville="Paris"
SELECT * "Client Parisiens" FROM Client WHERE Ville ="Paris"
Lister que les noms et adresses des clients :
SELECT NomCl, AdrCl FROM Client
De Paris ou Rouen : SELECT NomCl, AdrCl FROM Client WHERE
(Ville="Paris") OR (Ville="Rouen")
Lister tout les articles dont le prix unitaire est > 150 et dont la
quantité est < 100
SELECT * FROM Article WHERE (Prix > 150) AND (Qté<100)
Lister toutes les commandes enregistrés après le 2 janvier 1995 :
SELECT * FROM Commande WHERE DateEnr>’02-01-1995’
19/47
Quelques prédicats (1)
Le prédicat BETWEEN AND, exemples :
Lister tout les articles dont les prix sont compris entre 150 et 200 euros :
SELECT * FROM Article WHERE Prix BETWEEN 150 AND 200
Lister tout les consommables non enregistrés entre les dates du 1
janvier 1995 et du 31 mars 1996 : SELECT * FROM Consommable
WHERE DateCom NOT BETWEEN ’01-01-1995’ AND ’31-031996’
Remarque : On peut écrire une autre requête du premier problème mais
celle-ci sera beaucoup moins efficace car elle chargera plusieurs fois la
table en mémoire : (SELECT * FROM Article WHERE Prix>150)
INTERSECT (SELECT * FROM Article WHERE Prix<200)
Le prédicat IN, exemples :
Lister tout les clients des villes de Paris, Rouen, Créteil : SELECT *
FROM Client WHERE Ville IN (Paris, Rouen, Créteil)
Lister tout les articles dont le prix unitaire est 5, 11, 17, 23, 31, 37 :
SELECT * FROM ARTICLE WHERE Prix IN (5, 11, 17, 23, 31, 37)
20/47
Quelques prédicats (2)
Le prédicat LIKE
Lister tout les clients dont le nom se termine par "nd" : SELECT *
FROM Client WHERE NomCl LIKE ‘%nd’
Lister tout les article dont la désignation est de 7 caractères,
commence par ‘N’ et se termine par ‘d’ : SELECT * FROM
Article WHERE Designation LIKE ‘N-----d’
Le prédicat NULL
Lister tout les articles dont la désignation n’a pas été saisie (est
nulle) : SELECT * FROM Article WHERE Designation IS
NULL
21/47
Retour sur la jointure (1)
Produit cartésien :
concaténation de toutes les lignes de la première table avec toutes
les lignes de la seconde table.
Table 1 Table 2
Produit
Exemple
a
x
a,x
b
y
c
a,y
b,x
b,y
c,x
Jointure :
c,y
lien entre 2 tables disposant d’au moins une colonne commune
(sémantiquement). On associe a chaque ligne de la première table
toutes les lignes de la seconde table
Exemple
Table 1
Table 2
Jointure
a1,b1
b1,c1
a1,b1,c1
a2,b1
b2,c2
a2,b1,c1
a3,b2
b3,c3
a3,b2,c2
22/47
Retour sur la jointure (2)
Une jointure est un lien entre 2 tables disposant
d’une
ou
plusieurs
colonnes
commune
sémantiquement
L’opération de jointure consistera à créer une table
temporaire composé des lignes satisfaisant la
condition de jointure
Par exemple, pour connaître les clients qui ont
passé au moins une commande, on est amené à
utiliser le lien entre les tables Client et Commandes
puis d’en extraire seulement les lignes satisfaisant la
condition suivante :
Client.NumCl = Commande.NumCl
23/47
Exemples jointure
Equi-jointure :
Lister tout les clients Parisien qui ont passé une commande entre les dates du
premier janvier 1993 et aujourd’hui :
SELECT Client.NumCl, NomCl, AdrCl, Commande.Date FROM Client,
Commande
WHERE
Client.NumCl=Commande.NumCl
AND
Client.Ville="Paris" AND Date BETWEEN ’01-01-1993’ AND SYSDATE
Jointure multiple (equi et theta):
Lister tous les articles qui n’ont pas été commandé entre le premier janvier 1993
et aujourd’hui
SELECT Article.NumArticle, Designation FROM Article, LigneComm,
Commande WHERE Commande.NumCom=LigneComm.NumCom AND
Article.NumArticle<>LigneComm.NumArticle
AND Commande.Date
BETWEEN ’01-01-1993’ AND SYSDATE
On peut faire plus simple et éviter d’écrire toujours le nom de la table
SELECT A.NumArticle, Designation FROM Article A, LigneComm LC,
Commande
C
WHERE
C.NumCom=LC.NumCom
AND
A.NumArticle<>LC.NumArticle AND C.Date BETWEEN ’01-01-1993’
AND SYSDATE
24/47
Jointure externe (1)
Dans les différents types de jointure présentés ci-dessus,
toutes les lignes de la première table qui n’ont pas de lignes
dans la seconde table et qui vérifient la condition de
jointure, ne font pas partie du résultat final.
Si on souhaite faire apparaître cette ligne dans le résultat,
on utilise la jointure externe
Une jointure externe est une jointure qui favorise une table
par rapport à une autre. Aussi, les lignes de la table
dominante seront affiché même si la condition n’est pas
réalisée. Une jointure externe est explicité par l’opérateur
(+) qui est placé dans la clause WHERE d’après le nom de
la table et celui de la colonne subordonné. Comme suit :
WHERE table1.colonne = table2.colonne (+)
WHERE table1.colonne (+) = table2.colonne
25/47
Jointure externe (2)
Exemple : Lister tous les clients parisiens qui ont
passé ou non une ou plusieurs commande entre la
période du 1er janvier et aujourd’hui:
SELECT X.NumClient, Nom, Prénom, adresse,
DateComm,
NumComm
FROM
Client
X,
Commande Y WHERE X.NumClient=Y.NumClient
(+) AND Ville="Paris" AND Date BETWEEN ’01Jan-93" AND SYS_DATE
26/47
Condition de sous-requête (1)
SQL permet de comparer une expression ou une colonne ou
résultat d’une autre requête SELECT. Cette condition est
dite « condition de sous-requête » et les 2 requêtes sont
dites requête imbriqués
Bien-sur, une sous-requête peut faire appel à une autre
sous-requête etc.
Les requêtes imbriqués dans SQL peuvent être utilisés pour
comparer une expression ou une liste d’expression au
résultat d’une autre requête SELECT, déterminer
l’appartenance d’une expression ou l’existence d’une ligne
dans le résultat d’une requête SELECT etc.
27/47
Condition sous-requête (2)
Une condition de sous-requête peut être formulé selon
l’une des possibilité suivante :
WHERE Exp Opérateur_de_comparaison {ALL | ANY |
SOME} (Requête_SELECT)
WHERE Exp [NOT] IN (Requête_SELECT)
WHERE [NOT] EXISTS (Requête_SELECT)
L’évaluation des sous-requêtes peut renvoyer plusieurs
valeurs qui sont interprété comme suit :
ALL : la condition est vrai, si la comparaison est vrai pour
chacune des valeurs retournées (remarque : si l’expression de
condition est de type numérique et si l’opérateur de comparaison
est > alors la sous-requête sera équivalente à l’extraction de la
valeur maximale car si la condition est vrai pour le maximum, elle
est aussi vrai pour toutes les autres…)
28/47
Condition sous-requête (3)
ANY : la condition est vrai si la comparaison est vrai
pour au moins une des valeurs retournée (remarque : si
…. < alors ….. minimal car …. minimum , elle …)
SOME = ANY
IN ; la condition est vrai si la comparaison est vrai pour
une des valeurs retournées par la sous-requête
EXISTS : il est un peu différent des autres opérateurs, il
renvoie un booléen (vrai ou faux) selon le résultat de la
sous-requête. Si l’évaluation de la sous-requête donne
lieu a une ou plusieurs ligne, la valeur retourné est vrai.
Cette valeur sera fausse dans le cas contraire.
29/47
Exemples de sous-requêtes (1)
Lister tous les articles dont la quantité en stock est > à
toutes quantité commandé du même article
SELECT IdArticle, Designation, QtéStock from Article X
WHERE QtéStock > ALL (SELECT QtéComm FROM
ligne_commande Y WHERE X.IdArticle=Y.IdArticle)
SELECT IdArticle, Designation, QtéStock FROM Article X
WHERE QtéStock > (SELECT MAX(QtéCommandé) FROM
ligne_commande Y WHERE X.IdArticle=Y.IdArticle
Lister tous les articles dont la quantité est > a au moins une
quantité commandé au même article
… > ANY (SELECT QtéCommandé …)
ou SELECT Min(QtéCommandé)
30/47
Exemples de sous-requêtes (2)
Lister tous les clients parisien qui ont passé une commande
entre le 1er janvier 93 et aujourd’hui et dont la quantité
commandé est égale à la quantité en stock
SELECT DISTINCT IdClient FROM Commande WHERE
IdClient IN (SELECT IdClient FROM Client WHERE
ville="Paris") AND DateComm BETWEEN "01-Jan-93« AND
SYSDATE)
Mémo technique
(<= ou =) 1 seul valeur
(IN, ALL, ANY) une liste de valeur
EXISTS, un ensemble de valeur
31/47
Requêtes plus complexes
Les fonctions d’agrégat (1)
Ces fonctions sont appliqués sur des groupes de données ;
Ainsi, on peut déterminer le nombre de ligne par table ou
par groupe de données, calculer la somme ou la moyenne ou
déterminer le maximum ou le minimum d’une colonne etc.
Les fonction d’agrégat offert par SQL sont les suivantes :
COUNT(*) : nombre de ligne satisfaisant la requête
SELECT COUNT(*) FROM Clients
SELECT COUNT(NumClient) FROM Clients
COUNT(DISTINCT Colonne) : permet d’éviter les doublon
Calculer le nombre de clients qui ont passé des commandes entre le 01-101999 et aujourd’hui
SELECT COUNT(DISTINCT NumClient) FROM Commandes
WHERE Date BETWEEN ’01-10-1999’ AND SYS_DATE
SUM(DISTINCT Colonne) permet de faire la somme des valeurs
de la colonne donnée et satisfaisant la requête. L’option
33/47
DISTINCT somme les valeurs uniques
Les fonctions d’agrégat (2)
Suite des fonctions d’agrégat possible :
AVG(DISTINCT Colonne) permet de calculer la moyenne
(« AVeraGe » en Anglais) des données satisfaisant la requête.
MAX(DISTINCT Colonne) pour le maximal et MIN(DISTINCT
Colonne) pour le minimal
STDDE(DISTINCT Colonne)
pour
l’écart
type et
VARIANCE(DISTINCT Colonne) pour la variance
En présence de valeurs NULL dans les données, la fonction
COUNT prend en compte ces lignes. Les autres fonctions
ignorent les NULL…
Important : Les fonctions d’agrégat ne peuvent en aucun
cas être ailleurs que suite au SELECT (pas dans la clause
WHERE par exemple)
34/47
Les fonctions d’agrégat (3)
Exemples :
Lister le nombre d’articles, les prix unitaires, maximum,
minimum et moyen des différents articles
SELECT
COUNT(*)
MAX(PrixUnitaire)
MIN(PrixUnitaire) AVG(PrixUnitaire) FROM Article
Calculer le montant total du stock d’article
SELECT SUM(PrixUnitaire*QteEnStock) FROM
Article
35/47
Groupement de données (1)
Avec les fonctions d’agrégats SQL permet de
grouper des lignes de données ayant des valeurs
communes ; ainsi, on peut formuler par exemple,
une requête qui liste le nombre de clients par ville
Cette possibilité est explicité par la clause GROUP
BY
Exemple : Lister le nombre de lignes par commande
Imaginons une table "Client", faire un COUNT(*) sur
cette table reviendrai à compter le nombre de ligne de
cette table (le nombre de clients)
Par contre, COUNT(*) sur les groupes de la table
LigneComm donnerai le nombre de ligne par commande
36/47
Groupement de données (2)
NumClient :
NumCl
1
1
2
3
3
4
1
2
5
3
2
4
COUNT(*)
1
GROUP BY
2
2
COUNT(*)
2
3
3
COUNT(*)
3
4
COUNT(*)
4
5
SELECT
NumClient,
Count(*)
LigneComm GROUP BY NumClient
COUNT(*)
FROM
37/47
Groupement de données (3)
Autre exemple :
Lister le montant de chaque commande
SELECT NumCom, SUM(PrixUnit*QtéCommandé)
FROM Article Y, LigneComm X WHERE
X.NumArticle=Y.NumArticle GROUP BY NumCom
38/47
Trier les données
Lister tous les clients par ordre de ville et de nom de clients
SELECT * FROM Client ORDER BY Ville, Nom
ORDER BY Colonne Position
avec Position=ASC pour ordre ascendant et DESC pour ordre
décroissant
SELECT * FROM Client ORDER BY Ville DESC, Nom ASC
Lister toutes les commandes par ordre décroissant des
numéros de commande et de ligne
SELECT Num_Comm, Num_Ligne FROM Ligne_Comm
ORDER BY 1 DESC, 2 DESC
Remarque : il n’est pas très utile (pas optimisé) d’utiliser
ORDER BY dans une sous-requête vue que l’on veut juste
y prendre un ensemble de données
39/47
Fusion de requêtes
Il est possible d’"unifier" des requêtes avec des opérations
ensemblistes :
UNION :
INTERSECT :
Exemple stupide
Donnez la liste des clients vivant soit à Paris soit à Créteil
(SELECT * FROM Client WHERE Client.Ville="Paris") UNION (SELECT
* FROM Client WHERE Client.Ville="Créteil")
SELECT * FROM Client X WHERE X.Ville="Paris" OR X.Ville="Créteil"
La clause HAVING (1)
Supposons que l’utilisateur désire lister les commandes
dont le nombre de ligne est > 5. Il se doit donc utiliser la
condition COUNT (Num_Ligne)>5 avec un regroupement
sur la commande Num_Comm. Nous aurions la requête
suivante :
SELECT Num_Comm, COUNT(*) FROM Ligne_Comm
WHERE COUNT(Num_Ligne)>5 GROUP BY Num_Comm
Pourtant, cette requête ne peut donné le résultat escompté
car elle contient une erreur de syntaxe : on rappel que la
condition de la clause WHERE ne peut en aucun cas inclure
des fonctions d’agrégats
La clause HAVING offert par SQL permet une restriction
sur un groupe de ligne. Elle est liée à la clause GROUP BY
41/47
La clause HAVING (2)
HAVING Condition : la condition peut être une ou
plusieurs conditions liées avec les opérations logique AND
et/ou OR. Une condition sert à comparer une fonction
d’agrégat du groupe à une autre fonction d’agrégat ou une
constante.
Exemples :
Requête précédente :
SELECT Num_Com, COUNT(*) FROM Ligne_Comm
GROUP BY Num_Comm HAVING COUNT(Num_Ligne)>5
Lister la somme de toutes les commandes dont le nombre de ligne
est > 4
SELECT NumCom, SUM(PrixUnité*Qté_Comm) FROM
LigneComm X, Article Y WHERE X.NumArticle=Y.NumArticle
GROUP BY NumComm HAVING COUNT(Num_Ligne)>4
42/47
La clause HAVING (3)
La clause HAVING est une condition de regroupement ;
Exemple :
SELECT NumCom, SUM(PrixUnité*Qté_Comm) FROM
LigneComm X, Article Y WHERE X.NumArticle=Y.NumArticle
GROUP
BY
NumComm
HAVING
SUM(PrixUnité*Qté_Comm)>5
Voici donc l’ordre d’écriture dans une requête :
SELECT … … …
WHERE … … …
GROUP BY … … …
HAVING … … …
43/47
Exemples examen (1)
Nous utiliserons la base de donnée suivante :
Magazin(Num_Mag, Adr, Gu)
Article(Num_Art, Nom, Poids, Couleur, Prix_Achat,
Prix_Vente, Num_Fournisseur)
Clients(Num_Client, NomCl, Prénom, Pays, AdrCl)
Ventes(Num_Client, Num_Mag, Num_Art, Date,
Quantité, PrixRéel)
Donnée en SQL les requêtes suivantes :
44/47
Exemples examen (2)
Calculer par article, la remise total accordé par rapport aux
prix de vente catalogué pour une semaine.
On va construire au fur et à mesure la requête (pas à l’examen)
SELECT Nom FROM Ventes, Articles WHERE
Ventes.Num_Art=Articles.Num_Art AND Date BETWEEN
’01-10-1999’ AND SYS_DATE
SELECT Nom, SUM(Qté*(Prix_Vente-PrixRéel) FROM
Ventes, Articles WHERE Date BETWEEN ’01-10-1999’ AND
SYS_DATE
AND
Ventes.Num_Art=Articles.Num_Art
GROUP BY Num_Art
Rechercher la couleur des articles dont le prix de vente
moyen des articles de la couleur est > 100
SELECT Couleur, AVG(Prix_Vente) FROM Articles GROUP
45/47
BY Couleur HAVING AVG(Prix_Vente)>100
Optimisation de requêtes
Lorsque l’on écrit une requête, il faut penser au
conséquences calculatoires (en terme de calcul et de
ressources mémoires)
En effet, chaque sous-requêtes, chaque jointure, chaque
ORDER BY, chaque GROUP BY, chaque HAVING a pour
conséquence de créer une table temporaire : si le nombre de
données est très important, cela prend nécessairement du
temps (le SGBD n’est pas magique)
Il faut donc que vous pensiez au coût de vos requêtes :
entre 2 requêtes, pour un même résultats, laquelle des 2
créera le moins de tables temporaires
Exemple : regardons nos précédentes requêtes
En cas de doute, demander à un gentil informaticien ;-)
46/47
À la semaine prochaine !