· 5 years ago · Jun 21, 2020, 12:44 PM
1CREATE DATABASE TripService
2
3USE TripService
4
5--1.Database design
6CREATE TABLE Cities(
7 Id INT PRIMARY KEY IDENTITY,
8 [Name] NVARCHAR(20) NOT NULL,
9 CountryCode CHAR(2) NOT NULL
10)
11
12CREATE TABLE Hotels(
13 Id INT PRIMARY KEY IDENTITY,
14 [Name] NVARCHAR(30) NOT NULL,
15 CityId INT NOT NULL REFERENCES Cities(Id),
16 EmployeeCount INT NOT NULL,
17 BaseRate DECIMAL (18, 2)
18)
19
20CREATE TABLE Rooms(
21 Id INT PRIMARY KEY IDENTITY,
22 Price DECIMAL(18, 2) NOT NULL,
23 [Type] NVARCHAR(20) NOT NULL,
24 Beds INT NOT NULL,
25 HotelId INT NOT NULL REFERENCES Hotels(Id)
26)
27
28CREATE TABLE Trips(
29 Id INT PRIMARY KEY IDENTITY,
30 RoomId INT NOT NULL REFERENCES Rooms(Id),
31 BookDate DATE NOT NULL,
32 ArrivalDate DATE NOT NULL,
33 ReturnDate DATE NOT NULL,
34 CancelDate DATE,
35 CHECK(BookDate < ArrivalDate),
36 CHECK(ArrivalDate < ReturnDate)
37)
38
39CREATE TABLE Accounts(
40 Id INT PRIMARY KEY IDENTITY,
41 FirstName NVARCHAR(50) NOT NULL,
42 MiddleName NVARCHAR(20),
43 LastName NVARCHAR(50) NOT NULL,
44 CityId INT NOT NULL REFERENCES Cities(Id),
45 BirthDate DATE NOT NULL,
46 Email VARCHAR(100) NOT NULL UNIQUE
47)
48
49CREATE TABLE AccountsTrips(
50 AccountId INT NOT NULL REFERENCES Accounts(Id),
51 TripId INT NOT NULL REFERENCES Trips(Id),
52 Luggage INT NOT NULL,
53 PRIMARY KEY(AccountId, TripId),
54 CHECK(Luggage >= 0)
55)
56
57--2.Insert
58INSERT INTO Accounts(FirstName, MiddleName, LastName, CityId, BirthDate, Email)
59 VALUES ('John', 'Smith', 'Smith', 34, '1975-07-21', 'j_smith@gmail.com'),
60 ('Gosho', NULL, 'Petrov', 11, '1978-05-16', 'g_petrov@gmail.com'),
61 ('Ivan', 'Petrovich', 'Pavlov', 59, '1849-09-26', 'i_pavlov@softuni.bg'),
62 ('Friedrich', 'Wilhelm', 'Nietzsche', 2, '1844-10-15', 'f_nietzsche@softuni.bg')
63
64INSERT INTO Trips(RoomId, BookDate, ArrivalDate, ReturnDate, CancelDate)
65 VALUES (101, '2015-04-12', '2015-04-14', '2015-04-20', '2015-02-02'),
66 (102, '2015-07-07', '2015-07-15', '2015-07-22', '2015-04-29'),
67 (103, '2013-07-17', '2013-07-23', '2013-07-24', NULL),
68 (104, '2012-03-17', '2012-03-31', '2012-04-01', '2012-01-10'),
69 (109, '2017-08-07', '2017-08-28', '2017-08-29', NULL)
70
71--3.Update
72UPDATE Rooms
73SET Price *= 1.14
74WHERE HotelId IN (5, 7 ,9)
75
76--4.Delete
77DELETE
78FROM AccountsTrips
79WHERE AccountId = 47
80
81--5.EEE-Mails
82SELECT a.FirstName,
83 a.LastName,
84 FORMAT(BirthDate, 'MM-dd-yyyy') AS BirthDate,
85 c.Name,
86 a.Email
87FROM Accounts AS a
88JOIN Cities AS c ON a.CityId = c.Id
89WHERE Email LIKE 'e%'
90ORDER BY c.Name
91
92--6.City Statistics
93SELECT c.Name AS City,
94 COUNT(*) AS Hotels
95FROM Cities AS c
96JOIN Hotels AS h ON c.Id = h.CityId
97GROUP BY c.Name
98ORDER BY Hotels DESC,
99 c.Name
100
101--7.Longest and Shortest Trips
102SELECT Id,
103 FullName,
104 MAX(Days) AS LongestTrip,
105 MIN(Days) AS ShortesTrip
106FROM (
107 SELECT DATEDIFF(DAY, t.ArrivalDate, t.ReturnDate) AS [Days],
108 a.Id,
109 CONCAT(a.FirstName, ' ', a.LastName) AS FullName
110 FROM Accounts AS a
111 JOIN AccountsTrips AS at ON a.Id = at.AccountId
112 JOIN Trips AS t on at.TripId = t.Id
113 WHERE a.MiddleName IS NULL AND t.CancelDate IS NULL
114 ) AS AccountAndTripsInDays
115GROUP BY Id, FullName
116ORDER BY LongestTrip DESC,
117 ShortesTrip
118
119--8.Metropolis
120SELECT TOP(10) c.Id,
121 c.Name AS City,
122 c.CountryCode AS Country,
123 COUNT(*) AS Accounts
124FROM Cities AS c
125JOIN Accounts AS a ON c.Id = a.CityId
126GROUP BY c.Id, c.Name, c.CountryCode
127ORDER BY Accounts DESC
128
129--9.Romantic Getaways
130SELECT a.Id,
131 a.Email,
132 c.Name AS City,
133 COUNT(*) AS Trips
134FROM Accounts AS a
135JOIN AccountsTrips AS at ON a.Id = at.AccountId
136JOIN Trips AS t ON at.TripId = t.Id
137JOIN Rooms AS r ON t.RoomId = r.Id
138JOIN Hotels AS h ON r.HotelId = h.Id
139JOIN Cities AS c ON h.CityId = c.Id
140WHERE a.CityId = h.CityId
141GROUP BY a.Id, a.Email, c.Name
142ORDER BY Trips DESC,
143 a.Id
144
145--10.GDPR Violation
146SELECT t.Id,
147 CASE
148 WHEN a.MiddleName IS NULL THEN CONCAT(a.FirstName, ' ', a.LastName)
149 ELSE CONCAT(a.FirstName, ' ', a.MiddleName, ' ', a.LastName)
150 END AS [Full Name],
151 ac.[Name] AS [From],
152 hc.[Name] AS [To],
153 CASE
154 WHEN t.CancelDate IS NOT NULL THEN 'Canceled'
155 ELSE CAST(DATEDIFF(DAY, t.ArrivalDate, t.ReturnDate) AS VARCHAR(10)) + ' days'
156 END AS Duration
157FROM AccountsTrips AS at
158JOIN Accounts AS a ON at.AccountId = a.Id
159JOIN Cities AS ac ON a.CityId = ac.Id
160JOIN Trips AS t ON at.TripId = t.Id
161JOIN Rooms AS r ON t.RoomId = r.Id
162JOIN Hotels AS h ON r.HotelId = h.Id
163JOIN Cities AS hc ON h.CityId = hc.Id
164ORDER BY [Full Name], t.Id
165
166--11.Available Room
167GO
168
169CREATE FUNCTION udf_GetAvailableRoom(@HotelId INT, @Date DATE, @People INT)
170RETURNS VARCHAR(MAX)
171AS
172BEGIN
173 DECLARE @result VARCHAR(MAX)
174
175 IF EXISTS (
176 SELECT *
177 FROM Rooms AS r
178 JOIN Trips AS t ON r.Id = t.RoomId
179 WHERE r.Id NOT IN (
180 SELECT r.Id FROM Rooms AS r
181 JOIN Trips AS t ON r.Id = t.RoomId
182 WHERE @Date BETWEEN t.ArrivalDate AND t.ReturnDate
183 )
184 AND (t.CancelDate IS NULL)
185 AND (r.Beds >= @People)
186 AND (r.HotelId = @HotelId)
187 )
188 BEGIN
189 DECLARE @RoomNumber INT = (SELECT TOP(1) r.Id
190 FROM Rooms AS r
191 JOIN Trips AS t ON r.Id = t.RoomId
192 WHERE r.Id NOT IN (
193 SELECT r.Id FROM Rooms AS r
194 JOIN Trips AS t ON r.Id = t.RoomId
195 WHERE @Date BETWEEN t.ArrivalDate AND t.ReturnDate
196 )
197 AND (t.CancelDate IS NULL)
198 AND (r.Beds >= @People)
199 AND (r.HotelId = @HotelId)
200 ORDER BY r.Price DESC
201 )
202
203 DECLARE @RoomBeds INT = (SELECT TOP(1) Beds FROM Rooms WHERE Id = @RoomNumber)
204
205 DECLARE @RoomType VARCHAR(MAX) = (SELECT TOP(1) [Type] FROM Rooms WHERE Id = @RoomNumber)
206
207 DECLARE @RoomPrice DECIMAL(18, 2) = (SELECT TOP(1) Price FROM Rooms WHERE Id = @RoomNumber)
208
209 DECLARE @HotelBaseRate DECIMAL(18, 2) = (SELECT TOP(1) BaseRate FROM Hotels WHERE Id = @HotelId)
210
211 DECLARE @TotalPrice DECIMAL(18, 2) = (@HotelBaseRate + @RoomPrice) * @People
212
213 SET @result = CONCAT('Room ', @RoomNumber, ': ', @RoomType, ' (', @RoomBeds, ' beds',')',' - ','$', @TotalPrice)
214 END
215
216 ELSE
217 BEGIN
218 SET @result = 'No rooms available'
219 END
220
221 RETURN @result
222END
223
224GO
225
226--12.Switch Room
227GO
228
229CREATE PROC usp_SwitchRoom @TripId INT, @TargetRoomId INT
230AS
231BEGIN
232 DECLARE @CurrentRoom INT = (SELECT TOP(1) RoomId FROM Trips WHERE Id = @TripId)
233
234 IF (SELECT TOP(1) HotelId FROM Rooms WHERE Id = @CurrentRoom) !=
235 (SELECT TOP(1) HotelId FROM Rooms WHERE Id = @TargetRoomId)
236 BEGIN
237 THROW 51000, 'Target room is in another hotel!', 1
238 END
239
240 IF (SELECT Beds FROM Rooms WHERE Id = @TargetRoomId) <
241 (
242 SELECT COUNT(*) AS AccountsOnTrip
243 FROM Trips AS t
244 JOIN AccountsTrips AS at ON t.Id = at.TripId
245 WHERE t.Id = @TripId
246 GROUP BY t.Id
247 )
248 BEGIN
249 THROW 51001, 'Not enough beds in target room!', 1
250 END
251
252 UPDATE Trips
253 SET RoomId = @TargetRoomId
254 WHERE Id = @TripId
255END
256
257GO