LE SQL de A à Z : 2eme partie - le simple (?) SELECT et les fonctions SQL

Date de publication : 23/01/2003

Par SQLPro (autres articles) (CV)
 

niveau : débutant

Dans le précédent article nous avons vu l’historique de SQL et ses différentes composantes. Nous entrons maintenant dans le vif du sujet, en nous intéressant au simple SELECT.
Simple ? Pas si sûr...
Dans le dernier document normatif de l’ISO, la syntaxe de la commande SELECT, est décrite en plus de 300 pages… C’est pourquoi nous nous permettons de mettre en doute la simplicité de la commande SELECT !


Préambule
1. La commande SELECT
1.1. L'opérateur * (étoile)
1.2. L'opérateur DISTINCT (ou ALL)
1.3. L'opérateur AS
1.4. Opérateur de concaténation
1.5. Opérateurs mathématiques de base
1.6. Particularité du "FROM"
1.7. Utilisation du caractère double quote (guillemet)
2. La clause ORDER BY
3. La clause WHERE
3.1. Opérateurs de comparaison
3.2. Opérateur IN
3.3. Opérateur BETWEEN
3.4. Opérateur LIKE
3.5. Résumé des opérateurs pour les prédicats de la clause WHERE
4. Fonctions diverses
4.1. Trantypage à l'aide de la fonction CAST
4.2. Mise en majuscule / Minuscule
4.3. Supprimer les blancs (ou tout autre caractères)
4.4. Extraire une sous chaîne
4.5. Opérateur de traitement des dates
4.5.1. Extraire un paramètre temporel d'une date
4.5.2. Heure et date courante
4.6. Opérateurs statistiques
4.7. Autres fonctions normalisées
4.8. Autres opérateurs mathématiques (non normalisés)
4.9. Autres opérateurs de traitement des chaînes de caractères (non normalisés)
4.10. Autres opérateurs sur les valeurs temporelles (non normalisés)
4.11. Opérateurs d'ensemble (non normalisés)
5. Traitement des "valeurs" nulles
5.1. Le null n'est ni la chaîne vide, ni le zéro
5.2. Opérateurs de traitement des marqueurs NULL
6. Négation de valeurs
7. Les branchements dans le SQL
7.1. CASE sur expression
7.2. CASE généralisé
8. Le constructeur de lignes valuées (ROW VALUE CONSTRUCTOR)
9. Résumé
10. Conclusion


Préambule

NOTA : La structure de la base de données exemple, ainsi qu'une version des principales bases utilisées sont disponibles dans la page "La base de données exemple".


1. La commande SELECT

Vous trouverez des compléments d'information sur le sujet aux pages 53 à 127 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

Le SELECT est la commande de base du SQL destinée à extraire des données d’une base ou calculer de nouvelles données à partir d’existantes...

Voici la syntaxe générale d'une commande SELECT :

SELECT [DISTINCT ou ALL] * ou liste de colonnes
FROM nom de table ou de la vue
[WHERE prédicats]
[GROUP BY ordre des groupes]
[HAVING condition]
[ORDER BY ] liste de colonnes
NOTA : dans cette syntaxe, les mots clef du SQL sont en gras, les paramètres en minuscule et entre crochets on trouve les parties optionnelles

En fait l'ordre SQL SELECT est composé de 6 clauses dont 4 sont optionnelles.
Clauses de l'ordre SELECT :

SELECT

 Spécification des colonnes du résultat
FROM

 Spécification des tables sur lesquelles porte l'ordre
WHERE

 Filtre portant sur les données (conditions à remplir pour que les lignes soient présentes dans le résultat)
GROUP BY

 Définition d'un groupe (sous ensemble)
HAVING

 Filtre portant sur les résultats (conditions de regroupement des lignes)
ORDER BY

 Tri des données du résultat
NOTA : La plupart du temps, la difficulté réside dans la compréhension de la différence entre le filtre WHERE et le filtre HAVING. Disons plus pragmatiquement que le filtre WHERE permet de filtrer les données des tables tandis que le filtre HAVING permet de filtrer les données du résultat...

REMARQUE : pour spécifier une valeur littérale il faut l'entourer de guillemets simples.

Un premier exemple basique :

Exemple 1

SELECT   CLI_NOM, CLI_PRENOM
FROM     T_CLIENT
WHERE    TIT_CODE = 'M.'
 
CLI_NOM  CLI_PRENOM
-------  ----------
DUPONT   Alain
MARTIN   Marc
BOUVIER  Alain
DUBOIS   Paul
DREYFUS  Jean
FAURE    Alain
PAUL     Marcel
DUVAL    Arsène
PHILIPPE André
CHABAUD  Daniel
BAILLY   Jean-François
...
Permet de trouver les noms et prénoms des clients dont le titre est ‘M.’ (monsieur).

NOTA : comme tous les paramètres à prendre sous forme de littéraux doivent être exprimés entourés d'apostrophes (simple côtes), il faut dédoubler un tel caractère s'il s'avère présent dans la chaîne utilisé.


1.1. L'opérateur * (étoile)

Le caractère * (étoile) récupère toutes les colonnes de la table précisée dans la clause FROM de la requête.
Juste après le mot clef SELECT, on précise les colonnes de la table qui doivent être présentées dans la réponse.
L’utilisation du caractère étoile ramène toutes les colonnes de la table dans la réponse. Dans le cas contraire il faut expressément nommer chacune des colonnes et les séparer par des virgules.

Exemple 2

SELECT   *
FROM     T_CLIENT
WHERE    TIT_CODE = 'M.'
 
CLI_ID   TIT_CODE  CLI_NOM   CLI_PRENOM  CLI_ENSEIGNE
-------  --------  --------  ----------  ------------
      1  M.        DUPONT    Alain       NULL
      2  M.        MARTIN    Marc        Transports MARTIN & fils
      3  M.        BOUVIER   Alain       NULL
      4  M.        DUBOIS    Paul        NULL
      5  M.        DREYFUS   Jean        NULL
      6  M.        FAURE     Alain       Boulangerie du marché
     11  M.        PAUL      Marcel      Cie Internationale des Mach...
     12  M.        DUVAL     Arsène      NULL
     13  M.        PHILIPPE  André       NULL
     16  M.        CHABAUD   Daniel      NULL
...
Notons tout de suite la présence à plusieurs reprises du mot clef "NULL" dans la cologne CLI_ENSEIGNE. Non il ne s'agit pas d'une enseigne particulière, mais simplement de l'absence d'information. Nous verrons que l'absence d'information, c'est le marquer "NULL" qui différe de la chaîne de caractère vierge ("") ou encore du zéro.


1.2. L'opérateur DISTINCT (ou ALL)

Lorsque le moteur construit la réponse, il rapatrie toutes les lignes correspondantes, généralement dans l’ordre ou il les trouve, même si ces dernières sont en double, c'est à dire qu'il récupère toutes les lignes (ALL par défaut). C’est pourquoi il est souvent nécessaire d’utiliser le mot clef DISTINCT qui permet d‘éliminer les doublons dans la réponse.

Exemples 3 et 4

SELECT   CLI_PRENOM
FROM     T_CLIENT
WHERE    TIT_CODE = 'M.'
 
CLI_PRENOM
----------
Alain
Marc
Alain
Paul
Jean
Alain
Marcel
Arsène
André
Daniel
...
 
SELECT distinct   CLI_PRENOM
FROM              T_CLIENT
WHERE             TIT_CODE = 'M.'
 
CLI_PRENOM
----------
Alain
Alexandre
André
Arnaud
Arsène
Bernard
Christian
Christophe
Daniel
Denis
...

1.3. L'opérateur AS

Vous pouvez rajouter autant de colonnes que vous le désirez en utilisant le mot clef AS.
En principe l’opérateur AS sert à donner un nom à de nouvelles colonnes créées par la requête.

Exemple 5

SELECT   CLI_NOM as NOM, 'homme' as SEXE
FROM     T_CLIENT
WHERE    TIT_CODE = 'M.'
 
NOM      SEXE
-------  -----
DUPONT   homme
MARTIN   homme
BOUVIER  homme
DUBOIS   homme
DREYFUS  homme
FAURE    homme
PAUL     homme
DUVAL    homme
PHILIPPE homme
CHABAUD  homme
...

1.4. Opérateur de concaténation

