Langage de requête SQL

2-ème année IMA – S4

Année universitaire : 2024-2025

Pr. LAHMIDI Ayoub Pr. MERZOUK Soukaina lahmidiay@[Link]

L'Ordre SELECT
Élémentaire
Pr. MERZOUK Soukaina 2
Objectifs
A la fin de ce chapitre, vous saurez :
▪ Énumérer toutes les possibilités de l’ordre SQL SELECT
▪ Exécuter un ordre SELECT élémentaire
▪ Faire la différence entre les ordres SQL et les
commandes SQL*Plus

Pr. MERZOUK Soukaina 3

Les Possibilités de l'Ordre SQL SELECT
Sélection Projection

Table 1 Table 1

Jointure

Table 1 Table 2
Pr. MERZOUK Soukaina
Ordre SELECT Élémentaire

SELECT [DISTINCT] {*, column [alias],...}

FROM table;

▪ SELECT indique quelles colonnes rapporter

▪ FROM indique dans quelle table rechercher

mot-clé = mot isolé, clause = bloc de commande, ordre = phrase complète.

Pr. MERZOUK Soukaina 5

Écriture des Ordres SQL
➢Les ordres SQL peuvent être écrits indifféremment en
majuscules et/ou minuscules.
➢Les ordres SQL peuvent être écrits sur plusieurs lignes.
➢Les mots-clés ne doivent pas être abrégés ni scindés sur
deux lignes différentes.
➢Les clauses sont généralement placées sur des lignes
distinctes.
Pr. MERZOUK Soukaina 6
Sélection de Toutes les Colonnes

SQL> SELECT *
2 FROM dept;

DEPTNO DNAME LOC

--------- -------------- ------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Pr. MERZOUK Soukaina 7

Sélection d’Une ou Plusieurs Colonnes Spécifiques

SQL> SELECT deptno, loc

2 FROM dept;
DEPTNO LOC
--------- ------------
10 NEW YORK
20 DALLAS
30 CHICAGO
40 BOSTON

Pr. MERZOUK Soukaina 8

Valeurs par Défaut des En-têtes de Colonne

➢Justification par défaut

▪ A gauche : date et données alphanumériques
▪ A droite : données numériques
➢Affichage par défaut : en majuscules

Pr. MERZOUK Soukaina 9

Expressions Arithmétiques
Possibilité de créer des expressions avec des données de type NUMBER et
DATE au moyen d’opérateurs arithmétiques. Opérateur Description
Exemple + Addition
➢SQL> SELECT ename, sal, sal+300 - Soustraction
➢ 2 FROM emp;
* Multiplication
/ Division

Pr. MERZOUK Soukaina 10

Priorité des Opérateurs * + / -
➢La multiplication et la division ont priorité sur l’addition et la soustraction.
➢A niveau de priorité identique, les opérateurs sont évalués de gauche à
droite.
➢Les parenthèses forcent la priorité d’évaluation et permettent de clarifier les
ordres.
Exemple
SQL> SELECT ename, sal, 12*sal+100
2 FROM emp;
À faire:
SQL> SELECT ename, sal, 12*(sal+100)
2 FROM emp;
Pr. MERZOUK Soukaina 11
La Valeur NULL
➢NULL représente une valeur non disponible, non affectée, inconnue ou
inapplicable.
➢La valeur NULL est différente du zéro ou de l’espace.
SQL> SELECT ename, job, comm
2 FROM emp;
ENAME JOB COMM
---------- --------- ---------
KING PRESIDENT
BLAKE MANAGER
...
TURNER SALESMAN 0
...
14 rows selected.
Pr. MERZOUK Soukaina 12
Valeurs NULL dans les Expressions Arithmétiques
➢Les expressions arithmétiques comportant une valeur NULL sont
évaluées à NULL

SQL> select ename , 12*sal+comm

2 from emp
3 WHERE ename='KING';

ENAME 12*SAL+COMM
---------- -----------
KING

Pr. MERZOUK Soukaina 13

L’Alias de Colonne
➢Renomme un en-tête de colonne
➢Est utile dans les calculs
➢Suit immédiatement le nom de la colonne ; le mot-clé AS placé entre le nom
et l’alias est optionnel
➢Doit obligatoirement être inclus entre guillemets s’il contient des espaces,
des caractères spéciaux ou si les majuscules/minuscules doivent être
différenciées

Pr. MERZOUK Soukaina 14

Utilisation des Alias de Colonnes
SQL> SELECT ename AS name, sal salary
2 FROM emp;

NAME SALARY
------------- ---------
...
SQL> SELECT ename "Name",
2 sal*12 "Annual Salary"
3 FROM emp;
Name Annual Salary
------------- -------------
...
Pr. MERZOUK Soukaina 15
L’Opérateur de Concaténation
➢Concatène des colonnes ou chaînes de caractères avec d’autres colonnes
➢Est représenté par deux barres verticales (||)
➢La colonne résultante est une expression caractère
SQL> SELECT ename||job AS "Employees"
2 FROM emp;
Employees
-------------------
KINGPRESIDENT
BLAKEMANAGER
CLARKMANAGER
JONESMANAGER
MARTINSALESMAN
...
14 rows selected.
Pr. MERZOUK Soukaina 16
Littéral
➢Un littéral est un caractère, une expression, ou un nombre inclus dans la
liste SELECT.
➢Les valeurs littérales de type date et caractère doivent être placées
entre simples quotes.
➢Chaque littéral apparaît sur chaque ligne ramenée.
Employee Details
---------------------
KING is a PRESIDENT
SQL> SELECT ename ||' '||'is a'||' '||job BLAKE is a MANAGER
2 AS "Employee Details" CLARK is a MANAGER
3 FROM emp; JONES is a MANAGER
MARTIN is a SALESMAN
...
14 rows selected.

Pr. MERZOUK Soukaina 17

Doublons
➢Par défaut, le résultat d’une requête affiche toutes les lignes, y compris
DEPTNO
les doublons. ---------
10
SQL> SELECT deptno 30
10
2 FROM emp; 20
...
14 rows selected.

➢Pour éliminer les doublons il faut ajouter le mot-clé DISTINCT à la

