Developpez.com - SGBD & SQL
X

Choisissez d'abord la catégorieensuite la rubrique :

Solutions pour le petit jeu des requêtes SQL


I. Solutions - 1° partie
I-A. Solution au problème n° 1 - un dans dix
I-A-1. Solution question 1 : les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une
I-A-1-a. Solution 1
I-A-1-b. Solution 2
I-A-1-c. Solution 3
I-A-1-d. Solution 4
I-A-1-e. Solution 5
I-A-1-f. Solution 6
I-A-2. Solution question 2 : les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une valuée à UN...
I-A-2-a. Solution 1
I-A-2-b. Solution 2
I-A-2-c. Solution 3
I-A-2-d. Solution 4
I-A-2-e. Solution 5
I-A-2-f. Solution 6
I-B. Solution au problème n° 2 : le publipostage
I-C. Solution au problème n° 3 : la date an 2000
I-D. Solution au problème n° 4 : les chambres libres
I-D-1. Les chambres qui sont libres pendant toute la période allant du 11 au 14 janvier 2000 ?
I-D-2. L'occupation des chambres pour la journée du 13 janvier 2000 ?
I-D-3. Le planning des occupations pour toutes les chambres et toutes les dates du 11 au 14 janvier 2000 ?
I-E. Problème n° 5 : date d'anniversaire
I-E-1. Les anniversaires entre le 21 février et le 20 mars
I-E-2. Les anniversaires entre le 21 décembre et le 20 janvier
I-E-3. La requête paramétrée générique
I-F. Solution au problème n° 6 - énumération
I-G. Solution au problème n° 7 - Le comptage
I-H. Solution au problème n° 8 - linéarisation
I-I. Solution au problème n° 9 - les Trous
I-J. Solution au problème n° 10 - symétrie négative


I. Solutions - 1° partie


I-A. Solution au problème n° 1 - un dans dix


I-A-1. Solution question 1 : les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une


I-A-1-a. Solution 1
Créez une vue pour "coloniser" les dix valeurs :
CREATE VIEW V_CELKO_TEN_IN_ON_TIO
AS
   SELECT TIO_ID, TIO_1 AS TIO_N
   FROM   T_CELKO_TEN_IN_ON_TIO
   UNION  ALL
   SELECT TIO_ID, TIO_2 AS TIO_N
   FROM   T_CELKO_TEN_IN_ON_TIO
   UNION  ALL
   SELECT TIO_ID, TIO_3 AS TIO_N
   FROM   T_CELKO_TEN_IN_ON_TIO
   UNION  ALL
   SELECT TIO_ID, TIO_4 AS TIO_N
   FROM   T_CELKO_TEN_IN_ON_TIO
   UNION  ALL
   SELECT TIO_ID, TIO_5 AS TIO_N
   FROM   T_CELKO_TEN_IN_ON_TIO
   UNION  ALL
   SELECT TIO_ID, TIO_6 AS TIO_N
   FROM   T_CELKO_TEN_IN_ON_TIO
   UNION  ALL
   SELECT TIO_ID, TIO_7 AS TIO_N
   FROM   T_CELKO_TEN_IN_ON_TIO
   UNION  ALL
   SELECT TIO_ID, TIO_8 AS TIO_N
   FROM   T_CELKO_TEN_IN_ON_TIO
   UNION  ALL
   SELECT TIO_ID, TIO_9 AS TIO_N
   FROM   T_CELKO_TEN_IN_ON_TIO
   UNION  ALL
   SELECT TIO_ID, TIO_10 AS TIO_N
   FROM   T_CELKO_TEN_IN_ON_TIO
Dès lors la requête devient simple :
SELECT T.*
FROM   T_CELKO_TEN_IN_ON_TIO T
       INNER JOIN V_CELKO_TEN_IN_ON_TIO V
             ON T.TIO_ID = V.TIO_ID
WHERE  V.TIO_N = 0
GROUP  BY T.TIO_ID, T.TIO_1, T.TIO_2, T.TIO_3, T.TIO_4, 
          T.TIO_5, T.TIO_6, T.TIO_7, T.TIO_8, T.TIO_9, T.TIO_10
HAVING COUNT(*) = 9

I-A-1-b. Solution 2
Utilisez le Row Value Constructor pour globaliser la comparaison :
SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE  (TIO_1, TIO_2, TIO_3, TIO_4, TIO_5, TIO_6, TIO_7, TIO_8, TIO_9, TIO_10)
       IN ((TIO_1, 0, 0, 0, 0, 0, 0, 0, 0, 0),
           (0, TIO_2, 0, 0, 0, 0, 0, 0, 0, 0),
           (0, 0, TIO_3, 0, 0, 0, 0, 0, 0, 0),
           (0, 0, 0, TIO_4, 0, 0, 0, 0, 0, 0),
           (0, 0, 0, 0, TIO_5, 0, 0, 0, 0, 0),
           (0, 0, 0, 0, 0, TIO_6, 0, 0, 0, 0),
           (0, 0, 0, 0, 0, 0, TIO_7, 0, 0, 0),
           (0, 0, 0, 0, 0, 0, 0, TIO_8, 0, 0),
           (0, 0, 0, 0, 0, 0, 0, 0, TIO_9, 0),
           (0, 0, 0, 0, 0, 0, 0, 0, 0, TIO_10));

I-A-1-c. Solution 3
Certains SGBDR possèdent la fonction SIGN qui renvoie -1, 0 ou 1 si un nombre est respectivement négatif, zéro ou positif. Combiné à la fonction SQL ABS (valeur absolue), vous pouvez écrire :
SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE    SIGN(ABS(TIO_1)) + SIGN(ABS(TIO_2)) 
       + SIGN(ABS(TIO_3)) + SIGN(ABS(TIO_4))
       + SIGN(ABS(TIO_5)) + SIGN(ABS(TIO_6)) 
       + SIGN(ABS(TIO_7)) + SIGN(ABS(TIO_8)) 
       + SIGN(ABS(TIO_9)) + SIGN(ABS(TIO_10)) = 1 ;
