[CONCOURS SQL PRO] Problème n° 4 – les chambres libres ( 2 étoiles )

Laisser une réponse

Un exercice assez simple de sql pour sql serveur 2000.
Enonce :Problème n° 4 – les chambres libres
Solution :
Question 1 :

SELECT CHB_NUM  
FROM T_CHAMBRE_CHB  
WHERE CHB_NUM NOT IN  
( SELECT CHB_NUM  
FROM T_PLANNING_PLN  
WHERE PLN_LIBRE = 'FALSE'  
AND  PLN_JOUR>='11/01/2000' AND PLN_JOUR<='14/01/2000')

Question 2 :

SELECT CHB_NUM,'TRUE'  
FROM T_CHAMBRE_CHB  
WHERE CHB_NUM NOT IN  
( SELECT CHB_NUM  
FROM T_PLANNING_PLN  
WHERE PLN_LIBRE = 'FALSE'  
AND  PLN_JOUR='13/01/2000')
UNION
SELECT CHB_NUM,'FALSE'  
FROM T_CHAMBRE_CHB  
WHERE CHB_NUM IN  
( SELECT CHB_NUM  
FROM T_PLANNING_PLN  
WHERE PLN_LIBRE = 'FALSE'  
AND  PLN_JOUR='13/01/2000')

Question 3 :

CREATE TABLE CALENDRIER(DATEDUJOUR DATETIME);
INSERT INTO CALENDRIER(DATEDUJOUR) VALUES ('11/01/2000');
INSERT INTO CALENDRIER(DATEDUJOUR) VALUES ('12/01/2000');
INSERT INTO CALENDRIER(DATEDUJOUR) VALUES ('13/01/2000');
INSERT INTO CALENDRIER(DATEDUJOUR) VALUES ('14/01/2000');
 
SELECT DATEDUJOUR,CHB_NUM,'TRUE'  
FROM T_CHAMBRE_CHB,CALENDRIER  
WHERE CHB_NUM NOT IN  
( SELECT CHB_NUM  
FROM T_PLANNING_PLN  
WHERE PLN_LIBRE = 'FALSE'  
AND  PLN_JOUR=CALENDRIER.DATEDUJOUR)
UNION
SELECT CALENDRIER.DATEDUJOUR,CHB_NUM,'FALSE'  
FROM T_CHAMBRE_CHB ,CALENDRIER
WHERE CHB_NUM IN  
( SELECT CHB_NUM  
FROM T_PLANNING_PLN  
WHERE PLN_LIBRE = 'FALSE'  
AND  PLN_JOUR=CALENDRIER.DATEDUJOUR)

Laisser un commentaire