solutions - laboratoire d'informatique de paris 6 · solutions des exercices pratiques –...

Solutions des exercices pratiques – Synthex SQL, deuxième édition 1

© Pearson Education France

Solutions

TP 1 Un dans dix

Établissez la requête pour obtenir : a) Les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une.

Solution 1

Créez une vue pour « coloniser » les dix valeurs :

CREATE VIEW V_CELKO_TEN_IN_ON_TIO AS SELECT TIO_ID, TIO_1 AS TIO_N FROM T_CELKO_TEN_IN_ON_TIO UNION ALL SELECT TIO_ID, TIO_2 AS TIO_N FROM T_CELKO_TEN_IN_ON_TIO UNION ALL SELECT TIO_ID, TIO_3 AS TIO_N FROM T_CELKO_TEN_IN_ON_TIO UNION ALL SELECT TIO_ID, TIO_4 AS TIO_N FROM T_CELKO_TEN_IN_ON_TIO UNION ALL SELECT TIO_ID, TIO_5 AS TIO_N FROM T_CELKO_TEN_IN_ON_TIO UNION ALL SELECT TIO_ID, TIO_6 AS TIO_N FROM T_CELKO_TEN_IN_ON_TIO UNION ALL SELECT TIO_ID, TIO_7 AS TIO_N FROM T_CELKO_TEN_IN_ON_TIO UNION ALL SELECT TIO_ID, TIO_8 AS TIO_N FROM T_CELKO_TEN_IN_ON_TIO UNION ALL



SELECT TIO_ID, TIO_9 AS TIO_N FROM T_CELKO_TEN_IN_ON_TIO UNION ALL SELECT TIO_ID, TIO_10 AS TIO_N FROM T_CELKO_TEN_IN_ON_TIO

Dès lors la requête devient simple :

SELECT T.* FROM T_CELKO_TEN_IN_ON_TIO T INNER JOIN V_CELKO_TEN_IN_ON_TIO V ON T.TIO_ID = V.TIO_ID WHERE V.TIO_N = 0 GROUP BY T.TIO_ID, T.TIO_1, T.TIO_2, T.TIO_3, T.TIO_4, T.TIO_5, T.TIO_6, T.TIO_7, T.TIO_8, T.TIO_9, T.TIO_10 HAVING COUNT(*) = 9

Solution 2

Utilisez le Row Value Constructor pour globaliser la comparaison :

SELECT * FROM T_CELKO_TEN_IN_ON_TIO WHERE (TIO_1, TIO_2, TIO_3, TIO_4, TIO_5, TIO_6, TIO_7, TIO_8, TIO_9, TIO_10) IN ((TIO_1, 0, 0, 0, 0, 0, 0, 0, 0, 0), (0, TIO_2, 0, 0, 0, 0, 0, 0, 0, 0), (0, 0, TIO_3, 0, 0, 0, 0, 0, 0, 0), (0, 0, 0, TIO_4, 0, 0, 0, 0, 0, 0), (0, 0, 0, 0, TIO_5, 0, 0, 0, 0, 0), (0, 0, 0, 0, 0, TIO_6, 0, 0, 0, 0), (0, 0, 0, 0, 0, 0, TIO_7, 0, 0, 0), (0, 0, 0, 0, 0, 0, 0, TIO_8, 0, 0), (0, 0, 0, 0, 0, 0, 0, 0, TIO_9, 0), (0, 0, 0, 0, 0, 0, 0, 0, 0, TIO_10))

Solution 3 Certains SGBDR possèdent la fonction SIGN qui renvoie – 1, 0 ou 1 si un nombre est respectivement négatif, zéro ou positif. Combiné à la fonction SQL ABS (valeur absolue), vous pouvez écrire :

SELECT * FROM T_CELKO_TEN_IN_ON_TIO



WHERE SIGN(ABS(TIO_1)) + SIGN(ABS(TIO_2)) + SIGN(ABS(TIO_3)) + SIGN(ABS(TIO_4)) + SIGN(ABS(TIO_5)) + SIGN(ABS(TIO_6)) + SIGN(ABS(TIO_7)) + SIGN(ABS(TIO_8)) + SIGN(ABS(TIO_9)) + SIGN(ABS(TIO_10)) = 1

Si la fonction SIGN n’est pas présente dans votre SGBDR, vous pouvez utiliser la structure CASE :

SELECT * FROM T_CELKO_TEN_IN_ON_TIO WHERE CASE WHEN ABS(TIO_1) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_2) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_3) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_4) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_5) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_6) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_7) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_8) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_9 )> 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_10) > 0 THEN 1 ELSE 0 END = 1

Solution 4 Petit truc mathématique : si une et seulement une colonne est différente de zéro, alors il y a forcément une collection de 9 colonnes qui possèdent la valeur zéro.

SELECT * FROM T_CELKO_TEN_IN_ON_TIO WHERE 0 IN (ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9), ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_10), ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_9) + ABS(TIO_10), ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10), ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10), ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_5) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10), ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10), ABS(TIO_1) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6)



+ ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10), ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10)) AND ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10) <> 0

Solution 5

On peut utiliser la structure CASE d’une manière plus subtile :

SELECT * FROM T_CELKO_TEN_IN_ON_TIO WHERE CASE WHEN TIO_1 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_2 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_3 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_4 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_5 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_6 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_7 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_8 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_9 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_10 = 0 THEN 1 ELSE 0 END = 9

C’est-à-dire en comptabilisant les zéros.

Solution 6

Certaines propriétés mathématiques peuvent être utiles. Par exemple, la multiplication des valeurs + 1 de toutes les colonnes doit être égale à l’addition de toutes les valeurs de colonnes à condition que la somme des colonnes soit différente de zéro :

SELECT * FROM T_CELKO_TEN_IN_ON_TIO WHERE (TIO_1 + 1) * (TIO_2 + 1) * (TIO_3 + 1) * (TIO_4 + 1) * (TIO_5 + 1) * (TIO_6 + 1) * (TIO_7 + 1) * (TIO_8 + 1) * (TIO_9 + 1) * (TIO_10 + 1) = TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 + 1 AND TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 <> 0

Bien entendu cela ne fonctionne que si les nombres ne sont que des entiers.



b) Les lignes de la table dont toutes les cellules f1 à f10 sont à zéro sauf une valuée à un.

Reprenons les requêtes solutions déjà vues et essayons de les amender pour obtenir la solution.

Solution 1 Par rapport à notre vue, on obtient le résultat en déplaçant le filtre WHERE dans un HAVING combiné à CASE pour comptabiliser les zéros associés à une somme qui doit être valuée à 1 :

SELECT T.* FROM T_CELKO_TEN_IN_ON_TIO T INNER JOIN V_CELKO_TEN_IN_ON_TIO V ON T.TIO_ID = V.TIO_ID GROUP BY T.TIO_ID, T.TIO_1, T.TIO_2, T.TIO_3, T.TIO_4, T.TIO_5, T.TIO_6, T.TIO_7, T.TIO_8, T.TIO_9, T.TIO_10 HAVING SUM(CASE WHEN V.TIO_N = 0 THEN 1 ELSE 0 END) = 9 AND SUM(V.TIO_N) = 1

Solution 2

Avec le Row Value Constructor, c’est encore plus simple :

SELECT * FROM T_CELKO_TEN_IN_ON_TIO WHERE (TIO_1, TIO_2, TIO_3, TIO_4, TIO_5, TIO_6, TIO_7, TIO_8, TIO_9, TIO_10) IN ((1, 0, 0, 0, 0, 0, 0, 0, 0, 0), (0, 1, 0, 0, 0, 0, 0, 0, 0, 0), (0, 0, 1, 0, 0, 0, 0, 0, 0, 0), (0, 0, 0, 1, 0, 0, 0, 0, 0, 0), (0, 0, 0, 0, 1, 0, 0, 0, 0, 0), (0, 0, 0, 0, 0, 1, 0, 0, 0, 0), (0, 0, 0, 0, 0, 0, 1, 0, 0, 0), (0, 0, 0, 0, 0, 0, 0, 1, 0, 0), (0, 0, 0, 0, 0, 0, 0, 0, 1, 0), (0, 0, 0, 0, 0, 0, 0, 0, 0, 1))

Solution 3 Avec SIGN ou CASE, il suffit d’ajouter une condition supplémentaire :



SELECT * FROM T_CELKO_TEN_IN_ON_TIO WHERE SIGN(ABS(TIO_1)) + SIGN(ABS(TIO_2)) + SIGN(ABS(TIO_3)) + SIGN(ABS(TIO_4)) + SIGN(ABS(TIO_5)) + SIGN(ABS(TIO_6)) + SIGN(ABS(TIO_7)) + SIGN(ABS(TIO_8)) + SIGN(ABS(TIO_9)) + SIGN(ABS(TIO_10)) = 1 AND TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 = 1

