Extraction de Données

14/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Instruction SELECT

Restriction de donn´ees

Tri de donn´ees

Op´erateurs de comparaison

L’op´erateur 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Instruction SELECT

Restriction de donn´ees

Tri de donn´ees

Op´erateurs de comparaison

L’op´erateur LIKE

L’op´erateur LIKE permet de rechercher des chaˆınes de caracteres a l’aide de caract`eres

g´en´eriques

Les conditions de recherche peuvent contenir des caract`eres ou des nombres litt´eraux

% repr´esente Z´ero ou plusieurs caract`eres

repr´esente un caract`ere

Exemple

SELECT first name FROM employees

WHERE first name LIKE ’S e%’ ;

16/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Instruction SELECT

Restriction de donn´ees

Tri de donn´ees

Op´erateurs de comparaison

L’op´erateur IS NULL

L’op´erateur IS NULL permet de tester la pr´esence de valeurs NULL.

Exemple

SELECT first name, manager id FROM employees

WHERE manager id IS NULL;

17/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Instruction SELECT

Restriction de donn´ees

Tri de donn´ees

Op´erateurs logiques

18/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Instruction SELECT

Restriction de donn´ees

Tri de donn´ees

Op´erateurs logiques

L’op´erateur AND

Exemple

SELECT last name, job id, salary FROM employees

WHERE salary >=10000 AND job id LIKE ’%MAN%’;

19/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Instruction SELECT

Restriction de donn´ees

Tri de donn´ees

Op´erateurs logiques

L’op´erateur OR

Exemple

SELECT last name, job id, salary FROM employees

WHERE salary >=10000 OR job id LIKE ’%MAN%’;

20/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Instruction SELECT

Restriction de donn´ees

Tri de donn´ees

Op´erateurs logiques

L’op´erateur 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Instruction SELECT

Restriction de donn´ees

Tri de donn´ees

Op´erateurs logiques

R`egles de priorit´e

22/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Instruction SELECT

Restriction de donn´ees

Tri de donn´ees

Op´erateurs logiques

R`egles de priorit´e

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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Instruction SELECT

Restriction de donn´ees

Tri de donn´ees

Tri de donn´ees: la clause ORDER BY

La clause ORDER BY:

permet de triez les lignes extraites

ASC : ordre croissant (par d´efaut)

DESC : ordre d´ecroissant

toujours la derni`ere 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´ecroissant

SELECT last name, hire date FROM employees

ORDER BY hire date DESC ;

–ou bien ORDER By 2 DESC

24/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Instruction SELECT

Restriction de donn´ees

Tri de donn´ees

Tri de donn´ees: 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Plan

1 Extraction de donn´ees

4 Les jointures

2 Les fonctions

5 Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

3 Les sous int´errogations

26/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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`enent un

seul r´esultat

Fonctions analytiques: manipulent plusieurs lignes et ram`enent un

plusieurs r´esultats

Fonctions multi-lignes: manipulent plusieurs lignes et ram`enent un

seul r´esultat

27/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions de caract`eres

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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions de caract`eres

Fonctions de manipulation de caract`eres

29/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions de caract`eres

Fonctions de manipulation de caract`eres

Exemple

SELECT first name, last name, job id,

CONCAT(first name,last name) ”Nom et pr´enom”,

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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions num´eriques

31/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions num´eriques

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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions de dates

Dans la base de donn´ees Oracle, les dates sont stock´ees dans un

format num´eriques interne: si`ecle, ann´ee, mois, jour, heures, minutes

et secondes

Le format de date par d´efaut est ’DD-MON-YY’

SYSDATE est une fonction qui renvoie :

La date

L’heure

33/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions de dates

Calcul arithm´etique sur les dates

Calcul arithm´etique sur des dates:

ajout ou soustraction d’un nombre de jour `a une date afin d’obtenir une date

r´esultante

Ajout ou soustraction d’un nombre d’heures `a une date en divisant le nombre d’heures

par 24

soustraction d’une date d’une autre afin de d´eterminer 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions de dates

35/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´ee,

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;

36/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´ees suivants:

L’expression salary=‘2000’ entraˆıne la conversion implicite de la

chaˆıne ‘2000’ en valeur num´erique 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions de conversion

Conversion explicite

38/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´eros de

d´ebut

Exemple 2

