Blog ENI : Toute la veille numérique !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. SQL
  3. Les fonctions
Extrait - SQL Les fondamentaux du langage (avec exercices et corrigés) - (5e édition)
Extraits du livre
SQL Les fondamentaux du langage (avec exercices et corrigés) - (5e édition)
1 avis
Revenir à la page d'achat du livre

Les fonctions

Introduction

Les fonctions sont multiples et souvent implémentées différemment dans chaque SGBDR. Nous n’allons pas dérouler ici toutes les fonctions possibles. Nous allons aborder les plus couramment utilisées.

Les fonctions numériques

Tous les opérateurs sont utilisables : +, -, *, / mais également des fonctions comme la valeur absolue, le cosinus, les logarithmes, le modulo, l’arrondi, etc.

Consulter la documentation du SGBDR pour connaître les fonctions qui sont implémentées dans la version de base de données utilisée.

Nous ne serons pas exhaustifs sur toutes les fonctions existantes, mais nous allons décrire quelques fonctions généralement implémentées dans les SGBDR. 

1. ABS : valeur absolue

Exemple

SELECT idTarif, DateDebut, Prix, ABS(Prix) AS ValeurAbsolue FROM Tarifs; 

idTarif

DateDebut

Prix

ValeurAbsolue

1

2024-10-01

49,99

49,99

2

2024-10-01

59,99

59,99

3

2024-10-01

68,99

68,99

4

2024-10-01

59,99

59,99

5

2024-10-01

69,99

69,99

6

2024-10-01

79,99

79,99

7

2024-10-01

89,99

89,99

8

2024-12-15

57,49

57,49

2. Valeur ASCII d’un caractère

Dans cet exemple, on va afficher le code ASCII du premier caractère de la colonne Libelle des hôtels :

SQL Server, MySQL et PostgreSQL

SELECT idHotel, Libelle, ASCII(SUBSTRING(Libelle, 1, 1)) AS Code 
FROM Hotels; 

Oracle, MySQL et PostgreSQL

SELECT idHotel, Libelle, ASCII(SUBSTR(Libelle, 1, 1)) AS Code 
FROM Hotels; 

idHotel

Libelle

Code

1

Ski Hotel

83

2

Art Hotel

65

3

Rose Hotel

82

4

Lions Hotel

76

3. COS : cosinus - SIN : sinus

Dans cet exemple, on va afficher le cosinus du prix de la table Tarifs.

Exemple

SELECT idTarif, DateDebut, Prix, COS(Prix) AS Cosinus FROM Tarifs; 

idTarif

DateDebut

Prix

Cosinus

1

2024-10-01

49,99

0,962294075784641

2

2024-10-01

59,99

-0,955413415572478

3

2024-10-01

68,99

0,992192881610071

Pour obtenir le sinus, c’est la même syntaxe :

SELECT idTarif, DateDebut, Prix, SIN(Prix) AS Sinus FROM Tarifs; 

4. LOG (<numéro base>,<colonne>) : logarithme de la colonne sélectionnée dans la base indiquée

Dans cet exemple, on va afficher le logarithme en base 2 des tarifs.

Exemple

SELECT idTarif, DateDebut, Prix, LOG(2, Prix) AS Log FROM Tarifs; 

idTarif

DateDebut

Prix

Log

1

2024-10-01

49,99

0,177192879928986

2

2024-10-01

59,99

0,169300699821586

5. MOD(<colonne>,<valeur>) : modulo

Le modulo donne le reste de la division d’une colonne par une valeur. Dans cet exemple, on va afficher le modulo du prix divisé par 4.

Oracle, MySQL et PostgreSQL

SELECT idTarif, DateDebut, Prix, MOD(Prix, 4) AS Modulo FROM Tarifs; 

idTarif...

Les fonctions de gestion des dates et heures

Il existe une multitude de formats d’affichage, d’utilisation et de fonctions dans chaque SGBDR, nous ne pouvons pas tous les décrire ici. Nous verrons dans la section Les différents formats d’affichage des dates les formats les plus couramment utilisés et la méthode pour les manipuler.

1. Date du jour : CURRENT_DATE

Pour Oracle, il existe CURRENT_DATE et SYSDATE. Pour MySQL, on peut utiliser CURRENT_DATE, CURDATE, NOW. Pour SQL Server, c’est GETDATE() et SYSDATETIME().

Ces fonctions permettent de retourner la date du jour, avec des précisions différentes (millisecondes, nanosecondes…). En fonction du SGBDR, il existe différentes fonctions qui réalisent la même chose et qui sont synonymes.

Petite particularité pour Oracle et SQL Server, SYSDATE et SYSDATETIME() donnent la date du serveur sur lequel est installée la base de données et CURRENT_DATE ou GETDATE() donnent la date de la session de l’utilisateur.