L'opérateur || (double barre verticale) permet de concaténer des champs de type caractères.

Exemple 6

SELECT   TIT_CODE || ' ' || CLI_PRENOM || ' ' ||  CLI_NOM as NOM
FROM     T_CLIENT
 
NOM
-----------------------
M. Alain DUPONT
M. Marc MARTIN
M. Alain BOUVIER
M. Paul DUBOIS
M. Jean DREYFUS
M. Alain FAURE
M. Paul LACOMBE
Melle. Evelyne DUHAMEL
Mme. Martine BOYER
M. Martin MARTIN
...
Néanmoins on trouve dans certains SGBDR le + comme opérateur de concaténation, comme la fonction CONCAT.


1.5. Opérateurs mathématiques de base

On, peut utiliser les opérateurs mathématiques de base pour combiner différentes colonnes
(,+,-, *, /,).

Exemple 7

SELECT   CHB_ID, TRF_CHB_PRIX * 1.206 AS TARIF_TTC
FROM     TJ_TRF_CHB
WHERE    TRF_DATE_DEBUT = '2001-01-01'
 
CHB_ID  TARIF_TTC
------  ---------
     1      424,51
     2      482,40
     3      617,47
     4      424,51
     5      463,10
     6      482,40
     7      424,51
     8      540,29
     9      482,40
    10      617,47
...

1.6. Particularité du "FROM"

Il est possible de surnommer une table dans la clause FROM, dans ce cas, la syntaxe de la partie FROM de la commande SELECT est la suivante :

FROM nom_de_table ou nom_de_la_vue surnom
Nous verrons dans quel cas ce renom est nécessaire ou obligatoire.
NOTA : certains auteurs préfèrent utiliser le mot d'alias que nous rejetons car il indique souvent un autre concept, ou de synonyme, que nous acceptons de manière timorée...


1.7. Utilisation du caractère double quote (guillemet)

Lorsqu’un nom d’un élément d’une base de données (table, colonne par exemple) est identique à un mot clef du SQL, il convient de l’entourer de guillemets (double quote). En principe, les mots réservés du SQL sont déconseillés pour nommer des objets du modèle physique de données...

Imaginons une table de nom JOIN, composée des champs suivants :

NOM      SELECT  DATE        NOT
-------  ------  -------     ---
DURAND   Oui     1999-11-12  F
DUVAL    Non     1998-01-17  M
Exemple 8 : nous désirons sélectionner les colonnes SELECT et DATE lorsque la colonne NOT vaut F...

SELECT SELECT, DATE
FROM JOIN
WHERE NOT = 'F'
 
ERREUR !
 
SELECT "SELECT", "DATE"
FROM "JOIN"
WHERE "NOT" = 'F'
 
Correct : on entoure les mots clefs du SQL par des doubles côtes
Cela est aussi nécessaire lorsque le nom (d'une colonne ou d'une table) est composé de caractères particuliers tels que les blancs ou autres, ce qui est à éviter.

REMARQUE : les noms des identifiants d'objet de base de données doivent être écrit dans le jeux de caractères restreint suivant :
[A..Z] + [a..z] + [0..9] + [ _ ].
Ils ne doivent pas commencer par un chiffre et sont insensibles à la casse (indifférence entre majuscule et minuscule).


2. La clause ORDER BY

Vous trouverez des compléments d'information sur le sujet aux pages 66 à 70 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