SELECT * FROM T_CELKO_TEN_IN_ON_TIO WHERE CASE WHEN ABS(TIO_1) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_2) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_3) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_4) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_5) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_6) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_7) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_8) > 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_9 )> 0 THEN 1 ELSE 0 END + CASE WHEN ABS(TIO_10) > 0 THEN 1 ELSE 0 END = 1 AND TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 = 1

Solution 4 Il suffit de modifier la dernière clause :

SELECT * FROM T_CELKO_TEN_IN_ON_TIO WHERE 0 IN (ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9), ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_10), ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_9) + ABS(TIO_10), ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10), ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10), ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_5) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10), ABS(TIO_1) + ABS(TIO_2) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10), ABS(TIO_1) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10), ABS(TIO_2) + ABS(TIO_3) + ABS(TIO_4) + ABS(TIO_5) + ABS(TIO_6) + ABS(TIO_7) + ABS(TIO_8) + ABS(TIO_9) + ABS(TIO_10))



AND TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 = 1

Solution 5

Là encore, le simple ajout d’un prédicat complémentaire suffit :

SELECT * FROM T_CELKO_TEN_IN_ON_TIO WHERE CASE WHEN TIO_1 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_2 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_3 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_4 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_5 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_6 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_7 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_8 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_9 = 0 THEN 1 ELSE 0 END + CASE WHEN TIO_10 = 0 THEN 1 ELSE 0 END = 9 AND TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 = 1

Solution 6 Pour la solution 6, une modification mineure suffit :

SELECT * FROM T_CELKO_TEN_IN_ON_TIO WHERE (TIO_1 + 1) * (TIO_2 + 1) * (TIO_3 + 1) * (TIO_4 + 1) * (TIO_5 + 1) * (TIO_6 + 1) * (TIO_7 + 1) * (TIO_8 + 1) * (TIO_9 + 1) * (TIO_10 + 1) = TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 + 1 AND TIO_1 + TIO_2 + TIO_3 + TIO_4 + TIO_5 + TIO_6 + TIO_7 + TIO_8 + TIO_9 + TIO_10 = 1

TP 2 Le publipostage

Élaborez une requête, et une seule.

Il suffit de créer une table dotée d’une unique colonne contenant la séquence continue des nombres de 1 à



n, n étant une valeur égale ou supérieure à la colonne PRS_NOMBRE :

CREATE TABLE T_ENUMERE_NMR (NMR_NOMBRE INTEGER); INSERT INTO T_ENUMERE_NMR VALUES (1); INSERT INTO T_ENUMERE_NMR VALUES (2); INSERT INTO T_ENUMERE_NMR VALUES (3); INSERT INTO T_ENUMERE_NMR VALUES (4); INSERT INTO T_ENUMERE_NMR VALUES (5); INSERT INTO T_ENUMERE_NMR VALUES (6); INSERT INTO T_ENUMERE_NMR VALUES (7); INSERT INTO T_ENUMERE_NMR VALUES (8); … INSERT INTO T_ENUMERE_NMR VALUES (99); …

Dès lors la requête devient basique avec une non-équijointure :

SELECT PRS_NOM, PRS_VILLE FROM T_PERSONNE_PRS PRS INNER JOIN T_ENUMERE_NMR NMR ON PRS.PRS_NOMBRE >= NMR.NMR_NOMBRE

TP 3 La date an 2000

Notre développeur a décidé d’ajouter une nouvelle colonne 'AMT_FIN_Y2K' de type CHAR(10) et aimerait votre aide pour rétablir toutes les anciennes dates au bon format. Aucune date n’est antérieure au 1/1/1960. Comment peut-on l’aider ?

Solution 1

En utilisant les propriétés de la division entière, on peut ajouter 100 ans lorsque la division par 60 donne une valeur supérieure à zéro. Ainsi la requête :

SELECT *, CAST(SUBSTRING(AMT_FIN FROM 1 FOR 2)AS INTEGER) + 2000 — 100 * (CAST(SUBSTRING(AMT_FIN FROM 1 FOR 2)AS INTEGER)/60) AS AN, SUBSTRING(AMT_FIN ,3 , 2) AS MOIS, SUBSTRING(AMT_FIN ,5 , 2) AS JOUR



FROM T_AMORTISSEMENT_AMT

donne :

AMT_FIN AMT_FIN_Y2K AN MOIS JOUR ------- ----------- ----------- ---- ---- 990601 NULL 1999 06 01 970201 NULL 1997 02 01 021201 NULL 2002 12 01 941101 NULL 1994 11 01 920715 NULL 1992 07 15

Il est alors facile de construire une requête UPDATE:

UPDATE T_AMORTISSEMENT_AMT SET AMT_FIN_Y2K = CAST(CAST(SUBSTRING(AMT_FIN FROM 1 FOR 2)AS INTEGER) + 2000 — 100 * (CAST(SUBSTRING(AMT_FIN FROM 1 FOR 2)AS INTEGER)/60) AS CHAR(4)) + '-' + SUBSTRING(AMT_FIN FROM 3 FOR 2) + '-' + SUBSTRING(AMT_FIN FROM 5 FOR 2)

La même requête pour MS SQL Server :

UPDATE T_AMORTISSEMENT_AMT SET AMT_FIN_Y2K = CAST(CAST(SUBSTRING(AMT_FIN, 1, 2)AS INTEGER) + 2000 — 100 * (CAST(SUBSTRING(AMT_FIN, 1, 2)AS INTEGER)/60) AS CHAR(4)) + '-' + SUBSTRING(AMT_FIN, 3, 2) + '-' + SUBSTRING(AMT_FIN, 5, 2)

Solution 2 : une autre solution existe, via l’opération ensembliste UNION

UPDATE AMORTISSEMENT SET FIN_AMORT_Y2K = (SELECT '20' || SUBSTRING(FIN_AMORT FROM 1 FOR 2) || '-' || SUBSTRING(FIN_AMORT FROM 3 FOR 2) || '-' || SUBSTRING(FIN_AMORT FROM 5 FOR 2) FROM AMORTISSEMENT A1 WHERE SUBSTRING(FIN_AMORT FROM 1 FOR 2) < '60' AND A1.FIN_AMORT = AMORTISSEMENT.FIN_AMORT UNION ALL



SELECT '19' || SUBSTRING(FIN_AMORT FROM 1 FOR 2) || '-' || SUBSTRING(FIN_AMORT FROM 3 FOR 2) || '-' || SUBSTRING(FIN_AMORT FROM 5 FOR 2) FROM AMORTISSEMENT A2 WHERE SUBSTRING(FIN_AMORT FROM 1 FOR 2) >= '60' AND A2.FIN_AMORT = AMORTISSEMENT.FIN_AMORT)

Solution 3 : Mais une solution plus simple consiste à utiliser le CASE

UPDATE AMORTISSEMENT SET FIN_AMORT_Y2K = (SELECT CASE WHEN SUBSTRING(FIN_AMORT FROM 1 FOR 2) < '60' THEN '20' ELSE '19' END || SUBSTRING(FIN_AMORT FROM 1 FOR 2) || '-' || SUBSTRING(FIN_AMORT FROM 3 FOR 2) || '-' || SUBSTRING(FIN_AMORT FROM 5 FOR 2) || FROM AMORTISSEMENT A WHERE A.FIN_AMORT = AMORTISSEMENT.FIN_AMORT)

TP 4 Les chambres libres

Pourriez-vous retrouver, à l’aide d’une requête SQL de votre cru : a) Les chambres qui sont libres pendant toute la période allant du 11 au 14 janvier 2000 ?

La recherche du vide ou de la non-existence fait toujours partie des choses difficiles à exprimer en SQL. Mais l’opérateur IN, et plus particulièrement sa négation le NOT IN, permet souvent de sortir de ce genre de difficulté.

SELECT CHB_NUM FROM T_CHAMBRE_CHB WHERE CHB_NUM NOT IN (SELECT CHB_NUM FROM T_PLANNING_PLN WHERE PLN_JOUR BETWEEN '2000-01-11' AND '2000-01-14'



AND PLN_LIBRE = 'False') b) L’occupation des chambres pour la journée du 13 janvier 2000 ?

La problématique est différente. Il nous faut toutes les chambres et si possible l’indication d’occupation. Et s’il n’y a pas d’indication d’occupation, alors on renseigne avec la valeur True la colonne PLN_LIBRE. Bien entendu, il faut utiliser une jointure externe :

SELECT CHB.CHB_NUM, COALESCE(PLN_LIBRE, 'True') AS PLN_LIBRE FROM T_CHAMBRE_CHB CHB LEFT OUTER JOIN T_PLANNING_PLN PLN ON CHB.CHB_NUM = PLN.CHB_NUM WHERE PLN_JOUR = '2000-01-13' OR PLN_JOUR IS NULL

ou encore :

SELECT CHB.CHB_NUM, COALESCE(PLN_LIBRE, 'True') AS PLN_LIBRE FROM T_CHAMBRE_CHB CHB LEFT OUTER JOIN T_PLANNING_PLN PLN ON CHB.CHB_NUM = PLN.CHB_NUM AND PLN_JOUR = '2000-01-13' c) Le planning des occupations pour toutes les chambres et toutes les dates du 11 au 14 janvier 2000 ?

