· 8 years ago · Jun 09, 2018, 09:08 PM
1 --EMPLYEE TBALES--
2--==========================================================--
3
4CREATE TABLE dbo.Employee (
5 Employee_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
6 Name varchar(100) NOT NULL,
7 Surname varchar(100) NOT NULL,
8 Birthday date NOT NULL DEFAULT GETDATE(),
9 Job_position varchar(100) NOT NULL,
10 Date_hired date NOT NULL DEFAULT GETDATE(),
11 PESEL varchar(11) NOT NULL UNIQUE CHECK(LEN(pesel) = 11),
12 Salary MONEY NOT NULL DEFAULT (0),
13 E_mail varchar(30) NULL DEFAULT (NULL),
14 Phone_Nr varchar(14) NULL DEFAULT (NULL),
15);
16
17CREATE TABLE dbo.Employee_Adress(
18 Adress_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
19 Country varchar(100) NOT NULL DEFAULT (NULL),
20 City varchar(100) NOT NULL DEFAULT (NULL),
21 Street_Name varchar(100) NOT NULL DEFAULT (NULL),
22 Street_Number varchar(50) NOT NULL DEFAULT (NULL),
23 Door_Number varchar(10) DEFAULT (NULL),
24
25 Employee_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Employee(Employee_ID) on delete cascade
26);
27
28-------------------------------------------------------------------
29 --ADDITIONAL EMPLOYEE TABLES (CAR)--
30CREATE TABLE dbo.Car (
31 Car_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
32 Brand varchar(200) NOT NULL CHECK(LEN(brand) >=2),
33 Model varchar(200) NOT NULL CHECK(LEN(model) >=2),
34 Date_bought date NOT NULL DEFAULT (GETDATE()),
35 Licens_plate varchar(10) NOT NULL UNIQUE,
36
37
38 Employee_ID int NULL FOREIGN KEY REFERENCES dbo.Employee(Employee_ID) on delete set default
39);
40
41
42CREATE TABLE dbo.car_status (
43 Status_ID int NOT NULL IDENTITY(1,1),
44 Accident_number int NOT NULL DEFAULT (0),
45 Mileage int NOT NULL CHECK(mileage >= 0),
46 Insurance_end date NOT NULL DEFAULT (NULL),
47
48 Car_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Car(Car_ID) on delete cascade,
49);
50--========================================================--
51 --CUSTOMER TABLES--
52--========================================================--
53Create table dbo.Customer (
54 Customer_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
55 Name varchar(200) NOT NULL DEFAULT (NULL),
56 Surname varchar(200) NULL DEFAULT (NULL),
57 Phone_Nr varchar(14) NULL DEFAULT (NULL)
58);
59
60CREATE TABLE dbo.Customer_Adress(
61 Adress_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
62 City varchar(200) NULL DEFAULT (NULL),
63 Street_Name varchar(200) NULL DEFAULT (NULL),
64 Street_Number int NULL DEFAULT (NULL),
65 Door_Number int NULL DEFAULT (NULL),
66
67 Customer_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Customer(Customer_ID) on delete cascade
68);
69--========================================================--
70 --ORDERS TABLES--
71--========================================================--
72
73CREATE TABLE dbo.Payment_Method(
74 Payment_Method_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
75 Method varchar(50) NULL DEFAULT ('cash'),
76);
77
78CREATE TABLE dbo.Order_Type(
79 Order_Type_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
80 Order_type varchar(100) NULL DEFAULT ('reastaurant'),
81);
82
83CREATE TABLE dbo.Orders(
84 Order_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
85 Date_order date NOT NULL DEFAULT GETDATE(),
86 Order_price MONEY NULL CHECK(Order_price >= 0) DEFAULT (0),
87
88 Customer_ID int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Customer(Customer_ID) on delete set default,
89 Payment_Method_ID INT NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Payment_Method(Payment_Method_ID) on delete set default,
90 Order_type int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Order_Type(Order_Type_ID) on delete set default,
91);
92
93CREATE TABLE dbo.EmployeeOrders(
94 ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
95 Employee_ID int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Employee(Employee_ID) on delete cascade,
96 Order_ID int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Orders(Order_ID) on delete cascade,
97);
98
99--========================================================--
100 --PRODUCTS TABLES--
101--========================================================--
102CREATE TABLE dbo.Drink(
103 Drink_ID varchar(10) NOT NULL PRIMARY KEY,
104 Drink_Name varchar(50) NOT NULL,
105 Drink_type varchar(50) NOT NULL,
106 Drink_description varchar(200) NOT NULL,
107 Price MONEY NULL DEFAULT (0)
108);
109
110CREATE TABLE dbo.Pizza(
111 Pizza_ID varchar(10) NOT NULL PRIMARY KEY,
112 Pizza_Name varchar(50) NOT NULL,
113 Toppings varchar(200) NOT NULL,
114 Pizza_type varchar(50) NOT NULL,
115);
116
117CREATE TABLE dbo.Sauce(
118 Sauce_ID varchar(10) NOT NULL PRIMARY KEY,
119 Sauce_Name varchar(50) NOT NULL,
120 Sauce_description varchar(300) NOT NULL,
121 Sauce_type varchar(50) NOT NULL,
122 Price MONEY NULL DEFAULT (0)
123);
124
125CREATE TABLE dbo.size(
126 ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
127 Size varchar(50) NOT NULL DEFAULT('Medium'),
128 Size_cm int NOT NULL DEFAULT(30),
129 Price MONEY NULL DEFAULT (0),
130);
131
132CREATE TABLE dbo.Products(
133 Product_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
134
135 Pizza_ID varchar(10) NULL FOREIGN KEY REFERENCES Pizza(Pizza_ID)on delete set NULL,
136 Sauce_ID varchar(10) NULL FOREIGN KEY REFERENCES Sauce(Sauce_ID)on delete set NULL,
137 Drink_ID varchar(10) NULL FOREIGN KEY REFERENCES Drink(Drink_ID)on delete set NULL,
138 Size_ID INT NULL FOREIGN KEY REFERENCES dbo.size(ID)on delete set NULL,
139 Order_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Orders(Order_ID)on delete cascade,
140);
141
142
143--ZMIENIA FORMAT NUMERU--
144IF OBJECT_ID ('dbo.znak', 'F') IS NOT NULL
145-- deletes function
146 DROP FUNCTION dbo.znak;
147GO
148
149create function znak(@znaki char(14))
150returns char(14)
151as
152begin
153 declare @a char(3);
154 declare @b char(3);
155 declare @c char(4);
156 declare @d char(14);
157 set @a =(select SUBSTRING(@znaki,1,3));
158 set @b=(select substring(@znaki,4,3));
159 set @c=(select SUBSTRING(@znaki,7,4));
160
161 set @d='('+@a+')'+SPACE(1)+@b+'-'+@c;
162 return @d;
163end
164
165--SPRADZ FORMAT MAILOWY--
166drop function checkEmail
167
168CREATE FUNCTION checkEmail (@email VARCHAR(50))
169RETURNS INT
170AS
171BEGIN
172 IF (@email NOT LIKE '_%@__%.__%')
173 RETURN 0
174
175 RETURN 1
176END
177GO
178
179--========================================================--
180 --INSERT VALUES--
181--========================================================--
182 --EMPLOYEE--
183
184insert into dbo.Employee values('Dariusz','Rodzewicz','1998-12-18','Piekarz','2013-05-15','20054788612',120,'DRodzewicz@mgmail.com','861214043')
185insert into dbo.Employee values('Robert','Radulewicz','1998-04-07','Piekarz','2013-05-15','15937846132',600,'RobRAdulew@gmail.com','861234557')
186insert into dbo.Employee values('Lukasz','Dowgialo','1997-06-01','Kurier','2013-05-15','98712314785',1000,'DowgiaLukas@gmail.com','86856984')
187insert into dbo.Employee values('Ryszard','Granicki','1997-06-28','Piekarz','2013-05-15','65412398752',800,'RysiukGran@gmail.com','861523698')
188insert into dbo.Employee values('Lukasz','Baniukiewicz','1998-12-18','Kurier','2013-05-15','12589637415',1200,'Baniuk1999@gmail.com','86141586')
189insert into dbo.Employee values('Grzegorz','Wojtkiewicz','1999-01-16','Kasier','2013-05-15','12356987452',450,'WojtGrisha@gmail.com','861740012')
190insert into dbo.Employee values('Andrzej','Tichonow','1986-03-27','Piekarz','2013-05-15','20054788614',2000,'Lenkas89@gmail.com','861115225')
191insert into dbo.Employee values('Julia','Stanulyte','1998-10-11','Piekarz','2013-05-15','20111552386',600,'HarcJulia@gmail.com','861158996')
192insert into dbo.Employee values('Adriana','Wiszniewska','1996-08-22','Kasier','2013-05-15','12000254550',850,'MilionAdri@gmail.com','8614311258')
193insert into dbo.Employee values('Barbara','Zinkiewicz','1998-02-28','Kurier','2013-05-15','25555521587',775,'BasiutaZ@gmail.com','8645558555')
194--select * from Employee
195
196
197 --EMPLOYEE ADRESS--
198insert into dbo.Employee_Adress values('Lithuania','Vilnius','Erfuro','122','44',1),
199 ('Poland','Gdansk','Pomorska','44',NULL,2),
200 ('Poland','Gdansk','3-Maja','144','75',3),
201 ('Poland','Gdynia','Armii_Krajowej','98','B',4),
202 ('Poland','Sopot','Bitwy_Pod_Plowcami','64','209',5),
203 ('Poland','Sopot','Bitwy_Pod_Plowcami','64','209',6),
204 ('Poland','Gdansk','3-Maja','75',NULL,7),
205 ('Poland','Gdynia','Mickiewicza','47','65',8),
206 ('Belarus','Grodno','Zapackaja','44','56',9),
207 ('Poland','Gdansk','Reja','69','A',10);
208 --EMPLYEE ADRESS--
209--select * from Employee_Adress
210 --CAR--
211insert into dbo.Car values('Toyota','Auris','2014-08-15','GDN-45378',NULL),
212 ('Opel','Astra','2015-09-24','GDN-00218',NULL),
213 ('Volkswagen','Golf','2014-10-02','GDN-95734',NULL);
214--select * from Car
215 --CAR STATUS--
216insert into dbo.car_status values(0,150000,'2020-12-15',1),
217 (1,192025,'2020-12-15',2),
218 (0,134008,'2020-12-15',3);
219--select * from car_status
220 --PAYMENT METHOD--
221insert into dbo.Payment_Method values('cash'),
222 ('credit'),
223 ('PayPal'),
224 ('Bank Transfer');
225
226 --ORDER TYPE--
227insert into dbo.Order_Type values('Restaurant'),
228 ('Take-Away'),
229 ('Delivery');
230--select * from Order_Type
231 --PIZZA SIZE--
232insert into dbo.size values('Small',25,20),
233 ('Medium',30,26),
234 ('Large',40,34);
235--select * from size
236 --SAUCE--
237insert into dbo.Sauce values('CZWY','Czosnakowy','Nie ma','lagodny',2)
238insert into dbo.Sauce values('POWY','Pomidorowy','Nie ma','lagodny',1.5)
239insert into dbo.Sauce values('ARSKI','Arabski','Nie ma','ostry',2)
240--select * from Sauce
241 --PIZZA--
242insert into dbo.Pizza values('AlNO','Albano','ser, oregano, papryka, boczek, szynka, cebula','tradycyjna')
243insert into dbo.Pizza values('CASA','Calabresa','ser, oregano, salami, oliwki, papryczki apalenos, tabasco','ostra')
244insert into dbo.Pizza values('MACO','Marco','ser, oregano, salami, pieczarki','tradycyjna')
245insert into dbo.Pizza values('BAMA','Bahama','ser,oregano, szynka, ananas','slodka')
246insert into dbo.Pizza values('MATA','Margherita','ser, oregano','tradycyjna')
247insert into dbo.Pizza values('FONA','Foggiana',' ser, oregano, pieczarki','tradycyjna')
248insert into dbo.Pizza values('ONE','1x1','sos, ser, oregano, szynka','tradycyjna')
249insert into dbo.Pizza values('PORA','Povera','sos, ser, oregano, szynka, pieczarki','tradycyjna')
250insert into dbo.Pizza values('UNDO','Un Ricordo','sos, ser, oregano, szynka, kukurydza','tradycyjna')
251insert into dbo.Pizza values('UUGI','Quatro Formaggi','sos, ser, oregano, ser feta, ser pleśniowy, ser gouda','serowa')
252--select* from Pizza
253 --DRIKNS--
254insert into dbo.Drink values('CZHE','Czarna Herbata','Herbata','aroma',4)
255insert into dbo.Drink values('ZIHE','Zielona Herbata','Herbata','aroma',4)
256insert into dbo.Drink values('COLA','Co-Ca-Cola','gazowane','smczna',5)
257insert into dbo.Drink values('SPTE','Sprite','gazowane','smczna',5)
258insert into dbo.Drink values('FATA','Fanta','gazowane','smczna',5)
259--select* from Drink
260--------------------------------------------------------------------------------------------
261 --INSERT ZAMOWIEN--
262/*
263exec CREATE_ORDER 2,5,2,'Dariusz','Rodzewicz','861214043',NULL,NULL,NULL,NULL
264--select * from orders
265exec Add_Product 1,'BAMA','ARSKI',NULL,2
266exec Add_Product 1,'QUGI','POWY','FATA',2
267--select * from products
268exec CREATE_ORDER 3,5,1,'Mariusz','Rzeppa','841257869','Gdansk',NULL,NULL,NULL
269exec Add_Product 2,'BAMA','ARSKI',NULL,2
270exec CREATE_ORDER 3,7,1,'Jakub','Sildatk','541214785','Gdansk','Zaspa',122,42
271exec Add_Product 3,'BAMA','ARSKI',NULL,2
272exec CREATE_ORDER 3,7,1,'hajama','dddddd','541214785','Gdansk','Zaspa',122,42
273exec Add_Product 11,'BAMA',NULL,NULL,3
274select * from orders
275select * from size
276*/
277
278 --TWORZENIE ZAMOWIENIA--
279-----------------------------
280IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'CREATE_ORDER')
281DROP PROCEDURE CREATE_ORDER
282GO
283
284CREATE PROCEDURE dbo.CREATE_ORDER
285 @Order_type int,
286 @Employee_1 int, @Employee_2 int,@Payment_Method_ID int,
287 @Name varchar(100), @Surname varchar(100),@Phone_Nr int,
288 @City varchar(100), @Street_Name varchar(100), @Street_Number int, @Door_Number int
289AS
290BEGIN
291 INSERT INTO Customer(Name,Surname,phone_Nr) VALUES(@Name,@Surname,@Phone_Nr)
292
293 DECLARE @Customer_ID int
294 SET @Customer_ID = (SELECT TOP 1 Customer_ID From Customer ORDER BY Customer_ID DESC)
295
296 INSERT INTO Customer_Adress(City,Street_Name,Street_Number,Door_Number,Customer_ID)
297 VALUES(@City,@Street_Name,@Street_Number,@Door_Number,@Customer_ID)
298
299 INSERT INTO Orders(Customer_ID,Payment_Method_ID,Order_type)
300 Values(@Customer_ID,@Payment_Method_ID,@Order_type)
301
302 DECLARE @Order_ID int;
303 SET @Order_ID = (SELECT TOP 1 ORDER_ID From Orders ORDER BY Order_ID DESC)
304 INSERT INTO EmployeeOrders(Employee_ID,Order_ID)
305 VALUES(@Employee_1,@Order_ID)
306 INSERT INTO EmployeeOrders(Employee_ID,Order_ID)
307 VALUES(@Employee_2,@Order_ID)
308END
309GO
310
311
312 --DODAJ PRODUKT/Y DO ZAMOWIENIA--
313-----------------------------
314IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Add_Product')
315DROP PROCEDURE Add_Product
316GO
317
318CREATE PROCEDURE dbo.Add_Product
319 @Order_ID int,
320 @Pizza_ID varchar(10),
321 @Sauce_ID varchar(10),
322 @Drink_ID varchar(10),
323 @Size_ID int
324AS
325insert into Products(Order_ID,Pizza_ID,Sauce_ID,Drink_ID,Size_ID) VALUES(@Order_ID,@Pizza_ID,@Sauce_ID,@Drink_ID,@Size_ID)
326GO
327
328
329 --DAWANIE I ODEJMOWANIE SAMOCHODA PRACOWNIKU--
330-----------------------------
331IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'give_take_car')
332DROP PROCEDURE give_take_car
333GO
334
335CREATE PROCEDURE dbo.give_take_car
336@Employee_ID int,
337@Car_ID int
338AS
339BEGIN
340if (select Employee_ID from Car where car_ID=@Car_ID) = @Employee_ID
341UPDATE Car
342SET Employee_ID = NULL
343WHERE car_ID = @Car_ID
344
345else
346UPDATE Car
347SET Employee_ID = @Employee_ID
348WHERE car_ID = @Car_ID
349END
350GO
351
352
353 --DAWANIE PREMII PRACOWNIKU--
354-----------------------------
355IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'salary_up')
356DROP PROCEDURE salary_up
357GO
358
359CREATE PROCEDURE salary_up
360@Employee_ID int,
361@salary money
362AS
363IF @salary < 0
364 RAISERROR ('Wrong argument with salary',1,2);
365else
366Update Employee set salary = salary + @salary
367WHERE Employee_ID = @Employee_ID;
368GO
369
370 --POKAZ WSZYSTKIE ZAMOWIENIA WYPELNIONE PRZEZ EMPLOYEE_ID--
371-----------------------------
372IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Show_Employee_Orders')
373DROP PROCEDURE Show_Employee_Orders
374GO
375
376CREATE PROCEDURE Show_Employee_Orders
377@Employee_ID int
378AS
379SELECT Employee_ID,Name,Surname,Job_position,Date_hired,E_mail,Phone_Nr,Salary FROM Employee
380WHERE Employee_ID = @Employee_ID
381
382SELECT o.Order_ID,o.Date_order, o.Customer_ID,o.Payment_Method_ID,o.Order_Type,o.Order_Price FROM Orders as o
383JOIN EmployeeOrders as eo ON o.Order_ID=eo.Order_ID
384WHERE eo.Employee_ID = @Employee_ID
385GO
386 --FORMATOWANIE NUMERU--
387
388CREATE TRIGGER Format_Number_E
389ON dbo.Employee
390FOR INSERT--update
391AS
392BEGIN
393
394UPDATE Employee
395SET Phone_Nr = (select dbo.znak(Phone_Nr))
396WHERE Employee_ID = (SELECT Employee_ID FROM INSERTED)
397END
398GO
399
400CREATE TRIGGER Format_Number_C
401ON dbo.Customer
402FOR INSERT
403AS
404BEGIN
405
406UPDATE Customer
407SET Phone_Nr = dbo.znak(Phone_Nr)
408WHERE Customer_ID = (SELECT Customer_ID FROM INSERTED)
409END
410GO
411
412
413 --AUTOMATYCZNE OBLICZNIE CENY--
414
415CREATE TRIGGER Order_PriceT
416ON dbo.Products
417FOR INSERT
418AS
419BEGIN
420
421DECLARE @pizza MONEY;
422DECLARE @drink MONEY;
423DECLARE @sauce MONEY
424
425if (SELECT Price From Drink WHERE Drink_ID = (SELECT Drink_ID FROM INSERTED)) IS NULL
426SET @drink = 0
427ELSE
428SET @drink = (SELECT Price From Drink WHERE Drink_ID = (SELECT Drink_ID FROM INSERTED))
429
430if (SELECT Price From Sauce WHERE Sauce_ID = (SELECT Sauce_ID FROM INSERTED)) IS NULL
431SET @sauce = 0
432ELSE
433SET @sauce = (SELECT Price From Sauce WHERE Sauce_ID = (SELECT Sauce_ID FROM INSERTED))
434
435if (SELECT Price From size WHERE ID = (SELECT Size_ID FROM INSERTED)) IS NULL
436SET @pizza = 0
437ELSE
438SET @pizza = (SELECT Price From size WHERE ID = (SELECT Size_ID FROM INSERTED))
439
440UPDATE Orders
441SET Order_price = Order_price + @drink + @sauce + @pizza
442WHERE Order_ID = (SELECT Order_ID FROM INSERTED)
443
444END
445--BIG PICA promocja get two another 25% off
446
447CREATE TRIGGER proc_OFF_BIG_PIZZA
448ON dbo.Products
449FOR INSERT
450AS
451BEGIN
452DECLARE @Promo int;
453DECLARE @poz INT;
454
455SET @poz = (SELECT COUNT(*) FROM Products
456WHERE Order_ID = (SELECT Order_ID From Inserted) AND Size_ID = 3)
457
458SET @Promo = (SELECT Price FROM Size Where ID = 3) * 0.25
459
460IF (SELECT Size_ID From Inserted) = 3 AND @poz % 2 = 0
461 UPDATE Orders
462 SET Order_price = Order_price - @Promo WHERE Order_ID = (SELECT Order_ID FROM INSERTED)
463
464END
465
466
467 --NIEPRAWIDLOWY FORMAT EMAIL--
468
469CREATE TRIGGER email_checkT
470ON dbo.Employee
471FOR INSERT
472AS
473BEGIN
474DECLARE @CHECK varchar(40);
475SET @CHECK = (SELECT E_mail from INSERTED)
476IF dbo.checkEmail(@CHECK) = 0
477 BEGIN
478 RAISERROR('ERROR - bad E-mail format', 1, 2)
479 DELETE FROM Employee WHERE Employee_ID = (select Employee_ID from inserted)
480 END
481END
482
483
484 --PRODUCT ALREADY EXISTS--
485
486CREATE TRIGGER exists_pizza
487ON dbo.pizza
488FOR INSERT
489AS
490BEGIN
491
492IF (SELECT COUNT(Pizza_Name) FROM pizza WHERE Pizza_Name = (SELECT Pizza_Name FROM INSERTED)) > 1
493 BEGIN
494 RAISERROR('ERROR - THIS PIZZA ALREADY EXISTS IN DATABASE', 1, 2)
495 DELETE FROM Pizza WHERE Pizza_Name = (SELECT Pizza_Name FROM INSERTED)
496 END
497
498END
499----------
500CREATE TRIGGER exists_drink
501ON dbo.Drink
502FOR INSERT
503AS
504BEGIN
505
506IF (SELECT COUNT(Drink_Name) FROM Drink WHERE Drink_Name = (SELECT Drink_Name FROM INSERTED)) > 1
507 BEGIN
508 RAISERROR('ERROR - THIS DRINK ALREADY EXISTS IN DATABASE', 1, 2)
509 DELETE FROM Drink WHERE Drink_Name = (SELECT Drink_Name FROM INSERTED)
510 END
511
512END
513----------
514CREATE TRIGGER exists_sauce
515ON dbo.Sauce
516FOR INSERT
517AS
518BEGIN
519
520IF (SELECT COUNT(Sauce_Name) FROM Sauce WHERE Sauce_Name = (SELECT Sauce_Name FROM INSERTED)) > 1
521 BEGIN
522 RAISERROR('ERROR - THIS SAUCE ALREADY EXISTS IN DATABASE', 1, 2)
523 DELETE FROM Sauce WHERE Sauce_Name = (SELECT Sauce_Name FROM INSERTED)
524 END
525
526END
527 --INFORMACJA O ROBOTNIKACH--
528CREATE VIEW number
529AS
530select e.Employee_ID,count(e.Employee_ID) as number from Employee as e
531INNER JOIN EmployeeOrders as o ON e.Employee_ID = o.Employee_ID
532Group by e.Employee_ID
533GO
534
535CREATE VIEW Employee_INFO
536AS
537
538SELECT TOP 100 PERCENT e.Employee_ID, e.Name, e.Surname, e.Birthday, e.Job_position, e.Date_hired, e.PESEL, e.Salary, e.E_mail, e.Phone_Nr,
539ea.Country, ea.City, ea.Street_Name, ea.Street_Number, ea.Door_Number, n.number
540FROM Employee as e JOIN Employee_Adress as ea
541ON e.Employee_ID = ea.Employee_ID
542LEFT JOIN number as n ON e.Employee_ID = n.Employee_ID
543GROUP BY e.Employee_ID, e.Name, e.Surname, e.Birthday, e.Job_position, e.Date_hired, e.PESEL, e.Salary, e.E_mail, e.Phone_Nr,
544ea.Country, ea.City, ea.Street_Name, ea.Street_Number, ea.Door_Number,n.number
545ORDER BY e.Employee_ID ASC
546
547GO
548
549
550 --PIZZA MENU--
551CREATE VIEW Menu_pizza
552AS
553select Pizza_ID,Pizza_Name, Toppings,Pizza_Type,
554(select price as '20 cm' from size where id = 1) as 'Price of Small',
555(select price as '20 cm' from size where id = 2) as 'Price of Medium',
556(select price as '20 cm' from size where id = 3) as 'Price of Large'
557FROM pizza
558
559GO
560
561 --POPULAR PIZZA--
562
563
564CREATE VIEW pizza_popularity
565AS
566SELECT TOP 100 PERCENT p.Pizza_ID,p.Pizza_Name,p.Toppings,p.Pizza_Type,
567(select count(pa.Pizza_ID) from products as pa where pa.Pizza_ID = p.Pizza_ID) as 'Pizza number'
568FROM Pizza as p
569ORDER BY 'Pizza number' DESC
570GO
571
572-------------------------
573
574CREATE VIEW drink_popularity
575AS
576SELECT TOP 100 PERCENT d.Drink_ID,d.Drink_Name,d.Drink_type,d.Drink_description,d.Price,
577(select count(pa.Drink_ID) from products as pa where pa.Drink_ID = d.Drink_ID) as 'Drink number'
578FROM Drink as d
579ORDER BY 'Drink number' DESC
580GO
581
582----------------------------
583
584CREATE VIEW sauce_popularity
585AS
586SELECT TOP 100 PERCENT s.Sauce_ID,s.Sauce_Name,s.Sauce_type,s.Sauce_description,s.Price,
587(select count(pa.Sauce_ID) from products as pa where pa.Sauce_ID = s.sauce_ID) as 'Sauce number'
588FROM sauce as s
589ORDER BY 'Sauce number' DESC
590GO
591
592 --ORDER INFO--
593CREATE VIEW ORDER_INFO
594AS
595select TOP 100 PERCENT o.Order_ID, o.Date_order, o.Order_price, o.Customer_ID, e.Employee_ID,e.Name, e.Surname, pm.Method, ot.Order_type,
596(select count(pa.Pizza_ID) from products as pa where pa.Order_ID = o.Order_ID) as 'Pizza count',
597(select count(p.Drink_ID) from products as p where p.Order_ID = o.Order_ID) as 'Drink count',
598(select count(s.sauce_ID) from products as s where s.Order_ID = o.Order_ID) as 'Sauce count'
599from orders as o
600join EmployeeOrders as eo on eo.Order_ID=o.Order_ID
601join Employee as e on e.Employee_ID = eo.Employee_ID
602join Payment_Method as pm on pm.Payment_Method_ID = o.Payment_Method_ID
603join Order_Type as ot on o.Order_type = ot.Order_Type_ID
604ORDER BY Order_ID DESC
605GO
606
607
608 --CAR INFO--
609CREATE VIEW car_info
610AS
611select c.car_ID, c.brand, c.model, c.date_bought, c.licens_plate, cs.Accident_number,cs.mileage,cs.insurance_end,e.Employee_ID, e.Name, e.Surname from Car as c
612left join Employee as e on c.Employee_ID = e.Employee_ID
613join car_status as cs on c.car_ID = cs.car_ID
614GO
615
616 --NAJPOPULARNIEJSZE MIASTA DOSTAWY--
617
618
619CREATE VIEW DELIVERY_POPULARITY_CITY
620AS
621select TOP 100 PERCENT Street_Name, count(Street_Name) as 'Number of Orders' FROM Customer_Adress
622WHERE Street_Name IS NOT NULL
623Group by Street_Name
624ORDER BY 'Number of Orders' DESC
625GO
626
627 --DOCHOD ZA MIESIAC--
628
629
630CREATE VIEW Monthly_Income
631AS
632SELECT TOP 100 PERCENT Date_Order, SUM(Order_price) as 'Income' FROM Orders
633GROUP BY Date_Order
634ORDER BY Date_Order ASC
635GO
636
637--OSOBY KTORE ZARABIAJA MNIEJ NIZ SREDNIA--
638CREATE VIEW BELOW_AVG
639AS
640SELECT TOP 100 PERCENT Employee_ID,Name,Surname,Birthday,Job_position,Date_Hired,PESEL,Salary FROM Employee
641GROUP BY Employee_ID,Name,Surname,Birthday,Job_position,Date_Hired,PESEL,Salary
642HAVING salary < (select avg(salary) from Employee)
643ORDER BY Salary DESC
644GO