Developpez.com - SGBD & SQL
X

Choisissez d'abord la catégorieensuite la rubrique :


Requêtes SELECT avec Oracle 9i DataBase

07/09/2003

Par giminik (Matthieu Petiot) (autres articles)
 

niveau : normal

durée : 60 minutes

Cet article vous guidera dans des requêtes de sélection de données suivant certains critères. Il est destiné aux débutants qui souhaitent apprendre et visualiser les résultats que produisent les requêtes SQL. Il suppose toutefois que vous ayez tout de même quelques connaissances du SQL.
J'adresse mes remerciements à armatatuxa qui a gracieusement relu cet article et corrigé les fautes d'orthographe.


Introduction
1. Requêtes SELECT simples
1.1. Sélectionner tous les élèments d'une table
1.2. Sélectionner certains élèments d'une table
1.3. Sélectionner des élèments remplissant une condition
1.4. Effectuer un calcul
1.4.1. Calcul portant sur des valeurs non NULL
1.4.2. Erreurs de calcul liées aux valeurs NULL
1.4.3. Remédier à ces erreurs de valeurs NULL
1.5. Utilisation des alias de colonne
1.6. Utilisation de l'opérateur de concaténation
1.7. Ne pas afficher les doublons
1.8. Opérateurs logiques
1.9. Plage de données
1.10. Liste de valeurs
1.11. Détecter les valeurs NULL
1.12. Rechercher des chaînes de caractères
1.13. Manipulation des chaînes
1.13.1. Contrôle de la casse
1.13.2. Concaténation des données
1.13.3. Extraction de caractères
1.13.4. Nombre de caractères d'une chaîne
1.14. Manipulation des chiffres
1.14.1. Arrondir une valeur numérique
1.14.2. Tronquer une valeur numérique
1.14.3. Caluler un modulo
1.15. Manipulation des dates
1.15.1. Calcul du nombre de mois entre deux dates
1.15.2. Ajout d'un nombre de mois à une date
1.15.3. Utilisation de la date du jour
1.15.4. Fonction renvoyant le dernier jour du mois
1.15.5. Fonction renvoyant la date du prochain jour
1.15.6. Arrondir une date
1.15.7. Tronquer une date
1.15.8. Changement de format des dates
1.16. Trier les données
1.16.1. Tri simple
1.16.2. Tri multiple
1.16.3. Tri sur les Alias de colonne
1.16.4. Tri sur les numéros de colonne
1.17. Utilisation de la fonction de groupe
1.17.1. Fonction de groupe sur une seule colonne
1.17.2. Fonction de groupe sur plusieurs colonnes
1.17.3. Exclusion de groupe
2. Les requêtes SELECT avec jointure
2.1. Une jointure naturelle pour commencer
2.2. La jointure interne
2.2.1. Utiliser les alias de table
2.3. La jointure externe
2.4. La jointure croisée ou produit cartésien
2.4. L'auto-jointure
3. Les requêtes imbriquées
3.1. Requêtes imbriquées multi-lignes
3.1.1. Requête avec IN
3.1.2. Requête avec ANY
3.1.3. Requête avec ALL
3.2. Requêtes imbriquées multi-colonnes
3.3. Requêtes imbriquées synchronisées
3.3.1. Utilisation de l'opérateur EXISTS
4. Les opérateurs ensemblistes
4.1. L'opérateur UNION
4.2. L'opérateur INTERSECT
4.3. L'opérateur MINUS
5. Quelques fonctions propres à Oracle
5.1. Afficher la liste des tables de l'utilisateur connecté
5.2. Afficher la liste des contraintes
6. Faites le point avec quelques requêtes
7. Conclusion


Introduction


Nous pouvons maintenant pouvoir exploiter les données grâce à la requête SELECT. Nous allons donc commencer par de simples requêtes, puis continuer avec des jointures, et enfin terminer par des requêtes imbriquées.


1. Requêtes SELECT simples



1.1. Sélectionner tous les élèments d'une table


Ici, nous allons sélectionner toutes les lignes (pas de clause WHERE ) et toutes les colonnes (sélecteur * ) de la table employé. Il suffit d'effectuer la requête suivante :

SELECT *
FROM employe ;

IDEMPNOMEMPPRENOMEMPDATEEMBAUCHESALAIREEMPCOMMISSIONEMPSEXEEMPIDFONCSUPEMPIDDEP
1BigbossJames25/08/9515347,122011,12H1
1
2LamaligneSylvie03/09/958975,021298,87F211
3PoncePierre13/11/976891,571328,21H211
4FaidézafairMarc25/01/983815,544410H937
5LebofJacky05/02/983902,8501,4H937
6BadsellerCorinne25/11/022895,12
F934
7PopolJean Marie21/10/956410,951524H322
8CrozacDamien30/10/955214,05
H473
9MaginJulien12/11/954578,12
H683
10RanoujaJamel08/01/962410,51
H794
11RagondinMichel12/05/962108
H794
12BalaizLucie07/04/972510,54
F894
13LegrandMarcel18/12/954519,29854,74H576
14BigoufBernard26/02/963891,91514,74H576

1.2. Sélectionner certains élèments d'une table


Ici, nous allons sélectionner les colonnes nomEmp et prenomEmp de toutes les lignes.

SELECT nomEmp, prenomEmp
FROM employe ;

NOMEMPPRENOMEMP
BigbossJames
LamaligneSylvie
PoncePierre
FaidézafairMarc
LebofJacky
BadsellerCorinne
PopolJean Marie
CrozacDamien
MaginJulien
RanoujaJamel
RagondinMichel
BalaizLucie
LegrandMarcel
BigoufBernard

1.3. Sélectionner des élèments remplissant une condition


Ici, nous allons sélectionner les noms, prénoms et salaires des employés ayant un salaire de plus de 6000 Euros. Il faut utiliser la clause WHERE afin d'indiquer la condition de sélection.

SELECT nomEmp, prenomEmp, salaireEmp
FROM employe
WHERE salaireEmp > 6000 ;

NOMEMPPRENOMEMPSALAIREEMP
BigbossJames15347,12
LamaligneSylvie8975,02
PoncePierre6891,57
PopolJean Marie6410,95

1.4. Effectuer un calcul


Très utiles, les opérateurs les plus simples sont :

addition+
soustraction-
multiplication*
division/
Les opérateurs de comparaison sont :

inférieur à<
supérieur à>
égal à=
inférieur ou égal à<=
supérieur ou égal à>=
différent de<> ou !=

1.4.1. Calcul portant sur des valeurs non NULL


Il est possible de calculer le salaire annuel, primes exclues, de chaque employé.

SELECT prenomEmp, nomEmp, salaireEmp * 12
FROM employe ;

PRENOMEMPNOMEMPSALAIREEMP * 12
JamesBigboss184165,44
SylvieLamaligne107700,24
PierrePonce82698,84
MarcFaidézafair45786,48
JackyLebof46833,6
CorinneBadseller34741,44
Jean MariePopol76931,4
DamienCrozac62568,6
JulienMagin54937,44
JamelRanouja28926,12
MichelRagondin25296
LucieBalaiz30126,48
MarcelLegrand54231,48
BernardBigouf46702,92

1.4.2. Erreurs de calcul liées aux valeurs NULL


Pour illustrer ce cas, nous allons calculer le montant total que touche chaque employé, salaire + commission.

SELECT prenomEmp, nomEmp, salaireEmp, commissionEmp, salaireEmp + commissionEmp
FROM employe ;