Si la fonction SIGN n'est pas présente dans votre SGBDR, vous pouvez utiliser la structure CASE :
SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE    CASE WHEN ABS(TIO_1) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_2) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_3) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_4) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_5) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_6) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_7) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_8) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_9 )> 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_10) > 0 THEN 1 ELSE 0 END = 1 ;

I-A-1-d. Solution 4
Petit truc mathématique : si une et seulement une colonne est différente de zéro, alors il y a forcément une collection de 9 colonnes qui possèdent la valeur zéro… lapalissade !
SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE  0 IN (ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) 
                        + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9),
             ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) 
                        + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_10),
             ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) 
                        + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_9) + ABS(TIO_10),
             ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) 
                        + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
             ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_6) 
                        + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
             ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_5) + ABS(TIO_6) 
                        + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
             ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) 
                        + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
             ABS(TIO_1) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) 
                        + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
             ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) 
                        + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10))
  AND  ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + 
       ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10) <> 0 ;

I-A-1-e. Solution 5
On peut utiliser la structure CASE d'une manière plus subtile :
SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE  CASE WHEN TIO_1 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_2 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_3 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_4 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_5 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_6 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_7 = 0 THEN 1 ELSE 0 END + 
       CASE WHEN TIO_8 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_9 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_10 = 0 THEN 1 ELSE 0 END = 9 ;


C'est-à-dire en comptabilisant les zéro !


I-A-1-f. Solution 6
Certaines propriétés mathématiques peuvent être utilisées. Par exemple la multiplication des valeurs +1 de toutes les colonnes doit être égale à l'addition de toutes les valeurs de colonnes à condition que la somme des colonnes soit différent de zéro :
SELECT  * 
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE (TIO_1 + 1) * (TIO_2 + 1) * (TIO_3 + 1) * (TIO_4 + 1) * (TIO_5 + 1) *
      (TIO_6 + 1) * (TIO_7 + 1) * (TIO_8 + 1) * (TIO_9 + 1) * (TIO_10 + 1)
      = 
      TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 + 1
  AND TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 <> 0 ;
Bien entendu, cela ne fonctionne que si les nombres ne sont que des entiers...


I-A-2. Solution question 2 : les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une valuée à UN...

Reprenons les requêtes déjà vues et essayons de les amender pour obtenir la solution...


I-A-2-a. Solution 1

Par rapport à notre vue, on obtient le résultat en déplaçant le filtre WHERE dans un HAVING combiné à CASE pour comptabilisé les zéros associé à une somme qui doit être valuée à 1 :
SELECT T.*
FROM   T_CELKO_TEN_IN_ON_TIO T
       INNER JOIN V_CELKO_TEN_IN_ON_TIO V
             ON T.TIO_ID = V.TIO_ID
GROUP  BY T.TIO_ID, T.TIO_1, T.TIO_2, T.TIO_3, T.TIO_4, T.TIO_5,
          T.TIO_6, T.TIO_7, T.TIO_8, T.TIO_9, T.TIO_10
HAVING SUM(CASE WHEN V.TIO_N = 0 THEN 1 ELSE 0 END) = 9 
   AND SUM(V.TIO_N) = 1 ;

I-A-2-b. Solution 2
Avec le Row Value Constructor c'est encore plus trivial :
SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE  (TIO_1, TIO_2, TIO_3, TIO_4, TIO_5, TIO_6, TIO_7, TIO_8, TIO_9, TIO_10)
       IN ((1, 0, 0, 0, 0, 0, 0, 0, 0, 0),
           (0, 1, 0, 0, 0, 0, 0, 0, 0, 0),
           (0, 0, 1, 0, 0, 0, 0, 0, 0, 0),
           (0, 0, 0, 1, 0, 0, 0, 0, 0, 0),
           (0, 0, 0, 0, 1, 0, 0, 0, 0, 0),
           (0, 0, 0, 0, 0, 1, 0, 0, 0, 0),
           (0, 0, 0, 0, 0, 0, 1, 0, 0, 0),
           (0, 0, 0, 0, 0, 0, 0, 1, 0, 0),
           (0, 0, 0, 0, 0, 0, 0, 0, 1, 0),
           (0, 0, 0, 0, 0, 0, 0, 0, 0, 1)) ;

I-A-2-c. Solution 3
Avec SIGN ou CASE il suffit d'ajouter une condition supplémentaire
SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE  SIGN(ABS(TIO_1)) + SIGN(ABS(TIO_2)) + SIGN(ABS(TIO_3)) + SIGN(ABS(TIO_4))
       + SIGN(ABS(TIO_5)) + SIGN(ABS(TIO_6)) + SIGN(ABS(TIO_7)) + SIGN(ABS(TIO_8)) 
       + SIGN(ABS(TIO_9)) + SIGN(ABS(TIO_10)) = 1 
  AND TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 = 1 ;
SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE    CASE WHEN ABS(TIO_1) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_2) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_3) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_4) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_5) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_6) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_7) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_8) > 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_9 )> 0 THEN 1 ELSE 0 END
       + CASE WHEN ABS(TIO_10) > 0 THEN 1 ELSE 0 END = 1 
  AND TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 = 1 ;

I-A-2-d. Solution 4
Il suffit de modifier la dernière clause
SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE  0 IN (ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) 
                        + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9),
             ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) 
                        + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_10),
             ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) 
                        + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_9) + ABS(TIO_10),
             ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) 
                        + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
             ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_6) 
                        + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
             ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_5) + ABS(TIO_6) 
                        + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
             ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) 
                        + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
             ABS(TIO_1) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) 
                        + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10),
             ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) 
                        + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10))
 AND  TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 +
      TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 = 1 ;