ORDER BY colonne1 | 1 [ASC ou DESC ] [, colonne2 | 2 [ASC ou DESC ] ...
Cette clause permet de définir le tri des colonnes de la réponse, soit en précisant le nom littéral de la colonne, soit en précisant son n° d'ordre dans l'énumération qui suit le mot clef SELECT.
ASC spécifie l’ordre ascendant et DESC l’ordre descendant du tri. ASC ou DESC peut être omis, dans ce cas c'est l'ordre ascendant qui est utilisé par défaut.

Bien que la clause ORDER BY ne soit pas nécessaire, il est souvent utile de trier la réponse en fonction des colonnes. En revanche le temps de réponse s'en ressent souvent.
Pour spécifier l'ordre de tri, on doit placer les noms des colonnes séparées par des virgules juste après le mot clef "ORDER BY", dans l'ordre voulu.. On peut aussi utiliser le rang de chaque colonne dans l'ordre spécifié dans la clause SELECT.

Attention : le tri est un tri interne, il ne faut donc placer dans cette clause que les noms des colonnes présentées dans la clause SELECT.

Souvent, le fait de placer DISTINCT suffit, en général, à établir un tri puisque le moteur doit se livrer à une comparaison des lignes mais ce mécanisme n'est pas garanti car ce tri s’effectue dans un ordre non contrôlable qui peut varier d’un serveur à l’autre.

Exemple 9

SELECT     CLI_NOM, CLI_PRENOM
FROM       T_CLIENT
ORDER BY   CLI_NOM, CLI_PRENOM

ou

SELECT     CLI_NOM, CLI_PRENOM
FROM       T_CLIENT
ORDER BY   1, 2
 
CLI_NOM   CLI_PRENOM
--------  ----------
AIACH     Alexandre
ALBERT    Christian
AUZENAT   Michel
BACQUE    Michel
BAILLY    Jean-François
BAVEREL   Frédéric
BEAUNEE   Pierre
BENATTAR  Bernard
BENATTAR  Pierre
BENZAQUI  Joël
...
REMARQUE : les marqueurs NULL sont situés en premier dans l'ordre ainsi établi.

NOTA : Un problème, qui n’est pas résolu, est de pouvoir choisir l’ordre des colonnes de la réponse. Sur certains serveurs cela peut être obtenu en plaçant les noms des colonnes à obtenir dans l’ordre où l’on veut les voir apparaître dans la clause SELECT, mais cette possibilité n'est jamais garantie...

ATTENTION : la clause ORDER BY est la dernière clause de tout ordre SQL et ne doit figurer qu'une seule fois dans le SELECT, même s'i l existe des requêtes imbriquées ou un jeu de requêtes ensemblistes.


3. La clause WHERE

Vous trouverez des compléments d'information sur le sujet aux pages 62 à 165 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

WHERE prédicats
Le prédicat doit contenir n’importe quelle expression logique renvoyant une valeur vrai.
Ainsi, une requête aussi stupide que la suivante, est supposée fonctionner :

Exemple 10

SELECT   CLI_NOM
FROM     T_CLIENT
WHERE    1=1
 
CLI_NOM
------- 
DUPONT
MARTIN
BOUVIER
DUBOIS
DREYFUS
FAURE
LACOMBE
DUHAMEL
BOYER
MARTIN
...
Attention : la plupart des SGBDR ne comportent pas de colonne de type booléen. Une requête comme la première risque d'échouer.

Exemple 11

SELECT   *
FROM     TJ_CHB_PLN_CLI
WHERE    CHB_PLN_CLI_OCCUPE
 
ERREUR !
bien que CHB_PLN_CLI_OCCUPE puisse être du booléen, la plupart 
des compilateurs SQL n'accepte pas ce test direct.
 
SELECT   *
FROM     TJ_CHB_PLN_CLI
WHERE    CHB_PLN_CLI_OCCUPE = True
 
CORRECT ...
Mais sur certains compilateur SQL il faut faire : 
CHB_PLN_CLI_OCCUPE = 'True' (littéral).
Si le type booléen n'existe pas, alors il faut faire 
CHB_PLN_CLI_OCCUPE = 1 si l'on a choisi de définir 
les booléens comme INTEGER(1) avec 0 et 1
Pour palier au manque de booléen, on utilise soit un littéral (True/False, Vrai/Faux, Oui/Non), soit un numérique avec les valeurs 0 (Faux) et 1 (Vrai). L'avantage des valeurs numériques est que le calcul logique est comparable aux divisions et additions...

opérateur ET

 FAUX VRAI
FAUX FAUX FAUX
VRAI FAUX VRAI
similitude entre le ET et la multiplication

multiplication

 0 1
0 0 0
1 0 1, <> 0
opérateur OU

 FAUX VRAI
FAUX FAUX VRAI
VRAI VRAI VRAI
similitude entre le OU et l'addition

addition

 0 1
0 0 1, <> 0
1 1, <> 0 2, <> 0

3.1. Opérateurs de comparaison

Dans la clause WHERE, vous disposez de différents opérateurs de comparaisons logiques :

WHERE valeur1 [NOT et] = ou < ou <= ou > ou >= ou <>valeur2 [OR ou AND ...]
Exemple 12

SELECT   CLI_NOM, CLI_PRENOM
FROM     T_CLIENT
WHERE    CLI_NOM >= 'A' AND CLI_NOM <'E'

ou

SELECT   CLI_NOM, CLI_PRENOM
FROM     T_CLIENT
WHERE    (CLI_NOM >= 'A')
         AND
         (CLI_NOM <'E')

plus lisible !
 
CLI_NOM  CLI_PRENOM
-------  ----------
DUPONT   Alain
BOUVIER  Alain
DUBOIS   Paul
DREYFUS  Jean
DUHAMEL  Evelyne
BOYER    Martine
DUVAL    Arsène
DAUMIER  Amélie
CHABAUD  Daniel
BAILLY   Jean-François
...
Ici on obtient tous les noms et prénoms des clients dont le nom commence par les lettres A, B, C ou D.

Attention : dans certains moteurs de requête SQL l’opérateur « différent de » (<>) s’écrit !=


3.2. Opérateur IN

L'opérateur IN permet de rechercher si une valeur se trouve dans un ensemble donné, quel que soit le type des valeurs de référence spécifiées (alpha, numérique, date…). Bien entendu, il est possible d’inverser le fonctionnement de l’opérateur IN en lui adjoignant l’opérateur NOT.

Exemple 13

SELECT   TIT_CODE, CLI_NOM, CLI_PRENOM 
FROM     T_CLIENT
WHERE    TIT_CODE IN ('Mme.', 'Melle.')
 
TIT_CODE  CLI_NOM     CLI_PRENOM
--------  ----------  ----------
Mme.      BOYER       Martine
Mme.      GALLACIER   Noëlle
Mme.      HESS       Lucette
Mme.      LETERRIER   Monique
Mme.      MARTINET    Carmen
Mme.      DAVID       Jacqueline
Mme.      MOURGUES    Jacqueline
Mme.      ZAMPIERO    Annick
Mme.      ROURE       Marie-Louise
Mme.      DE CONINCK  Patricia
...
On recherche les clients de sexe féminin, basés sur le code titre.

Le contenu de la parenthèse peut être remplacé par le resultat d'une requête possédant une colonne unique. Dans ce cas on parle de requêtes imbriquées, ce que nous verrons plus loin.


3.3. Opérateur BETWEEN

L'opérateur BETWEEN permet de rechercher si une valeur se trouve dans un intervalle donné, quel que soit le type des valeurs de référence spécifiées (alpha, numérique, date...).

Ainsi, la requête vue dans l'exemple 12 peut s'écrire :

Exemple 14

SELECT   CLI_NOM, CLI_PRENOM
FROM     T_CLIENT
WHERE    CLI_NOM BETWEEN 'A' AND 'E'
 
CLI_NOM  CLI_PRENOM
-------  ----------
DUPONT   Alain
BOUVIER  Alain
DUBOIS   Paul
DREYFUS  Jean
DUHAMEL  Evelyne
BOYER    Martine
DUVAL    Arsène
DAUMIER  Amélie
CHABAUD  Daniel
BAILLY   Jean-François
...
NOTA : les opérateurs IN et BETWEEN sont très pratiques dans le cas où l’on désire effectuer des requêtes où l’utilisateur peut saisir une liste de choix multiples (IN) ou une plage de valeur (BETWEEN).


3.4. Opérateur LIKE

L'opérateur LIKE permet d’effectuer une comparaison partielle. Il est surtout employé avec les colonnes contenant des données de type alpha. Il utilise les jokers % et _ (‘pour cent’ et ‘blanc souligné’). Le joker % remplace n'importe quelle chaîne de caractères, y compris la chaîne vide. Le blanc souligné remplace un et un seul caractère.

Exemple 15

SELECT   CLI_NOM, CLI_PRENOM
FROM     T_CLIENT
WHERE    CLI_NOM LIKE 'B%'
 
CLI_NOM  CLI_PRENOM
-------  ----------
BOUVIER  Alain
BOYER    Martine
BAILLY   Jean-François
BOUCHET  Michel
BEAUNEE  Pierre
BERGER   Jean-Pierre
BOURA    André
BENZAQUI Joël
BAVEREL  Frédéric
BERTRAND Christophe
...
On recherche les clients dont le nom commence par B.

Mais si vos données sont susceptibles de contenir un des deux caractères joker, alors il faut recourir à une séquence d’échappement, à l’aide du mot clef ESCAPE...

Cherchons les clients dont l'enseigne contient au moins un caractère blanc souligné :

Exemple 16

SELECT   *
FROM     T_CLIENT
WHERE    CLI_ENSEIGNE
         LIKE  '%_%'
 
CLI_ID TIT_CODE CLI_NOM    CLI_PRENOM    CLI_ENSEIGNE
------ -------- ---------- ------------- ------------------------------
     2 M.       MARTIN     Marc          Transports MARTIN & fils
     6 M.       FAURE      Alain         Boulangerie du marché
    10 M.       MARTIN     Martin        HERMAREX IMPORT_EXPORT
    11 M.       PAUL       Marcel        Cie Internationale des Mach...
    17 M.       BAILLY     Jean-François Entreprise DUPONT CHAUFFAGE
    24 M.       CHTCHEPINE Dominique     HOTEL *** DE LA GARE
    26 M.       GARREAU    Paul          IBM Corp.
    34 Mme.     GALLACIER  Noëlle        Transports GALLACIER
    42 Mme.     LETERRIER  Monique       SA AROMAX ENTREVONT
    49 M.       COULOMB    Renaud        Cabinet COULOMN et CALEMANT
 
SELECT   *
FROM     T_CLIENT
WHERE    CLI_ENSEIGNE
         LIKE  '%#_%' ESCAPE '#'
 
CLI_ID TIT_CODE CLI_NOM    CLI_PRENOM    CLI_ENSEIGNE
------ -------- ---------- ------------- ------------------------------
    10 M.       MARTIN     Martin        HERMAREX IMPORT_EXPORT
Pour traiter ce cas, on défini « # » comme caractère d’échappement. Le caractère qui suit ce caractère d’échappement est donc interprété comme un caractère et non comme un joker.

NOTA : l’opérateur LIKE effectue une recherche en tenant compte de la différence entre lettres majuscules et minuscules. Si vous voulez effectuer une recherche en ne tenant aucunement compte de la différence entre majuscules et minuscules, il convient d’utiliser les opérateurs LOWER et UPPER (voir ci dessous). Mais la plupart du temps, l'utilisation du like dans un SGBDR donné ignore la casse.


3.5. Résumé des opérateurs pour les prédicats de la clause WHERE

Voici une tableau résumant les principaux opérateurs utilisés pour la construction des prédicats :

opérateurs de comparaisons = <> < <= > >=
connecteurs logiques {OR | AND}
opérateur de négation NOT
parenthèses ( ... )
opérateurs mathématiques + - * /
comparaison logique IS [NOT] {TRUE | FALSE | UNKNOWN}
comparaison avec valeur IS [NOT] NULL
intervalle valeur BETWEEN borne_basse AND borne_haute
comparaison partielle de chaîne de caractères valeur LIKE motif [ESCAPE echappement]
comparaison à une liste de valeur valeur [NOT] IN (liste)

4. Fonctions diverses

Vous trouverez des compléments d'information sur le sujet aux pages 71 à 107 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.


4.1. Trantypage à l'aide de la fonction CAST

Il permet de changer le type de données d'une colonne afin d’effectuer une comparaison de données de type hétérogène par exemple entre un champ contenant des données numériques et un champ contenant des données de type chaîne de caractères...
Sa syntaxe est CAST(colonne AS nouveau type).

Exemple 17

SELECT CHB_ID, CHB_NUMERO, CHB_POSTE_TEL
FROM T_CHAMBRE
WHERE CAST(CHB_POSTE_TEL AS INTEGER) / 10 > CHB_NUMERO
 
CHB_ID  CHB_NUMERO  CHB_POSTE_TEL
------  ----------  -------------
     1           1  101
     2           2  102
     3           3  103
     4           4  104
     5           5  105
     6           6  106
     7           7  107
     8           8  108
     9           9  109
    10          10  110
L’opérateur CAST permet de transtyper les valeurs contenues dans une colonne.
Bien entendu il faut qu'un type de donnée puisse être converti dans un autre type (compatibilité de types) afin que la réponse ne soit pas entaché d'erreurs ou d'omissions.

Exemple 18

SELECT   ADR_VILLE, CAST(ADR_CP AS INTEGER) + 1
FROM     T_ADRESSE
 
ADR_VILLE             ADR_CP  ADR_CP + 1
------------          ------  ----------
VERSAILLES            78000        78001
MONTMAIZIN            11254        11255
PARIS                 75015        75016
VERGNOLLES CEDEX 452  84524        84525
MARSEILLE             13002        13003
PARIS                 75012        75013
BONNEUIL CEDEX        94152        94153
PARIS                 75012        75013
PARIS                 75014        75015
PARIS                 75017        75018
...

4.2. Mise en majuscule / Minuscule

Les opérateurs LOWER et UPPER permettent de mettre en majuscule ou en minuscule des chaînes de caractères dans les requêtes.

Exemple 19

SELECT upper(CLI_PRENOM), lower(CLI_NOM)
FROM T_CLIENT
 
CLI_NOM  CLI_PRENOM
-------  ----------
ALAIN    dupont
MARC     martin
ALAIN    bouvier
PAUL     dubois
JEAN     dreyfus
ALAIN    faure
PAUL     lacombe
EVELYNE  duhamel
MARTINE  boyer
MARTIN   martin
...
NOTA : pour effectuer une recherche en ne tenant aucunement compte de la différence entre majuscules et minuscules, il faut utiliser l’opérateur UPPER (ou lower mais attention à la transformation des accents !) :

Exemple 20

SELECT *
FROM T_CLIENT
where upper(CLI_PRENOM) = upper(CLI_NOM)
 
CLI_ID  TIT_CODE  CLI_NOM  CLI_PRENOM  CLI_ENSEIGNE
------  --------  -------  ----------  ----------------------
    10  M.        MARTIN   Martin      HERMAREX IMPORT_EXPORT
NOTA : certains SGBDR permettent de paramétrer l'activation de la recherche systématique des chaînes de caractères sans tenir compte de la casse. Sur d'autres, le paramétrage permet de confondre les lettres accentuées ou non...


4.3. Supprimer les blancs (ou tout autre caractères)

La fonction TRIM permet de supprimer en tête ou en queue (ou les deux) le blanc ou tout autre caractère spécifié. 

TRIM ([LEADING ou TRAILING ou BOTH] [caractère] FROM nom de colonne)
LEADING : suppression en tête
TRAILING : suppression en queue
BOTH : suppression en tête et en queue

Dans notre table téléphone, nous voulons supprimer le zéro de tête des n° afin de pouvoir les communiquer aux étrangers qui n’ont pas besoin de composer ce chiffre (ils doivent simplement composer le 00 33 suivi du numéro à 9 chiffres).

Exemple 21

SELECT   TEL_NUMERO,
         '00~33 ' || TRIM(LEADING '0' FROM TEL_NUMERO)
         AS TEL_INTERNATIONAL 
FROM     T_TELEPHONE
 
TEL_NUMERO      TEL_INTERNATIONAL
--------------  -----------------
01-45-42-56-63  00~33 1-45-42-56-63
01-44-28-52-52  00~33 1-44-28-52-52
01-44-28-52-50  00~33 1-44-28-52-50
06-11-86-78-89  00~33 6-11-86-78-89
02-41-58-89-52  00~33 2-41-58-89-52
01-51-58-52-50  00~33 1-51-58-52-50
01-54-11-43-21  00~33 1-54-11-43-21
06-55-41-42-95  00~33 6-55-41-42-95
01-48-98-92-21  00~33 1-48-98-92-21
01-44-22-56-21  00~33 1-44-22-56-21
...
NOTA : certains serveurs SQL proposent différentes fonctions comme LTRIM et RTRIM pour une suppression des blancs en tête ou en queue.


4.4. Extraire une sous chaîne

La fonction SUBSTRING permet d’extraire une sous chaîne d’une chaîne de caractère. Elle a besoin de l’ordre du premier caractère et du nombre de caractères sur lequel elle doit opérer.

SUBSTRING ( nom de colonne FROM n TO m)
Extrait la sous chaîne de nom de colonne en commençant à n sur m caractères.

Exemple 22

SELECT  CLI_NOM, CLI_PRENOM,
        SUBSTRING(CLI_PRENOM FROM 1 FOR 1) ||
        SUBSTRING(CLI_NOM FROM 1 FOR 1)
        AS INITIALES
FROM    T_CLIENT
 
CLI_NOM  CLI_PRENOM  INITIALES
-------  ----------  ---------
DUPONT   Alain       AD
MARTIN   Marc        MM
BOUVIER  Alain       AB
DUBOIS   Paul        PD
DREYFUS  Jean        JD
FAURE    Alain       AF
LACOMBE  Paul        PL
DUHAMEL  Evelyne     ED
BOYER    Martine     MB
MARTIN   Martin      MM
...
Cet exemple construit les initiales des clients à partir des colonnes CLI_NOM et CLI_PRENOM_CLI.

Attention, certains SGBDR utilisent la fonction SUBSTR.


4.5. Opérateur de traitement des dates


4.5.1. Extraire un paramètre temporel d'une date

L’opérateur EXTRACT permet d’extraire depuis une date, le jour le mois ou l’année…

EXTRACT ( YEAR ou MONTH ou DAY FROM nom de colonne )
Dans la table des réservation on recherche l'identifiant des chambres ayant été réservées au cours du mois de mai de n'importe quelle année et pour 3 personnes.

Exemple 23

SELECT distinct CHB_ID
FROM  TJ_CHB_PLN_CLI
WHERE EXTRACT(MONTH FROM PLN_JOUR) = 5
  AND CHB_PLN_CLI_RESERVE = 1
  AND CHB_PLN_CLI_NB_PERS = 3
 
CHB_ID
------
     1
     5
     6
     8
    11
    12
    16
    17
    18
    20
NOTA : il est dommage de constater que la fonction EXTRACT du standard SQL, souvent fort utile, est rarement présente dans les moteurs de bases de données. Ni Access, ni Oracle, ni Sybase, ni SQL Server en sont dotés. Seul le middleware BDE de Borland Inprise Corel permet d'exploiter pleinement cette fonction avec les SGBDR Paradox, dBase, FoxPro, InterBase, MSSQL, Sybase, Informix, DB2, Oracle.
Cependant il est courant de trouver des fonctions s'en approchant : Exemple DATEPART dans SQL Server.


4.5.2. Heure et date courante

L’heure courante, la date courante et le combiné date/heure courant peuvent être obtenu à l’aide des fonctions CURRENT_DATE, CURRENT_TIME et CURRENT_TIMESTAMP.

Exemple 24

SELECT distinct CHB_ID
FROM TJ_CHB_PLN_CLI
WHERE (CHB_PLN_CLI_RESERVE = 1)
  AND PLN_JOUR BETWEEN CURRENT_DATE and CURRENT_DATE + 14
  AND CHB_PLN_CLI_NB_PERS = 3

attention, le résultat de cette requête varie en fonction de 
la date à laquelle vous l'exécutez !
 
CHB_ID
------
     1
     5
     6
     8
    11
    12
    16
    17
    18
    20
Cette requête renvoie les chambres réservées pour 3 personnes entre la date du jour et pour les deux semaines à venir.

Attention : la plupart des SGBDR n'acceptent pas encore cette version normalisée des fonctions de recherche de temps courant. Voici les fonctions spécifiques aux différents serveurs SQL :

Oracle SYSDATE()
Sybase GETDATE()
SQL Server GETDATE()
Access NOW()
MySQL NOW()
Paradox (QBE) TODAY

4.6. Opérateurs statistiques

Il est possible de réaliser des comptages statistiques sur les colonnes, à l'aide des opérateurs AVG (moyenne), MAX (maximum), MIN (minimum), SUM (total), COUNT (nombre). On les appelent aussi fonctions d'aggrégations.

Exemple 25

SELECT AVG(TRF_CHB_PRIX) as MOYENNE,
   MAX(TRF_CHB_PRIX) as MAXI,
   MIN(TRF_CHB_PRIX) as MINI,
   SUM(TRF_CHB_PRIX) as TOTAL,
   COUNT(TRF_CHB_PRIX) as NOMBRE
 FROM TJ_TRF_CHB
 WHERE TRF_DATE_DEBUT = '2001-01-01'
 
MOYENNE   MAXI      MINI      TOTAL       NOMBRE
--------  --------  --------  ----------  ------
406,74 F  512,00 F  352,00 F  7 728,00 F      19
Cette requête calcule la moyenne, le montant maximum, minimum, la totalisation et le nombre des tarifs de chambre pour la date de debut du premier janvier 2001.

On peut s'étonner que les opérateurs statistiques VARIANCE ou STDDEV (écart type) soient rarement présents dans les SGBDR car il s'agit de fonctions statistiques qui possèdent une grande utilité. Mais la norme SQL 92 ne les a pas retenu. Cependant, ils existent notamment dans Oracle. Cela est d'autant plus étonnant que les techniques modernes du DataWareHouse et en particulier le DataMining, utilisent ces fonctions dans le cadres de calculs statistiques d'hypercubes (techniques OLAP et ROLAP).

ATTENTION : nous verrons que l'utilisation des fonctions statistiques nécessite la plupart du temps la mise en place d'une clause de groupage, afin de déterminé quel est le sous ensemble cible d'agrégation pour les calculs.


4.7. Autres fonctions normalisées

BIT_LENGTH Taille d'une colonne de type BIT ou BIT VARYING (nombre de bits)
CHAR_LENGTH Taille d'une colonne de type caractère (nombre de caractères)
OCTET_LENGTH Taille d'une colonne de type caractère (nombre d'octets)
CURRENT_DATE Date en cours
CURRENT_TIME Heure en cours
CURRENT_TIMESTAMP Date et heure en cours
CONVERT Conversion paramétrèe d'une chaîne de caractères
POSITION Position d'une chaîne de caractères dans une sous chaîne
TRANSLATE Traduction d'une chaîne de caractères dans un format spécifié

