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 (1ere 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 1 à 10.


 

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° 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 :
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
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 :
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 :
-- 1) les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une :

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
-- 2) les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une valuée à un :

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 ! 

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

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 :
CREATE TABLE T_PERSONNE_PRS
(PRS_NOM    CHAR(16) NOT NULL,
 PRS_VILLE  CHAR(16),
 PRS_NOMBRE INTEGER);		 INSERT INTO T_PERSONNE_PRS VALUES ('MARTIN', 'PARIS', 3);
INSERT INTO T_PERSONNE_PRS VALUES ('DUPOND', 'STRASBOURG', 2);		 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 :  
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 !

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

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
 
CREATE TABLE T_AMORTISSEMENT_AMT
(AMT_FIN     CHAR(6),
 AMT_FIN_Y2K CHAR(10));		 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');		 AMT_FIN AMT_FIN_Y2K
------- ----------
990601  NULL
970201  NULL
021201  NULL
941101  NULL
920715  NULL

Et voici le résultat attendu :
 
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

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

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.

CREATE TABLE T_CHAMBRE_CHB
(CHB_NUM INTEGER);		 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) ;
CREATE TABLE T_PLANNING_PLN
(PLN_JOUR  DATE,
 CHB_NUM   INTEGER,
 PLN_LIBRE CHAR(5));		 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

CHB_NUM     
-----------
3
4
 -- 2) occupation au
      13 janvier 2000

CHB_NUM     PLN_LIBRE
----------- ---------
1           False
2           False
3           True
4           True
-- 3) planning occupation des chambres
      du 11 au 14 janvier 2000 ?

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

Courriel   cliquez sur le mail pour répondre et titrez votre mail "Problème n°4 - les chambres libres"

 

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

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

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

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

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

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

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

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

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

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 :
 
CREATE TABLE T_PROSPECT_PSP
(PSP_NOM VARCHAR(16));		 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 :

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


A vos requêtes !

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

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 ..."
CREATE TABLE TBL1 
(TBL_ID INTEGER);		 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);
CREATE TABLE TBL2 
(TBL_ID INTEGER);		 INSERT INTO TBL2 VALUES (2);
INSERT INTO TBL2 VALUES (4);
INSERT INTO TBL2 VALUES (6);
INSERT INTO TBL2 VALUES (8);
CREATE TABLE TBL3 
(TBL_ID INTEGER);		 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 :
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
TABLE NB         
----- -----------
TBL1  5
TBL2  4
TBL3  5

Il veut le résultat sous la forme suivante :
TBL1  TBL2   TBL3
----- ------ ------
5     4      5 

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

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

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 :
CREATE TABLE T_NUMERO_NMR
(NMR INTEGER);		 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 :
TROU
-----------
4
7
13

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

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.
 
CREATE TABLE T_FACTURE_FAC
(FAC_NUM  INTEGER,
 FAC_DATE DATE);		 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');
CREATE TABLE T_LIGNE_FACTURE_LIF
(LIF_NUM     INTEGER,
 FAC_NUM     INTEGER,
 LIF_ARTICLE VARCHAR(16),
 LIF_MONTANT DECIMAL(16,2));		 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);
FAC_NUM     FAC_DATE   
----------- ------------
56          2001-08-11
79          2001-08-17
101         2001-09-01
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 ?
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 !
 

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