LES FONCTIONS SQL

Le langage SQL dispose de plusieurs types de fonctions qui permettent de manipuler les données stockées dans les SGBD. On a les fonctions Scalaires, les fonctions Systèmes, les fonctions de chaines de caractères, les fonctions numériques, les fonctions temporelles, … . Les plus courants sont les fonctions arithmétiques appelées encore fonctions d’agrégation statiques.

Les fonctions d’agrégation (association) statiques permettent de faire des opérations (calculs) sur un ensemble de valeurs et retourner un résultat unique. Elles associent plusieurs valeurs (lignes) en entrée et renvoient une valeur unique en sortie sauf dans le cas où elles utilisent la clause GROUP BY. Elles sont :

La fonction AVG () ;

La fonction COUNT () ;

La fonction SUM () ;

La fonction MAX () ;

La fonction MIN () ; … .

Ces fonctions sont écrites en majuscules. La fonction d’agrégat se place devant le nom du champ qui est mis entre parenthèses par exemple AVG (prix). Prix est le nom de l’attribut (colonne) et AVG la fonction. Toutes ces fonctions d’agrégation ne prennent pas en compte la valeur « NULL ».

Remarque : On ne mélange pas une fonction d’agrégat avec d’autres champs (attributs). Associer une fonction d’agrégat avec d’autres attributs entraine le renvoie de faux résultats. Voyez les syntaxes suivantes.

La syntaxe de ces fonctions :

SELECT  Fonction_agrégat  FROM nom_table ;

Ou

SELECT  Fonction_agrégat]   nom + FROM nom_table ;

Par exemple nous pouvons dire nom = Moyenne de classe.

La syntaxe avec la clause WHERE et GROUP BY :

SELECT  attribut_p Fonction_agrégat  FROM nom_table WHERE/GROUP BY  attribut_p ;

Attribut_p = Attribut de partitionnement.

Une requête qui contient une fonction d’agrégation est une requête qui est composée uniquement de la fonction d’agrégat et d’un ou de plusieurs attribut de partitionnement.

 

Ou

SELECT attribut_p  Fonction_agrégat AS nom  FROM Table WHERE/GROUP BY attribut_p ;

Nous allons utiliser la table emlpoyee  dans laquelle nous allons lancer des requêtes avec chaque fonction.

Table employee

id_employeenamegradesalaryage
1Jones245019
2Michael120025
3William118020
4Charles349030
5Jack121028
6Hilary214017

 

 

 

La fonction AVG ()

La syntaxe donne :

SELECT AVG (nom_colonne)  FROM nom_table ;

Cette fonction permet de calculer la moyenne des valeurs en nombres d’une colonne. La fonction AVG fonctionne uniquement sur les valeurs de type numérique (les nombres).

Exemple on affiche la moyenne des salaires des emploés de la table employee.

SELECT AVG (Salaire) FROM employee ;

Résultat :

AVG (Salaire)
278.3333

 

On a calculé la moyenne de des salaires de toutes les personnes de la table employee.

Supposons qu’on désirait calculer le salaire moyen de quelques employés d’un niveau donné, dans ce cas il faut utiliser la clause WHERE qui permet de faire la sélection avec précision.

La fonction AVG avec la clause WHERE

La syntaxe donne :

SELECT AVG (nom_colonne)  FROM nom_Table  WHERE condition ;

AVG avec WHERE permet de sélectionner certaines lignes ou valeurs d’une colonne selon les spécificités que l’on désire. Les spécificités dans notre cas ici sont les différentes valeurs que contiennent les colonnes de notre table employee.

Nous pouvons calculer le salaire moyen des employés selon l’âge (exemple ceux qui ont 20 ans), selon le niveau (exemple le niveau 2) et selon les noms (ceux qui portent le nom « Jones » par exemple).

On affiche le salaire moyen des employés de niveau 2.

SELECT  AVG(Salaire) FROM employee WHERE grade = 2 ;

AVG (Salaire)
295.0000

 

On a obtenu la moyenne des salaires des employés du niveau 2.

Supposons que l’on veut obtenir les salaires moyens de tous les employés.

On se rappelle que les fonctions d’agrégations agrègent (associent) plusieurs valeurs en entrée et renvoient en sortie un résultat (valeur) unique. Pour avoir des résultats uniques classés par groupe, on utilise la clause GROUP BY.

 

La fonction AVG avec la clause GROUP BY

La syntaxe1 :

SELECT AVG (nom_colonne) FROM Table  GROUP BY  nom_colonne ;

Ou

Syntaxe2 :

SELECT attribut_p AVG (nom_colonne) FROM nom_table  GROUP BY  attribut_p ;

AVG avec WHERE permet de calculer une ou plusieurs moyennes classées selon le nombre de différentes valeurs d’une colonne. Le nombre de résultat unique en sortie dépend du nombre de valeurs différentes présentes dans la colonne choisie.

