Le-langage-SQL-Part2 (3)

Plan
•
•
•
•
Introduction
La sélection simple
Les fonctions
Extraction de données à partir de plusieurs
tables
• Les sous-requêtes
• La manipulation de données
• La gestion des transactions
Plan
• Introduction
• La sélection simple
• Les fonctions
• Extraction de données à partir de plusieurs
tables
• Les sous-requêtes
• La manipulation de données
• La gestion des transactions
Les fonctions
Une fonction a:
Un ou plusieurs arguments ou paramètres
(pouvant être une colonne, une expression, une
constante ou le résultat d’une fonction).
Un seul résultat sous forme de valeur.
• Il existe deux types de fonctions:
Les fonctions qui opèrent sur les lignes .
Les fonctions qui opèrent sur des groupes de
lignes.
• Une fonction :
Un ou plusieurs arguments ou paramètres
(pouvant être une colonne, une expression, une
constante ou le résultat d’une fonction).
Un seul résultat sous forme de valeur.
• Il existe deux types de fonctions:
Les fonctions qui opèrent sur les lignes .
Les fonctions qui opèrent sur des groupes de
lignes.
Les fonctions de lignes
Concernent une seule ligne et retournent un seul
résultat par ligne (utilisées dans la clause SELECT, dans la
clause WHERE ou encore dans la clause ORDER BY);
Cinq types de fonctions de lignes (selon le type de
données de leurs arguments principaux )
Les fonctions de caractères:
• Au moins un argument de type chaîne de caractères.
• Un seul résultat de type chaîne de caractères ou nombre.
Exemple 1:
LOWER(arg) : renvoie l’argument mais en minuscule.
REQ 20
SELECT LOWER(‘NOM’) NOM, LOWER(ENAME) FROM EMP ;
(Suite)
Exemple 2:
LENGTH(arg) : retourne la taille de l’argument en nombre de
caractères.
REQ 21
Afficher le nombre d’occurrences du caractère ‘E’ dans chaque
nom d’employé ?
SELECT LENGTH(ENAME)–LENGTH(REPLACE(ENAME,’E’)) NBRE
FROM EMP ;
(Voir annexe B pour la liste des fonctions de caractères)
Les fonctions numériques
(Voir annexe B pour la liste des fonctions numériques)
Les fonctions de date
• Le format de date par défaut est : DD-MON-YY.
• Oracle stocke une date suivant un format numérique
interne plus détaillé : siècle, année, mois, jour, heure,
minute et seconde.
• La pseudo colonne SYSDATE de la table DUAL retourne
la date système (inclut aussi l’heure système).
(Voir annexe B)
Les fonctions de conversion
• Conversion entre types de données.
• Le serveur Oracle peut convertir implicitement, sans
l’utilisation d’une fonction :
1. VARCHAR2/CHAR
NUMBER
2. VARCHAR2/CHAR
DATE
3. DATE
VARCHAR2 /CHAR
4. NUMBER
VARCHAR2/CHAR
La conversion explicite est faite à travers un ensemble
de fonctions de conversion (voir annexe B)
(Suite)
REQ 22
SELECT TO_CHAR(SYSDATE,’DD/MM/YYYY’) FROM DUAL;
Affiche : 08/11/2020
REQ 23
SELECT TO_CHAR(SYSDATE,’DAY’) FROM DUAL;
Affiche : ‘SATURDAY’
Les fonctions opérant sur tous les types de données
Exemple:
DECODE(arg,SEARCH1,val1,[SEARCH2,val2,…],default_arg)
Cette fonction retourne vali si arg=SEARCHi, si aucune
valeur SEARCHi ne correspond à l’argument arg, alors
default_arg sera retournée.
(voir annexe B)
(Suite)
REQ 24
Afficher les salaires de tous les employés sauf les
managers à qui on affichera ‘MMM’ et le président à
qui on affichera ‘PPP’ ?
SELECT ENAME,
DECODE(JOB, ‘MANAGER’,
‘PRESIDENT’, ’PPP’, SAL) SALAIRE FROM EMP;
‘MMM’,
Les fonctions de groupe
Les fonctions de groupe opèrent sur des groupes de
lignes et retournent un seul résultat par groupe.
Deux manières de faire:
1. Traiter la table comme étant un seul groupe de
lignes.
2. Diviser la table en petits groupes (clause GROUP BY)
1. Traiter la table comme étant un seul groupe de lignes:
Exemple 1:
AVG([DISTINCT|ALL] arg) : retourne la moyenne des
arguments pour chaque groupe tout en ignorant les
valeurs NULL.
REQ 25
Afficher la moyenne des commissions des employés.
SELECT AVG(COMM) MOYENNE FROM EMP ;
(Suite)
REQ 26
Afficher la moyenne des salaires annuels des employés du
département 10.
SELECT AVG(SAL*12+NVL(COMM,0)) MOYENNE FROM
EMP WHERE DEPTNO=10;
(Suite)
Exemple 2:
COUNT(*|[DISTINCT]|[ALL] arg) : retourne pour chaque groupe le
nombre de lignes où l’argument est différent de NULL.
COUNT(*): retourner le nombre de lignes total.
REQ 27
Afficher le nombre de lignes de la table DEPT.
SELECT COUNT(*) “NB LIGNES” FROM DEPT ;
REQ 28
Afficher le nombre de jobs dans l’entreprise.
SELECT COUNT(DISTINCT JOB) NB_JOB FROM EMP ;
(Suite)
Exemple 3:
MAX([DISTINCT|ALL] arg) : retourne la valeur maximale de
l’argument pour chaque groupe.
REQ 29
Afficher le salaire maximum.
SELECT MAX(SAL) SAL_MAX FROM EMP ;
(Suite)
Exemple 4:
MIN([DISTINCT|ALL] arg) : retourne la valeur minimale de
l’argument pour chaque groupe.
REQ 30
Afficher le salaire minimum.
SELECT MIN(SAL) SAL_MIN FROM EMP ;
Remarques:
• Le mot clé DISTINCT oblige une fonction de groupe à ne
considérer que les lignes distinctes.
• ALL l’oblige à considérer les valeurs dupliquées. (par
défaut).
• Toutes les fonctions de groupe ignorent les valeurs
NULL.
Pour considérer cette valeur spéciale, on utilise la
fonction NVL.
2. Diviser la table en petits groupes :
La clause GROUP BY :
Suivie d’une colonne.
Les lignes qui sont incluses dans chaque
groupe résultant ont la même valeur pour
cette colonne.
REQ 31
Afficher le nombre d’employés dans chaque département.
SELECT DEPTNO, COUNT(*) NBR_EMP FROM EMP
GROUP BY DEPTNO ;
REQ 32
Afficher la moyenne des salaires par job dans le département 30.
SELECT JOB, AVG(SAL) FROM EMP
WHERE DEPTNO=30
GROUP BY JOB ;
Remarques:
• On ne peut mettre dans la clause SELECT que les
fonctions de groupe et/ou les colonnes qui suivent la
clause GROUP BY.
• Groupes dans les groupes:
Pour créer des groupes à l’intérieur des groupes
Lister plus qu’une colonne dans la clause GROUP BY.
REQ 33
Afficher la moyenne de salaires des employés pour
chaque job à l’intérieur de chaque département.
SELECT DEPTNO, JOB, AVG(SAL) FROM EMP
GROUP BY DEPTNO, JOB ;
La clause HAVING:
Sert à restreindre les groupes retournés.
Suivie d’une condition simple ou composée
qui concerne les colonnes qui suivent la clause
GROUP BY, ou/et des fonctions de groupe.
REQ 34
Afficher les moyennes de salaires de chaque département
s’il inclut plus de 3 employés.
SELECT DEPTNO, AVG(SAL) FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) > 3 ;
REQ 35
Afficher la date d’embauche du dernier salarié recruté pour
chaque unité de travail et par département (une unité de
travail regroupe tous les employés de même directeur). Exclure
les groupes dont le salaire minimum est inférieur à 1000.
Exclure tous les employés dont le nom commence par ‘C’.
SELECT MGR, DEPTNO , MAX(HIREDATE) FROM EMP
WHERE ENAME NOT LIKE ‘C%’
GROUP BY MGR, DEPTNO
HAVING MIN(SAL)>=1000;
A suivre…