Il n’est pas possible de faire cette requête directement car une partie de l’information est manquante. Par exemple, le 11 janvier ne figure nulle part. Il faut donc rajouter cette information à la base de données sous la forme d’une table de date que nous allons appeler T_CALENCRIER_CLD.

CREATE TABLE T_CALENDRIER_CLD (CLD_JOUR DATETIME) INSERT INTO T_CALENDRIER_CLD VALUES ('2000-01-01') ... INSERT INTO T_CALENDRIER_CLD VALUES ('2000-01-10') INSERT INTO T_CALENDRIER_CLD VALUES ('2000-01-11') INSERT INTO T_CALENDRIER_CLD VALUES ('2000-01-12') INSERT INTO T_CALENDRIER_CLD VALUES ('2000-01-13') INSERT INTO T_CALENDRIER_CLD VALUES ('2000-01-14') INSERT INTO T_CALENDRIER_CLD VALUES ('2000-01-15') ... INSERT INTO T_CALENDRIER_CLD VALUES ('2000-12-31')

Pour obtenir toutes les dates considérées et toutes les chambres, un produit cartésien semble parfait. SELECT CLD.CLD_JOUR, CHB.CHB_NUM, COALESCE(PLN.PLN_LIBRE, 'True') AS PLN_LIBRE FROM T_CALENDRIER_CLD CLD



CROSS JOIN T_CHAMBRE_CHB CHB LEFT OUTER JOIN T_PLANNING_PLN PLN ON CLD.CLD_JOUR = PLN.PLN_JOUR AND CHB.CHB_NUM = PLN.CHB_NUM WHERE CLD.CLD_JOUR BETWEEN '2000-01-11' AND '2000-01-14'

Et on fait la jointure externe de ce produit cartésien avec la table T_PLANNING_PLN.

TP 5 Dates d’anniversaire

Établissez les requêtes pour l’envoi des cartes d’anniversaire.

Connaissez-vous la fonction EXTRACT ? Non, sinon elle vous aurait grandement facilité la vie. Elle permet d’extraire une partie de date comme le mois ou le jour. Pour MS SQL Server cette fonction s’appelle DATEPART.

a) Les anniversaires entre le 21 février et le 20 mars. SELECT * FROM T_CLIENT_CLI WHERE ((EXTRACT(MONTH FROM CLI_DATE_NAISSANCE) — 1) * 31 + EXTRACT(DAY FROM CLI_DATE_NAISSANCE)) BETWEEN 52 AND 82

Les chiffes 83 et 113 sont calculés de la façon suivante : partant de février = 1 (2e mois – 1) et mars = 2 (3e mois – 1) 1 * 31 j + 21 j = 52 jours et 2 * 31 j + 20 j = 82 jours La même requête pour MS SQL Server :

SELECT * FROM T_CLIENT_CLI WHERE ((DATEPART(MONTH, CLI_DATE_NAISSANCE) — 1) * 31 + DATEPART(DAY, CLI_DATE_NAISSANCE)) BETWEEN 52 AND 82 b) Les anniversaires entre le 21 décembre et le 20 janvier.



SELECT * FROM T_CLIENT_CLI WHERE MOD(((EXTRACT (MONTH FROM CLI_DATE_NAISSANCE) — 1) * 31 + EXTRACT(DAY FROM CLI_DATE_NAISSANCE)), 362 ) BETWEEN 0 AND 20

MOD étant la fonction modulo (reste de la division entière). La difficulté est le passage à l’année suivante. Mais une simple translation via la fonction modulo suffit :21 décembre => 11 * 31 + 21 = 36220 janvier => 31 * 0 + 20 = 20 La même requête pour MS SQL Server :

SELECT * FROM T_CLIENT_CLI WHERE ((DATEPART(MONTH, CLI_DATE_NAISSANCE) — 1) * 31 + DATEPART(DAY, CLI_DATE_NAISSANCE)) % 362 BETWEEN 0 AND 20

c) La requête paramétrée générique.

Il suffit de combiner les deux requêtes à l’aide de la structure CASE :

SELECT * FROM T_CLIENT_CLI WHERE MOD(((EXTRACT(MONTH FROM CLI_DATE_NAISSANCE) — 1) * 31 + EXTRACT(DAY FROM CLI_DATE_NAISSANCE)), CASE WHEN (EXTRACT(MONTH FROM :dd) — 1) * 31 + EXTRACT(DAY FROM :dd) > (EXTRACT(MONTH FROM :df) — 1) * 31 + EXTRACT(DAY FROM :df) THEN (EXTRACT(MONTH FROM :dd) — 1) * 31 + EXTRACT(DAY FROM :dd) ELSE ((EXTRACT(MONTH FROM CLI_DATE_NAISSANCE) — 1) * 31 + EXTRACT(DAY FROM CLI_DATE_NAISSANCE)) + 1 END ) BETWEEN CASE WHEN (EXTRACT(MONTH FROM :dd) — 1) * 31 + EXTRACT(DAY FROM :dd) < (EXTRACT(MONTH FROM :df) — 1) * 31 + EXTRACT(DAY FROM :df) THEN (EXTRACT(MONTH FROM :dd) — 1) * 31 + EXTRACT(DAY FROM :dd) ELSE 0 END AND (EXTRACT(MONTH FROM :df) — 1) * 31 + EXTRACT(DAY FROM :df)

La requête paramétrée pour MS SQL Server (dans une procédure stockée) :

CREATE PROCEDURE SP_ANNIVERSAIRES @dd DATETIME, @df DATETIME AS



SELECT * FROM T_CLIENT_CLI WHERE ((DATEPART(MONTH, CLI_DATE_NAISSANCE) — 1) * 31 + DATEPART(DAY, CLI_DATE_NAISSANCE)) % CASE WHEN (DATEPART(MONTH, @dd) — 1) * 31 + DATEPART(DAY, @dd) > (DATEPART(MONTH, @df) — 1) * 31 + DATEPART(DAY, @df) THEN (DATEPART(MONTH, @dd) — 1) * 31 + DATEPART(DAY, @dd) ELSE ((DATEPART(MONTH, CLI_DATE_NAISSANCE) — 1) * 31 + DATEPART(DAY, CLI_DATE_NAISSANCE)) + 1 END BETWEEN CASE WHEN (DATEPART(MONTH, @dd) — 1) * 31 + DATEPART(DAY, @dd) < (DATEPART(MONTH, @df) — 1) * 31 + DATEPART(DAY, @df) THEN (DATEPART(MONTH, @dd) — 1) * 31 + DATEPART(DAY, @dd) ELSE 0 END AND (DATEPART(MONTH, @df) — 1) * 31 + DATEPART(DAY, @df)

De plus, si vous disposez de la fonction CURRENT_DATE, avec un peu d’astuce, vous pouvez réaliser une requête vous permettant de trouver tous les clients dont la date anniversaire sera dans les 15 jours qui viennent.

TP 6 Énumération

N’utilisez qu’un seul insert pour cette requête.

En fait, n’importe quel nombre entre 10 et 9 999, n’est que l’addition d’une unité, d’une dizaine, d’une centaine et d’un millier. Il suffit donc de placer dans la requête 4 fois la table pour représenter chacun des membres de cette addition.

Solution 1 Nous allons utiliser la jointure croisée qui réalise le produit cartésien des tables :

INSERT INTO T_ENTIER_ENT SELECT TEU.ENT_N -- les unités + 10 * TED.ENT_N -- les dizaines + 100 * TEC.ENT_N -- les centaines + 1000 * TEM.ENT_N -- les milliers FROM T_ENTIER_ENT TEU -- table des entiers pour les unités



CROSS JOIN T_ENTIER_ENT TED -- table des entiers pour les dizaines CROSS JOIN T_ENTIER_ENT TEC -- table des entiers pour les centaines CROSS JOIN T_ENTIER_ENT TEM -- table des entiers pour les milliers WHERE TEU.ENT_N + 10 * TED.ENT_N + 100 * TEC.ENT_N + 1000 * TEM.ENT_N > 9 -- empêche d’insérer les doublons des unités

Si votre SGBDR ne dispose pas de la jointure croisée, il suffit d’écrire votre requête comme ceci :

INSERT INTO T_ENTIER_ENT SELECT TEU.ENT_N -- les unités + 10 * TED.ENT_N -- les dizaines + 100 * TEC.ENT_N -- les centaines + 1000 * TEM.ENT_N -- les milliers FROM T_ENTIER_ENT TEU, -- table des entiers pour les unités T_ENTIER_ENT TED, -- table des entiers pour les dizaines T_ENTIER_ENT TEC, -- table des entiers pour les centaines T_ENTIER_ENT TEM -- table des entiers pour les milliers WHERE TEU.ENT_N + 10 * TED.ENT_N + 100 * TEC.ENT_N + 1000 * TEM.ENT_N > 9 -- empêche d’insérer les doublons des unités