4.8. Autres opérateurs mathématiques (non normalisés)

Les opérateurs ci dessous peuvent être implémentés dans différents moteurs.

ABS valeur absolue
MOD modulo
SIGN signe
SQRT racine carrée
CEIL plus petit entier
FLOOR plus grand entier
ROUND arrondi
TRUNC tronqué
EXP exponentielle
LN logarithme népérien
LOG logarithme décimal
POWER puissance
COS cosinus
COSH cosinus hyperbolique
SIN sinus
SINH sinus hyperbolique
TAN tangente
TANH tangente hyperbolique
PI constante Pi
Certains sont rarement implémentés du fait que les SGBDR sont axés sur l’informatique de gestion, la collecte et le traitement d’informations et non le calcul mathématique.

Attention : le nom de certains de ces opérateurs peut différer d’un SGBDR à l’autre.


4.9. Autres opérateurs de traitement des chaînes de caractères (non normalisés)

CONCAT concaténation : équivalent du || - Nota : utiliser de préférence || plus standard. Le + entre colonne alphanumérique peut aussi souvent être utilisé comme opérateur de concaténation, préférez de toutes façons ||
INITCAP initiales en lettres capitales
LPAD complément ou troncature à n position à gauche
LTRIM / RTRIM suppression en tête/queue d’une chaîne
REPLACE remplacement
RPAD complément ou troncature à n position à droite
SOUNDEX

 code de consonnance – Attention : phonétique souvent anglaise