I-A-2-e. Solution 5
Là encore, le simple ajout d'un prédicat complémentaire suffit :
SELECT *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE  CASE WHEN TIO_1 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_2 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_3 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_4 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_5 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_6 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_7 = 0 THEN 1 ELSE 0 END + 
       CASE WHEN TIO_8 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_9 = 0 THEN 1 ELSE 0 END +
       CASE WHEN TIO_10 = 0 THEN 1 ELSE 0 END  = 9
  AND  TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + 
       TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 = 1 ;

I-A-2-f. Solution 6
Même topo pour la solution 6, une modification mineure suffit :
SELECT  *
FROM   T_CELKO_TEN_IN_ON_TIO
WHERE (TIO_1 + 1) * (TIO_2 + 1) * (TIO_3 + 1) * (TIO_4 + 1) * (TIO_5 + 1) *
      (TIO_6 + 1) * (TIO_7 + 1) * (TIO_8 + 1) * (TIO_9 + 1) * (TIO_10 + 1)
      =
      TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 + 1
  AND TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 = 1 ;

I-B. Solution au problème n° 2 : le publipostage

Il suffit de créer une table dotée d'une unique colonne contenant la séquence continue des nombres de 1 à n, n étant une valeur égale ou supérieure à la colonne PRS_NOMBRE :
CREATE TABLE T_ENUMERE_NMR
(NMR_NOMBRE INTEGER);
INSERT INTO T_ENUMERE_NMR VALUES (1);
INSERT INTO T_ENUMERE_NMR VALUES (2);
INSERT INTO T_ENUMERE_NMR VALUES (3);
INSERT INTO T_ENUMERE_NMR VALUES (4);
INSERT INTO T_ENUMERE_NMR VALUES (5);
INSERT INTO T_ENUMERE_NMR VALUES (6);
INSERT INTO T_ENUMERE_NMR VALUES (7);
INSERT INTO T_ENUMERE_NMR VALUES (8);
...
INSERT INTO T_ENUMERE_NMR VALUES (99);
...
Dès lors, la requête devient basique avec un non équi jointure :
SELECT PRS_NOM, PRS_VILLE
FROM   T_PERSONNE_PRS PRS
       INNER JOIN T_ENUMERE_NMR NMR
             ON PRS.PRS_NOMBRE >= NMR.NMR_NOMBRE
Simple, non ???


I-C. Solution au problème n° 3 : la date an 2000

En utilisant les propriété de la division entière on peut ajouter 100 ans lorsque si la division par 60 donne une valeur supérieure à zéro. Ainsi la requête :
SELECT *, CAST(SUBSTRING(AMT_FIN FROM 1 FOR 2)AS INTEGER) + 2000 - 100 * (CAST(SUBSTRING(AMT_FIN FROM 1 FOR 2)AS INTEGER)/60) AS AN,
       SUBSTRING(AMT_FIN ,3 , 2) AS MOIS, SUBSTRING(AMT_FIN ,5 , 2) AS JOUR
FROM   T_AMORTISSEMENT_AMT ;
Donne :
AMT_FIN AMT_FIN_Y2K AN          MOIS JOUR 
------- ----------- ----------- ---- ---- 
990601  NULL        1999        06   01
970201  NULL        1997        02   01
021201  NULL        2002        12   01
941101  NULL        1994        11   01
920715  NULL        1992        07   15
Il est alors facile de construite une requête UPDATE dessus :
UPDATE T_AMORTISSEMENT_AMT
SET    AMT_FIN_Y2K =
       CAST(CAST(SUBSTRING(AMT_FIN FROM 1 FOR 2)AS INTEGER) + 2000 
            - 100 * (CAST(SUBSTRING(AMT_FIN FROM 1 FOR 2)AS INTEGER)/60) AS CHAR(4)) + '-'
       + SUBSTRING(AMT_FIN FROM 3 FOR 2) + '-' + SUBSTRING(AMT_FIN FROM 5 FOR 2) ;
La même requête pour MS SQL Server :
UPDATE T_AMORTISSEMENT_AMT
SET    AMT_FIN_Y2K =
       CAST(CAST(SUBSTRING(AMT_FIN, 1, 2)AS INTEGER) + 2000
            - 100 * (CAST(SUBSTRING(AMT_FIN, 1, 2)AS INTEGER)/60) AS CHAR(4)) + '-'
       + SUBSTRING(AMT_FIN, 3, 2) + '-' + SUBSTRING(AMT_FIN, 5, 2) ;

Un internaute, Akshell, nous a donné une solution via l'opération ensembliste UNION :
UPDATE AMORTISSEMENT 
SET FIN_AMORT_Y2K = (SELECT '20' || 
                            SUBSTRING(FIN_AMORT FROM 1 FOR 2) || '-' ||
                            SUBSTRING(FIN_AMORT FROM 3 FOR 2) || '-' || 
                            SUBSTRING(FIN_AMORT FROM 5 FOR 2) 
                     FROM   AMORTISSEMENT A1 
                     WHERE  SUBSTRING(FIN_AMORT FROM 1 FOR 2) < '60' 
                       AND  A1.FIN_AMORT = AMORTISSEMENT.FIN_AMORT 
                     UNION  ALL 
                     SELECT '19' || 
                            SUBSTRING(FIN_AMORT FROM 1 FOR 2) || '-' || 
                            SUBSTRING(FIN_AMORT FROM 3 FOR 2) || '-' || 
                            SUBSTRING(FIN_AMORT FROM 5 FOR 2)
                     FROM   AMORTISSEMENT A2 
                     WHERE  SUBSTRING(FIN_AMORT FROM 1 FOR 2) >= '60' 
                       AND  A2.FIN_AMORT = AMORTISSEMENT.FIN_AMORT) ;