Solution 2 : une autre solution existe. Elle reste similaire dans son principe.

INSERT INTO T_ENTIER_ENT SELECT CAST(CAST(TEM.ENT_N AS CHAR(1)) || CAST(TEC.ENT_N AS CHAR(1)) || CAST(TED.ENT_N AS CHAR(1)) || CAST(TEU.ENT_N AS CHAR(1)) AS INTEGER) FROM T_ENTIER_ENT TEU -- table des entiers pour les unités CROSS JOIN T_ENTIER_ENT TED -- table des entiers pour les dizaines CROSS JOIN T_ENTIER_ENT TEC -- table des entiers pour les centaines CROSS JOIN T_ENTIER_ENT TEM -- table des entiers pour les milliers WHERE (CAST(TEM.ENT_N AS CHAR(1)) || CAST(TEC.ENT_N AS CHAR(1)) || CAST(TED.ENT_N AS CHAR(1)) || CAST(TEU.ENT_N AS CHAR(1))) NOT BETWEEN '0000' AND '0009'

Cette solution propose de réaliser l’énumération en combinant les chiffres sous forme littérale. Elle est moins performante du fait de l’utilisation nonuple de la fonction CAST.



Même requête qui ci dessus pour MS SQL Server :

INSERT INTO T_ENTIER_ENT SELECT CAST(CAST(TEM.ENT_N AS CHAR(1)) + CAST(TEC.ENT_N AS CHAR(1)) + CAST(TED.ENT_N AS CHAR(1)) + CAST(TEU.ENT_N AS CHAR(1)) AS INTEGER) FROM T_ENTIER_ENT TEU -- table des entiers pour les unités CROSS JOIN T_ENTIER_ENT TED -- table des entiers pour les dizaines CROSS JOIN T_ENTIER_ENT TEC -- table des entiers pour les centaines CROSS JOIN T_ENTIER_ENT TEM -- table des entiers pour les milliers WHERE (CAST(TEM.ENT_N AS CHAR(1)) + CAST(TEC.ENT_N AS CHAR(1)) + CAST(TED.ENT_N AS CHAR(1)) + CAST(TEU.ENT_N AS CHAR(1))) NOT BETWEEN '0000' AND '0009'

TP 7 Le comptage

Le résultat recherché est de numéroter les lignes dans l’ordre alphabétique des noms. Établissez la requête.

Le problème de ce genre de requête est la présence de doublons absolus. En l’occurrence, nous avons deux fois la présence du nom DUPONT dans nos prospects. Si nous faisons une requête classique de comptage pour numéroter les lignes et que DUPONT ne figure qu’une seule fois dans la table, alors tout va bien :

SELECT PP1.PSP_NOM, COUNT(PP2.PSP_NOM) AS N FROM T_PROSPECT_PSP PP1 INNER JOIN T_PROSPECT_PSP PP2 ON PP1.PSP_NOM > PP2.PSP_NOM GROUP BY PP1.PSP_NOM

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

Mais en présence du doublon DUPONT, cette même requête donne un résultat qui n’est pas exploitable :



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

Dès lors il faut ajouter une information capable de discriminer les deux DUPONT. Une idée consiste à prendre une table de nombre unique et de faire une jointure croisée (produit cartésien), puis de prendre un nombre différent pour chaque valeur de même occurrence. En partant d’une table telle que celle-ci :

CREATE TABLE T_ENTIER_ENT (ENT_N INTEGER); 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); …

La requête suivante réalise le produit cartésien :

SELECT PSP_NOM, ENT_N FROM T_PROSPECT_PSP CROSS JOIN T_ENTIER_ENT

PSP_NOM ENT_N ---------------- ----------- ARMAND 1 ARMAND 2 ARMAND 3 ARMAND 4 ARMAND 5 ARMAND 6



ARMAND 7 ARMAND 8 ARMAND 9 BAILLE 1 BAILLE 2 BAILLE 3 …

Mais il y a bien trop de lignes pour que ce résultat soit exploitable. Il faut donc restreindre le produit cartésien sur le nombre d’occurrences des valeurs de noms. Ceci est possible à l’aide de la requête suivante :

SELECT PSP_NOM, N, ENT_N FROM (SELECT PSP_NOM, COUNT(*) AS N FROM T_PROSPECT_PSP GROUP BY PSP_NOM) T CROSS JOIN T_ENTIER_ENT WHERE ENT_N <= T.N

PSP_NOM N ENT_N ---------------- ----------- ----------- ARMAND 1 1 BAILLE 1 1 CLAUDE 1 1 DUPONT 2 1 DUPONT 2 2 GAUTIER 1 1 MARTIN 1 1

Nous avons maintenant notre discriminant d’occurrence de nom constitué par la colonne ENT_N. Par facilité constituons une vue :

CREATE VIEW V_PROSPECT_PSP AS SELECT PSP_NOM, ENT_N AS PSP_DISCRIMINANT, CAST(PSP_NOM AS CHAR(16)) + CAST(ENT_N AS CHAR(16)) AS PSP_NOM_DIFFERENT FROM (SELECT PSP_NOM, COUNT(*) AS N FROM T_PROSPECT_PSP GROUP BY PSP_NOM) T CROSS JOIN T_ENTIER_ENT WHERE ENT_N <= T.N

Une extraction brute de cette vue donne :



PSP_NOM PSP_DISCRIMINANT PSP_NOM_DIFFERENT ---------------- ---------------- ----------------- ARMAND 1 ARMAND 1 BAILLE 1 BAILLE 1 CLAUDE 1 CLAUDE 1 DUPONT 1 DUPONT 1 DUPONT 2 DUPONT 2 GAUTIER 1 GAUTIER 1 MARTIN 1 MARTIN 1

Ce qui est suffisant maintenant pour traiter le problème initial :

SELECT PP1.PSP_NOM, COUNT(*) N FROM V_PROSPECT_PSP PP1 INNER JOIN V_PROSPECT_PSP PP2 ON PP1.PSP_NOM_DIFFERENT > PP2.PSP_NOM_DIFFERENT GROUP BY PP1.PSP_NOM, PP1.PSP_NOM_DIFFERENT

Et donne le résultat attendu :

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

Depuis la norme SQL:2003 de nouvelles fonctions ont été ajoutées pour traiter ce genre de cas : RANK(), DENSE_RANK() et ROW_NUMBER(). Voici un exemple de leur fonctionnement sur notre table exercice :

SELECT PSP_NOM, RANK() OVER(ORDER BY PSP_NOM) AS RANG, DENSE_RANK() OVER(ORDER BY PSP_NOM) AS RANG_DENSE, ROW_NUMBER() OVER(ORDER BY PSP_NOM) AS NUMERO FROM T_PROSPECT_PSP

PSP_NOM RANG RANG_SENSE NUMERO ---------------- ----------- ----------- ----------- BAILLE 1 1 1



CLAUDE 2 2 2 DUPONT 3 3 3 DUPONT 3 3 4 GAUTIER 5 4 5 MARTIN 6 5 6

C’est plus pratique, il faut l’avouer, mais ces fonctions dites de fenêtrage ne peuvent pas apparaître dans les sous-requêtes dans la mesure où elles n’opèrent qu’en présence des résultats devant être affichés.

TP 8 Symétrie négative

Pouvez-vous alimenter ces nouvelles lignes et obtenir le résultat demandé ?

Il suffit de négativer l’identifiant numérique, comme le montant : INSERT INTO T_LIGNE_FACTURE_LIF SELECT LIF_NUM * -1, FAC_NUM, LIF_ARTICLE, LIF_MONTANT * -1 FROM T_LIGNE_FACTURE_LIF L WHERE FAC_NUM = 79 AND LIF_NUM > 0

Dès lors la requête d’extraction pour présenter les informations dans le bon ordre est la suivante : SELECT FAC_NUM, LIF_ARTICLE, LIF_MONTANT FROM T_LIGNE_FACTURE_LIF ORDER BY FAC_NUM, ABS(LIF_NUM), LIF_NUM DESC Mais certains SGBDR ne supportent pas d’expression, ni de fonctions, dans la clause de tri, ni même le tri externe. Dans ce cas, il faut calculer les nouvelles colonnes : SELECT FAC_NUM, LIF_ARTICLE, LIF_MONTANT, ABS(LIF_NUM) AS LIF_NUM1, LIF_NUM AS LIF_NUM2 FROM T_LIGNE_FACTURE_LIF ORDER BY FAC_NUM, LIF_NUM1, LIF_NUM2 DESC



TP 9 Premiers

En partant d’une simple table contenant les nombres de 0 à 10 000, vous devez, en une seule requête, obtenir tous les nombres premiers entre 1 et 10 000.

Il suffit de prendre le problème à l’envers et de récupérer facilement tous les nombres non premiers, c’est-à-dire ceux pour lesquels le reste de la division entière par au moins un des nombres qui lui est inférieur vaut 0 :