Par exemple on va afficher les moyennes des salaires des employés selon le niveau. N’oubliez pas que dans notre table, l’attribut grade contient des niveaux (1, 2, 3).

SELECT grade, AVG(Salary) FROM employee GROUP BY grade ;

gradeAVG(Salary)
1196.6667
2295.0000
3490.0000

 

On a types de niveau : le grade 1, le grade 2 et le grade 3 avec chacun son salaire moyen.

 

La fonction COUNT () ;

Cette fonction permet de compter les lignes (valeurs) d’une colonne. Elle est utilisée pour les valeurs numériques et les valeurs non numériques puisse qu’il s’agit de compter les lignes.

Dans une requête, l’expression « COUNT (*) » n’élimine pas les « NULL », c’est-à-dire elle compte les lignes qui contiennent des valeurs et les lignes qui ne contiennent pas des valeurs.

Par contre l’expression « COUNT (nom_colonne) »  élimine les « NULL » mais prend en compte tous les doublons. Elle compte uniquement les lignes qui ont des valeurs.

L’expression « COUNT (DISTINCT nom_colonne) » quant à elle élimine les « NULL » et les doublons. Elle prend en compte uniquement les lignes qui ont des valeurs différentes.

Fonction COUNT ()Prend en compte
COUNT(*)Les lignes
COUNT (nom_colonne)Les lignes qui ont des valeurs
COUNT (DISTINCT nom_colonne)Les lignes qui ont des valeurs différentes

 

Nous allons modifier notre table auparavant pour pouvoir tester chaque cas évoqué dans cette fonction COUNT ().

Voici notre table workers1 modifié :

id_employeenamegradesalaryage
1Jones245019
2Michael1NULLNULL
3William118020
4Charles3NULL30
5Jack1210NULL
6Hilary2140NULL
7William118020

 

COUNT (*) 

On affiche le nombre total des valeurs (enregistrements) de la table workers1.

SELECT  COUNT(*) AS ‘T_Valeurs’ FROM employee ;

Résultat :

T_Valeurs
7

 

En voyant le résultat, on remarque que la fonction particulière « COUNT (*) » a juste compté les lignes de notre table workers1, ce qui confirme que COUNT(*) compte les lignes contenant des valeurs et des lignes qui ne contiennent pas de valeurs.

COUNT (nom_attribut) 

On affiche le nombre total  des personnes qui sont salariées de la table employee.

SELECT COUNT (Salary)  AS ‘Nombre’ FROM employee ;

Résultat :

Nombre
5

 

On constate que les deux lignes contenant « NULL » n’ont pas été comptées.

COUNT (DISTINCT nom_attribut)

On affiche le nombre total  des employés différents qui sont salariées de la table employee.

SELECT COUNT (DISTINCT Salaire)  AS ‘Nombre ’ FROM employee ;

Résultat :

Nombre
4

 

En comparant les données de la table employee au résultat, on remarque qu’un salarié n’est pas compté. Étant donné que la table contient un doublon, ce doublon a été éliminé par

« COUNT (DISTINCT nom_attribut).

 

La fonction COUNT () avec la clause WHERE

La clause WHERE va permettre de sélectionner les lignes de certaines valeurs spécifiées. Nous  pouvons par exemple afficher le nombre de lignes de grade 1, ou 2 ou 3 de notre table employee.

COUT(*) avec WHERE

On affiche le nombre de valeurs (lignes) pour le niveau 1 de employee.

SELECT COUNT(*)  AS ‘Number’ FROM employee WHERE grade = 1;

Résultat :

Number
4

 

Le constat est que les lignes contenant des valeurs et les lignes nulles du grade 1 ont  été prises en compte.

COUNT (nom_attribut) avec WHERE

On affiche le nombre de personnes salariés du grade 1 de la table employee.

SELECT COUNT(Salary) FROM employee WHERE grade = 1 ;

Résultat :

COUNT(Salary)
3

 

La valeur NULL n’a pas été prise en compte.

COUNT (DISTINC nom_attribut) avec WHERE

On renvoie le nombre d’employés salariés différents du grade 1.

SELECT COUNT (DISTINCT Salary)  AS ‘Number’ FROM employee WHERE grade = 1 ;

Résultat :

Number
2

 

Le doublon et la valeur NULL ont été exclus.

La fonction COUNT () avec la clause GROUPE BY

Table employee

id_employeenamegradeSalaryage
1Jones245019
2Michael1NULLNULL
3William118020
4Charles3NULL30
5Jack1210NULL
6Hilary2140NULL
7William118020

 

COUT(*) avec GROUP BY

On renvoie le nombre de lignes de chaque grade (1, 2, 3).

SELECT grade, COUNT(*) AS ‘Lines’ FROM employee GROUP BY grade ;

Résultat :

