-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathNancyYoon-HotelQueries.sql
171 lines (132 loc) · 4.93 KB
/
NancyYoon-HotelQueries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
USE HotelReservation;
-- #1
-- Displays the name, address, and phone number of a guest based on their phone number
SELECT CONCAT(FirstName, ' ', LastName) `Name`, CONCAT(Address, ', ', City, ', ', State, ' ', Zip) Address, Phone
FROM Guest
WHERE Phone = '(917) 123-4567';
/*
Name Address Phone
Nancy Yoon 2020 Broadway, New York, NY 10023 (917) 123-4567
*/
-- #2
-- Returns a list of reservations that end in July 2023,
-- including the name of the guest, the room number(s), and the reservation dates
SELECT * FROM Reservation;
SELECT CONCAT(FirstName, ' ', LastName) `Name`, RoomNumber Room, StartDate `From`, EndDate `To`
FROM Reservation
INNER JOIN Guest ON Reservation.GuestID = Guest.GuestID
WHERE EndDate BETWEEN '2023-07-01' AND '2023-07-31';
/*
Name Room From To
1 Nancy Yoon 205 2023-06-28 2023-07-02
2 Walter Holaway 204 2023-07-13 2023-07-14
3 Wilfred Vise 401 2023-07-18 2023-07-21
4 Bettyann Seery 303 2023-07-28 2023-07-29
*/
-- #3
-- Returns a list of all reservations for rooms with a jacuzzi,
-- displaying the guest's name, the room number, and the dates of the reservation
SELECT CONCAT(FirstName, ' ', LastName) `Name`, Reservation.RoomNumber Room, StartDate `From`, EndDate `To`
FROM Reservation
JOIN Guest ON Reservation.GuestID = Guest.GuestID
JOIN Room ON Reservation.RoomNumber = Room.RoomNumber
JOIN RoomAmenity ON Room.RoomNumber = RoomAmenity.RoomNumber
WHERE AmenityID = 2;
/*
Name Room From To
1 'Karie Yang', '201', '2023-03-06', '2023-03-07'
2 'Bettyann Seery', '203', '2023-02-05', '2023-02-10'
3 'Karie Yang', '203', '2023-09-13', '2023-09-15'
4 'Nancy Yoon', '205', '2023-06-28', '2023-07-02'
5 'Wilfred Vise', '207', '2023-04-23', '2023-04-24'
6 'Walter Holaway', '301', '2023-04-09', '2023-04-13'
7 'Mack Simmer', '301', '2023-11-22', '2023-11-25'
8 'Bettyann Seery', '303', '2023-07-28', '2023-07-29'
9 'Duane Cullison', '305', '2023-02-22', '2023-02-24'
10 'Bettyann Seery', '305', '2023-08-30', '2023-09-01'
11 'Nancy Yoon', '307', '2023-03-17', '2023-03-20'
*/
-- #4
-- Returns all the rooms reserved for a specific guest,
-- including the guest's name, the room(s) reserved, the starting date of the reservation,
-- and how many people were included in the reservation
SELECT CONCAT(FirstName, ' ', LastName) `Name`, RoomNumber Room, StartDate `From`, (Adult + Children) NumberOfPeople
FROM Reservation
JOIN Guest ON Reservation.GuestID = Guest.GuestID
WHERE FirstName = 'Mack' AND LastName = 'Simmer';
/*
Name Room From NumberOfPeople
1 'Mack Simmer', '308', '2023-02-02', '1'
2 'Mack Simmer', '208', '2023-09-16', '2'
3 'Mack Simmer', '206', '2023-11-22', '2'
4 'Mack Simmer', '301', '2023-11-22', '4'
*/
-- #5
-- Returns a list of rooms, reservation ID, and per-room cost for each reservation.
-- The results should include all rooms,
-- whether or not there is a reservation associated with the room
SELECT IFNULL(Reservation.RoomNumber, Room.RoomNumber) Room, ReservationID, CONCAT('$', FORMAT(TotalRoomCost, 2)) Cost
FROM Reservation
RIGHT JOIN Room ON Reservation.RoomNumber = Room.RoomNumber
JOIN RoomType ON Room.RoomTypeID = RoomType.RoomTypeID
ORDER BY Room.RoomNumber;
/*
Room ReservationID Cost
1 '201', '4', '$199.99'
2 '202', '7', '$349.98'
3 '203', '2', '$999.95'
4 '203', '21', '$399.98'
5 '204', '16', '$184.99'
6 '205', '15', '$699.96'
7 '206', '12', '$599.96'
8 '206', '23', '$449.97'
9 '207', '10', '$174.99'
10 '208', '13', '$599.96'
11 '208', '20', '$149.99'
12 '301', '9', '$799.96'
13 '301', '24', '$599.97'
14 '302', '6', '$924.95'
15 '302', '25', '$699.96'
16 '303', '18', '$199.99'
17 '304', '14', '$184.99'
18 '305', '3', '$349.98'
19 '305', '19', '$349.98'
20 '306', NULL, NULL
21 '307', '5', '$524.97'
22 '308', '1', '$299.98'
23 '401', '11', '$1,199.97'
24 '401', '17', '$1,259.97'
25 '401', '22', '$1,199.97'
26 '402', NULL, NULL
*/
-- #6
-- Returns all the rooms accommodating at least three guests and that are reserved on any date in April 2023
SELECT ReservationID, RoomNumber, (Adult + Children) NumberOfPeople, StartDate `From`, EndDate `To`
FROM Reservation
WHERE (Adult + Children) >= 3 AND EndDate BETWEEN '2023-04-01' AND '2023-04-30';
/*
0 result because there is no reservation with such conditions
*/
-- #7
-- Returns a list of all guest names and
-- the number of reservations per guest, sorted starting with the guest
-- with the most reservations and then by the guest's last name
SELECT CONCAT(FirstName, ' ', LastName) `Name`, COUNT(ReservationID) NumberOfReservations
FROM Reservation
JOIN Guest ON Reservation.GuestID = Guest.GuestID
GROUP BY CONCAT(FirstName, ' ', LastName)
ORDER BY COUNT(ReservationID) DESC, CONCAT(FirstName, ' ', LastName);
/*
Name NumberOfReservations
1 'Mack Simmer', '4'
2 'Bettyann Seery', '3'
3 'Aurore Lipton', '2'
4 'Duane Cullison', '2'
5 'Joleen Tison', '2'
6 'Karie Yang', '2'
7 'Maritza Tilton', '2'
8 'Nancy Yoon', '2'
9 'Walter Holaway', '2'
10 'Wilfred Vise', '2'
11 'Zachery Luechtefeld', '1'
*/