SELECT EN1.ENT_N FROM T_ENTIER_ENT EN1 CROSS JOIN T_ENTIER_ENT EN2 WHERE MOD(EN1.ENT_N, EN2.ENT_N) = 0 AND EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1

Pour ce faire, on réalise le produit cartésien du nombre avec tous les nombres compris entre 2 et ce nombre moins un, et l’on teste si le reste de la division entière vaut zéro. Si ce reste vaut zéro, alors preuve est faite que ce nombre n’est pas premier. Dès que nous avons tous les nombres non premiers, il ne s’agit plus que de trouver le complément :

SELECT * FROM T_ENTIER_ENT WHERE ENT_N NOT IN (SELECT EN1.ENT_N FROM T_ENTIER_ENT EN1 CROSS JOIN T_ENTIER_ENT EN2 WHERE MOD(EN1.ENT_N, EN2.ENT_N) = 0 AND EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1) ORDER BY 1

Ceci peut aussi se faire à l’aide de l’opérateur ensembliste EXCEPT :

SELECT * FROM T_ENTIER_ENT EXCEPT SELECT EN1.ENT_N FROM T_ENTIER_ENT EN1 CROSS JOIN T_ENTIER_ENT EN2 WHERE MOD(EN1.ENT_N, EN2.ENT_N) = 0 AND EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1



ORDER BY 1

La requête pour MS SQL Server :

SELECT * FROM T_ENTIER_ENT WHERE ENT_N NOT IN (SELECT EN1.ENT_N FROM T_ENTIER_ENT EN1 CROSS JOIN T_ENTIER_ENT EN2 WHERE EN1.ENT_N % EN2.ENT_N = 0 AND EN2.ENT_N BETWEEN 2 AND EN1.ENT_N -1) ORDER BY 1

TP 10 Scores

Il vous est demandé d’obtenir, à l’aide d’une requête, le nombre de bonnes réponses de chaque sondé.

En fait la difficulté réside dans la jointure, mais un simple CROSS JOIN suffit à résoudre le problème. Pour compter les bonnes réponses, il suffit d’utiliser le CASE et d’attribuer la valeur 1 si la réponse est bonne et la valeur 0 si elle est fausse. La somme de ces valeurs donne le score.

Solution 1

SELECT PNL_NOM, CASE WHEN J.PNL_REPONSE1 = R.RPS_REPONSE1 THEN 1 ELSE 0 END + CASE WHEN J.PNL_REPONSE2 = R.RPS_REPONSE2 THEN 1 ELSE 0 END + CASE WHEN J.PNL_REPONSE3 = R.RPS_REPONSE3 THEN 1 ELSE 0 END + CASE WHEN J.PNL_REPONSE4 = R.RPS_REPONSE4 THEN 1 ELSE 0 END + CASE WHEN J.PNL_REPONSE5 = R.RPS_REPONSE5 THEN 1 ELSE 0 END AS BONNE_REPONSE FROM T_PANEL_PNL J CROSS JOIN T_REPONSES_RPS R ORDER BY BONNE_REPONSE DESC

Solution 2 : une solution dans le même genre nous a été donnée par Fabien C.



SELECT PNL_NOM, ((SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE1 = J.PNL_REPONSE1) + (SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE2 = J.PNL_REPONSE2) + (SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE3 = J.PNL_REPONSE3) + (SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE4 = J.PNL_REPONSE4) + (SELECT COUNT(*) FROM T_REPONSES_RPS R WHERE R.RPS_REPONSE5 = J.PNL_REPONSE5)) AS T_REPONSES_RPS FROM T_PANEL_PNL J

TP 11 Tranches d’âge

Effectuez la requête pour obtenir les tranches d’âge. Vous devez répondre à cette demande de façon générique, c’est-à-dire quelles que soient les futures tranches d’âge à gérer.

• Plusieurs petites difficultés : le calcul de l’âge, la réalisation des tranches, les effets de bords. Pour le calcul de l’âge, on a la formule suivante qui donne un âge décimal :