gradeLines
14
22
31

 

Ici toutes les lignes de chaque niveau ont été comptées.

COUNT (nom_attribut) avec GROUP BY

On renvoie le nombre de personnes salariées de chaque grade (1, 2, 3).

SELECT grade, COUNT(Salary) AS ‘Number’ FROM employee GROUP BY grade ;

Résultat :

gradeNumber
13
22
30

 

On constate que la ligne NULL du niveau 3 n’a pas été prise en compte.

 

COUNT (DISTINCT nom_attribut) avec GROUP BY

On renvoie le nombre de personnes salariées différentes de chaque grade (1, 2, 3).

SELECT Niveau, COUNT (DISTINCT Salary) AS ‘Number’ FROM employee GROUP BY grade ;

Résultat :

gradeNumber
12
22
30

 

En voyant le résultat, on voit que les lignes contenant des valeurs NULL et le doublon ont été exclus

 

La fonction SUM () ;

La fonction SUM permet de calculer la somme de toutes les valeurs d’une colonne donnée.

La syntaxe donne :

SELECT SUM (nom_colonne)  FROM nom_table ;

On affiche la somme des salaires des employés de la table employee.

SELECT SUM (Salary) AS ‘somme’ FROM employee ;

Résultat :

somme
1670

 

La fonction SUM () avec la clause WHERE

La fonction SUM () avec WHERE permet de calculer le somme des valeurs de certaines catégories de lignes. Si on se réfère à notre table, on peut calculer la somme de salaires des employés de grade 1 ou grade 2 ou grade 3.

Par exemple on affiche la somme des personnes de grade 2.

SELECT SUM (Salary) AS ‘somme ’ FROM employee WHERE grade = 2 ;

Résultat :

somme
590

 

La fonction SUM () avec la clause GROUPE BY

GROUP BY dans une fonction SUM () permet de renvoyer les sommes par groupe de valeurs d’une colonne. Ici on va calculer la somme d’âges de chaque grade : grade 1, 2 et 3.

On affiche le niveau avec la somme d’âges des personnes de la table employee.

SELECT grade, SUM (age) AS ‘Somme’ FROM employee GROUP BY grade ;

Résultat :

gradeSomme
173
236
330

 

La fonction MAX () ;

La syntaxe donne :

SELECT MAX (nom_colonne)  FROM nom_table ;

Elle permet de récupérer la plus grande valeur (plus grand nombre) d’une colonne. Elle fonctionne sur les valeurs numérique comme son opposé la fonction MIN ().

On affiche le salaire maximal des employes de la table employee.

SELECT MAX(Salary) FROM employee ;

MAX (Salary)
490

Ou

SELECT MAX(Salary) AS ‘Salaire’ FROM employees ;

Salaire
490

 

La fonction MAX avec la clause WHERE

La fonction MAX avec WHERE permet de renvoyer la plus grande valeur en nombre d’un groupe donné. Par exemple nous pouvons afficher le plus âgé des personnes du grade 1.

On affiche l’âge de l’employé le plus âgé de grade 1 de la table employee.

SELECT MAX (Age) AS ‘great’ FROM employee WHERE grade = 1 ;

Résultat :

great
28

 

La fonction MAX avec la clause GROUP BY

La fonction MAX avec GROUP BY permet de renvoyer la plus grande valeur parmi les différents groupes de valeurs qui existent dans une colonne.

On va afficher le salaire le plus élevé de chaque niveau de la table employee.

SELECT grade, MAX (Salary) AS ‘Salaire’ FROM employee GROUP BY grade ;

Résultat :

gradeSalaire
1210
2450
3490

 

La fonction MIN () ;

SELECT MIN (nom_colonne)  FROM nom_table ;

Elle permet de récupérer la plus petite valeur (plus petit nombre) d’une colonne.

On affiche le salaire minimal des personnes de la table employee.

SELECT MIN (salary) FROM employee;

MIN (Salaire)
140

 

La fonction MIN avec la clause WHERE

La fonction MIN () avec WHERE permet de renvoyer la plus petite valeur en nombre d’un groupe donné. Par exemple nous pouvons afficher le moins âgé des personnes du grade 1.

On affiche l’âge de la personne la moins âgée de grade 1 de la table employee.

SELECT MIN (age) AS ‘small’ FROM employee WHERE grade = 1 ;

Résultat :

small
20

 

La fonction MIN avec la clause GROUP BY

La fonction MAIN () avec GROUP BY permet de renvoyer la plus petite valeur parmi les différents groupes de valeurs qui existent dans une colonne. N’oubliez pas qu’il s’agit des nombres ici.

On va afficher le salaire le plus bas de chaque niveau de la table employee.

SELECT grade, MIN (Salary) AS ‘Salaire’ FROM employee GROUP BY grade ;

Résultat :

gradeSalaire
1180
2140
3490