AXSYffua

1# 1. Sporządź listę pokoi które były wolne więcej niż 10 dni w maju 2006r.
2SELECT r.id_pokoju
3FROM rezerwacje r
4WHERE (r.data_przyjazdu>='2006-05-01' AND r.data_wyjazdu<='2006-05-31')
5GROUP BY 1
6HAVING SUM(DATEDIFF(data_wyjazdu, data_przyjazdu)) <= 21
7UNION
8SELECT id_pokoju
9FROM rezerwacje
10WHERE (data_przyjazdu >= '2006-05-10' AND data_wyjazdu > '2006-05-31') 
11OR (data_przyjazdu < '2006-05-01' AND data_wyjazdu <= '2006-05-21')
12GROUP BY 1
13UNION
14SELECT numer
15FROM pokoje
16WHERE numer NOT IN (SELECT id_pokoju FROM rezerwacje)
17GROUP BY 1
18ORDER BY 1;
19# 2. Wypisz nazwiska wszystkich gości którzy mieszkali w pokojach  osobowych od 1 stycznia 2007 do 29 stycznia 2007.
20SELECT DISTINCT k.nazwisko FROM rezerwacje r, statusy s, klienci k, pokoje p, typy_pokoi t
21WHERE r.data_przyjazdu >= '2007-01-01' AND r.data_wyjazdu <= '2007-01-29' AND s.nazwa = 'Klient wyjechal' AND t.ilosc_osob = 1;
22    
23# 3. Sporządź zestawienie wykorzystania (zajętości) poszczególnych pokoi za ostatni kwartał.
24SELECT numer, SUM(DATEDIFF(data_wyjazdu, data_przyjazdu))
25FROM Rezerwacje
26LEFT JOIN Pokoje ON rezerwacje.id_pokoju = pokoje.numer
27WHERE data_przyjazdu>=DATE_SUB(NOW(), INTERVAL 1 QUARTER)
28AND data_wyjazdu<=NOW() GROUP BY 1;
29
30# 4. Dokonaj rezerwacji, przypisując wolny pokój zgodnie z życzeniami gościa.
31/* Krok 1 – sprawdzenie czy klient już jest w bazie */
32SELECT id 
33FROM Klienci 
34WHERE imie='Jan' AND nazwisko='Nowak';
35        
36/* Krok 2 – w przypadku braku id dodać klienta do bazy */
37INSERT INTO Klienci (imie, nazwisko) VALUES ('Jan', 'Nowak');
38        
39/* Krok 3 – wyszukanie wolnego pokoju */
40SELECT MIN(numer) 
41FROM Pokoje p 
42INNER JOIN Typy_pokoi t ON p.typ_id = t.typ_id 
43WHERE t.ilosc_osob = 1 AND numer NOT IN 
44(SELECT numer 
45 FROM Rezerwacje 
46 WHERE data_przyjazdu >= '2007-07-07' 
47 AND data_wyjazdu < '2007-07-07');
48        
49/* Krok 4 – dodanie rezerwacji */
50INSERT INTO Rezerwacje (id_klienta, id_pokoju, cena_calkowita, data_rezerwacji, data_przyjazdu, data_wyjazdu, status_id) 
51VALUES (1, 10, 1200, NOW(), '2007-07-07', 
52'2007-07-08', 1 /* Status Id 1 = Oczekiwanie na klienta */);
53# 5. Sprawdź, ile razy dana osoba była gościem w ostatnich dwóch latach.
54SELECT COUNT(r.id_rezerwacji)
55FROM Rezerwacje r 
56INNER JOIN Klienci k ON r.id_klienta = k.id 
57INNER JOIN Statusy s ON r.status_id = s.id
58WHERE k.imie='Jan' AND k.nazwisko='Nowak' 
59AND r.data_przyjazdu > DATE_SUB (NOW(), INTERVAL 2 YEAR) AND s.nazwa='Zakonczona';
60# 6. Jest godzina 20. Sprawdź, którzy goście przybyli do hotelu zgodnie z rezerwacją.
61SELECT k.imie, k.nazwisko, k.id
62FROM Rezerwacje r, Klienci k, Statusy s
63WHERE data_przyjazdu ='2018-04-30 20:00' AND s.nazwa='Klient w hotelu';
64# 7. Sporządź zestawienie opłat za poszczególne kategorie pokoi za ostatni miesiąc.
65SELECT t.nazwa_typu, SUM(r.cena_calkowita)
66FROM Rezerwacje r 
67INNER JOIN Pokoje p ON r.id_pokoju = p.numer 
68INNER JOIN Typy_pokoi t ON p.typ_id = t.typ_id
69WHERE data_przyjazdu>=DATE_SUB(NOW(), INTERVAL 1 MONTH) 
70AND data_wyjazdu<=NOW()
71GROUP BY 1;
72# 8. Pokaż wszystkie wolne i zajęte pokoje wg. kategorii.
73/* Krok 1 – lista wolnych pokoi wg. kategorii */
74DROP TABLE IF EXISTS wolne_pokoje;
75CREATE TABLE wolne_pokoje AS
76(SELECT p.typ_id, COUNT(*) AS ilosc
77 FROM Rezerwacje r 
78 INNER JOIN Pokoje p ON r.id_pokoju = p.numer
79 WHERE NOW() NOT BETWEEN data_przyjazdu AND data_wyjazdu  GROUP BY 1);
80 8. Pokaż wszystkie wolne i zajęte pokoje wg. kategorii.
81/* Krok 1 – lista wolnych pokoi wg. kategorii */
82DROP TABLE IF EXISTS wolne_pokoje;
83CREATE TABLE wolne_pokoje AS
84(SELECT p.typ_id, COUNT(*) AS ilosc
85 FROM Rezerwacje r
86 INNER JOIN Pokoje p ON r.id_pokoju = p.numer
87 WHERE NOW() NOT BETWEEN data_przyjazdu AND data_wyjazdu  GROUP BY 1);
88/* Krok 2 – lista zajętych pokoi wg. kategorii */ DROP TABLE IF EXISTS zajete_pokoje;
89CREATE TABLE zajete_pokoje AS
90(SELECT p.typ_id, COUNT(*) AS ilosc
91 FROM Rezerwacje r
92 INNER JOIN Pokoje p ON r.id_pokoju = p.numer
93 WHERE NOW() BETWEEN data_przyjazdu AND data_wyjazdu  GROUP BY 1);
94       
95/* Krok 3 – zestawienie kategorii */
96SELECT nazwa_typu, w.ilosc AS liczba_wolnych, z.ilosc AS liczba_zajetych
97FROM Typy_pokoi t, Wolne_pokoje w, Zajete_pokoje z
98WHERE t.typ_id=w.typ_id AND t.typ_id=w.typ_id;
99       
100# 9. Zwolnij wolny pokój i wypisz rachunek.
101UPDATE Rezerwacje
102SET status_id=5 /* Wyjechal */ WHERE id_rezerwacji=4;
103SELECT cena_calkowita
104FROM Rezerwacje
105WHERE id_rezerwacji=4;