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.
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 6Notre 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 61) 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 123) 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 6A 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 :
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));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 |
Sélectionnez |
|
Sélectionnez |
Sélectionnez |
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]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.
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 ??
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 :
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
DufourA 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 :
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 2Auriez-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 6Observez 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 |
Sélectionnez |
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 :
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_USRUSR_ID USR_NOM
----------- ----------------
1 DUPONT
2 DURAND
3 DURAND1Par 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 :
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 !
|
Sélectionnez |
Sélectionnez |
|
Sélectionnez |
Sélectionnez |
|
Sélectionnez |
Sélectionnez |
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 :
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 :
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.1Simple ? 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 :
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 0Raisonnez à 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 :
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 1Aidez-vous d'une table des dates...
cliquez pour répondre et titrez votre mail "Problème n°30 - Nombre de jours contigus