clause SELECT.
DEPTNO
---------
SQL> SELECT DISTINCT deptno 10
2 FROM emp; 20
30

Pr. MERZOUK Soukaina 18

Affichage de la Structure d’une Table
➢Utilisez la commande SQL*Plus DESCRIBE pour afficher la structure
d’une table.
DESC[RIBE] tablename

Exemple
SQL> DESCRIBE dept
Name Null? Type
----------------- -------- ----
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

Pr. MERZOUK Soukaina 19

Commandes de Fichiers SQL*Plus

➢SAVE filename
➢GET filename
➢START filename
➢@ filename
➢EDIT filename : fichier [Link]
➢SPOOL filename
➢EXIT

Pr. MERZOUK Soukaina 20

Résumé

SELECT [DISTINCT] {*,column[alias],...}

FROM table;

L’environnement SQL*Plus permet :

▪ D’exécuter des ordres SQL
▪ D’éditer des ordres SQL

Pr. MERZOUK Soukaina 21

Sélection et Tri des
Lignes Retournées par
un SELECT
Pr. MERZOUK Soukaina 22
Objectifs
➢A la fin de ce chapitre, vous saurez :
▪ Limiter le nombre de lignes retournées par une requête
▪ Trier les lignes retournées par une requête

Pr. MERZOUK Soukaina 23

Sélectionner les Lignes
EMP
EMPNO ENAME JOB ... DEPTNO
“…rechercher tous
7839 KING PRESIDENT 10 les employés du
7698 BLAKE MANAGER 30 département 10”
7782 CLARK MANAGER 10
7566 JONES MANAGER 20
...

EMP
EMPNO ENAME JOB ... DEPTNO

7839 KING PRESIDENT 10

7782 CLARK MANAGER 10
7934 MILLER CLERK 10

Pr. MERZOUK Soukaina 24

Sélectionner les Lignes
➢Restreindre la sélection au moyen de la clause WHERE.
SELECT [DISTINCT] {*, column [alias], ...}
FROM table
[WHERE condition(s)];

➢La clause WHERE se place après la clause FROM.

ENAME JOB DEPTNO
SQL> SELECT ename, job, deptno ---------- --------- ---------
2 FROM emp JAMES CLERK 30
3 WHERE job='CLERK'; SMITH CLERK 20
ADAMS CLERK 20
MILLER CLERK 10

Pr. MERZOUK Soukaina 25

Chaînes de Caractères et Dates
➢Les constantes chaînes de caractères et dates doivent être placées
entre simples quotes.
➢La recherche tient compte des majuscules et minuscules (pour les
chaînes de caractère) et du format (pour les dates.)
➢Le format de date par défaut est
❑'DD-MON-YY'.

SQL> SELECT ename, job, deptno

2 FROM emp
3 WHERE ename = 'JAMES';

Pr. MERZOUK Soukaina 26

Opérateurs de Comparaison
Opérateur Signification
= Egal à
> Supérieur à
>= Supérieur ou égal à
< Inférieur à
<= Inférieur ou égal à
<> Différent de

SQL> SELECT ename, sal, comm ENAME SAL COMM

2 FROM emp ---------- --------- ---------
MARTIN 1250 1400
3 WHERE sal<=comm;

Pr. MERZOUK Soukaina 27

Autres Opérateurs de Comparaison

Opérateur Signification
BETWEEN Compris entre ... et ...
...AND... (bornes comprises)
IN (liste) Correspond à une valeur de la
liste
LIKE Ressemblance partielle de
chaînes de caractères
IS NULL Correspond à une valeur NULL

Pr. MERZOUK Soukaina 28

Utilisation de l’Opérateur BETWEEN
BETWEEN permet de tester l'appartenance à une fourchette de valeurs.
SQL> SELECT ename, sal
2 FROM emp
3 WHERE sal BETWEEN 1000 AND 1500;

ENAME SAL
---------- --------- Limite Limite
MARTIN 1250
TURNER 1500 inférieure supérieure
WARD 1250
ADAMS 1100
MILLER 1300

Pr. MERZOUK Soukaina 29

Utilisation de l’Opérateur IN

➢IN permet de comparer une expression avec une liste de valeurs.

SQL> SELECT empno, ename, sal, mgr
2 FROM emp
3 WHERE mgr IN (7902, 7566, 7788);
EMPNO ENAME SAL MGR
--------- ---------- --------- ---------
7902 FORD 3000 7566
7369 SMITH 800 7902
7788 SCOTT 3000 7566
7876 ADAMS 1100 7788

Pr. MERZOUK Soukaina 30

Utilisation de l’Opérateur LIKE
➢LIKE permet de rechercher des chaînes de caractères à l'aide de caractères
génériques
➢Les conditions de recherche peuvent contenir des caractères ou des nombres
littéraux.
SQL> SELECT ename
▪ (%) représente zéro ou plusieurs caractères 2 FROM emp
▪ ( _ ) représente un caractère 3 WHEREename LIKE 'S%';
▪ Vous pouvez combiner plusieurs caractères génériques de recherche.
▪ Vous pouvez utiliser l’identifiant ESCAPE "\" pour rechercher "%" ou "_".
SQL> SELECT ename ENAME
----------
2 FROM emp JAMES
3 WHEREename LIKE '_A%'; WARD
Pr. MERZOUK Soukaina 31
Utilisation de l’Opérateur IS NULL
➢Recherche de valeurs NULL avec l’opérateur IS NULL

SQL> SELECT ename, mgr

2 FROM emp
3 WHERE mgr IS NULL;
ENAME MGR
---------- ---------
KING

Pr. MERZOUK Soukaina 32

Opérateurs Logiques

Opérateur Signification
AND Retourne TRUE si les deux conditions sont
VRAIES
OR Retourne TRUE si l’une au moins des conditions
est VRAIE
NOT Ramène la valeur TRUE si la condition qui suit
l’opérateur est FAUSSE

Pr. MERZOUK Soukaina 33

Utilisation de l’Opérateur AND

Avec AND, les deux conditions doivent être VRAIES.

SQL> SELECT empno, ename, job, sal
2 FROM emp
3 WHERE sal>=1100
4 AND job='CLERK';

