IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

Le SQL de A à Z

Exercices et travaux pratiques


précédentsommairesuivant

IV. Exercices - 3° partie

IV-A. 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.

 
Sélectionnez
CREATE TABLE T_PAYS_PAY
(PAY_NOM      VARCHAR(16),
 PAY_POSITION INTEGER);
 
Sélectionnez
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);
 
Sélectionnez
SELECT * 
FROM   T_PAYS_PAY;
 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
DELETE FROM T_PAYS_PAY;
 
Sélectionnez
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);
 
Sélectionnez
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 :

 
Sélectionnez
PAY_NOM          PAY_POSITION 
---------------- ------------ 
Espagne          1
Grèce            2
Belgique         3
Croatie          4
Allemagne        5
France           6

A vos claviers...

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

IV-B. 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 :

 
Sélectionnez
CREATE TABLE T_PUBLIC_PBL
(PBL_ID  INTEGER,
 PBL_NOM VARCHAR(16)); 

CREATE TABLE T_PRESTATION_PST
(PST_ID      INTEGER,
 PST_LIBELLE VARCHAR(25),
 PST_PUBLIC  VARCHAR(32));
 
Sélectionnez
INSERT INTO T_PUBLIC_PBL VALUES (1, 'particulier');
INSERT INTO T_PUBLIC_PBL VALUES (3, 'entreprise');
INSERT INTO T_PUBLIC_PBL VALUES (2, 'groupe'); 

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 sportives en visant différents publics.
Les données sont donc les suivantes :

 
Sélectionnez
SELECT * FROM T_PUBLIC_PBL
 
Sélectionnez
SELECT * FROM T_PRESTATION_PST
 
Sélectionnez
PBL_ID      PBL_NOM          
----------- ---------------- 
1           particulier
3           entreprise
2           groupe
 
Sélectionnez
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 :

 
Sélectionnez
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]

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

IV-C. Problème n° 23 - Insertion conditionnelle

En voilà 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.

 
Sélectionnez
CREATE TABLE MATABLE 
( COL1 INTEGER,
  COL2 VARCHAR(16))  ;
 
Sélectionnez
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 ??

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

IV-D. 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 :

 
Sélectionnez
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)) ;
 
Sélectionnez
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 :

 
Sélectionnez
NOM              
----------------- 
IBM
 Durand
 Dupont
 Dubois
 Duval
EDF
 Dupond
 Duhamel
 Dufour

A vous de jouer !

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

IV-E. 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 :

 
Sélectionnez
CREATE TABLE LIGNE
(ID_LIGNE INTEGER, 
 ID_REF   INTEGER,
 QUANTITE INTEGER);
 
Sélectionnez
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 :

 
Sélectionnez
SELECT *
FROM   LIGNE
ORDER  BY ID_REF ;
 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
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
 
Sélectionnez
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...

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

IV-F. 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 :

 
Sélectionnez
CREATE TABLE T_UTILISATEUR_USR
(USR_ID    INTEGER NOT NULL PRIMARY KEY,
 USR_NOM   CHAR(16) NOT NULL UNIQUE);
 
Sélectionnez
INSERT INTO T_UTILISATEUR_USR VALUES (1, 'DUPONT');
INSERT INTO T_UTILISATEUR_USR VALUES (2, 'DURAND');
INSERT INTO T_UTILISATEUR_USR VALUES (3, 'DURAND1');
 
Sélectionnez
SELECT *
FROM   T_UTILISATEUR_USR
 
Sélectionnez
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 ?

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

IV-G. 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 :

 
Sélectionnez
CREATE TABLE T_CONTACT_CTC
(CTC_NOM   VARCHAR(32));
 
Sélectionnez
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');
 
Sélectionnez
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 !

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

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

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

IV-H. 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 :

 
Sélectionnez
Select adresseSource 
from Y 
where 10.120.12.1 <= adresseSource <= 10.130.23.1
  • 10.120.12.1 correspond à l'attribut DebutIPValide dans la table X
  • 10.130.23.1 correspond à l'attribut FinIPValide dans la table X adresseSource fait partie 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 :

 
Sélectionnez
CREATE TABLE TIP
(TIP_ADR VARCHAR(15));
 
Sélectionnez
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 :

 
Sélectionnez
TIP_ADR
-------------------
10.120.12.1
10.130.23.1

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

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

IV-I. 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 :

 
Sélectionnez
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));
 
Sélectionnez
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 :

 
Sélectionnez
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 à petits pas. Emboitez vos requêtes...

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

IV-J. 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 :

 
Sélectionnez
CREATE TABLE T_PLANNING_PNG 
(PNG_DATE DATE);
 
Sélectionnez
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 :

 
Sélectionnez
DateDebut       NbJours                                               
--------------- ------------- 
2004-01-01      3
2004-01-15      4
2004-01-30      1

Aidez-vous d'une table des dates...

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


précédentsommairesuivant

Copyright © SQLPro. Aucune reproduction, même partielle, ne peut être faite de ce site ni 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.