PRENOMEMPNOMEMPSALAIREEMPCOMMISSIONEMPSALAIREEMP + COMMISSIONEMP
JamesBigboss15347,122011,1217358,24
SylvieLamaligne8975,021298,8710273,89
PierrePonce6891,571328,218219,78
MarcFaidézafair3815,5444108225,54
JackyLebof3902,8501,44404,2
CorinneBadseller2895,12

Jean MariePopol6410,9515247934,95
DamienCrozac5214,05

JulienMagin4578,12

JamelRanouja2410,51

MichelRagondin2108

LucieBalaiz2510,54

MarcelLegrand4519,29854,745374,03
BernardBigouf3891,91514,744406,65
Comme vous pouvez le constater, les résultats sont faussés par les éventuelles valeurs NULL de commissionEmp. En effet, lorsqu'un calcul comporte une valeur NULL, le résultat est NULL. Voyons la parade à cela dans le prochain point.


1.4.3. Remédier à ces erreurs de valeurs NULL


Pour pouvoir effectuer le calcul précédent, il suffit d'utiliser la fonction NVL(expr1, expr2). Cette fonction renvoie expr2 si expr1 est NULL, sinon renvoie expr1. Nous allons donc renvoyer 0 (zéro) si la commission a pour valeur NULL, afin de pouvoir faire la somme.

SELECT prenomEmp, nomEmp, salaireEmp, NVL (commissionEmp, 0), salaireEmp + NVL (commissionEmp, 0)
FROM employe ;

PRENOMEMPNOMEMPSALAIREEMPNVL(COMMISSIONEMP, 0)SALAIREEMP + NVL(COMMISSIONEMP, 0)
JamesBigboss15347,122011,1217358,24
SylvieLamaligne8975,021298,8710273,89
PierrePonce6891,571328,218219,78
MarcFaidézafair3815,5444108225,54
JackyLebof3902,8501,44404,2
CorinneBadseller2895,1202895,12
Jean MariePopol6410,9515247934,95
DamienCrozac5214,0505214,05
JulienMagin4578,1204578,12
JamelRanouja2410,5102410,51
MichelRagondin210802108
LucieBalaiz2510,5402510,54
MarcelLegrand4519,29854,745374,03
BernardBigouf3891,91514,744406,65

1.5. Utilisation des alias de colonne


Les alias servent à renommer les titres des colonnes. Le nom de l'alias suit immédiatement le nom de la colonne. En utilisant les " la casse est respectée, sinon tout est mis en majuscule.

SELECT prenomEmp "prenom", nomEmp nom, salaireEmp + NVL (commissionEmp, 0) "Salaire Total Mensuel"
FROM employe ;

prenomNOMSalaire Total Mensuel
JamesBigboss17358,24
SylvieLamaligne10273,89
PierrePonce8219,78
MarcFaidézafair8225,54
JackyLebof4404,2
CorinneBadseller2895,12
Jean MariePopol7934,95
DamienCrozac5214,05
JulienMagin4578,12
JamelRanouja2410,51
MichelRagondin2108
LucieBalaiz2510,54
MarcelLegrand5374,03
BernardBigouf4406,65
Comme vous pouvez le voir, la casse des alias entourés de " n'a pas changé.


1.6. Utilisation de l'opérateur de concaténation


La concaténation est utile pour regrouper plusieurs colonnes, ou pour générer des phrases. L'opérateur de concaténation est || (touches Alt Gr + 6). Pour insérer une chaîne de caractères, il suffit de l'entourer de ' (guillemets simples).

SELECT prenomEmp || ' ' || nomEmp || ' gagne ' || salaireEmp || ' Euros' "Employé et son salaire"
FROM employe ;

Employé et son salaire
James Bigboss gagne 15347,12 Euros
Sylvie Lamaligne gagne 8975,02 Euros
Pierre Ponce gagne 6891,57 Euros
Marc Faidézafair gagne 3815,54 Euros
Jacky Lebof gagne 3902,8 Euros
Corinne Badseller gagne 2895,12 Euros
Jean Marie Popol gagne 6410,95 Euros
Damien Crozac gagne 5214,05 Euros
Julien Magin gagne 4578,12 Euros
Jamel Ranouja gagne 2410,51 Euros
Michel Ragondin gagne 2108 Euros
Lucie Balaiz gagne 2510,54 Euros
Marcel Legrand gagne 4519,29 Euros
Bernard Bigouf gagne 3891,91 Euros

1.7. Ne pas afficher les doublons


Lorsqu'un champ contient plusieurs fois la même valeur, et que l'on souhaite afficher la liste de ces valeurs, il faut utiliser le paramètre DISTINCT . Ici, deux exemples, une fois sans ce paramètre et une fois avec. Nous allons afficher la liste de valeurs de idDep et de sexeEmp .

SELECT idDep, sexeEmp
FROM employe ;

IDDEPSEXEEMP
1H
1F
1H
7H
7H
4F
2H
3H
3H
4H
4H
4F
6H
6H
SELECT DISTINCT idDep, sexeEmp
FROM employe ;

IDDEPSEXEEMP
1F
1H
2H
3H
4F
4H
6H
7H

1.8. Opérateurs logiques


Ces opérateurs OR , AND et NOT (OU, ET et NON) sont aussi très utilisés. Ici nous allons afficher les employés qui gagnent moins de 4000 Euros et qui ne sont pas des femmes (F).

SELECT prenomEmp, nomEmp, salaireEmp
FROM employe
WHERE salaireEmp < 4000
    AND NOT sexeEmp = 'F' ;

N'affichera que des hommes ne gagnant pas plus de 4000 Euros. Notez qu'il était possible, et même recommandé de mettre AND sexeEmp = 'H' ;. Mais cela n'avait pour but que de montrer le fonctionnement de NOT.

PRENOMEMPNOMEMPSALAIREEMP
MarcFaidézafair3815,54
JackyLebof3902,8
JamelRanouja2410,51
MichelRagondin2108
BernardBigouf3891,91

1.9. Plage de données


Sélectionnons les employés ayant un salaire compris entre 4000 et 6000 Euros. Voici deux méthodes pour y parvenir.

avec un opérateur logique :

SELECT nomEmp, prenomEmp, salaireEmp
FROM employe
WHERE salaireEmp > 4000
    AND salaireEmp < 6000 ;

avec la commande BETWEEN :

SELECT nomEmp, prenomEmp, salaireEmp
FROM employe
WHERE salaireEmp BETWEEN 4000 AND 6000 ;

NOMEMPPRENOMEMPSALAIREEMP
CrozacDamien5214,05
MaginJulien4578,12
LegrandMarcel4519,29
Ceci ne fonctionne pas qu'avec des chiffres. Exemple avec des chaînes de caractères (attention à la casse) :

SELECT nomEmp, prenomEmp
FROM employe
WHERE nomemp BETWEEN 'C' AND 'H' ;

NOMEMPPRENOMEMP
CrozacDamien
FaidézafairMarc
Il est possible d'utiliser BETWEEN avec la négation NOT.


1.10. Liste de valeurs


Sélectionner les employés ayant un des salaires suivants : 8975,02 - 6410,95 - 3891,91. Il suffit pour cela d'utiliser la fonction IN (attention, pour des valeurs décimales, il faut utiliser des points à la place des virgules). NOT peut s'appliquer à IN.

SELECT prenomEmp, nomEmp
FROM employe
WHERE salaireEmp IN (8975.02, 6410.95, 3891.91) ;

PRENOMEMPNOMEMP
SylvieLamaligne
Jean MariePopol
BernardBigouf

