Le SQL de A à Z

Exercices et travaux pratiques


précédentsommairesuivant

II. Exercices - 1° partie

II-A. Problème n° 1 - un dans dix 

Notre premier problème est intitulé 'un dans dix' et il nous est posé par Joe CELKO...

Une table est composée de 11 colonnes comme suit :

Création de la table
Sélectionnez
CREATE TABLE T_CELKO_TEN_IN_ON_TIO  
(TIO_ID INTEGER NOT NULL,  
 TIO_1  INTEGER NOT NULL,  
 TIO_2  INTEGER NOT NULL,  
 TIO_3  INTEGER NOT NULL,  
 TIO_4  INTEGER NOT NULL,  
 TIO_5  INTEGER NOT NULL,  
 TIO_6  INTEGER NOT NULL,  
 TIO_7  INTEGER NOT NULL,  
 TIO_8  INTEGER NOT NULL,  
 TIO_9  INTEGER NOT NULL,  
 TIO_10 INTEGER NOT NULL); 
Insertion des données
Sélectionnez
INSERT INTO T_CELKO_TEN_IN_ON_TIO VALUES (1,   0,  1,  1,  0,  0,  0,  0,  0,  0,  0); 
INSERT INTO T_CELKO_TEN_IN_ON_TIO VALUES (2,   0,  0,  0,  0,  0,  0,  0,  1,  0,  0); 
INSERT INTO T_CELKO_TEN_IN_ON_TIO VALUES (3,   0,  1, -2,  3, -4,  5, -6,  7, -8,  5);
INSERT INTO T_CELKO_TEN_IN_ON_TIO VALUES (4,   0,  0,  0,  0,  0,  0,  0,  0,  9,  0);
INSERT INTO T_CELKO_TEN_IN_ON_TIO VALUES (5,   0,  0,  0,  0,  0, -1,  1,  0,  0,  0);
INSERT INTO T_CELKO_TEN_IN_ON_TIO VALUES (6,   0,  0,  0,  0,  0, -1,  1,  1,  0,  0);
INSERT INTO T_CELKO_TEN_IN_ON_TIO VALUES (7,   0,  1,  0,  1,  0,  0,  0,  0,  0,  1);
INSERT INTO T_CELKO_TEN_IN_ON_TIO VALUES (8,   1,  0,  0,  0,  0,  0,  0,  0,  0,  0);
INSERT INTO T_CELKO_TEN_IN_ON_TIO VALUES (9,   0,  0,  0, -1,  0,  0,  0,  0,  0,  0);
INSERT INTO T_CELKO_TEN_IN_ON_TIO VALUES (10,  0,  0,  0,  0,  0,  0,  0,  0,  0,  0);
INSERT INTO T_CELKO_TEN_IN_ON_TIO VALUES (11,  1,  1,  1,  1,  1,  1,  1,  1,  1,  1);
INSERT INTO T_CELKO_TEN_IN_ON_TIO VALUES (12,  1,  0,  0,  0,  0,  0,  0,  0,  0, -1); 
Données de la table
Sélectionnez
TIO_ID   TIO_1    TIO_2    TIO_3    TIO_4    TIO_5    TIO_6    TIO_7    TIO_8    TIO_9    TIO_10      
-------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- 
1        0        1        1        0        0        0        0        0        0        0
2        0        0        0        0        0        0        0        1        0        0
3        0        1        -2       3        -4       5        -6       7        -8       5
4        0        0        0        0        0        0        0        0        9        0
5        0        0        0        0        0        -1       1        0        0        0
6        0        0        0        0        0        -1       1        1        0        0
7        0        1        0        1        0        0        0        0        0        1
8        1        0        0        0        0        0        0        0        0        0
9        0        0        0        -1       0        0        0        0        0        0 
10       0        0        0        0        0        0        0        0        0        0
11       1        1        1        1        1        1        1        1        1        1
12       1        0        0        0        0        0        0        0        0        -1 

Il semble que Joe, le concepteur de cette base de données veuille stocker des tableaux d'entiers à 10 cellules. Le problème qui est soulevé, c'est que notre quidam veut obtenir en une seule requête :

  1. les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une,
  2. les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une valuée à un.

Les résultats attendus :