INSTR Position d’une chaîne dans une sous chaîne
LENGTH longueur de la chaîne
TO_CHAR numérique sous forme littérale – Attention : souvent en anglais
ASCII code ASCII d'un caractère
CHR caractère dont le code ASCII est donné
REVERSE Inverse l'ordre des caractères d'une châine
FLIP Pivote les parties droite et gauche d'une chaîne par rapport au n° du caractère servant de pivot.
Attention : le nom de certains de ces opérateurs peut différer d’un SGBDR à l’autre.


4.10. Autres opérateurs sur les valeurs temporelles (non normalisés)

ADD_MONTH, ADD_DAY, ADD_YEAR ... ajoute des mois, des jours des années à une date
LAST_DAY renvoie le n° du dernier jour d’un mois d’une date
MONTH_BETWEEN nombre de mois entre deux dates
NEXT_DAY date du prochain jour d’un nom donné
TO_CHAR date sous forme littérale – Attention : souvent en anglais
DATE_DIFF différence entre deux dates

4.11. Opérateurs d'ensemble (non normalisés)

GREATEST la plus grande des valeurs d’un ensemble
LEAST la plus petite des valeurs d’un ensemble
DECODE renvoi la valeur situé en n-ième position

5. Traitement des "valeurs" nulles

Vous trouverez des compléments d'information sur le sujet aux pages 115 à 120 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

NOTA : le NULL n'est pas à proprement parler une valeur, mais bien l'absence de valeur, c'est pourquoi nous parlerons de marqueur NULL et non de valeur NULL.
Le marqueur NULL pose une quantité de problèmes et nous allons dans ce paragraphe soulever un coin du voile, que nous traiterons un peu plus tard dans le cas général de la recherche des occurrences d’inexistence.


5.1. Le null n'est ni la chaîne vide, ni le zéro

NULL n'est pas une valeur. C'est un marqueur. Par conséquent le marqueur NULL ne peut jamais être comparé a une valeur.

Recherchons les clients qui n'ont pas d'enseigne.

Exemple 26

SELECT CLI_ID, CLI_NOM
FROM T_CLIENT
WHERE CLI_ENSEIGNE = ''
 
CLI_ID  CLI_NOM
-------  --------
...
La réponse doit produire une table vide !
Pour controurner ce problème il faut :
soit penser à enregistrer une chaîne de caractère vide lors de l'insertion des données dans la table
soit la clause WHERE avec un opérateur spécialisé dans le traitement des valeurs nulles

Le problème des « null » a été bien décrit dans le livre de Joe Celko intitulé « SQL avancé » publié par Thomson International Publishing. Joe Celko pose la question en ces termes : l’absence d’information est-elle due à son ignorance ou à son impertinence ? Pourquoi donc ne pas faire de différence entre la couleur du toit d’une voiture qui n’est pas connue, et la couleur du toit d’une moto qui n’est pas applicable… Certains logiciens de l’algèbre relationnel sont même allés plus loin en proposant différentes valeurs pour gérer les différents cas, en distinguant des cas très différents : le « null », le « inconnu » et le « inapplicable »...

Il y a donc un véritable dilemme à utiliser des requêtes en se basant sur des critères d’absence de valeur et il faut toujours faire très attention aux clauses qui utilisent des références aux valeurs nulles, suivant ce que l’on veut obtenir. D’autant plus que les NULL se propagent dans les calculs.

Voici un extrait de la table T_LIGNE_FACTURE

LIF_ID    FAC_ID    LIF_QTE    LIF_REMISE_POURCENT  LIF_REMISE_MONTANT  LIF_MONTANT  LIF_TAUX_TVA
--------  --------  ---------  -------------------  ------------------  -----------  ------------
       1         1       1,00                15,00                NULL     320,00 F         18,60
       2         3       1,00                 NULL             50,00 F     250,00 F         18,60
       3         3       1,00                 NULL             50,00 F     320,00 F         18,60
       4         3       1,00                 NULL             50,00 F     240,00 F         18,60
       5         5       1,00                 NULL                NULL     320,00 F         18,60
       6         5       1,00                 NULL                NULL     220,00 F         18,60
       7         7       1,00                 NULL                NULL     220,00 F         18,60
       8         7       1,00                 NULL                NULL     250,00 F         18,60
       9         7       1,00                 NULL                NULL     320,00 F         18,60
      10         7       1,00                 NULL                NULL     270,00 F         18,60
...
Nous voulons calculer le montant total de chacune des lignes de cette table, pour une facture donnée.

La requête pour FAC_ID = 3 est la suivante :

Exemple 27

SELECT FAC_ID, 
       sum (LIF_QTE * (LIF_MONTANT - LIF_REMISE_MONTANT) * (1 - LIF_REMISE_POURCENT / 100)) AS TOTAL_FAC,
       sum((LIF_QTE * (LIF_MONTANT - LIF_REMISE_MONTANT) * (1 - LIF_REMISE_POURCENT / 100)) 
           * (LIF_TAUX_TVA / (100 +   LIF_TAUX_TVA))) AS TOTAL_TAXES
