Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Le langage SQL
Langage d’Intérrogation de Données
Ines BAKLOUTI
Ecole Supérieure Privée d’Ingénierie et de Technologies
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Plan
1 Extraction de données
Instruction SELECT
Restriction de données
Tri de données
2 Les fonctions
Les fonctions mono-ligne
Les fonctions de caractères
Les fonctions numériques
Les fonctions de dates
Les fonctions de conversion
Autres fonctions
Les fonctions analytiques
Les fonctions multi-lignes
3 Les sous intérrogations
Les sous-intérrogations monoligne
Les sous-intérrogations multi-lignes
4 Les jointures
Jointure interne
Jointure externe
Equijointure / Non-équijointure
Auto-jointure
Jointure naturelle
Produit cartésien
5 Les opérateurs ensemblistes
L’opérateur UNION
L’opérateur UNION ALL
L’opérateur INTERSECT
L’opérateur MINUS
2/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Plan
1 Extraction de données
Instruction SELECT
Restriction de données
Tri de données
2 Les fonctions
3 Les sous intérrogations
4 Les jointures
5 Les opérateurs ensemblistes
3/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Instruction SELECT
Syntaxe
SELECT * | { [DISTINCT] <colonne> | <expression> [alias],...}
FROM <nom table>;
SELECT : indique les colonnes à afficher
DISTINCT : supprime les doublons
FROM : indique les tables contenant les colonnes
Exemple 1
SELECT * FROM departments;
4/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Instruction SELECT
Exemple 2
SELECT department id, department name FROM departments;
Exemple 3
SELECT DISTINCT department id FROM employees;
5/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Expressions arithmétiques
Expressions contenant des données de type NUMBER, DATE et des
opérateurs arithmétiques
Exemple
SELECT last name, first name, salary+1000, commission pct*100 FROM employees;
6/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Valeur NULL
NULL représente une valeur non disponible, non affectée
NULL est différente de zéro, espace ou chaˆıne vide
Exemple
SELECT last name, first name, commission pct FROM employees;
7/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Valeur NULL et expressions arithméthiques
Les expressions arithmétiques comportant une valeur NULL
renvoient toujours une valeur NULL
Exemple
SELECT last name, salary, salary+commission pct, salary*commission pct FROM employees;
8/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Allias de colonne
Un alias de colonne :
Renomme un entête de colonne
Est utile avec les calculs
Suit immédiatement le nom d’une colonne (le mot clé facultatif AS peut également
être utilisé entre le nom de la colonne et l’alias)
Nécessité des guillemets (”alias”) s’il contient des espaces ou des caractères spéciaux
(# $), ou s’il distingue les majuscules des minuscules
Exemple
SELECT last name nom, first name AS prénom, salary*12 ”revenu annuel” FROM employees;
9/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Opérateur de concaténation
Concatene des colonnes ou des chaines de caracteres
Est représenté par le symbole ||
La colonne résultante est une expression de type caratère
Exemple
SELECT department id||’ ** ’||department name AS ”département” from departments;
10/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Restriction de données: la clause WHERE
Restreindre les lignes renvoyées à l’aide la clause WHERE
Syntaxe
SELECT * | { [DISTINCT] <colonne> | <expression> [alias],...}
FROM <nom table>
[WHERE <condition(s)>];
Exemple
SELECT employee id, last name, department id
FROM employees
WHERE department id= 80;
11/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Chaˆınes de caractères et dates
Les chaˆınes de caractères et les dates sont incluses entre apostrophes.
Les valeurs de type caractère distinguent les majuscules des minuscules
Les valeurs de type date sont sensibles au format
Le format de date par défaut est DD-MM-RR
Exemples
1 SELECT employee id,first name FROM employees WHERE first name=’James’;
2 SELECT employee id,first name FROM employees WHERE first name=’JAMES’;
=> aucune ligne sélectionnée
12/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Opérateurs de comparaison
13/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Opérateurs de comparaison
L’opérateur BETWEEN
Exemple 1
SELECT first name, salary FROM employees
WHERE salary BETWEEN 15000 AND 20000;
Exemple 2
SELECT first name, salary FROM employees
WHERE first name BETWEEN ’V’ AND ’X’;
14/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Opérateurs de comparaison
L’opérateur IN
Exemple 1
SELECT first name, department id FROM employees
WHERE department id IN (10,20);
Exemple 2
SELECT first name, last name FROM employees
WHERE first name IN (’James’,’David’,’Diana’);
15/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Opérateurs de comparaison
L’opérateur LIKE
L’opérateur LIKE permet de rechercher des chaˆınes de caracteres a l’aide de caractères
génériques
Les conditions de recherche peuvent contenir des caractères ou des nombres littéraux
% représente Zéro ou plusieurs caractères
représente un caractère
Exemple
SELECT first name FROM employees
WHERE first name LIKE ’S e%’ ;
16/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Opérateurs de comparaison
L’opérateur IS NULL
L’opérateur IS NULL permet de tester la présence de valeurs NULL.
Exemple
SELECT first name, manager id FROM employees
WHERE manager id IS NULL;
17/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Publicité
Restriction de données
Tri de données
Opérateurs logiques
18/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Opérateurs logiques
L’opérateur AND
Exemple
SELECT last name, job id, salary FROM employees
WHERE salary >=10000 AND job id LIKE ’%MAN%’;
19/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Opérateurs logiques
L’opérateur OR
Exemple
SELECT last name, job id, salary FROM employees
WHERE salary >=10000 OR job id LIKE ’%MAN%’;
20/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Opérateurs logiques
L’opérateur NOT
Exemple
SELECT last name, job id FROM employees
WHERE job id NOT IN (’IT PROG’, ’ST CLERK’, ’SA REP’) ;
21/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Opérateurs logiques
Règles de priorité
22/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Opérateurs logiques
Règles de priorité
Exemple 1
SELECT last name, job id, salary FROM employees
WHERE job id= ’SA MAN’ OR job id= ’AD VP’ AND salary > 12000;
Exemple 1
SELECT last name, job id, salary FROM employees
WHERE ( job id= ’SA MAN’ OR job id= ’AD VP’ ) AND salary > 12000;
23/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Tri de données: la clause ORDER BY
La clause ORDER BY:
permet de triez les lignes extraites
ASC : ordre croissant (par défaut)
DESC : ordre décroissant
toujours la dernière clause dans l’instruction SELECT
Syntaxe
SELECT * | { [DISTINCT] <colonne> | <expression> [alias],...}
FROM <nom table>;
[ WHERE condition(s) ]
[ORDER BY {<colonne>, <expression>, <alias>} [ASC | DESC] ] ;
Exemple 1: tri par ordre décroissant
SELECT last name, hire date FROM employees
ORDER BY hire date DESC ;
–ou bien ORDER By 2 DESC
24/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Instruction SELECT
Restriction de données
Tri de données
Tri de données: la clause ORDER BY
Exemple 2: tri par alias de colonne
SELECT employee id, last name, salary*12 ”Salaire Annuel”
FROM employees
ORDER BY ”Salaire Annuel”;
–ou bien ORDER By 3
Exemple 3: tri selon plusieurs colonnes
SELECT employee id, last name, salary*12 ”Salaire Annuel”
FROM employees
ORDER BY ”Salaire Annuel”, last name DESC;
—ou bien ORDER By 3, 2 DESC
25/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Plan
1 Extraction de données
4 Les jointures
2 Les fonctions
5 Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
3 Les sous intérrogations
26/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions
Il existe 3 types de fonctions dans le language SQL
Fonctions mono-ligne: manipulent une seule ligne et ramènent un
seul résultat
Fonctions analytiques: manipulent plusieurs lignes et ramènent un
plusieurs résultats
Fonctions multi-lignes: manipulent plusieurs lignes et ramènent un
seul résultat
27/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions de caractères
Fonctions de conversion majuscules/minuscules
Exemple
SELECT first name, lower(first name) , upper(first name), initcap(first name) FROM employees;
28/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions de caractères
Fonctions de manipulation de caractères
29/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions de caractères
Fonctions de manipulation de caractères
Exemple
SELECT first name, last name, job id,
CONCAT(first name,last name) ”Nom et prénom”,
LENGTH (last name) ”longueur nom”,
INSTR(last name,’a’) ”position a”,
LPAD(last name,10,’*’),
RPAD(last name,10,’*’)
FROM employees
WHERE SUBSTR(job id, 4) = ’REP’;
30/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions numériques
31/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions numériques
Exemple
SELECT commission pct+0.2,
ROUND(commission pct+0.2),
TRUNC(commission pct+0.2),
FLOOR(commission pct+0.2),
CEIL(commission pct+0.2)
FROM employees where department id=80;
32/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions de dates
Dans la base de données Oracle, les dates sont stockées dans un
format numériques interne: siècle, année, mois, jour, heures, minutes
et secondes
Le format de date par défaut est ’DD-MON-YY’
SYSDATE est une fonction qui renvoie :
La date
L’heure
33/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions de dates
Calcul arithmétique sur les dates
Calcul arithmétique sur des dates:
ajout ou soustraction d’un nombre de jour à une date afin d’obtenir une date
résultante
Ajout ou soustraction d’un nombre d’heures à une date en divisant le nombre d’heures
par 24
soustraction d’une date d’une autre afin de déterminer le nombre de jours entre les
deux dates
Exemple
SELECT first name,
(SYSDATE-hire date) AS jours,
(SYSDATE-hire date)/7 AS semaines
FROM employees;
34/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions de dates
35/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions de dates
Exemple
SELECT systimestamp,
extract(year from systimestamp) as Année,
extract(month from systimestamp) as Mois,
extract(day from systimestamp) as Jour,
extract(hour from systimestamp) as Hour,
extract(minute from systimestamp) as Minutes,
extract(second from systimestamp) as Secondes
FROM dual;
Publicité
36/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions de conversion
Conversion implicite
Pour les affectations, le serveur Oracle peut convertir
automatiquement les types de données suivants:
L’expression salary=‘2000’ entraˆıne la conversion implicite de la
chaˆıne ‘2000’ en valeur numérique 2000
L’expression hire date>’01-Jan-90’ entraˆıne la conversion implicite
de la chaˆıne ’01-Jan-90’ en date
37/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions de conversion
Conversion explicite
38/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions de conversion
Conversion explicite: fonction TO CHAR
39/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions de conversion
Conversion explicite:fonction TO CHAR
Exemple 1
SELECT last name, TO CHAR(hire date, ’fm DD Month YYYY’) AS HIREDATE FROM
employees;
fm permet de supprimer les espaces de remplissage ou les zéros de
début
Exemple 2
SELECT first name, TO CHAR(salary, ’$99,000.00’) SALARY FROM employees;
40/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions de conversion
Conversion explicite:fonctions TO DATE / TO NUMBER
Exemple 1
select first name, hire date from emp
where hire date>to date(’01/01/1982’,’DD-
MM-YYYY’);
Exemple 2
Select first name, salary from employees
where salary>=to number(’15000’);
41/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Autres fonctions
Fonction DECODE
La fonction decodepermet de faire un traitement conditionnel sur les données :
Syntaxe
Decode (expr, val1, res1, val2, res2, . . . . ValN, resN, default)
retourne res1 si expr = val1, res2 si expr=val2,...,resN si expr=valN sinon default
Exemple
SELECT first name,department id, decode(department id,10, ’ACCOUNTING’, 20, ’RESEARCH’,
’DEP. INCONNU ’) AS ”NOM DEPARTEMENT” FROM employees;
42/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Autres fonctions
Fonction NVL / NVL2
NVL(expr,val): retourne val si expr est NULL
NVL2(expr,val1,val2): retourne val1 si expr est NOT NULL et val2 si expr est NULL
expr peut être de type date, les caractère et valeur numérique. Les types de données de expr
et val doivent correspondre.
Exemple
SELECT first name, salary, commission pct,
NVL(commission pct,0),
NVL(to char(commission pct), ’Pas de commission’) AS ”commission ?”,
NVL2(commission pct,commission pct*salary,0) AS ”commision”,
to char(NVL2(commission pct,commission pct*100/salary,0)) || ’%’ AS ”pourcentage commission”
FROM employees;
43/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Autres fonctions
Fonction NULLIF
NULLIF (expr1, expr2) : retourne NULL si expr1= expr2, sinon
retourne expr1
Exemple
SELECT first name, LENGTH(first name) nbr1, last name, LENGTH(last name) nbr2,
NULLIF(LENGTH(first name), LENGTH(last name)) result FROM employees;
44/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Autres fonctions
Fonction COALESCE
Coalesce (exp1,expr2,expr3,. . . ) : retourne la première valeur non
nulle
Exemple
select Coalesce(NULL,1,NULL,7) from dual;
=> retourne 1
45/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Autres fonctions
Fonction CASE
La fonction case évalue une liste de conditions et retourne un résultat parmi les cas possibles
Syntaxe
1 case <expression>
when <valeur1> then <resultat1>
.
.
.
when <valeurN> then <resultatN>
else resultat
end
2 case
when <condition1> then <resultat1>
.
.
.
when <conditionN> then <resultatN>
else resultat
end
46/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Autres fonctions
Fonction CASE
Exemple
SELECT first name, department id,
case department id
when 10 then ’Accounting’
when 20 then ’RESEARCH’
else ’INCONNU’
end as departement
FROM employees;
47/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions analytiques
Les fonctions analytiques calculent une valeur globale basée sur un groupe de lignes. Ils
diffèrent des fonctions de groupe (ou d’agrégation) en ce qu’ils renvoient plusieurs lignes
pour chaque groupe.
Les fonctions analytiques sont la derniere série d’opérations effectuées dans une requête a
l’exception de la clause finale ORDER BY. Par conséquent, elles analytiques ne peuvent
apparaˆıtre que dans la liste de sélection ou clause ORDER BY.
Syntaxe d’une fonction analytique
fonction analytique(expression) OVER( [clause partitionnement] [clause ordre])
clause partitionnement: sous forme PARTITION BY expression1,expression2,...,expressionN
: définit les groupes de partitionnement
clause ordre: sous forme ORDER BY expression1,expression2,...,expressionN
[NULLSFIRST|LAST] : définit l’ordre à l’intérieur de chaque partition
NULLSFIRST/LAST: indique si les valeurs nulles seront en premier ordre/dernier
ordre
48/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions analytiques
Fonction ROW NUMBER()
La fonction row number() retourne le numéro séquentiel d’une ligne
dans une partition de résultats, en commençant a 1 pour la premiere
ligne de chaque partition.
Exemple 1
SELECT employee id,department id, salary, row number() over(order by salary DESC) ”N
FROM employees ;
(cid:176)
Salaire”
49/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions analytiques
Fonction ROW NUMBER()
Exemple 2
SELECT employee id,department id, salary,
row number() over(partition by
department id order by salary DESC) ”Rang
Salaire” FROM employees;
Exemple 3
SELECT employee id,department id,job id,
salary, row number() over(partition by
department id,job id order by salary DESC)
”Rang Salaire” FROM employees;
50/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions analytiques
Fonction RANK()
La fonction rank() retourne le rang dechaque ligne au sein de la
partition d’un ensemble de résultats
Exemple
SELECT employee id,department id, salary, rank() over(partition by department id order by salary
DESC) ”Rang Salaire” FROM employees;
51/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions analytiques
Fonction DENSE RANK()
La fonction dense rank() retourne le rang des lignes à l’intérieur de
la partition d’un ensemble de résultats,sans aucun vide dans le
classement
Exemple
SELECT employee id,department id, salary, dense rank() over(partition by department id order by
salary DESC) ”Rang Salaire” FROM employees;
Publicité
52/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions analytiques
Fonction FIRST VALUE()
La fonction first value() retourne la première valeur d’une partition
Exemple
SELECT employee id,department id, salary, first value(salary) over(partition by department id
order by salary ) as first valeur from employees;
53/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions analytiques
Fonction LAST VALUE()
La fonction last value() retourne la dernière valeur d’une partition
Exemple
SELECT employee id,department id, salary, last value(salary) over(partition by department id
order by salary ) as last valeur from employees;
54/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions multi-lignes
Les fonctions multi-lignes (appelées aussi de groupe ou d’agrégation) opèrent sur
des ensembles de lignes afin de renvoyer un seul résultat par groupe.
Les fonctions de groupe les plus utilisées:
AVG([distinct | all] expr) : valeur moyenne en ignorant les valeurs NULL
COUNT ([* | distinct | all] expr) : nombre de lignes où expr est différente
de NULL. Le caractère * comptabilise toutes les lignes sélectionnées
MAX ([distinct | all] expr) : valeur maximale en ignorant les valeurs NULL
MIN([ distinct | all] expr) : valeur minimale en ignorant les valeurs NULL
STDDEV([distinct | all] expr) : ecart-type en ignorant les valeurs NULL
SUM([distinct | all] expr) : somme en ignorant les valeurs NULL
VARIANCE([distinct | all] expr) : variance en ignorant les valeurs NULL
55/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions multi-lignes
Syntaxe
SELECT [colonne,] fonction groupe(colonne), ...
FROM <nom table>
[WHERE <condition>]
[GROUP BY colonne]
[ORDER BY colonne];
Exemple 1
SELECT trunc(AVG(salary),3), SUM(salary), MAX(hire date), MIN(hire date)
FROM employees
WHERE department id in(80,90);
56/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions multi-lignes
Exemple 2
SELECT COUNT(*)
FROM employees
WHERE department id in(80,90);
=>retourne le nombre de lignes qui vérifient la condition de la clause WHERE
Exemple 3
SELECT COUNT(commission pct) ”count”, COUNT(all commission pct) ”all”,
COUNT(DISTINCT commission pct) ”distinct”
FROM employees
WHERE department id in(80,90);
=>COUNT(expr) /COUNT(all expr): retourne le nombre de ligne ayant des valeurs non NULL de expr
=>COUNT(distinct expr): retourne le nombre de ligne ayant des valeurs non NULL et DISTINCT de expr
57/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions multi-lignes
Remarque
Les fonctions de groupe ignorent les valeurs NULL de la colonne
Exemple:
SELECT trunc(AVG(commission pct) ,3) FROM employees;
La fonction NVL force les fonctions de groupe à inclure les valeurs
NULL
Exemple:
SELECT trunc(AVG( NVL(commission pct,0) ) ,3) FROM employees;
58/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions multi-lignes
La clause GROUP BY
Exemple 1
SELECT department id, trunc(AVG(salary),3)
FROM employees WHERE department id in(80,90)
GROUP BY department id;
Notez Bien
Toute colonne ou expression de la liste SELECT qui ne constitue pas une fonction d’agrégation
doit figurer dans la clause GROUP BY
Exemple:
SELECT department id, job id, trunc(AVG(salary),3)
FROM employees WHERE department id in(80,90)
GROUP BY department id, job id
59/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions multi-lignes
La clause HAVING
La clause HAVING permet de restreindre l’affichage des résultats de
groupes à ceux qui vérifient la condition dans cette clause
Les lignes sont regroupées
La fonction de groupe est appliquée
Les groupes qui correspondent à la clause HAVING sont affichés
Syntaxe
SELECT [colonne,] fonction groupe(colonne), ...
FROM <nom table>
[WHERE <condition>]
[GROUP BY colonne]
[HAVING <condition groupe>]
[ORDER BY colonne];
60/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions multi-lignes
La clause HAVING
Exemple 1
SELECT department id, MAX(salary)
FROM employees
WHERE job id LIKE ’%REP’
GROUP BY department id
ORDER BY department id;
Exemple 2
SELECT department id, MAX(salary)
FROM employees
WHERE job id LIKE ’%REP’
GROUP BY department id
HAVING MAX(salary)>=10000
ORDER BY department id;
61/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions multi-lignes
L’opérateur ROLLUP
L’opérateur ROLLUP : calcule des agrégats (SUM, COUNT, MAX,
MIN, AVG) à tous les niveaux de totalisation sur une hiérarchie de
dimensions et calcule le total général selon l’ordre de gauche à droite
dans la clause GROUP BY
S’il y a n colonnes de regroupements, GROUP BY ROLLUP génère
n+1 niveaux de totalisation
ROLLUP (a, b, c)
(a, b, c)
(a, b)
(a)
()
62/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions multi-lignes
L’opérateur ROLLUP
Exemple
SELECT Department id, JOB id,manager id, SUM (SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT ID in (10,20,30)
GROUP BY Rollup(Department id, JOB id,manager id);
63/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions multi-lignes
L’opérateur CUBE
L’opérateur CUBE : calcule des agrégats (SUM, COUNT, MAX, MIN, AVG) à
différents niveaux d’agrégation comme ROLLUP mais de plus permet de calculer
toutes les combinaisons d’agrégations :
L’opérateur CUBE: calcule des sous-totaux pour toutes les combinaisons
possibles d’un ensemble de colonnes de regroupement
Si la clause CUBE contient n colonnes, CUBE calcule 2n combinaisons de totaux
CUBE (a, b, c)
(a, b, c)
(a, b)
(a, c)
(a)
(b, c)
(b)
(c)
()
64/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions multi-lignes
L’opérateur CUBE
Exemple
SELECT Department id, JOB id, SUM (SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT ID in (10,20,30)
GROUP BY Cube(Department id, JOB id);
65/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions multi-lignes
La fonction GROUPING
Les lignes de totaux correspondent génaralement aux lignes ayant
des valeurs NULL
=>possibilité de confusion si les lignes contiennent déja des valeurs
NULL !
La fonction GROUPING permet d’éliminer cette ambiguité. Elle
accepte une seule colonne comme paramètre et retourne:
1 si la colonne contient une valeur null généré dans le cadre d’un
sous-total par un ROLLUP ou CUBE
0 pour une autre valeur, y compris les valeurs NULL stockées
66/90
Extraction de données
Les fonctions
Les sous intérrogations
Les jointures
Les opérateurs ensemblistes
Les fonctions mono-ligne
Les fonctions analytiques
Les fonctions multi-lignes
Les fonctions multi-lignes
...