· 8 years ago · Aug 07, 2018, 05:32 PM
1--02. Insert
2INSERT INTO Employees (FirstName, LastName, Phone, Salary) VALUES
3 ('Stoyan', 'Petrov', '888-785-8573', 500.25),
4 ('Stamat', 'Nikolov', '789-613-1122', 999995.25),
5 ('Evgeni', 'Petkov', '645-369-9517', 1234.51),
6 ('Krasimir', 'Vidolov', '321-471-9982', 50.25)
7
8INSERT INTO Items (Name, Price, CategoryId) VALUES
9 ('Tesla battery',154.25 ,8),
10 ('Chess', 30.25, 8),
11 ('Juice', 5.32,1),
12 ('Glasses',10, 8),
13 ('Bottle of water', 1, 1)
14
15--03. Update
16UPDATE Items
17SET Price *= 1.27
18WHERE CategoryId IN (1, 2, 3)
19
20--04. Delete
21
22DELETE FROM OrderItems
23WHERE OrderID = 48
24
25--5. Richest People
26SELECT Id,FirstName
27FROM Employees
28WHERE Salary > 6500
29ORDER BY FirstName, Id
30
31--6. Cool Phone Numbers
32SELECT
33 FirstName + ' ' + LastName AS [Full Name],
34 Phone
35FROM Employees
36WHERE Phone LIKE '3%'
37ORDER BY FirstName, Phone
38
39--7. Employee Statistics
40SELECT
41 FirstName,
42 LastName,
43 COUNT(o.Id) AS [Count]
44FROM Employees AS e
45 JOIN Orders AS o ON o.EmployeeId = e.Id
46GROUP BY FirstName, LastName
47ORDER BY [Count] DESC, FirstName
48
49
50--8. Hard Workers Club
51SELECT
52 FirstName,
53 LastName,
54 AVG(DATEDIFF(HOUR, s.CheckIn, s.CheckOut)) AS [Work Hours]
55FROM Employees AS e
56 JOIN Shifts AS s ON s.EmployeeId = e.Id
57GROUP BY FirstName, LastName, e.Id
58HAVING AVG(DATEDIFF(HOUR, s.CheckIn, s.CheckOut)) > 7
59ORDER BY [Work Hours] DESC, e.Id
60
61--9. The Most Expensive Order (2)
62SELECT TOP (1)
63 o.Id,
64 SUM(i.Price * oi.Quantity) AS [TotalPrice]
65FROM Orders As o
66 JOIN OrderItems AS oi ON oi.OrderId = o.Id
67 JOIN Items As i ON i.Id = oi.ItemId
68GROUP BY o.Id
69ORDER BY TotalPrice DESC
70
71--10. Rich Item, Poor Item
72SELECT TOP (10)
73 o.Id,
74 MAX(i.Price) AS [ExpensivePrice],
75 MIN(i.Price) [CheapPrice]
76FROM Orders AS o
77 JOIN OrderItems AS oi ON oi.OrderId = o.Id
78 JOIN Items AS i ON i.Id = oi.ItemId
79GROUP BY o.Id
80ORDER BY ExpensivePrice DESC, o.Id ASC
81
82-- 11. Cashiers
83SELECT DISTINCT
84 e.Id,
85 e.FirstName,
86 e.LastName
87FROM Employees AS e
88 RIGHT JOIN Orders AS o ON o.EmployeeId = e.Id
89ORDER BY e.Id
90
91-- 12.Lazy Employees
92SELECT DISTINCT
93 e.Id,
94 FirstName + ' ' + e.LastName AS [Full Name]
95FROM Employees AS e
96 JOIN Shifts AS s ON s.EmployeeId = e.Id
97WHERE DATEDIFF(HOUR, s.CheckIn, s.CheckOut) < 4
98ORDER BY e.Id
99
100--13. Sellers
101SELECT
102 e.FirstName + ' ' + e.LastName AS [Full Name],
103 SUM(i.Price * oi.Quantity) AS [Total Count],
104 COUNT(i.Id) AS [Items]
105FROM Orders o
106 JOIN OrderItems oi on o.Id = oi.OrderId
107 JOIN Items i on i.Id = oi.ItemId
108 JOIN Employees e on e.Id = o.EmployeeId
109WHERE o.DateTime < '2018-06-15'
110GROUP BY e.FirstName, e.LastName
111ORDER BY [Total Count] DESC, Items DESC
112
113
114--14 Tough days
115SELECT
116 e.FirstName + ' ' + e.LastName AS FullName,
117 CASE
118 WHEN DATEPART(WEEKDAY, s.CheckIn) = 1
119 THEN 'Monday'
120 WHEN DATEPART(WEEKDAY, s.CheckIn) = 2
121 THEN 'Tuesday'
122 WHEN DATEPART(WEEKDAY, s.CheckIn) = 3
123 THEN 'Wednesday'
124 WHEN DATEPART(WEEKDAY, s.CheckIn) = 4
125 THEN 'Thursday'
126 WHEN DATEPART(WEEKDAY, s.CheckIn) = 5
127 THEN 'Friday'
128 WHEN DATEPART(WEEKDAY, s.CheckIn) = 6
129 THEN 'Saturday'
130 WHEN DATEPART(WEEKDAY, s.CheckIn) = 7
131 THEN 'Sunday'
132 END as DayOfWeek
133FROM Employees AS e
134 LEFT JOIN Orders AS o ON o.EmployeeId = e.Id
135 JOIN Shifts AS s ON s.EmployeeId = e.Id
136WHERE o.Id IS NULL AND DATEDIFF(HOUR, s.CheckIn, s.CheckOut) > 12
137ORDER BY e.Id
138
139--15 No name -- missing description
140SELECT
141 e.FullName,
142 e.WorkHours,
143 e.TotalOrderSum
144FROM (
145 SELECT
146 e.FirstName + ' ' + e.LastName AS FullName,
147 DATEDIFF(HOUR, s.CheckIn, s.CheckOut) AS WorkHours,
148 SUM(i.Price * oi.Quantity) AS TotalOrderSum,
149 ROW_NUMBER()
150 OVER ( PARTITION BY e.FirstName, e.LastName
151 ORDER BY e.FirstName, e.LastName, SUM(i.Price * oi.Quantity) DESC ) AS Rank
152 FROM Items AS i
153 JOIN OrderItems AS oi on oi.ItemId = i.Id
154 JOIN Orders AS o ON o.Id = oi.OrderId
155 JOIN Employees AS e ON e.Id = o.Id
156 JOIN Shifts AS s ON s.EmployeeId = e.Id
157 GROUP BY e.FirstName, e.LastName, DATEDIFF(HOUR, s.CheckIn, s.CheckOut)
158 ) AS e
159WHERE e.Rank = 1
160ORDER BY e.FullName, e.WorkHours DESC, e.TotalOrderSum DESC
161
162
163--16 Average profit per day
164SELECT
165 e.DayOfMonth,
166 CAST(AVG(e.TotalPrice) AS decimal(15, 2)) AS AveragePricePerDay
167FROM (
168 SELECT
169 DATEPART(DAY, o.DateTime) AS [DayOfMonth],
170 SUM(i.Price * oi.Quantity) AS TotalPrice
171 FROM Orders AS o
172 JOIN OrderItems AS oi ON oi.OrderId = o.Id
173 JOIN Items AS i ON i.Id = oi.ItemId
174 GROUP BY DATEPART(DAY, o.DateTime)
175 ) AS e
176GROUP BY e.DayOfMonth
177ORDER BY e.DayOfMonth ASC
178
179--17
180SELECT
181 i.Name,
182 c.Name,
183 COUNT(o.Id) As [Count],
184 SUM(i.Price) AS TotalPrice
185FROM Orders AS o
186 JOIN OrderItems AS oi ON oi.OrderId = o.Id
187 RIGHT JOIN Items AS i ON i.Id = oi.ItemId
188 JOIN Categories AS c ON c.Id = i.CategoryId
189GROUP BY i.Name, c.Name
190ORDER BY TotalPrice DESC, [Count] DESC
191
192--18 Promotion day
193CREATE FUNCTION udf_GetPromotedProducts(@CurrentDate DATETIME, @StartDate DATETIME, @EndDate DATETIME, @Discount INT, @FirstItemId INT, @SecondItemId INT, @ThirdItemId INT)
194RETURNS VARCHAR(80)
195AS
196BEGIN
197 DECLARE @FirstItemPrice DECIMAL(15,2) = (SELECT Price FROM Items WHERE Id = @FirstItemId)
198 DECLARE @SecondItemPrice DECIMAL(15,2) = (SELECT Price FROM Items WHERE Id = @SecondItemId)
199 DECLARE @ThirdItemPrice DECIMAL(15,2) = (SELECT Price FROM Items WHERE Id = @ThirdItemId)
200
201 IF (@FirstItemPrice IS NULL OR @SecondItemPrice IS NULL OR @ThirdItemPrice IS NULL)
202 BEGIN
203 RETURN 'One of the items does not exists!'
204 END
205
206 IF (@CurrentDate <= @StartDate OR @CurrentDate >= @EndDate)
207 BEGIN
208 RETURN 'Current date is not within promotion dates!'
209 END
210
211 DECLARE @NewFirstItemPrice DECIMAL(15,2) = @FirstItemPrice - (@FirstItemPrice * @Discount / 100)
212 DECLARE @NewSecondItemPrice DECIMAL(15,2) = @SecondItemPrice - (@SecondItemPrice * @Discount / 100)
213 DECLARE @NewThirdItemPrice DECIMAL(15,2) = @ThirdItemPrice - (@ThirdItemPrice * @Discount / 100)
214
215 DECLARE @FirstItemName VARCHAR(50) = (SELECT [Name] FROM Items WHERE Id = @FirstItemId)
216 DECLARE @SecondItemName VARCHAR(50) = (SELECT [Name] FROM Items WHERE Id = @SecondItemId)
217 DECLARE @ThirdItemName VARCHAR(50) = (SELECT [Name] FROM Items WHERE Id = @ThirdItemId)
218
219
220
221 RETURN @FirstItemName + ' price: ' + CAST(ROUND(@NewFirstItemPrice,2) as varchar) + ' <-> ' +
222 @SecondItemName + ' price: ' + CAST(ROUND(@NewSecondItemPrice,2) as varchar)+ ' <-> ' +
223 @ThirdItemName + ' price: ' + CAST(ROUND(@NewThirdItemPrice,2) as varchar)
224
225END
226
227--19
228CREATE PROCEDURE usp_CancelOrder(@OrderId INT, @CancelDate DATETIME)
229AS
230BEGIN
231 DECLARE @order INT = (SELECT Id FROM Orders WHERE Id = @OrderId)
232
233 IF (@order IS NULL)
234 BEGIN
235 ;THROW 51000, 'The order does not exist.', 1
236 END
237
238 DECLARE @OrderDate DATETIME = (SELECT [DateTime] FROM Orders WHERE Id = @OrderId)
239 DECLARE @DateDiff INT = (SELECT DATEDIFF(DAY, @OrderDate, @CancelDate))
240
241 IF (@DateDiff > 3)
242 BEGIN
243 ;THROW 51000, 'You can not cancel the order.', 2
244 END
245
246 DELETE FROM OrderItems
247 WHERE OrderId = @OrderId
248
249 DELETE FROM Orders
250 WHERE Id = @OrderId
251END
252
253
254--20 Cancel order
255CREATE TABLE DeletedOrders
256(
257 OrderId INT,
258 ItemId INT,
259 ItemQuantity INT
260)
261
262CREATE TRIGGER t_DeleteOrders
263 ON OrderItems AFTER DELETE
264 AS
265 BEGIN
266 INSERT INTO DeletedOrders (OrderId, ItemId, ItemQuantity)
267 SELECT d.OrderId, d.ItemId, d.Quantity
268 FROM deleted AS d
269 END