Mais une solution plus simple consiste à utiliser le CASE :
UPDATE AMORTISSEMENT
SET FIN_AMORT_Y2K = (SELECT CASE
                               WHEN SUBSTRING(FIN_AMORT FROM 1 FOR 2) < '60' 
                                  THEN '20'
                                  ELSE '19'
                            END ||
                            SUBSTRING(FIN_AMORT FROM 1 FOR 2) ||  '-' || 
                            SUBSTRING(FIN_AMORT FROM 3 FOR 2) || '-' || 
                            SUBSTRING(FIN_AMORT FROM 5 FOR 2) || 
                           
                     FROM AMORTISSEMENT A
                     WHERE A.FIN_AMORT = AMORTISSEMENT.FIN_AMORT) ;

I-D. Solution au problème n° 4 : les chambres libres


I-D-1. Les chambres qui sont libres pendant toute la période allant du 11 au 14 janvier 2000 ?

La recherche du vide ou de la non existence fait toujours partie des choses difficiles à exprimer en SQL...
Mais l'opérateur IN et plus particulièrement sa négation le NOT IN permet souvent de se tirer de ce genre d'embarras...
SELECT CHB_NUM
FROM   T_CHAMBRE_CHB
WHERE  CHB_NUM NOT IN (SELECT CHB_NUM
                       FROM   T_PLANNING_PLN
                       WHERE  PLN_JOUR BETWEEN '2000-01-11' AND '2000-01-14'
                         AND  PLN_LIBRE = 'False') ;

I-D-2. L'occupation des chambres pour la journée du 13 janvier 2000 ?

Là la problématique est différente. Il nous faut toutes les chambres et si possible l'indication d'occupation. Et s'il n'y a pas d'indication d'occupation, alors on renseigne avec la valeur True la colonne PLN_LIBRE. Bien entendu il faut utiliser une jointure externe :

SELECT CHB.CHB_NUM, COALESCE(PLN_LIBRE, 'True') AS PLN_LIBRE
FROM   T_CHAMBRE_CHB CHB
       LEFT OUTER JOIN T_PLANNING_PLN PLN
            ON CHB.CHB_NUM = PLN.CHB_NUM
WHERE  PLN_JOUR = '2000-01-13' OR PLN_JOUR IS NULL ;

O u encore :
SELECT CHB.CHB_NUM, COALESCE(PLN_LIBRE, 'True') AS PLN_LIBRE
FROM   T_CHAMBRE_CHB CHB
       LEFT OUTER JOIN T_PLANNING_PLN PLN
            ON CHB.CHB_NUM = PLN.CHB_NUM 
               AND  PLN_JOUR = '2000-01-13' ;

I-D-3. Le planning des occupations pour toutes les chambres et toutes les dates du 11 au 14 janvier 2000 ?

Il n'est pas possible de faire cette requête directement car une partie de l'information est manquante. Par exemple le 11 janvier ne figure nulle part. Il faut donc rajouter cette information à notre base de données sous la forme d'une table de date que nous allons appeler T_CALENCRIER_CLD
CREATE TABLE T_CALENDRIER_CLD
(CLD_JOUR DATETIME);

INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-01-01');
...
INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-01-10');
INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-01-11');
INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-01-12');
INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-01-13');
INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-01-14');
INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-01-15');
...
INSERT INTO  T_CALENDRIER_CLD VALUES ('2000-12-31');

Pour obtenir toutes les dates considérées et toutes les chambre un produit cartésien semble parfait...
SELECT CLD.CLD_JOUR, CHB.CHB_NUM, COALESCE(PLN.PLN_LIBRE, 'True') AS PLN_LIBRE
FROM   T_CALENDRIER_CLD CLD
       CROSS JOIN T_CHAMBRE_CHB CHB
       LEFT OUTER JOIN T_PLANNING_PLN PLN
            ON CLD.CLD_JOUR = PLN.PLN_JOUR 
               AND CHB.CHB_NUM = PLN.CHB_NUM
WHERE  CLD.CLD_JOUR BETWEEN '2000-01-11' AND '2000-01-14' ;

… et on fait la jointure externe de ce produit cartésien avec la table T_PLANNING_PLN


I-E. Problème n° 5 : date d'anniversaire

Connaissez-vous la fonction EXTRACT ? Non, sinon elle vous aurait grandement facilité la vie. Elle permet d'extraire une partie de date comme le mois ou le jour... Pour MS SQL Server cette fonction s'appelle DATEPART.


I-E-1. Les anniversaires entre le 21 février et le 20 mars

SELECT *
FROM   T_CLIENT_CLI
WHERE  ((EXTRACT(MONTH FROM CLI_DATE_NAISSANCE) - 1) * 31 + EXTRACT(DAY FROM CLI_DATE_NAISSANCE))
       BETWEEN  52 AND 82 ;

Les chiffes 83 et 113 sont calculés de la façon suivante : partant de février = 1 (2eme mois -1) et mars = 2 (3eme mois -1)
1 * 31 j + 21 j = 52 jours
et
2 * 31 j + 20 j = 82 jours

La même requête pour MS SQL Server :
SELECT *
FROM   T_CLIENT_CLI 
WHERE  ((DATEPART(MONTH, CLI_DATE_NAISSANCE) - 1) * 31 + DATEPART(DAY, CLI_DATE_NAISSANCE)) 
           BETWEEN  52 AND 82 ;

I-E-2. Les anniversaires entre le 21 décembre et le 20 janvier

