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
Microsoft Most Valuable Professional

LE SQL de A à Z

EXERCICES ET TRAVAUX PRATIQUES (3eme 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 21 à 30.

 

dernière mise à jour : 

Courriel
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 :

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 :

  1. la structure de la ou les tables sous la forme d'un ordre SQL CREATE TABLE...;
  2. les données à insérer sous la forme d'un jeu d'ordre SQL INSERT INTO...;
  3. le résultat attendu (lignes résultant de l'exécution de la requête solution);
  4. le niveau de difficulté noté avec des étoiles de 1 (facile) à 5 (très difficile);
  5. 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° 21 - Ordonner, réordonner !

Un problème d'ordonnancement avec SQL m'a été proposé par Mouse sur le forum SQL de developpez. Il s'agit de modifier une colonne d'une table assurant l'ordonnacement des données de la table.
Par exemple, la colonne position est numérotée de 1 à 5 et l'on désire remplacer la valeur 4
par la valeur 2 tout en préservant la continuité de l'ordre de 1 à 5...

Un petit exemple permettra de mieux comprendre la chose... Voici une table de pays. La colonne position indique la position que le pays obtiendra dans la liste une fois quelle sera triée.
CREATE TABLE T_PAYS_PAY
(PAY_NOM      VARCHAR(16),
 PAY_POSITION INTEGER);
 INSERT INTO T_PAYS_PAY VALUES ('Allemagne', 1);
INSERT INTO T_PAYS_PAY VALUES ('Belgique', 2);
INSERT INTO T_PAYS_PAY VALUES ('Croatie', 3);
INSERT INTO T_PAYS_PAY VALUES ('Espagne', 4);
INSERT INTO T_PAYS_PAY VALUES ('France', 5);
INSERT INTO T_PAYS_PAY VALUES ('Grèce', 6);
SELECT *
FROM   T_PAYS_PAY;		 PAY_NOM          PAY_POSITION
---------------- ------------
Allemagne        1
Belgique         2
Croatie          3
Espagne          4
France           5
Grèce            6

Notre utilisateur cherche à recombiner l'ordre de la liste en faisant passer la France en tête, sans pour autant bousculer l'ordre des autres pays. Tant est si bien que finalement, après cette requête de mise à jour, la table doit apparaître comme ceci :
PAY_NOM          PAY_POSITION
---------------- ------------
Allemagne        2
Belgique         3
Croatie          4
Espagne          5
France           1
Grèce            6

1) Sauriez vous faire cela en une seule requête ?

2) pourriez vous proposer une solution générique pour cette permutation d'ordre de tri ?

Nous allons corser le problème en admettant que la colonne PAY_POSITION puisse prendre des valeurs non continues :
Voici donc les nouvelles conditions du jeu d'essai :
DELETE FROM T_PAYS_PAY; INSERT INTO T_PAYS_PAY VALUES ('Allemagne', 11);
INSERT INTO T_PAYS_PAY VALUES ('Belgique', 8);
INSERT INTO T_PAYS_PAY VALUES ('Croatie', 9);
INSERT INTO T_PAYS_PAY VALUES ('Espagne', 5);
INSERT INTO T_PAYS_PAY VALUES ('France', 12);
INSERT INTO T_PAYS_PAY VALUES ('Grèce', 7);		 PAY_NOM          PAY_POSITION
---------------- ------------
Espagne          5
Grèce            7
Belgique         8
Croatie          9
Allemagne        11
France           12

3) Sauriez vous renuméroter les positions en continuité de 1 à n (n étant le nombre de lignes dans la table) quelque soit les données de la colonne position, mais en gardant toujours le même ordre... ? Et, en partant des données ci dessus, obtenir le résultat suivant :
PAY_NOM          PAY_POSITION
---------------- ------------
Espagne          1
Grèce            2
Belgique         3
Croatie          4
Allemagne        5
France           6

A vos claviers...

Courriel  cliquez sur le mail pour répondre et titrez votre mail "Problème n°21 - Ordonner, réordonner"

Problème n° 22 - Jointure hétérogène

Voici un internaute qui a hérité d'un modèle particulièrement mal modélisé. En effet, dans l'une des colonnes de la première table ("public") on trouve toutes les données relatives aux références de l'autres tables sous la forme suivante : [clef1].[clef2].[clef3]... 

Problème... comment réaliser une requête qui fait la jointure entre ces deux tables ?

Voici le jeu d'essais de notre internaute :
CREATE TABLE T_PUBLIC_PBL
(PBL_ID  INTEGER,
 PBL_NOM VARCHAR(16));		 INSERT INTO T_PUBLIC_PBL VALUES (1, 'particulier');