EMPNO ENAME JOB SAL

--------- ---------- --------- ---------
7876 ADAMS CLERK 1100
7934 MILLER CLERK 1300

Pr. MERZOUK Soukaina

34
Utilisation de l’Opérateur OR
Avec OR, l'une ou l'autre des deux conditions doit être VRAIE.
SQL> SELECT empno, ename, job, sal
2 FROM emp
3 WHERE sal>=1100
4 OR job='CLERK';
EMPNO ENAME JOB SAL
--------- ---------- --------- ---------

7839 KING PRESIDENT 5000

7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
...
14 rows selected.
Pr. MERZOUK Soukaina 35
Utilisation de l’Opérateur NOT
SQL> SELECT ename, job
2 FROM emp
3 WHERE job NOT IN ('CLERK','MANAGER','ANALYST');
ENAME JOB
---------- ---------
KING PRESIDENT
MARTIN SALESMAN
ALLEN SALESMAN
TURNER SALESMAN
WARD SALESMAN

... WHERE sal NOT BETWEEN 1000 AND 1500

... WHERE ename NOT LIKE ’%A%’
... WHERE comm IS NOT NULL
Pr. MERZOUK Soukaina
36
Règles de Priorité
Opérateur Signification
1 Tous les opérateurs de comparaison
2 NOT
3 AND
4 OR

➢Les parenthèses permettent de modifier les règles de priorité

Pr. MERZOUK Soukaina 37

Règles de Priorité
SQL> SELECT ename, job, sal
2 FROM emp
3 WHERE job='SALESMAN'
4 OR job='PRESIDENT'
5 AND sal>1500;

ENAME JOB SAL

---------- --------- ---------
KING PRESIDENT 5000
MARTIN SALESMAN 1250
ALLEN SALESMAN 1600
TURNER SALESMAN 1500
WARD SALESMAN 1250
Pr. MERZOUK Soukaina 38
Règles de Priorité
Utilisation de parenthèses pour forcer la priorité.

SQL> SELECT ename, job, sal

2 FROM emp
3 WHERE (job='SALESMAN'
4 OR job='PRESIDENT')
5 AND sal>1500;
ENAME JOB SAL
---------- --------- ---------
KING PRESIDENT 5000
ALLEN SALESMAN 1600
Pr. MERZOUK Soukaina 39
Clause ORDER BY
➢Tri des lignes avec la clause ORDER BY
▪ ASC : ordre croissant (par défaut)
▪ DESC : ordre décroissant
➢La clause ORDER BY se place à la fin de l’ordre SELECT
SQL> SELECT ename, job, deptno, hiredate
2 FROM emp
3 ORDER BY hiredate;
ENAME JOB DEPTNO HIREDATE
---------- --------- --------- ---------
SMITH CLERK 20 17-DEC-80
ALLEN SALESMAN 30 20-FEB-81
...
14 rows selected.

Pr. MERZOUK Soukaina 40

Tri par Ordre Décroissant
SQL> SELECT ename, job, deptno, hiredate
2 FROM emp
3 ORDER BY hiredate DESC;

ENAME JOB DEPTNO HIREDATE

---------- --------- --------- ---------
ADAMS CLERK 20 12-JAN-83
SCOTT ANALYST 20 09-DEC-82
MILLER CLERK 10 23-JAN-82
JAMES CLERK 30 03-DEC-81
FORD ANALYST 20 03-DEC-81
KING PRESIDENT 10 17-NOV-81
MARTIN SALESMAN 30 28-SEP-81
...
14 rows selected.
Pr. MERZOUK Soukaina
41
Tri sur l’Alias de Colonne
SQL> SELECT empno, ename, sal*12 annsal
2 FROM emp
3 ORDER BY annsal;

EMPNO ENAME ANNSAL

--------- ---------- ---------
7369 SMITH 9600
7900 JAMES 11400
7876 ADAMS 13200
7654 MARTIN 15000
7521 WARD 15000
7934 MILLER 15600
7844 TURNER 18000
...
14 rows selected.
Pr. MERZOUK Soukaina
42
Tri sur Plusieurs Colonnes
➢L’ordre des éléments de la liste ORDER BY donne l’ordre du tri.
SQL> SELECT ename, deptno, sal
2 FROM emp
3 ORDER BY deptno, sal DESC;

ENAME DEPTNO SAL

---------- --------- ---------
KING 10 5000
CLARK 10 2450
MILLER 10 1300
FORD 20 3000
...
14 rows selected.

➢Vous pouvez effectuer un tri sur une colonne ne figurant pas dans la liste SELECT.

Pr. MERZOUK Soukaina 43

Résumé

SELECT [DISTINCT] {*, column [alias], ...}

FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, alias} [ASC|DESC]];

Pr. MERZOUK Soukaina

44
Fonctions Mono-
Ligne
Pr. MERZOUK Soukaina 45
Objectifs

➢A la fin de ce chapitre, vous saurez :

▪ Décrire différents types de fonctions SQL
▪ Utiliser les fonctions caractère, numériques et date dans les
ordres SELECT
▪ Expliquer les fonctions de conversion

Pr. MERZOUK Soukaina 46

Qu’est ce qu’une fonction ?
➢Une fonction est une expression d’un type de données spécifique qui
fait partie d’une instruction utilisée pour calculer une valeur .

Entrée Sortie
Fonction

arg 1 La fonction
exécute une
arg 2 action Valeur
résultante

arg n

Pr. MERZOUK Soukaina 47

Deux Types de Fonctions SQL

Fonctions

Fonctions Fonctions
mono-ligne multi-ligne

Pr. MERZOUK Soukaina 48

Fonctions Mono-Ligne
➢Manipulent des éléments de données
➢Acceptent des arguments et ramènent une valeur
➢Agissent sur chacune des lignes rapportées
➢Ramènent un seul résultat par ligne
➢Peuvent modifier les types de données
➢Peuvent être imbriquées

function_name (column|expression, [arg1, arg2,...])

Pr. MERZOUK Soukaina 49

Fonctions Mono-Ligne

Caractère

Générale Numérique
Fonctions
mono-ligne

Conversion Date

Pr. MERZOUK Soukaina 50