1.11. Détecter les valeurs NULL


N'afficher que les employés qui ont une commission. Il suffit d'utiliser la commande IS NULL qui accepte une négation NOT : IS NOT NULL

SELECT prenomEmp, nomEmp
FROM employe
WHERE commissionEmp IS NOT NULL ;

PRENOMEMPNOMEMP
JamesBigboss
SylvieLamaligne
PierrePonce
MarcFaidézafair
JackyLebof
Jean MariePopol
MarcelLegrand
BernardBigouf

1.12. Rechercher des chaînes de caractères


Afficher les noms des employés ayant pour deuxième lettre a et ayant un i après le deuxième caractère. Pour cela, on utilise la commande LIKE et les caractères génériques _ (représentant un et un seul caractère) et % (représentant zéro ou plusieurs caractères). Attention, cette fonction est sensible à la casse (elle différencie les majuscules des minuscules) et elle accepte également la négation NOT.

SELECT nomEmp
FROM employe
WHERE nomEmp LIKE '_a%i%' ;

NOMEMP
Lamaligne
Faidézafair
Magin
Ragondin
Balaiz

1.13. Manipulation des chaînes



1.13.1. Contrôle de la casse


Ces fonctions servent à spécifier quel sera le format de sortie d'un élèment. UPPER() sert à convertir les valeurs alpha-numériques en lettres majuscules, LOWER() en lettres minuscules et INITCAP() convertit la première lettre de chaque mot en majuscule, toutes les autres lettres étant mises en minuscules.

SELECT UPPER(nomEmp), LOWER(prenomEmp)
FROM employe
WHERE salaireEmp > 6000 ;

UPPER(NOMEMP)LOWER(PRENOMEMP)
BIGBOSSjames
LAMALIGNEsylvie
PONCEpierre
POPOLjean marie
SELECT INITCAP(emp.nomEmp || ' a pour supérieur ' || sup.nomEmp )
FROM employe emp
    INNER JOIN employe sup
    ON emp.supEmp = sup.idEmp ;

INITCAP(EMP.NOMEMP||'APOURSUPÉRIEUR'||SUP.NOMEMP)
Lamaligne A Pour Supérieur Bigboss
Ponce A Pour Supérieur Bigboss
Faidézafair A Pour Supérieur Ponce
Lebof A Pour Supérieur Ponce
Badseller A Pour Supérieur Ponce
Popol A Pour Supérieur Lamaligne
Crozac A Pour Supérieur Popol
Magin A Pour Supérieur Crozac
Ranouja A Pour Supérieur Magin
Ragondin A Pour Supérieur Magin
Balaiz A Pour Supérieur Magin
Legrand A Pour Supérieur Popol
Bigouf A Pour Supérieur Popol

1.13.2. Concaténation des données


La fonction CONCAT(expr1, expr2) équivalente à l'opérateur de concaténation || permet de concaténer deux élèments.

SELECT CONCAT (nomEmp, prenomEmp)
FROM employe ;

CONCAT(NOMEMP,PRENOMEMP)
BigbossJames
LamaligneSylvie
PoncePierre
FaidézafairMarc
LebofJacky
BadsellerCorinne
PopolJean Marie
CrozacDamien
MaginJulien
RanoujaJamel
RagondinMichel
BalaizLucie
LegrandMarcel
BigoufBernard

1.13.3. Extraction de caractères


La fonction SUBSTR(expr, a[, b]) sert à extraire les caractères d'une chaîne. a représente le caractère de départ, et le paramètre optionnel b représente la longueur de la chaîne, s'il n'est pas spécifié, c'est la chaîne de longueur maximum qui est extraite. Si a est négatif, le décompte commence à partir de la fin de la chaîne.

Exemple : afficher les noms des employés à partir du deuxième caractère.

SELECT SUBSTR (nomEmp, 2)
FROM employe ;

SUBSTR(NOMEMP,2)
igboss
amaligne
once
aidézafair
ebof
adseller
opol
rozac
agin
anouja
agondin
alaiz
egrand
igouf
Exemple : afficher les 3 caractères à partir du deuxième des noms des employés.

SELECT SUBSTR (nomEmp, 2, 3)
FROM employe ;

SUB
igb
ama
onc
aid
ebo
ads
opo
roz
agi
ano
ago
ala
egr
igo
Exemple : afficher les 4 derniers caractères des noms des employés.

SELECT SUBSTR (nomEmp, -4)
FROM employe ;

SUBS
boss
igne
once
fair
ebof
ller
opol
ozac
agin
ouja
ndin
laiz
rand
gouf
Exemple : afficher les chaînes de 3 caractères à partir du quatrième en partant de la fin des noms des employés.

SELECT SUBSTR (nomEmp, -4, 3)
FROM employe ;

SUB
bos
ign
onc
fai
ebo
lle
opo
oza
agi
ouj
ndi
lai
ran
gou

1.13.4. Nombre de caractères d'une chaîne


La fonction LENGTH() compte le nombre de caractères composant une chaîne.
Exemple : n'afficher que les noms des employés ayant au plus 6 caractères.

SELECT nomEmp, LENGTH (nomEmp)
FROM employe
WHERE LENGTH (nomEmp) <= 6 ;

NOMEMPLENGTH(NOMEMP)
Ponce5
Lebof5
Popol5
Crozac6
Magin5
Balaiz6
Bigouf6

1.14. Manipulation des chiffres



1.14.1. Arrondir une valeur numérique


La fonction ROUND(expr, a) arrondit la valeur expr en fonction du nombre de décimales précisées a. Si a est omis, le valeur est arrondie à l'entier le plus près. Si a est négatif, ce sont les chiffres situés à gauche du point décimal (.) qui sont arrondis.

SELECT salaireEmp, ROUND (salaireEmp, 1)
FROM employe ;

SALAIREEMPROUND(SALAIREEMP,1)
15347,1215347,1
8975,028975
6891,576891,6
3815,543815,5
3902,83902,8
2895,122895,1
6410,956411
5214,055214,1
4578,124578,1
2410,512410,5
21082108
2510,542510,5
4519,294519,3
3891,913891,9
SELECT salaireEmp, ROUND (salaireEmp, -1)
FROM employe ;

SALAIREEMPROUND(SALAIREEMP,-1)
15347,1215350
8975,028980
6891,576890
3815,543820
3902,83900
2895,122900
6410,956410
5214,055210
4578,124580
2410,512410
21082110
2510,542510
4519,294520
3891,913890

1.14.2. Tronquer une valeur numérique


La fonction TRUNC(expr, a) tronque la valeur numérique à la décimale a spécifiée. Si a est omis, la partie décimale est tronquée. Si a est négatif, ce sont les chiffres situés à gauche du point décimal (.) qui sont tronqués.

SELECT salaireEmp, TRUNC (salaireEmp, 1)
FROM employe ;

SALAIREEMPTRUNC(SALAIREEMP,1)
15347,1215347,1
8975,028975
6891,576891,5
3815,543815,5
3902,83902,8
2895,122895,1
6410,956410,9
5214,055214
4578,124578,1
2410,512410,5
21082108
2510,542510,5
4519,294519,2
3891,913891,9
SELECT salaireEmp, TRUNC (salaireEmp, -2)
FROM employe ;

SALAIREEMPTRUNC(SALAIREEMP,-2)
15347,1215300
8975,028900
6891,576800
3815,543800
3902,83900
2895,122800
6410,956400
5214,055200
4578,124500
2410,512400
21082100
2510,542500
4519,294500
3891,913800

