-- Ex 6

-- 1
-- les noms d’hôtels et leur ville
SELECT nom, ville FROM hotel ;


-- 2
-- les noms d’hôtels ayant 3 étoiles ou plus
SELECT nom FROM hotel WHERE etoiles >= 3 ;

-- 3
-- Listez les hôtels sur lesquels portent une réservation

SELECT DISTINCT numhotel, nom, ville FROM hotel NATURAL JOIN reservation ;

SELECT DISTINCT * FROM
hotel
WHERE EXISTS
(
 SELECT 1 FROM reservation
 WHERE reservation.numhotel= hotel.numhotel
 )	   
;

-- 4
-- les clients ayant déjà occupé (ou en cours d’occupation d’) une chambre.

-- Pas bonne repose mais interressante
SELECT DISTINCT client.* FROM
client NATURAL JOIN reservation
UNION
SELECT DISTINCT client.* FROM
client NATURAL JOIN occupation
ORDER BY nom 
;

SELECT DISTINCT client.* FROM
client NATURAL JOIN occupation
;

-- 5
-- our chaque hôtel, indiquer le nombre total de chambres.
 SELECT nom, ville, COUNT(numchambre)
 FROM hotel natural join chambre
 GROUP BY numhotel ;

-- 6
-- Quels sont tous ceux qui ont été, ou sont encore,
-- clients de l’hôtel des voyageurs à Nice ?
-- 

WITH allOccRes AS
(SELECT numclient, numhotel FROM occupation
UNION
SELECT numclient, numhotel FROM reservation)
SELECT
client.*
FROM
client JOIN allOccRes USING (numclient)
JOIN hotel USING (numhotel)
WHERE
hotel.nom = 'Hotel des voyageurs'
AND
hotel.ville = 'Nice' ;

-- 7
-- Combien de chambres triples possède l’Hôtel des ambassadeurs à Grenoble ?

SELECT count(numchambre)
FROM chambre NATURAL JOIN hotel
WHERE
type = 'triple'
AND nom = 'Hotel des ambassadeurs'
AND ville = 'Grenoble'
;

-- 8 
-- Pour chaque client, lister le nom et la ville des hôtels dans
-- lesquels il a séjourné, ou séjourne encore, avec les dates
-- d’arrivée et de départ correspondant à chaque séjour

SELECT client.*, hotel.nom, ville, datearrivee, datedepart
FROM
client JOIN occupation USING (numclient)
JOIN hotel USING (numhotel)
 ;

-- 9
-- les chambres (hôtel et numéro) avec une colonne
-- supplémentaire avec le numéro de jours pendant lesquels elles ont
-- été occupés
    
SELECT
numchambre, nom, ville, numhotel, COUNT(julianday(datedepart) - julianday(datearrivee)) AS numdays
FROM
chambre NATURAL JOIN  hotel
JOIN occupation USING (numchambre,numhotel)
GROUP BY
numchambre, numhotel
;

-- 10 Si la TVA est de 19.6\%, quel sera le montant de chaque séjour
-- que le client Jean Nemarre a eu à régler à la date du 21/01/2015
-- (on ne compte donc pas un éventuel séjour actuel non terminé) ?

WITH
occsDeJean AS
(SELECT * FROM client NATURAL JOIN occupation
WHERE
nom='Nemarre' AND prenom='Jean'
AND
datedepart  < '2015-01-21'
),
chambrehotel AS
(SELECT * FROM chambre NATURAL JOIN hotel)
SELECT
chambrehotel.nom, chambrehotel.ville,
prixnuitht*(julianday(datedepart) -  julianday(datearrivee))*(100 - 19.6)/100
as montant
FROM
occsDeJean JOIN chambreHotel USING (numchambre,numhotel)
;

-- 11
-- les numéros de chambres disponibles dans l’hôtel de la gare, à
-- Bordeaux, pour les nuits du 12 avril 2014 au 17 avril 2014.
-- Pas nécessairement la meilleure réponse

WITH chambresAMonHotel AS
(SELECT chambre.*
FROM chambre NATURAL JOIN hotel
WHERE
ville = 'Bordeaux'
AND
nom LIKE '%gare%'
),
occChambresAMonHotel AS
(SELECT numchambre
FROM chambre NATURAL JOIN hotel
JOIN reservation USING (numchambre,numhotel)
WHERE
ville = 'Bordeaux'
AND
nom LIKE '%gare%'
AND
(
date(datearrivee) BETWEEN date('2014-04-12') AND date('2017-04-12')
OR
date(datedepart) BETWEEN date('2014-04-12') AND date('2017-04-12')
)
)
SELECT *
FROM chambresAMonHotel 
WHERE 
numchambre NOT IN occChambresAMonHotel
;

