Le petit jeu des requêtes SQL

ATTENTION : le contenu des ces pages web est soumis aux dispositions légales françaises et internationales en matière de droits d'auteurs et de copyright.

SQLpro, c'est aussi un expert langage SQL, SGBD relationnels, Microsoft SQL Server à votre service :
expertise, audit, conseil, assistance, formation, analyse, modélisation de données & développement... MVP SQL Server

LE SQL de A à Z

EXERCICES ET TRAVAUX PRATIQUES (2^eme partie) ...

Problèmes et exercices corrigés sur le langage SQL.

Voici une série de travaux pratiques sur le langage SQL appelant des réponses devant être écrites uniquement à partir de requêtes, en utilisant le plus souvent possible le jeu de commande du SQL 2 normalisé (1992). Les données de départ, comme la structure des tables en jeu et la réponse attendue sous la forme d'un jeu de résultat (table) sont donné dans chacun des énoncés

Pour répondre à ces, questions, rien de plus simple : envoyez-moi vos solutions en utilisant le lien hypertexte ouvrant votre messagerie. Bien entendu, je vous répond personnellement et vous propose une correction didactique et pédagogique afin que vous compreniez le mécanisme de construction de la requête.

Voici donc une première série d'exercices numérotés de 11 à 20.

dernière mise à jour :

Par Frédéric BROUARD

LES RÈGLES DU JEU

Pour répondre à la question posée il vous faut écrire une seule requête SQL. Vous avez le droit d'utiliser toutes les techniques disponibles dans SQL:2003 :