Les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une :
Sélectionnez
TIO_ID   TIO_1    TIO_2    TIO_3    TIO_4    TIO_5    TIO_6    TIO_7    TIO_8    TIO_9    TIO_10      
-------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- 
2        0        0        0        0        0        0        0        1        0        0
4        0        0        0        0        0        0        0        0        9        0
8        1        0        0        0        0        0        0        0        0        0
9        0        0        0        -1       0        0        0        0        0        0 
Les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une valuée à un :
Sélectionnez
TIO_ID   TIO_1    TIO_2    TIO_3    TIO_4    TIO_5    TIO_6    TIO_7    TIO_8    TIO_9    TIO_10      
-------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- 
2        0        0        0        0        0        0        0        1        0        0
8        1        0        0        0        0        0        0        0        0        0 

Il existe de multiples solutions à ce problème et j'en découvre une nouvelle tous les mois... A vous de jouer !

cliquez pour répondre et titrez votre mail "Problème n° 1 - un dans dix, ma solution"

II-B. Problème n°2 - le publipostage

Notre second problème est intitulé 'le publipostage' et il m'a été suggéré par Larry DiGiovanni...

Nous disposons d'une table contenant les noms et adresses de personnes (table PERSON) doté d'une colonne NOMBRE. Larry à besoin de reproduire certaines lignes plusieurs fois afin de faire des étiquettes de publipostage. Ainsi pour Monsieur MARTIN il désire 2 étiquettes, tandis que pour M. DUPOND il en faut trois. Ce nombre est saisie dans la table T_PERSONNE_PRS qui a la structure suivante :

 
Sélectionnez
CREATE TABLE T_PERSONNE_PRS 
(PRS_NOM    CHAR(16) NOT NULL, 
 PRS_VILLE  CHAR(16), 
 PRS_NOMBRE INTEGER); 
 
Sélectionnez
INSERT INTO T_PERSONNE_PRS VALUES ('MARTIN', 'PARIS', 3);
INSERT INTO T_PERSONNE_PRS VALUES ('DUPOND', 'STRASBOURG', 2); 
 
Sélectionnez
PRS_NOM    PRS_VILLE    PRS_NOMBRE  
---------- ------------ ----------- 
MARTIN     PARIS        3
DUPOND     STRASBOURG   2 

Le but étant de fournir une réponse dans laquelle chaque ligne de la table PERSON est recopié autant de fois que spécifié par la valeur de la colonne NOMBRE.

Comment faire, en une requête et une seule, pour obtenir la réponse :  

 
Sélectionnez
PRS_NOM          PRS_VILLE        
---------------- ---------------- 
MARTIN           PARIS           
MARTIN           PARIS           
MARTIN           PARIS           
DUPOND           STRASBOURG      
DUPOND           STRASBOURG   

Vous ne pouvez pas modifier la structure de la table ni modifier les données, mais vous avez le droit de vous aider en utilisant ou en créant d'autres éléments dans la base de données...

A vous de jouer !

cliquez pour répondre et titrez votre mail "Problème n°2 - le publipostage"

II-C. Problème n° 3 - la date an 2000

Ce troisième problème "intitulé date an 2000" est inspiré des petits tracas que l'arrivée de l'an 2000 a laissé à nos développeurs.

La table comptable ci dessous comporte une colonne AMT_FIN qui indique la date de fin d'amortissement de certaines références. Mais cette colonne est de type CHAR(6) et contient des données formatée de la façon suivante : AAMMJJ (années sur deux chiffres, mois sur deux chiffres, jour sur deux chiffres) afin de pouvoir trier facilement sur l'ordre alpha correspondant dans ce cas à l'ordre calendaire.

Notre développeur a décidé d'ajouter une nouvelle colonne 'AMT_FIN_Y2K' de type CHAR(10) cette fois, et aimerais votre aide pour rétablir toutes les anciennes dates au bon format. Aucune date n'est antérieure au 1/1/1960. Sauriez-vous l'aider ???
Je vous précise que la date doit être stockée dans cette base de données sous la forme AAAA-MM-JJ (format ISO).

Voici un extrait des données de cette table intitulé T_AMORTISSEMENT_AMT

 
Sélectionnez
CREATE TABLE T_AMORTISSEMENT_AMT
(AMT_FIN     CHAR(6), 
 AMT_FIN_Y2K CHAR(10)); 
 
Sélectionnez
INSERT INTO T_AMORTISSEMENT_AMT (AMT_FIN) VALUES ('990601');
INSERT INTO T_AMORTISSEMENT_AMT (AMT_FIN) VALUES ('970201'); 
INSERT INTO T_AMORTISSEMENT_AMT (AMT_FIN) VALUES ('021201');
INSERT INTO T_AMORTISSEMENT_AMT (AMT_FIN) VALUES ('941101'); 
INSERT INTO T_AMORTISSEMENT_AMT (AMT_FIN) VALUES ('920715'); 
 