INSERT INTO T_PUBLIC_PBL VALUES (3, 'entreprise');
INSERT INTO T_PUBLIC_PBL VALUES (2, 'groupe');
CREATE TABLE T_PRESTATION_PST
(PST_ID      INTEGER,
 PST_LIBELLE VARCHAR(25),
 PST_PUBLIC  VARCHAR(32));
 INSERT INTO T_PRESTATION_PST VALUES (1, 'Tour en voiture',         '[3]');
INSERT INTO T_PRESTATION_PST VALUES (2, 'Compétition à plusieurs', '[3].[2]');
INSERT INTO T_PRESTATION_PST VALUES (3, 'Course d''endurance ',    '[1].[2]');
INSERT INTO T_PRESTATION_PST VALUES (4, 'Bapteme ',                '[1]');
INSERT INTO T_PRESTATION_PST VALUES (5, 'Course en tandem ',       '[2]');
Il modélise des prestations sportive en visant différents publics.
Les données sont donc les suivantes :
SELECT * FROM T_PUBLIC_PBL SELECT * FROM T_PRESTATION_PST
PBL_ID      PBL_NOM          
----------- ----------------
1           particulier
3           entreprise
2           groupe
 		 PST_ID      PST_LIBELLE               PST_PUBLIC                       
----------- ------------------------- --------------------------------
1           Tour en voiture           [3]
2           Compétition à plusieurs   [3].[2]
3           Course d'endurance        [1].[2]
4           Bapteme                   [1]
5           Course en tandem          [2]

Et notre internaute voudrait réaliser la jointure entre la colonne PBL_ID de la table T_PUBLIC_PBL et chacune des valeurs situées entre crochets de la colonne PST_PUBLIC de la table T_PRESTATION_PST., Bref, en une requête sauriez vous afficher les données comme ceci :
PBL_ID      PBL_NOM       PST_ID       PST_LIBELLE               PST_PUBLIC 
----------- ------------- ------------ ------------------------- ----------------
3           entreprise    1            Tour en voiture           [3]
2           groupe        2            Compétition à plusieurs   [3].[2]
3           entreprise    2            Compétition à plusieurs   [3].[2]
1           particulier   3            Course d'endurance        [1].[2]
2           groupe        3            Course d'endurance        [1].[2]
1           particulier   4            Bapteme                   [1]
2           groupe        5            Course en tandem          [2]

Courriel  cliquez sur le mail pour répondre et titrez votre mail "Problème n°22 - Jointure hétérogène"

Problème n° 23 - Insertion conditionnelle



En voila une question intéressante : comment insérer une ligne dans une table, uniquement si elle n'y est pas déjà ? Autrement dit il me faut une requête capable d'insérer la ligne si elle n'existe pas, sinon, de ne pas procéder à l'insertion...

Pour mieux comprendre la chose voici le modèle de données et les différents jeu d'essais.
CREATE TABLE MATABLE
( COL1 INTEGER,
  COL2 VARCHAR(16)) 		 INSERT INTO MATABLE VALUES (1, 'toto')
INSERT INTO MATABLE VALUES (1, 'titi')

Pourriez vous :
1) créer une requête INSERT dans cette table avec les valeurs 1, toto et faire en sorte que rien ne soit inséré sans générer d'erreur ?
2) insérer 3, tutu avec cette même requête ??

Courriel  cliquez sur le mail pour répondre et titrez votre mail "Problème n°23 - Insertion conditionnelle"

Problème n° 24 - Un arbre à deux niveaux

Posté par the tigrou...,dans le forum SQL de developpez :

Je dois écrire des requêtes qui font appel à des lignes appartenant à des tables differentes. Par exemple une table "liste entreprise" et une table "liste salarié". Comment selectionner les salariés de chaque entreprise de façon a faire une arboresence à deux niveaux ?

Pour vous aider, voici le jeu d'essais concocté par mes soins :
CREATE TABLE T_ENTREPRISE_ETP
(ETP_ID    INTEGER NOT NULL PRIMARY KEY,
 ETP_NOM   VARCHAR(16))
 CREATE TABLE T_EMPLOYEE_EMP
(EMP_ID    INTEGER NOT NULL PRIMARY KEY,
 EMP_NOM   VARCHAR(16),
 ETP_ID    INTEGER NOT NULL
               FOREIGN KEY REFERENCES T_ENTREPRISE_ETP (ETP_ID))