SELECT *
FROM   T_CLIENT_CLI
WHERE  MOD(((EXTRACT (MONTH FROM CLI_DATE_NAISSANCE) - 1) * 31
           + EXTRACT(DAY FROM CLI_DATE_NAISSANCE)),  362 )
       BETWEEN 0 AND 20 ;

MOD étant la fonction modulo (reste de la division entière).
La difficulté est le passage à l'année suivante. Mais une simple translation via la fonction modulo suffit :
   21 décembre => 11 * 31 + 21 = 362,
   20 janvier => 31 * 0 + 20 = 20  

La même requête pour MS SQL Server :
SELECT *
FROM   T_CLIENT_CLI 
WHERE  ((DATEPART(MONTH, CLI_DATE_NAISSANCE) - 1) * 31 
          + DATEPART(DAY, CLI_DATE_NAISSANCE)) % 362 
        BETWEEN 0 AND 20 ;

I-E-3. La requête paramétrée générique

Il suffit de combiner les deux requêtes à l'aide de la structure CASE :
SELECT *
FROM T_CLIENT_CLI 
WHERE  MOD(((EXTRACT(MONTH FROM CLI_DATE_NAISSANCE) - 1) * 31 
             + EXTRACT(DAY FROM CLI_DATE_NAISSANCE)), 
       CASE WHEN (EXTRACT(MONTH FROM :dd) - 1) * 31 + EXTRACT(DAY FROM :dd) 
                  > (EXTRACT(MONTH FROM :df) - 1) * 31 + EXTRACT(DAY FROM :df) 
                THEN (EXTRACT(MONTH FROM :dd) - 1) * 31 + EXTRACT(DAY FROM :dd) 
                ELSE ((EXTRACT(MONTH FROM CLI_DATE_NAISSANCE) - 1) * 31 
                      + EXTRACT(DAY FROM CLI_DATE_NAISSANCE)) + 1 
             END )
       BETWEEN CASE 
                  WHEN (EXTRACT(MONTH FROM :dd) - 1) * 31 + EXTRACT(DAY FROM :dd) 
                        < (EXTRACT(MONTH FROM :df) - 1) * 31 + EXTRACT(DAY FROM :df) 
                     THEN (EXTRACT(MONTH FROM :dd) - 1) * 31 + EXTRACT(DAY FROM :dd) 
                     ELSE 0 
                  END AND (EXTRACT(MONTH FROM :df) - 1) * 31 + EXTRACT(DAY FROM :df) ;
La requête paramétrée pour MS SQL Server (dans une procédure stockée) :
CREATE PROCEDURE SP_ANNIVERSAIRES @dd DATETIME, @df DATETIME
AS
SELECT *
FROM T_CLIENT_CLI 
WHERE  ((DATEPART(MONTH, CLI_DATE_NAISSANCE) - 1) * 31 
         + DATEPART(DAY, CLI_DATE_NAISSANCE)) 
      % CASE WHEN (DATEPART(MONTH, @dd) - 1) * 31 + DATEPART(DAY, @dd) 
                   > (DATEPART(MONTH, @df) - 1) * 31 + DATEPART(DAY, @df) 
                THEN (DATEPART(MONTH, @dd) - 1) * 31 + DATEPART(DAY, @dd) 
                ELSE ((DATEPART(MONTH, CLI_DATE_NAISSANCE) - 1) * 31 
                       + DATEPART(DAY, CLI_DATE_NAISSANCE)) + 1 
             END 
       BETWEEN CASE 
                  WHEN (DATEPART(MONTH, @dd) - 1) * 31 + DATEPART(DAY, @dd) 
                        < (DATEPART(MONTH, @df) - 1) * 31 + DATEPART(DAY, @df) 
                     THEN (DATEPART(MONTH, @dd) - 1) * 31 + DATEPART(DAY, @dd) 
                     ELSE 0 
                  END AND (DATEPART(MONTH, @df) - 1) * 31 + DATEPART(DAY, @df) ;
De plus, si vous disposez de la fonction CURRENT_DATE, avec un peu d'astuce, vous pouvez trouver une requête vous permettant de trouver tous les clients dont la date anniversaire sera dans les 15 jours qui viennent...


I-F. Solution au problème n° 6 - énumération

Finalement, n'importe quel nombre entre 10 et 9999 n'est que l'addition d'une unité, d'une dizaine, d'une centaine et d'un millier. Il suffit donc de placer dans la requête 4 fois la table pour représenter chacun des membres de cette addition. Pour cela, nous allons utiliser la jointure croisée qui réalise le produit cartésien des tables :
INSERT INTO T_ENTIER_ENT 
SELECT TEU.ENT_N                      -- les unités
       + 10   * TED.ENT_N             -- les dizaines
       + 100  * TEC.ENT_N             -- les centaines
       + 1000 * TEM.ENT_N             -- les milliers
FROM   T_ENTIER_ENT TEU               -- table des entiers pour les unités
       CROSS JOIN T_ENTIER_ENT TED    -- table des entiers pour les dizaines
       CROSS JOIN T_ENTIER_ENT TEC    -- table des entiers pour les centaines
       CROSS JOIN T_ENTIER_ENT TEM    -- table des entiers pour les milliers
WHERE  TEU.ENT_N           
       + 10   * TED.ENT_N  
       + 100  * TEC.ENT_N  
       + 1000 * TEM.ENT_N  > 9        -- empêche d'insérer les doublons des unités
Si votre SGBDR ne dispose pas de la jointure croisée, il suffit d'écrire votre requête comme ceci :
INSERT INTO T_ENTIER_ENT 
SELECT TEU.ENT_N                      -- les unités
       + 10   * TED.ENT_N             -- les dizaines
       + 100  * TEC.ENT_N             -- les centaines
       + 1000 * TEM.ENT_N             -- les milliers
