30 mars 2013
29 mars 2013
15 mars 2013
Tests de performances avec OpenSTA (1)
De nombreuses années d'expériences m'ont amené au constat suivant: une forte proportion des applications réceptionnées en production rencontre des problèmes de performance dans les premiers mois qui suivent leur déploiement. Les coûts induits annuellement se chiffrent en centaines de jours-homme. Les tests constituent une approche pragmatique permettant, pour une application, en plate-forme:
- De Certifier sa disponibilité;
- De Valider sa fiabilité ;
- D’Etablir de façon aussi réaliste que possible, ses performances;
- De Dimensionner l’Infrastructure requise pour sa mise en production.
Rappelons à cet effet que :
-L’audit transactionnel vise à auditer les applications au niveau des transactions (Analyse du flux transactionnel, bande passante), à mesurer les performances applicatives sur le réseau.
-Les tests de montée en charge, visent à reproduire une activité de production, à mesurer les temps de réponse du côté utilisateur, à mesurer l’utilisation des ressources systèmes des serveurs.
Les tests de montées en charge obéissent à une démarche qui peut être schématisée par la figure ci-dessous. Dans le cadre de cette démarche, l’Outil OpenSTA, qui ne s’applique qu’aux applications utilisant le protocole http, couvre les phases 3 (Elaboration des scripts de montée en charge) et 4 (Exécution de la
montée en charge).
Examinons comment installer et mettre en œuvre l’outil OpenSTA, dans le contexte d’un processus de test de montée en charge.
Les tests de charge permettent classiquement :
- La mesure des temps de réponse Utilisateurs ;
- La mesure de la consommation des ressources systèmes (réseau et serveurs) ;
- Le calibrage du Système de façon optimale ;
- L’optimisation des applications.
Une plate-forme de test va comporter :
- Un poste utilisé comme conducteur, pour le pilotage des tests en temps réel et le recueil des mesures, et aussi comme genérateur de script (ScriptModeler) ;
- Un ou plusieurs postes utilisés comme injecteur (simulation des utilisateurs) .
Le poste utilisé comme conducteur héberge un repository comportant les scripts, la logique d’enchaînement des scripts, le recueil des mesures. La logique d’enchaînement des scripts, associé à un nombre d’utilisateurs, et à leur comportement constitue un test. Dans OpenSTA, le conducteur est appelé TestCommander. Il est recommandé, pour des tests impliquant de nombreux utilisateurs, d’utiliser un repository unique, centralisé sur le conducteur (RepositoryHost). Ceci permet d’effectuer des test distribués (injection du scénario à partir de plusieurs postes).
Le conducteur capture un scénario et génère le script correspondant. Ce script, flux http, peut être rejoué (injecté) par un poste injecteur.
L’installation est à effectuer à partir des "sources" windows 32 bit, se présentant sous la forme d’un exécutable msi :
Installation
Double-cliquez sur le fichier d’installation .msi. Vous devez obtenir une image-écran du type :
Actionnez Next.
Le processus d’installation se déroule sans intervention particulière jusqu’à l’affichage de la fenêtre de fin d’installation.
Actionnez le bouton Close .
Redémarrez l’ordinateur.
OpenSTA doit être accessible à partir du menu Démarrer de Windows :
Anatomie de l’installation
OpenSTA a installé une arborescence comprenant les sous-répertoires suivants:
Répertoire
|
Contenu
|
BaseUI
|
Interfaces utilisateur : Script Modeler, OpenSTAT Commander, etc
|
Common
|
Modules commun OpenSTA
|
Engines
|
Moniteurs/injecteurs de test
|
Plugins
|
Modules utilises pour la présentation des résultants de tests
|
Repository
|
Référentiel des scripts, des tests et des résultants de tests
|
Server
|
Serveur CORBA mis en oeuvre par les différents modules
|
Configuration de base
Dans un environnement où plusieurs machines vont être utilisées pour produire de l a charge, openSTA devra être installé sur chacune des machines. Celles-ci doivent pouvoir communiquer. Le répertoire Repository de la machine principale sera utilisé comme référentiel central. La machine principale est celle à partir laquelle les tests seront lancés sur les autres machines
A partir du menu Démarrer, sélectionnez l’option OpenSTA Name Server. Une icône OPenSTA NameServer doit apparaître dans la barre des tâche de Windows :
Sélectionnez l’option configure :
Le champ Repository path est lui, configuré sur OpenSTA Commander, à partir du menu Tools, option Repository path. Le Repository est toujours un répertoire sur la machine courante. Pour des environnements destinés à supporter des tests importants, je recommande de placer ce répertoire à l’extérieur de l’arborescence d’installation d’OpenSTA
Le Repository défini est affiché dans le panneau gauche :
Configuration d'un script
Lancez OpenSTA Commander à partir du menu Démarrer.
La configuration d’un script s’effectue en créant le script à partir du dossier Scripts et en double cliquant sur le script concerné, de façon à faire apparaître la fenêtre de gestion de scripts : Script Modeler.
Gateway
Dans cette option, vous allez configurer le module de capture.
A partir du menu Options/Gateway, sélectionnez l’option « console » pour visualiser les actions exécutées par OpenSTA.
Selectionnez « Local », et cochez les cases « Automatic cookie generation » et « Page Timers »
Vérifiez que la machine sur laquelle s’exécute OpenSTA Commander ne comporte pas d’application utilisant les ports 3000 et 81 (Apache par exemple). Pour vérification, ouvrez une fenêtre de commandes DOS et entrez la commande suivante :
Netstat –an
Si l’un de ces ports est déjà utilisé (colonne « Adresse locale), vous devrez modifier les paramètres Administration Port et/ou Port du panneau Capture.
Le navigateur
A partir du menu Options/Browser, sélectionnez le navigateur mis en œuvre sur le poste de capture.
Paramétrage du navigateur
Si vous travaillez dans un environnement impliquant un proxy (Intranet d'Entreprise par exemple) , vous devrez, si votre profil le permet, ajuster la configuration de votre navigateur (Firefox, IE, etc...) , autrement, vous obtiendrez le message suivant lors de la demande de capture :
Lancez internet Explorer (ou tout autre navigateur), puis, dans le menu « Outils », choisissez « Options
Internet ».
Dans l’onglet connexion, actionnez le bouton
« Paramètres réseau ».
Vous devez obtenir une fenêtre du genre :
Copiez l’adresse du script de configuration automatique et
sauvez-la de façon à pouvoir la
restaurer après les captures de scénario de test.
Décochez la case intitulée « Utiliser un script de configuration
automatique ».
Validez l’ensemble, puis fermez le navigateur.
Construction d'un test
Rappel du positionnement de OpenSTA
Dans le processus des tests, OpenSTA se
positionne :
- Dans la capture des scénarii et
l’élaboration des scripts ;
- Dans l’exécution des scripts.
Capture des scénarii et génération des scripts
En cliquant sur le bouton droit de la souris, on peut
aussi renommer, supprimer ou décrire un script.
Double-cliquez sur le nouveau script pour
obtenir la fenêtre du Script Modeler.
Les principaux boutons de cette image-écran sont les suivants :
Capture du scénario
Re-jeu du scénario
Arrêt de la capture
Pause de la capture
Actionnez le bouton rouge (« Record »). 
Vous
devez obtenir un
affichage console du genre :
Puis
la fenêtre à partir de laquelle vous pouvez saisir votre URL
d’un scénario de test.
Sur l’espace de travail OpenSta ScriptModeler,
vous devez obtenir un écran du type de celui-ci.
Celui-ci comporte un volet présentant le texte du
script généré (volet gauche).
Actionnez
le bouton
pour arrêter
la capture.
pour arrêter
la capture.Pour rejouer le script à titre d’essai, actionnez le bouton
Important : Un script rejoué à un instant donné
doit correspondre à la version du logiciel applicatif qui a donné lieu à sa
génération. Entre deux versions, l’applicatif peut, dans une transaction,
envoyer des paramètres de signification différente. Exemple, un numéro de session suivi d’un user Id et
d’un mot de passe dans une transaction ;
Un numéro de session suivi d’une option de menu et d’un index d’une
liste de choix dans une autre transaction. Si dans une version ultérieure
l’application envoie deux index de listes de choix, le fonctionnement du script
ne sera pas satisfaisant.
La suite...
En examinant un script généré, on constate que celui-ci fait intervenir, dans les requêtes GET/POST, des constantes (identification de session par exemple), et des variables contenant des constantes (UserID, password, …) .
Rejouer le script tel quel ne serait pas
réaliste : Si l’on voudrait simuler 5 utilisateurs, le script doit pouvoir
envoyer 5 login différents au serveur ; d’autres valeurs envoyées au
serveur doivent varier en fonction des utilisateurs, par exemple.
La « variabilisation » intervient à
ce stade pour rendre le script dynamique. C'est l'objet de l'article consacré à la "variabilisation".
11 mars 2013
Premiers pas avec SQL
BREF SURVOL
Extraire des données d'une table
Utilisez l’instruction SELECT pour extraire un ensemble de données d’une table SQL.
L’instruction SELECT désigne la table à partir de laquelle les données doivent être extraites, et précise quelles colonnes sont concernées.
Pour extraire les salariés de la table EMP, avec leur maricule, nom, département et niveau d’études, l’instruction SELECT sera la suivante:
SELECT EMPNO, ENAME, EDLVEL, DEPTNO FROM PERSON/EMP
Le résultat obtenu pourrait être celui-ci:
EMPNO
|
ENAME
|
EDLVEL
|
DEPTNO
|
1010
|
ELBAZE
|
18
|
A00
|
2020
|
HASS
|
18
|
B01
|
3030
|
SALLY
|
10
|
C01
|
7070
|
GEYER
|
16
|
E01
|
5050
|
STERN
|
12
|
D11
|
6060
|
REGIS
|
16
|
D21
|
8080
|
DARBOY
|
14
|
E11
|
9090
|
SPENCER
|
15
|
E21
|
9101
|
GOUNOT
|
12
|
E21
|
9102
|
LEE
|
10
|
E21
|
9103
|
MEHTA
|
12
|
E21
|
9104
|
DANDIN
|
10
|
E11
|
9105
|
SMITH
|
12
|
D11
|
9001
|
LUCCINO
|
16
|
A00
|
9002
|
DURAND
|
10
|
A00
|
1. Une clause SELECT (SELECT EMPNO, ENAME, EDLVEL, DEPTNO ). Celle-ci précise quelles colonnes prendre.
2. Une clause FROM (FROM PERSON/EMP) spécifiant la table qui contient les colonnes indiquées.
L’ordre des colonnes n’a aucune importance dans cette instruction.
La clause WHERE
Pour obtenir l’ensemble des analystes de la table EMP, une clause WHERE peut être associée à la clause SELECT et donner ceci:
SELECT ENAME, JOB, DEPTNO
FROM PERSON/EMP
WHERE JOB = ‘ANALYST’
L’ensemble obtenu pourra être le suivant:
ENAME JOB DEPTNO
MITANA ANALYST C01
MICOLE ANALYST C01
Insertion de lignes dans une table SQL
L’exemple ci-dessous utilise l’instruction INSERT pour insérer une nouvelle ligne dans la table des départements.
INSERT INTO DEMO/DEPT ( DEPTNO, DNAME, MGRNO, REPDPT, LOC)
VALUES ( ‘X10’, ‘VOYAGES’, ‘1010’, ‘D21’, ‘PARIS’)
Dans l’instruction INSERT, on spécifie la table concernée, la liste des colonnes, ensuite, la liste des valeurs associées.
Mise à jour d’une ligne dans une table
La mise à jour d’une ligne consiste à changer la valeur d’une ou plusieurs colonnes d’une ou plusieurs lignes d’une table. L’instruction SQL utilisée à cet effet est l’instruction UPDATE.
Prenons le département ‘X10’ et changeons son responsable ainsi que son département de rattachement:
UPDATE PERSON/DEPT
SET MGRNO = ‘8080’, REPDPT = ‘E11’
WHERE DEPTNO = ‘X10’
La clause SET indique les colonnes dont il faut changer la valeur, et pour chacune d’elles, la nouvelle valeur.
La clause WHERE quant à elle précise quelle est la ligne concernée par cette mise à jour.
Suppression d’une ligne dans une table SQL
Pour supprimer le département 'X10', utilisons l’instruction DELETE.
DELETE FROM PERSON/DEPT
WHERE DEPTNO = ‘X10’
La clause FROM spécifie la table sur laquelle porte la suppression. La clause WHERE précise quelle ligne doit être supprimée.
Création d’une table SQL
L’instruction CREATE TABLE permet de créer une table dans une collection SQL ou une bibliothèque. L’instruction ci-dessous crée une table des grades dans la collection PERSON.
CREATE TABLE PERSON/GRADE ( GRADNO SMALLINT,
LOSAL DEC(9, 2),
HISAL DEC(9, 2) )
Dans l’instruction CREATE TABLE, on précise le nom qualifié de la table, et les différentes colonnes. Pour chaque colonne, on spécifie la nature de son contenu. GRADNO est un entier court, LOSAL et HISAL sont des valeurs numériques en format condensé de 9 chiffres dont 2 décimales.
Les requêtes sur les colonnes
L’instruction SELECT
Une requête permet d’obtenir un ensemble de données à partir d’une ou pluiseurs tables. Les requêtes sont émises par l’instruction SELECT, dont la forme générale réduite est la suivante:
SELECT liste-de-colonnes
FROM liste-de-tables
L’instruction SELECT peut être écrite sur une ou plusieurs lignes .
Sélection des colonnes d’une table
Dans l’instruction SELECT, on peut demander toutes les colonnes de la table ou seulement une partie des colonnes.
SELECT DEPTNO, DNAME, MGRNO, REPDPT, LOC
FROM DEMO/DEPT
Le résultat pourrait être le suivant:
DEPTNO DNAME MGRNO REPDPT LOC
A00 Sce Informatique 1010 A00 PARIS LA DEFENSE
B01 PLANNING 2020 A00 PARIS LA DEFENSE
C01 FORMATION 3030 A00 PARIS LA DEFENSE
D01 ETUDES 4040 A00 MARNE LA VALLEE
D11 FABRICATION 5050 D01 MARNE LA VALLEE
D21 VENTES 6060 D21 PARIS
E01 APRES VENTE 7070 D21 VELIZY
E11 COMUNICATION 8080 E11 PARIS
E21 SUPPORT LOGICIEL 9090 A00 PARIS LA DEFENSE
Sélection de toutes les colonnes d’une table
Pour obtenir toutes les colonnes d’une table, on peut soit fournir la liste intégrale des colonnes, soit mettre un caractère * à la place de la liste des colonnes:
SELECT DEPTNO, DNAME, MGRNO, REPDPT, LOC
FROM PERSON/DEPT
ou bien
SELECT * FROM PERSON/DEPT
Le résultat obtenu est le même:
DEPTNO DNAME MGRNO REPDPT LOC
A00 Sce Informatique 1010 A00 PARIS LA DEFENSE
B01 PLANNING 2020 A00 PARIS LA DEFENSE
C01 FORMATION 3030 A00 PARIS LA DEFENSE
D01 ETUDES 4040 A00 MARNE LA VALLEE
D11 FABRICATION 5050 D01 MARNE LA VALLEE
D21 VENTES 6060 D21 PARIS
E01 APRES VENTE 7070 D21 VELIZY
E11 COMUNICATION 8080 E11 PARIS
E21 SUPPORT LOGICIEL 9090 A00 PARIS LA DEFENSE
Elimination de lignes répétitives
Dans une requête, on peut souhaiter obtenir un résultat ne faisant pas apparaître, plus d’une fois, les lignes contenant une valeur donnée:
La requête suivante fournit la liste des emplois occupés par les salariés. Certains emplois sont présentés plus d’une fois.
SELECT JOB
FROM EMP
JOB
PRESIDENT
MANAGER
MANAGER
MANAGER
MANAGER
MANAGER
MANAGER
MANAGER
FILE REP
FILE REP
FILE REP
OPERATEUR
OPERATEUR
DESIGNER
SALES REP
CLERCK
Pour obtenir que chaque emploi n’apparaisse qu’une fois, la clause DISTINCT est associée à l’instruction SELECT:
SELECT DISTINCT JOB
FROM EMP
JOB
ANALYST
CLERCK
CLERK
DESIGNER
FILE REP
MANAGER
OPERATEUR
PRESIDENT
SALES REP
Les requêtes sur des lignes
La clause WHERE
La clause WHERE permet de sélectionner seulement certaines lignes d’une table. La clause WHERE, dans l’instruction SELECT, apparaît après la clause FROM, selon la syntaxe suivante:
FROM table
WHERE conditions
Une instruction SELECT avec la clause WHERE permet d’obtenir seulement les lignes répondant à certaines conditions.
Voici une instruction SELECT permettant d’obtenir l’ensemble des employés du département 'E21':
FROM PERSON/EMP
WHERE DEPTNO = ‘E21’
9090 SPENCER PHILIP E21
9101 GOUNOT JASON E21
9102 LEE WANG YU E21
9103 MEHTA RAMRANIA E21
Dans cette clause WHERE, la colonne DEPTNO est comparée à la constante ‘E21’. On peut trouver divers types d’opérateurs dans une clause WHERE. Ces opérateurs peuvent combiner des colonnes et des constantes, mais aussi des colonnes avec d’autres colonnes.
FROM DEPT
WHERE DEPTNO > ‘D21’
7070 GEYER JOHN E01
8080 DARBOY HELENE E11
9090 SPENCER PHILIP E21
9101 GOUNOT JASON E21
9102 LEE WANG YU E21
9103 MEHTA RAMRANIA E21
9104 DANDIN GEORGES E11
9105 SMITH BARBARA E11
FROM PERSON/EMP
WHERE BONUS > COMM
SALLY 4.000,00 3.000,00
LEE 2.000,00 1.500,00
MEHTA 1.250,00 1.000,00
Voici une liste des opérateurs de comparaison classiques:
= égal à
<> différent de
> supérieur à
>= supérieur ou égal à
< inférieur à
<= inférieur ou égal à
BETWEEN ...AND entre
IN(liste) dans
LIKE rapprochement avec une série de caractères
Expressions composées
Une clause WHERE peut être composée d’expressions de comparaison simples, combinées entre elles par des opérateurs logiques AND, OR
La requête ci-dessous permet d’obtenir l’ensemble des employés concepteurs (DESIGNER) dont le salaire est supérieur à 20000 et le bonus inférieur à 800:
FROM PERSON/EMP
WHERE JOB = ‘DESIGNER’
AND SAL > 20000
AND BONUS < 800
TREVOR 22.000,00 500,00 DESIGNER
RUFUS 20.500,00 450,00 DESIGNER
YOSHIMURA 24.000,00 300,00 DESIGNER
WALLIS 20.500,00 350,00 DESIGNER
FROM PERSON/EMP
WHERE JOB = ‘DESIGNER’
OR SAL > 40000
ELBAZE 52.750,00 PRESIDENT
HASS 41.250,00 MANAGER
GEYER 40.175,00 MANAGER
LUTZ 19.000,00 DESIGNER
TREVOR 22.000,00 DESIGNER
RUFUS 20.500,00 DESIGNER
YOSHIMURA 24.000,00 DESIGNER
DUVIVIER 19.000,00 DESIGNER
WALLIS 20.500,00 DESIGNER
L’instruction ci-dessous combine les opérateurs OR et AND pour obtenir l’ensemble des employés qui sont soit MANAGER, soit DESIGNER mais avec un salaire de plus de 20000.
FROM PERSON/EMP
WHERE JOB = ‘MANAGER’
OR (JOB = ‘DESIGNER’ AND SAL > 20000)
HASS 41.250,00 MANAGER
SALLY 38.500,00 MANAGER
GEYER 40.175,00 MANAGER
STERN 32.250,00 MANAGER
REGIS 36.000,00 MANAGER
DARBOY 29.950,00 MANAGER
SPENCER 25.400,00 MANAGER
TREVOR 22.000,00 DESIGNER
RUFUS 20.500,00 DESIGNER
YOSHIMURA 24.000,00 DESIGNER
WALLIS 20.500,00 DESIGNER
FROM PERSON/EMP
WHERE JOB = ‘MANAGER’
AND DEPTNO <> ‘B01’
SALLY MANAGER C01
GEYER MANAGER E01
STERN MANAGER D11
REGIS MANAGER D21
DARBOY MANAGER E11
SPENCER MANAGER E21
FROM PERSON/EMP
WHERE NOT (JOB = ‘MANAGER’ OR JOB = ‘DESIGNER’)
AND SAL > 21000
ELBAZE PRESIDENT 52.750,00
LUCCINO SALES REP 25.000,00
MITANA ANALYST 25.000,00
Sélection entre limites de valeurs
L’opérateur BETWEEN permet de sélectionner des lignes dans lesquelles une colonne donnée contient une valeur comprise dans une fourchette.
Supposons que nous souhaitons obtenir l’ensemble des employés dont le salaire est compris entre 20000 et 30000, la requête pourra être la suivante:
FROM PERSON/EMP
WHERE SAL BETWEEN 20000 AND 30000
DARBOY MANAGER 29.950,00
SPENCER MANAGER 25.400,00
LUCCINO SALES REP 25.000,00
DURAND CLERCK 20.000,00
MITANA ANALYST 25.000,00
MICOLE ANALYST 21.000,00
TREVOR DESIGNER 22.000,00
RUFUS DESIGNER 20.500,00
YOSHIMURA DESIGNER 24.000,00
WALLIS DESIGNER 20.500,00
DARBONNE CLERK 20.500,00
La requête contraire sera la suivante:
FROM PERSON/EMP
WHERE SAL NOT BETWEEN 20000 AND 30000
ELBAZE PRESIDENT 52.750,00
HASS MANAGER 41.250,00
SALLY MANAGER 38.500,00
GEYER MANAGER 40.175,00
STERN MANAGER 32.250,00
REGIS MANAGER 36.000,00
GOUNOT FILE REP 18.500,00
LEE FILE REP 17.000,00
MEHTA FILE REP 16.000,00
DANDIN OPERATEUR 14.000,00
SMITH OPERATEUR 18.000,00
LUTZ DESIGNER 19.000,00
DUVIVIER DESIGNER 19.000,00
PEREZ CLERCK 18.000,00
FRANCE CLERK 17.500,00
Rapprochement avec une liste
L’opérateur IN permet de sélectionner les lignes dont une colonne donnée contient une valeur correspondant à une des valeurs d’une liste.
FROM PERSON/EMP
WHERE JOB IN (‘CLERK’, ‘ANALYST’, ‘DESIGNER’)
ENAME JOB DEPTNO
LUTZ DESIGNER D11
DURAND CLERK A00
MITANA ANALYST C01
MICOLE ANALYST C01
TREVOR DESIGNER D11
RUFUS DESIGNER D11
YOSHIMURA DESIGNER D11
DUVIVIER DESIGNER D11
WALLIS DESIGNER D11
PEREZ CLERK D21
DARBONNE CLERK D21
FRANCE CLERK D21
FROM PERSON/EMP
WHERE JOB NOT IN (‘CLERK’, ‘ANALYST’, ‘DESIGNER’)
ELBAZE PRESIDENT A00
GOUNOT FILE REP E21
LEE FILE REP E21
MEHTA FILE REP E21
DANDIN OPERATEUR E11
SMITH OPERATEUR E11
LUCCINO SALES REP A00
MITANA ANALYST C01
MICOLE ANALYST C01
Lorsqu’on effectue une recherche avec les opérateurs = ou IN, la correspondance entre les valeurs doit être totale. Il peut se présenter des cas où l’on recherche simplement une correspondance partielle.
Par exemple, on peut souhaiter obtenir l’ensemble des employés dont le nom commence par le caractère D:
FROM PERSON/EMP
WHERE ENAME LIKE ‘D%’
DARBOY MANAGER E11
DANDIN OPERATEUR E11
DURAND CLERK A00
DUVIVIER DESIGNER D11
DARBONNE CLERK D21
Ordre de présentation des données
L’ordre dans lequel on reçoit les données dans un programme peut être géré grâce à la clause ORDER BY de l’instruction SELECT.
La clause ORDER BY permet de classer les lignes:
- dans l’ordre croissant avec la clause ASC
- dans l’ordre décroissant avec la clause DESC
- dans un ordre quelconque portant sur plusieurs colonnes.
FROM EMP
WHERE DEPTNO = ‘D21’
ORDER BY SAL DESC
36.000,00 MANAGER REGIS D21
20.500,00 CLERK DARBONNE D21
18.000,00 CLERK PEREZ D21
17.500,00 CLERK FRANCE D21
FROM EMP
WHERE DEPTNO = ‘D21’
ORDER BY JOB, SAL DESC
20.500,00 CLERK DARBONNE D21
18.000,00 CLERK PEREZ D21
17.500,00 CLERK FRANCE D21
36.000,00 MANAGER REGIS D21
Jointures de tables
On peut effectuer des jointures entre des lignes d’une table et celle d’une autre table, en fonction d’une colonne commune aux tables concernées. Les tables EMP et DEPT peuvent être jointes par la colonne DEPTNO.
La table EMP ne contient pas la localisation des départements. Pour obtenir la localisation du département dans lequel travaille 'DANDIN’, une jointure sera effectuée entre la table EMP et la table DEPT.
FROM PERSON/EMP, PERSON/DEPT
WHERE ENAME = ‘DANDIN’
AND EMP.DEPTNO = DEPT.DEPTNO
DANDIN 5563 PARIS
La forme générale d’une jointure est la suivante:
FROM table, table, ...table
WHERE expression-logique
Dans la liste-des-colonnes, sont nommées les colonnes à obtenir. La clause FROM désigne les tables jointes.
La clause WHERE spécifie les conditions de jointure, ou si l’on veut veut, de cocanténation de colonnes. Elle peut aussi, en plus, spécifier des conditions de sélection, dans ce cas il doit apparaître un opérateur AND pour connecter les deux conditions. C’était le cas dans l’exemple ci-dessus.
Dans l’exemple ci-dessus, la condition ENAME = ‘DANDIN’ sert à sélectionner la ligne relative à l’employé DANDIN. La condition EMP.DEPTNO = DEPT.DEPTNO est la condition de jointure. Elle spécifie que si la colonne DEPTNO d’une ligne de la table EMP est égale à la colonne DEPTNO de la table DEPT, alors, les deux lignes doivent être concaténées.
Dans cet exemple, la colonne DEPTNO est préfixée par un nom de table. En effet, la colonne DEPTNO apparaissant dans les deux tables utilisées, doit être préfixée par un nom de table pour permettre la distinction entre les deux informations.
A00 Sce Informatique ELBAZE PRESIDENT
E11 COMUNICATION DARBOY MANAGER
E11 COMUNICATION DANDIN OPERATEUR
E11 COMUNICATION SMITH OPERATEUR
A00 Sce Informatique LUCCINO SALES REP
A00 Sce Informatique DURAND CLERK
Equi-jointures et non equi-jointures
Une condition de jointure peut spécifier, entre les colonnes, une relation qui ne soit pas une relation d’égalité. Dans ce cas il s’agit d’une non équi-jointure.
Dans l’exemple ci-dessous, une non équi-jointure est utilisée pour obtenir l’ensemble des employés qui gagnent plus que 'REGIS':
FROM EMP X, EMP Y
WHERE X.SAL > Y.SAL
AND Y.ENAME = ‘REGIS’
SALLY 38.500,00 REGIS 36.000,00
GEYER 40.175,00 REGIS 36.000,00
HASS 41.250,00 REGIS 36.000,00
ELBAZE 52.750,00 REGIS 36.000,00
Etant donnée une table des grades:
1 5.000,00 10.000,00
2 10.001,00 15.000,00
3 15.001,00 20.000,00
4 20.001,00 25.000,00
5 25.001,00 30.000,00
6 30.001,00 40.000,00
7 40.001,00 50.000,00
8 50.001,00 60.000,00
La requête suivante permet d’obtenir le grade de chaque employé, en fonction de son salaire:
FROM PERSON/EMP, PERSON/GRADE
WHERE SAL BETWEEN LOSAL AND HISAL
ORDER BY GRADE, JOB
2 OPERATEUR DANDIN 14.000,00 10
3 CLERK FRANCE 17.500,00 12
3 CLERK PEREZ 18.000,00 10
3 CLERK DURAND 20.000,00 10
3 DESIGNER LUTZ 19.000,00 12
3 DESIGNER DUVIVIER 19.000,00 10
3 FILE REP MEHTA 16.000,00 12
3 FILE REP LEE 17.000,00 10
3 FILE REP GOUNOT 18.500,00 12
3 OPERATEUR SMITH 18.000,00 13
4 ANALYST MICOLE 21.000,00 14
4 ANALYST MITANA 25.000,00 12
...
Les Alias
La nécessité de préfixer des noms de colonne peut s’avérer lourde d’emploi, si les noms de tables sont longs. La clause FROM peut être utilisée pour attribuer un alias à chaque table.
Utilisation sans alias:
FROM PERSON/EMP, PERSON/DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO AND
EMP.DEPTNO IN (‘A00’, ‘E11’)
ORDER BY EMP.DEPTNO
A00 Sce Informatique ELBAZE PRESIDENT
E11 COMUNICATION DARBOY MANAGER
E11 COMUNICATION DANDIN OPERATEUR
E11 COMUNICATION SMITH OPERATEUR
A00 Sce Informatique LUCCINO SALES REP
A00 Sce Informatique DURAND CLERK
Utilisation Avec un alias
SELECT DEPT.DEPTNO, DNAME, ENAME, JOB
FROM PERSON/EMP X, PERSON/DEPT Y
WHERE X.DEPTNO = Y.DEPTNO AND
X.DEPTNO IN (‘A00’, ‘E11’)
ORDER BY X.DEPTNO
Les Expressions rithmétiques
Des calculs peuvent être effectués sur les colonnes de tables par insertion d'expressions arithmétiques dans les instructions SQL.
Les expressions arithmétiques sont construites à partir des opérateurs classiques:
+ Addition
- Soustraction
* Multiplication
/ Division
** Exponentiation
Expressions dans une instruction SELECT
Pour recueillir le résultat d'un calcul, inserez une expression arithmétique dans l'instruction SELECT.
SELECT ENAME, SAL, BONUS, SAL + BONUS
FROM EMP
WHERE JOB = 'CLERK'
ENAME SAL BONUS SAL + BONUS
DURAND 20.000,00 400,00 20.400,00
PEREZ 18.000,00 500,00 18.500,00
DARBONNE 20.500,00 500,00 21.000,00
FRANCE 17.500,00 500,00 18.000,00
Expressions dans une clause WHERE
Des expressions arithmétiques peuvent aussi figurer dans la clause WHERE, comme dans cette requête de recherche des employés dont la commission est supérieur à 8% du salaire:
SELECT ENAME, SAL, COMM
FROM PERSON/EMP
WHERE COMM > 0,08 * SAL
ENAME SAL COMM
ELBAZE 52.750,00 4.500,00
HASS 41.250,00 3.500,00
LEE 17.000,00 1.500,00
SMITH 18.000,00 1.750,00
PEREZ 18.000,00 2.500,00
Expressions composées
Une expression arithmétique peut être composée de plusieurs opérateurs. Elle est alors évaluée selon les règles classiques de précédence: les expressions entre parenthèses sont d'abord évaluées. Lorsque l'ordre dévaluation n'est pas imposé par les parenthèses, l'exponentiation est appliquée, avant la multiplication et la division. Multiplication et division sont évaluées avant l'addition et la soustraction. Les opérateurs de même précédence sont pris en compte de gauche à droite.
SELECT ENAME , SAL, BONUS, 12 * (SAL + BONUS)
FROM PERSON/EMP
WHERE JOB = 'MANAGER'
ENAME SAL BONUS Expression numérique
HASS 41.250,00 800,00 504.600,00
SALLY 38.500,00 4.000,00 510.000,00
GEYER 40.175,00 800,00 491.700,00
STERN 32.250,00 800,00 396.600,00
REGIS 36.000,00 800,00 441.600,00
DARBOY 29.950,00 750,00 368.400,00
SPENCER 25.400,00 700,00 313.200,00
Ecriture du programme C:
void main(void)
{
int CurNo = 1;
int RetCode;
char stmt[] = "SELECT ENAME , SAL, BONUS, 12 * (SAL + BONUS) INTO :ename, :sal, :bonus, :salan FROM PERSON/EMP WHERE JOB = 'MANAGER'";
...
_fstrcpy(sql_crd.stmt, stmt);
Sql_Exec(CurNo, (CARD)&sql_crd, 0L, &RetCode);
...
}
NOTES:
Quelques fonctions
Dans SQL, les fonctions peuvent s’appliquer, soit sur un ensemble de valeurs, soit sur une seule valeur. Les fonctions dites de groupe sont celles qui s’appliquent à des ensembles de valeurs. Les fonctions scalaires sont celles qui s’appliquesnt sur une seule valeur.
Les différentes fonctions SQL sont présentées dans le Manuel de Référence SQL.
Quelques Fonctions de Groupe
La fonction MAX
Pour obtenir le salaire le plus élevé dans la table EMP, l’instruction SELECT suivante peut être utilisée:
SELECT MAX(SAL) into :sal_maxi from PERSON/EMP
Dans cette instruction, la variable hôte sal_maxi reçoit la valeur la plus élevée de la colonne SAL, dans la table EMP. Une variable hôte est une variable du programme.
La fonction MIN
Pour obtenir le salaire le plus bas figurant dans la table EMP, l’instruction SELECT suivante peut être utilisée:
SELECT MIN(SAL) into :sal_mini from PERSON/EMP
Dans cette instruction, la variable hôte sal_mini reçoit la valeur la plus basse figurant dans la colonne SAL de la table EMP.
La fonction AVG
La fonction AVG permet de calculer la moyenne d’un ensemble de valeurs, dans une colonne. Pour calculer le salaire moyen dans le département 'C01', l’instruction SELECT utilisée pourraît être la suivante:
SELECT AVG(SAL) into :sal_moyen FROM PERSON/EMP
WHERE DEPTNO = ‘C01’
Dans cette instruction, la variable hôte sal_moyen reçoit la valeur du salaire moyen calculé par SQL.
La fonction COUNT
Pour compter le combre de salariés du département ‘C01’, l’instruction SELECT suivante peut être utilisée:
SELECT COUNT(*) INTO :nbsal FROM PERSON/EMP
WHERE DEPTNO = ‘C01’
L’instruction ci-dessous quant à elle, permet de calculer, pour le département ‘C01’, le nombre total de salariés, le salaire moyen, le salaire maximum, le salaire minimum, le total des salaires.
SELECT COUNT(*), AVG(SAL), MAX(SAL), MIN(SAL), SUM(SAL)
INTO :nbsal, :salmoy, :salmax, :salmin, :totsal
FROM PERSON/EMP
WHERE DEPTNO = ‘C01’
La fonction SUM
La fonction SUM est utilisée pour effectuer des cumuls sur une colonne. Dans l’exemple ci-dessous, le cumul des bonus du département ‘A00’ est effectué et affecté à la variable hôte totbonus.
SELECT SUM(BONUS)
INTO :totbonus
FROM PERSON/EMP
WHERE DEPTNO = ‘A00’
Dans l’exemple suivant, SUM est utilisé pour effectuer, par département, le cumul des salaires et des bonus.
SELECT DEPTNO, SUM(SAL), SUM(BONUS)
INTO :deptno, :totsal, :totbonus
FROM EMP
GROUP BY DEPTNO
Le résultat obtenu est le suivant:
DEPTNO TOTSAL TOTBONUS
A00 97.750,00 1.900,00
B01 41.250,00 800,00
C01 84.500,00 5.100,00
D11 157.250,00 3.300,00
D21 92.000,00 2.300,00
E01 40.175,00 800,00
E11 61.950,00 1.850,00
E21 76.900,00 4.450,00
Quelques fonctions scalaires
La fonction CHAR
La fonction CHAR permet de convertir une zone date, ou une zone heure en un format donné, parmi les formats suivants: ISO, USA, EUR, JIS.
SELECT EMPNO, ENAME, BIRTHD, CHAR(BIRTHD, ISO), CHAR(BIRTHD, USA), CHAR(BIRTHD, EUR), CHAR(BIRTHD, JIS)
INTO :empno, :ename, :dt, :dtiso, :dtusa, :dteur, :dtjis
FROM PERSON/EMP
WHERE EMPNO = ‘9105’
L’exemple ci-dessus fournit les résultats suivants:
EMPNO ENAME dt dtiso dtusa dteur dtjis
9105 SMITH 14/07/68 1968-07-14 07/14/1968 14.07.1968 1968-07-14
Rappel du format des dates:
Nom du format Abréviation Format de la date Exemple
International Standard Organisation ISO aaaa-mm-jj 1993-01-25
IBM USA Standard USA mm/jj/aaaa 01/25/1993
IBM European Standard EUR jj.mm.aaaa 25.01.1993
Japan Industrial Standard JIS aaaa-mm-jj 1993-01-25
Date julienne - aaaajjj 1993025
Date julienne formattée(*JUL) - aa/jjj 93/025
(*MDY) - mm/jj/aa 01/25/93
(*DMY) - jj/mm/aa 25/01/93
(*YDM) - aa/mm/jj 93/01/25
La fonction DATE
La fonction DATE permet d’obtenir une représentation d’une valeur au format d’une date.
SELECT EMPNO, ENAME, BIRTHD, DATE(‘1993025’)
INTO :empno, :ename, :birthd, :dtjour
from PERSON/EMP
WHERE EMPNO = ‘9107’
L’exécution de l’instruction ci-dessus produit le résultat suivant:
empno ename birthd dtjour
9105 SMITH 14/07/68 25/01/93
Rappel du format des heures:
Nom du format Abréviation Format de l’heure Exemple
International Standard Organisation ISO hh.mm.ss 15.25.10
IBM USA Standard USA hh.mm PM ou AM 3.25 PM
IBM European Standard EUR hh.mm.ss 15.25.10
Japan Industrial Standard JIS hh:mm:ss 15:25:10
(*HMS) - hh:mm:ss 15:25:10
La fonction DAY
La fonction DAY permet, à partir d’une date, d’en extraire la portion représentant le jour. L’instruction ci-dessous extrait la portion jour de la zone BIRTHD et la place dans la variable journ.
SELECT EMPNO, ENAME, BIRTHD, DAY(BIRTHD)
INTO :empno, :ename, :birthd, :journ
FROM PERSON/EMP
WHERE EMPNO = ‘9105’
empno ename birthd journ
9105 SMITH 14/07/68 14
La fonction DAYS
La fonction DAYS retourne une valeur entière représentant la date spécifiée en paramètre. Cette fonction est souvent utilisée pour calculer le nombre de jours entre deux dates.
SELECT EMPNO, ENAME, DAYS(CURRENT DATE) - DAYS(BIRTHD)
into :empno, :ename, :nbjours
FROM PERSON/EMP
La fonction DIGITS
La fonction DIGITS sert à convertir une valeur numérique en une chaine de caractères. Par exemple, si la variable hôte :niveau est une chaine de caractères, alors on pourrait avoir l’instruction suivante:
SELECT EMPNO, ENAME, DIGITS(EDLVEL)
INTO :empno, :ename, :niveau
FROM PERSON/EMP
La fonction HOUR
La fonction HOUR sert à extraire la portion heure à partir d’une heure.
SELECT EMPNO, ENAME, HOUR(CURRENT TIME)
INTO :empno, :ename, :hh
FROM PERSON/EMP
La fonction MONTH
La fonction MONTH est utilisée dans une instruction pour extraire la portion mois d’une date. Dans l’exemple ci-dessous, l’on extrait les salariés nés en Janvier:
SELECT EMPNO, ENAME, BIRTHD
FROM EMP
WHERE MONTH(BIRTHD) = 01
EMPNO ENAME BIRTHD
1010 ELBAZE 05/01/45
9101 GOUNOT 12/01/54
9106 LUTZ 28/01/66
9107 WALLIS 26/01/61
La fonction STRIP
STRIP est une fonction de mise en forme servant à enlever des caractères en début et/ou en fin d’une chaine de caractères.
SELECT EMPNO, STRIP(ENAME, LEADING, ‘ ‘) FROM EMP
Cette instruction supprime les éventuels blancs figurant en tête de la zone ENAME.
La fonction SUBSTR
La fonction SUBSTR permet d’extraire une sous-chaine de caractères d’une chaine de caractères. L’instruction suivante extrait les cinq premiers caractères du prénom:
SELECT EMPNO, ENAME, SUBSTR(LSTNAM, 1, 5)
FROM EMP
LES CLAUSES GROUP BY, HAVING
GROUP BY et HAVING sont des clauses se rapportant à des groupes de ligne. Leur action porte toujours sur un groupe de lignes et non pas sur des lignes individuelles comme c’est le cas pour les clauses WHERE et ORDER BY.
La clause HAVING
De même que la clause WHERE permet de sélectionner certaines lignes, la clause HAVING permet elle, de sélectionner certains groupes de lignes. La clause HAVING se place après la clause GROUP BY.
Une clause HAVING permet de comparer certaines propriétés d’un groupe avec une valeur. Si l’expression ainsi construite est vraie, le groupe est retenu pour la requête.
Voici un exemple de requête utilisant la clause HAVING. Cet exemple permet d’obtenir le salaire annuel moyen pour tous les groupes de catégories d’emploi comprenant plus de deux personnes.
SELECT JOB, COUNT(*), 12 * AVG(SAL)
INTO :job, :nbsal, :anmoy
FROM PERSON/EMP
GROUP BY JOB
HAVING COUNT(*) > 2
Ici, la clause HAVING compare COUNT(*) (une propriété du groupe) avec la constante 2.
La clause WHERE peut aussi figurer dans une requête contenant la clause HAVING, comme le montrent les deux exemples ci-dessous:
.Sélection des départements comprenant au moins deux analystes.
SELECT DEPTNO
FROM PERSON/EMP
WHERE JOB = ‘ANALYST’
GROUP BY DEPTNO
HAVING COUNT(*) >= 2
.Sélection du salaire moyen des femmes dans chaque département, uniquement pour les départements dont le niveau minimum de formation est égal à 10.
SELECT DEPTNO, AVG(SAL), MIN(EDLVEL)
FROM PERSON/EMP
WHERE SEX = ‘F’
GROUP BY DEPTNO
HAVING MIN(EDLVEL) >= 10
Dans la requête ci-dessous, nous recherchons les groupes d’emploi dont le salaire moyen est supérieur à celui des managers:
SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) >
(SELECT AVG(SAL)
FROM EMP
WHERE JOB = ‘MANAGER’ )
D’une façon générale, lorsque les clauses WHERE et HAVING sont utilisées, SQL procède dans cet ordre, dans l’exécution de l’instruction:
1. Il applique la clause WHERE pour sélectionner les colonnes requises.
2. Il constitue les groupes et évalue les fonctions de groupe (AVG, SUM, etc).
3. Il applique la clause HAVING pour ne retenir que les groupes voulus.
La clause GROUP BY
La clause GROUP BY répartit les lignes de la table référencée en groupes, sur la base d’une ou plusieurs colonnes prises comme critère de groupement. Ainsi, pour toutes les lignes d’un même groupe, les colonnes servant de critère de groupement ont la même valeur.
Une fois les groupes constitués, SQL traite chaque groupe pour produire une ligne unique par groupe. Les colonnes de la ligne produite et qui figurent dans le SELECT, sont les propriétés du groupe, et non pas celles des lignes prises individuellement.
L’exemple ci-dessous permet d’obtenir le salaire moyen de chaque département:
SELECT DEPTNO, AVG(SAL)
FROM PERSON/EMP
GROUP BY DEPTNO
Pour exécuter cette instruction, SQL commence par répartir les lignes de la table en groupes, en fonction du numéro de département, DEPTNO. Ensuite, pour chaque groupe, il applique la fonction AVG(SAL).
La clause GROUP BY n’effectue pas de tri, mais simplement une répartition des lignes. Chaque ligne est placée dans un groupe qui sera ensuite traité par SQL. Ceci est différent de la clause ORDER BY qui, elle, classe les lignes de la table résultat, dans l’ordre spécifié par cette clause.
Une clause WHERE peut figuer dans une instruction SQL en même temps qu’une clause GROUP BY. Dans ce cas, la clause GROUP BY doit être placée après la clause WHERE. La clause GROUP BY doit être placée après la clause FROM, lorsqu’il n’y a pas de clause WHERE.
L’exemple suivant calcule, par département, la masse salariale des employés autres que MANAGER ou PRESIDENT:
SELECT DEPTNO, 12 * SUM(SAL)
FROM PERSON/EMP
WHERE JOB NOT IN (‘MANAGER’, ‘PRESIDENT’)
GROUP BY DEPTNO
GROUP BY peut aussi specifier un groupement sur plus d’une colonne, comme dans cet exemple, permettant de calculer par département et emploi, le salaire moyen annuel; un comptage du nombres d’employés concernés est également effectué, avec la fonction COUNT(*):
SELECT DEPTNO, JOB, COUNT(*), 12 * AVG(SAL)
FROM PERSON/EMP
GROUP BY DEPTNO, JOB
LES SOUS-REQUETES
Une sous-requête est une requête utilisée dans une clause d’une autre requête SQL.
Une sous-requête pourra ainsi apparaître dans une instruction SELECT, UPDATE, ou DELETE. Cette caractéristique confère au SQL une grande puissance, car elle permet, comme nous le verrons dans des exemples, de réaliser, avec quelques instructions, des fonctions complexes, ou des fonctions qui, en programmation classique, donneraient lieu à un ou plusieurs programmes complexes.
Utilisation de Base
Les sous-requêtes sont d’abord utilisées dans la clause WHERE de l’instruction SELECT, en général, pour extraire dans une table, un ensemble de lignes avec une condition dépendant de données se trouvant dans la table elle-même.
Supposons que nous voulions trouver tous les salariés occupant le même emploi que SMITH. Dans une première approche, ceci pourrait être effectué avec deux requêtes successives: la première requête, pour trouver l’emploi qu’occupe SMITH, et la seconde pour trouver les salariés occupant ce même emploi.
Voici la première requête:
SELECT JOB
FROM PERSON/EMP
WHERE ENAME = ‘SMITH’
Le résultat obtenu est le suivant:
JOB
OPERATEUR
Voici la seconde requête:
SELECT ENAME, JOB
FROM PERSON/EMP
WHERE JOB = ‘OPERATEUR’
Le résultat de cette requête est le suivant:
ENAME JOB
DANDIN OPERATEUR
SMITH OPERATEUR
Le même résultat final est obtenu avec cette instruction comportant une sous-requête:
SELECT ENAME, JOB
FROM PERSON/EMP
WHERE JOB = (SELECT JOB
FROM PERSON/EMP
WHERE ENAME = ‘SMITH’ )
Remarquez que la sous-requête est placée entre des parenthèses.
D’une façon générale, une sous-requête peut toujours être utilisée là où apparaît une clause WHERE.
Dans une sous requête, les noms de colonne qui ne sont pas préfixés sont considérés comme se rapportant aux tables référencées dans la sous-requête.
Voici un autre exemple de sous-requête permettant de trouver tous les salariés dont le salaire est supérieur à la moyenne des salaires:
SELECT ENAME, SAL
FROM PERSON/EMP
WHERE SAL > (SELECT AVG(SAL)
FROM PERSON/EMP )
Une forme plus élaborée fait usage des clauses GROUP BY et HAVING pout trouver les départements dont la moyenne des salaires est la plus élevée:
SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) >= ALL
(SELECT AVG(SAL)
FROM EMP
GROUP BY DEPTNO)
Voici une autre forme de sous-requête permettant de trouver les salariés travaillant à PARIS:
SELECT EMPNO, ENAME
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘PARIS’)
Utilisation avec ANY ET ALL
ANY et ALL sont utilisés lorsque la sous-requête retourne un ensemble de valeurs. Ainsi, dans l’exemple ci-dessous, nous recherchons tous les salariés qui gaganent plus que au moins un salarié du département ‘B01’:
SELECT ENAME, JOB, SAL, DEPTNO
FROM PERSON/EMP
WHERE SAL > ANY (SELECT SAL
FROM PERSON/EMP
WHERE DEPTNO = ‘B01’ )
En utilisant ALL, nous trouverions tous les salariés qui gagent plus que le plus haut salaire du département ‘B01’:
SELECT ENAME, JOB, SAL, DEPTNO
FROM PERSON/EMP
WHERE SAL > ALL (SELECT SAL
FROM PERSON/EMP
WHERE DEPTNO = ‘B01’ )
Utilisation avec IN et NOT IN
IN et NOT IN sont utilisés dans des sous-requêtes retournant une liste de valeurs, comme dans l’exemple ci-dessous:
SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE DEPTNO = ‘E21’ AND
JOB IN ( SELECT JOB
FROM PERSON/EMP
WHERE DEPTNO = ‘E11’ )
Utlisation du mot-clé EXISTS
Dans les instructions SQL utilisant une sous-requête, le SQL évalue d’abord la sous-requête, et ensuite, utilise le résultat de celle-ci comme élément de la clause WHERE pour le SELECT de niveau supérieur. En revanche, lorsque le mo-clé EXISTS est utilisé, SQL vérifie simplement si la sous-requête retourne ou non une ou plusieurs lignes. Si la sous-requête retourne une ou plusieurs lignes, la condition est satisfaite, sinon, la condition est fausse.
L’instruction ci-dessous permet de trouver les départements dans lesquels il y a des salariés gagnant plus de 40.000. Deux tables sont impliquées: la table des salariés, EMP, et la table des départements, DEPT.
SELECT deptno, dname
FROM DEPT
WHERE EXISTS (SELECT *
FROM EMP
WHERE dept.deptno = emp.deptno
AND sal > 40000 )
avec la clause EXISTS, la liste des colonnes n’a pas besoin d’être spécifiée et l’écriture SELECT * est suffisante.
Sous-requêtes multiples
Une sous-requête peut, elle-même, contenir une autre sous-requête, comme dans l’exemple ci-dessous. Cet exemple permet de trouver les salariés du département ‘E11’ qui occupent le même emploi qu’un salarié du département ‘FABRICATION’.
SELECT ENAME, JOB
FROM EMP
WHERE DEPTNO = ‘E11’ AND
JOB IN (SELECT JOB
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE DNAME = ‘FABRICATION’ ) )
Utilisation du mot-clé UNION
Le mot-clé UNION est utilisé pour combiner plusieurs tables résultat de requêtes ou sous-requêtes en une seule. Lorsque SQL rencontre le mo-clé UNION, il évalue chaque sous-requête, constitue une table intermédiaire pour les résultats de celle-ci, puis passe à la sous-requête suivante. Ensuite les tables intermédaires sont combinées pour n’en former qu’une, avec élimination des lignes en double.
Dans l’exemple ci-dessous, sont recherchés les salariés qui ont le même salaire que SMITH ou DANDIN.
SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL IN ( SELECT SAL
FROM EMP
WHERE ENAME = ‘SMITH’
UNION
SELECT SAL
FROM EMP
WHERE ENAME = ‘DANDIN’)
Quant à la requête suivante, elle recherche les salariés qui ont le même salaire qu’un salarié, soit du département fourni dans la variable hôte numdpt, soit du département ‘C01’.
SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL IN ( SELECT SAL
FROM EMP
WHERE DEPTNO = :numdpt
UNION
SELECT SAL
FROM EMP
WHERE DEPTNO = ‘C01’)
La clause UNION est surtout indispensable lorsque les sous-requêtes impliquées portent sur des tables différentes comme dans l’exemple ci-dessous:
SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL IN ( SELECT SAL
FROM EMP1
WHERE ENAME = ‘SMITH’
UNION
SELECT SAL
FROM EMP2
WHERE ENAME = ‘DURELL’
SELECT SAL
FROM EMP3
WHERE ENAME = ‘GASCOGNE’)
Sous-requêtes sur plusieurs tables
Une sous-requête peut extraire des données à partir de plusieurs tables. C’est le cas de l’exemple ci-dessous, qui permet de trouver les salariés qui occupent le même emploi que des salariés travaillant à PARIS.
SELECT ENAME, JOB
FROM EMP
WHERE JOB IN (SELECT JOB
FROM EMP, DEPT
WHERE LOC = ‘PARIS’ AND
EMP.DEPTNO = DEPT.DEPTNO )
Requêtes ou Sous-requêtes et instruction INSERT
L’instruction INSERT peut être utilisée avec une sous-requête, en général, pour prendre des lignes dans une table et les insérer dans une autre table. La sous-requête remplace dans ce cas la clause VALUES.
Supposons que nous disposons d’une table appelée FORMATION comprenant les colonnes EMPNO, ENAME, JOB, HIREDT. A partir de la table EMP, nous pourrions, avec l’instruction ci-dessous, y ajouter les OPERATEURS de moins d’un an d’ancienneté.
INSERT INTO FORMATION (EMPNO, ENAME, JOB, HIREDT)
SELECT EMPNO, ENAME, JOB, HIREDT
FROM EMP
WHERE JOB = ‘OPERATEUR’ AND
YEAR(CURRENT DATE) - YEAR(HIREDT) <= 1
Voici un autre exemple d’utilisation d’une sous-reqûete avec l’instruction INSERT:
INSERT INTO DEPT (DEPTNO, DNAME, MGRNO, REPDPT, LOC)
SELECT ‘A01’, ‘Sce Bureautique’, MGRNO, REPDPT, LOC
FROM DEPT
WHERE DEPTNO = ‘A00’
Dans cet exemple, une ligne est extraite de la table DEPT. Les données de cette ligne sont utilisées pour l’insertion d’une nouvelle ligne avec des colonnes DEPTNO et DNAME spécifiques.
Sous-requêtes correlées
Dans une sous-requête normale, la sous-requête est exécutée une seule fois, et son résultat est utilisé par la clause WHERE pour l’évaluation de la requête principale. Mais il est possible de construire une sous-requête de telle sorte que celle-ci s’exécute de façon répétitive, pour chaque ligne prise en compte par la requête principale.
Par exemple, pour trouver les employés qui gagnent plus que le salaire moyen de leur département, nous avons besoin d’une requête principale pour prendre chaque employé de la table:
SELECT DEPTNO, ENAME, SAL
FROM EMP
WHERE SAL > (moyenne des salaires du département)
Nous avons aussi besoin d’une sous-requête pour calculer le salaire moyen du département auqel appartient l’employé en cours:
(SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = (département d’appartenance de l’employé))
Comme la requête principale prend chaque employé, elle doit faire appel à la sous- requête en lui fournissant le numéro de département de l’employé considéré. La sous-requête calcule alors le salaire moyen du département et le rend à la requête principale. Celle-ci n’ a plus qu’à comparer cette valeur moyenne au salaire de l’employé.
Ce type de sous-requête est appelé sous-requête correlée, parceque chaque exécution de la sous-requête est liée à une valeur d’une colonne de la ligne traitée (ligne de référence) par la requête principale.
Voici la requête finale:
SELECT DEPTNO, ENAME, SAL
FROM EMP X
WHERE SAL >
(SELECT AVG(SAL)
FROM EMP
WHERE X.DEPTNO = DEPTNO)
ORDER BY DEPTNO
Dans cette requête, la lettre X est appelée alias ou nom de correlation. L’alias apparaît dans la sous-requête pour désigner la ligne de référence, c’est à dire la ligne de la table pour laquelle la sous-requête est en train de s’exécuter.
Dans cet exemple, X.DEPTNO désigne le numéro de département de la ligne de référence, tandisque DEPTNO figurant à droite du signe =, désigne la colonne DEPTNO d’une ligne quelconque de la table EMP.
Ainsi, lorsque, pour chaque ligne de référence la sous-requête est exécutée, la comparaison est effectuée entre la valeur de la colonne DEPTNO (X.DEPTNO) de la ligne de référence, et celle de la colonne DEPTNO de chaque ligne de la table (DEPTNO). Pour le calcul de la moyenne, la sous-requête retient chaque ligne pour laquelle l’égalité est satisfaite. La moyenne ainsi calculée est retournée.
Pour bien retenir le concept de sous-requête correlée, il faut savoir que:
- une sous-requête correlée est tojours liée à une colonne se rapportant à la requête principale (SELECT)
- si la sous-requête travaille sur la même table que la requête principale, alors, la requête principale doit définir un alias ou nom de correlation pour le nom de la table, et la sous-requête doit utiliser l’alias chaque fois qu’elle doit désigner des colonnes de la ligne de référence.
Sous-requête correlée et clause HAVING
L’exemple ci-dessous permet d’obtenir les départements dont le salaire moyen est supérieur au salaire moyen des départements du même groupe (même 1ère lettre).
SELECT DEPTNO, AVG(SAL)
FROM PERSON/EMP X
GROUP BY DEPTNO
HAVING AVG(SAL) >
(SELECT AVG(SAL)
FROM PERSON/EMP
WHERE SUBSTR(DEPTNO, 1, 1) = SUBSTR(X.DEPTNO, 1, 1)
Pour chaque département, SQL calcule le salaire moyen du département. Il compare ensuite ce salaire moyen à la moyenne des salaires des départements du même groupe. La sous-reqête est utilisée pour calculer la moyene des salaires du groupe de départements courant.
Sous-requête correlée et instruction UPDATE
Une sous-requête peut être utilisée dans la clause WHERE d’une instruction UPDATE. Dans ce cas, l’alias ou le nom de correlation se rapporte toujours à la table dont les lignes sont mises à jour.
Soit la table BONUS comprenant les colonnes EMPNO, ENAME, SAL, DEPTNO. L’instruction suivante permet d’augmenter de 1% la colonne SAL de la table BONUS, pour tous les employés dont le salaire est inférieur à la moyenne des salaires de leur département.
UPDATE PERSON/BONUS X
SET SAL = SAL * 1.1
WHERE SAL <
(SELECT AVG(SAL)
FROM PERSON/EMP
WHERE DEPTNO = X.DEPTNO)
Dans l'exemple ci-dessus, X est un alias utilisé pour désigner la table BONUS dans la sous-requête correlée. X.DEPTNO désigne la zone DEPTNO de la ligne de la table BONUS en cours de traitement. Pour chaque ligne de la table BONUS, SQL calcule la moyene des salaires du département associé. Si la moyenne calculée est supérieur à la zone SAL de la ligne en cours de traitement dans BONUS, la mise à jour est effectuée: SAL = SAL * 1.1.
Restriction: Dans une instruction UPDATE, une sous-requête correlée ne doit pas référencer la table sur laquelle porte la mise à jour.
Sous-requête correlée et instruction DELETE
Une sous-requête peut être utilisée dans la clause WHERE d’une instruction DELETE. Dans ce cas, l’alias ou le nom de correlation se rapporte toujours à la table dans laquelle les lignes sont supprimées. Pour chaque ligne de la table référencée dans l'instruction DELETE, SQL évalue la sous-requête et décide ensuite si la suppression doit être effectuée ou non.
L’instruction suivante permet de supprimer les employés non référencés dans un département:
DELETE FROM PERSON/EMP X
WHERE NOT EXISTS
(SELECT * FROM PERSON/DEPT
WHERE DEPTNO = X.DEPTNO)
Dans cet exemple, X est un alias utilisé pour désigner la table EMP dans la sous-requête correlée. X.DEPTNO désigne la zone DEPTNO de la ligne de EMP en cours de traitement tandisque DEPTNO tout court désigne la colonne DEPTNO de la table DEPT.
Restriction: Dans une instruction DELETE, une sous-requête correlée ne doit pas référencer la table sur laquelle porte la suppression.
LES VUES
Les données d’une base de données peuvent être examinées sous divers angles, chaque vue offrant une perspective différente. SQL permet de créer des vues différentes à partir des tables de la base de données.
Définition d’une vue
Une vue est comme une fenêtre à travers laquelle les données peuvent être examinées, traitées. A partir de notre table EMP, nous pourrions, par exemple, définir une vue EMPE21 ne contenant que les employés du département E21, ou une vue EMPUSR, contenant les mêmes colonnes que la table EMP à l'exception des colonnes SAL, BONUS et COMM.
Une vue ne contient pas de données, mais elle peut, à quelques restrictions près, être traitée comme une table. Dans le contexte AS/400, une vue est un fichier logique dépourvu de clé.
Les vues présentent un double intérêt:
- Sécurité. Grâce aux vues, il est possible de donner accès uniquement à certaines colonnes d’une table. En définissant sur la table EMP une vue EMPUSR ne contenant ni la colonne SAL, ni la colonne BONUS, ni la colonne COMM, puis en verrouillant l’accès à la table EMP (instruction GRANT ), on peut assurer la confidentialité concerant la rémunération des employés.
- Facilité d’usage. Les vues permettent de simplifier les requêtes mettant en oeuvre plusieurs tables, avec des critères complexes.
Pour créer une vue, on utilise l’instruction CREATE VIEW, suivie d’une requête SELECT, selon la forme générale:
CREATE VIEW nom_de_vue
AS requête
L’instruction ci-dessous crée la vue EMPE21 donnant accès aux seuls employés du département E21.
CREATE VIEW EMPE21 AS
SELECT EMPNO, ENAME, LSTNAME, BIRTHD, HIREDT, SAL
FROM PERSON/EMP
WHERE DEPTNO = ‘E21’
Dans cette instruction, la requête sélectionne:
- Des colonnes (EMPNO, ENAME, LSTNAME, BIRTHD, HIREDT, SAL) et
- Des lignes (département E21), à partir
- De la table EMP de la collection ou bibliothèque PERSON.
Voici une autre forme d’instruction CREATE VIEW:
CREATE VIEW EMPA00 (EMPNO, ENAME, DEPTNO, BRUT) AS
SELECT EMPNO, ENAME, DEPTNO, SAL + COMM + BONUS
FROM PERSON/EMP
WHERE DEPTNO = ‘A00’
Dans cette instruction, nous avons défini explicitement les colonnes de la vue EMPA00. Les colonnes ainsi définies doivent correspondre, en nombre et en type, aux données résultant du SELECT.
Utilisation de plusieurs tables
Une vue peut combiner des données issues de plusieurs tables. Dans l’exemple ci-dessous, une vue est construite à partir de la table EMP et de la table DEPT:
CREATE VIEW EMPB AS
SELECT EMPNO, ENAME, JOB, DNAME
FROM PERSON/EMP, PERSON/DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
CREATE VIEW EMPBR (EMPNO, ENAME, JOB, BRUT, DEPTNO, DNAME) AS
SELECT EMPNO, ENAME, JOB, SAL + COMM + BONUS, DNAME
FROM PERSON/EMP, PERSON/DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
Requête sur une vue
Les requêtes sont effectuées sur les vues de la même façon que sur les tables. Pour obtenir toute la vue EMPE21, la requête serait la suivante:
SELECT *
FROM EMPE21
Pour obtenir la liste des managers:
SELECT ENAME, JOB
FROM PERSON/EMPE21
WHERE JOB = ‘MANAGER’
Les modifications intervenant sur les tables de base sont automatiquement reflétées dans la vue.
Mise à jour d’une vue
Les mises à jour peuvent être effectuées directement sur certaines vues. Ainsi, dans notre vue EMPE21, le changement d’emploi de l’employé LEE peut être effectué sur EMPE21:
UPDATE PERSON/EMPE21
SET JOB = ‘ANALYST’
WHERE ENAME = ‘LEE’
Utlisation d’expressions et de fonctions
Des expressions et des fonctions peuvent figurer dans la requête définissant une vue. Ces expressions apparaissent exactement comme les autres colonnes de la vue, excepté que leurs champs sont calculés à partir des tables de base, chaque fois que la vue est utilisée. Ces colonnes, qui n’ont pas d’existence réelle, sont appelées colonnes virtuelles.
Lorsqu’on définit des colonnes virtuelles dans une vue, on doit explicitement leur donner un nom dans la clause CREATE VIEW.
Voici une vue destinée à fournir le salaire annuel de chaque salarié:
CREATE VIEW EMPZ (NOEMP, NOM, MENSAL, ANSAL, DEPTNO) AS
SELECT EMPNO, ENAME, SAL, 12 * SAL, DEPTNO
FROM EMP
Dans cette instruction, le nom ANSAL est attribué à la colonne virtuelle fournissant le salaire annuel.
Utilisation de fonctions de groupe
Il est possible de définir des vues dans lesquelles apparaissent des fonctiions de groupe et la clause GROUP BY (voir chapitre 8). Cette possibilité est particulièrement utile pour obtenir des données statistiques.
Dans l’exemple ci-dessous, nous définissons une vue calculant, par département, le salaire minimum, le salaire moyen, le salaire le plus élévé, et le total des salaires.
CREATE VIEW STATDEPT (DEPTNO, SALMIN, SALMOY, SALMAX, TOTSAL)
AS
SELECT DEPTNO, MIN(SAL), AVG(SAL), MAX(SAL), SUM(SAL)
FROM PERSON/EMP
GROUP BY DEPTNO
Dès lors, une requête sur cette vue pourrait être la suivante:
SELECT DEPTNO, SALMIN, SALMOY, SALMAX, TOTSAL
FROM STATDEPT
Restrictions concernant les vues
.Il n’est pas possible de modifier (UPDATE), insérer (INSERT), ou supprimer (DELETE) des données dans une vue, si elle comprend un des éléments suivants:
- La première clause FROM identifie plus d’une table (cas de jointure).
- La première clause FROM identifie une vue en lecture seule.
- La première clause SELECT contient l’une des fonctions AVG, COUNT, MAX, MIN ou SUM.
- La première clause SELECT contient le mot-clé DISTINCT.
- La requête SELECT principale contient une clause GROUP BY ou HAVING.
- Une sous-requête portant sur la même table que celle référencée dans SELECT principal.
Il n’est pas possible d’insérer une ligne dans une vue si:
- La table à partir de laquelle la vue est construite comporte une colonne sans valeur par défaut, n’admettant pas de valeur NULL et qui ne figure pas dans la définition de la vue.
- La vue contient une colonne résultat d’une expression, une constante, une fonction, ou un régistre spécial et si la colonne est citée dans liste du INSERT.
Il n’est pas possible de mettre à jour une colonne d’une vue résultant d’une expression , une constante, une fonction ou un régistre spécial.
Dans la définition d’une vue, les clause suivantes ne sont pas autorisées: UNION, UNION ALL, ORDER BY, FOR UPDATE OF, FOR FETCH ONLY.
LES INDEX
Un Index sur une table est un fichier logique muni d’une clé, et permettant, en général, un accès plus rapide aux données de la table de base.
Création d’un index
L’instruction CREATE INDEX est utilisée pour créer un index sur une table, selon la syntaxe simplifiée:
CREATE INDEX nom_index
ON nom_table(nom_colonne1, nom_colonne2, ...)
La clause CREATE INDEX est suivie du nom de l’index ou fichier logique. Ce nom peut être qualifié par un nom de collection ou de bibliothèque. Dans le cas contraire, l’index est créé dans la même collection ou bibliothèque que la table sur laquelle il porte.
La clause ON est suivie du nom qualifié de la table sur laquelle l’index est construit, puis suivent, entre parenthèses, les noms des colonnes constituant la clé de l’index.
Dans l’exemple ci-dessous, un index portant sur la colonne ENAME est créé sur la table EMP.
CREATE INDEX EMPNOM
ON PERSON/EMP (ENAME)
Index portant sur les colonnes ENAME et EMPNO:
CREATE INDEX EMPNOMNO
ON PERSON/EMP (ENAME, EMPNO)
Unicité des clés
L’index permet aussi de contrôler que des lignes dans une table ne contiennent pas le même identifiant. Par exemple, dans la table EMP, deux employés ne doivent pas avoir le même numéro. Cette caractéristique peut être imposée lors de la création de l’index en utilisant le mot-clé UNIQUE après la clause CREATE:
CREATE UNIQUE INDEX EMP_NUM
ON PERSON/EMP (EMPNO)
Utilisation de l’index
La syntaxe SQL n’autorise pas l’utilisation explicite d’un index dans une requête (clause FROM). Autrement dit, s’il existe un index sur une table, celui-ci ne peut que être utilisé automatiquement par SQL, et dans certaines conditions.
SQL utilise automatiquement l'index lorsqu’une clause WHERE fait référence à une colonne faisant partie d’un index. Si la table référencée est importante, le traitement de la requête sera plus rapide. En revanche, si la table référencée est petite, l’utilisation d’un index n’apportera aucune amélioration de performances, et celles-ci seront plutôt moins bonnes que si la table ne comportait pas d’index du tout.
Dans l’exemple ci-dessous, SQL utilisera automatiquement l’index EMPNOM:
SELECT *
FROM PERSON/EMP
WHERE ENAME = ‘SMITH’
SQL n’utilisera pas l’index, si dans la clause WHERE, une colonne de l’index figure dans une expression, comme dans l’exemple suivant:
SELECT *
FROM PERSON/EMP
WHERE TRANSLATE(ENAME) = ‘SMITH’
Index et jointures
Les index sont particulièrement importants dans les opérations de jointure, puisque SQL doit rechercher dans une table, les correspondances pour chaque ligne d’une autre table.
Autant que possible, un index doit être créé sur les colonnes devant servir de critère de jointure entre des tables.
Par exemple, si une jointure entre les tables EMP et DEPT est souvent utilisée, un index portant sur la colonne DEPTNO devra être créé sur la table EMP.
CREATE UNIQUE INDEX EMPDEPT
ON PERSON/EMP(DEPTNO)
SELECT ENAME, LOC
FROM PERSON/EMP, PERSON/DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
L’index sur DEPTNO est utilisé ici pour trouver les lignes de EMP qui sont à joindre aux lignes sélectionnées de DEPT. Dans cet exemple, l’une ou l’autre des colonnes (EMP.DEPTNO, DEPT.DEPTNO) aurait pu servir à la construction de l’index pour améliorer les performances. Mais ceci n’est pas toujours le cas, comme dans l’exemple ci-dessous, où nous recherchons le lieu de travail et le nom de l’employé 9105.
SELECT ENAME, LOC
FROM PERSON/EMP, PERSON/DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMPNO = ‘9105’
Dans cet exemple en effet, l’index EMPDEPT sur EMP ne sera pas utilisé et donc ne nous sera d’aucune utilité, la recherche dans EMP ne s’effectuant pas sur le numéro de département. Il faudra donc créer un index portant sur DEPT.DEPTNO (table DEPT) et un index sur EMP.EMPNO (Table EMP).
CREATE UNIQUE INDEX DEPTNUM
ON DEPT (DEPTNO)
CREATE UNIQUE INDEX EMPNUM
ON EMP(EMPNO)
ensuite:
SELECT ENAME, LOC
FROM PERSON/EMP, PERSON/DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMPNO = ‘9105’
Deux index seront utilsés pour évaluer cette requête:
- L’index EMPNUM sera utilisé pour trouver la ligne adéquate dans la table EMP.
- L’index DEPTNUM sera utilisé pour trouver la ligne de la table DEPT à joindre à la ligne de la table EMP.
Suppression d’un index
Pour supprimer un index d’une table, utilisez l’instruction DROP INDEX
DROP INDEX PERSON/DEPTNUM
Inscription à :
Commentaires
(
Atom
)