INSERT INTO T_ENTREPRISE_ETP
VALUES (1, 'IBM')
INSERT INTO T_ENTREPRISE_ETP
VALUES (2, 'EDF')		 INSERT INTO T_EMPLOYEE_EMP
VALUES (1, 'Durand', 1)
INSERT INTO T_EMPLOYEE_EMP
VALUES (2, 'Dupont', 1)
INSERT INTO T_EMPLOYEE_EMP
VALUES (3, 'Dubois', 1)
INSERT INTO T_EMPLOYEE_EMP
VALUES (4, 'Duval', 1)
INSERT INTO T_EMPLOYEE_EMP
VALUES (5, 'Dupond', 2)
INSERT INTO T_EMPLOYEE_EMP
VALUES (6, 'Duhamel', 2)
INSERT INTO T_EMPLOYEE_EMP
VALUES (7, 'Dufour', 2)

Ce qui Tigrou voudrait, doit se présenter comme ceci  :
NOM              
-----------------
IBM
 Durand
 Dupont
 Dubois
 Duval
EDF
 Dupond
 Duhamel
 Dufour

A vous de jouer !

Courriel  cliquez sur le mail pour répondre et titrez votre mail "Problème n°24 - Arbre à deux niveaux"


Problème n° 25 - Éclater des lignes

 

C'est un de mes clients qui m'a mis au défi de trouver comment réaliser cette transformation...
Partant d'une table de "LIGNE" ainsi modélisée et des données suivantes :

CREATE TABLE LIGNE
(ID_LIGNE INTEGER,
 ID_REF   INTEGER,
 QUANTITE INTEGER);		 INSERT INTO LIGNE (ID_LIGNE, ID_REF, QUANTITE) VALUES (1, 1, 4);
INSERT INTO LIGNE (ID_LIGNE, ID_REF, QUANTITE) VALUES (2, 2, 1);
INSERT INTO LIGNE (ID_LIGNE, ID_REF, QUANTITE) VALUES (3, 3, 2);
INSERT INTO LIGNE (ID_LIGNE, ID_REF, QUANTITE) VALUES (4, 1, 1);
INSERT INTO LIGNE (ID_LIGNE, ID_REF, QUANTITE) VALUES (5, 2, 2);
INSERT INTO LIGNE (ID_LIGNE, ID_REF, QUANTITE) VALUES (6, 4, 2);

Que l'on présentera mieux sous sa forme extraite :
SELECT *
FROM   LIGNE
ORDER  BY ID_REF
 ID_LIGNE    ID_REF      QUANTITE   
----------- ----------- -----------
1           1           4
4           1           1
5           2           2
2           2           1
3           3           2
6           4           2

Auriez vous l'amabilité de produire la table suivante :

ID_ARTICLE  ID_REF      SERIE       ID_LIGNE    
----------- ----------- ----------- -----------
1           1           1           1
2           1           2           1
3           1           3           1
4           1           4           1
5           1           5           4
6           2           1           2
7           2           2           5
8           2           3           5
9           3           1           3
10          3           2           3
11          4           1           6
12          4           2           6

Observez bien la manière dont est construite cette table réponse : SERIE est un incrément dépendant uniquement de ID_REF, avec comme particularité qu'il doit éclater et numéroter à l'intérieur d'un même ID_REF et dans l'ordre ID_LIGNE, des lignes dont le nombre figure dans QUANTITE.
Autrement dit si pour ID_REF = 1, QUANTITE = 4, il y aura 4 lignes avec ce même ID_REF. Si pour ce même ID_REF de 1 il y a une ID_LIGNE différente, alors la numérotation doit de poursuivre en séquence...

Voici une présentation qui met en correspondance les lignes de la table sources avec la table réponse :

ID_REF      ID_LIGNE    QUANTITE    
----------- ----------- -----------
1           1           4
1           1           4
1           1           4
1           1           4
1           4           1
2           2           1
2           5           2
2           5           2
3           3           2
3           3           2
4           6           2
4           6           2
 ID_REF      ID_LIGNE    SERIE       ID_ARTICLE 
----------- ----------- ----------- -----------
1           1           1           1
1           1           2           2
1           1           3           3
1           1           4           4
1           4           5           5
2           2           1           6
2           5           2           7
2           5           3           8
3           3           1           9
3           3           2           10
4           6           1           11
4           6           2           12

Les lignes grise représentent des doublons de la ligne précédente. D'ou lidée d'éclater chaque ligne en autant de fois que l'indique la valeur de QUANTITE...

Courriel  cliquez sur le mail pour répondre et titrez votre mail "Problème n°25 - Éclater des lignes"