FROM T_LIGNE_FACTURE
WHERE FAC_ID = 3
GROUP BY FAC_ID
 
FAC_ID  TOTAL_FAC  TOTAL_TAXES
------  ---------  -----------
     3       NULL         NULL
On constate que pour les lignes qui n'ont pas de valeurs renseignées dans les colonnes LIF_REMISE_POURCENT, LIF_REMISE_MONTANT, le résultat du calcul donne la valeur « null » qui se traduit à l'affichage par... rien !

NOTA : en général, pour se sortir de ce mauvais pas, on peut, lors de la création de la base de données, obliger tous les champs de type numérique (réels ou entiers) a ne pas accepter la valeur nulle et prendre par défaut la valeur zéro...

Attention : l’arithmétique des nuls est assez particulière... Souvenez vous toujours que les NULL se propagent. Cela est vrai pour les numériques, les dates mais aussi pour les chaînes de caractères. Ainsi SQL opère une distinction entre une chaîne de caractère vide et un champ non renseigné. Dans le cas de la concaténation d’une colonne NULL et d’une colonne proprement renseigné, la valeur renvoyée sera NULL !!!


5.2. Opérateurs de traitement des marqueurs NULL

La norme SQL 2 (1992) spécifie une comparaison et différents opérateurs sur les marqueurs NULL :
IS NULL / IS NOT NULL : teste si la colonne est vide ou non vide.
COALESCE() qui recherche la première valeur non vide dans un ensemble
NULLIF NULLifie une colonne en fonction d'une valeur donnée 

COALESCE ( valeur1, valeur2 [, valeur3] ... )
NULLIF ( nom_de_colonne, valeur)
expression IS [NOT] NULL
NOTA : ISNULL (en un seul mot) est une autre fonction de branchement que l'on rencontre parfois (renvoi une valeur si la valeur est nulle). Dans la même veine, NVL ou VALUE sont des expressions équivalentes à COALESCE que l'on rencontre sur certains SGBDR.

La requête précédente s'exprime, à l'aide de l'opérateur ISNULL :

Exemple 28

SELECT FAC_ID, 
        sum (LIF_QTE * (LIF_MONTANT - ISNULL(LIF_REMISE_MONTANT, 0))
            * (1 - ISNULL(LIF_REMISE_POURCENT, 0) / 100)) AS TOTAL_FAC,
        sum((LIF_QTE * (LIF_MONTANT - ISNULL(LIF_REMISE_MONTANT, 0))
            * (1 - ISNULL(LIF_REMISE_POURCENT, 0) / 100)) 
            * (LIF_TAUX_TVA / (100 +   LIF_TAUX_TVA))) AS TOTAL_TAXES
FROM T_LIGNE_FACTURE
WHERE FAC_ID = 3
GROUP BY FAC_ID
 
FAC_ID  TOTAL_FAC  TOTAL_TAXES
------  ---------  -----------
     3      810.0       127.03
NOTA : En règle générale, dès que l'on traite des colonnes contenant des valeurs monétaires ou numériques, il est bon de faire en sorte que la colonne soit obligatoire et que par défaut elle soit renseignée à zéro.
Sinon, il faudra faire un usage systématique des fonctions NULLIF ou COALESCE dans tous les calculs et cela grèvera les performances d'exécution !


6. Négation de valeurs

C'est l'opérateur NOT qui réalise la négation de valeurs et inverse la valeur logique d'un prédicat.
L'opérateur NOT peut être combiné avec la plupart des opérateurs de comparaison. Mais il devient très intéressant lorqu'il est combiné aux opérateurs IN, BETWEEN, LIKE et NULL

Recherchons par exemple toutes les chambres permettant de recevoir au moins 3 personnes, ne comportant pas le chiffre 4 (chiffre de la mort au japon) ni les chambres portant les n° 7 et 13 pour un client particulièrement superstitieux...

Exemple 29

SELECT CHB_ID, CHB_NUMERO, CHB_COUCHAGE
FROM   T_CHAMBRE
WHERE  NOT (CAST(CHB_NUMERO AS VARCHAR(10)) LIKE '%4%')
       AND CHB_NUMERO NOT IN ('7', '13')
       AND CHB_COUCHAGE >= 3
 
CHB_ID      CHB_NUMERO CHB_COUCHAGE 
----------- ---------- ------------ 
1           1          3
5           5          3
6           6          5
8           8          3
11          11         3
12          12         3
15          16         3
16          17         5
17          18         3
19          20         3
Nous verrons que le NOT IN est particulièrement précieux dans les requêtes imbriquées, c'est à dire les requêes multitables.

Nous voulons maintenant le nom des clients qui ne commence pas par 'DU' :

Exemple 30

SELECT CLI_NOM 
FROM T_CLIENT
WHERE CLI_NOM NOT LIKE 'DU%'
 
-------------------------------- 
MARTIN 
BOUVIER 
DREYFUS 
FAURE 
LACOMBE 
BOYER 
MARTIN 
PAUL 
PHILIPPE 
PIERRELAYE 
...

7. Les branchements dans le SQL

Vous trouverez des compléments d'information sur le sujet aux pages 120 à 123 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

SQL possède un branchement à la manière des IF et autres structures de test des langages procéduraux.
Mais il convient de ne l'utiliser qu'a bon escient, c'est à dire aussi peu souvent que possible, beaucoup de cas ouvant être traités soit par le COALESCE soit par des requêtes avec des opérations ensemblistes de type UNION. En effet les performances se dégradent très vite lors de l'usage du CASE à cause de l'impossibilité d'effcetuer des traitements par "paquets".

La structure CASE du SQL comprend deux syntaxes différentes. Le CASE pour branchement sur les valeurs d'une expression et le CASE généralisé.


7.1. CASE sur expression

Dans ce cas, la syntaxe est la suivante :

CASE expression
   WHEN valeur1 THEN expression1
  [WHEN valeur2 THEN expression2]
   ...
  [ELSE expression_défaut]
END
Exemple 31

SELECT CHB_NUMERO, CASE CHB_ETAGE
                      WHEN 'RDC' THEN 0 
                      WHEN '1er' THEN 1
                      WHEN '2e'  THEN 2
                   END AS ETAGE, CHB_COUCHAGE
FROM   T_CHAMBRE
ORDER  BY  ETAGE, CHB_COUCHAGE
 
CHB_NUMERO ETAGE       CHB_COUCHAGE 
---------- ----------- ------------ 
2          0           2
3          0           2
4          0           2
1          0           3
7          1           2
9          1           2
10         1           2
5          1           3
8          1           3
11         1           3
12         1           3
6          1           5
14         2           2
15         2           2
19         2           2
21         2           2
16         2           3
18         2           3
20         2           3
17         2           5

7.2. CASE généralisé

L'expression disparait au profit de différents prédicats. 

CASE WHEN condition1 THEN expression1
    [WHEN condition2 THEN expression2]
    ...
    [ELSE expression_défaut]
END
Exemple 32

SELECT CHB_NUMERO, CASE WHEN CHB_ETAGE = 'RDC' THEN 0 
                        WHEN CHB_ETAGE = '1er' THEN 1
                        WHEN CHB_ETAGE = '2e'  THEN 2
                   END AS ETAGE, CHB_COUCHAGE
FROM   T_CHAMBRE
ORDER  BY  ETAGE, CHB_COUCHAGE
 
CHB_NUMERO ETAGE       CHB_COUCHAGE 
---------- ----------- ------------ 
2          0           2
3          0           2
4          0           2
1          0           3
7          1           2
9          1           2
10         1           2
5          1           3
8          1           3
11         1           3
12         1           3
6          1           5
14         2           2
15         2           2
19         2           2
21         2           2
16         2           3
18         2           3
20         2           3
17         2           5
Qui donne le même résultat !

ATTENTION : tous les SGBDR ne supportent pas ces deux syntaxes.

NOTA : dans les deux cas il est possible de remplacer le IF d'un langage procédural :

CASE WHEN condition1 THEN expression1
    [ELSE expression_défaut]
END
 