Fonctions Caractère
Fonction
caractère

Fonctions de conversion Fonctions de manipulation

majuscules/minuscules des caractères
LOWER CONCAT
UPPER SUBSTR
INITCAP LENGTH
INSTR
LPAD ...

Pr. MERZOUK Soukaina 51

Fonctions de Conversion Majuscules/Minuscules

Fonction Résultat
LOWER('Cours SQL') cours sql
UPPER('Cours SQL') COURS SQL
INITCAP('Cours SQL') Cours Sql

Pr. MERZOUK Soukaina 52

Utilisation des Fonctions de Conversion
Majuscules/Minuscules
Afficher le matricule, le nom et le numéro de département de l’employé Blake.
SQL> SELECT empno, ename, deptno
2 FROM emp
3 WHERE ename = 'blake’;
no rows selected

SQL> SELECT empno, ename, deptno

2 FROM emp
3 WHERE LOWER(ename) = 'blake';

EMPNO ENAME DEPTNO

--------- ---------- ---------
7698 BLAKE 30
Pr. MERZOUK Soukaina 53
Fonctions de Manipulation des Caractères
Manipulation de chaînes de caractères
Fonction Résultat
CONCAT('Une', 'Chaîne') UneChaîne
SUBSTR('Chaîne',1,3) Cha
LENGTH('Chaîne') 6
INSTR('Chaîne', 'a') 3
LPAD(sal,10,'*') ******5000

Pr. MERZOUK Soukaina 54

Utilisation des Fonctions de Manipulation des Caractères

SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename),

2 INSTR(ename, 'A')
3 FROM emp
4 WHERE SUBSTR(job,1,5) = 'SALES';

ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A')

---------- ------------------- ------------- ----------------
MARTIN MARTINSALESMAN 6 2
ALLEN ALLENSALESMAN 5 1
TURNER TURNERSALESMAN 6 0
WARD WARDSALESMAN 4 2

Pr. MERZOUK Soukaina 55

Fonctions Numériques
➢ROUND : Arrondit la valeur à la précision spécifiée
ROUND(45.926, 2) 45.93

➢TRUNC : Tronque la valeur à la précision spécifiée

TRUNC(45.926, 2) 45.92

➢MOD : Ramène le reste d’une division

MOD(1600,300) 100

Pr. MERZOUK Soukaina 56

Utilisation de la Fonction ROUND
➢Affichage de la valeur 45.923 arrondie au centième, à 0 décimale et à
la dizaine supérieure.

SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),

2 ROUND(45.923,-1)
3 FROM DUAL;

ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)

--------------- -------------- -----------------
45.92 46 50

Pr. MERZOUK Soukaina 57

Utilisation de la Fonction TRUNC
➢Affichage de la valeur 45.923 tronquée au centième, à 0 décimale et
à la dizaine.

SQL> SELECT TRUNC(45.923,2), TRUNC(45.923),

2 TRUNC(45.923,-1)
3 FROM DUAL;

TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)

--------------- ------------- ---------------
45.92 45 40

Pr. MERZOUK Soukaina 58

Utilisation de la Fonction MOD

➢Calculer le reste de la division salaire par commission pour l’ensemble

des employés ayant un poste de vendeur.
SQL> SELECT ename, sal, comm, MOD(sal, comm)
2 FROM emp
3 WHERE job = 'SALESMAN';

ENAME SAL COMM MOD(SAL,COMM)

---------- --------- --------- -------------
MARTIN 1250 1400 1250
ALLEN 1600 300 100
TURNER 1500 0 1500
WARD 1250 500 250

Pr. MERZOUK Soukaina 59

Autres Fonctions Numériques

➢ABS(x) : Valeur absolue de x

➢CEIL(n) : Plus petit entier supérieur ou égal à n.

➢SIGN(n) : Si n<0, -1; si n=0, 0; si n>0, 1.

➢FLOOR(n) : Plus grand entier supérieur ou égal à n.

Pr. MERZOUK Soukaina 60

Utilisation des Dates

➢Oracle stocke les dates dans un format numérique interne : siècle,

année, mois, jour, heures, minutes, secondes.
➢Le format de date par défaut est DD-MON-YY.
➢La fonction SYSDATE ramène la date et l’heure courante.
➢DUAL est une table factice qu'on peut utiliser pour visualiser
SYSDATE.

Pr. MERZOUK Soukaina 61

Opérations Arithmétiques sur les Dates

➢Ajout ou soustraction d’un nombre à une date pour obtenir un

résultat de type date.
➢Soustraction de deux dates afin de déterminer le nombre de jours
entre ces deux dates.
➢Ajout d’un nombre d’heures à une date en divisant le nombre
d’heures par 24.

Pr. MERZOUK Soukaina 62

Utilisation d’Opérateurs Arithmétiques avec les Dates

SQL> SELECT ename, (SYSDATE-hiredate)/7 WEEKS

2 FROM emp
3 WHERE deptno = 10;

ENAME WEEKS
---------- ---------
KING 830.93709
CLARK 853.93709
MILLER 821.36566

Pr. MERZOUK Soukaina 63

Fonctions Date
FONCTION DESCRIPTION
MONTHS_BETWEEN(d1,d2) Nombre de mois situés entre deux dates

ADD_MONTHS(date, n) Ajoute des mois calendaires à une date

NEXT_DAY(date,’char’) Jour qui suit la date spécifiée

LAST_DAY(date) Dernier jour du mois

ROUND(date [,’fmt’] ) Arrondit une date

TRUNC (date [,’fmt’] ) Tronque une date

Pr. MERZOUK Soukaina 64

Utilisation des Fonctions Date