CAST(CURRENT_DATE — CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425

Pour la réalisation des tranches, il faut ajouter l’information puisqu’elle n’y est pas. Construisons donc une nouvelle table :

CREATE TABLE T_TRANCHE_AGE_TAG (TAG_LIMITE INTEGER) INSERT INTO T_TRANCHE_AGE_TAG VALUES (18) INSERT INTO T_TRANCHE_AGE_TAG VALUES (40)

Ces informations nous suffisent, mais il serait plus pratique d’avoir la représentation des tranches sous la forme :

TRANCHE_MIN TRANCHE_MAX ------------- ------------- 0 18 18 40



40 NULL

Car nous pourrons alors joindre la table des clients par une fourchette de valeur à cette représentation. Bien entendu, nous ferrons en sorte de représenter le NULL comme étant une valeur loin dans le futur. Cette requête nous donne presque la solution :

SELECT * FROM T_TRANCHE_AGE_TAG TA1 LEFT OUTER JOIN T_TRANCHE_AGE_TAG TA2 ON TA1.TAG_LIMITE < TA2.TAG_LIMITE TAG_LIMITE TAG_LIMITE ----------- ----------- 18 40 40 NULL

Il faut juste rajouter la tranche commençant par zéro. Cela peut être fait à l’aide d’une opération d’UNION. Profitons-en pour mettre tout cela dans une vue :

CREATE VIEW V_TRANCHE_AGE_TAG AS SELECT TA1.TAG_LIMITE AS TRANCHE_MIN, TA2.TAG_LIMITE AS TRANCHE_MAX FROM T_TRANCHE_AGE_TAG TA1 LEFT OUTER JOIN T_TRANCHE_AGE_TAG TA2 ON TA1.TAG_LIMITE < TA2.TAG_LIMITE UNION SELECT 0, MIN(TAG_LIMITE) FROM T_TRANCHE_AGE_TAG

Assemblons maintenant tout ceci dans la requête finale :

SELECT TRANCHE_MIN, COALESCE(TRANCHE_MAX, 9999) AS TRANCHE_MAX, COUNT(*) AS NOMBRE FROM V_TRANCHE_AGE_TAG TAG INNER JOIN T_CLIENT_CLI ON CAST(CURRENT_DATE — CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425 BETWEEN TRANCHE_MIN AND COALESCE(TRANCHE_MAX, 9999) GROUP BY TRANCHE_MIN, TRANCHE_MAX ORDER BY TRANCHE_MIN



TRANCHE_MIN TRANCHE_MAX NOMBRE ----------- ----------- ----------- 0 18 6 18 40 4 40 9999 13

Néanmoins cette solution n’est pas correcte. En effet, ceux qui ont exactement 18 ans ou 40 ans sont comptabilisés deux fois. Voici ce qui serait beaucoup plus correct :

SELECT TRANCHE_MIN, COALESCE(TRANCHE_MAX, 9999) AS TRANCHE_MAX, COUNT(*) AS NOMBRE FROM V_TRANCHE_AGE_TAG TAG INNER JOIN T_CLIENT_CLI ON CAST(CURRENT_DATE — CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425 > TRANCHE_MIN AND CAST(CURRENT_DATE — CLI_DATE_NAISSANCE AS INTERVAL DAY) / 365.2425 <= COALESCE(TRANCHE_MAX, 999) GROUP BY TRANCHE_MIN, TRANCHE_MAX ORDER BY 1

La requête pour MS SQL Server :

SELECT TRANCHE_MIN, COALESCE(TRANCHE_MAX, 9999) AS TRANCHE_MAX, COUNT(*) AS NOMBRE FROM V_TRANCHE_AGE_TAG TAG INNER JOIN T_CLIENT_CLI ON DATEDIFF(DAY, CLI_DATE_NAISSANCE, CURRENT_TIMESTAMP) / 365.2425 > TRANCHE_MIN AND DATEDIFF(DAY, CLI_DATE_NAISSANCE, CURRENT_TIMESTAMP) / 365.2425 <= COALESCE(TRANCHE_MAX, 999) GROUP BY TRANCHE_MIN, TRANCHE_MAX ORDER BY 1

TP 12 Appariement

Dans ce TP, il s’agit de récupérer les valeurs de la colonne A pour laquelle les données de la colonne B sont exactement les mêmes qu’une autre valeur de la colonne A.

En fait une double condition doit être simultanément vraie : que tous les PER_2 de la valeur PER_1 scrutée soient dans les valeurs de PER_2 en regard et que tous les PER_2 en regard soient dans les valeurs des



PER_2 de la valeur PER_1 scrutée. On joint la table sur elle-même avec équivalence pour PER_1 et différence pour PER_2. Pour plus de clarté, appelons les deux occurrences de notre table ORIGINE pour la première et DESTINATION pour la seconde :

• SELECT DISTINCT ORIGINE.PER_1, DESTINATION.PER_1 FROM T_PAIRE_PER ORIGINE INNER JOIN T_PAIRE_PER DESTINATION ON ORIGINE.PER_2 = DESTINATION.PER_2 AND ORIGINE.PER_1 <> DESTINATION.PER_1Le premier filtre considère que toutes les valeurs PER_2 de ORIGINE doivent être présentes dans PER_2 de DESTINATION.Avec cette clause, A1 et A9 sont éliminées.

• WHERE NOT EXISTS (SELECT * FROM T_PAIRE_PER WHERE PER_1 = ORIGINE.PER_1 AND PER_2 NOT IN (SELECT PER_2 FROM T_PAIRE_PER WHERE PER_1 = DESTINATION.PER_1))Le second filtre considère que toutes les valeurs PER_2 de DESTINATION doivent être présentes dans PER_2 de ORIGINE.Avec cette clause, A5 est éliminée.

AND NOT EXISTS (SELECT * FROM T_PAIRE_PER WHERE PER_1 = DESTINATION.PER_1 AND PER_2 NOT IN (SELECT PER_2 FROM T_PAIRE_PER WHERE PER_1 = ORIGINE.PER_1)) ORDER BY 1, 2

Le premier filtre conduit au jeu de résultats suivant :

Le second filtre conduit au jeu de résultats suivant :

Solution

PER_1 PER_1 ----- ----- A2 A3 A2 A4 A2 A6 A2 A8 A2 A9

PER_1 PER_1 ----- ----- A1 A5 A2 A4

PER_1 PER_1 ----- ----- A2 A4



A3 A6 A3 A8 A4 A2 A4 A3 A4 A6 A4 A8 A4 A9 A5 A1 A6 A3 A6 A8 A8 A3 A8 A6

A3 A2 A3 A4 A3 A6 A3 A8 A4 A2 A6 A2 A6 A3 A6 A4 A6 A8 A8 A2 A8 A3 A8 A4 A8 A6 A9 A2 A9 A4

A3 A6 A3 A8 A4 A2 A6 A3 A6 A8 A8 A3 A8 A6

Les données communes ont été mises en évidence en gras. On peut lire ces filtres de la manière suivante : il ne faut pas qu’il existe une valeur de PER_2 de la table origine qui ne soit pas dans la table de destination. Et vice-versa pour le second filtre.

En fait le problème s’apparente à la division relationnelle et plus particulièrement à la division de Todd que Joe Celko évoque dans son ouvrage SQL for smarties (SQL Avancé, Vuibert éditeur).

TP 13 Ordonner, réordonner !

1. Recombiner l’ordre de la liste en une seule requête. La requête se présente ainsi : UPDATE T_PAYS_PAY SET PAY_POSITION = CASE



WHEN PAY_POSITION = 5 THEN 1 WHEN PAY_POSITION >= 1 AND PAY_POSITION < 5 THEN PAY_POSITION + 1 ELSE PAY_POSITION END

Le résultat après exécution de cette requête est : SELECT * FROM T_PAYS_PAY ORDER BY PAY_POSITION PAY_NOM PAY_POSITION ---------------- ------------ France 1 Allemagne 2 Belgique 3 Croatie 4 Espagne 5 Grèce 6

Attention, si les items à échanger consistent à déplacer une valeur vers le bas, il faut inverser le fonctionnement de cet UPDATE. Par exemple, si nous voulons faire glisser l’Allemagne du 2e au 5e rang, il faut construire l’UPDATE comme suit :

UPDATE T_PAYS_PAY SET PAY_POSITION = CASE WHEN PAY_POSITION = 2 THEN 5 WHEN PAY_POSITION > 2 AND PAY_POSITION <= 5 THEN PAY_POSITION — 1 ELSE PAY_POSITION END

2. Proposez une solution générique pour cette permutation d’ordre de tri. Notons ORIGINE la position originale de l’item à échanger et DESTINATION la valeur finale.

La requête s’écrit alors : UPDATE T_PAYS_PAY SET PAY_POSITION = CASE WHEN :ORIGINE — :DESTINATION > 0 THEN CASE WHEN PAY_POSITION>= :DESTINATIONAND PAY_POSITION< :ORIGINE THEN PAY_POSITION+ 1



WHEN PAY_POSITION= :ORIGINE THEN :DESTINATION ELSE PAY_POSITION END WHEN :ORIGINE — :DESTINATION < 0 THEN CASE WHEN PAY_POSITION= :ORIGINE THEN :DESTINATION WHEN PAY_POSITION> :ORIGINE AND PAY_POSITION<= :DESTINATION THEN PAY_POSITION- 1 ELSE PAY_POSITION END ELSE PAY_POSITION END

Exemple pour MS SQL Server : la requête paramétrée sous forme de procédure stockée : CREATE PROCEDURE P_ECHANGE_POSITION @ORIGINE INTEGER, @DESTINATION INTEGER AS UPDATE T_PAYS_PAY SET PAY_POSITION = CASE WHEN @ORIGINE — @DESTINATION > 0 THEN CASE WHEN PAY_POSITION >= @DESTINATION AND PAY_POSITION < @ORIGINE THEN PAY_POSITION + 1 WHEN PAY_POSITION = @ORIGINE THEN @DESTINATION ELSE PAY_POSITION END WHEN @ORIGINE — @DESTINATION < 0 THEN CASE WHEN PAY_POSITION = @ORIGINE THEN @DESTINATION WHEN PAY_POSITION > @ORIGINE AND PAY_POSITION <= @DESTINATION THEN PAY_POSITION — 1 ELSE PAY_POSITION END ELSE PAY_POSITION END; 3. Renumérotez les positions en continuité de 1 à n (n étant le nombre de lignes dans la table) quelles que soient les données de la colonne position, mais en gardant toujours le même ordre. Avec le nouveau jeu d’essai, comment donc passer de :



PAY_NOM PAY_POSITION ---------------- ------------ Espagne 5 Grèce 7 Belgique 8 Croatie 9 Allemagne 11 France 12 à : PAY_NOM PAY_POSITION ---------------- ------------ Espagne 1 Grèce 2 Belgique 3 Croatie 4 Allemagne 5 France 6 L’idée est de faire une non-équijointure pour exécuter un comptage : SELECT T1.PAY_NOM, COUNT(T2.PAY_POSITION) NEW_POSITION FROM T_PAYS_PAY T1 LEFT OUTER JOIN T_PAYS_PAY T2 ON T1.PAY_POSITION <= T2.PAY_POSITION GROUP BY T1.PAY_NOM PAY_NOM NEW_POSITION ---------------- ------------ Allemagne 2 Belgique 4 Croatie 3 Espagne 6 France 1 Grèce 5 Le calcul de la nouvelle position doit se faire en conservant l’ancienne en référence pour que l’UPDATE puisse se faire. Une telle requête le permet : SELECT T1.PAY_NOM, T1.PAY_POSITION, (SELECT COUNT(*) FROM T_PAYS_PAY) — COUNT(*) + 1 AS NEW_POSITION FROM T_PAYS_PAY T1 LEFT OUTER JOIN T_PAYS_PAY T2 ON T1.PAY_POSITION <= T2.PAY_POSITION GROUP BY T1.PAY_NOM, T1.PAY_POSITION PAY_NOM PAY_POSITION NEW_POSITION ---------------- ------------ ------------ Espagne 5 1 Grèce 7 2



Belgique 8 3 Croatie 9 4 Allemagne 11 5 France 12 6

Requête qu’il faut maintenant corréler dans l’UPDATE de cette manière : UPDATE T_PAYS_PAY SET PAY_POSITION = (SELECT (SELECT COUNT(*) FROM T_PAYS_PAY) — COUNT(*) + 1 AS NEW_POSITION FROM T_PAYS_PAY T1 LEFT OUTER JOIN T_PAYS_PAY T2 ON T1.PAY_POSITION <= T2.PAY_POSITION WHERE T1.PAY_POSITION = T_PAYS_PAY.PAY_POSITION GROUP BY T1.PAY_NOM, T1.PAY_POSITION)

TP 14 Jointure hétérogène multiple

Réalisez une requête qui fasse la jointure entre cette table et la table T_PRESTATION_PST.

Plusieurs solutions sont possibles. Il suffit d’isoler par des extractions de sous-chaînes les valeurs numériques. On utilise le SUBSTRING combiné au LIKE :

SELECT * FROM T_PUBLIC_PBL PBL INNER JOIN T_PRESTATION_PST PST ON PST.PST_PUBLIC LIKE '%[' +CAST(PBL.PBL_ID AS VARCHAR(16))+']%' ORDER BY PBL_ID, PST_ID

TP 15 Insertion conditionnelle

Créez une requête INSERT dans cette table avec les valeurs « 1 », « toto » et faites en sorte que rien ne soit inséré sans générer d’erreur.

On peut toujours insérer des données en utilisant une sous-requête :

INSERT INTO… SELECT …



Dans la sous-requête SELECT on peut placer des constantes comme 1, toto ! Le problème est de trouver la bonne requête SELECT qui retournera une seule fois la ligne 1, toto si cette ligne n’est pas déjà dans la table et aucune ligne, si ces données sont déjà dans la table. L’idée est d’utiliser le prédicat EXISTS, ou plutôt NOT EXISTS, pour piloter l’apparition de la ligne à insérer.

• En fait le problème est plus compliqué qu’il n’y paraît : il faut étudier deux cas bien distincts :il n’y a rien dans la table ;il y a des lignes dans la table.

Cette première requête fonctionne s’il y a des lignes dans la table :

INSERT INTO MATABLE (COL1, COL2) SELECT DISTINCT 1, 'titi' FROM MATABLE AS M WHERE NOT EXISTS(SELECT * FROM MATABLE WHERE COL1 = 1 AND COL2 = 'titi')

La difficulté maintenant est d’améliorer cette requête si la table est vide. Dans ce cas, on ne peut faire appel à un filtre sur la table elle-même car toute interrogation sur une table vide renvoie par principe « aucune ligne » ! L’idée est alors d’utiliser une autre table. Dans ce cas, on prend une table du dictionnaire que l’on sait contenir au moins une ligne. Voyons, par exemple, la vue d’information de schéma INFORMATION_SCHEMA.TABLES :

INSERT INTO MATABLE (COL1, COL2) SELECT DISTINCT 1, 'titi' FROM INFORMATION_SCHEMA.TABLES WHERE NOT EXISTS (SELECT * FROM MATABLE)

Le tout peut être aggloméré à l’aide de l’opérateur ensembliste UNION : INSERT INTO MATABLE (COL1, COL2) SELECT DISTINCT 1, 'tutu' FROM MATABLE AS M WHERE NOT EXISTS(SELECT * FROM MATABLE WHERE COL1 = 1 AND COL2 = 'tutu') UNION SELECT DISTINCT 1, 'titi'



FROM INFORMATION_SCHEMA.TABLES WHERE NOT EXISTS (SELECT * FROM MATABLE)

Si votre SGBDR le supporte, utilisez le constructeur de lignes valuées : INSERT INTO MATABLE (COL1, COL2) SELECT DISTINCT 1, 'tutu' FROM MATABLE AS M WHERE NOT EXISTS(SELECT * FROM MATABLE WHERE (COL1 , COL2) = (1, 'tutu') UNION SELECT DISTINCT 1, 'titi' FROM INFORMATION_SCHEMA.TABLES WHERE NOT EXISTS (SELECT * FROM MATABLE)

TP 16 Noms incrémentés

Réalisez la requête pour insérer un nom recalculé.

Bien évidemment, pour numéroter il faut avoir une table de nombres. Construisons-la : CREATE TABLE T_NUM (NUM INT NOT NULL PRIMARY KEY); -- insertion des nombres de 0 à 9 INSERT INTO T_NUM VALUES (0); INSERT INTO T_NUM VALUES (1); INSERT INTO T_NUM VALUES (2); INSERT INTO T_NUM VALUES (3); INSERT INTO T_NUM VALUES (4); INSERT INTO T_NUM VALUES (5); INSERT INTO T_NUM VALUES (6); INSERT INTO T_NUM VALUES (7); INSERT INTO T_NUM VALUES (8); INSERT INTO T_NUM VALUES (9);

Insertion des nombres de 10 à 999



INSERT INTO T_NUM SELECT DISTINCT T1.NUM + 10 * T2.NUM + 100 * T3.NUM FROM T_NUM T1 CROSS JOIN T_NUM T2 CROSS JOIN T_NUM T3 WHERE T1.NUM + 10 * T2.NUM + 100 * T3.NUM BETWEEN 10 AND 999

L’idée est de prévoir toutes les combinaisons possibles de NOM + n° et de retenir la première qui n’est pas dans le lot ! Nous allons voir que cette idée possède un petit effet de bord.

Exemple :

Nous essayons avec 'DUPOND' : SELECT 'DUPOND' + CAST(NUM AS VARCHAR(3)) AS NEW_NOM, NUM FROM T_NUM WHERE NOT EXISTS (SELECT * FROM T_UTILISATEUR_USR WHERE USR_NOM = 'DUPOND'+ CAST(NUM AS VARCHAR(3))) NEW_NOM NUM --------- ----------- DUPOND0 0 DUPOND1 1 DUPOND2 2 DUPOND3 3 DUPOND4 4 DUPOND5 5 DUPOND6 6 DUPOND7 7 DUPOND8 8 DUPOND9 9 DUPOND10 10 DUPOND11 11 DUPOND12 12 DUPOND13 13 ...

Comme dans notre table il n’y a pas de DUPOND avec un D, il faudrait insérer ce DUPOND sans numéro ! Comment faire ? Tout simplement en transformant le zéro en chaîne vide à l’aide de la structure CASE :

SELECT 'DUPOND' + CASE WHEN NUM = 0 THEN ''



ELSE CAST(NUM AS VARCHAR(3)) END AS NEW_NOM, NUM FROM T_NUM WHERE NOT EXISTS (SELECT * FROM T_UTILISATEUR_USR WHERE USR_NOM = 'DUPOND' + CASE WHEN NUM = 0 THEN '' ELSE CAST(NUM AS VARCHAR(3)) END)

NEW_NOM NUM --------- ----------- DUPOND 0 DUPOND1 1 DUPOND2 2 DUPOND3 3 ...

Il faut maintenant récupérer le premier. Ce premier est donné par la requête suivante : SELECT MIN(NUM) FROM T_NUM WHERE NOT EXISTS (SELECT * FROM T_UTILISATEUR_USR WHERE USR_NOM = 'DUPOND' + CASE WHEN NUM = 0 THEN '' ELSE CAST(NUM AS VARCHAR(3)) END)

Avec ces deux requêtes, on obtient la formule d’insertion suivante : INSERT INTO T_UTILISATEUR_USR SELECT 4, 'DUPOND' + CASE WHEN NUM = 0 THEN '' ELSE CAST(NUM AS VARCHAR(3)) END AS NEW_NOM FROM T_NUM WHERE NOT EXISTS (SELECT * FROM T_UTILISATEUR_USR WHERE USR_NOM = 'DUPOND' + CASE WHEN NUM = 0 THEN '' ELSE CAST(NUM AS VARCHAR(3)) END) AND NUM = (SELECT MIN(NUM) FROM T_NUM



WHERE NOT EXISTS (SELECT * FROM T_UTILISATEUR_USR WHERE USR_NOM = 'DUPOND' + CASE WHEN NUM = 0 THEN '' ELSE CAST(NUM AS VARCHAR(3)) END))

Voici une autre formulation plus concise, qui possède l’avantage de ne nécessiter qu’une seule fois l’usage du nom à insérer :

INSERT INTO T_UTILISATEUR_USR SELECT 4, NOM + CASE NUM WHEN 0 THEN '' ELSE CAST(NUM AS VARCHAR(3)) END FROM (SELECT MIN(NOM) AS NOM, MIN(NUM) AS NUM FROM (SELECT 'DUPOND' AS NOM, NUM FROM T_NUM) AS T WHERE NOM + CASE NUM WHEN 0 THEN '' ELSE CAST(NUM AS VARCHAR(3)) END NOT IN (SELECT USR_NOM FROM T_UTILISATEUR_USR)) AS TT

Attention : si vous voulez numéroter au-delà de 999, prévoyez une table plus remplie et modifiez le transptypage de VARCHAR(3) pour l’adapter à votre problématique. Si l’on analyse les plans d’exécution de ces différentes requêtes, voici ce que cela donne.

Solution 1 avec NOT EXISTS SELECT 4, 'DUPOND' + CASE WHEN NUM = 0 THEN '' ELSE CAST(NUM AS VARCHAR(3)) END AS NEW_NOM FROM T_NUM WHERE NOT EXISTS (SELECT * FROM T_UTILISATEUR_USR WHERE USR_NOM = 'DUPOND' + CASE WHEN NUM = 0 THEN '' ELSE CAST(NUM AS VARCHAR(3)) END) AND NUM = (SELECT MIN(NUM) FROM T_NUM



WHERE NOT EXISTS (SELECT * FROM T_UTILISATEUR_USR WHERE USR_NOM = 'DUPOND' + CASE WHEN NUM = 0 THEN '' ELSE CAST(NUM AS VARCHAR(3)) END))

Solution 2 avec sous-requêtes dans la clause FROM SELECT 4, NOM + CASE NUM WHEN 0 THEN '' ELSE CAST(NUM AS VARCHAR(3)) END FROM (SELECT MIN(NOM) AS NOM, MIN(NUM) AS NUM FROM (SELECT 'DUPOND' AS NOM, NUM FROM T_NUM) AS T WHERE NOM + CASE NUM WHEN 0 THEN '' ELSE CAST(NUM AS VARCHAR(3)) END NOT IN (SELECT USR_NOM FROM T_UTILISATEUR_USR)) AS TT



Mais il faut prendre en compte les lectures dans les tables. D’autres solutions existent, notamment si vous pouvez générer un SELECT sans table, ce qui est possible sous SQL Server, ou encore si vous disposez d’une table d’une seule ligne faite pour cela, comme c’est le cas chez Oracle avec la célèbre table DUAL. De toute façon, vous pouvez toujours créer une telle table ou bien même une vue suivant ce principe, par exemple :

CREATE TABLE DUAL (DUMMY NVARCHAR(1)); INSERT INTO DUAL VALUES ('X');

Comme nous avons déjà jouté une table de nombres, nous proposons la vue suivante : CREATE VIEW LIGNE AS SELECT SQRT(NUM) AS COLONNE FROM T_NUM WHERE NUM = 1

L’intérêt d’avoir fait une racine carrée de 1 est que la vue ne peut plus être mise à jour !

Solution 3

Voici maintenant d’autres expressions de requêtes résolvant ce TP : SELECT MIN(NOUVEAU_NOM) FROM (SELECT RTRIM(USR_NOM) + CAST(NUM AS VARCHAR(34)) NOUVEAU_NOM FROM T_UTILISATEUR_USR U CROSS JOIN T_NUM N WHERE USR_NOM = 'DUPOND'



AND NUM > 0 UNION SELECT 'DUPOND' FROM LIGNE) AS T WHERE NOUVEAU_NOM NOT IN (SELECT USR_NOM FROM T_UTILISATEUR_USR);

SELECT MIN(NOUVEAU_NOM) FROM (SELECT RTRIM(USR_NOM) + CAST(NUM AS VARCHAR(34)) NOUVEAU_NOM FROM T_UTILISATEUR_USR U CROSS JOIN T_NUM N WHERE USR_NOM = 'DUPOND' AND NUM > 0 UNION SELECT 'DUPOND' FROM LIGNE) AS T EXCEPT SELECT USR_NOM FROM T_UTILISATEUR_USR;

TP 17 Filtrer les adresses IP

Écrivez la requête pour filtrer les adresses IP recherchées.

La table proposée viole notablement la première forme normale qui indique que tout attribut doit être atomique, c’est-à-dire non décomposable. Or, une adresse IP est au moins composée de 4 parties et devrait l’être de 6 dès que la norme IPV6 sera en place. Qu’à cela ne tienne… Il suffit de substituer à la table une autre table rectifiant l’erreur de modélisation. Voici donc, dans un premier temps, une requête faisant cette opération. Cette requête gagnerait à être transformée en vue !

Extraction du premier membre de l’adresse ip :

SELECT TIP_ID, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS BIP_ADR1 FROM TIP TIP_ID BIP_ADR1 ----------- ---------------



1 10 2 10 3 10 4 10

Pour extraire le suivant, il faut se débarrasser du premier : SELECT TIP_ID, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS BIP_ADR1, SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) — CHARINDEX('.', TIP_ADR)) AS TIP_ADR FROM TIP TIP_ID BIP_ADR1 TIP_ADR ----------- --------------- --------------- 1 10 120.12.1 2 10 130.23.1 3 10 130.201.1 4 10 13.11.1

Puis, recommencer le processus par imbrication des requêtes. Pour les deux premiers :

SELECT TIP_ID, BIP_ADR1, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS BIP_ADR2, SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) — CHARINDEX('.', TIP_ADR)) AS TIP_ADR FROM (SELECT TIP_ID, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS BIP_ADR1, SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) — CHARINDEX('.', TIP_ADR)) AS TIP_ADR FROM TIP) T TIP_ID BIP_ADR1 BIP_ADR2 TIP_ADR ----------- --------------- --------------- --------------- 1 10 120 12.1 2 10 130 23.1 3 10 130 201.1 4 10 13 11.1