FROM   T_ENTIER_ENT TEU,              -- table des entiers pour les unités
              T_ENTIER_ENT TED,       -- table des entiers pour les dizaines
              T_ENTIER_ENT TEC,       -- table des entiers pour les centaines
              T_ENTIER_ENT TEM        -- table des entiers pour les milliers
WHERE  TEU.ENT_N           
       + 10   * TED.ENT_N  
       + 100  * TEC.ENT_N  
       + 1000 * TEM.ENT_N  > 9        -- empêche d'insérer les doublons des unités ;
Une autre solution m'a été donnée par un internaute, Akshell. Elle reste similaire dans son principe :
INSERT INTO T_ENTIER_ENT 
SELECT CAST(CAST(TEM.ENT_N AS CHAR(1)) || 
            CAST(TEC.ENT_N AS CHAR(1)) ||
            CAST(TED.ENT_N AS CHAR(1)) ||
            CAST(TEU.ENT_N AS CHAR(1))  AS INTEGER)
FROM   T_ENTIER_ENT TEU               -- table des entiers pour les unités
       CROSS JOIN T_ENTIER_ENT TED    -- table des entiers pour les dizaines
       CROSS JOIN T_ENTIER_ENT TEC    -- table des entiers pour les centaines
       CROSS JOIN T_ENTIER_ENT TEM    -- table des entiers pour les milliers
WHERE  (CAST(TEM.ENT_N AS CHAR(1)) || 
        CAST(TEC.ENT_N AS CHAR(1)) ||
        CAST(TED.ENT_N AS CHAR(1)) ||
        CAST(TEU.ENT_N AS CHAR(1))) NOT BETWEEN '0000' AND '0009' ;
Elle propose de réaliser cette énumération en combinant les chiffres sous forme littérale. Mais elle est moins performante du fait de l'utilisation nonuple de la fonction CAST.

Même requête que ci-dessus pour MS SQL Server :
INSERT INTO T_ENTIER_ENT 
SELECT CAST(CAST(TEM.ENT_N AS CHAR(1)) + CAST(TEC.ENT_N AS CHAR(1)) + CAST(TED.ENT_N AS CHAR(1)) + CAST(TEU.ENT_N AS CHAR(1))  AS INTEGER)
FROM   T_ENTIER_ENT TEU               -- table des entiers pour les unités
       CROSS JOIN T_ENTIER_ENT TED    -- table des entiers pour les dizaines
       CROSS JOIN T_ENTIER_ENT TEC    -- table des entiers pour les centaines
       CROSS JOIN T_ENTIER_ENT TEM    -- table des entiers pour les milliers
WHERE  (CAST(TEM.ENT_N AS CHAR(1)) +
        CAST(TEC.ENT_N AS CHAR(1)) +
        CAST(TED.ENT_N AS CHAR(1)) +
        CAST(TEU.ENT_N AS CHAR(1))) NOT BETWEEN '0000' AND '0009' ;

I-G. Solution au problème n° 7 - Le comptage

Le problème de ce genre de requête est la présence de doublons absolus. En l'occurrence nous avons deux fois la présence du nom DUPONT dans nos prospects. Si nous faisons une requête classique de comptage pour numéroter les lignes et que DUPONT ne figure qu'une seule fois dans la table, alors tout va bien :
SELECT PP1.PSP_NOM, COUNT(PP2.PSP_NOM) AS N
FROM   T_PROSPECT_PSP PP1
       INNER JOIN T_PROSPECT_PSP PP2
             ON PP1.PSP_NOM > PP2.PSP_NOM
GROUP BY PP1.PSP_NOM ;
PSP_NOM          N            
---------------- ----------- 
BAILLE           1
CLAUDE           2
DUPONT           3
GAUTIER          4
MARTIN           5
Mais en présence du doublon DUPONT, cette même requête donne un résultat qui n'est pas exploitable :
PSP_NOM          N           
---------------- ----------- 
BAILLE           1
CLAUDE           2
DUPONT           6
GAUTIER          5
MARTIN           6
Dès lors, il faut ajouter une information capable de discriminer les deux DUPONT.
Une idée consiste à prendre une table de nombre uniques et de faire une jointure croisée (produit cartésien), puis de prendre un nombre différent pour chaque valeur de même occurrence.
En partant d'une table telle que celle-ci :
CREATE TABLE T_ENTIER_ENT
(ENT_N INTEGER);
INSERT INTO T_ENTIER_ENT VALUES (1);
INSERT INTO T_ENTIER_ENT VALUES (2);
INSERT INTO T_ENTIER_ENT VALUES (3);
INSERT INTO T_ENTIER_ENT VALUES (4);
INSERT INTO T_ENTIER_ENT VALUES (5);
INSERT INTO T_ENTIER_ENT VALUES (6);
INSERT INTO T_ENTIER_ENT VALUES (7);
INSERT INTO T_ENTIER_ENT VALUES (8);
INSERT INTO T_ENTIER_ENT VALUES (9);
...
Dès lors, la requête suivante réalise le produit cartésien :
SELECT PSP_NOM, ENT_N
FROM   T_PROSPECT_PSP
       CROSS JOIN T_ENTIER_ENT ;
PSP_NOM          ENT_N 
---------------- ----------- 
ARMAND           1
ARMAND           2
ARMAND           3
ARMAND           4
ARMAND           5
ARMAND           6
ARMAND           7
ARMAND           8
ARMAND           9
BAILLE           1
BAILLE           2
BAILLE           3
...
Mais il y a bien trop de lignes pour que ce résultat soit exploitable. Il faut donc restreindre le produit cartésien sur le nombre d'occurrences des valeurs de mons. Ceci est possible à l'aide de la requête suivante :
SELECT PSP_NOM, N, ENT_N
FROM   (SELECT PSP_NOM, COUNT(*) AS N
        FROM   T_PROSPECT_PSP
        GROUP  BY PSP_NOM) T
       CROSS JOIN T_ENTIER_ENT