Sélectionnez
AMT_FIN AMT_FIN_Y2K 
------- ---------- 
990601  NULL
970201  NULL
021201  NULL
941101  NULL
920715  NULL 

Et voici le résultat attendu :

 
Sélectionnez
AMT_FIN     AMT_FIN_Y2K 
---------   ------------- 
990601      06-01-1999 
970201      02-01-1997 
021201      12-01-2002 
941101      11-01-1994 
920715      07-15-1992 

cliquez pour répondre et titrez votre mail "Problème n°3 - la date an 2000"

II-D. Problème n° 4 - les chambres libres

Ce problème "chambres libres" m'a été inspiré par un internaute qui se posait la question suivante : "il me faut gerer les 350 jours de l'année pour des réservations hotelières. Par exemple, il faudrait pouvoir afficher les chambres libre du 22/03/2000 au 15/04/2000"

Notre internaute avait modélisé cela dans deux tables : la table T_CHAMBRE_CHB, contenant, entre autres, le n° des différentes chambres existantes et une table T_PLANNING_PLN contenant 3 colonnes, le numero de la chambre, la date et une colonne contenant une représentation booléenne pour indiquer que la chambre est libre ou non. L'hôtel de notre exemple comporte 4 chambres numérotés de 1 à 4, et la table planning ne contient que des lignes pour les chambres réservées ou occupées. En principe, les chambres libres à une date données ne sont pas représentées dans la table T_PLANNING_PLN, sauf si la colonne PLN_LIBRE est valuée à True.

 
Sélectionnez
CREATE TABLE T_CHAMBRE_CHB
(CHB_NUM INTEGER); 

CREATE TABLE T_PLANNING_PLN 
(PLN_JOUR  DATE, 
 CHB_NUM   INTEGER, 
 PLN_LIBRE CHAR(5)); 
 
Sélectionnez
INSERT INTO T_CHAMBRE_CHB VALUES (1) ;
INSERT INTO T_CHAMBRE_CHB VALUES (2) ;
INSERT INTO T_CHAMBRE_CHB VALUES (3) ;
INSERT INTO T_CHAMBRE_CHB VALUES (4) ; 

INSERT INTO T_PLANNING_PLN VALUES ('2000-01-12', 1, 'False') ;
INSERT INTO T_PLANNING_PLN VALUES ('2000-01-12', 2, 'False') ;
INSERT INTO T_PLANNING_PLN VALUES ('2000-01-13', 1, 'False') ;
INSERT INTO T_PLANNING_PLN VALUES ('2000-01-13', 2, 'False') ;
INSERT INTO T_PLANNING_PLN VALUES ('2000-01-13', 4, 'True' ) ; 

Sauriez-vous retrouver, à l'aide d'une requête SQL de votre cru,
1) les chambres qui sont libre pendant toute la période allant du 11 au 14 janvier 2000 ?
2) l'occupation des chambres pour la journée du 13 janvier 2000 ?
3) le planning des occupations pour toutes les chambres et toutes les dates du  11 au 14 janvier 2000 ?

Les réponses attendues sont les suivantes :

1) Chambres libres période du 11 au 14 janvier 2000
Sélectionnez
CHB_NUM     
----------- 
3
4 
2) Occupation au 13 janvier 2000
Sélectionnez
CHB_NUM     PLN_LIBRE 
----------- --------- 
1           False
2           False
3           True 
4           True 
3) Planning occupation des chambres du 11 au 14 janvier 2000 ?
Sélectionnez
CLD_JOUR      CHB_NUM     PLN_LIBRE 
------------- ----------- --------- 
2000-01-11    1           True 
2000-01-11    2           True 
2000-01-11    3           True 
2000-01-11    4           True 
2000-01-12    1           False
2000-01-12    2           False
2000-01-12    3           True 
2000-01-12    4           True 
2000-01-13    1           False
2000-01-13    2           False
2000-01-13    3           True 
2000-01-13    4           True 
2000-01-14    1           True 
2000-01-14    2           True 
2000-01-14    3           True 
2000-01-14    4           True 

cliquez pour répondre et titrez votre mail "Problème n°4 - les chambres libres"http://cerbermail.com/?98a8IH4SOe

II-E. Problème n° 5 - dates d'anniversaire