Par exemple, si la base est installée sur un serveur américain et que l’utilisateur est en France, il y aura une différence de 6 heures entre les deux dates.

Oracle

SELECT CURRENT_DATE, SYSDATE FROM DUAL;  

MySQL

SELECT CURRENT_DATE,CURRENT_DATE(), CURDATE(), NOW() FROM 
DUAL; 

SQL Server

SELECT GETDATE() AS dateSession, GETUTCDATE() AS dateUTCSession, 
CURRENT_TIMESTAMP AS dateSession, SYSDATETIME() AS dateSystème, 
SYSUTCDATETIME() AS dateUTCSystème, SYSDATETIMEOFFSET() AS 
dateOffsetSystème; 

PostgreSQL

SELECT CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP, now(),  
transaction_timestamp(), statement_timestamp(), clock_timestamp(), timeofday(); 

Dans MySQL, SQL Server et PostgreSQL, le () signifie qu’il s’agit d’une fonction. S’il n’y a pas de parenthèses, c’est une variable.

Il existe quelques différences d’affichage entre ces fonctions et variables. NOW par exemple affiche la date et l’heure courante et les autres uniquement la date pour MySQL. L’affichage écran de ces fonctions de type date est aussi dépendant des options et paramètres utilisés pour la base de données (NLS_DATE_FORMAT pour Oracle et dateformat pour SQL Server).

Nous avons déjà vu qu’il est préférable de fournir dans...

Les fonctions sur les chaînes de caractères

1. Changement de casse LOWER / UPPER / UCASE / LCASE (minuscules et majuscules)

Il existe des fonctions pour mettre les chaînes de caractères en minuscules ou en majuscules. Les noms différent là encore entre chaque SGBD.

Sous Oracle et SQL Server, on utilisera LOWER et UPPER. Sous MySQL, LOWER, UPPER, UCASE et LCASE sont autorisés.

LCASE et LOWER sont utilisés pour mettre en minuscules et UCASE et UPPER sont utilisés pour mettre en majuscules.

Exemples pour Oracle

SELECT LOWER('Ceci est un TEST') AS MINUSCULE FROM DUAL; 
 
MINUSCULE 
---------------- 
ceci est un test 
 
SELECT UPPER('Ceci est un TEST') AS MAJUSCULE FROM DUAL; 
 
MAJUSCULE 
---------------- 
CECI EST UN TEST 

Exemples pour MySQL

SELECT LCASE('Ceci est un TEST') AS MINUSCULE 
 
+------------------+ 
| MINUSCULE        | 
+------------------+ 
| ceci est un test | 
+------------------+ 
 
SELECT UCASE('Ceci est un TEST') AS MAJUSCULE 
 
+------------------+ 
| MAJUSCULE        | 
+------------------+ 
| CECI EST UN TEST | 
+------------------+ 

Exemples pour SQL Server et PostgreSQL

SELECT LOWER('Ceci est un TEST') AS MINUSCULE;  
  
MINUSCULE  
----------------  
ceci est un test  
  
SELECT UPPER('Ceci est un TEST') AS MAJUSCULE;  
  
MAJUSCULE  
----------------  
CECI EST UN TEST 

La syntaxe est donc :

SELECT LOWER ou UPPER (<colonne ou variable>) ... FROM <table1>, 
<table2> ... 

avec la possibilité d’utiliser LCASE et UCASE en MySQL.

2. Supprimer les espaces à droite ou à gauche d’une chaîne de caractères : TRIM / LTRIM / RTRIM

Pour supprimer des espaces dans une chaîne de caractères, il faut utiliser soit LTRIM pour supprimer les espaces à gauche (Left) ou RTRIM pour supprimer les espaces à droite (Right). Pour supprimer les espaces à droite et à gauche, il existe TRIM.

Exemples pour Oracle et PostgreSQL

SELECT '*'||LTRIM('   SUPPRESSION DES ESPACES A GAUCHE   ')||'*' AS 
SUPG...

Les principales fonctions de conversion

1. Transformer un numérique ou une date en texte : TO_CHAR

Ce sont les fonctions TO_CHAR sous Oracle et CAST sous MySQL qui permettent de convertir un numérique ou une date en caractères.

Exemple pour Oracle

SELECT TO_CHAR(PRIX) PRIX, TO_CHAR(DATEDEBUT,'DD/MM/YY') DateDebut 
FROM Tarifs; 

PRIX

DATEDEBUT

69.99

16/04/24

59.99

16/04/24

69.99

16/04/24

79.99

16/04/24

89.99

16/04/24

Exemple pour PostgreSQL, MySQL et SQL Server