1.14.3. Caluler un modulo


Le modulo renvoie le reste d'une division de deux nombres. C'est la fonction MOD(a, b) qui permet de faire cela. MOD renverra donc le reste de a / b.
Exemple : afficher le reste de la division du salaire par la commission.

SELECT MOD(salaireEmp, commissionEmp)
FROM employe
WHERE commissionEmp IS NOT NULL ;

MOD(SALAIREEMP,COMMISSIONEMP)
1269,28
1181,8
250,52
3815,54
393
314,95
245,59
288,73

1.15. Manipulation des dates



1.15.1. Calcul du nombre de mois entre deux dates


La société ayant été fondée le 20/07/1995, on désire connaître le nombre de mois entre cette date et la date d'embauche de chaque employé. On utilise la fonction MONTHS_BETWEEN(date1, date2), cette fonction fait la différence date1 - date2.

SELECT nomEmp, prenomEmp, MONTHS_BETWEEN (dateEmbauche, '20/07/1995')
FROM employe ;

NOMEMPPRENOMEMPMONTHS_BETWEEN(DATEEMBAUCHE,'20/07/1995')
BigbossJames1,16129032
LamaligneSylvie1,4516129
PoncePierre27,7741935
FaidézafairMarc30,1612903
LebofJacky30,516129
BadsellerCorinne88,1612903
PopolJean Marie3,03225806
CrozacDamien3,32258065
MaginJulien3,74193548
RanoujaJamel5,61290323
RagondinMichel9,74193548
BalaizLucie20,5806452
LegrandMarcel4,93548387
BigoufBernard7,19354839

1.15.2. Ajout d'un nombre de mois à une date


Calculons, la date à laquelle les employés auront 15 ans d'expérience soit 180 mois dans cette société. On utilise la fonction ADD_MONTHS(date, a). Cette fonction renvoie la date obtenue en ajoutant a mois à date. a peut être un entier quelconque. Si le mois obtenu a moins de jours que le jour de date, le jour obtenu est le dernier du mois. a peut être négatif, les mois sont alors retranchés.

SELECT dateEmbauche, ADD_MONTHS (dateEmbauche, 180)
FROM employe ;

DATEEMBAADD_MONT
25/08/9525/08/10
03/09/9503/09/10
13/11/9713/11/12
25/01/9825/01/13
05/02/9805/02/13
25/11/0225/11/17
21/10/9521/10/10
30/10/9530/10/10
12/11/9512/11/10
08/01/9608/01/11
12/05/9612/05/11
07/04/9707/04/12
18/12/9518/12/10
26/02/9626/02/11

1.15.3. Utilisation de la date du jour


La fonction qui permet d'obtenir la date du jour est SYSDATE. Calculons l'ancienneté en nombre de mois de chaque employé.

SELECT nomEmp, prenomEmp, MONTHS_BETWEEN (SYSDATE , dateEmbauche)
FROM employe ;

Ici, la requête varie de jour en jour, le résultat ne sera pas affiché.


1.15.4. Fonction renvoyant le dernier jour du mois


La fonction LAST_DAY(date) renvoie la date du dernier jour du mois de date.

SELECT dateEmbauche, LAST_DAY (dateEmbauche)
FROM employe ;

DATEEMBALAST_DAY
25/08/9531/08/95
03/09/9530/09/95
13/11/9730/11/97
25/01/9831/01/98
05/02/9828/02/98
25/11/0230/11/02
21/10/9531/10/95
30/10/9531/10/95
12/11/9530/11/95
08/01/9631/01/96
12/05/9631/05/96
07/04/9730/04/97
18/12/9531/12/95
26/02/9629/02/96

1.15.5. Fonction renvoyant la date du prochain jour


La fonction NEXT_DAY(date, jour) renvoie la date du prochain jour de la semaine dont le nom est jour.
Exemple : afficher la date du prochain LUNDI suivant le date d'embauche de chaque employé.

SELECT dateEmbauche, NEXT_DAY (dateEmbauche, 'LUNDI')
FROM employe ;

DATEEMBANEXT_DAY
25/08/9528/08/95
03/09/9504/09/95
13/11/9717/11/97
25/01/9826/01/98
05/02/9809/02/98
25/11/0202/12/02
21/10/9523/10/95
30/10/9506/11/95
12/11/9513/11/95
08/01/9615/01/96
12/05/9613/05/96
07/04/9714/04/97
18/12/9525/12/95
26/02/9604/03/96

1.15.6. Arrondir une date


La fonction ROUND(date, precision) renvoie date arrondie à l'unité spécifiée dans precision. L'unité de précision est indiquée en utilisant un des masques de mise en forme de la date. On peut ainsi arrondir une date à l'année, au mois, à la minute,... Par défaut la précision est le jour. Les différentes précisions sont : 'YEAR', 'MONTH', 'DAY'. precision est optionnel.
Exemple : afficher le mois le plus prêt de chaque date d'embauche.

SELECT dateEmbauche, ROUND (dateEmbauche, 'MONTH')
FROM employe ;