➢MONTHS_BETWEEN ('01-SEP-95','11-JAN-94’) 19.6774194

➢ADD_MONTHS ('11-JAN-94’,6) '11-JUL-94'
➢NEXT_DAY ('01-SEP-95','FRIDAY’) '08-SEP-95'
➢LAST_DAY('01-SEP-95’) '30-SEP-95'

Pr. MERZOUK Soukaina 65

Fonctions de Conversion

Conversion
de types
de données

Conversion Conversion
de types de types
de données de données
implicite explicite

Pr. MERZOUK Soukaina 66

Conversion de Types de Données Implicite

➢Pour les affectations, Oracle effectue automatiquement les

conversions suivantes
De Vers
VARCHAR2 ou CHAR NUMBER
VARCHAR2 ou CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2

Pr. MERZOUK Soukaina 67

 Conversion de Types de Données Implicite
➢Pour l’évaluation d’expressions, Oracle effectue automatiquement les
conversions suivantes
De Vers
VARCHAR2 ou CHAR NUMBER
VARCHAR2 ou CHAR DATE

TO_NUMBER TO_DATE

NUMBER CHARACTER DATE

TO_CHAR TO_CHAR
Pr. MERZOUK Soukaina 68
Utilisation de la Fonction TO_CHAR avec les Dates
TO_CHAR(date, 'fmt')

➢Le modèle de format :

▪ Doit être placé entre simples quotes et différencie les majuscules et
minuscules.
▪ Peut inclure tout élément valide de format date
▪ Comporte un élément fm qui supprime les espaces de remplissage ou les
zéros de tête
▪ Est séparé de la valeur date par une virgule
Pr. MERZOUK Soukaina 69
Modèles de Format Date

YYYY Année exprimée avec 4 chiffres

YEAR Année exprimée en toutes lettres

MM Mois exprimé avec 2 chiffres

MONTH Mois exprimé en toutes lettres

3 premières lettres du nom du jour
DY

DAY Jour exprimé en toutes lettres

Pr. MERZOUK Soukaina 70

Modèles de Format pour les Dates
➢Les éléments horaires formatent la partie horaire de la date.

HH24:MI:SS AM 15:45:32 PM

➢Pour ajouter des chaînes de caractères, les placer entre guillemets.

DD "of" MONTH 12 of OCTOBER

➢Différents suffixes existent pour les nombres.

ddspth fourteenth

Pr. MERZOUK Soukaina 71

Utilisation de la Fonction TO_CHAR avec les Dates

SQL> SELECT ename,

2 TO_CHAR(hiredate,'fmDD Month YYYY') HIREDATE
3 FROM emp;

ENAME HIREDATE
---------- -----------------
KING 17 November 1981
BLAKE 1 May 1981
CLARK 9 June 1981
JONES 2 April 1981
MARTIN 28 September 1981
ALLEN 20 February 1981
...
14 rows selected.

Pr. MERZOUK Soukaina

Utilisation de la Fonction TO_CHAR avec les Nombres

TO_CHAR(number, 'fmt')

➢Utilisez les formats suivants avec TO_CHAR pour afficher un nombre sous la
forme d'une chaîne de caractère.

9 Représente un chiffre
0 Force l’affichage du zéro
$ Place un signe dollar flottant
L Utilise le symbole monétaire local flottant
. Imprime un point décimal
, Imprime un séparateur de milliers

Pr. MERZOUK Soukaina 73

Utilisation de la Fonction TO_CHAR avec les Nombres

SQL> SELECT TO_CHAR(sal,'$99,999') SALARY

2 FROM emp
3 WHERE ename = 'SCOTT';

SALARY
--------
$3,000

Pr. MERZOUK Soukaina 74

 Fonctions TO_NUMBER et TO_DATE
➢Conversion d’une chaîne de caractères en format numérique avec la
fonction TO_NUMBER

TO_NUMBER(char)

• Conversion d’une chaîne de caractères en format date avec la fonction

TO_DATE

TO_DATE(char[, 'fmt'])

Pr. MERZOUK Soukaina 75

Fonction NVL
➢Convertit une valeur NULL en une valeur réelle
▪ Fonctionne avec les données de type date, caractère et
numérique.
▪ Les types de données doivent correspondre
• NVL(comm,0)
• NVL(hiredate,'01-JAN-97')
• NVL(job,'No Job Yet')

Pr. MERZOUK Soukaina 76

Utilisation de la Fonction NVL

SQL> SELECT ename, sal, comm, (sal*12)+NVL(comm,0)

2 FROM emp;

ENAME SAL COMM (SAL*12)+NVL(COMM,0)

---------- --------- --------- --------------------
KING 5000 60000
BLAKE 2850 34200
CLARK 2450 29400
JONES 2975 35700
MARTIN 1250 1400 16400
ALLEN 1600 300 19500
...
14 rows selected.

Pr. MERZOUK Soukaina 77

Fonction DECODE

➢Facilite les recherches conditionnelles en jouant le rôle de CASE ou

IF-THEN-ELSE

DECODE(col/expression, search1, result1

[, search2, result2,...,]
[, default])

Pr. MERZOUK Soukaina 78

Utilisation de la Fonction DECODE
SQL> SELECT job, sal,
2 DECODE(job, 'ANALYST', SAL*1.1,
3 'CLERK', SAL*1.15,
4 'MANAGER', SAL*1.20,
5 SAL)
6 REVISED_SALARY
7 FROM emp;

JOB SAL REVISED_SALARY

--------- --------- --------------
PRESIDENT 5000 5000
MANAGER 2850 3420
MANAGER 2450 2940
...
14 rows selected.
Pr. MERZOUK Soukaina 79
Imbrication des Fonctions
➢Le niveau d’imbrication des fonctions mono-ligne est illimité
➢Les fonctions imbriquées sont évaluées de l'intérieur vers l'extérieur

F3(F2(F1(col,arg1),arg2),arg3)

Etape 1 = Résultat 1
Etape 2 = Résultat 2
Etape 3 = Résultat 3

Pr. MERZOUK Soukaina 80

Imbrication des Fonctions

SQL> SELECT ename,

2 NVL(TO_CHAR(mgr),'No Manager')
3 FROM emp
4 WHERE mgr IS NULL;

ENAME NVL(TO_CHAR(MGR),'NOMANAGER')
---------- -----------------------------
KING No Manager

Pr. MERZOUK Soukaina 81

Résumé

➢Utilisez des fonctions mono-ligne pour :

➢Transformer des données
➢Formater des dates et des nombres pour l'affichage
➢Convertir des types de données de colonnes

Pr. MERZOUK Soukaina 82

Afficher des Données
Issues de Plusieurs
Tables
Pr. MERZOUK Soukaina 83
Objectifs

➢A la fin de ce chapitre, vous saurez :

▪ Ecrire des ordres SELECT pour accéder aux données de plusieurs
tables en utilisant des équijointures et des non-équijointures
▪ Visualiser des données ne répondant pas aux conditions de jointure,
en utilisant les jointures externes
▪ Relier une table à elle-même

Pr. MERZOUK Soukaina 84

Afficher des Données Issues de Plusieurs Tables
EMP DEPT
EMPNO ENAME ... DEPTNO DEPTNO DNAME LOC
------ ----- ... ------ ------ ---------- --------
7839 KING ... 10 10 ACCOUNTING NEW YORK
7698 BLAKE ... 30 20 RESEARCH DALLAS
... 30 SALES CHICAGO
7934 MILLER ... 10 40 OPERATIONS BOSTON

EMPNO DEPTNO LOC

----- ------- --------
7839 10 NEW YORK
7698 30 CHICAGO
7782 10 NEW YORK
7566 20 DALLAS
7654 30 CHICAGO
7499 30 CHICAGO
...
14 rows selected.

Pr. MERZOUK Soukaina 85

Qu'est-ce qu'une Jointure ?
➢Une jointure sert à extraire des données de plusieurs tables.

SELECT [Link], [Link]

FROM table1, table2
WHERE table1.column1 = table2.column2;

❑Ecrivez la condition de jointure dans la clause WHERE.

❑Placez le nom de la table avant le nom de la colonne lorsque celui-ci
figure dans plusieurs tables.

Pr. MERZOUK Soukaina 86

Produit Cartésien
➢On obtient un produit cartésien lorsque :
▪ Une condition de jointure est omise
▪ Une condition de jointure est incorrecte
➢Toutes les lignes de la première table sont jointes à toutes les lignes
de la seconde
➢Pour éviter un produit cartésien, toujours insérer une condition de
jointure correcte dans la clause WHERE.

Pr. MERZOUK Soukaina 87

Génération d'un Produit Cartésien
EMP (14 lignes) DEPT (4 lignes)
EMPNO ENAME ... DEPTNO DEPTNO DNAME LOC
------ ----- ... ------ ------ ---------- --------
7839 KING ... 10 10 ACCOUNTING NEW YORK
7698 BLAKE ... 30 20 RESEARCH DALLAS
... 30 SALES CHICAGO
7934 MILLER ... 10 40 OPERATIONS BOSTON

ENAME DNAME
------ ----------
KING ACCOUNTING
"Produit cartésien : BLAKE ACCOUNTING
14*4=56 lignes" ...
KING RESEARCH
BLAKE RESEARCH
...
56 rows selected.
Pr. MERZOUK Soukaina 88
Types de Jointures

Equijointure Non-équijointure

Jointure externe Autojointure

Pr. MERZOUK Soukaina

Qu'est-ce qu'une Equijointure ?
EMP DEPT
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
------ ------- ------- ------- ---------- --------
7839 KING 10 10 ACCOUNTING NEW YORK
7698 BLAKE 30 30 SALES CHICAGO
7782 CLARK 10 10 ACCOUNTING NEW YORK
7566 JONES 20 20 RESEARCH DALLAS
7654 MARTIN 30 30 SALES CHICAGO
7499 ALLEN 30 30 SALES CHICAGO
7844 TURNER 30 30 SALES CHICAGO
7900 JAMES 30 30 SALES CHICAGO
7521 WARD 30 30 SALES CHICAGO
7902 FORD 20 20 RESEARCH DALLAS
7369 SMITH 20 20 RESEARCH DALLAS
... ...
14 rows selected. 14 rows selected.

Clé étrangère Clé primaire

Pr. MERZOUK Soukaina 90
Extraction d'Enregistrements avec les Equijointures
SQL> SELECT [Link], [Link], [Link],
2 [Link], [Link]
3 FROM emp, dept
4 WHERE [Link]=[Link];

EMPNO ENAME DEPTNO DEPTNO LOC

----- ------ ------ ------ ---------
7839 KING 10 10 NEW YORK
7698 BLAKE 30 30 CHICAGO
7782 CLARK 10 10 NEW YORK
7566 JONES 20 20 DALLAS
...
14 rows selected.

Pr. MERZOUK Soukaina 91

Différencier les Noms de Colonne Ambigus

➢Préfixer avec le nom de la table pour différencier les noms de

colonnes appartenant à plusieurs tables.
➢Ces préfixes de table améliorent les performances.
➢Différencier des colonnes de même nom appartenant à plusieurs
tables en utilisant des alias de colonne.

Pr. MERZOUK Soukaina 92

Ajout de Conditions de Recherche avec l'Opérateur AND
EMP DEPT
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
------ ------- ------- ------ --------- --------
7839 KING 10 10 ACCOUNTING NEW YORK
7698 BLAKE 30 30 SALES CHICAGO
7782 CLARK 10 10 ACCOUNTING NEW YORK
7566 JONES 20 20 RESEARCH DALLAS
7654 MARTIN 30 30 SALES CHICAGO
7499 ALLEN 30 30 SALES CHICAGO
7844 TURNER 30 30 SALES CHICAGO
7900 JAMES 30 30 SALES CHICAGO
7521 WARD 30 30 SALES CHICAGO
7902 FORD 20 20 RESEARCH DALLAS
7369 SMITH 20 20 RESEARCH DALLAS
... ...
14 rows selected. 14 rows selected.
Pr. MERZOUK Soukaina
93
Utilisation d'Alias de Table
➢Simplifiez les requêtes avec les alias de table.

SQL> SELECT [Link], [Link], [Link],

2 [Link], [Link]
3 FROM emp, dept
4 WHERE [Link]=[Link];

SQL> SELECT [Link], [Link], [Link],

2 [Link], [Link]
3 FROM emp e, dept d
4 WHERE [Link]=[Link];

Pr. MERZOUK Soukaina 94

Non-Equijointures
EMP SALGRADE
EMPNO ENAME SAL GRADE LOSAL HISAL
------ ------- ------ ----- ----- ------
7839 KING 5000 1 700 1200
7698 BLAKE 2850 2 1201 1400
7782 CLARK 2450 3 1401 2000
7566 JONES 2975 4 2001 3000
7654 MARTIN 1250 5 3001 9999
7499 ALLEN 1600
7844 TURNER 1500
7900 JAMES 950
... "Les salaires (SAL) de la table
14 rows selected. EMP sont compris entre le
salaire minimum (LOSAL) et le
salaire maximum (HISAL) de la
table SALGRADE"
Pr. MERZOUK Soukaina 95
Extraction d'Enregistrements avec les Non-Equijointures

SQL> SELECT [Link], [Link], [Link]

2FROM emp e, salgrade s
3WHERE [Link]
4BETWEEN [Link] AND [Link];

ENAME SAL GRADE

---------- --------- ---------
JAMES 950 1
SMITH 800 1
ADAMS 1100 1
...
14 rows selected.

Pr. MERZOUK Soukaina 96

Jointures Externes
EMP DEPT
ENAME DEPTNO DEPTNO DNAME
----- ------ ------ ----------
KING 10 10 ACCOUNTING
BLAKE 30 30 SALES
CLARK 10 10 ACCOUNTING
JONES 20 20 RESEARCH
... ...
40 OPERATIONS

Pas d'employés dans le

département OPERATIONS

Pr. MERZOUK Soukaina 97

Jointures Externes
➢Les jointures externes permettent de visualiser des lignes qui ne
répondent pas à la condition de jointure.
➢L'opérateur de jointure externe est le signe (+).
SELECT [Link], [Link]
FROM table1, table2
WHERE [Link](+) = [Link];

SELECT [Link], [Link]

FROM table1, table2
WHERE [Link] = [Link](+);
Pr. MERZOUK Soukaina 98
Utilisation des Jointures Externes
SQL> SELECT [Link], [Link], [Link]
2 FROM emp e, dept d
3 WHERE [Link](+) = [Link]
4 ORDER BY [Link];

ENAME DEPTNO DNAME

---------- --------- -------------
KING 10 ACCOUNTING
CLARK 10 ACCOUNTING
...
40 OPERATIONS
15 rows selected.

Pr. MERZOUK Soukaina 99

Autojointures
EMP (WORKER) EMP (MANAGER)
EMPNO ENAME MGR EMPNO ENAME
----- ------ ---- ----- --------
7839 KING
7698 BLAKE 7839 7839 KING
7782 CLARK 7839 7839 KING
7566 JONES 7839 7839 KING
7654 MARTIN 7698 7698 BLAKE
7499 ALLEN 7698 7698 BLAKE

"Dans la table WORKER, MGR équivaut à

EMPNO dans la table MANAGER"

Pr. MERZOUK Soukaina 100

Liaison d'une Table à Elle-même
SQL> SELECT [Link]||' works for '||[Link]
2 FROM emp worker, emp manager
3 WHERE [Link] = [Link];

[Link]||'WORKSFOR'||MANAG
-------------------------------
BLAKE works for KING
CLARK works for KING
JONES works for KING
MARTIN works for BLAKE
...
13 rows selected.

Pr. MERZOUK Soukaina 101

INNER JOIN
SELECT *
FROM table1
INNER JOIN table2
ON [Link] = table2.fk_id

La syntaxe ci-dessus stipule qu’il faut sélectionner les enregistrements

des tables table1 et table2 lorsque les données de la colonne “id” de
table1 est égal aux données de la colonne fk_id de table2.

Pr. MERZOUK Soukaina

102
INNER JOIN
SELECT ename,sal, Dname, Loc
FROM emp
INNER JOIN dept
ON [Link] = [Link]

Pr. MERZOUK Soukaina

103
Résumé
SELECT [Link], [Link]
FROM table1, table2
WHERE table1.column1 = table2.column2;
Equijointure Non-équijointure

Jointure externe Autojointure

Pr. MERZOUK Soukaina

104
Regrouper les Données
avec les Fonctions de
Groupe
Pr. MERZOUK Soukaina 10
5
Objectifs

➢A la fin de ce chapitre, vous saurez :

➢Identifier les fonctions de groupe disponibles
➢Expliquer l'utilisation des fonctions de groupe
➢Regrouper les données avec la clause GROUP BY
➢Inclure ou exclure des groupes de lignes avec la clause
HAVING

Pr. MERZOUK Soukaina 106

Fonctions de Groupe
Les fonctions de groupe agissent sur des groupes de lignes et donnent
un résultat par groupe. EMP
DEPTNO SAL
--------- ---------
10 2450
10 5000
10 1300
20 800
20 1100
20 3000 "salaire maximum MAX(SAL)
20 3000 de la table EMP" ---------
20 2975 5000
30 1600
30 2850
30 1250
30 950
30 1500
30 1250

Pr. MERZOUK Soukaina 107

Types de Fonctions de Groupe

➢AVG ([DISTINCT|ALL]n)
➢COUNT ({ *|[DISTINCT|ALL]expr})
➢MAX ([DISTINCT|ALL]expr)
➢MIN ([DISTINCT|ALL]expr)
➢STDDEV ([DISTINCT|ALL]n)
➢SUM ([DISTINCT|ALL]n)
➢VARIANCE ([DISTINCT|ALL]n)

Pr. MERZOUK Soukaina 108

Fonctions AVG et SUM
➢AVG et SUM s'utilisent avec des données numériques.
SQL> SELECT AVG(sal), MAX(sal),
2 MIN(sal), SUM(sal)
3 FROM emp
4 WHERE job LIKE 'SALES%';
AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)
-------- --------- --------- ---------
1400 1600 1250 5600

➢MIN et MAX s'utilisent avec tous types de données.

Pr. MERZOUK Soukaina 109

Utilisation de la Fonction COUNT
➢COUNT(*) ramène le nombre de lignes d'une table.

SQL> SELECT COUNT(*) COUNT(*)

2 FROM emp ---------
3 WHERE deptno = 30; 6

➢COUNT(expr) ramène le nombre de lignes non NULL.

SQL> SELECT COUNT(comm) COUNT(COMM)

2 FROM emp -----------
3 WHERE deptno = 30; 4

Pr. MERZOUK Soukaina 110

Fonctions de Groupe et Valeurs NULL
➢Les fonctions de groupe, à l'exception de COUNT (*), ignorent les
valeurs NULL des colonnes.
SQL> SELECT AVG(comm)
2 FROM emp;

AVG(COMM)
---------
550

Pr. MERZOUK Soukaina 111

Utilisation de la Fonction NVL avec les Fonctions de Groupe

➢La fonction NVL force la prise en compte des valeurs NULL dans les
fonctions de groupe.

SQL> SELECT AVG(NVL(comm,0))

2 FROM emp;

AVG(NVL(COMM,0))
----------------
157.14286

Pr. MERZOUK Soukaina 112

Création de Groupes de Données
EMP DEPTNO SAL
--------- ---------
10 2450
10 5000 2916.6667
10 1300 DEPTNO AVG(SAL)
20 800
20 1100
2175 ------- ---------
"salaire
20 3000 moyen pour chaque 10 2916.6667
20 3000
département 20 2175
20 2975
30 1600 de la table EMP" 30 1566.6667
30 2850
30 1250
30 950 1566.6667
30 1500
30 1250
Pr. MERZOUK Soukaina 113
Création de Groupes de Données :
la Clause GROUP BY

SELECT column, group_function

FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

➢Divisez une table en groupes de lignes avec la clause GROUP

BY.

Pr. MERZOUK Soukaina 114

Utilisation de la Clause GROUP BY
➢La clause GROUP BY doit inclure toutes les colonnes de la liste
SELECT qui ne figurent pas dans des fonctions de groupe.
SQL> SELECT deptno, AVG(sal)
2 FROM emp
3 GROUP BY deptno;
DEPTNO AVG(SAL)
--------- ---------
10 2916.6667
20 2175
30 1566.6667
Pr. MERZOUK Soukaina 115
Utilisation de la Clause GROUP BY
➢La colonne citée en GROUP BY ne doit pas nécessairement figurer dans la
liste SELECT.
SQL> SELECT AVG(sal)
2 FROM emp
3 GROUP BY deptno;

AVG(SAL)
---------
2916.6667
2175
1566.6667

Pr. MERZOUK Soukaina 116

Regroupement sur Plusieurs Colonnes
EMP
DEPTNO JOB SAL
--------- --------- ---------
10 MANAGER 2450
DEPTNO JOB SUM(SAL)
10 PRESIDENT 5000
-------- --------- ---------
10 CLERK 1300
10 CLERK 1300
20 CLERK 800 '"somme des 10 MANAGER 2450
20 CLERK 1100 salaires 10 PRESIDENT 5000
20 ANALYST 3000 de la table EMP
20 ANALYST 6000
20 ANALYST 3000 pour chaque poste,
20 CLERK 1900
20 MANAGER 2975 regroupés par
20 MANAGER 2975
30 SALESMAN 1600 département"
30 CLERK 950
30 MANAGER 2850
30 MANAGER 2850
30 SALESMAN 1250
30 SALESMAN 5600
30 CLERK 950
30 SALESMAN 1500
30 SALESMAN 1250

Pr. MERZOUK Soukaina 117

Utilisation de la Clause GROUP BY sur Plusieurs
Colonnes
SQL> SELECT deptno, job, sum(sal)
2 FROM emp
3 GROUP BY deptno, job;

DEPTNO JOB SUM(SAL)

--------- --------- ---------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
...
9 rows selected.
Pr. MERZOUK Soukaina 118
Erreurs d'Utilisation des Fonctions de Groupe dans une
Requête
➢Toute colonne ou expression de la liste SELECT autre qu'une
fonction de groupe, doit être incluse dans la clause GROUP BY.

SQL> SELECT deptno, COUNT(ename)

2 FROM emp;

SELECT deptno, COUNT(ename)

*
ERROR at line 1:
ORA-00937: not a single-group group function

Pr. MERZOUK Soukaina 119

Erreurs d'Utilisation des Fonctions de Groupe dans une
Requête
➢Vous ne pouvez utiliser la clause WHERE pour limiter les groupes.
➢Utilisez la clause HAVING.
SQL> SELECT deptno, AVG(sal)
2 FROM emp
3 WHERE AVG(sal) > 2000
4 GROUP BY deptno;

WHERE AVG(sal) > 2000

*
ERROR at line 3:
ORA-00934: group function
Pr. MERZOUK Soukaina is not allowed here 120
Exclusion de Groupes
EMP
DEPTNO SAL
--------- ---------
10 2450
10 5000 5000
10 1300
20 800
20 1100 "salaire maximum DEPTNO MAX(SAL)
20 3000 supérieur à --------- ---------
3000
20 3000 $2900 dans 10 5000
20 2975 chaque département" 20 3000
30 1600
30 2850
30 1250
2850
30 950
30 1500
30 1250

Pr. MERZOUK Soukaina 121

Exclusion de Groupes : la Clause HAVING
➢Utilisez la clause HAVING pour restreindre les groupes
• Les lignes sont regroupées.
• La fonction de groupe est appliquée.
• Les groupes qui correspondent à la clause HAVING sont affichés.
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

Pr. MERZOUK Soukaina 122

Utilisation de la clause HAVING
SQL> SELECT deptno, max(sal)
2 FROM emp
3 GROUP BY deptno
4 HAVING max(sal)>2900;

DEPTNO MAX(SAL)
--------- ---------
10 5000
20 3000

Pr. MERZOUK Soukaina 123

Utilisation de la Clause HAVING
SQL> SELECT job, SUM(sal) PAYROLL
2 FROM emp
3 WHERE job NOT LIKE 'SALES%'
3 GROUP BY job
4 HAVING SUM(sal)>5000
5 ORDER BY SUM(sal);

JOB PAYROLL
--------- ---------
ANALYST 6000
MANAGER 8275
Pr. MERZOUK Soukaina
Imbrication des Fonctions de Groupe
➢Afficher le salaire moyen maximum.

SQL> SELECT max(avg(sal))

2 FROM emp
3 GROUP BY deptno;

MAX(AVG(SAL))
-------------
2916.6667

Pr. MERZOUK Soukaina 125

Résumé

SELECT column, group_function

FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

Pr. MERZOUK Soukaina 126