SELECT CAST(Prix as char(6)) AS Prix, CAST(DateDebut as char(10)) 
AS DateDebut FROM Tarifs; 

Autre exemple pour SQL Server

SELECT CONVERT(char(6), Prix) AS Prix, CONVERT(char(10), 
DateDebut) AS DateDebut FROM Tarifs; 

La syntaxe est donc pour Oracle :

SELECT TO_CHAR(<colonne ou variable>,[<FORMAT>],<chaine  
recherchée> ...  FROM <table1>, <table2> ... 

La syntaxe est donc pour PostgreSQL, MySQL et SQL Server :

SELECT CAST(<colonne ou variable> as <TYPE>) ...  FROM <table1>, 
<table2> ... 

Une autre syntaxe pour SQL Server :

SELECT CONVERT(<TYPE>, <colonne ou variable>) ...  FROM <table1>,  
<table2> ... 

2. Changer le type d’une colonne : CAST ou CONVERT

Ces fonctions permettent de changer le type d’une colonne le temps de l’ordre SQL, par exemple changer une colonne qui est initialement en VARCHAR en INTEGER afin de faire un calcul ou tester la valeur...

Les fonctions de fenêtrage

Les fonctions de fenêtrage permettent de ranger des lignes ou d’effectuer des calculs sur des sous-groupes après avoir partitionné des enregistrements.

1. Numérotation séquentielle et rangement de lignes

Ce sont les fonctions qui permettent de ranger des lignes. RANK permet de numéroter des lignes dans un ordre. DENSE_RANK permet de numéroter les lignes dans un ordre avec des numéros à suivre. ROW_NUMBER permet de numéroter des lignes avec des numéros à suivre, telle une suite. La numérotation peut être effectuée sur des sous-groupes d’enregistrements.

Voici des exemples de numérotation de la table tarifs par prix, sans et avec partitionnement par type de chambre.

Exemples pour SQL Server, Oracle et PostgreSQL

SELECT Hotel, typeChambre, Prix, RANK() OVER(ORDER BY Prix DESC) 
AS Numero, DENSE_RANK() OVER(ORDER BY Prix DESC) AS NumeroASuivre, 
ROW_NUMBER() OVER(ORDER BY Prix DESC) AS Suite FROM Tarifs; 

Hotel

typeChambre

Prix

Numero

NumeroASuivre

Suite

2

7

103,49

1

1

1

3

7

103,49

1

1

2

1

7

103,49

1

1

3

4

7

103,49

1

1

4

4

6

91,99

5

2

5

1

6

91,99

5

2

6

SELECT Hotel, typeChambre, Prix 
, RANK() OVER(PARTITION BY TypeChambre ORDER BY Prix DESC) AS Numero 
, DENSE_RANK() OVER(PARTITION BY TypeChambre ORDER BY Prix DESC) AS 
NumeroASuivre 
, ROW_NUMBER() OVER(PARTITION BY TypeChambre ORDER BY Prix DESC) AS Suite 
FROM Tarifs; 

Hotel

typeChambre

Prix

Numero

NumeroASuivre

Suite

1

1

58,49

1

1

1

2

1

57,49

2

2

2

3

1

57,49

2

2

3

1

1

57,49

2

2

4

4

1

57,49

2

2

5

2

1

49,99

6

3

6

3

1

49,99

6

3

7

4

1

49,99

6

3

8

1

1

49,99

6

3

9

1

2

69,99

1

1

1

2

2

68,99

2

2

2

3

2

68,99

2

2

3

4

2

68,99

2

2

4

Dans cet exemple, il y a deux types de chambres que l’on classe par prix. Le premier type a trois prix différents. On voit la différence entre le Numero et NumeroASuivre sur le troisième prix où le numéro (fonction RANK) reprend le positionnement du tarif c’est-à-dire 6, alors que NumeroAsuivre (fonction DENSE_RANK) continue la numérotation. La suite est basée sur un pas de 1. La numérotation reprend à 1 sur le second type de chambre, car le partitionnement est basé sur le champ TypeChambre.

2. Distribution de lignes en groupes numérotés

La fonction NTILE permet de distribuer et numéroter des lignes dans un nombre...

Les autres fonctions

1. NVL : tester une colonne à null

Le NVL pour « Null Value » permet de savoir si une colonne est renseignée ou pas et de lui attribuer une valeur dans le cas où elle est à null. La fonction correspondante dans SQL Server est ISNULL.

Syntaxe

SELECT NVL(<nom colonne>,<valeur attribuée>), ... 

La valeur attribuée doit être de même type que la colonne testée.

Exemple Oracle

SELECT NumChambre, Commentaire, NVL(Commentaire, 'Vue sur le jardin') 
AS Commentaires FROM Chambres; 

Exemple SQL Server

SELECT NumChambre, Commentaire, ISNULL(Commentaire, 'Vue sur le jardin') 
AS Commentaires FROM Chambres; 

Résultat

NumChambre

Commentaire

Commentaires

1

Belle vue

Belle vue

2

3

NULL

Vue sur le jardin

4

NULL

Vue sur le jardin

On constate que les chambres qui ont la colonne Commentaire à NULL sont remplacées par la valeur « Vue sur le jardin ». La seconde ligne contient un espace, qui n’est pas considéré comme NULL.

2. Tester plusieurs valeurs : COALESCE

Cette fonction permet de tester plusieurs valeurs NULL de colonnes sur une même fonction, évitant ainsi de faire des tests avec des « IF » « THEN », etc.

Elle teste chaque valeur de colonne puis attribue le résultat de gauche à droite. La première colonne non NULL est attribuée. Si toutes les colonnes sont NULL, la fonction prendra la valeur par défaut qui doit être le dernier argument.

Syntaxe

COALESCE(<colonne1>, <colonne 2>,... <valeur par défaut>); 

Exemple

Si on a dans la table Tarifs les données suivantes :

idTarif

Hotel

typeChambre

DateDebut

DateFin

Prix...

Exercices

Ces exercices sont basés sur les tables précisées dans la section La sélection de données - Exercices sur la sélection de données du chapitre précédent.

Premier exercice

Récupérer la date du jour au format DD-MM-YYYY.

Deuxième exercice

Mettre la chaîne suivante en majuscules puis rechercher la position de la chaîne ’BUT’.

’Lors du match Lille - Brest le premier but a été marqué à la 69ème minute’

Troisième exercice

Enlever les espaces à gauche et ajouter des ’-’ à la chaîne ci-dessous jusqu’à obtenir une chaîne de 50 caractères.

’             Le temps ne permet pas de réaliser les travaux’

Quatrième exercice

Sélectionner les films dont le réalisateur a comme prénom ’LUC’ et qui sont sortis entre le ’01/01/85’ et le ’30/05/1995’.

Cinquième exercice

Afficher la date et l’heure du jour sous la forme :

Nous sommes le Vendredi 25 septembre 2024 il est 16 heures et 26 minutes

Sixième exercice

Calculer le nombre de jours passés depuis la sortie de chaque film de la table FILM.

Puis afficher ces nombres exprimés en mois.

Septième exercice

Quelle est la recette mondiale, arrondie à 2 chiffres après...

Solutions des exercices

Premier exercice

Récupérer la date du jour au format DD-MM-YYYY.

SELECT TO_CHAR(CURRENT_DATE,'DD/MM/YYYY') FROM DUAL; 
(Oracle et MySQL) 
SELECT TO_CHAR(CURRENT_DATE,'DD-MM-YYYY'); (PostgreSQL et MySQL) 
SELECT CONVERT(CHAR(10),GETDATE(),105); (SQL Server) 

Deuxième exercice

Mettre la chaîne suivante en majuscules puis rechercher la position de la chaîne ’BUT’.

’Lors du match Lille - Brest le premier but a été marqué à la 69ème minute’

SELECT INSTR(UPPER('Lors du match Lille / Brest le premier but a 
été marqué à la 69ème minute'),'BUT') AS POSITION FROM DUAL; 
 
(Oracle et MySQL) 
SELECT POSITION('BUT' IN UPPER('Lors du match Lille / 
Brest le premier but a été marqué à la 69ème minute')) AS POSITION; (PostgreSQL) 
SELECT CHARINDEX('BUT',UPPER('Lors du match Lille / Brest 
le premier but a été marqué à la 69ème minute')) AS POSITION; 
(SQL Server) 
  POSITION 
---------- 
          40 

Troisième exercice

Enlever les espaces à gauche et ajouter des ’-’ à la chaîne ci-dessous jusqu’à obtenir une chaîne de 50 caractères.

’             Le temps ne permet pas de réaliser les travaux’

SELECT RPAD(LTRIM('             Le temps ne permet pas de réaliser 
les travaux'),50,'-') AS MEF FROM DUAL; 
(Oracle et MySQL) 
 
SELECT RPAD(LTRIM('            Le temps ne permet pas de réaliser 
les travaux'),50,'-') AS MEF; (MySQL et PostgreSQL) 
SELECT LTRIM('                 Le temps ne permet pas de réaliser 
les travaux') + REPLICATE('-','50') AS MEF; (SQL Server) 
MEF 
--- 
 
Le temps ne permet pas de réaliser les travaux--- 

Quatrième exercice

Sélectionner les films dont le réalisateur...