DATEEMBAROUND(DA
25/08/9501/09/95
03/09/9501/09/95
13/11/9701/11/97
25/01/9801/02/98
05/02/9801/02/98
25/11/0201/12/02
21/10/9501/11/95
30/10/9501/11/95
12/11/9501/11/95
08/01/9601/01/96
12/05/9601/05/96
07/04/9701/04/97
18/12/9501/01/96
26/02/9601/03/96

1.15.7. Tronquer une date


La fonction TRUNC(date, precision) renvoie date tronquée à l'unité spécifiée dans précision. Les paramètres sont les même que ceux de la fonction ROUND. precision est optionnel.
Exemple : afficher les débuts de chaque mois des dates d'embauche.

SELECT dateEmbauche, TRUNC (dateEmbauche, 'MONTH')
FROM employe ;

DATEEMBATRUNC(DA
25/08/9501/08/95
03/09/9501/09/95
13/11/9701/11/97
25/01/9801/01/98
05/02/9801/02/98
25/11/0201/11/02
21/10/9501/10/95
30/10/9501/10/95
12/11/9501/11/95
08/01/9601/01/96
12/05/9601/05/96
07/04/9701/04/97
18/12/9501/12/95
26/02/9601/02/96

1.15.8. Changement de format des dates


Il est parfois utile de changer le format des dates, pour n'afficher que le nom du jour par exemple...

Liste des paramêtres les plus utiles :

  • 'cc' : siècle
  • 'yyyy' : année
  • 'yyy' : 3 derniers chiffres de l'année
  • 'yy' : 2 derniers chiffres de l'année
  • 'y' : dernier chiffre de l'année
  • 'q' : numéro du trimestre dans l'année
  • 'ww' : numéro de la semaine dans l'année
  • 'w' : numéro de la semaine dans le mois
  • 'mm' : numéro du mois
  • 'ddd' : numéro du jour dans l'année
  • 'dd' : numéro du jour dans le mois
  • 'd' : numéro du jour dans la semaine
  • 'hh' ou 'hh12' : heure (sur 12 heures)
  • 'hh24' : heure sur 24 heures
  • 'mi' : minutes
  • 'ss' : secondes
  • 'month' : nom du mois
  • 'mon' : nom du mois abrégé sur 3 lettres
  • 'day' : nom du jour
  • 'dy' : nom du jour abrégé sur 2 lettres
  • 'fm' : permet de supprimer les espaces ajoutés lors de la conversion par Oracle

Exemple : afficher les dates sous la forme : dimanche 02 juin 1998. Ici les jours n'ayant pas le même nombre de caractères (même chose pour les mois), Oracle comble ces différences avec des espaces, c'est pour cela que l'on utilise la paramêtre 'fm', vous pouvez essayer de supprimer ce paramêtre ou de le déplacer afin de visualiser son effet.

SELECT TO_CHAR(dateEmbauche, 'fmday dd month yyyy') "Format de date"
FROM employe
ORDER BY dateEmbauche ;

Format de date
vendredi 25 août 1995
dimanche 03 septembre 1995
samedi 21 octobre 1995
lundi 30 octobre 1995
dimanche 12 novembre 1995
lundi 18 décembre 1995
lundi 08 janvier 1996
lundi 26 février 1996
dimanche 12 mai 1996
lundi 07 avril 1997
jeudi 13 novembre 1997
dimanche 25 janvier 1998
jeudi 05 février 1998
lundi 25 novembre 2002
Il est possible d'utiliser d'autres caractères de séparation.
Exemple : afficher la date de ce type : di-02-jui-98.

SELECT TO_CHAR(dateEmbauche, 'dy-dd-mon-yy') "Format de date"
FROM employe
ORDER BY dateEmbauche ;


1.16. Trier les données


Il peut s'avérer très utile de pouvoir trier les données. Pour cela on utilise la clause ORDER BY qui doit être placée à la fin de l'ordre SELECT. Elle peut prendre pour paramètre ASC (tri croissant par défaut) ou DESC (tri décroissant).


1.16.1. Tri simple


Afficher les noms des employés, avec un tri décroissant sur la date d'embauche.

SELECT prenomEmp, nomEmp, dateEmbauche
FROM employe
ORDER BY dateEmbauche DESC ;

PRENOMEMPNOMEMPDATEEMBA
CorinneBadseller25/11/02
JackyLebof05/02/98
MarcFaidézafair25/01/98
PierrePonce13/11/97
LucieBalaiz07/04/97
MichelRagondin12/05/96
BernardBigouf26/02/96
JamelRanouja08/01/96
MarcelLegrand18/12/95
JulienMagin12/11/95
DamienCrozac30/10/95
Jean MariePopol21/10/95
SylvieLamaligne03/09/95
JamesBigboss25/08/95

1.16.2. Tri multiple


Afficher les noms et prénoms des employés ordonnés par sexe (croissant) puis par prénom (décroissant).

SELECT prenomEmp, nomEmp
FROM employe
ORDER BY sexeEmp, prenomEmp DESC ;

Il aurait été possible de mettre ORDER BY sexeEmp ASC, prenomEmp DESC ;

PRENOMEMPNOMEMP
SylvieLamaligne
LucieBalaiz
CorinneBadseller
PierrePonce
MichelRagondin
MarcelLegrand
MarcFaidézafair
JulienMagin
Jean MariePopol
JamesBigboss
JamelRanouja
JackyLebof
DamienCrozac
BernardBigouf

1.16.3. Tri sur les Alias de colonne


Affichons les employés et leur salaire annuel, primes exclues. Le tri (décroissant) se fera sur le salaire annuel.

SELECT prenomEmp, nomEmp, salaireEmp * 12 SalaireAnnuel
FROM employe
ORDER BY SalaireAnnuel DESC ;

PRENOMEMPNOMEMPSALAIREANNUEL
JamesBigboss184165,44
SylvieLamaligne107700,24
PierrePonce82698,84
Jean MariePopol76931,4
DamienCrozac62568,6
JulienMagin54937,44
MarcelLegrand54231,48
JackyLebof46833,6
BernardBigouf46702,92
MarcFaidézafair45786,48
CorinneBadseller34741,44
LucieBalaiz30126,48
JamelRanouja28926,12
MichelRagondin25296

1.16.4. Tri sur les numéros de colonne


Il est également possible de faire le tri en indiquant le numéro de colonne tel qu'elle est positionnée dans la clause SELECT. Le premier élèment porte le numéro 1, le second le numéro 2 et ainsi de suite... reprenons l'exemple précedént.

SELECT prenomEmp, nomEmp, salaireEmp * 12
FROM employe
ORDER BY 3 DESC ;


1.17. Utilisation de la fonction de groupe


Les fonctions de groupe agissent sur les groupes de lignes et donnent un résultat par groupe.
La liste des fonctions de base utiles :

  • AVG() : calcule la moyenne.
  • COUNT() : compte le nombre de lignes non nulles, les doublons sont comptabilisés.
    COUNT DISTINCT() permet de ne pas les comptabiliser.
  • MAX() : trouve le maximum.
  • MIN() : trouve le minimum.
  • SUM() : calcule la somme.

Attention, la clause GROUP BY doit inclure toutes les colonnes de la liste SELECT qui ne figure pas dans des fonctions de groupe.


1.17.1. Fonction de groupe sur une seule colonne


Calculons le salaire moyen par sexe.

SELECT sexeEmp, AVG (salaireEmp)
FROM employe
GROUP BY sexeEmp ;

SAVG(SALAIREEMP)
F4793,56
H5371,80545

1.17.2. Fonction de groupe sur plusieurs colonnes


Calculons le salaire maximum et le nombre d'employé d'abord par département puis par fonction.

SELECT idDep, idFonc, MAX (salaireEmp), COUNT (nomEmp)
FROM employe
GROUP BY idDep, idFonc ;

IDDEPIDFONCMAX(SALAIREEMP)COUNT(nomEmp)
1115347,121
128975,022
236410,951
345214,051
364578,121
472410,512
482510,541
492895,121
654519,292
793902,82
Il aurait été possible de mettre COUNT(*) à la place de COUNT(nomEmp) car aucune ligne de nom n'est nulle.


1.17.3. Exclusion de groupe


Seuls les groupes qui correspondent à la condition de la clause HAVING seront affichés. HAVING ne peut être utilisé sans GROUP BY.

Calculons par exemple la moyenne des salaires des hommes de chaque département où le salaire minimal est supérieur à 4000 Euros.

SELECT idDep, AVG (salaireEmp)
FROM employe
GROUP BY idDep
HAVING MIN (salaireEmp) > 4000 ;

IDDEPAVG(SALAIREEMP)
110404,57
26410,95
34896,085

2. Les requêtes SELECT avec jointure


Les jointures servent à extraire des données de plusieurs tables tout en utilisant les liens qui les lient entre elles.


2.1. Une jointure naturelle pour commencer


Exemple : Afficher les noms des employés et leur fonction. Cette jointure ne peut s'effectuer que si les clés primaires et étrangères (liées entre elles) portent le même nom et ont le même type de données. Oracle recherche donc les colonnes portant le même nom, et utilise l'égalité comme critère de jointure. Dans cet exemple, la condition de jointure est donc : employe.idFonc = fonction.idFonc. En effet, la clé primaire de la table fonction porte le même nom que la clé étrangère de la table employe.

SELECT prenomEmp, nomEmp, nomFonc
FROM employe
    NATURAL JOIN fonction ;

PRENOMEMPNOMEMPNOMFONC
JamesBigbossPrésident
SylvieLamaligneDirecteur
PierrePonceDirecteur
MarcFaidézafairVendeur
JackyLebofVendeur
CorinneBadsellerVendeur
Jean MariePopolChercheur
DamienCrozacAnalyste
JulienMaginDessinateur
JamelRanoujaTourneur
MichelRagondinTourneur
LucieBalaizFraiseur
MarcelLegrandTesteur
BernardBigoufTesteur
Voici un exemple sur plusieurs tables : Afficher noms, prénoms, fonctions, départements et villes de chaque employé.

SELECT prenomEmp, nomEmp, nomFonc, nomDep, nomLoc
FROM employe
    NATURAL JOIN fonction
    NATURAL JOIN departement
    NATURAL JOIN localisation ;

PRENOMEMPNOMEMPNOMFONCNOMDEPNOMLOC
JamesBigbossPrésidentDirectionBesancon
SylvieLamaligneDirecteurDirectionBesancon
PierrePonceDirecteurDirectionBesancon
MarcFaidézafairVendeurVenteBesancon
JackyLebofVendeurVenteBesancon
CorinneBadsellerVendeurFabricationVesoul
Jean MariePopolChercheurEtudeDole
DamienCrozacAnalysteAnalyseBelfort
JulienMaginDessinateurAnalyseBelfort
JamelRanoujaTourneurFabricationVesoul
MichelRagondinTourneurFabricationVesoul
LucieBalaizFraiseurFabricationVesoul
MarcelLegrandTesteurEssaiDijon
BernardBigoufTesteurEssaiDijon

2.2. La jointure interne


Avec celle-ci, il est possible de spécifier quelle est la condition de jointure, ce qui est idéal lorsque les noms de clé ne sont pas identiques. Reprenons le premier exemple du point précédent, INNER JOIN sert à préciser qu'il s'agit d'une jointure de type interne et ON permet de définir la condition de jointure. Le résultat est évidemment le même. Ici, il n'y a que des noms de clés identiques, il est donc plus intéressant d'utiliser la jointure naturelle. Cet exemple n'est fourni qu'à titre purement pédagogique, afin de bien vous présenter son fonctionnement.

SELECT prenomEmp, nomEmp, nomFonc
FROM employe
    INNER JOIN fonction ON employe.idFonc = fonction.idFonc ;

Reprenons le deuxième exemple du point précédent :

SELECT prenomEmp, nomEmp, nomFonc, nomDep, nomLoc
FROM employe
    INNER JOIN fonction ON employe.idFonc = fonction.idFonc
    INNER JOIN departement ON employe.idDep = departement.idDep
    INNER JOIN localisation ON departement.idLoc = localisation.idLoc ;


2.2.1. Utiliser les alias de table


Cette notation est très utilisée avec les jointures, elle sert à simplifier les requêtes, diminuer la longueur du code. Ici, on reprend le deuxième exemple du point précédent en utilisant les alias de tables.

SELECT prenomEmp, nomEmp, nomFonc, nomDep, nomLoc
FROM employe e
    INNER JOIN fonction f ON e.idFonc = f.idFonc
    INNER JOIN departement d ON e.idDep = d.idDep
    INNER JOIN localisation l ON d.idLoc = l.idLoc ;

Le résultat en sera le même.


2.3. La jointure externe


Elle permet d'afficher des enregistrements sans lien direct avec une autre table. On peut donc visualiser les lignes qui n'ont pas de liaison avec des informations contenues dans une autre table jointe. Ici, le département Prototype ne possède aucun employé.

Il suffit d'utiliser la commande OUTER JOIN précédée d'un des éléments suivants : LEFT, RIGHT, ou FULL. La condition de jointure est toujours exprimée à l'aide de ON.
Dans tous les cas, les élèments correspondant à la condition de jointure sont affichés, puis suivant les paramètres suivants :

  • LEFT : affiche tous les éléments de la table située à gauche de OUTER JOIN n'ayant pas satisfait à la condition de jointure.
  • RIGHT : affiche tous les éléments de la table située à droite de OUTER JOIN n'ayant pas satisfait à la condition de jointure.
  • FULL : affiche tous les éléments des tables situées à gauche et à droite de OUTER JOIN n'ayant pas satisfait à la condition de jointure.

Ici, nous allons afficher tous les éléments de la table département. Le nom étant situé à gauche, on utilise donc le paramètre LEFT.

SELECT nomEmp, nomDep
FROM departement d
    LEFT OUTER JOIN employe e ON d.idDep = e.idDep ;

NOMEMPNOMDEP
BigbossDirection
LamaligneDirection
PonceDirection
FaidézafairVente
LebofVente
BadsellerFabrication
PopolEtude
CrozacAnalyse
MaginAnalyse
RanoujaFabrication
RagondinFabrication
BalaizFabrication
LegrandEssai
BigoufEssai

Prototype
Il reviendrait au même de faire cela :

SELECT nomEmp, nomDep
FROM employe e
    RIGHT OUTER JOIN departement d ON d.idDep = e.idDep ;


2.4. La jointure croisée ou produit cartésien


Fait correspondre à chaque ligne d'une table, toutes les lignes d'une autre table. Le nombre de lignes du résultat sera le produit des nombres de lignes de chaque table.

Exemple : produit cartésien de département et localisation.

SELECT nomDep, nomLoc
FROM departement
    CROSS JOIN localisation ;


2.4. L'auto-jointure


Elle permet de lier une table à elle-même. Un employé peut avoir un supérieur qui est lui-même un employé, ils se trouvent donc dans la même table employe. Ici on veut trouver qui est le supérieur de Damien Crozac. Etant donné que l'on utilise deux fois la même table, l'utilisation des alias de table est obligatoire.

SELECT emp.prenomEmp || ' ' || emp.nomEmp || ' a pour supérieur ' || sup.prenomEmp || ' ' || sup.nomEmp "Un employé et son supérieur :"
FROM employe emp
    INNER JOIN employe sup ON emp.supEmp = sup.idEmp
WHERE emp.nomEmp = 'Crozac'
    AND emp.prenomEmp = 'Damien' ;

Un employé et son supérieur :
Damien Crozac a pour supérieur Jean Marie Popol

3. Les requêtes imbriquées


Elles sont très utilisées. Une requête à besoin du résultat d'une seconde pour pouvoir s'exécuter.
Un petit exemple s'impose : on désire connaître toutes les personnes du même sexe que Corinne Badseller. Il faut d'abord effectuer la requête qui déterminera le sexe de Corinne Badseller, pour ensuite pouvoir exécuter le reste de la requête, c'est à dire afficher toutes les personnes du sexe retourné.


3.1. Requêtes imbriquées multi-lignes


La sous-interrogation ramène plusieurs lignes, il faut donc utiliser un opérateur multi-lignes.

  • IN : doit être égal à un des résultats de la sous-requête.

  • ANY : doit satisfaire au moins à un résultat de la sous-requête.

< ANY : signifie inférieur à au moins une des valeurs, donc inférieur au maximum.
> ANY : signifie supérieur à au moins une des valeurs, donc supérieur au minimum.
= ANY : doit être égal à une des valeurs, cela revient à faire un IN.

  • ALL : doit satisfaire à tous les résultats de la sous-requête.

< ALL : signifie inférieur à tous les résultats retournés par la sous-requête.
> ALL : signifie supérieur à tous les résultats retournés par la sous-requête.


3.1.1. Requête avec IN


Afficher les noms, prénoms, salaire, et département des personnes ayant le plus petit salaire de leur département.

SELECT prenomEmp, nomEmp, salaireEmp, nomDep
FROM employe e JOIN departement d
    ON e.idDep = d.idDep
WHERE salaireEmp IN (
    SELECT MIN (salaireEmp)
    FROM employe
    GROUP BY idDep
) ;

PRENOMEMPNOMEMPSALAIREEMPNOMDEP
MichelRagondin2108Fabrication
MarcFaidézafair3815,54Vente
BernardBigouf3891,91Essai
JulienMagin4578,12Analyse
Jean MariePopol6410,95Etude
PierrePonce6891,57Direction

3.1.2. Requête avec ANY


Afficher les nom, prénom, salaire, et fonction des employés gagnant moins qu'un des employés de la fonction Vendeur .

SELECT prenomEmp, nomEmp, salaireEmp, nomFonc
FROM employe e JOIN fonction f
    ON e.idFonc = f.idFonc
WHERE salaireEmp < ANY (
    SELECT salaireEmp
    FROM employe e JOIN fonction f
        ON e.idFonc = f.idFonc
    WHERE nomFonc = 'Vendeur'
);

PRENOMEMPNOMEMPSALAIREEMPNOMFONC
MarcFaidézafair3815,54Vendeur
CorinneBadseller2895,12Vendeur
JamelRanouja2410,51Tourneur
MichelRagondin2108Tourneur
LucieBalaiz2510,54Fraiseur
BernardBigouf3891,91Testeur

3.1.3. Requête avec ALL


Afficher les nom, prénom, salaire, et fonction des employés gagnant moins que toutes les moyennes des salaires de chaque département.

SELECT prenomEmp, nomEmp, salaireEmp, nomFonc
FROM employe e, fonction f
WHERE e.idFonc = f.idFonc
    AND salaireEmp < ALL (
        SELECT AVG (salaireEmp)
        FROM employe
        GROUP BY idDep
    );

PRENOMEMPNOMEMPSALAIREEMPNOMFONC
JamelRanouja2410,51Tourneur
MichelRagondin2108Tourneur

3.2. Requêtes imbriquées multi-colonnes


Elles ont la même utilité que les requêtes imbriquées, sauf qu'elles peuvent comparer plusieurs colonnes.
Exemple : afficher les nom, prénom, fonction et date d'embauche, des employés ayant le même salaire et la même commission que James Bigboss. Cet exemple qui ne retournera qu'un seul résultat, n'est fourni qu'à titre pédagogique : il y a en effet une autre méthode plus simple, que vous aurez trouvé, bien entendu...

SELECT nomEmp, prenomEmp, nomFonc, dateEmbauche
FROM employe
    NATURAL JOIN fonction
WHERE (salaireEmp, commissionEmp) IN (
    SELECT salaireEmp, commissionEmp
    FROM employe
    WHERE nomEmp = 'Bigboss'
        AND prenomEmp = 'James'
    );

Cette requête aurait pu être remplacée par :

SELECT nomEmp, prenomEmp, nomFonc, dateEmbauche
FROM employe
    NATURAL JOIN fonction
WHERE salaireEmp = (
    SELECT salaireEmp
    FROM employe
    WHERE nomEmp = 'Bigboss'
        AND prenomEmp = 'James'
    )
    AND commissionEmp = (
        SELECT commissionEmp
        FROM employe
        WHERE nomEmp = 'Bigboss'
            AND prenomEmp = 'James'
        ) ;
   

NOMEMPPRENOMEMPNOMFONCDATEEMBAUCHE
BigbossJamesPrésident25/08/95

3.3. Requêtes imbriquées synchronisées


Elles sont conçues pour un traitement ligne à ligne : chaque sous-interrogation est exécutée une seule fois pour chaque ligne de la requête principale.
Exemple : Afficher les nom et prénom des employés dont le salaire est supérieur au salaire moyen de leur département.

SELECT nomEmp, prenomEmp, salaireEmp, nomDep
FROM departement d
    INNER JOIN employe e1 ON d.idDep = e1.idDep
WHERE salaireEmp > (
    SELECT AVG (salaireEmp)
    FROM employe e2
    WHERE e1.idDep = e2.idDep
);

NOMEMPPRENOMEMPSALAIREEMPNOMDEP
BigbossJames15347,12Direction
LebofJacky3902,8Vente
BadsellerCorinne2895,12Fabrication
CrozacDamien5214,05Analyse
BalaizLucie2510,54Fabrication
LegrandMarcel4519,29Essai

3.3.1. Utilisation de l'opérateur EXISTS


Cet opérateur renvoie TRUE si au moins une ligne de résultat est trouvée. On ne désire pas connaître le résultat mais uniquement savoir s'il existe.
Exemple : Afficher les noms des départements ayant au moins un employé.

SELECT nomDep
FROM departement d
WHERE EXISTS (
    SELECT idEmp
    FROM employe e
    WHERE e.idDep = d.idDep
);

NOMDEP
Direction
Etude
Analyse
Fabrication
Essai
Vente
Il est possible d'utiliser le NOT.
Exemple : Afficher les nom et prénom des employés n'ayant personne sous leur responsabilité.

SELECT nomEmp, prenomEmp
FROM employe e1
WHERE NOT EXISTS (
    SELECT idEmp
    FROM employe e2
    WHERE e1.idEmp = e2.supEmp
);

NOMEMPPRENOMEMP
FaidézafairMarc
LebofJacky
BadsellerCorinne
RanoujaJamel
RagondinMichel
BalaizLucie
LegrandMarcel
BigoufBernard

4. Les opérateurs ensemblistes


L'union, l'intersection, la différence, sont des opérateurs très simples. Ils s'utilisent avec au minimum deux SELECT générant le même nombre de colonnes.


4.1. L'opérateur UNION


Affichons les noms et prénoms des employés embauchés après le 01/05/96, et ceux de sexe féminin. Il est tout à fait possible de réaliser cette requête avec l'opérateur logique OR :

SELECT nomEmp, prenomEmp, dateEmbauche, sexeEmp
FROM employe
WHERE dateEmbauche > '01/05/96'
    OR sexeEmp = 'F'
ORDER BY dateEmbauche ;

Mais nous allons nous intéresser à UNION. Cet opérateur est commutatif, c'est à dire que l'ordre des termes n'a pas d'importance. A UNION B = B UNION A.

SELECT nomEmp, prenomEmp, dateEmbauche, sexeEmp
FROM employe
WHERE dateEmbauche > '01/05/96'
    UNION
SELECT nomEmp, prenomEmp, dateEmbauche, sexeEmp
FROM employe
WHERE sexeEmp = 'F'
ORDER BY dateEmbauche ;

NOMEMPPRENOMEMPDATEEMBAUCHESEXEEMP
LamaligneSylvie03/09/95 F
RagondinMichel12/05/96 H
BalaizLucie07/04/97 F
PoncePierre13/11/97 H
FaidézafairMarc25/01/98 H
LebofJacky05/02/98 H
BadsellerCorinne25/11/02 F

4.2. L'opérateur INTERSECT


Affichons les noms et prénoms des employés à la fois embauchés après le 01/05/96 et de sexe féminin. Il est tout à fait possible de réaliser cette requête avec l'opérateur logique AND :

SELECT nomEmp, prenomEmp, dateEmbauche, sexeEmp
FROM employe
WHERE dateEmbauche > '01/05/96'
    AND sexeEmp = 'F'
ORDER BY dateEmbauche ;

Mais nous allons nous interresser à INTERSECT. Cet opérateur est aussi commutatif.

SELECT nomEmp, prenomEmp, dateEmbauche, sexeEmp
FROM employe
WHERE dateEmbauche > '01/05/96'
    INTERSECT
SELECT nomEmp, prenomEmp, dateEmbauche, sexeEmp
FROM employe
WHERE sexeEmp = 'F'
ORDER BY dateEmbauche ;

NOMEMPPRENOMEMPDATEEMBAUCHESEXEEMP
BalaizLucie07/04/97F
BadsellerCorinne25/11/02F

4.3. L'opérateur MINUS


Cet opérateur n'est pas commutatif, nous allons le voir par deux exemples opposés. Affichons les noms des personnes vérifiant la différence entre le groupe de sexe féminin et le groupe des personnes embauchées après le 01/05/96.

SELECT nomEmp, prenomEmp, dateEmbauche, sexeEmp
FROM employe
WHERE sexeEmp = 'F'
    MINUS
SELECT nomEmp, prenomEmp, dateEmbauche, sexeEmp
FROM employe
WHERE dateEmbauche > '01/05/96'
ORDER BY dateEmbauche ;

NOMEMPPRENOMEMPDATEEMBAUCHESEXEEMP
LamaligneSylvie03/09/95F
Intervertissons maintenant les termes.

SELECT nomEmp, prenomEmp, dateEmbauche, sexeEmp
FROM employe
WHERE dateEmbauche > '01/05/96'
    MINUS
SELECT nomEmp, prenomEmp, dateEmbauche, sexeEmp
FROM employe
WHERE sexeEmp = 'F'
ORDER BY dateEmbauche ;

NOMEMPPRENOMEMPDATEEMBAUCHESEXEEMP
RagondinMichel12/05/96H
PoncePierre13/11/97H
FaidézafairMarc25/01/98H
LebofJacky05/02/98H
Nous voyons donc que la soustraction n'est pas commutative.


5. Quelques fonctions propres à Oracle



5.1. Afficher la liste des tables de l'utilisateur connecté


SELECT TABLE_NAME
FROM USER_TABLES ;

Ce qui affichera les tables FONCTION, LOCALISATION, DEPARTEMENT, EMPLOYE et éventuellement les autres que vous auriez déjà créer.


5.2. Afficher la liste des contraintes


Afficher les nom, type et condition des contraintes d'une table. Le nom de la table doit être en lettres majuscules. Ici, on affiche les contraintes de la table employe.

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYE' ;

Afficher les contraintes et les colonnes correspondantes d'une table. Le nom doit également être en lettres majuscules.

SELECT CONSTRAINT_NAME, COLUMN_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMPLOYE' ;


6. Faites le point avec quelques requêtes


Afficher les noms, prénoms, salaire, commission et salaire + commission de tous les vendeurs.

SELECT nomEmp, prenomEmp, salaireEmp, NVL (commissionEmp, 0), salaireEmp + NVL (commissionEmp, 0)
FROM employe
    NATURAL JOIN fonction
WHERE nomFonc = 'Vendeur' ;

Afficher les noms et prénoms des vendeurs par ordre décroissant du rapport commission / salaire.

SELECT nomEmp, prenomEmp, NVL (commissionEmp, 0) / salaireEmp ratio
FROM employe
    NATURAL JOIN fonction
WHERE nomFonc = 'Vendeur'
ORDER BY ratio DESC ;

Afficher les noms et prénoms des vendeurs ayant une commission inférieure à 25% de leur salaire.

SELECT nomEmp, prenomEmp
FROM employe
    NATURAL JOIN fonction
WHERE nomFonc = 'Vendeur'
    AND commissionEmp < salaireEmp * 0.25 ;

Afficher le nombre d'employés du département Fabrication.

SELECT COUNT (nomEmp)
FROM departement
    NATURAL JOIN employe
WHERE nomDep = 'Fabrication' ;

Afficher le nombre d'employés de chaque département.

SELECT nomDep, COUNT (nomEmp)
FROM employe
    NATURAL JOIN departement
GROUP BY nomDep ;

Afficher le nombre d'employés touchant une commission.

SELECT COUNT (commissionEmp)
FROM employe ;

Afficher le salaire moyen par fonction, sans tenir compte des commissions.

SELECT nomFonc, AVG (salaireEmp)
FROM employe
    NATURAL JOIN fonction
GROUP BY nomFonc ;

Afficher le total des salaires du département Direction.

SELECT SUM (salaireEmp)
FROM employe
    NATURAL JOIN departement
WHERE nomDep = 'Direction' ;

Afficher les noms, prénoms des employés et leur département.

SELECT nomEmp, prenomEmp, nomDep
FROM employe
    NATURAL JOIN departement ;

Afficher les nom et prénom, fonction et salaire de l'employé ayant le salaire le plus élevé.

SELECT nomEmp, prenomEmp, nomFonc, salaireEmp
FROM employe
    NATURAL JOIN fonction
WHERE salaireEmp = (
    SELECT MAX (salaireEmp)
    FROM employe
) ;

Afficher les noms, prénoms des employés gagnant plus que Bernard Bigouf.

SELECT nomEmp, prenomEmp
FROM employe
WHERE salaireEmp > (
    SELECT salaireEmp
    FROM employe
    WHERE nomEmp = 'Bigouf'
        AND prenomEmp = 'Bernard'
    ) ;

Afficher les noms, prénoms des employés ayant la même fonction que Marc Faidézafair (id : 4).

SELECT nomEmp, prenomEmp
FROM employe
WHERE idFonc = (
    SELECT idFonc
    FROM employe
    WHERE idEmp = 4
    )
    AND idEmp <> 4 ;

Afficher les noms, prénoms des employés ayant le même supérieur que Marcel Legrand (id : 13).

SELECT nomEmp, prenomEmp
FROM employe
WHERE supEmp = (
    SELECT supEmp
    FROM employe
    WHERE idEmp = 13
    )
    AND idEmp <> 13 ;

Afficher les noms, prénoms et fonction des employés ayant même supérieur et même fonction que Marcel Legrand (id : 13).

SELECT nomEmp, prenomEmp, nomFonc
FROM employe
    NATURAL JOIN fonction
WHERE (supEmp, idFonc) IN (
    SELECT supEmp, idFonc
    FROM employe
    WHERE idEmp = 13
    )
    AND idEmp <> 13 ;

Afficher les noms, prénoms des employés embauchés avant tous les employés du département Analyse.

SELECT nomEmp, prenomEmp
FROM employe
WHERE dateEmbauche < (
    SELECT MIN (dateEmbauche)
    FROM employe
        NATURAL JOIN departement
    WHERE nomDep = 'Analyse'
);

Afficher les nom, prénom et salaire des employés qui gagnent plus que le salaire moyen et qui travaillent dans un département ayant au moins un employé dont le nom contient un 'a' ;

SELECT prenomEmp, nomEmp, salaireEmp
FROM employe
WHERE salaireEmp > (
    SELECT AVG (salaireEmp)
    FROM employe
    )
    AND idDep IN (
        SELECT idDep
        FROM employe
        WHERE nomEmp LIKE '%a%'
            OR nomEmp LIKE 'A%'
        );
   

Afficher les nom, prénom salaire et fonction des employés gagnant plus que tous les employés du département Analyse et trier les résultats par ordre décroissant des salaires.

SELECT nomEmp, prenomEmp, salaireEmp, nomFonc
FROM employe
    NATURAL JOIN fonction
WHERE salaireEmp > ALL (
    SELECT salaireEmp
    FROM employe
        NATURAL JOIN departement
    WHERE nomDep = 'Analyse'
    )
ORDER BY salaireEmp DESC ;


7. Conclusion


Maintenant que vous savez sélectionner des données, vous pouvez passer au châpitre suivant : la mise à jour des données.



Ce document est issu de http://www.developpez.com et reste la propriété exclusive de son auteur.
La copie, modification et/ou distribution par quelque moyen que ce soit est soumise à l'obtention préalable de l'autorisation de l'auteur.
Contacter le responsable de la rubrique SGBD & SQL