· 8 years ago · Feb 11, 2018, 01:38 AM
1/* CLEANING UP PREVIOUS THINGS */
2ALTER TABLE TBL DROP CONSTRAINT FK_WAITER;
3ALTER TABLE BOOKING DROP CONSTRAINT FK_TABLE;
4ALTER TABLE BOOKING DROP CONSTRAINT FK_CUSTOMER;
5ALTER TABLE ORDR DROP CONSTRAINT FK_DISH;
6ALTER TABLE WAITER DROP CONSTRAINT PK_WAITER;
7ALTER TABLE TBL DROP CONSTRAINT PK_TABLE;
8ALTER TABLE CUSTOMER DROP CONSTRAINT PK_CUSTOMER;
9ALTER TABLE BOOKING DROP CONSTRAINT PK_BOOKING;
10ALTER TABLE MENU DROP CONSTRAINT PK_MENU;
11ALTER TABLE ORDR DROP CONSTRAINT PK_ORDER;
12DROP TABLE WAITER;
13DROP TABLE TBL;
14DROP TABLE CUSTOMER;
15DROP TABLE BOOKING;
16DROP TABLE ORDR;
17DROP TABLE MENU;
18
19/* CREATING TABLES */
20CREATE TABLE WAITER (
21 W_ID INTEGER NOT NULL IDENTITY(1,1),
22 W_NAME VARCHAR(25) NOT NULL,
23 W_SURNAME VARCHAR(25) NOT NULL,
24 W_SALARY INTEGER NULL,
25 CONSTRAINT PK_WAITER PRIMARY KEY (W_ID)
26);
27
28CREATE TABLE TBL (
29 T_ID INTEGER NOT NULL IDENTITY(1,1),
30 T_CHAIRS INTEGER NOT NULL,
31 T_WAITER INTEGER NULL,
32 CONSTRAINT PK_TABLE PRIMARY KEY (T_ID),
33 CONSTRAINT FK_WAITER FOREIGN KEY(T_WAITER) REFERENCES WAITER(W_ID)
34);
35
36CREATE TABLE CUSTOMER (
37 C_ID INTEGER NOT NULL IDENTITY(1,1),
38 C_NAME VARCHAR(25) NOT NULL,
39 C_SURNAME VARCHAR(25) NOT NULL,
40 C_PHONE VARCHAR(15) NULL,
41 CONSTRAINT PK_CUSTOMER PRIMARY KEY (C_ID)
42);
43
44CREATE TABLE BOOKING (
45 B_ID INTEGER NOT NULL IDENTITY(1,1),
46 B_DATE DATETIME NOT NULL,
47 B_TABLE INTEGER NOT NULL,
48 B_CUSTOMER INTEGER NULL,
49 B_DISCOUNT INTEGER NULL,
50 CONSTRAINT PK_BOOKING PRIMARY KEY (B_ID),
51 CONSTRAINT FK_TABLE FOREIGN KEY(B_TABLE) REFERENCES TBL(T_ID),
52 CONSTRAINT FK_CUSTOMER FOREIGN KEY(B_CUSTOMER) REFERENCES CUSTOMER(C_ID)
53);
54
55CREATE TABLE MENU (
56 M_ID INTEGER NOT NULL IDENTITY(1,1),
57 M_NAME VARCHAR(35) NOT NULL,
58 M_DESCRIPTION VARCHAR(100) NOT NULL,
59 M_PRICE INTEGER NOT NULL,
60 CONSTRAINT PK_MENU PRIMARY KEY (M_ID)
61);
62
63CREATE TABLE ORDR (
64 O_ID INTEGER NOT NULL IDENTITY(1,1),
65 O_BOOKING INTEGER NOT NULL,
66 O_DISH INTEGER NOT NULL,
67 O_QUANTITY INTEGER NOT NULL,
68 CONSTRAINT PK_ORDER PRIMARY KEY (O_ID),
69 CONSTRAINT FK_DISH FOREIGN KEY(O_DISH) REFERENCES MENU(M_ID)
70);
71
72/* TRIGGERS */
73
74/* TRIGGER dontDuplicateCustomers
75- trigger won't let you insert a new customer, which has same data as the one which already exists
76- trigger will notify if name and surname match, but phone number is different (some customers change their phone numbers)
77- trigger will inform if customer was added correctly
78 */
79
80CREATE OR ALTER TRIGGER dontDuplicateCustomers ON CUSTOMER FOR INSERT AS
81 DECLARE @name VARCHAR(25), @surname VARCHAR(25), @phone VARCHAR(15), @howMany INTEGER;
82BEGIN
83 SELECT @name = C_NAME,
84 @surname = C_SURNAME,
85 @phone = C_PHONE
86 FROM INSERTED;
87
88 IF (SELECT COUNT(*) FROM CUSTOMER WHERE C_SURNAME = @surname AND C_NAME = @name AND C_PHONE = @phone) > 1
89 BEGIN
90 RAISERROR('Such customer already exists!',16,1);
91 ROLLBACK;
92 END;
93 ELSE IF (SELECT COUNT(*) FROM CUSTOMER C,inserted I WHERE I.C_SURNAME = C.C_SURNAME AND I.C_NAME = C.C_NAME) > 1
94 BEGIN
95 PRINT 'Customer with same data apart from phone number already exists (just a notice).';
96 PRINT 'Customer has been added!';
97 END
98 ELSE
99 PRINT 'Customer has been added!';
100END;
101GO;
102
103/* testing dontDuplicateCustomers */
104
105BEGIN
106 BEGIN TRANSACTION
107 INSERT INTO CUSTOMER(C_NAME, C_SURNAME, C_PHONE) VALUES('NAME','SURNAME', 997); -- first will succeed
108 INSERT INTO CUSTOMER(C_NAME, C_SURNAME, C_PHONE) VALUES('NAME','SURNAME', 997); -- here won't allow
109 ROLLBACK
110END
111
112
113/* TRIGGER deleteCustomerPhoneOnly
114 In order not to lose information about person who made a booking, but still be able to respect person's privacy,
115 deleting will only remove customer's phone number.
116 */
117
118CREATE OR ALTER TRIGGER deleteCustomerPhoneOnly ON CUSTOMER INSTEAD OF DELETE AS
119 DECLARE @counter BIT = (SELECT COUNT(C_ID) FROM DELETED);
120DECLARE @id INTEGER;
121DECLARE @phone VARCHAR(15);
122BEGIN
123 IF @counter = 0
124 BEGIN
125 RAISERROR('Such customer does not exist!',16,1);
126 END
127 ELSE
128 BEGIN
129 SET @id = (SELECT C_ID FROM DELETED);
130 SET @phone = (SELECT C_PHONE FROM CUSTOMER WHERE C_ID = @id);
131 IF @phone IS NULL
132 BEGIN
133 RAISERROR('This customer already has no phone number assigned!',16,1);
134 END
135 ELSE
136 BEGIN
137 UPDATE CUSTOMER SET C_PHONE = NULL WHERE C_ID = (SELECT C_ID FROM DELETED);
138 PRINT 'Phone number of customer with ID = ' + CAST(@counter AS VARCHAR) + ' has been removed.';
139 END
140 END
141END
142
143/* testing deleteCustomerPhoneOnly */
144
145BEGIN
146 BEGIN TRANSACTION
147 INSERT INTO CUSTOMER(C_NAME, C_SURNAME, C_PHONE) VALUES('Test100','Surname100',123);
148 UPDATE CUSTOMER SET C_PHONE = 997 WHERE C_NAME = 'Test100' AND C_SURNAME = 'Surname100';
149 DELETE FROM CUSTOMER WHERE C_NAME = 'Test100' AND C_SURNAME = 'Surname100';
150 SELECT * FROM CUSTOMER;
151 ROLLBACK
152END
153
154/* TRIGGER noPricesBelowZero
155Trigger will decrease price of a dish if you try to change price of a dish in menu to one which is lower than zero by
156this value. For example if dish costs 50 and you try to update it to -10, the cost of a dish will be reduced to 40.
157Rollbacks when you try to change price to null.
158 */
159
160CREATE OR ALTER TRIGGER noPricesBelowZero ON MENU FOR UPDATE AS
161 DECLARE @price INTEGER = (SELECT M_PRICE FROM INSERTED);
162BEGIN
163 IF @price < 0
164 BEGIN
165 DECLARE @oldPrice INTEGER = (SELECT M_PRICE FROM DELETED);
166 UPDATE MENU SET M_PRICE = @oldPrice + @price WHERE M_ID = (SELECT M_ID FROM INSERTED);
167 PRINT 'Price of a dish has been reduced by ' + CAST(@price AS VARCHAR);
168 END;
169 ELSE IF @price IS NULL
170 BEGIN
171 PRINT 'Price cannot be null!';
172 ROLLBACK;
173 END
174 ELSE
175 PRINT 'Price of menu item has been changed to ' + CAST(@price AS VARCHAR);
176END
177
178BEGIN
179 BEGIN TRANSACTION
180 INSERT INTO MENU(M_NAME, M_DESCRIPTION, M_PRICE) VALUES ('Kebab', 'Tasty Polish meal', 20);
181 UPDATE MENU SET M_PRICE = 15 WHERE M_NAME = 'Kebab'; --should change price from 20 to 15
182 INSERT INTO MENU(M_NAME, M_DESCRIPTION, M_PRICE) VALUES ('Pizza', 'Margheritta', 60);
183 UPDATE MENU SET M_PRICE = -5 WHERE M_NAME = 'Pizza'; --should reduce price by 5
184 SELECT * FROM MENU;
185 ROLLBACK;
186END
187
188/* TRIGGER randomWaiterWhenAdding
189 If no waiter assigned to this table while inserting it, then waiter with the smallest number of duties will be
190 assigned.
191*/
192
193CREATE OR ALTER TRIGGER randomWaiterWhenAdding ON TBL AFTER INSERT AS
194 DECLARE @this INTEGER = (SELECT T_ID FROM INSERTED);
195DECLARE @waiter INTEGER = (SELECT T_WAITER FROM INSERTED);
196DECLARE @result BIT;
197IF @waiter IS NULL
198 BEGIN
199 EXEC @result = assignWaiter @this;
200 IF @result = 1
201 PRINT 'Waiter was automatically assigned for added table.';
202 END
203
204/* testing randomWaiterAdding */
205
206BEGIN
207 BEGIN TRANSACTION
208 INSERT INTO TBL(T_CHAIRS, T_WAITER) VALUES(5, null); -- will assign a waiter anyway
209 ROLLBACK
210END
211
212/* TRIGGER noMoney
213 We are too poor to pay more than XXX (set in variable) PLN for waiters. Trigger won't allow to add a waiter if the
214 limit of 10 000 is (will be) exceeded.
215 */
216
217CREATE OR ALTER TRIGGER noMoney ON WAITER FOR INSERT AS
218 DECLARE @maxMoney INTEGER = 10000, @moneyLeft INTEGER;
219BEGIN
220 IF (SELECT SUM(W_SALARY) FROM WAITER) > @maxMoney
221 BEGIN
222 ROLLBACK;
223 SELECT @moneyLeft = @maxMoney - SUM(W_SALARY) FROM WAITER
224 PRINT 'We dont have enough money to employ a waiter with such a salary! ' +
225 'Maximum we can afford for a waiter is ' + CAST(@moneyLeft AS VARCHAR) + ' PLN';
226 END
227 ELSE
228 PRINT 'New waiter added.';
229END
230
231/* testing noMoney */
232
233BEGIN
234 BEGIN TRANSACTION
235 INSERT INTO WAITER(W_NAME, W_SURNAME, W_SALARY) VALUES ('Dariusz', 'Bober', 100); -- will work
236 INSERT INTO WAITER(W_NAME, W_SURNAME, W_SALARY) VALUES ('Agata', 'Jelen', 10000000); -- will bring error
237 ROLLBACK
238END
239
240/* PROCEDURE assignWaiter
241- assigns to given table a waiter, who has the smallest number of duties.
242- raises error when given table does not exist
243- raises error when given table already has a waiter
244- raises error when it's not possible to assign a waiter, because there are no waiters
245@return
2461 - if waiter was assigned
247nothing - if waiter was not assigned, see error to know the reason
248 */
249
250CREATE OR ALTER PROCEDURE assignWaiter(@tbl INTEGER) AS
251 DECLARE @currWaiter INTEGER = (SELECT T_WAITER FROM TBL WHERE T_ID = @tbl);
252 DECLARE @name VARCHAR(25), @surname VARCHAR(25);
253 BEGIN
254 IF NOT EXISTS(SELECT * FROM TBL WHERE T_ID = @tbl)
255 RAISERROR ('Given table does not exist!',16,1);
256 ELSE IF @currWaiter IS NOT NULL
257 RAISERROR ('This table already has a waiter assigned!',16,1);
258 ELSE IF (SELECT COUNT(*) FROM WAITER) < 1
259 RAISERROR ('No waiter is employed!',16,1);
260 ELSE
261 BEGIN
262 --if there are waiters which don't have any table assigned
263 IF (SELECT COUNT(W_ID) FROM WAITER WHERE W_ID NOT IN (SELECT DISTINCT T_WAITER FROM TBL WHERE T_WAITER IS NOT NULL)) > 0
264 BEGIN
265 SET @currWaiter = (SELECT TOP 1 W_ID FROM WAITER WHERE W_ID NOT IN (SELECT DISTINCT T_WAITER FROM TBL WHERE T_WAITER IS NOT NULL));
266 END
267 ELSE
268 SET @currWaiter = (SELECT TOP 1 T_WAITER FROM TBL WHERE T_WAITER IS NOT NULL GROUP BY T_WAITER ORDER BY COUNT(T_ID));
269 UPDATE TBL SET T_WAITER = @currWaiter WHERE T_ID = @tbl;
270 SELECT @name = W_NAME, @surname = W_SURNAME FROM WAITER WHERE W_ID = @currWaiter;
271 PRINT CAST(@name AS VARCHAR) + ' ' + CAST(@surname AS VARCHAR) + ' has been assigned to table ' + CAST(@tbl AS VARCHAR);
272 RETURN 1;
273 END
274 END
275
276 /* testing assignWaiter */
277
278 BEGIN
279 BEGIN TRANSACTION
280 INSERT INTO WAITER(W_NAME,W_SURNAME,W_SALARY) VALUES('FILIP','BABLA',1900);
281 INSERT INTO TBL(T_CHAIRS) VALUES(2); -- adding table without waiter assigned (but the trigger will add one anyway)
282 PRINT 'Although trigger automatically added some waiter, we remove it now to use the procedure.';
283 DECLARE @mid INTEGER = (SELECT MAX(T_ID) FROM TBL);
284 UPDATE TBL SET T_WAITER = NULL WHERE T_ID = @mid; -- now it's null again
285 DECLARE @resultAssignWaiter BIT;
286 EXEC @resultAssignWaiter = assignWaiter @mid;
287 PRINT 'Result of an operation: ' + CAST(@resultAssignWaiter AS VARCHAR) + ' <-- should be 1 :)';
288 ROLLBACK;
289 END;
290
291 /* PROCEDURE discountToday
292 Give discount for all bookings which have been made for today.
293 (shouldn't be really made with cursor, but have to learn on something...)
294 Raises error when given discount is bigger or equal zero.
295 Raises error when there are no bookings.
296 */
297
298 CREATE OR ALTER PROCEDURE discountToday(@howBig INTEGER, @counter INTEGER OUTPUT) AS
299 DECLARE @bid INTEGER, @bdate DATETIME, @discount INTEGER;
300 DECLARE bookings CURSOR FOR SELECT B_ID,B_DATE,B_DISCOUNT FROM BOOKING;
301 BEGIN
302 SET @counter = 0;
303 IF @howBig <= 0 RAISERROR('Dont be so greedy, give discount which is at least bigger than 0 :(',16,1);
304 IF (SELECT COUNT(*) BOOKING) < 1 RAISERROR('There are no bookings in the restaurant!',16,1);
305 ELSE
306 BEGIN
307 OPEN bookings;
308 FETCH NEXT FROM bookings INTO @bid, @bdate, @discount
309 WHILE @@FETCH_STATUS = 0
310 BEGIN
311 IF CONVERT(DATE, @bdate) = CONVERT(DATE, GETDATE())
312 BEGIN
313 SET @discount = @howBig;
314 SET @counter = @counter + 1;
315 UPDATE BOOKING SET B_DISCOUNT = @discount WHERE B_ID = @bid;
316 END
317 FETCH NEXT FROM bookings INTO @bid, @bdate, @discount
318 END
319 CLOSE bookings;
320 DEALLOCATE bookings;
321 END;
322 END;
323
324 /* testing discountToday
325 1) Adds a booking for today's date (change that manually!)
326 2) Executes discountToday procedure
327 3) Prints the output - number of given discounts - should be 1.
328 */
329 BEGIN
330 BEGIN TRANSACTION
331 INSERT INTO CUSTOMER(C_NAME, C_SURNAME, C_PHONE) VALUES('ForTesting','DiscountToday', '123');
332 DECLARE @cid INTEGER = (SELECT C_ID FROM CUSTOMER WHERE C_NAME = 'ForTesting' AND C_SURNAME = 'DiscountToday');
333 INSERT INTO BOOKING(B_DATE, B_TABLE, B_CUSTOMER, B_DISCOUNT) VALUES('20180117 10:30:00 PM', 2, @cid, 0);
334 DECLARE @resultDiscountToday INTEGER;
335 EXEC discountToday 10, @resultDiscountToday OUTPUT;
336 PRINT 'Discount was given to ' + CAST(@resultDiscountToday AS VARCHAR) + ' bookings.';
337 SELECT * FROM BOOKING;
338 ROLLBACK;
339 END;
340
341 /* PROCEDURE salPlus4GoodWaiters
342 Increases salary for each waiter who is working on more than one table. Increase is equal to number of tables he takes
343 care of multiplied by 100. For example, if he takes care of 3 tables, salary will be increases by 3*100 = 300.
344 Returns:
345 1) number of waiters which had their salaries increased
346 2) sum of money we spent on increases of salaries
347 Raises error when there are no waiters.
348 */
349
350 CREATE OR ALTER PROCEDURE salPlus4GoodWaiters (@counter INTEGER OUTPUT, @sumOfSals INTEGER OUTPUT) AS
351 DECLARE @wid INTEGER, @wsal INTEGER, @newSal INTEGER, @numOfTables INTEGER;
352 DECLARE wlist CURSOR FOR SELECT W_ID,W_SALARY FROM WAITER;
353 SET NOCOUNT ON;
354 BEGIN
355 IF (SELECT COUNT(*) WAITER) < 1 RAISERROR('There are no waiters in the restaurant!',16,1);
356 SET @counter = 0;
357 SET @sumOfSals = 0;
358 OPEN wlist;
359 FETCH NEXT FROM wlist INTO @wid, @wsal;
360 WHILE @@FETCH_STATUS = 0
361 BEGIN
362 SELECT @numOfTables = COUNT(T_ID) FROM TBL WHERE T_WAITER = @wid;
363 IF @numOfTables > 1
364 BEGIN
365 SET @newSal = @numOfTables * 100;
366 SET @sumOfSals = @sumOfSals + @newSal;
367 SET @newSal = @newSal + @wsal;
368 UPDATE WAITER SET W_SALARY = @newSal WHERE W_ID = @wid;
369 SET @counter = @counter + 1;
370 END
371 FETCH NEXT FROM wlist INTO @wid, @wsal;
372 END
373 CLOSE wlist;
374 DEALLOCATE wlist;
375 END
376
377 /* testing salPlus4GoodWaiters */
378
379 BEGIN
380 BEGIN TRANSACTION
381 DECLARE @counter INTEGER, @sumOfSals INTEGER;
382 EXECUTE salPlus4GoodWaiters @counter OUTPUT, @sumOfSals OUTPUT;
383 PRINT 'Salary of ' + CAST(@counter AS VARCHAR) + ' waiters was increased. We spent ' + CAST(@sumOfSals AS VARCHAR) + ' on that';
384 ROLLBACK
385 END
386
387 /* PROCEDURE bestCustomers
388 Will return list of X (argument) customers. Best means those who made largest number of bookings.
389 Raises error when there are no bookings.
390 */
391
392 CREATE OR ALTER PROCEDURE bestCustomers(@howMany INTEGER) AS
393 IF EXISTS (SELECT * FROM BOOKING)
394 SELECT TOP (@howMany) C_NAME,C_SURNAME FROM BOOKING,CUSTOMER WHERE CUSTOMER.C_ID = BOOKING.B_CUSTOMER
395 GROUP BY C_NAME,C_SURNAME ORDER BY COUNT(B_ID) DESC;
396 ELSE
397 RAISERROR('There were no bookings, so its impossible to say who is the best customer...',16,1);
398
399 /* testing bestCustomers */
400
401 BEGIN
402 BEGIN TRANSACTION
403 INSERT INTO CUSTOMER(C_NAME, C_SURNAME, C_PHONE) VALUES('Test100','Surname100',123);
404 INSERT INTO CUSTOMER(C_NAME, C_SURNAME, C_PHONE) VALUES('Test200','Surname200',123);
405 DECLARE @cid INTEGER = (SELECT C_ID FROM CUSTOMER WHERE C_NAME = 'Test100' AND C_SURNAME = 'Surname100');
406 DECLARE @tbl INTEGER = (SELECT MAX(T_ID) FROM TBL);
407 INSERT INTO BOOKING(B_DATE, B_TABLE, B_CUSTOMER, B_DISCOUNT) VALUES('20180106 10:30:00 PM', @tbl, @cid, 0);
408 INSERT INTO BOOKING(B_DATE, B_TABLE, B_CUSTOMER, B_DISCOUNT) VALUES('20180106 15:30:00 PM', @tbl, @cid, 0);
409 INSERT INTO BOOKING(B_DATE, B_TABLE, B_CUSTOMER, B_DISCOUNT) VALUES('20180106 16:30:00 PM', @tbl, @cid, 0);
410 INSERT INTO BOOKING(B_DATE, B_TABLE, B_CUSTOMER, B_DISCOUNT) VALUES('20180106 16:30:00 PM', @tbl, @cid + 1, 0);
411 EXEC bestCustomers 2;
412 ROLLBACK
413 END
414
415 /* PROCEDURE callPeopleFromDate
416 Will return list of people who had bookings on given date.
417 Raises error when there were no bookings for selected date.
418 */
419
420 CREATE OR ALTER PROCEDURE callPeopleFromDate(@date AS DATE) AS
421 IF EXISTS (SELECT * FROM BOOKING WHERE @date = CONVERT(DATE, B_DATE))
422 SELECT C_NAME,C_SURNAME,C_PHONE FROM CUSTOMER,BOOKING WHERE CUSTOMER.C_ID = BOOKING.B_CUSTOMER AND
423 @date = CONVERT(DATE, B_DATE) AND C_PHONE IS NOT NULL;
424 ELSE
425 RAISERROR('There were no bookings on selected day.',16,1);
426
427 /* testing callPeopleFromDate */
428
429 BEGIN
430 BEGIN TRANSACTION
431 INSERT INTO CUSTOMER(C_NAME, C_SURNAME, C_PHONE) VALUES('Test100','Surname100',123);
432 INSERT INTO CUSTOMER(C_NAME, C_SURNAME, C_PHONE) VALUES('Test200','Surname200',123);
433 DECLARE @cid INTEGER = (SELECT C_ID FROM CUSTOMER WHERE C_NAME = 'Test100' AND C_SURNAME = 'Surname100');
434 DECLARE @tbl INTEGER = (SELECT MAX(T_ID) FROM TBL);
435 INSERT INTO BOOKING(B_DATE, B_TABLE, B_CUSTOMER, B_DISCOUNT) VALUES('20180106 10:30:00 PM', @tbl, @cid, 0);
436 INSERT INTO BOOKING(B_DATE, B_TABLE, B_CUSTOMER, B_DISCOUNT) VALUES('20180106 15:30:00 PM', @tbl, @cid + 1, 0);
437 EXEC callPeopleFromDate '20180106';
438 --EXEC callPeopleFromDate '20180107'; --should bring error here
439 ROLLBACK
440 END
441
442 /* PROCEDURE checkIfWithDiscount
443 Returns:
444 1 - when given booking has a discount
445 0 - when given booking has no discount
446 -1 - when such booking does not exist
447 */
448
449 CREATE OR ALTER PROCEDURE checkIfWithDiscount(@bid AS INTEGER) AS
450 DECLARE @discount INTEGER;
451 IF EXISTS (SELECT * FROM BOOKING WHERE B_ID = @bid)
452 BEGIN
453 SELECT @discount = B_DISCOUNT FROM BOOKING WHERE B_ID = @bid;
454 IF @discount > 0
455 RETURN 1;
456 ELSE
457 RETURN 0;
458 END
459 ELSE
460 RETURN -1;
461
462 /* testing checkIfWithDiscount */
463
464 BEGIN
465 BEGIN TRANSACTION
466 INSERT INTO CUSTOMER(C_NAME, C_SURNAME, C_PHONE) VALUES('Test5555','Surname5555',123);
467 DECLARE @cid INTEGER = (SELECT C_ID FROM CUSTOMER WHERE C_NAME = 'Test5555' AND C_SURNAME = 'Surname5555');
468 DECLARE @tbl INTEGER = (SELECT MAX(T_ID) FROM TBL);
469 INSERT INTO BOOKING(B_DATE, B_TABLE, B_CUSTOMER, B_DISCOUNT) VALUES('20180106 10:30:00 PM', @tbl, @cid, 0);
470 INSERT INTO BOOKING(B_DATE, B_TABLE, B_CUSTOMER, B_DISCOUNT) VALUES('20180106 10:30:00 PM', @tbl, @cid, 50);
471 DECLARE @booking INTEGER = (SELECT MAX(B_ID) FROM BOOKING);
472 DECLARE @return INTEGER;
473
474 EXEC @return = checkIfWithDiscount @booking; -- first will return 1
475 PRINT 'Result: ' + CAST(@return AS VARCHAR);
476
477 SET @booking = @booking - 1;
478 EXEC @return = checkIfWithDiscount @booking; -- second will return 0
479 PRINT 'Result: ' + CAST(@return AS VARCHAR);
480 ROLLBACK
481 END
482
483 /* PROCEDURE whoEarnsMoreThanAvg
484 Shows waiters who earn more than average salary and display number of tables they have already attended to.
485 */
486
487 CREATE OR ALTER PROCEDURE whoEarnsMoreThanAvg AS
488 DECLARE @avgsal INTEGER = (SELECT AVG(W_SALARY) FROM WAITER),
489 @wid INTEGER, @wname VARCHAR(25), @wsurname VARCHAR(25), @wsal INTEGER, @bookNum INTEGER;
490 DECLARE wtr CURSOR FOR SELECT W_ID,W_NAME,W_SURNAME,W_SALARY FROM WAITER;
491 BEGIN
492 OPEN wtr;
493 FETCH NEXT FROM wtr INTO @wid, @wname, @wsurname, @wsal;
494 WHILE @@FETCH_STATUS = 0
495 BEGIN
496 IF @wsal > @avgsal
497 BEGIN
498 SET @bookNum = (SELECT COUNT(B_TABLE) FROM BOOKING WHERE B_TABLE IN (SELECT T_ID FROM TBL WHERE T_WAITER = @wid))
499 PRINT @wname + ' ' + @wsurname + ' earns ' + CAST(@wsal AS VARCHAR) + ' and attended to ' + CAST(@bookNum AS VARCHAR) + ' bookings.';
500 END
501 FETCH NEXT FROM wtr INTO @wid, @wname, @wsurname, @wsal;
502 END
503 CLOSE wtr;
504 DEALLOCATE wtr;
505 END
506
507 /* testing whoEarnsMoreThanAvg */
508
509 BEGIN
510 BEGIN TRANSACTION
511 DECLARE @cid INTEGER = (SELECT C_ID FROM CUSTOMER WHERE C_NAME = 'Test100' AND C_SURNAME = 'Surname100' AND C_PHONE = 123);
512 DECLARE @tbl INTEGER = (SELECT MAX(T_ID) FROM TBL);
513 DECLARE @waiter INTEGER = (SELECT MAX(W_ID) FROM WAITER);
514 UPDATE TBL SET T_WAITER = @waiter WHERE T_ID = @tbl;
515 INSERT INTO BOOKING(B_DATE, B_TABLE, B_CUSTOMER, B_DISCOUNT) VALUES('20180106 10:30:00 PM', @tbl, @cid, 0);
516 INSERT INTO BOOKING(B_DATE, B_TABLE, B_CUSTOMER, B_DISCOUNT) VALUES('20180106 10:30:00 PM', @tbl, @cid, 50);
517 EXEC whoEarnsMoreThanAvg;
518 ROLLBACK;
519 END
520
521 /* PROCEDURE bookingsForMoreThan
522 Shows all the bookings with customers who made them. Only bookings with total value of orders which is bigger than
523 given argument will be displayed.
524 Raises error when there are no bookings.
525 */
526 CREATE OR ALTER PROCEDURE bookingsForMoreThan(@minVal AS INTEGER) AS
527 DECLARE @orderVal INTEGER, @bid INTEGER;
528 DECLARE bookings CURSOR FOR SELECT B_ID FROM BOOKING;
529 BEGIN
530 IF (SELECT COUNT(*) FROM BOOKING) < 1
531 RAISERROR ('There are no bookings!',16,1);
532 ELSE
533 BEGIN
534 OPEN bookings;
535 FETCH NEXT FROM bookings INTO @bid;
536 WHILE @@FETCH_STATUS = 0
537 BEGIN
538 SET @orderVal = (SELECT SUM(M_PRICE * O_QUANTITY) FROM ORDR,MENU WHERE O_DISH = M_ID AND O_BOOKING = @bid);
539 IF @orderVal > @minVal
540 PRINT 'Booking ' + CAST(@bid AS VARCHAR) + ' ordered food for ' + CAST(@orderVal AS VARCHAR);
541 FETCH NEXT FROM bookings INTO @bid;
542 END
543 CLOSE bookings;
544 END
545 DEALLOCATE bookings;
546 END
547
548 /* testing bookingsForMoreThan */
549
550 BEGIN
551 BEGIN TRANSACTION;
552 INSERT INTO CUSTOMER(C_NAME, C_SURNAME, C_PHONE) VALUES('Test123','Surname123',666);
553 DECLARE @cid INTEGER = (SELECT C_ID FROM CUSTOMER WHERE C_NAME = 'Test123' AND C_SURNAME = 'Surname123');
554 DECLARE @tbl INTEGER = (SELECT MAX(T_ID) FROM TBL);
555 INSERT INTO BOOKING(B_DATE, B_TABLE, B_CUSTOMER, B_DISCOUNT) VALUES('20180106 10:30:00 PM', @tbl, @cid, 0);
556 INSERT INTO BOOKING(B_DATE, B_TABLE, B_CUSTOMER, B_DISCOUNT) VALUES('20180106 10:30:00 PM', @tbl, @cid, 50);
557 INSERT INTO MENU(M_NAME, M_DESCRIPTION, M_PRICE) VALUES ('Chips', 'Potato chips', 10);
558 DECLARE @book INTEGER = (SELECT MAX(B_ID) FROM BOOKING);
559 DECLARE @menu INTEGER = (SELECT MAX(M_ID) FROM MENU);
560 INSERT INTO ORDR(O_BOOKING, O_DISH, O_QUANTITY) VALUES (@book, @menu, 500);
561
562 -- should show nothing
563 EXEC bookingsForMoreThan 6000;
564
565 -- should show this booking
566 EXEC bookingsForMoreThan 1000;
567
568 ROLLBACK;
569
570 BEGIN TRANSACTION;
571 -- should show that there are no bookings
572 EXEC bookingsForMoreThan 5000;
573 ROLLBACK;
574 END