CASE expression
   WHEN valeur1 THEN expression1
  [ELSE expression_défaut]
END

8. Le constructeur de lignes valuées (ROW VALUE CONSTRUCTOR)

Nous voici devant un élément peu connu du SQL car rarement implanté. Rendons hommage à Oracle car il est l'un des rares SGBDR à l'implémenter. Cette construction peut vous tirer d'embarras pour bien des cas en simplifiant drastiquement vos requêtes.

L'idée consiste à dire que différentes colonnes devant correspondre à différents critères comparées à l'aide d'un même opérateur de comparaison peuvent être évaluées simultanément.

Il est plus facile de comprendre ce concept à l'aide d'un exemple.

Un utilisateur créé une table T_EVENEMENT dans laquelle il a quatre colonnes indiquant l'heure, la minute, la seconde de survenance de l'événement et la nature de l'événement :

CREATE table T_EVENEMENT
(EVT_HEURE      int            NOT NULL,
 EVT_MINUTE     smallint       NULL, 
 EVT_SECONDE    smallint       NULL,
 EVT_EVENEMENT  varchar(64)    NOT NULL,
 CONSTRAINT CKC_EVT_HEURE CHECK  (EVT_HEURE >= 0),
 CONSTRAINT CKC_EVT_MINUTE CHECK  (EVT_MINUTE BETWEEN 0 AND 59),
 CONSTRAINT CKC_EVT_SECONDE CHECK (EVT_SECONDE BETWEEN 0 AND 59))
 
INSERT INTO T_EVENEMENT VALUES (3, 12, 25, 'Surtension')
INSERT INTO T_EVENEMENT VALUES (3, 2,  48, 'Surchauffe')
INSERT INTO T_EVENEMENT VALUES (3, 15, 0,  'Arrêt')
INSERT INTO T_EVENEMENT VALUES (4, 0,  0,  'Incendie')
INSERT INTO T_EVENEMENT VALUES (2, 58, 59, 'Démarrage')
INSERT INTO T_EVENEMENT VALUES (4, 22, 33, 'Explosion')
 
EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT 
----------- ---------- ----------- --------------
2           58         59          Démarrage
3           2          48          Surchauffe
3           12         25          Surtension
3           15         0           Arrêt
4           0          0           Incendie
4           22         33          Explosion
La question est : quel sont les événements survenus après 3h 10 ?

Exemple 33 : intuitivement, nous sommes amenés à écrire la requête suivante :

SELECT *
FROM T_EVENEMENT
WHERE EVT_HEURE >3 AND EVT_MINUTE >10
 
EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT
----------- ---------- ----------- --------------
4           22         33          Explosion
Mais elle oublie sciement trois événements survenus avant 3h10 ! Comment est-ce possible ?

En fait l'événement qui s'est produit à 4h 0 n'est pas pris en compte du fait de la première ligne de la clause WHERE !
EVT_HEURE > 3 AND EVT_MINUTE > 10
tout simplement parce que 0 minute est bien inférieur à 10 minutes ...
Or 4h 0 est bien supérieur à 3h 10... il faut donc supprimer la partie AND EVT_MINUTE > 10 de la clause WHERE
La bonne construction est donc :

Exemple 34

SELECT *
FROM T_EVENEMENT
WHERE EVT_HEURE > 3
   OR EVT_HEURE = 3 AND EVT_MINUTE > 10
 
EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT
----------- ---------- ----------- -------------
3           12         25          Surtension
3           15         0           Arrêt
4           0          0           Incendie
4           22         33          Explosion
D'où l'idée d'implémenter une construction capable d'évaluer globalement une telle opération. Cette construction est le ROW VALUE CONSTRUCTOR que j'ai traduit par constructeur de lignes valuées...

Avec un tel outil, la requête précédente s'exprime plus simplement :

Exemple 35

SELECT *
FROM T_EVENEMENT
WHERE (EVT_HEURE, EVT_MINUTE)  > (3, 10)
 
EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT
----------- ---------- ----------- -------------
3           12         25          Surtension
3           15         0           Arrêt
4           0          0           Incendie
4           22         33          Explosion
La syntaxe d'une telle expression utilisant un ROW VALUE CONSTRUCTOR est :

( <constructeur de lignes valuées> ) <comparateur> ( <constructeur de lignes valuées> )
Le constructeur de lignes valuées pouvant être : une liste de colonnes, une liste de valeurs, une liste combinant colonnes et valeurs ou encore une sous requête :

  • (colonne1, colonne2, colonne3)
  • (colonne1, valeur1, valeur2, colonne2)
  • (valeur1, valeur2)
  • SELECT * FROM Matable à condition de ne retourner qu'une seule ligne
etc...

L'équivalent entre cette construction et celle n'utilisant pas le ROW VALUE CONSTRUCTOR, peut être développée comme suit.
Supposons que le premier constructeur de lignes valuées comporte des colonnes de tables de 1 à n et que le second constructeur de ligne valuées comporte des valeurs de 1 à n, c'est à dire :

(colonne1, colonne2, ... colonneN) <comparateur> (valeur1, valeur2, ... valeurN)
alors cette comparaison est équivalente à :

colonne1 <comparateur> valeur1
OR colonne1 = valeur1 AND colonne2 <comparateur> valeur2
OR colonne1 = valeur1 AND colonne2 = valeur2 AND colonne3 <comparateur> valeur3
...
OR colonne1 = valeur1 AND colonne2 = valeur2 AND ... AND colonneN <comparateur> valeurN
Toujours basé sur notre jeu d'essais, voici un exemple plus complet :: cherchons tous les événements survenus après 3h12'30" :

Exemple 36

SELECT *
FROM T_EVENEMENT
WHERE (EVT_HEURE, EVT_MINUTE, EVT_SECONDE)  > (3, 12, 30)
 
EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT
----------- ---------- ----------- -------------
3           15         0           Arrêt
4           0          0           Incendie
4           22         33          Explosion
Exemple 37 : et sa construction logiquement équivalente :

SELECT *
FROM T_EVENEMENT
WHERE EVT_HEURE > 3
   OR EVT_HEURE = 3 AND EVT_MINUTE > 12
   OR EVT_HEURE = 3 AND EVT_MINUTE = 12 AND EVT_SECONDE > 30
 
EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT
----------- ---------- ----------- -------------
3           15         0           Arrêt
4           0          0           Incendie
4           22         33          Explosion
En fait le ROW VALUE CONSTRUCTOR agit, dans les recherches, un peu comme si l'on visualisait les données de manière fractale
Le plus grossier est traité, puis on s'interrese à quelques données plus fine une fois le grossier fixé, et ainsi de suite en fonction du niveau fractal que l'on s'est fixé.

NOTA : l'utilisation du ROW VALUE CONSTRUCTOR avec un critère de comparaison d'égalité donne parfois des résultats surprenants, qui peuvent défier la logique. Il faut se méfier d'un recours systématique à une telle construction.

Exemple 38 : quels sont les événement survenus à 3h 12 (en utilisant le ROW VALUE CONSTRUCTOR)

SELECT *
FROM T_EVENEMENT
WHERE (EVT_HEURE, EVT_MINUTE, EVT_SECONDE)  = (3, 12)
 
EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT
----------- ---------- ----------- ---------------
3           12         25          Surtension
3           2          48          Surchauffe
3           15         0           Arrêt
Ce qui offre peut d'intérêt il faut bien le dire !

La construction logique équivalente étant :

Exemple 39

SELECT *
FROM T_EVENEMENT
WHERE EVT_HEURE = 3
   OR EVT_HEURE = 3 AND EVT_MINUTE = 12
 
EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT
----------- ---------- ----------- ---------------
3           12         25          Surtension
3           2          48          Surchauffe
3           15         0           Arrêt
ENFIN, notez que le ROW VALUE CONSTRUCTOR possède un intérêt immense, celui de permettre l'insertion multiple (plusieurs lignes de données) au sein du même ordre INSERT !

Exemple 40 : insertion multiple avec le ROW VALUE CONSTRUCTOR