Problème n° 26 - Noms incrémentés

 

Dans un site Web un problème récurent vient des noms qui peuvent être homonymes. Pour lever toute ambiguité, notre quidam veut qu'en cas d'insertion le nom dont la colonne est muni d'une clause d'unicité ne soit par rejeté, mais qu'il lui soit ajouté un numéro calculé automatiquement en séquence...

Voici le modèle de table et les données de notre quidam :

CREATE TABLE T_UTILISATEUR_USR
(USR_ID    INTEGER NOT NULL PRIMARY KEY,
 USR_NOM   CHAR(16) NOT NULL UNIQUE);
 INSERT INTO T_UTILISATEUR_USR VALUES (1, 'DUPONT');
INSERT INTO T_UTILISATEUR_USR VALUES (2, 'DURAND');
INSERT INTO T_UTILISATEUR_USR VALUES (3, 'DURAND1');

SELECT *
FROM   T_UTILISATEUR_USR 		USR_ID      USR_NOM         
----------- ----------------
1           DUPONT         
2           DURAND         
3           DURAND1

Par exemple; si je tente de rentrer DUPOND, il doit insérer DUPOND. Si je tente de rentrer DUPONT, il doit insérer DUPONT1. Si je tente de rentrer DURAND, il doit insérer DURAND2...

Comment faire cela en une seule requête ?

Courriel  cliquez sur le mail pour répondre et titrez votre mail "Problème n°26 - Noms incrémentés"

Problème n° 27 - Une lettre un nom

 

Notre internaute recherche à construire une requete sql qui me permette d'extraire
d'une table la première entrée de chaque lettre alphabétique, donc de a à z, par rapport à une colonne contenant le nom d'une personne.

Par exemple, avec la table T_CONTACT_CTC contenant une colonne CTC_NOM avec les valeur suivantes :(Aaron, Abel, Babe, Boudet, Cabi...), il voudrait obtenir la

première personne ayant l'initiale A, la première personne ayant l'initiale B, etc... jusqu'à Z.

Voici la table pour ce faire :

CREATE TABLE T_CONTACT_CTC
(CTC_NOM   VARCHAR(32));
 INSERT INTO T_CONTACT_CTC VALUES ('Aaron');
INSERT INTO T_CONTACT_CTC VALUES ('Abel');
INSERT INTO T_CONTACT_CTC VALUES ('Babet');
INSERT INTO T_CONTACT_CTC VALUES ('Boudet');
INSERT INTO T_CONTACT_CTC VALUES ('Brouard');
INSERT INTO T_CONTACT_CTC VALUES ('Cabu');
INSERT INTO T_CONTACT_CTC VALUES ('Cortès');
INSERT INTO T_CONTACT_CTC VALUES ('Cardeau');

INSERT INTO T_CONTACT_CTC VALUES ('Zoltan');

LETTRE    CTC_NOM
--------- -------------------------
A         Aaron
B         Babet
C         Cabu
...
Z         Zoltan

Mais n'ayons pas peur d'aller plus loin en posant quelques questions supplémentaires...
variante 1 : obtenir les noms des "secondes" personnes
variante 2 : obtenir les noms des niemes personnes
variante 3 : obtenir les noms des niemes personnes, mais si elle n'existe pas, alors la dernière !


variante 1 :
obtenir les noms des "secondes" personnes		 LETTRE CTC_NOM                         
------ --------------------------------
A      Aaron
B      Babet
C      Cabu
Z      Zoltan
variante 2 : (n = 3)
 LETTRE CTC_NOM                         
------ --------------------------------
B      Brouard
C      Cortès
variante 3 : (n = 2)
obtenir les noms des niemes personnes, mais si elle n'existe pas, alors la dernière ! 		LETTRE CTC_NOM                         
------ --------------------------------
A      Abel
B      Boudet
C      Cardeau
Z      Zoltan


Comment faire cela et toujours en une seule requête ?

Courriel  cliquez sur le mail pour répondre et titrez votre mail "Problème n°27 - Une lettre un nom"


Problème n° 28 - Filtrer les adresses IP

 

Pêché sur le forum SQL de www.developpez.com :

Bonjour à tous,
je travaille sur des adresses IP et je n'arrive pas a faire une selection comme ceci dans une requête sql :

Select adresseSource
from Y
where 10.120.12.1 <= adresseSource <= 10.130.23.1

- 10.120.12.1 correspont à l'attribut DebutIPValide dans la table X
- 10.130.23.1 correspond à l'attribut FinIPValide dans la table X
adresseSource fait parti de la table Y