Pour les trois premiers : SELECT TIP_ID, BIP_ADR1, BIP_ADR2, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS BIP_ADR3, SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) — CHARINDEX('.', TIP_ADR)) AS TIP_ADR FROM (SELECT TIP_ID, BIP_ADR1, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS BIP_ADR2, SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) — CHARINDEX('.', TIP_ADR)) AS TIP_ADR FROM (SELECT TIP_ID, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS BIP_ADR1,



SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) — CHARINDEX('.', TIP_ADR)) AS TIP_ADR FROM TIP) T) TT TIP_ID BIP_ADR1 BIP_ADR2 BIP_ADR3 TIP_ADR ----------- --------------- --------------- --------------- --------------- 1 10 120 12 1 2 10 130 23 1 3 10 130 201 1 4 10 13 11 1 Pour les quatre premiers : SELECT TIP_ID, BIP_ADR1, BIP_ADR2, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS BIP_ADR3, SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) — CHARINDEX('.', TIP_ADR)) AS BIP_ADR4 FROM (SELECT TIP_ID, BIP_ADR1, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS BIP_ADR2, SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) — CHARINDEX('.', TIP_ADR)) AS TIP_ADR FROM (SELECT TIP_ID, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS BIP_ADR1, SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) — CHARINDEX('.', TIP_ADR)) AS TIP_ADR FROM TIP) T) TT TIP_ID BIP_ADR1 BIP_ADR2 BIP_ADR3 BIP_ADR4 ----------- --------------- --------------- --------------- --------------- 1 10 120 12 1 2 10 130 23 1 3 10 130 201 1 4 10 13 11 1