Ce problème "date d'anniversaire" est venu de mon patron, qui m'a dit un jour, comme ça, tout de go, "je voudrais envoyer une carte pour chacun de mes clients afin de lui souhiater un bon anniversaire..."
Fastoche, me suis-je dis. Hélas, j'ai du piannoter un bon moment avant d'arriver à trouver une solution honnête.
Sauriez vous trouver aussi bien, sinon mieux encore que ce que j'ai fait ???

Voici un exemple de la table de nos clients :

 
Sélectionnez
CREATE TABLE T_CLIENT_CLI
(CLI_ID             INTEGER,
 CLI_NOM            VARCHAR(16),
 CLI_DATE_NAISSANCE DATE); 
 
Sélectionnez
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'); 
 
Sélectionnez
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 problème est, par exemple, d'extraire les clients qui vont avoir leur anniversaire entre :
1) le 21 février et le 20 mars ;
2) le 21 décembre au 20 janvier ;
3) n'importe quelle fourchette de date (même à cheval sur deux années).

Les réponses attendues sont les suivantes :

1) Anniversaires entre le 21 février et le 20 mars
Sélectionnez
CLI_ID      CLI_NOM          CLI_DATE_NAISSANCE           
----------- ---------------- -----------
39          DOUBLET          1961-03-02 
40          MATHIEU          1940-02-24  
2) Anniversaires entre le 21 décembre et le 20 janvier
Sélectionnez
CLI_ID      CLI_NOM          CLI_DATE_NAISSANCE
----------- ---------------- ------------------
25          CHATON           1938-12-31
34          COULOMB          1963-01-01
41          MOURGUES         1953-01-14
44          ZAMPIERO         1945-01-19 

A vos requêtes...

cliquez pour répondre et titrez votre mail "Problème n° 5 - dates d'anniversaire"

II-F. Problème n° 6 - énumération

Pour cet exercice,  il s'agit d'alimenter les nombres de 0 à 9999 dans une table de nom T_ENTIER_ENT contenant un seul champ de type entier et de nom ENT_N. Au départ, cette table contient les nombres de 0 à 9.
J'ai intitulé ce problème, ' énumération' ...

La table T_ENTIER_NET est ainsi constituée :  

 
Sélectionnez
CREATE TABLE T_ENTIER_ENT 
(ENT_N INTEGER); 
 
Sélectionnez
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) ; 
 
Sélectionnez
ENT_N
------
0 
1 
2 
3 
4 
5 
6 
7 
8 
9 

Sauriez vous faire une telle requête avec un seul update ?

Voici un extrait de la table des données à insérer :

 
Sélectionnez
NOMBRE           
----------- 
10
11
12
13
14
...
9995
9996
9997
9998
9999  

cliquez pour répondre et titrez votre mail " Problème n° 6 - énumération "

II-G. Problème n° 7 - le comptage


Voici un problème qui m'a été soumis par un collègue. Je l'ai intitulé le comptage...
Comment faire en sorte que l'on puisse générer une colonne de comptage allant de 1 à n pour compter chaque occurence de la réponse ? C'est un problème récurent. Beaucoup de développeurs souhaitent numéroter les lignes d'une table réponse.

Dans notre exemple, mon collègue possède une table "T_PROSPECT_PSP", dans laquelle il y a des noms de personne (colonne PSP_NOM) et il voudrait faire en sorte que le résultat de sa requête numérote les lignes dans l'aordre alphabétique des noms :

 
Sélectionnez
CREATE TABLE T_PROSPECT_PSP 
(PSP_NOM VARCHAR(16)); 
 
Sélectionnez
INSERT INTO T_PROSPECT_PSP VALUES ('ARMAND');
INSERT INTO T_PROSPECT_PSP VALUES ('DUPONT');
INSERT INTO T_PROSPECT_PSP VALUES ('BAILLE');
INSERT INTO T_PROSPECT_PSP VALUES ('GAUTIER');
INSERT INTO T_PROSPECT_PSP VALUES ('MARTIN');
INSERT INTO T_PROSPECT_PSP VALUES ('CLAUDE');
INSERT INTO T_PROSPECT_PSP VALUES ('DUPONT'); 

Avez-vous une idée pour traiter ce problème ?

Voici le résultat attendu :

 
Sélectionnez
PSP_NOM          N           
---------------- ----------- 
BAILLE           1
CLAUDE           2
DUPONT           3
DUPONT           4
GAUTIER          5
MARTIN           6 

A vos requêtes !

cliquez pour répondre et titrez votre mail " Problème n° 7 - comptage "