Merci pour vos suggestions

Sauriez vous l'aider ?
Voici la table des adresses IP et quelques lignes bien suffisantes pour tester votre travail :

CREATE TABLE TIP
(TIP_ADR VARCHAR(15));

 INSERT INTO TIP VALUES ('10.120.12.1');
INSERT INTO TIP VALUES ('10.130.23.1');
INSERT INTO TIP VALUES ('10.130.201.1');
INSERT INTO TIP VALUES ('10.13.11.1');

Le résultat de votre requête doit donner :

TIP_ADR
-------------------
10.120.12.1
10.130.23.1

Simple ? Pas sûr !  A vos code...


Courriel  cliquez sur le mail pour répondre et titrez votre mail "Problème n°28 - Filtrer les adresses IP"



Problème n° 29 - Calculer l'adresses IP suivante

 

L'exercice précédent m'a donné l'idée d'un exercice complémentaire...
Partant d'une table de machines ayant des adresses IP, cette fois ci bien modélisées, comment trouver l'adresse IP suivante pour insérer une nouvelle machine ?

Voici la nouvelle table des machines avec leur adresse IP et quelques lignes de test :

CREATE TABLE T_MACHINE_MAC
(MAC_ID     INT NOT NULL PRIMARY KEY,
 MAC_NOM    VARCHAR(16),
 MAC_ADRIP1 SMALLINT CHECK(MAC_ADRIP1 BETWEEN 0 AND 255),
 MAC_ADRIP2 SMALLINT CHECK(MAC_ADRIP2 BETWEEN 0 AND 255),
 MAC_ADRIP3 SMALLINT CHECK(MAC_ADRIP3 BETWEEN 0 AND 255),
 MAC_ADRIP4 SMALLINT CHECK(MAC_ADRIP4 BETWEEN 0 AND 255)
 CONSTRAINT CU_ADRIP UNIQUE (MAC_ADRIP1, MAC_ADRIP2, MAC_ADRIP3, MAC_ADRIP4));
 INSERT INTO T_MACHINE_MAC VALUES (1, 'PC', 123,  12,   1, 200);
INSERT INTO T_MACHINE_MAC VALUES (2, 'PC', 123,  12,   1, 255);
INSERT INTO T_MACHINE_MAC VALUES (3, 'PC', 123,  12, 255, 255);
INSERT INTO T_MACHINE_MAC VALUES (4, 'PC', 123,  13,   0,   0);
INSERT INTO T_MACHINE_MAC VALUES (5, 'PC', 123, 255, 255, 255);

Le résultat de votre requête doit être :

MAC_ID      MAC_NOM          NEW_ADRIP1  NEW_ADRIP2  NEW_ADRIP3  NEW_ADRIP4 
----------- ---------------- ----------- ----------- ----------- -----------
1           PC               123         12          1           201
2           PC               123         12          2           0
3           PC               123         13          0           1
4           PC               123         13          0           1
5           PC               124         0           0           0

Raisonnez à paetit pas. Emboitez vos requêtes...


Courriel  cliquez sur le mail pour répondre et titrez votre mail "Problème n°29 - Calculer l'adresse IP suivante

Problème n° 30 - Nombre de jours contigus

 

Pas facile, la question de Stéphane T...
Y a t il une formule magique qui permet de compter des dates consécutives en sql ?

Comme vous le savez certainement déjà, cette formule magique est une requête !

Voici les données qui nous servirons de test et la structure de la table associée :

CREATE TABLE T_PLANNING_PNG
(PNG_DATE DATE); 		INSERT INTO T_PLANNING_PNG VALUES ('2004-01-01');
INSERT INTO T_PLANNING_PNG VALUES ('2004-01-02');
INSERT INTO T_PLANNING_PNG VALUES ('2004-01-03');
INSERT INTO T_PLANNING_PNG VALUES ('2004-01-15');
INSERT INTO T_PLANNING_PNG VALUES ('2004-01-16');
INSERT INTO T_PLANNING_PNG VALUES ('2004-01-17');
INSERT INTO T_PLANNING_PNG VALUES ('2004-01-18');
INSERT INTO T_PLANNING_PNG VALUES ('2004-01-30');

Le résultat de votre requête doit être :

DateDebut       NbJours                                               
--------------- -------------
2004-01-01      3
2004-01-15      4
2004-01-30      1 

Aidez vous d'une table des dates...


Courriel  cliquez sur le mail pour répondre et titrez votre mail "Problème n°30 - Nombre de jours contigus