Initiation à SQL

Expressions de table

Une expression de table permet de définir une vue qui n'est nécessaire que dans une seule requête.

Les expressions de table sont provisoires. Elles ne sont valables que pour la durée de l'instruction SQL considérée ; elles ne peuvent pas être partagées comme des vues, mais leur utilisation est cependant plus souple que celle des vues.

Vous trouverez ci-dessous des exemples d'utilisation, dans des requêtes, d'expressions de table, imbriquées ou non imbriquées.

Expressions de table imbriquées

Une expression de table imbriquée est une vue temporaire dont la définition est imbriquée (définie directement) dans la clause FROM de la requête principale.

La requête ci-après utilise une expression de table imbriquée pour générer une réponse à la question suivante : Quel est le salaire total moyen, ainsi que le niveau d'études et l'ancienneté, des employés dont le niveau d'études est supérieur à 16 :

 
     SELECT EDLEVEL, HIREYEAR, DECIMAL(AVG(TOTAL_PAY), 7,2)
        FROM (SELECT EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,
                      SALARY+BONUS+COMM AS TOTAL_PAY
                           FROM EMPLOYEE
                 WHERE EDLEVEL > 16 )  AS PAY_LEVEL
        GROUP BY EDLEVEL, HIREYEAR
        ORDER BY EDLEVEL, HIREYEAR	

Voici le résultat obtenu :

     EDLEVEL HIREYEAR    3        
     ------- ----------- ---------
          17        1967  28850,00
          17        1973  23547,00
          17        1977  24430,00
          17        1979  25896,50
          18        1965  57970,00
          18        1968  32827,00
          18        1973  45350,00
          18        1976  31294,00
          19        1958  51120,00
          20        1975  42110,00

Cette requête nécessite l'utilisation d'une expression de table imbriquée pour extraire l'année d'embauche de la colonne HIREDATE. Cette année sera ensuite utilisée dans la clause GROUP BY. Vous pouvez choisir de ne pas générer l'expression en tant que vue, si vous avez l'intention d'exécuter les mêmes requêtes à l'aide de valeurs différentes de EDLEVEL.

L'exemple utilise la fonction scalaire intégrée DECIMAL qui renvoie la représentation décimale d'un nombre ou d'une chaîne de caractères. Pour plus de détails sur les fonctions, consultez le document SQL Reference.

Expressions de table communes

Une expression de table commune est une expression de table que vous créez parce que vous avez l'intention de l'utiliser plusieurs fois dans une requête complexe. Définissez-la et attribuez-lui un nom dès le début de la requête, à l'aide d'une clause WITH. Les utilisations répétées d'une expression de table commune se serviront à chaque fois du même ensemble de résultats. Par comparaison, si vous utilisiez une expression de table imbriquée ou une vue imbriquée, l'ensemble de résultats serait généré à chaque fois, avec des résultats éventuellement différents.

L'exemple suivant permet de répertorier tous les employés de la société dont le niveau d'études est supérieur à 16, dont le salaire moyen est inférieur à celui des employés de même niveau d'études embauchés la même année. Les éléments de la requête sont décrits de façon plus détaillée à la suite de la requête.

(1)

     WITH
           PAYLEVEL AS                                                 
              (SELECT EMPNO, EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,           
                      SALARY+BONUS+COMM AS TOTAL_PAY                                     
                           FROM EMPLOYEE                                                         
                  WHERE EDLEVEL > 16),                                                       

(2)

           PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS         
              (SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY)                  
                  FROM PAYLEVEL                                            
                  GROUP BY EDLEVEL, HIREYEAR)                                                         

(3)

     SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, DECIMAL(AVG_TOTAL_PAY,7,2)  
        FROM PAYLEVEL, PAYBYED                                             
        WHERE EDLEVEL = EDUC_LEVEL                                        
          AND HIREYEAR = YEAR_OF_HIRE                              
          AND TOTAL_PAY < AVG_TOTAL_PAY                        

(1)
Il s'agit d'une expression de table commune appelée PAYLEVEL. Cette table résultat comporte le matricule, l'année d'embauche et le salaire total de l'employé ainsi que son niveau d'études. Elle ne contient que des lignes concernant des employés dont le niveau d'études est supérieur à 16.

(2)
Il s'agit d'une expression de table commune appelée PAYBYED (pour paie selon études). Elle utilise la table PAYLEVEL générée dans l'expression de table commune précédente pour obtenir le niveau d'études, l'année d'embauche et le salaire moyen des employés, par niveau d'études, embauchés la même année. Les noms des colonnes renvoyées par cette table sont différents (par exemple, EDUC_LEVEL) des noms de colonnes utilisés dans la liste de sélection. Elle produit un ensemble de résultats appelé PAYBYED qui est identique au résultat obtenu par l'exemple d'expression de Table imbriquée.

(3)
Voici enfin la requête qui donne le résultat souhaité. Les deux tables (PAYLEVEL, PAYBYED) sont jointes pour déterminer les employés dont le salaire total est inférieur au salaire moyen des employés embauchés la même année qu'eux. Notez que PAYBYED est basé sur PAYLEVEL. Ainsi, la requête accède deux fois à PAYLEVEL dans l'ensemble de l'instruction. Les deux fois, l'évaluation de la requête utilise le même ensemble de lignes.

Voici le résultat obtenu :

     EMPNO  EDLEVEL YEAR_OF_HIRE TOTAL_PAY     5        
     ------ ------- ------------ ------------- ---------
     000210      17         1979      20132,00  25896,50
     


[ Début de page | Page précédente | Page suivante | Table des matières | Index ]