Ajout du transtypage en entier : SELECT TIP_ID, CAST(BIP_ADR1 AS TINYINT) AS BIP_ADR1, CAST(BIP_ADR2 AS TINYINT) AS BIP_ADR2, CAST(SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS TINYINT) AS BIP_ADR3, CAST(SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) — CHARINDEX('.', TIP_ADR)) AS TINYINT) AS BIP_ADR4 FROM (SELECT TIP_ID, BIP_ADR1, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS BIP_ADR2, SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) — CHARINDEX('.', TIP_ADR)) AS TIP_ADR FROM (SELECT TIP_ID, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS BIP_ADR1, SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) — CHARINDEX('.', TIP_ADR)) AS TIP_ADR FROM TIP) T) TT TIP_ID BIP_ADR1 BIP_ADR2 BIP_ADR3 BIP_ADR4 ----------- -------- -------- -------- -------- 1 10 120 12 1 2 10 130 23 1



3 10 130 201 1 4 10 13 11 1

Transformation en vue : CREATE VIEW VIP AS SELECT TIP_ID, CAST(BIP_ADR1 AS TINYINT) AS BIP_ADR1, CAST(BIP_ADR2 AS TINYINT) AS BIP_ADR2, CAST(SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS TINYINT) AS BIP_ADR3, CAST(SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) — CHARINDEX('.', TIP_ADR)) AS TINYINT) AS BIP_ADR4 FROM (SELECT TIP_ID, BIP_ADR1, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS BIP_ADR2, SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) — CHARINDEX('.', TIP_ADR)) AS TIP_ADR FROM (SELECT TIP_ID, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) — 1) AS BIP_ADR1, SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) — CHARINDEX('.', TIP_ADR)) AS TIP_ADR FROM TIP) T) TTÀ ce stade, la requête devient triviale pour les SGBDR utilisant le concept de ROW VALUE CONSTRUCTOR : SELECT * FROM VIP WHERE (BIP_ADR1, BIP_ADR2, BIP_ADR3, BIP_ADR4) BETWEEN (10, 120, 12, 1) AND (10, 130, 23, 1)

Si ce n’est pas le cas, il faudra faire avec la décomposition suivante : SELECT * FROM VIP WHERE (BIP_ADR1 > 10 OR BIP_ADR1 = 10 AND BIP_ADR2 > 120 OR BIP_ADR1 = 10 AND BIP_ADR2 = 120 AND BIP_ADR3 > 12 OR BIP_ADR1 = 10 AND BIP_ADR2 = 120 AND BIP_ADR3 = 12 AND BIP_ADR4 >= 1) AND (BIP_ADR1 < 10 OR BIP_ADR1 = 10 AND BIP_ADR2 < 130 OR BIP_ADR1 = 10 AND BIP_ADR2 = 130 AND BIP_ADR3 < 23 OR BIP_ADR1 = 10 AND BIP_ADR2 = 130 AND BIP_ADR3 = 23 AND BIP_ADR4 <= 1)

Avec un petite astuce de mathématicien, on peut arriver à une formulation plus concise. En effet, les différentes parties d’une adresse étant codées sur un entier limité à 256 positions, cela revient à une énumération en base 256 que l’on peut transformer en base 10 de la sorte :

CREATE VIEW DIP AS SELECT BIP_ADR1 * POWER(256, 3) + BIP_ADR2 * POWER(256, 2) + BIP_ADR3 * POWER(256, 1) + BIP_ADR4 * POWER(256, 0) AS ADR_IP, BIP_ADR1, BIP_ADR2, BIP_ADR3, BIP_ADR4



FROM VIP

Dès lors la requête devient simple : SELECT BIP_ADR1, BIP_ADR2, BIP_ADR3, BIP_ADR4 FROM DIP WHERE ADR_IP BETWEEN 10 * POWER(256, 3) + 120 * POWER(256, 2) + 12 * 256 + 1 AND 10 * POWER(256, 3) + 130 * POWER(256, 2) + 23 * 256 + 1

solutions - laboratoire d'informatique de paris 6 · solutions des exercices pratiques –...

Documents