II-H. Problème n° 8 - linéarisation

Nous apellerons ce problème " linéarisation "...

Opaz, un internaute me demande : " J'ai besoin d'un affichage en ligne plutot qu'en colonne.  J'ai trois tables différentes et je souhaite simplement afficher le nombre de lignes de chacunes...   Voici mes tables et les données ... "

 
Sélectionnez
CREATE TABLE TBL1  
(TBL_ID INTEGER); 

CREATE TABLE TBL2  
(TBL_ID INTEGER); 

CREATE TABLE TBL3  
(TBL_ID INTEGER); 
 
Sélectionnez
INSERT INTO TBL1 VALUES (1);
INSERT INTO TBL1 VALUES (3);
INSERT INTO TBL1 VALUES (5);
INSERT INTO TBL1 VALUES (7);
INSERT INTO TBL1 VALUES (9); 

INSERT INTO TBL2 VALUES (2);
INSERT INTO TBL2 VALUES (4);
INSERT INTO TBL2 VALUES (6);
INSERT INTO TBL2 VALUES (8); 

INSERT INTO TBL3 VALUES (1);
INSERT INTO TBL3 VALUES (2);
INSERT INTO TBL3 VALUES (3);
INSERT INTO TBL3 VALUES (5);
INSERT INTO TBL3 VALUES (7); 
Cette façon de présenter l'information ne lui va pas :
Sélectionnez
SELECT 'TBL1' AS "TABLE",  COUNT(*) AS NB 
FROM TBL1 
UNION ALL 
SELECT 'TBL2' AS "TABLE",  COUNT(*) AS NB 
FROM TBL2 
UNION ALL 
SELECT 'TBL3' AS "TABLE",  COUNT(*) AS NB 
FROM TBL3 
 
Sélectionnez
TABLE NB          
----- ----------- 
TBL1  5
TBL2  4
TBL3  5 

Il veut le résultat sous la forme suivante :

 
Sélectionnez
TBL1  TBL2   TBL3 
----- ------ ------ 
5     4      5  

Sauriez vous aider Opaz et présenter ce sésulta en une seule requête ???

cliquez pour répondre et titrez votre mail " Problème n°8 - linéarisation "

II-I. Problème n° 9 - les trous

Voici un problème envoyé par Guillaume, un internaute, je l'ai apellé " Les Trous " !

" J'ai une table dont la clef primaire est un entier (non auto incrémenté). Je cherche une requête qui me donnerai le premier entier disponible dans la séquence parmi ces clefs.  Par exemple si la table contient les enregistrements dont les clefs sont 1, 2, 3, 5, 6, 8, 9, 10, 11, 12, 14, 15 suite à divers ajouts et suppressions d'enregistrements, je veux que la requête me donne 4... ."

Pour simplifier la demande de Guillaume, voici la table qui constitue le coeur de cet exercice :

 
Sélectionnez
CREATE TABLE T_NUMERO_NMR 
(NMR INTEGER); 
 
Sélectionnez
INSERT INTO T_NUMERO_NMR VALUES (1); 
INSERT INTO T_NUMERO_NMR VALUES (2);
INSERT INTO T_NUMERO_NMR VALUES (3); 
INSERT INTO T_NUMERO_NMR VALUES (5); 
INSERT INTO T_NUMERO_NMR VALUES (6); 
INSERT INTO T_NUMERO_NMR VALUES (8); 
INSERT INTO T_NUMERO_NMR VALUES (9); 
INSERT INTO T_NUMERO_NMR VALUES (10); 
INSERT INTO T_NUMERO_NMR VALUES (11); 
INSERT INTO T_NUMERO_NMR VALUES (12); 
INSERT INTO T_NUMERO_NMR VALUES (14); 
INSERT INTO T_NUMERO_NMR VALUES (15); 

Je suppose que Guillaume veut gérer les trous de clefs générés par les suppressions d'enregistrements afin d'en récupérer la place... C'est en général une fort mauvaise idée.  Mais sauriez vous quand même dépanner Guillaume ? Donc récupérer tous les trous ?  En une seule requête évidemment !

Voici une image du résultat :

 
Sélectionnez
TROU
----------- 
4
7
13 

cliquez pour répondre et titrez votre mail " Problème n°9 - les trous "

II-J. Problème n° 10 - symétrie négative

C'est une collègue qui m'a posé ce problème ... que j'ai intitulé symétrie négative !

