Solutions pour le petit jeu des requêtes SQL

Version PDF (Miroir) Version hors-ligne (Miroir)

II. Solutions - 2° partie
II-A. Solution au problème n° 11 - premiers
II-B. Solution au problème n° 12 - traduction
II-C. Solution au problème n° 13 - les bons joeurs
II-D. Solution au problème n° 14 - tranches d'âge
II-E. Solution au problème n° 15 - Les articles
II-F. Solution au problème n° 16 - tri alphabétique
II-G. Solution au problème n° 17 - appariement
II-H. Solution au problème n° 18 - meilleure correspondance partielle
II-I. Solution au problème n° 19 - la médiane
II-I-1. Solution question 1 : les données à calculées sont toutes différentes (il n'y a pas de doublon) et en nombre impaires :
II-I-2. Solution question 2 : les données à calculées sont toutes différentes (il n'y a pas de doublon) et en nombre paires :
II-I-3. Solution question 3 : les données à calculer admettent des doublons et un nombre de lignes impairs
II-J. Solution au problème n° 20 - insertion en bloc

II. Solutions - 2° partie

II-A. Solution au problème n° 11 - premiers

Il suffit de prendre le problème à l'envers et de récupérer facilement tous les nombres non premiers, c'est-à-dire ceux pour lesquels le reste de le division entière par au moins un des nombres qui lui est inférieur vaut 0 :

SELECT EN1.ENT_N
FROM   T_ENTIER_ENT EN1
       CROSS JOIN T_ENTIER_ENT EN2
WHERE  MOD(EN1.ENT_N, EN2.ENT_N) = 0
  AND  EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1 ;

Pour ce faire, on réalise le produit cartésien du nombre avec tous les nombres compris entre 2 et ce nombre moins un et l'on teste si le reste de la division entière vaut zéro. Si ce reste vaut zéro, alors preuve est faite que ce nombre n'est pas premier.

Dès que nous avons tous les nombres non premiers, il ne s'agit plus que de trouver le complément :

SELECT * 
FROM   T_ENTIER_ENT
WHERE  ENT_N NOT IN (SELECT EN1.ENT_N
                     FROM   T_ENTIER_ENT EN1
                            CROSS JOIN T_ENTIER_ENT EN2
                     WHERE  MOD(EN1.ENT_N, EN2.ENT_N) = 0
                       AND  EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1)
ORDER BY 1 ;

Ceci peut aussi être fait à l'aide de l'opérateur ensembliste EXCEPT

SELECT * 
FROM   T_ENTIER_ENT
EXCEPT
SELECT EN1.ENT_N
FROM   T_ENTIER_ENT EN1
       CROSS JOIN T_ENTIER_ENT EN2
WHERE  MOD(EN1.ENT_N, EN2.ENT_N) = 0
  AND  EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1
ORDER BY 1 ;

La requête pour MS SQL Server :

SELECT * 
FROM   T_ENTIER_ENT
WHERE  ENT_N NOT IN (SELECT EN1.ENT_N
                     FROM   T_ENTIER_ENT EN1
                            CROSS JOIN T_ENTIER_ENT EN2
                     WHERE  EN1.ENT_N % EN2.ENT_N = 0
                       AND  EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1)
ORDER BY 1 ;

II-B. Solution au problème n° 12 - traduction

Facile avec la fonction COALESCE et des sous requêtes. Voici un premier exemple de solution.
Solution 1 :

SELECT TDR_LIBELLE, TDR_ID, TDR_LANGUE
FROM   T_TRADUCTION_TDR TDR
WHERE  TDR_LANGUE = COALESCE((SELECT DISTINCT TDR_LANGUE 
                              FROM   T_TRADUCTION_TDR 
                              WHERE  TDR_LANGUE = 'Français' 
                                AND  TDR_ID = TDR.TDR_ID), (SELECT DISTINCT TDR_LANGUE 
                                                            FROM   T_TRADUCTION_TDR 
                                                            WHERE  TDR_LANGUE = 'Anglais'  
                                                              AND  TDR_ID = TDR.TDR_ID)) ;

Une solution élégante nous a été donnée par Sonia.

Solution 2 :

SELECT TDR_LIBELLE, TDR_ID, TDR_LANGUE
FROM   T_TRADUCTION_TDR
WHERE  TDR_LANGUE = 'Français'
UNION
SELECT TDR_LIBELLE, TDR_ID, TDR_LANGUE
FROM   T_TRADUCTION_TDR
WHERE  TDR_LANGUE='Anglais'
  AND  TDR_ID NOT IN (SELECT TDR_ID
                      FROM   T_TRADUCTION_TDR
                      WHERE  TDR_LANGUE='Français')

Cette solution n'utilise pas la fonction COALESCE.
Enfin, une solution sans utiliser de sous requêtes.

Solution 3 :

SELECT COALESCE(fr.TDR_LIBELLE, en.TDR_LIBELLE) AS TDR_LIBELLE,
       COALESCE(fr.TDR_LANGUE, en.TDR_LANGUE) AS TDR_LANGUE,
       COALESCE(fr.TDR_ID, en.TDR_ID) AS TDR_ID
FROM   T_TRADUCTION_TDR t
       LEFT OUTER JOIN T_TRADUCTION_TDR as en
            ON en.TDR_ID = t.TDR_ID
               AND en.TDR_LANGUE='Anglais'
       LEFT OUTER JOIN T_TRADUCTION_TDR as fr
            ON fr.TDR_ID = t.TDR_ID
               AND fr.TDR_LANGUE='Français'
GROUP BY t.TDR_ID, fr.TDR_LIBELLE, en.TDR_LIBELLE, fr.TDR_LANGUE, en.TDR_LANGUE, fr.TDR_ID, en.TDR_ID

Si l'on analyse les performances des trois requêtes à l'aide d'un outil qui visualise le plan d'exécution (MS SQL Server), on est frappé par la grande différence qui existe entre la première version et les deux suivantes...

Solution 1 (24 phases de traitement) :

Solution 2 (7 phases de traitement) :

Solution 3 (8 phases de traitement) :

Mais la manière de faire n'indique pas toujours le coût exact. Mieux vaut s'interroger sur le nombre de lectures effectuées pour chacune des requêtes :

Solution 1 :

Table 'T_TRADUCTION_TDR'. Compte d'analyses 7, lectures logiques 7, lectures physiques 0, lectures anticipées 0.

Solution 2 :

Table 'T_TRADUCTION_TDR'. Compte d'analyses 4, lectures logiques 4, lectures physiques 0, lectures anticipées 0.

Solution 3 :

Table 'T_TRADUCTION_TDR'. Compte d'analyses 9, lectures logiques 9, lectures physiques 0, lectures anticipées 0.

Autrement dit, la solution 3, pourtant assez économe en phases de traitement, est pire en terme de lecture que la première.

II-C. Solution au problème n° 13 - les bons joeurs

En fait, la difficulté réside dans la jointure... Mais un simple CROSS JOIN suffit à résoudre le problème.

Pour compter les bonne réponses il suffit d'utiliser le CASE et d'attribuer la valeur 1 si la réponse est bonne et la valeur 0 si elle est fausse. La somme de ces valeurs donne le score.

Voici donc une solution :

SELECT PNL_NOM,
       CASE WHEN J.PNL_REPONSE1 = R.RPS_REPONSE1 THEN 1 ELSE 0 END +
       CASE WHEN J.PNL_REPONSE2 = R.RPS_REPONSE2 THEN 1 ELSE 0 END +
       CASE WHEN J.PNL_REPONSE3 = R.RPS_REPONSE3 THEN 1 ELSE 0 END +
       CASE WHEN J.PNL_REPONSE4 = R.RPS_REPONSE4 THEN 1 ELSE 0 END +
       CASE WHEN J.PNL_REPONSE5 = R.RPS_REPONSE5 THEN 1 ELSE 0 END AS BONNE_REPONSE
FROM  T_PANEL_PNL J
      CROSS JOIN T_REPONSES_RPS R
ORDER BY BONNE_REPONSE DESC ;

Une solution dans le même genre nous a été donnée par Fabien C. :

SELECT PNL_NOM, 
       ((SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE1 = J.PNL_REPONSE1) +
        (SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE2 = J.PNL_REPONSE2) +
        (SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE3 = J.PNL_REPONSE3) +
        (SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE4 = J.PNL_REPONSE4) +
        (SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE5 = J.PNL_REPONSE5)) AS T_REPONSES_RPS
FROM T_PANE id="XV"L_PNL J ;

II-D. Solution au problème n° 14 - tranches d'âge

Plusieurs petites difficultés :

le calcul de l'âge ;
la réalisation des tranches ;
les effets de bords...

Pour le calcul de l'âge, je me sert de la formule suivante :

CAST(CURRENT_DATE - CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425

qui me donne un age décimal.

Pour la réalisation des tranches, il faut ajouter l'information puisqu'elle n'y est pas. Construisons donc une nouvelle table :

CREATE TABLE T_TRANCHE_AGE_TAG
(TAG_LIMITE INTEGER) ;

INSERT INTO T_TRANCHE_AGE_TAG VALUES (18) ;
INSERT INTO T_TRANCHE_AGE_TAG VALUES (40) ;

Ces informations nous suffisent, mais il serait plus pratique d'avoir la représentation des tranches sous la forme :

TRANCHE_MIN   TRANCHE_MAX
------------- -------------
0             18
18            40
40            NULL

Car nous pourrons alors joindre la table des clients par une fourchette de valeur à cette représentation. Bien entendu nous ferrons en sorte de représenter le NULL comme étant une valeur loin dans le futur.

Cette requête nous donne presque la solution :

SELECT *
FROM   T_TRANCHE_AGE_TAG TA1
       LEFT OUTER JOIN  T_TRANCHE_AGE_TAG TA2
            ON TA1.TAG_LIMITE < TA2.TAG_LIMITE ;

TAG_LIMITE  TAG_LIMITE  
----------- ----------- 
18          40
40          NULL

Il faut juste rajouter la tranche commençant par zéro. Cela peut être fait à l'aide d'une opération d'UNION. Profitons-en pour mettre tout cela dans une vue :

CREATE VIEW V_TRANCHE_AGE_TAG
AS
SELECT TA1.TAG_LIMITE AS TRANCHE_MIN, TA2.TAG_LIMITE AS TRANCHE_MAX
FROM   T_TRANCHE_AGE_TAG TA1
       LEFT OUTER JOIN  T_TRANCHE_AGE_TAG TA2
            ON TA1.TAG_LIMITE < TA2.TAG_LIMITE
UNION  
SELECT 0, MIN(TAG_LIMITE)
FROM   T_TRANCHE_AGE_TAG ;

Assemblons maintenant tout ceci dans la requête finale :

SELECT TRANCHE_MIN, COALESCE(TRANCHE_MAX, 9999) AS TRANCHE_MAX, COUNT(*) AS NOMBRE 
FROM   V_TRANCHE_AGE_TAG TAG
       INNER JOIN T_CLIENT_CLI
             ON  CAST(CURRENT_DATE - CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425 
                    BETWEEN TRANCHE_MIN AND COALESCE(TRANCHE_MAX, 9999)
GROUP BY TRANCHE_MIN, TRANCHE_MAX
ORDER BY TRANCHE_MIN ;

TRANCHE_MIN TRANCHE_MAX NOMBRE      
----------- ----------- ----------- 
0           18          6
18          40          4
40          9999        13

Néanmoins cette solution n'est pas correcte. En effet ceux qui ont exactement 18 ans ou 40 ans sont comptabilisé 2 fois. Voici ce qui serait beaucoup plus correct :

SELECT TRANCHE_MIN, COALESCE(TRANCHE_MAX, 9999) AS TRANCHE_MAX, COUNT(*) AS NOMBRE 
FROM   V_TRANCHE_AGE_TAG TAG
       INNER JOIN T_CLIENT_CLI
             ON  CAST(CURRENT_DATE - CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425 > TRANCHE_MIN AND
                 CAST(CURRENT_DATE - CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425 <= COALESCE(TRANCHE_MAX, 999)
GROUP BY TRANCHE_MIN, TRANCHE_MAX
ORDER BY 1 ;

La requête pour MS SQL Server :

SELECT TRANCHE_MIN, COALESCE(TRANCHE_MAX, 9999) AS TRANCHE_MAX, COUNT(*) AS NOMBRE 
FROM   V_TRANCHE_AGE_TAG TAG
       INNER JOIN T_CLIENT_CLI
             ON  DATEDIFF(DAY, CLI_DATE_NAISSANCE, CURRENT_TIMESTAMP)  / 365.2425 > TRANCHE_MIN AND
                 DATEDIFF(DAY, CLI_DATE_NAISSANCE, CURRENT_TIMESTAMP)  / 365.2425 <= COALESCE(TRANCHE_MAX, 999)
GROUP BY TRANCHE_MIN, TRANCHE_ id="XVI"MAX
ORDER BY 1 ;

II-E. Solution au problème n° 15 - Les articles

La encore la technique consiste à rajouter de l'information à notre base de données. Notamment en créant une tables des articles parasites. Pour cela nous créons une table de nom T_ARTICLE_ATC avec l'article considéré et sa longueur incluant soit le blanc soit l'apostrophe avec le type SQL CHAR et non VARCHAR qui élimine les blancs finaux, et nous stockons la longueur considérée de cet article avec le blanc ou l'apostrophe, comme ceci :

CREATE TABLE T_ARTICLE_ATC 
(ATC_ARTICLE  VARCHAR(8), 
 ATC_LONGUEUR INTEGER);

On y insère les principaux articles de la langue française :

INSERT INTO T_ARTICLE_ATC VALUES ('L''', 2) ;
INSERT INTO T_ARTICLE_ATC VALUES ('LE',  3) ;
INSERT INTO T_ARTICLE_ATC VALUES ('LA',  3) ;
INSERT INTO T_ARTICLE_ATC VALUES ('LES', 4) ;
INSERT INTO T_ARTICLE_ATC VALUES ('UN',  3) ;
INSERT INTO T_ARTICLE_ATC VALUES ('UNE', 4) ;
INSERT INTO T_ARTICLE_ATC VALUES ('DE',  3) ;
INSERT INTO T_ARTICLE_ATC VALUES ('DES', 4) ;
INSERT INTO T_ARTICLE_ATC VALUES ('D''', 2) ;

Ensuite, la requête consiste à lier les deux tables par jointure externe afin de conserver tous les titres en utilisant comme critère de jointure le début du titre par rapport au contenu de la table des articles. Si jointure il y a (donc colonne ATC_ARTICLE non vide), alors on transforme le titre en supprimant l'article en tête du titre et en le repoussant en fin entre parenthèses. La requête suivante répond exactement à la demande du sieur Bouchon :

SELECT CASE 
          WHEN ATC.ATC_ARTICLE IS NULL THEN OVG.OVG_TITRE 
          ELSE SUBSTRING(OVG.OVG_TITRE FROM ATC.ATC_LONGUEUR + 1 FOR 64 - ATC.ATC_LONGUEUR) 
               + ' (' + TRIM(RIGHT, SUBSTRING(OVG.OVG_TITRE FROM 1 FOR ATC.ATC_LONGUEUR)) + ')' 
       END AS TITRE 
FROM   T_OUVRAGES_OVG OVG 
       LEFT OUTER JOIN T_ARTICLE_ATC ATC 
            ON UPPER(SUBSTRING(OVG.OVG_TITRE FROM 1 FOR ATC.ATC_LONGUEUR)) 
               = SUBSTRING(ATC.ATC_ARTICLE FROM 1 FOR ATC.ATC_LONGUEUR)  ;

La requête pour MS SQL Server :

SELECT CASE
          WHEN ATC.ATC_ARTICLE IS NULL THEN OVG.OVG_TITRE
          ELSE SUBSTRING(OVG.OVG_TITRE, ATC.ATC_LONGUEUR + 1, 64 - ATC.ATC_LONGUEUR)
               + ' (' + RTRIM(SUBSTRING(OVG.OVG_TITRE, 1, ATC.ATC_LONGUEUR)) + ')'
       END AS TITRE
FROM   T_OUVRAGES_OVG OVG
       LEFT OUTER JOIN T_ARTICLE_ATC ATC
            ON UPPER(SUBSTRING(OVG.OVG_TITRE,   1, ATC.ATC_LONGUEUR))
                   = SUBSTRING(ATC.ATC_ARTIC id="XVII"LE, 1, ATC.ATC_LONGUEUR) ;

II-F. Solution au problème n° 16 - tri alphabétique

La solution consiste, comme souvent, à rajouter des informations afin de piloter l'ordre...

Ajoutons une table avec les nombres et leur correspondance littérale :

CREATE TABLE T_NOMBRE_LETTRE_NBL 
(NBL_NOMBRE CHAR(1), 
 NBL_LETTRES VARCHAR(16));

Et insérons-y les valeurs suivantes :

INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('1', 'Un');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('2', 'Deux');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('3', 'Trois');
INSERT INTO T_NOMBRE_LETTRE_NBLVALUES ('4', 'Quatre');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('5', 'Cinq');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('6', 'Six');
INSERT INTO T_NOMBRE_LETTRE_NBLVALUES ('7', 'Sept');
INSERT INTO T_NOMBRE_LETTRE_NBLVALUES ('8', 'Huit');
INSERT INTO T_NOMBRE_LETTRE_NBLVALUES ('9', 'Neuf');
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('0', 'Zéro');

Si votre SGBDR dispose des spécifications de collations conforme à la norme SQL, alors cette table suffit.

Dès lors, la requête s'exprime de la sorte :

SELECT LVR_TITRE,
       CASE
          WHEN NBL.NBL_NOMBRE IS NULL THEN LVR_TITRE
          ELSE NBL_LETTRES || ' ' || SUBSTRING(LVR.LVR_TITRE FROM 2 FOR CHARACTER_LENGTH(LVR.LVR_TITRE)-1) 
       END COLLATE SQL_latin_DictionnaryUsage
FROM   T_LIVRE_LVR LVR
       LEFT OUTER JOIN T_NOMBRE_LETTRE_NBL NBL
            ON SUBSTRING(LVR.LVR_TITRE FROM 1 FOR 1) = NBL.NBL_NOMBRE
ORDER  BY 2 ;

NOTA : Le nom de la collation est donné à titre indicatif, les éditeurs ayant toute latitude à ce sujet.

La requête pour MS SQL Server :

SELECT LVR_TITRE 
FROM   T_LIVRE_LVR LVR
       LEFT OUTER JOIN T_NOMBRE_LETTRE_NBL NBL
            ON SUBSTRING(LVR.LVR_TITRE, 1, 1) = NBL.NBL_NOMBRE
ORDER  BY CASE 
             WHEN NBL.NBL_NOMBRE IS NULL THEN LVR_TITRE
             ELSE NBL_LETTRES + ' ' + SUBSTRING(LVR.LVR_TITRE, 2, LEN(LVR.LVR_TITRE)-1)  
          END  COLLATE French_CI_AI ;

SQL Server possède l'avantage de pouvoir effectuer un tri externe, c'est à dire sans que la colonne ne figure dans la clause SELECT. Ici la collation choisie permet de faire confusion entre les caractères accentuées ou non (et plus généralement les caractères diacritiques ou non : cédille, tilde, ligature...) et de confondre aussi la casse (majuscule/minuscule).

Si telle n'était pas le cas; il faudrait alors allonger la table T_NOMBRE_LETTRE_NBL avec de nouvelles entrées telles que :

INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('À', 'a'); 
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('Â', 'a'); 
INSERT INTO T_NOMBRE_LETTRE_NBL VALUES ('â', 'a');

Etc.

Un seul problème subsisterait alors, celui de la gestion de l'espace pour le remplacement des nombres par les littéraux alors que cela n'est pas nécessaire pour id="XVIII" les caractères.

II-G. Solution au problème n° 17 - appariement

En fait une double condition doit être simultanément vraie : que tous les PER_2 de la valeur PER_1 scrutée soit dans les valeur de PER_2 en regard ET que tous les PER_2 en regard soit dans les valeurs des PER_2 de la valeur PER_1 scrutée. En négativant cette double condition, on obtient :

On joint la table sur elle-même avec équivalence pour PER_1 et différence pour PER_2. Pour plus de clarté, appelons les deux occurrences de notre table ORIGINE pour la première et DESTINATION pour la seconde :

SELECT DISTINCT ORIGINE.PER_1, DESTINATION.PER_1
FROM   T_PAIRE_PER ORIGINE
       INNER JOIN T_PAIRE_PER DESTINATION
             ON ORIGINE.PER_2 = DESTINATION.PER_2
                AND ORIGINE.PER_1 <> DESTINATION.PER_1
-- Le premier filtre considère que toutes les valeurs PER_2 de ORIGINE doivent être présentent dans PER_2 de DESTINATION.
-- Avec cette clause A1 et A9 sont éliminées.
WHERE NOT EXISTS (SELECT *
                   FROM   T_PAIRE_PER
                   WHERE  PER_1 = ORIGINE.PER_1
                      AND PER_2 NOT IN (SELECT PER_2
                                        FROM   T_PAIRE_PER
                                        WHERE  PER_1 = DESTINATION.PER_1))
-- Le second filtre considère que toutes les valeurs PER_2 de DESTINATION doivent être présentent dans PER_2 de ORIGINE.
-- Avec cette clause A5 est éliminée.
  AND  NOT EXISTS (SELECT *
                   FROM   T_PAIRE_PER
                   WHERE  PER_1 = DESTINATION.PER_1
                      AND PER_2 NOT IN (SELECT PER_2
                                        FROM   T_PAIRE_PER
                                        WHERE  PER_1 = ORIGINE.PER_1))
ORDER BY 1, 2 ;

Le premier filtre
conduit au jeu de
résultat suivant : Le second filtre
conduit au jeu de
résultat suivant : Solution

PER_1 PER_1 
----- ----- 

A2    A3
A2    A4
A2    A6
A2    A8
A2    A9


A3    A6
A3    A8

A4    A2
A4    A3
A4    A6
A4    A8
A4    A9

A5    A1

A6    A3

A6    A8


A8    A3

A8    A6

PER_1 PER_1 
----- ----- 
A1    A5

A2    A4



A3    A2
A3    A4
A3    A6
A3    A8

A4    A2






A6    A2
A6    A3
A6    A4
A6    A8

A8    A2
A8    A3
A8    A4
A8    A6

A9    A2
A9    A4

PER_1 PER_1
----- ----- 


A2    A4





A3    A6
A3    A8

A4    A2







A6    A3

A6    A8


A8    A3

A8    A6

Les données communes ont été mise en évidence par une couleur blanche et en gras.

On peut lire ces filtres de la manière suivante : il ne faut pas qu'il existe une valeur de PER_2 de la table origine qui ne soit pas dans la table de destination. Et vice versa pour le second filtre.

En fait le problème s'apparente à la division relationnelle et plus particulièrement à la division de Todd que Joe Celko évoque dans son ouvrage SQL for smarties (SQ id="XIX"L Avancé, Vuibert éditeur).

II-H. Solution au problème n° 18 - meilleure correspondance partielle

Pour trouver la réponse à ce problème, il faut décomposer la chaîne de caractère composant le code en autant de chaînes de caractères de longueur allant de 1 à la valeur maximum permet le format du type SQL, ici 16. Puis de comptabiliser pour chacune de ces combinaisons, celle qui sont communes à l'aide d'un GROUP BY. Enfin de retenir de ce calcul celles ayant le nombre le plus élevé d'occurrences avec la longueur la plus grande.

Pour décomposer la chaîne représentant le code, il faut une table des nombres allant de 1 à la longueur maximale de la colonnes RTE_CODE soit 16. Pour cela reprenons la table T_ENTIER_ENT de l'exercice 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                      -- 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

Volontairement, je la peuple avec des valeurs nettement supérieures à la taille maximale de la colonne concernée...

La requête est alors la suivante :

SELECT DISTINCT R.RTE_DESTINATION, SUBSTRING(RTE_CODE FROM 1 FOR LNG) as RTE_CODE
FROM   T_ROUTE_RTE R
       INNER JOIN (SELECT RTE_DESTINATION, MAX(LNG) AS LNG
                   FROM   (SELECT RTE_DESTINATION, RTE_CODE2, LNG, COUNT(*) AS NBR
                           FROM   (SELECT RTE_DESTINATION,
                                          SUBSTRING(RTE_CODE FROM 1 FOR ENT_N) as RTE_CODE2,
                                          ENT_N AS LNG
                                   FROM   T_ROUTE_RTE
                                          CROSS JOIN T_ENTIER_ENT
                                   WHERE  ENT_N BETWEEN 1 AND CHARACTER_LENGTH(RTE_CODE)) T
                           GROUP  BY RTE_DESTINATION, RTE_CODE2, LNG
                           HAVING COUNT(*) = (SELECT COUNT(*)
                                              FROM   T_ROUTE_RTE
                                              WHERE  RTE_DESTINATION = T.RTE_DESTINATION
                                              GROUP  BY RTE_DESTINATION)) TT
                   GROUP BY RTE_DESTINATION) TTT
            ON R.RTE_DESTINATION = TTT.RTE_DESTINATION