SELECT first name, TO CHAR(salary, ’$99,000.00’) SALARY FROM employees;

40/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´ees :

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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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 ˆetre de type date, les caract`ere et valeur num´erique. Les types de donn´ees 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Autres fonctions

Fonction COALESCE

Coalesce (exp1,expr2,expr3,. . . ) : retourne la premi`ere valeur non

nulle

Exemple

select Coalesce(NULL,1,NULL,7) from dual;

=> retourne 1

45/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Autres fonctions

Fonction CASE

La fonction case ´evalue une liste de conditions et retourne un r´esultat 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions analytiques

Les fonctions analytiques calculent une valeur globale bas´ee sur un groupe de lignes. Ils

diff`erent des fonctions de groupe (ou d’agr´egation) en ce qu’ils renvoient plusieurs lignes

pour chaque groupe.

Les fonctions analytiques sont la derniere s´erie d’op´erations effectu´ees dans une requˆete a

l’exception de la clause finale ORDER BY. Par cons´equent, elles analytiques ne peuvent

apparaˆıtre que dans la liste de s´election 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´efinit les groupes de partitionnement

clause ordre: sous forme ORDER BY expression1,expression2,...,expressionN

[NULLSFIRST|LAST] : d´efinit l’ordre `a l’int´erieur de chaque partition

NULLSFIRST/LAST: indique si les valeurs nulles seront en premier ordre/dernier

ordre

48/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´ero s´equentiel d’une ligne

dans une partition de r´esultats, en commen¸cant 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´esultats

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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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 `a l’int´erieur de

la partition d’un ensemble de r´esultats,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;

52/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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`ere 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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`ere 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions multi-lignes

Les fonctions multi-lignes (appel´ees aussi de groupe ou d’agr´egation) op`erent sur

des ensembles de lignes afin de renvoyer un seul r´esultat par groupe.

Les fonctions de groupe les plus utilis´ees:

AVG([distinct | all] expr) : valeur moyenne en ignorant les valeurs NULL

COUNT ([* | distinct | all] expr) : nombre de lignes o`u expr est diff´erente

de NULL. Le caract`ere * comptabilise toutes les lignes s´electionn´ees

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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´erifient 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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 `a inclure les valeurs

NULL

Exemple:

SELECT trunc(AVG( NVL(commission pct,0) ) ,3) FROM employees;

58/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´egation

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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´esultats de

groupes `a ceux qui v´erifient la condition dans cette clause

Les lignes sont regroup´ees

La fonction de groupe est appliqu´ee

Les groupes qui correspondent `a la clause HAVING sont affich´es

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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions multi-lignes

L’op´erateur ROLLUP

L’op´erateur ROLLUP : calcule des agr´egats (SUM, COUNT, MAX,

MIN, AVG) `a tous les niveaux de totalisation sur une hi´erarchie de

dimensions et calcule le total g´en´eral selon l’ordre de gauche `a droite

dans la clause GROUP BY

S’il y a n colonnes de regroupements, GROUP BY ROLLUP g´en`ere

n+1 niveaux de totalisation

ROLLUP (a, b, c)

(a, b, c)

(a, b)

(a)

()

62/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions multi-lignes

L’op´erateur 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions multi-lignes

L’op´erateur CUBE

L’op´erateur CUBE : calcule des agr´egats (SUM, COUNT, MAX, MIN, AVG) `a

diff´erents niveaux d’agr´egation comme ROLLUP mais de plus permet de calculer

toutes les combinaisons d’agr´egations :

L’op´erateur 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions multi-lignes

L’op´erateur 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´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs 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´enaralement aux lignes ayant

des valeurs NULL

=>possibilit´e de confusion si les lignes contiennent d´eja des valeurs

NULL !

La fonction GROUPING permet d’´eliminer cette ambiguit´e. Elle

accepte une seule colonne comme param`etre et retourne:

1 si la colonne contient une valeur null g´en´er´e dans le cadre d’un

sous-total par un ROLLUP ou CUBE

0 pour une autre valeur, y compris les valeurs NULL stock´ees

66/90

Extraction de donn´ees

Les fonctions

Les sous int´errogations

Les jointures

Les op´erateurs ensemblistes

Les fonctions mono-ligne

Les fonctions analytiques

Les fonctions multi-lignes

Les fonctions multi-lignes

...