Prédicats : BETWEEN, IN, IS NULL, IS TRUE, IS FALSE, IS UNKNOWN, ALL, ANY, SOME, EXISTS, UNIQUE, MATCH, LIKE, OVERLAPS...
Mots clef : ALL, DISTINCT...
Comparaisons : =, <=, >=, <, >, <>...
Connecteurs logiques : AND, OR, NOT...
Constructions : Row Value Constructor, CASE...
Opérateurs : UNION, EXCEPT, INTERSECT...
Fonctions : CAST, OCTET_LENGTH, BIT_LENGTH, COALESCE, NULLIF, CHARACTER_LENGTH, SUBSTRING, POSITION, TRIM, UPPER, LOWER, ABS, MOD, LN, EXP, POWER, FLOOR, CEILING, EXTRACT, CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP...
Agrégats statistiques : COUNT(*), COUNT(...), MAX(...), MIN(...), SUM(...), AVG(...)...
Clauses : WHERE, HAVING, GROUP BY, COLLATE, ORDER BY...
Les jointures : INNER JOIN, LEFT, RIGHT ou FULL OUTER JOIN, CROSS JOIN, UNION JOIN
Les agrégats cumulatifs CUBE, ROLLUP, GROUPING SETS et la fonction GROUPING (SQL:1999)
Les fonctions de fenêtrage (RANK, DENSE_RANK, PERCENT_RANK, ROW_NUMBER, CUM_DIST et leurs clauses spécifiques : ORDER BY, PARTITION BY (SQL:2003)
Les agrégats partiels obtenur avec les clauses ORDER BY, PARTITION BY (SQL:2003)
la technique de la CTE (Common table expression - expression de table) dont vous trouverez une étude ici : CTE ET récursivité des requêtes

Vous pouvez utiliser toutes les opérations disponibles dans SQL, y compris les sous requêtes dans les clauses SELECT, FROM, WHERE et HAVING, et bien entendu les sous requêtes corrélées.

Vous pouvez ajoutez dans la base une ou plusieurs nouvelles tables et leurs données ou encore définir autant de vues que vous le voulez,pour concourrir à la solution. En revanche vous n'avez pas le droit d'utiliser une UDF (fonction utilisateur), ni une procédure stockée, ni un trigger, ni bien entendu modifier la ou les tables et les données à l'origine du problème.

Tous ces exercices ont une solution et souvent plusieurs !

Pour vous aider à répondre, nous vous donnons :

la structure de la ou les tables sous la forme d'un ordre SQL CREATE TABLE...;
les données à insérer sous la forme d'un jeu d'ordre SQL INSERT INTO...;
le résultat attendu (lignes résultant de l'exécution de la requête solution);
le niveau de difficulté noté avec des étoiles de 1 (facile) à 5 (très difficile);
parfois une ébauche du résultat pour vous mettre sur la piste.

Pour savoir si votre solution est bonne, comme pour obtenir la solution, envoyez votre réponse ou votre demande par mail en précisant le titre ou le n° du problème.

Problème n° 11 - premiers

En partant d'une simple table contenant les nombre de 0 à 10000, sauriez vous en une seule requête obtenir tous les nombres premiers entre 1 et 10000 ?

Pour cela repartons de la table générée à 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);

...

ENT_N

------

0 

1 

2 

3 

4 

5 

6 

7 

8 

9

...

C'est plus facile que cela en à l'air, si l'on se souvient qu'un nombre premier est un nombre qui n'est divisible que par lui même et 1. Ou plutôt qu'un nombre qui n'est pas premier est divisible par un nombre compris entre 2 et lui-même moins un...

En l'occurrence, le résultat doit être le suivant :

ENT_N
-----------
0
1
2
3
5
7
11
13
17
19
23
29
31
...

On pourra bien entendu disserter éternellement sur la fait que 0 est premier ou non ! Mais sauriez vous exprimer la solution en une seule requête ?

cliquez sur le mail pour répondre et titrez votre mail "Problème n°11 - premiers"

Problème n° 12 - la traduction

Voici un probléme intéressant pour les utilisateurs d'applications multilingues. C'est à nouveau un internaute, Sylvain qui m'a posé une colle ! Voici ce que Sylvain à posté :
"Bonjour, j'ai une table avec trois colonnes : id, langue, titre avec comme clé (id, langue). Je voudrais faire une requête du genre :

SELECT titre, id, langue
FROM matable
WHERE "(langue='francais' ou alors langue='anglais' si n'existe pas en francais)"

En gros, je veux les résultats, de préférence en francais, mais si il n'y a pas de ligne pour le français, alors je voudrais la ligne pour la langue anglaise."
Est-ce possible ?

Voici le jeu d'essais avec lequel j'ai travaillé :

CREATE TABLE T_TRADUCTION_TDR
(TDR_ID INTEGER,
TDR_LANGUE VARCHAR(8),
TDR_LIBELLE VARCHAR(256)); INSERT INTO T_TRADUCTION_TDR VALUES (1, 'Français', 'Erreur irrécupérable');
INSERT INTO T_TRADUCTION_TDR VALUES (1, 'Anglais', 'Fatal error');
INSERT INTO T_TRADUCTION_TDR VALUES (3, 'Français', 'Disque saturé');
INSERT INTO T_TRADUCTION_TDR VALUES (4, 'Anglais', 'Memory fault');

Quelque uns de mes collègues ont trouvé d'élégantes solutions... Sauriez vous trouver au moins une façon de faire ?

Voici en tout cas le résultat attendu :

TDR_LIBELLE                TDR_ID      TDR_LANGUE
-------------------------- ----------- ----------
Erreur irrécupérable       1           Français
Disque saturé              3           Français
Memory fault               4           Anglais

A vos claviers !

cliquez sur le mail pour répondre et titrez votre mail "Problème n°12 - traduction"

Problème n° 13 - scores

La question vient d'un forum. Étant anonyme, je vous la donne telle quelle :
"Je fait un petit QCM sur internet et je crée 2 tables dans ma base de données. L'une de ces tables contient les informations sur les sondès (nom,prénom,etc..) ainsi que leurs réponses à mes questions (table T_PANEL_PNL). L'autre table contient, elle, les réponses justes à mes questions (T_REPONSES_RPS). J'aimerais savoir si il est possible à l'aide de requètes de comparer ces 2 tables, c'est à dire que j'aimerais comparer les réponses de mes sondès aux réponses justes afin de déterminer les scores..."

Voici le jeu d'essais utilisé :

CREATE TABLE T_PANEL_PNL
(PNL_NOM VARCHAR(16),
PNL_REPONSE1 INTEGER,
PNL_REPONSE2 INTEGER,
PNL_REPONSE3 INTEGER,
PNL_REPONSE4 INTEGER,
PNL_REPONSE5 INTEGER);
INSERT INTO T_PANEL_PNL VALUES ('Pierre', 1, 2, 3, 0, 0);
INSERT INTO T_PANEL_PNL VALUES ('Paul', 1, 5, 3, 2, 1);
INSERT INTO T_PANEL_PNL VALUES ('Jacques', 0, 3, 3, 2, 2);

CREATE TABLE T_REPONSES_RPS
(RPS_REPONSE1 INTEGER,
RPS_REPONSE2 INTEGER,
RPS_REPONSE3 INTEGER,
RPS_REPONSE4 INTEGER,
RPS_REPONSE5 INTEGER);
INSERT INTO T_REPONSES_RPS VALUES (1, 4, 3, 2, 1);

Sauriez vous trouver une réponse pour qu'en une seule requête apparaisse la réponse suivante :

PNL_NOM          BONNES_REPONSES
---------------- ---------------
Paul             4
Pierre           2
Jacques          2

A vos claviers !

cliquez sur le mail pour répondre et titrez votre mail "Problème n°13 - scores"

Problème n° 14 - tranches d'âge

C'est encore un anonyme qui me pose une question sur le forum SQLpro...

Bonjour,
Sur une table comprenant une date de naissance, je cherche à obtenir le nombre d'enregistrements dont les personnes ont de 1 jour à 18 ans, de 18 à 40 ans et les plus de 40 ans. Qui peut m'indiquer la requête à effectuer ?

Reprenons les données de l'exercice 5 :

      CREATE
TABLE T_CLIENT_CLI

(CLI_ID            
INTEGER,

 CLI_NOM           
VARCHAR(16),

 CLI_DATE_NAISSANCE DATE);
    

INSERT INTO T_CLIENT_CLI VALUES (24,
'HESS',      '1984-08-30'); 

       INSERT INTO T_CLIENT_CLI
VALUES (25, 'CHATON',    '1938-12-31'); 

       INSERT INTO T_CLIENT_CLI
VALUES (26, 'PLATONOFF', '1960-03-29'); 

       INSERT INTO T_CLIENT_CLI
VALUES (27, 'LETERRIER', '1945-08-26'); 

       INSERT INTO T_CLIENT_CLI
VALUES (28, 'MONTEIL',   '1985-03-23'); 

       INSERT INTO T_CLIENT_CLI
VALUES (29, 'SPITHAKIS', '1956-03-31'); 

       INSERT INTO T_CLIENT_CLI
VALUES (30, 'ORELL',     '1996-01-27'); 

       INSERT INTO T_CLIENT_CLI
VALUES (31, 'MARTINET',  '1946-10-24'); 

       INSERT INTO T_CLIENT_CLI
VALUES (32, 'RAY',       '1979-12-20'); 

       INSERT INTO T_CLIENT_CLI
VALUES (33, 'TARSAC',    '1992-08-14'); 

       INSERT INTO T_CLIENT_CLI
VALUES (34, 'COULOMB',   '1963-01-01'); 

       INSERT INTO T_CLIENT_CLI
VALUES (35, 'SAVY',      '1942-10-03'); 

       INSERT INTO T_CLIENT_CLI
VALUES (36, 'DAVID',     '1922-09-19'); 

       INSERT INTO T_CLIENT_CLI
VALUES (37, 'FORGEOT',   '1989-09-13'); 

       INSERT INTO T_CLIENT_CLI
VALUES (38, 'BERGER',    '1946-10-16'); 

       INSERT INTO T_CLIENT_CLI
VALUES (39, 'DOUBLET',   '1961-03-02'); 

       INSERT INTO T_CLIENT_CLI
VALUES (40, 'MATHIEU',   '1990-02-24'); 

       INSERT INTO T_CLIENT_CLI
VALUES (41, 'MOURGUES',  '1953-01-14'); 

       INSERT INTO T_CLIENT_CLI
VALUES (42, 'PIERROT',   '1933-02-11'); 

       INSERT INTO T_CLIENT_CLI
VALUES (44, 'ZAMPIERO',  '1985-01-19'); 

       INSERT INTO T_CLIENT_CLI
VALUES (45, 'PASCOT',    Null      
 ); 

       INSERT INTO
T_CLIENT_CLI VALUES (46, 'MECHRI',    '1950-08-11'); 

CLI_ID     
CLI_NOM         
CLI_DATE_NAISSANCE

      ----------- ----------------
------------------

      24         
HESS            
1934-08-30 

      25         
CHATON          
1938-12-31 

      26         
PLATONOFF        1960-03-29 

      27         
LETERRIER        1945-08-26 

      28         
MONTEIL         
1935-03-23 

      29         
SPITHAKIS        1956-03-31 

      30         
ORELL           
1956-01-27 

      31         
MARTINET         1946-10-24 

      32         
RAY             
1939-12-20 

      33         
TARSAC          
1942-08-14 

      34         
COULOMB         
1963-01-01 

      35         
SAVY            
1942-10-03 

      36         
DAVID           
1922-09-19 

      37         
FORGEOT         
1949-09-13 

      38         
BERGER          
1946-10-16 

      39         
DOUBLET         
1961-03-02 

      40         
MATHIEU         
1940-02-24 

      41         
MOURGUES         1953-01-14 

      42         
PIERROT         
1933-02-11 

      44         
ZAMPIERO         1945-01-19 

      45         
PASCOT           NULL

      46         
MECHRI          
1950-08-11

Le résulta de votre requête doit apparaître comme suit :

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

Sauriez vous répondre à notre internaute et de façon générique, c'est à dire quelle que soit les futures tranches d'âge à gérer ?

cliquez sur le mail pour répondre et titrez votre mail "Problème n°14 - tranches d'age"

Problème n° 15 - titres sans article

Dans les titres des livres (comme dans les titres des films) il y a souvent un article (le, la les, un, une, des...) situé en tête qui parasite l'apparition des oeuvres dans l'ordre alphabétique. D'ou la question de notre internaute, "Bouchon", son nom de code sur le forum SQL de www.developpez.com, qui demande : "J'aimerais, s'il y a un article au début du titre ("LE","LA","L'"), que celui-ci soit retiré de la chaine et se retrouve à la fin de celle-ci et entre parenthèses."

Voici notre jeu d'essais :

CREATE TABLE T_OUVRAGES_OVG 

(OVG_TITRE VARCHAR(64));

INSERT INTO T_OUVRAGES_OVG VALUES ('Le journal de
Raymond'); 

INSERT INTO T_OUVRAGES_OVG VALUES ('Le vélo d''Alphonse'); 

INSERT INTO T_OUVRAGES_OVG VALUES ('Découvrir l''escalade'); 

INSERT INTO T_OUVRAGES_OVG VALUES ('L''âge du capitaine'); 

INSERT INTO T_OUVRAGES_OVG VALUES ('Comment dévisser la vis');

INSERT INTO T_OUVRAGES_OVG VALUES ('D''amour et d''eau fraiche');

-- contenu de la table
-- résultat attendu

SELECT TITRES FROM OUVRAGES ???

TITRE
----------------------------------
Le journal de Raymond
Le vélo d'Alphonse
Découvrir l'escalade
L'âge du capitaine
Comment dévisser la vis
D'amour et d'eau fraiche
TITRE
----------------------------------
journal de Raymond (Le)
vélo d'Alphonse (Le)
Découvrir l'escalade
âge du capitaine (L')
Comment dévisser la vis
amour et d'eau fraiche (D')

Une colle finalement assez facile. Je l'ai résolue en 10 minutes... Sauriez vous être aussi performant que moi ???

Sauriez vous répondre à notre internaute ?

cliquez sur le mail pour répondre et titrez votre mail "Problème n°15 - titres sans article"

Problème n° 16 - Tri des titres

Voici encore un problème de tri... Il m'a été posé par un internaute sur le forum SQL de www.developpez.com. Il pourrait être le suite de notre exercice précédent.
Notre internaute voulait trier les titres de ses livres par ordre aplhabétique et laisser tous ceux qui ne commençent pas par une lettre à la fin. Mais en plus il exige que les titres commençant par un chiffre soient positionnés par rapport à leur écriture en lettre... Autrement dit "2001 l'odyssée de l'espace', doit figurer après "Ben Hur" et avant "Double assassinat dans la rue morgue.
Sauriez vous lui répondre ?

Exemple :

CREATE TABLE T_LIVRE_LVR
(LVR_TITRE VARCHAR(64)); INSERT INTO T_LIVRE_LVR VALUES ('À la recherche du temps perdu');
INSERT INTO T_LIVRE_LVR VALUES ('La bible') ;
INSERT INTO T_LIVRE_LVR VALUES ('2001 l''odyssée de l''espace');
INSERT INTO T_LIVRE_LVR VALUES ('Ben Hur');
INSERT INTO T_LIVRE_LVR VALUES ('Double assassinat dans la rue morgue');
INSERT INTO T_LIVRE_LVR VALUES ('Les trois mousquetaires');

Si vous avez brillament répondu à notre précédent puzzle, alors vous devriez solutionner celui là !

Voici le résultat attendu :

LVR_TITRE
----------------------------------------------------------------
À la recherche du temps perdu
Ben Hur
2001 l'odyssée de l'espace
Double assassinat dans la rue morgue
La bible
Les trois mousquetaires

cliquez sur le mail pour répondre et titrez votre mail "Problème n°16 - tri des titres"

Problème n° 17 - Appariement

Le terme appariement vient de mettre en paires, c'est à dire apparier. On dit appariement et et non apparition comme s'il s'agissait de fantôme par exemple...
Cette demande, qui m'a donné du fil à retordre, provient du forum SQL du site www.developpez.com que j'anime.
Il s'agit de récupérer les valeurs de la colonne A pour laquelle les données de la colonne B sont exactement les mêmes qu'une autre valeur de la colonne A.

CREATE TABLE T_PAIRE_PER
(PER_1 CHAR(2),
PER_2 CHAR(2)); INSERT INTO T_PAIRE_PER VALUES ('A1', 'B1');
INSERT INTO T_PAIRE_PER VALUES ('A1', 'B2');
INSERT INTO T_PAIRE_PER VALUES ('A2', 'B1');
INSERT INTO T_PAIRE_PER VALUES ('A2', 'B3');
INSERT INTO T_PAIRE_PER VALUES ('A3', 'B1');
INSERT INTO T_PAIRE_PER VALUES ('A3', 'B3');
INSERT INTO T_PAIRE_PER VALUES ('A3', 'B7');
INSERT INTO T_PAIRE_PER VALUES ('A3', 'B4');
INSERT INTO T_PAIRE_PER VALUES ('A4', 'B1');
INSERT INTO T_PAIRE_PER VALUES ('A4', 'B3');
INSERT INTO T_PAIRE_PER VALUES ('A5', 'B2');
INSERT INTO T_PAIRE_PER VALUES ('A6', 'B1');
INSERT INTO T_PAIRE_PER VALUES ('A6', 'B4');
INSERT INTO T_PAIRE_PER VALUES ('A6', 'B7');
INSERT INTO T_PAIRE_PER VALUES ('A6', 'B3');
INSERT INTO T_PAIRE_PER VALUES ('A8', 'B1');
INSERT INTO T_PAIRE_PER VALUES ('A8', 'B4');
INSERT INTO T_PAIRE_PER VALUES ('A8', 'B7');
INSERT INTO T_PAIRE_PER VALUES ('A8', 'B3');
INSERT INTO T_PAIRE_PER VALUES ('A9', 'B1');
INSERT INTO T_PAIRE_PER VALUES ('A9', 'B4');
INSERT INTO T_PAIRE_PER VALUES ('A9', 'B8');
INSERT INTO T_PAIRE_PER VALUES ('A9', 'B3');

Pour bien comprendre le problème nous allons analyser ensemble les deux premières valeurs de la colonne A. Démarrons avec la valeur 'A1' de la colonne PER_1 :

      PER_1 PER_2 

----- ----- 

      A1    B1

      A1    B2

      A2    B1

      A2    B3

      A3    B1

      A3    B3

      A3    B7

      A3    B4

      A4    B1

      A4    B3

      A5    B2

      A6    B1

      A6    B4

      A6    B7

      A6    B3

      A8    B1

      A8    B4

      A8    B7

      A8    B3

      A9    B1

      A9    B4

      A9    B8

      A9    B3

Pour la valeur 'A1' de la colonne PER_1, 

la colonne PER_2 possède les valeurs :

'B1' et 'B2'.

Quelles sont les autres valeurs de PER_1

qui ont en regard les valeurs 'B1' et 'B2'

dans la colonne PER_2 ? 
      PER_1 PER_2 

----- ----- 

      A1    B1

      A1    B2

      A2    B1

      A2    B3

      A3    B1

      A3    B3

      A3    B7

      A3    B4

      A4    B1

      A4    B3

      A5    B2

      A6    B1

      A6    B4

      A6    B7

      A6    B3

      A8    B1

      A8    B4

      A8    B7

      A8   
B3

      A9    B1

      A9    B4

      A9    B8

      A9    B3

      Aucune autre valeur de PER_1

      n'a simultanément les
valeurs 

      'B1' et 'B2' dans la colonne
PER_2

Analysons maintenant la valeur 'A2' de la colonne PER_1 :

      PER_1 PER_2 

----- ----- 

      A1    B1

      A1    B2

      A2    B1

      A2    B3

      A3    B1

      A3   
B3

      A3    B7

      A3    B4

      A4    B1

      A4    B3

      A5    B2

      A6    B1

      A6    B4

      A6    B7

      A6    B3

      A8    B1

      A8    B4

      A8    B7

      A8    B3

      A9    B1

      A9    B4

      A9    B8

      A9    B3

Pour la valeur 'A2' de la colonne PER_1, 

la colonne PER_2 possède les valeurs :

'B1' et 'B3'.

Quelles sont les autres valeurs de PER_1

qui ont en regard les valeurs 'B1' et 'B3'

dans la colonne PER_2 ? 
      PER_1 PER_2 

----- ----- 

      A1    B1

      A1    B2

      A2    B1

      A2    B3

      A3    B1

      A3    B3

      A3    B7

      A3    B4

      A4    B1

      A4    B3

      A5    B2

      A6    B1

      A6    B4

      A6    B7

      A6    B3

      A8    B1

      A8    B4

      A8    B7

      A8    B3

      A9    B1

      A9    B4

      A9    B8

      A9    B3

      'A3', 'A4', 'A6', 'A8' et 'A9'
ont simultanément

les valeurs 'B1' et 'B3'
dans la colonne PER_2

mais certaines occurrences ('A3', 'A6', 'A8' et 'A9')

ont en sus d'autres valeurs...

Seule la valeur A4 est donc à retenir.

Ici les seuls résulats valables sont 'A2' avec 'A4', puis 'A3' avec 'A6' et 'A8'. En effet les données de la colonne PER_2 sont identiques pour 'A2' et 'A4' ('B1' et 'B3'). Les données de la colonne B sont aussi identiques pour 'A3', 'A6' et 'A8', (' B1', 'B3', 'B4', 'B7').
Le résultat doit donc être :

PER_1
-----
A2
A3
A4
A6
A8

Simple en apparence, ce problème s'avère en fin de compte assez complexe. Mais j'ai déjà commencé le travail de décortication pour vous mettre sur la voie...

cliquez sur le mail pour répondre et titrez votre mail "Problème n°17 - appariement"

Problème n° 18 - Meilleure correspondance

Voici une question qui m'a été posée sur le forum SQL de www.developpez.com par neness...

Je possède une table T_ROUTE_RTE ainsi remplie :

CREATE TABLE T_ROUTE_RTE
(RTE_DESTINATION VARCHAR(32),
RTE_CODE        CHAR(16)); INSERT INTO T_ROUTE_RTE VALUES ('albania-Mobile', '0035538');
INSERT INTO T_ROUTE_RTE VALUES ('albania-Mobile', '0035569');
INSERT INTO T_ROUTE_RTE VALUES ('albania-Mobile', '0035560');
INSERT INTO T_ROUTE_RTE VALUES ('SFR-Mobile',     '0077280');
INSERT INTO T_ROUTE_RTE VALUES ('SFR-Mobile',     '0077390');
INSERT INTO T_ROUTE_RTE VALUES ('BOUYGE_TEL',     '0078452');

Pour chaque RTE_DESTINATION, je recherche la partie de la colonne RTE_CODE dont les données sont communes dans le sens de lecture. Notre quidam essaye donc de trouver les meilleures correspondances partielles de RTE_CODE communes à une même famille de RTE_DESTINATION. Notez qu'il s'agit d'ailleurs d'une opération proche d'un calcul d'agrégat...

Dans l'exemple donné, la requête doit renvoyer :

RTE_DESTINATION      RTE_CODE
-------------------- ---------------
albania-Mobile       00355
BOUYGE_TEL           0078452
SFR-Mobile           0077

En effet, '00355' est la plus grande partie commune commençant chaque RTE_CODE pour 'albania-Mobile', de même '0078452' pour 'BOUYGE_TEL' et de même '0077' pour 'SFR-Mobile'.
Autrementy dit, quelle est la partie de RTE_DESTINATION correspondant aux premiers caractères communs à chaque occurence de la colonne RTE_CODE.

Simple, mais comment faire ?

cliquez sur le mail pour répondre et titrez votre mail "Problème n°18 meilleure correspondance"

Problème n° 19 - La médiane

Dans un forum consacré à SQL Server de Microsoft, j'ai trouvé cette question forte intéressante :
Bonjour, Je doit calculer des données statistiques et la médiane est absente... Comment cela peut il être réalisé en une requête ?.

Comme ce problème s'avère en définitif assez complexe, le voici exprimés pour différents cas...

1) les données à calculées sont toutes différentes (il n'y a pas de doublon) et en nombre impaires :
Voici le jeu de données que j'ai utilisé pour ce cas :

CREATE TABLE T_STATISTIQUES_STT
(STT_ID INT,
STT_VALEUR FLOAT NOT NULL); INSERT INTO T_STATISTIQUES_STT VALUES (1, 22.0);
INSERT INTO T_STATISTIQUES_STT VALUES (2, 27.5);
INSERT INTO T_STATISTIQUES_STT VALUES (3, 22.5);
INSERT INTO T_STATISTIQUES_STT VALUES (4, 24.0);
INSERT INTO T_STATISTIQUES_STT VALUES (5, 23.0);

La solution étant :

STT_VALEUR
----------
23.0

2) les données à calculées sont toutes différentes (il n'y a pas de doublon) et en nombre paires :
Rajoutons au jeu précédent la ligne :

INSERT INTO T_STATISTIQUES_STT VALUES (6, 23.5);

La solution devient :

STT_VALEUR
----------
23.25

3) voyons ce qui se passe avec des valeurs identiques multiples et un nombre de lignes impair :
Rajoutons au jeu précédent les lignes :

INSERT INTO T_STATISTIQUES_STT VALUES (7, 22.0);
INSERT INTO T_STATISTIQUES_STT VALUES (8, 22.0);
INSERT INTO T_STATISTIQUES_STT VALUES (9, 22.0);
INSERT INTO T_STATISTIQUES_STT VALUES (10, 22.0);
INSERT INTO T_STATISTIQUES_STT VALUES (11, 22.0);

La solution devient :

STT_VALEUR
----------
22.0

C'est le genre de problème qui a passionné les spécialistes de SQL que sont Joe Celko et Chris Date. Sauriez vous au moins traiter le cas n° 1 ?

cliquez sur le mail pour répondre et titrez votre mail "Problème n°19 - mediane"

Problème n° 20 - Insertion en bloc

Voici un problème intéressant pour s'affanchir de certaines procédures stockées bêtes et méchante...Comment insérer de multiple ligne, par exemple dans une table de jointure, sans faire appel à une boucle et à l'aide d'une seule et unique requête ?

Notre problème est le suivant. Soit une table d'objets définie comme suit :

CREATE TABLE T_OBJET_OBJ
(OBJ_OBJET VARCHAR(16));

INSERT INTO T_OBJET_OBJ VALUES ('feu tricolore');
INSERT INTO T_OBJET_OBJ VALUES ('drapeau français');

... une table listant des couleurs :

CREATE TABLE T_COULEUR_CLR
(CLR_COULEUR VARCHAR(8));

INSERT INTO T_COULEUR_CLR VALUES ('blanc');
INSERT INTO T_COULEUR_CLR VALUES ('bleu');
INSERT INTO T_COULEUR_CLR VALUES ('vert');
INSERT INTO T_COULEUR_CLR VALUES ('rouge');
INSERT INTO T_COULEUR_CLR VALUES ('orange');

... et une table de jointure associant les deux, table actuellement vide :

CREATE TABLE T_OBJET_COULEUR_OBC
(OBJ_OBJET VARCHAR(16),
CLR_COULEUR VARCHAR(8));

Nous aimerions parvenir à remplir de la sorte la table d'association T_OBJET_COULEUR_OBC :

OBJ_OBJET        CLR_COULEUR
---------------- -----------
feu tricolore    vert
feu tricolore    rouge
feu tricolore    orange
drapeau français blanc
drapeau français bleu
drapeau français rouge

Sauriez vous trouver en une seule requête comment procéder à l'insertion massive de ces six lignes ?

cliquez sur le mail pour répondre et titrez votre mail "Problème n°20 - insertion en bloc"

CREATE TABLE T_PANEL_PNL (PNL_NOM VARCHAR(16), PNL_REPONSE1 INTEGER, PNL_REPONSE2 INTEGER, PNL_REPONSE3 INTEGER, PNL_REPONSE4 INTEGER, PNL_REPONSE5 INTEGER);	INSERT INTO T_PANEL_PNL VALUES ('Pierre', 1, 2, 3, 0, 0); INSERT INTO T_PANEL_PNL VALUES ('Paul', 1, 5, 3, 2, 1); INSERT INTO T_PANEL_PNL VALUES ('Jacques', 0, 3, 3, 2, 2);
CREATE TABLE T_REPONSES_RPS (RPS_REPONSE1 INTEGER, RPS_REPONSE2 INTEGER, RPS_REPONSE3 INTEGER, RPS_REPONSE4 INTEGER, RPS_REPONSE5 INTEGER);	INSERT INTO T_REPONSES_RPS VALUES (1, 4, 3, 2, 1);

-- contenu de la table	-- résultat attendu
SELECT TITRES FROM OUVRAGES	???
TITRE ---------------------------------- Le journal de Raymond Le vélo d'Alphonse Découvrir l'escalade L'âge du capitaine Comment dévisser la vis D'amour et d'eau fraiche	TITRE ---------------------------------- journal de Raymond (Le) vélo d'Alphonse (Le) Découvrir l'escalade âge du capitaine (L') Comment dévisser la vis amour et d'eau fraiche (D')