WHERE  ENT_N <= T.N ;
PSP_NOM          N           ENT_N
---------------- ----------- ----------- 
ARMAND           1           1
BAILLE           1           1
CLAUDE           1           1
DUPONT           2           1
DUPONT           2           2
GAUTIER          1           1
MARTIN           1           1
Nous avons maintenant notre discriminant d'occurrence de nom constitué par la colonne ENT_N
Par facilité, constituons une vue :
CREATE VIEW V_PROSPECT_PSP
AS
SELECT PSP_NOM, ENT_N AS PSP_DISCRIMINANT, 
       CAST(PSP_NOM AS CHAR(16)) + CAST(ENT_N AS CHAR(16)) AS PSP_NOM_DIFFERENT
FROM   (SELECT PSP_NOM, COUNT(*) AS N
        FROM   T_PROSPECT_PSP
        GROUP  BY PSP_NOM) T
       CROSS JOIN T_ENTIER_ENT
WHERE ENT_N <= T.N ;
Une extraction brute de cette vue nous donne :
PSP_NOM          PSP_DISCRIMINANT PSP_NOM_DIFFERENT
---------------- ---------------- -----------------
ARMAND           1                ARMAND          1
BAILLE           1                BAILLE          1
CLAUDE           1                CLAUDE          1
DUPONT           1                DUPONT          1
DUPONT           2                DUPONT          2
GAUTIER          1                GAUTIER         1
MARTIN           1                MARTIN          1
Ce qui est suffisant maintenant pour traiter notre problème initial :
SELECT PP1.PSP_NOM, COUNT(*) N
FROM   V_PROSPECT_PSP PP1
       INNER JOIN V_PROSPECT_PSP PP2
             ON PP1.PSP_NOM_DIFFERENT > PP2.PSP_NOM_DIFFERENT 
GROUP BY PP1.PSP_NOM, PP1.PSP_NOM_DIFFERENT ;
Et donne le résultat attendu :
PSP_NOM          N
---------------- ----------- 
BAILLE           1
CLAUDE           2
DUPONT           3
DUPONT           4
GAUTIER          5
MARTIN           6
Depuis la norme SQL:2003, de nouvelles fonction ont été ajoutées pour traiter ce genre de cas : RANK(), DENSE_RANK() et ROW_NUMBER(). Voici un exemple de leur fonctionnement sur notre table exercice :
SELECT PSP_NOM,
       RANK()       OVER(ORDER BY PSP_NOM) AS RANG,
       DENSE_RANK() OVER(ORDER BY PSP_NOM) AS RANG_DENSE,
       ROW_NUMBER() OVER(ORDER BY PSP_NOM) AS NUMERO
FROM   T_PROSPECT_PSP ;
PSP_NOM          RANG        RANG_SENSE  NUMERO
---------------- ----------- ----------- ----------- 
BAILLE           1           1           1
CLAUDE           2           2           2
DUPONT           3           3           3
DUPONT           3           3           4
GAUTIER          5           4           5
MARTIN           6           5           6
C'est plus pratique il faut l'avouer, mais ces fonctions dites de fenêtrage ne peuvent pas apparaître dans les sous requêtes en ce sens qu'elle n'opèrent qu'en présence des résultats devant être affichés.
 


I-H. Solution au problème n° 8 - linéarisation

Une première possibilité, la plus traditionnelle est d'utiliser les sous requêtes dans la clause select :
SELECT COUNT(*) AS 'TBL1', 
       (SELECT COUNT(*) FROM TBL2) AS 'TBL2', 
       (SELECT COUNT(*) FROM TBL3) AS 'TBL3'
FROM   TBL1 ;
Mais certains SGBDR acceptent de ne pas faire figurer de clause FROM, par exemple MS SQL Server :
SELECT (SELECT COUNT(*) FROM TBL1) AS 'TBL1', 
       (SELECT COUNT(*) FROM TBL2) AS 'TBL2', 
       (SELECT COUNT(*) FROM TBL3) AS 'TBL3';
D'autres nécessitent l'usage d'une "fausse" table. C'est le cas d'Oracle avec la table DUAL :
SELECT (SELECT COUNT(*) FROM TBL1) AS 'TBL1', 
       (SELECT COUNT(*) FROM TBL2) AS 'TBL2', 
       (SELECT COUNT(*) FROM TBL3) AS 'TBL3'
FROM DUAL
Mais que faire en l'absence de sous requêtes ?

On peut y arriver en faisant une table regroupant toutes les informations puis en allant à la pêche pour compter les clefs non redondantes.

L'idée est de faire une jointure qui permette de retrouver toutes les clefs de toutes les tables jointes.

La seule jointure capable de cela c'est de faire un produit cartésien (une multiplication des lignes des tables) à l'aide du CROSS JOIN :

Avec la syntaxe des jointures normalisées, cela donne :
SELECT COUNT (DISTINCT TBL1.TBL_ID) as TBL1,
       COUNT (DISTINCT TBL2.TBL_ID) as TBL2,
       COUNT (DISTINCT TBL3.TBL_ID) as TBL3
FROM TBL1 CROSS JOIN TBL2 CROSS JOIN TBL3 
Avec la syntaxe "ancienne" :
SELECT COUNT (DISTINCT TBL1.TBL_ID) as TBL1,
       COUNT (DISTINCT TBL2.TBL_ID) as TBL2,
       COUNT (DISTINCT TBL3.TBL_ID) as TBL3
FROM TBL1, TBL2, TBL3
Mais attention : cette requête est explosive !