INSERT INTO T_EVENEMENT (EVT_HEURE, EVT_MINUTE, EVT_SECONDE, EVT_EVENEMENT
VALUES ((4, 37, 21, 'Catastrophe'),
        (4, 44, 18, 'Cataclisme') ,
        (5, 21, 1, 'Destruction finale planète terre'))
ATTENTION : seul ORACLE et quelques SGBDR plus expérimentaux que professionnels (OCELOT par exemple) utilisent une telle technique !


9. Résumé

Voici les différences entre les moteurs des bases de données :

Fonctions \ SGBDR Paradox 9 Access 2000 Sybase Adaptive SQL Server 7 Oracle 8
|| (concaténation)		 Oui Non (&) Non (+) Non (+) Oui
CAST		 Oui Non Non (CONVERT) Oui Non (TO_CHAR / TO_NUM / TO_DATE)
LIKE / ESCAPE / % / _		 Oui/Oui/Oui/Oui Oui/Non/Non/Non [1] Oui/Oui/Oui/Oui Oui/Oui/Oui/Oui Oui/Oui/Oui/Oui
TRIM		 Oui Non (LTRIM, RTRIM) Non (LTRIM, RTRIM) Non (LTRIM, RTRIM) Non (LTRIM, RTRIM)
SUBSTRING		 Oui Non (MID) Non (ODBC SUBSTRING) Non (ODBC SUBSTRING) Non (SUBSTR)
LOWER / UPPER		 Oui/Oui Non (UCASE) / Non (LCASE) Oui/Oui Oui/Oui Oui/Oui
EXTRACT		 Oui Non Non (DATEPART) Non (DATEPART) Non
CURRENT_DATE / CURRENT_TIME / CURRENT_TIMESTAMP		 Non/Non/Non (TODAY, NOW en QBE) Non (NOW) Non (GETDATE) Non (GETDATE() / CURRENT_TIMESTAMP) Non (SYSDATE)
AVG / MAX / MIN / SUM / COUNT		 Oui/Oui/Oui/Oui Oui/Oui/Oui/Oui Oui/Oui/Oui/Oui Oui/Oui/Oui/Oui Oui/Oui/Oui/Oui
VARIANCE / STDEV Non/Non Non/Non Non/Non Non/Non Oui/Oui
ABS / MOD / SIGN / SQRT / PI Non/Non/Non/Non/Non Oui /Non/ Non (SGN) / Non (SQR) / Non Oui/Non (%)/Oui/Oui/Oui Oui/Non (%)/Oui/Oui/Oui Oui/Oui/Oui/Oui/Non
CEIL / FLOOR / ROUND / TRUNC Non/Non/Non/Non Non/Non (INT)/Non/Non Non/Oui/Oui/Non Non/Oui/Oui/Non Oui/Oui/Oui/Oui
EXP / LN / LOG / POWER Non/Non/Non/Non Oui/Non/Oui/Non Oui/Non/Oui/Oui Oui/Non/Oui/Oui Oui/Oui/Non (LOG (M, n))/Oui
COS / SIN / TAN Non/Non/Non Oui/Oui/Oui Oui/Oui/Oui Oui/Oui/Oui Oui/Oui/Oui
COSH / SINH / TANH Non/Non/Non Non/Non/Non Non/Non/Non Non/Non/Non
Oui/Oui/Oui [2]
INITCAP / LPAD / RPAD / REPLACE Non/Non/Non/Non Non/Non/Non/Non Non/Non/Non/Non Non/Non/Non/Oui Oui/Oui/Oui/Oui
SOUNDEX / TRANSLATE / INSTR / LENGTH Non (COMME dans QBE) / Non / Non / Non Non / Non / Non (LOCATE) / Non Oui / Non (REPLACE) / Non (PATINDEX) / Non (LEN) Oui / Non (REPLACE) / Non (PATINDEX) / Non (LEN) Oui / Oui / Non (LOCATE) / Non
TO_CHAR pour numérique / pour date Non/Non Non/Non Non (CONVERT) / Non (CONVERT) Non (CONVERT) / Non (CONVERT)
Non (auto [3])/ Non
ASCII / CHR Non/Non Non/Non Oui / Non (CHAR) Oui / Non (CHAR) Oui/Oui
ADD_MONTH / MONTH_BETWEEN Non/Non Non (DATEADD) / Non (DATEDIFF) Non (DATEADD) / Non (DATEDIFF) Non (DATEADD) / Non (DATEDIFF) Oui/Oui
LAST_DAY / NEXT_DAY Non/Non Non/Non Non/Non Non/Non Oui/Oui
GREATEST / LEAST / DECODE Non/Non/Non Non/Non/Non Non/Non/Non Non/Non/Non Oui/Oui/Non
IS NULL / COALESCE / NULLIF		 Oui/Non/Non Oui / Non / Non (ISNULL) Oui / Non / Non (ISNULL) Oui/Oui/Oui Oui / Non / Non (NVL)
ROW VALUE CONSTRUCTOR		 Non Non Non Non Oui

10. Conclusion

Curieusement Paradox n’a pas implémenté les opérateurs de récupération des valeurs temporelles courantes alors qu’ils existent en QBE ! Il est facile de s’en passer en passant la date ou l’heure courante en paramètre de la requête, mais tout de même...

En ce qui concerne MS Access, on ne peut qu’être frappé par le fait que la plupart des fonctions de base des requêtes sont incompatible avec la norme. Par exemple le LIKE utilise des jokers différent : * remplace le % et ? remplace le _. Cela oblige à utiliser une syntaxe propriétaire qui rend la portabilité des requêtes très difficile d’un SGBDR à l’autre. Mais ne serait-ce pas là une tactique voulue ??? Autre inconvénient il ne sait pas traiter le NOT BETWEEN !!!

Plus curieux la plupart des SGBDR n'accepte pas l'opérateur de concaténation ||!

Dans Sybase comme SQL Server la fonction modulo s'exprime sous la forme d'un caractère '%' d'ou d'énormes possibilité de confusions entre les caractères joker du like, comme le calcul de pourcentage... A quand une release sur ce sujet ???

Le SGBDR le plus proche de la norme est celui de Sybase, suivi de SQL Server. Le plus complet par son jeu de fonction est sans doute Oracle.


Livres
SQL - développement
SQL - le cours de référence sur le langage SQL
Avant d'aborder le SQL
Définitions
SGBDR fichier ou client/serveur ?
La base de données exemple (gestion d'un hôtel)
Modélisation MERISE
Mots réservés du SQL
Le SQL de A à Z
Les fondements
Le simple (?) SELECT
Les jointures, ou comment interroger plusieurs tables
Groupages, ensembles et sous-ensembles
Les sous-requêtes
Insérer, modifier, supprimer
Création des bases
Gérer les privilèges ("droits")
Toutes les fonctions de SQL
Les techniques des SGBDR
Les erreur les plus fréquentes en SQL
Les petits papiers de SQLPro
Conférence Borland 2003
L'héritage des données
Données et normes
Modélisation par méta données
Optimisez votre SGBDR et vos requêtes SQL
Le temps, sa mesure, ses calculs
QBE, le langage de ZLOOF
Des images dans ma base
La jointure manquante
Clefs auto incrémentées
L'indexation textuelle
L'art des "Soundex"
Une seule colonne, plusieurs données
La division relationnelle, mythe ou réalité ?
Gestion d'arborescence en SQL
L'avenir de SQL
Méthodes et standards
Les doublons
SQL Server
Eviter les curseurs
Un aperçu de TRANSACT SQL V 2000
SQL Server 2000 et les collations
Sécurisation des accès aux bases de données SQL Server
Des UDF pour SQL Server
SQL Server et le fichier de log...
Paradox
De vieux articles publiés entre 1995 et 1999 dans la défunte revue Point DBF

(1)Contrairement à la norme, l’opérateur like d’Access comme celui de SQL Server, ne fait aucune différence entre les majuscules et les minuscules (sauf paramétrage spécifique du serveur). Quand à l’opérateur « escape » il n’est tout simplement pas implémenté dans Access, mais il semble possible de le contourner en utilisant une syntaxe à base de jokers spécifique à Access…. Les jokers d’Access ne sont pas standards : * remplace le % et ? remplace le _.
(2)La tangente hyperbolique est bien implémentée mais elle ne donne apparement pas les bons résultats... !
(3)La conversion est automatique dans Oracle

Copyright © 2003 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Cette page est déposée.

 
 
Developpez.com

Nous contacter

Participez

Informations légales

 
Services

Forum SGBD & SQL

Blogs

Hébergement

 
Partenaires

Hébergement Web