Dans une application comptable ma collègue avait besoin d'annuler logiquement des lignes de détail liée à une ligne maître, c'est à dire contrepasser une écriture. Comme un exemple vaut mieux qu'on long discours, nous prendrons une facture et les lignes afférentes à la facture.

 
Sélectionnez
CREATE TABLE T_FACTURE_FAC 
(FAC_NUM  INTEGER, 
 FAC_DATE DATE); 

CREATE TABLE T_LIGNE_FACTURE_LIF 
(LIF_NUM     INTEGER, 
 FAC_NUM     INTEGER, 
 LIF_ARTICLE VARCHAR(16), 
 LIF_MONTANT DECIMAL(16,2)); 
 
Sélectionnez
INSERT INTO T_FACTURE_FAC VALUES(56, '2001-08-11') ;
INSERT INTO T_FACTURE_FAC VALUES(79, '2001-08-17') ;
INSERT INTO T_FACTURE_FAC VALUES(101,'2001-09-01'); 

INSERT INTO T_LIGNE_FACTURE_LIF VALUES (11, 56, 'Cartable', 123.50) ;
INSERT INTO T_LIGNE_FACTURE_LIF VALUES (13, 56, 'Crayons', 17.52) ;
INSERT INTO T_LIGNE_FACTURE_LIF VALUES (15, 56, 'Trousse', 29.99) ;
INSERT INTO T_LIGNE_FACTURE_LIF VALUES (16, 56, 'Feutres', 11.25) ;
INSERT INTO T_LIGNE_FACTURE_LIF VALUES (18, 56, 'Ardoise', 44.21) ;
INSERT INTO T_LIGNE_FACTURE_LIF VALUES (19, 79, 'Cigarettes', 21.55) ;
INSERT INTO T_LIGNE_FACTURE_LIF VALUES (21, 79, 'Whisky', 147.65) ;
INSERT INTO T_LIGNE_FACTURE_LIF VALUES (22, 79, 'Petite pépé', 12857.59) ;
INSERT INTO T_LIGNE_FACTURE_LIF VALUES (27, 101, 'Savon', 16.24) ;
INSERT INTO T_LIGNE_FACTURE_LIF VALUES (28, 101, 'Serviette', 45.00) ;
INSERT INTO T_LIGNE_FACTURE_LIF VALUES (32, 101, 'Shampoing', 44.22) ;
INSERT INTO T_LIGNE_FACTURE_LIF VALUES (33, 101, 'Bain moussant', 118.40); 
 
Sélectionnez
FAC_NUM     FAC_DATE    
----------- ------------
56          2001-08-11 
79          2001-08-17 
101         2001-09-01 
 
Sélectionnez
LIF_NUM     FAC_NUM     LIF_ARTICLE      LIF_MONTANT        
----------- ----------- ---------------- ------------------ 
11          56          Cartable         123.50
13          56          Crayons          17.52
15          56          Trousse          29.99
16          56          Feutres          11.25
18          56          Ardoise          44.21
19          79          Cigarettes       21.55
21          79          Whisky           147.65
22          79          Petite pépé      12857.59
27          101         Savon            16.24
28          101         Serviette        45.00
32          101         Shampoing        44.22
33          101         Bain moussant    118.40 

Mais voilà... La facture n°79 (lignes en gras) doit être annulée et pour ce faire, on doit recopier les lignes de la table T_LIGNE_FACTURE_LIF correspondant à cette facture, tout en répondant à deux critères bien précis :
- la clef de ces nouvelles lignes doit être négative
- l'ordre de ces nouvelles lignes doit être le même que les lignes d'origine...
- les montants doivent être négatifs

Sauriez vous alimenter ces nouvelles lignes et extraire le résultat global comme ceci ?

 
Sélectionnez
FAC_NUM     LIF_ARTICLE      LIF_MONTANT        
----------- ---------------- ------------------ 
56          Cartable         123.50
56          Crayons          17.52
56          Trousse          29.99
56          Feutres          11.25
56          Ardoise          44.21
79          Cigarettes       21.55
79          Cigarettes       -21.55
79          Whisky           -147.65
79          Whisky           147.65
79          Petite pépé      12857.59
79          Petite pépé      -12857.59
101         Savon            16.24
101         Serviette        45.00
101         Shampoing        44.22
101         Bain moussant    118.40 

On constate que la clause de tri provoque l'apparition de la ligne de contrepassation d'écriture juste après la ligne de l'écriture comptable originale...

A vous de jouer !

cliquez pour répondre et titrez votre mail " Problème n°10 - symétrie négative "


précédentsommairesuivant

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

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