En effet dans notre exemple, nous avons respectivement 5, 4 et 5 lignes. Le produit cartésien de ces 3 tables va donc donner : 100 lignes.

Imaginez ce que cela pourra donner si les tables comptent 80, 100 et 140 lignes seulement... Plus d'un million de lignes à traiter !


I-I. Solution au problème n° 9 - les Trous

Une solution simple et rapide est donnée par :
SELECT NMR + 1 as NUM_MANQUANT
FROM   T_NUMERO_NMR
WHERE  NMR + 1 NOT IN (SELECT NMR
                       FROM   T_NUMERO_NMR)
  AND  NMR + 1 < (SELECT MAX(NMR) 
                  FROM T_NUMERO_NMR) ;
Mais cette solution suppose que le trou ne fasse jamais qu'un seul n°. Imaginez ce qui se passerait si la table des n° était ainsi constituée :
DELETE FROM T_NUMERO_NMR;

INSERT INTO T_NUMERO_NMR VALUES (2);
INSERT INTO T_NUMERO_NMR VALUES (3);
INSERT INTO T_NUMERO_NMR VALUES (5);
INSERT INTO T_NUMERO_NMR VALUES (8);
INSERT INTO T_NUMERO_NMR VALUES (9);
INSERT INTO T_NUMERO_NMR VALUES (10);
INSERT INTO T_NUMERO_NMR VALUES (14);
INSERT INTO T_NUMERO_NMR VALUES (15);
Alors la requête ci-dessus appliquée à ce nouveau jeu de données donnerait :
NUM_MANQUANT
------------
4
6
11
Ce qui ne correspond qu'aux 1er n° de chaque trou...

Une solution passe par la création d'une liste contenant tous les nombres de 1 à n, n étant au moins la plus forte valeur dans la table considérée puis faire une requête imbriquée avec un NOT in en sélectant le min !

Pour créer une table d'entiers de 1 à n, on peut partir d'une table contenant les 10 premiers entiers de 0 à 9 comme nous l'avons fait lors du problème n°6 :
CREATE TABLE T_ENTIER_ENT
(ENT_N INTEGER);
INSERT INTO T_ENTIER_ENT VALUES (0);
INSERT INTO T_ENTIER_ENT VALUES (1);
INSERT INTO T_ENTIER_ENT VALUES (2);
INSERT INTO T_ENTIER_ENT VALUES (3);
INSERT INTO T_ENTIER_ENT VALUES (4);
INSERT INTO T_ENTIER_ENT VALUES (5);
INSERT INTO T_ENTIER_ENT VALUES (6);
INSERT INTO T_ENTIER_ENT VALUES (7);
INSERT INTO T_ENTIER_ENT VALUES (8);
INSERT INTO T_ENTIER_ENT VALUES (9);
INSERT INTO T_ENTIER_ENT
SELECT TEU.ENT_N                      
       + 10   * TED.ENT_N             
       + 100  * TEC.ENT_N             
       + 1000 * TEM.ENT_N             
FROM   T_ENTIER_ENT TEU               
       CROSS JOIN T_ENTIER_ENT TED    
       CROSS JOIN T_ENTIER_ENT TEC    
       CROSS JOIN T_ENTIER_ENT TEM    
WHERE  TEU.ENT_N           
       + 10   * TED.ENT_N  
       + 100  * TEC.ENT_N  
       + 1000 * TEM.ENT_N  > 9 ;
La requête pour récupérer tous les trous est la suivante :
SELECT ENT_N
FROM   T_ENTIER_ENT
WHERE  ENT_N NOT IN (SELECT NMR 
                     FROM T_NUMERO_NMR) ;
ENT_N
----------- 
0
4
7
20
30
40
50
60
70
80
90
100
110
...
Le problème est que cette requête va bien au delà des bornes des valeurs de la table concernée. Nous devons donc restreindre le résultat aux valeurs max et min de la table d'origine...
SELECT ENT_N
FROM   T_ENTIER_ENT
WHERE  ENT_N NOT IN (SELECT NMR 
                     FROM   T_NUMERO_NMR)
  AND  ENT_N BETWEEN (SELECT MIN(NMR) 
                      FROM   T_NUMERO_NMR) 
                 AND (SELECT MAX(NMR) 
                      FROM   T_NUMERO_NMR)

I-J. Solution au problème n° 10 - symétrie négative

Rien de sorcier. Il suffit de négativer l'identifiant numérique, comme le montant :
INSERT INTO T_LIGNE_FACTURE_LIF
SELECT LIF_NUM * -1,
       FAC_NUM, LIF_ARTICLE, LIF_MONTANT * -1
FROM   T_LIGNE_FACTURE_LIF L
WHERE  FAC_NUM = 79
  AND  LIF_NUM > 0 ;

Dès lors, la requête d'extraction pour présenter les informations dans le bon ordre est la suivante :
SELECT FAC_NUM, LIF_ARTICLE, LIF_MONTANT
FROM   T_LIGNE_FACTURE_LIF
ORDER  BY FAC_NUM, ABS(LIF_NUM), LIF_NUM DESC ;

Mais certains SGBDR ne supportent pas d'expression, ni de fonctions, dans la clause de tri, ni même le tri externe. Dans ce cas il faut calculer les nouvelles colonnes :
SELECT FAC_NUM, LIF_ARTICLE, LIF_MONTANT,  ABS(LIF_NUM) AS LIF_NUM1, LIF_NUM AS LIF_NUM2
FROM   T_LIGNE_FACTURE_LIF
ORDER  BY FAC_NUM, LIF_NUM1, LIF_NUM2 DESC ;
 

Valid XHTML 1.1!Valid CSS!

Copyright © SQLPro. 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.

Contacter le responsable de la rubrique SGBD & SQL