· 8 years ago · Jun 10, 2018, 11:16 AM
1 --DROP TABLES--
2--=====================================================--
3DROP TABLE IF EXISTS car_status
4DROP TABLE IF EXISTS Car
5DROP TABLE IF EXISTS Customer_Adress
6DROP TABLE IF EXISTS Employee_Adress
7DROP TABLE IF EXISTS EmployeeOrders
8DROP TABLE IF EXISTS Employee
9DROP TABLE IF EXISTS Products
10DROP TABLE IF EXISTS Drink
11DROP TABLE IF EXISTS Pizza
12DROP TABLE IF EXISTS Sauce
13DROP TABLE IF EXISTS size
14DROP TABLE IF EXISTS Orders
15DROP TABLE IF EXISTS Order_Type
16DROP TABLE IF EXISTS Payment_Method
17DROP TABLE IF EXISTS Customer
18
19 --DROP FUNCTIONS--
20--=====================================================--
21DROP FUNCTION IF EXISTS znak
22DROP FUNCTION IF EXISTS checkEmail
23
24 --DROP TRIGGERS--
25--=====================================================--
26DROP TRIGGER IF EXISTS Format_Number_E
27DROP TRIGGER IF EXISTS Format_Number_C
28DROP TRIGGER IF EXISTS Order_PriceT
29DROP TRIGGER IF EXISTS proc_OFF_BIG_PIZZA
30DROP TRIGGER IF EXISTS email_checkT
31DROP TRIGGER IF EXISTS exists_pizza
32DROP TRIGGER IF EXISTS exists_drink
33DROP TRIGGER IF EXISTS exists_sauce
34
35 --DROP PROCEDURES--
36--=====================================================--
37DROP PROCEDURE IF EXISTS CREATE_ORDER
38DROP PROCEDURE IF EXISTS Add_Product
39DROP PROCEDURE IF EXISTS give_take_car
40DROP PROCEDURE IF EXISTS salary_up
41DROP PROCEDURE IF EXISTS Show_Employee_Orders
42
43 --DROP VIEW--
44--=====================================================--
45DROP VIEW IF EXISTS number
46DROP VIEW IF EXISTS Employee_INFO
47DROP VIEW IF EXISTS Menu_pizza
48DROP VIEW IF EXISTS pizza_popularity
49DROP VIEW IF EXISTS drink_popularity
50DROP VIEW IF EXISTS sauce_popularity
51DROP VIEW IF EXISTS ORDER_INFO
52DROP VIEW IF EXISTS car_info
53DROP VIEW IF EXISTS DELIVERY_POPULARITY_CITY
54DROP VIEW IF EXISTS Monthly_Income
55DROP VIEW IF EXISTS BELOW_AVG
56GO
57--==========================================================================================================--
58 --TABLES--
59
60 --EMPLOYEE TBALES--
61--==========================================================--
62
63 --EMPLOYEE--
64CREATE TABLE dbo.Employee (
65 Employee_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
66 Name varchar(100) NOT NULL,
67 Surname varchar(100) NOT NULL,
68 Birthday date NOT NULL DEFAULT GETDATE(),
69 Job_position varchar(100) NOT NULL,
70 Date_hired date NOT NULL DEFAULT GETDATE(),
71 PESEL varchar(11) NOT NULL UNIQUE CHECK(LEN(pesel) = 11),
72 Salary MONEY NOT NULL DEFAULT (0),
73 E_mail varchar(30) NULL DEFAULT (NULL),
74 Phone_Nr varchar(14) NULL DEFAULT (NULL),
75);
76
77 --EMPLOYEE ADRESS--
78CREATE TABLE dbo.Employee_Adress(
79 Adress_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
80 Country varchar(100) NOT NULL DEFAULT (NULL),
81 City varchar(100) NOT NULL DEFAULT (NULL),
82 Street_Name varchar(100) NOT NULL DEFAULT (NULL),
83 Street_Number varchar(50) NOT NULL DEFAULT (NULL),
84 Door_Number varchar(10) DEFAULT (NULL),
85
86 Employee_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Employee(Employee_ID) on delete cascade
87);
88
89-------------------------------------------------------------------
90 --ADDITIONAL EMPLOYEE TABLES--
91
92 --CAR--
93CREATE TABLE dbo.Car (
94 Car_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
95 Brand varchar(200) NOT NULL CHECK(LEN(brand) >=2),
96 Model varchar(200) NOT NULL CHECK(LEN(model) >=2),
97 Date_bought date NOT NULL DEFAULT (GETDATE()),
98 Licens_plate varchar(10) NOT NULL UNIQUE,
99
100
101 Employee_ID int NULL FOREIGN KEY REFERENCES dbo.Employee(Employee_ID) on delete set default
102);
103
104 --CAR STATUS--
105CREATE TABLE dbo.car_status (
106 Status_ID int NOT NULL IDENTITY(1,1),
107 Accident_number int NOT NULL DEFAULT (0),
108 Mileage int NOT NULL CHECK(mileage >= 0),
109 Insurance_end date NOT NULL DEFAULT (NULL),
110
111 Car_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Car(Car_ID) on delete cascade,
112);
113--========================================================--
114 --CUSTOMER TABLES--
115--========================================================--
116
117 --CUSTOMER--
118Create table dbo.Customer (
119 Customer_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
120 Name varchar(200) NULL DEFAULT (NULL),
121 Surname varchar(200) NULL DEFAULT (NULL),
122 Phone_Nr varchar(14) NULL DEFAULT (NULL)
123);
124
125 --CUSTOMER ADRESS--
126CREATE TABLE dbo.Customer_Adress(
127 Adress_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
128 City varchar(200) NULL DEFAULT (NULL),
129 Street_Name varchar(200) NULL DEFAULT (NULL),
130 Street_Number int NULL DEFAULT (NULL),
131 Door_Number int NULL DEFAULT (NULL),
132
133 Customer_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Customer(Customer_ID) on delete cascade
134);
135--========================================================--
136 --ORDERS TABLES--
137--========================================================--
138
139 --PAYMENT METHOD--
140CREATE TABLE dbo.Payment_Method(
141 Payment_Method_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
142 Method varchar(50) NULL DEFAULT ('cash'),
143);
144
145 --ORDER TYPE--
146CREATE TABLE dbo.Order_Type(
147 Order_Type_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
148 Order_type varchar(100) NULL DEFAULT ('reastaurant'),
149);
150
151 --ORDERS--
152CREATE TABLE dbo.Orders(
153 Order_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
154 Date_order date NOT NULL DEFAULT GETDATE(),
155 Order_price MONEY NULL CHECK(Order_price >= 0) DEFAULT (0),
156
157 Customer_ID int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Customer(Customer_ID) on delete set default,
158 Payment_Method_ID INT NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Payment_Method(Payment_Method_ID) on delete set default,
159 Order_type int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Order_Type(Order_Type_ID) on delete set default,
160);
161
162 --EMPLOYEE ORDERS (BRIDGE TABLE N:N)--
163CREATE TABLE dbo.EmployeeOrders(
164 ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
165 Employee_ID int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Employee(Employee_ID) on delete cascade,
166 Order_ID int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Orders(Order_ID) on delete cascade,
167);
168
169--========================================================--
170 --PRODUCTS TABLES--
171--========================================================--
172
173 --DRINK--
174CREATE TABLE dbo.Drink(
175 Drink_ID varchar(10) NOT NULL PRIMARY KEY,
176 Drink_Name varchar(50) NOT NULL,
177 Drink_type varchar(50) NOT NULL,
178 Drink_description varchar(200) NOT NULL,
179 Price MONEY NULL DEFAULT (0)
180);
181
182 --PIZZA--
183CREATE TABLE dbo.Pizza(
184 Pizza_ID varchar(10) NOT NULL PRIMARY KEY,
185 Pizza_Name varchar(50) NOT NULL,
186 Toppings varchar(200) NOT NULL,
187 Pizza_type varchar(50) NOT NULL
188);
189
190 --SAUCE--
191CREATE TABLE dbo.Sauce(
192 Sauce_ID varchar(10) NOT NULL PRIMARY KEY,
193 Sauce_Name varchar(50) NOT NULL,
194 Sauce_description varchar(300) NOT NULL,
195 Sauce_type varchar(50) NOT NULL,
196 Price MONEY NULL DEFAULT (0)
197);
198
199 --SIZE--
200CREATE TABLE dbo.size(
201 ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
202 Size varchar(50) NOT NULL DEFAULT('Medium'),
203 Size_cm int NOT NULL DEFAULT(30),
204 Price MONEY NULL DEFAULT (0),
205);
206
207 --PRODUCTS--
208CREATE TABLE dbo.Products(
209 Product_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
210
211 Pizza_ID varchar(10) NULL FOREIGN KEY REFERENCES Pizza(Pizza_ID)on delete set NULL,
212 Sauce_ID varchar(10) NULL FOREIGN KEY REFERENCES Sauce(Sauce_ID)on delete set NULL,
213 Drink_ID varchar(10) NULL FOREIGN KEY REFERENCES Drink(Drink_ID)on delete set NULL,
214 Size_ID INT NULL FOREIGN KEY REFERENCES dbo.size(ID)on delete set NULL,
215 Order_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Orders(Order_ID)on delete cascade,
216);
217GO
218--==========================================================================================================--
219 --FUNCTIONS--
220 --ZMIENIA FORMAT NUMERU TELEFONU--
221
222create function znak(@znaki char(14))
223returns char(14)
224as
225begin
226 declare @a char(3);
227 declare @b char(3);
228 declare @c char(4);
229 declare @d char(14);
230 set @a =(select SUBSTRING(@znaki,1,3));
231 set @b=(select substring(@znaki,4,3));
232 set @c=(select SUBSTRING(@znaki,7,4));
233
234 set @d='('+@a+')'+SPACE(1)+@b+'-'+@c;
235 return @d;
236end
237GO
238
239 --SPRADZ FORMAT MAILOWY--
240
241CREATE FUNCTION checkEmail (@email VARCHAR(50))
242RETURNS INT
243AS
244BEGIN
245 IF (@email NOT LIKE '_%@__%.__%')
246 RETURN 0
247
248 RETURN 1
249END
250GO
251
252
253--==========================================================================================================--
254 --TRIGGERS--
255
256 --FORMATOWANIE NUMERU--
257
258CREATE TRIGGER Format_Number_E
259ON dbo.Employee
260FOR INSERT--update
261AS
262BEGIN
263
264UPDATE Employee
265SET Phone_Nr = (select dbo.znak(Phone_Nr))
266WHERE Employee_ID = (SELECT Employee_ID FROM INSERTED)
267END
268GO
269
270CREATE TRIGGER Format_Number_C
271ON dbo.Customer
272FOR INSERT
273AS
274BEGIN
275
276UPDATE Customer
277SET Phone_Nr = dbo.znak(Phone_Nr)
278WHERE Customer_ID = (SELECT Customer_ID FROM INSERTED)
279END
280GO
281
282
283 --AUTOMATYCZNE OBLICZNIE CENY--
284
285CREATE TRIGGER Order_PriceT
286ON dbo.Products
287FOR INSERT
288AS
289BEGIN
290
291DECLARE @pizza MONEY;
292DECLARE @drink MONEY;
293DECLARE @sauce MONEY
294
295if (SELECT Price From Drink WHERE Drink_ID = (SELECT Drink_ID FROM INSERTED)) IS NULL
296SET @drink = 0
297ELSE
298SET @drink = (SELECT Price From Drink WHERE Drink_ID = (SELECT Drink_ID FROM INSERTED))
299
300if (SELECT Price From Sauce WHERE Sauce_ID = (SELECT Sauce_ID FROM INSERTED)) IS NULL
301SET @sauce = 0
302ELSE
303SET @sauce = (SELECT Price From Sauce WHERE Sauce_ID = (SELECT Sauce_ID FROM INSERTED))
304
305if (SELECT Price From size WHERE ID = (SELECT Size_ID FROM INSERTED)) IS NULL
306SET @pizza = 0
307ELSE
308SET @pizza = (SELECT Price From size WHERE ID = (SELECT Size_ID FROM INSERTED))
309
310UPDATE Orders
311SET Order_price = Order_price + @drink + @sauce + @pizza
312WHERE Order_ID = (SELECT Order_ID FROM INSERTED)
313
314END
315GO
316
317 --BIG PICA promocja get two another 25% off
318
319CREATE TRIGGER proc_OFF_BIG_PIZZA
320ON dbo.Products
321FOR INSERT
322AS
323BEGIN
324DECLARE @Promo int;
325DECLARE @poz INT;
326
327SET @poz = (SELECT COUNT(*) FROM Products
328WHERE Order_ID = (SELECT Order_ID From Inserted) AND Size_ID = 3)
329
330SET @Promo = (SELECT Price FROM Size Where ID = 3) * 0.25
331
332IF (SELECT Size_ID From Inserted) = 3 AND @poz % 2 = 0
333 UPDATE Orders
334 SET Order_price = Order_price - @Promo WHERE Order_ID = (SELECT Order_ID FROM INSERTED)
335
336END
337GO
338
339 --NIEPRAWIDLOWY FORMAT EMAIL--
340
341CREATE TRIGGER email_checkT
342ON dbo.Employee
343FOR INSERT
344AS
345BEGIN
346DECLARE @CHECK varchar(40);
347SET @CHECK = (SELECT E_mail from INSERTED)
348IF dbo.checkEmail(@CHECK) = 0
349 BEGIN
350 RAISERROR('ERROR - bad E-mail format (CORRECT FORMAT: Example@mail.com)', 1, 2)
351 UPDATE Employee
352 SET E_mail = NULL WHERE Employee_ID = (select Employee_ID from inserted)
353 END
354END
355GO
356
357 --PRODUCT ALREADY EXISTS--
358
359CREATE TRIGGER exists_pizza
360ON dbo.pizza
361FOR INSERT
362AS
363BEGIN
364
365IF (SELECT COUNT(Pizza_Name) FROM pizza WHERE Pizza_Name = (SELECT Pizza_Name FROM INSERTED)) > 1
366 BEGIN
367 RAISERROR('ERROR - THIS PIZZA ALREADY EXISTS IN DATABASE', 1, 2)
368 DELETE FROM Pizza WHERE Pizza_Name = (SELECT Pizza_Name FROM INSERTED)
369 END
370
371END
372GO
373----------
374CREATE TRIGGER exists_drink
375ON dbo.Drink
376FOR INSERT
377AS
378BEGIN
379
380IF (SELECT COUNT(Drink_Name) FROM Drink WHERE Drink_Name = (SELECT Drink_Name FROM INSERTED)) > 1
381 BEGIN
382 RAISERROR('ERROR - THIS DRINK ALREADY EXISTS IN DATABASE', 1, 2)
383 DELETE FROM Drink WHERE Drink_Name = (SELECT Drink_Name FROM INSERTED)
384 END
385
386END
387GO
388----------
389CREATE TRIGGER exists_sauce
390ON dbo.Sauce
391FOR INSERT
392AS
393BEGIN
394
395IF (SELECT COUNT(Sauce_Name) FROM Sauce WHERE Sauce_Name = (SELECT Sauce_Name FROM INSERTED)) > 1
396 BEGIN
397 RAISERROR('ERROR - THIS SAUCE ALREADY EXISTS IN DATABASE', 1, 2)
398 DELETE FROM Sauce WHERE Sauce_Name = (SELECT Sauce_Name FROM INSERTED)
399 END
400
401END
402GO
403
404--==========================================================================================================--
405 --PROCEDURES--
406
407 --TWORZENIE ZAMOWIENIA--
408
409CREATE PROCEDURE dbo.CREATE_ORDER
410 @Order_type int,
411 @Employee_1 int, @Employee_2 int,@Payment_Method_ID int,
412 @Name varchar(100), @Surname varchar(100),@Phone_Nr int,
413 @City varchar(100), @Street_Name varchar(100), @Street_Number int, @Door_Number int
414AS
415BEGIN
416 INSERT INTO Customer(Name,Surname,phone_Nr) VALUES(@Name,@Surname,@Phone_Nr)
417
418 DECLARE @Customer_ID int
419 SET @Customer_ID = (SELECT TOP 1 Customer_ID From Customer ORDER BY Customer_ID DESC)
420
421 INSERT INTO Customer_Adress(City,Street_Name,Street_Number,Door_Number,Customer_ID)
422 VALUES(@City,@Street_Name,@Street_Number,@Door_Number,@Customer_ID)
423
424 INSERT INTO Orders(Customer_ID,Payment_Method_ID,Order_type)
425 Values(@Customer_ID,@Payment_Method_ID,@Order_type)
426
427 DECLARE @Order_ID int;
428 SET @Order_ID = (SELECT TOP 1 ORDER_ID From Orders ORDER BY Order_ID DESC)
429 INSERT INTO EmployeeOrders(Employee_ID,Order_ID)
430 VALUES(@Employee_1,@Order_ID)
431 INSERT INTO EmployeeOrders(Employee_ID,Order_ID)
432 VALUES(@Employee_2,@Order_ID)
433END
434GO
435
436
437 --DODAJ PRODUKT/Y DO ZAMOWIENIA--
438
439CREATE PROCEDURE dbo.Add_Product
440 @Order_ID int,
441 @Pizza_ID varchar(10),
442 @Sauce_ID varchar(10),
443 @Drink_ID varchar(10),
444 @Size_ID int
445AS
446insert into Products(Order_ID,Pizza_ID,Sauce_ID,Drink_ID,Size_ID) VALUES(@Order_ID,@Pizza_ID,@Sauce_ID,@Drink_ID,@Size_ID)
447GO
448
449
450 --DAWANIE I ODEJMOWANIE SAMOCHODA PRACOWNIKU--
451
452CREATE PROCEDURE dbo.give_take_car
453@Employee_ID int,
454@Car_ID int
455AS
456BEGIN
457if (select Employee_ID from Car where car_ID=@Car_ID) = @Employee_ID
458UPDATE Car
459SET Employee_ID = NULL
460WHERE car_ID = @Car_ID
461
462else
463UPDATE Car
464SET Employee_ID = @Employee_ID
465WHERE car_ID = @Car_ID
466END
467GO
468
469
470 --DAWANIE PREMII PRACOWNIKU--
471
472CREATE PROCEDURE salary_up
473@Employee_ID int,
474@salary money
475AS
476IF @salary < 0
477 RAISERROR ('Wrong argument with salary',1,2);
478else
479Update Employee set salary = salary + @salary
480WHERE Employee_ID = @Employee_ID;
481GO
482
483 --POKAZ WSZYSTKIE ZAMOWIENIA WYPELNIONE PRZEZ EMPLOYEE_ID--
484
485CREATE PROCEDURE Show_Employee_Orders
486@Employee_ID int
487AS
488SELECT Employee_ID,Name,Surname,Job_position,Date_hired,E_mail,Phone_Nr,Salary FROM Employee
489WHERE Employee_ID = @Employee_ID
490
491SELECT o.Order_ID,o.Date_order, o.Customer_ID,o.Payment_Method_ID,o.Order_Type,o.Order_Price FROM Orders as o
492JOIN EmployeeOrders as eo ON o.Order_ID=eo.Order_ID
493WHERE eo.Employee_ID = @Employee_ID
494GO
495
496--==========================================================================================================--
497 --VIEWS--
498
499 --INFORMACJA O ROBOTNIKACH--
500
501CREATE VIEW number
502AS
503select e.Employee_ID,count(e.Employee_ID) as number from Employee as e
504INNER JOIN EmployeeOrders as o ON e.Employee_ID = o.Employee_ID
505Group by e.Employee_ID
506GO
507
508CREATE VIEW Employee_INFO
509AS
510
511SELECT 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,
512ea.Country, ea.City, ea.Street_Name, ea.Street_Number, ea.Door_Number, n.number
513FROM Employee as e JOIN Employee_Adress as ea
514ON e.Employee_ID = ea.Employee_ID
515LEFT JOIN number as n ON e.Employee_ID = n.Employee_ID
516GROUP 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,
517ea.Country, ea.City, ea.Street_Name, ea.Street_Number, ea.Door_Number,n.number
518ORDER BY e.Employee_ID ASC
519
520GO
521
522
523 --PIZZA MENU--
524
525CREATE VIEW Menu_pizza
526AS
527select Pizza_ID,Pizza_Name, Toppings,Pizza_Type,
528(select price as '20 cm' from size where id = 1) as 'Price of Small',
529(select price as '20 cm' from size where id = 2) as 'Price of Medium',
530(select price as '20 cm' from size where id = 3) as 'Price of Large'
531FROM pizza
532
533GO
534
535 --POPULAR PIZZA--
536
537CREATE VIEW pizza_popularity
538AS
539SELECT TOP 100 PERCENT p.Pizza_ID,p.Pizza_Name,p.Toppings,p.Pizza_Type,
540(select count(pa.Pizza_ID) from products as pa where pa.Pizza_ID = p.Pizza_ID) as 'Pizza number'
541FROM Pizza as p
542ORDER BY 'Pizza number' DESC
543GO
544
545-------------------------
546
547CREATE VIEW drink_popularity
548AS
549SELECT TOP 100 PERCENT d.Drink_ID,d.Drink_Name,d.Drink_type,d.Drink_description,d.Price,
550(select count(pa.Drink_ID) from products as pa where pa.Drink_ID = d.Drink_ID) as 'Drink number'
551FROM Drink as d
552ORDER BY 'Drink number' DESC
553GO
554
555----------------------------
556
557CREATE VIEW sauce_popularity
558AS
559SELECT TOP 100 PERCENT s.Sauce_ID,s.Sauce_Name,s.Sauce_type,s.Sauce_description,s.Price,
560(select count(pa.Sauce_ID) from products as pa where pa.Sauce_ID = s.sauce_ID) as 'Sauce number'
561FROM sauce as s
562ORDER BY 'Sauce number' DESC
563GO
564
565 --ORDER INFO--
566
567CREATE VIEW ORDER_INFO
568AS
569select TOP 100 PERCENT o.Order_ID, o.Date_order, o.Order_price, o.Customer_ID,
570e.Employee_ID,e.Name, e.Surname, pm.Method, ot.Order_type,
571(select count(pa.Pizza_ID) from products as pa where pa.Order_ID = o.Order_ID) as 'Pizza count',
572(select count(p.Drink_ID) from products as p where p.Order_ID = o.Order_ID) as 'Drink count',
573(select count(s.sauce_ID) from products as s where s.Order_ID = o.Order_ID) as 'Sauce count'
574from orders as o
575join EmployeeOrders as eo on eo.Order_ID=o.Order_ID
576join Employee as e on e.Employee_ID = eo.Employee_ID
577join Payment_Method as pm on pm.Payment_Method_ID = o.Payment_Method_ID
578join Order_Type as ot on o.Order_type = ot.Order_Type_ID
579ORDER BY Order_ID DESC
580GO
581
582
583 --CAR INFO--
584
585CREATE VIEW car_info
586AS
587select 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
588left join Employee as e on c.Employee_ID = e.Employee_ID
589join car_status as cs on c.car_ID = cs.car_ID
590GO
591
592 --NAJPOPULARNIEJSZE MIASTA DOSTAWY--
593
594CREATE VIEW DELIVERY_POPULARITY_CITY
595AS
596select TOP 100 PERCENT Street_Name, count(Street_Name) as 'Number of Orders' FROM Customer_Adress
597WHERE Street_Name IS NOT NULL
598Group by Street_Name
599ORDER BY 'Number of Orders' DESC
600GO
601
602 --DOCHOD ZA MIESIAC--
603
604CREATE VIEW Monthly_Income
605AS
606SELECT TOP 100 PERCENT Date_Order, SUM(Order_price) as 'Income' FROM Orders
607GROUP BY Date_Order
608ORDER BY Date_Order ASC
609GO
610
611 --OSOBY KTORE ZARABIAJA MNIEJ NIZ SREDNIA--
612
613CREATE VIEW BELOW_AVG
614AS
615SELECT TOP 100 PERCENT Employee_ID,Name,Surname,Birthday,Job_position,Date_Hired,PESEL,Salary FROM Employee
616GROUP BY Employee_ID,Name,Surname,Birthday,Job_position,Date_Hired,PESEL,Salary
617HAVING salary < (select avg(salary) from Employee)
618ORDER BY Salary DESC
619GO
620
621--==========================================================================================================--
622 --INSERT TO DATABASE--
623
624 --EMPLOYEE--
625insert into dbo.Employee values('Dariusz','Rodzewicz','1998-12-18','Piekarz','2013-05-15','20054788612',120,'DRodzewicz@mgmail.com','861214043')
626insert into dbo.Employee values('Robert','Radulewicz','1998-04-07','Piekarz','2013-05-15','15937846132',600,'RobRAdulew@gmail.com','861234557')
627insert into dbo.Employee values('Lukasz','Dowgialo','1997-06-01','Kurier','2013-05-15','98712314785',1000,'DowgiaLukas@gmail.com','86856984')
628insert into dbo.Employee values('Ryszard','Granicki','1997-06-28','Piekarz','2013-05-15','65412398752',800,'RysiukGran@gmail.com','861523698')
629insert into dbo.Employee values('Lukasz','Baniukiewicz','1998-12-18','Kurier','2013-05-15','12589637415',1200,'Baniuk1999@gmail.com','86141586')
630insert into dbo.Employee values('Grzegorz','Wojtkiewicz','1999-01-16','Kasier','2013-05-15','12356987452',450,'WojtGrisha@gmail.com','861740012')
631insert into dbo.Employee values('Andrzej','Tichonow','1986-03-27','Piekarz','2013-05-15','20054788614',2000,'Lenkas89@gmail.com','861115225')
632insert into dbo.Employee values('Julia','Stanulyte','1998-10-11','Piekarz','2013-05-15','20111552386',600,'HarcJulia@gmail.com','861158996')
633insert into dbo.Employee values('Adriana','Wiszniewska','1996-08-22','Kasier','2013-05-15','12000254550',850,'MilionAdri@gmail.com','8614311258')
634insert into dbo.Employee values('Barbara','Zinkiewicz','1998-02-28','Kurier','2013-05-15','25555521587',775,'BasiutaZ@gmail.com','8645558555')
635--select * from Employee
636
637 --EMPLOYEE ADRESS--
638insert into dbo.Employee_Adress values('Lithuania','Vilnius','Erfuro','122','44',1)
639insert into dbo.Employee_Adress values('Poland','Gdansk','Pomorska','44',NULL,2)
640insert into dbo.Employee_Adress values('Poland','Gdansk','3-Maja','144','75',3)
641insert into dbo.Employee_Adress values('Poland','Gdynia','Armii_Krajowej','98','B',4)
642insert into dbo.Employee_Adress values('Poland','Sopot','Bitwy_Pod_Plowcami','64','209',5)
643insert into dbo.Employee_Adress values('Poland','Sopot','Bitwy_Pod_Plowcami','64','209',6)
644insert into dbo.Employee_Adress values('Poland','Gdansk','3-Maja','75',NULL,7)
645insert into dbo.Employee_Adress values('Poland','Gdynia','Mickiewicza','47','65',8)
646insert into dbo.Employee_Adress values('Belarus','Grodno','Zapackaja','44','56',9)
647insert into dbo.Employee_Adress values('Poland','Gdansk','Reja','69','A',10)
648--select * from Employee_Adress
649
650 --CAR--
651insert into dbo.Car values('Toyota','Auris','2014-08-15','GDN-45378',NULL),
652 ('Opel','Astra','2015-09-24','GDN-00218',NULL),
653 ('Volkswagen','Golf','2014-10-02','GDN-95734',NULL);
654--select * from Car
655
656 --CAR STATUS--
657insert into dbo.car_status values(0,150000,'2020-12-15',1),
658 (1,192025,'2020-12-15',2),
659 (0,134008,'2020-12-15',3);
660--select * from car_status
661
662 --PAYMENT METHOD--
663insert into dbo.Payment_Method values('cash'),
664 ('credit'),
665 ('PayPal'),
666 ('Bank Transfer');
667--select * from Payment_Method
668
669 --ORDER TYPE--
670insert into dbo.Order_Type values('Restaurant'),
671 ('Take-Away'),
672 ('Delivery');
673--select * from Order_Type
674
675 --PIZZA SIZE--
676insert into dbo.size values('Small',25,20),
677 ('Medium',30,26),
678 ('Large',40,34);
679--select * from size
680
681 --SAUCE--
682insert into dbo.Sauce values('CZWY','Czosnakowy','Nie ma','lagodny',2)
683insert into dbo.Sauce values('POWY','Pomidorowy','Nie ma','lagodny',1.5)
684insert into dbo.Sauce values('ARSKI','Arabski','Nie ma','ostry',2)
685insert into dbo.Sauce values('MIWY','Miodowy','Nie ma','slodki',2)
686insert into dbo.Sauce values('MUWY','Musztardowy','Nie ma','Gorzki',1.5)
687insert into dbo.Sauce values('PIWY','Piepszowy','Nie ma','Gorzki',2)
688--select * from Sauce
689
690 --PIZZA--
691insert into dbo.Pizza values('AlNO','Albano','ser, oregano, papryka, boczek, szynka, cebula','tradycyjna')
692insert into dbo.Pizza values('CASA','Calabresa','ser, oregano, salami, oliwki, papryczki apalenos, tabasco','ostra')
693insert into dbo.Pizza values('MACO','Marco','ser, oregano, salami, pieczarki','tradycyjna')
694insert into dbo.Pizza values('BAMA','Bahama','ser,oregano, szynka, ananas','slodka')
695insert into dbo.Pizza values('MATA','Margherita','ser, oregano','tradycyjna')
696insert into dbo.Pizza values('FONA','Foggiana',' ser, oregano, pieczarki','tradycyjna')
697insert into dbo.Pizza values('ONE','1x1','sos, ser, oregano, szynka','tradycyjna')
698insert into dbo.Pizza values('PORA','Povera','sos, ser, oregano, szynka, pieczarki','tradycyjna')
699insert into dbo.Pizza values('UNDO','Un Ricordo','sos, ser, oregano, szynka, kukurydza','tradycyjna')
700insert into dbo.Pizza values('UUGI','Quatro Formaggi','sos, ser, oregano, ser feta, ser pleśniowy, ser gouda','serowa')
701--select* from Pizza
702
703 --DRIKNS--
704insert into dbo.Drink values('CZHE','Czarna Herbata','Herbata','aroma',4)
705insert into dbo.Drink values('ZIHE','Zielona Herbata','Herbata','aroma',4)
706insert into dbo.Drink values('COLA','Co-Ca-Cola','gazowane','smczna',5)
707insert into dbo.Drink values('SPTE','Sprite','gazowane','smczna',5)
708insert into dbo.Drink values('FATA','Fanta','gazowane','smczna',5)
709--select* from Drink
710
711--==========================================================================================================--
712 --TESTOWANIE--
713 ---------------------
714 --TRIGGERS--
715
716 --Format_Number_E--
717--korzystamy z funkcji formatowania numeru
718insert into dbo.Employee values('Test','Testowy','2010-10-10','Piekarz','2013-05-15','00000000142',120,'DRodzewicz@mgmail.com','861214043')
719insert into dbo.Employee_Adress values('Poland','Gdansk','Reja','69','A',11)
720SELECT *FROM EMPLOYEE WHERE Employee_ID = 11
721
722 --Format_Number_C--
723--korzystamy z funkcji formatowania numeru
724INSERT INTO Customer Values('Tester','Testowy','814523654')
725SELECT * FROM Customer
726DELETE FROM Customer WHere Name = 'Tester'
727
728 --Order_PriceT--
729--oblicza za nas cene calego zamowienia (pokaze to w sekcji procedury przy dodaniu produktow do zamowienia)
730
731 --proc_OFF_BIG_PIZZA--
732--przy zamowieniu parzystej pary Duzych pic za druga pice placisz o 25% mniej (pokaze to w sekcji procedury przy dodaniu produktow do zamowienia)
733
734 --email_checkT--
735--korzystamy z funkcji sprawdzania formatu E-mail
736insert into dbo.Employee values('Test2','Testowy2','2010-10-10','Piekarz','2013-05-15','00000000131',120,'DRodzewiczmgmail.com','861214043')
737insert into dbo.Employee_Adress values('Poland','Gdansk','Reja','69','A',11)
738SELECT *FROM EMPLOYEE WHERE Employee_ID = 11
739UPDATE dbo.Employee SET E_mail = 'example@mail.com' WHERE Employee_ID = 11
740
741 --exists_pizza--
742insert into dbo.Pizza values('A123','Albano','ser, oregano, papryka, boczek, szynka, cebula','tradycyjna')
743 --exists_drink--
744insert into dbo.Drink values('CZ12','Czarna Herbata','Herbata','aroma',4)
745 -- exists_sauce--
746insert into dbo.Sauce values('CZ14','Czosnakowy','Nie ma','lagodny',2)
747
748 ---------------------
749 --PROCEDURES--
750
751 --CREATE_ORDER--
752
753select * from Order_Type
754select * from Employee
755select * from Payment_Method
756select * from Orders
757
758EXEC CREATE_ORDER 3,2,3,2,'Test','TEstowy','124253789','Gdansk','Barzynskiego',64,122
759--
760
761 --Add_Product--
762
763select * FROM pizza
764select * from Drink
765select * from sauce
766select * from size
767select * from products
768
769EXEC Add_Product 1,'CASA','MIWY','COLA',3
770EXEC Add_Product 1,'FONA',NULL,NULL,3
771--34+34-8.5+2+5=66.5
772
773 --give_take_car--
774
775SELECT * FROM Employee
776SELECT * FROM Car
777
778EXEC give_take_car 5,2
779
780 --salary_up--
781
782SELECT * FROM Employee
783
784EXEC salary_up 1,80
785
786 --Show_Employee_Orders--
787exec Show_Employee_Orders 2
788
789------------------------------------------------------------------TWORZYMY ZAMOIENIA DO PRZYKLADU NA WIDOKI
790EXEC CREATE_ORDER 3,2,3,2,'Test2','TEstowy2','124253789','Gdansk','Barzynskiego',64,122
791EXEC Add_Product 2,'MATA','MIWY','COLA',3
792EXEC Add_Product 2,'CASA',NULL,'COLA',1
793
794EXEC CREATE_ORDER 3,5,7,2,'Test3','TEstowy3','124253789','Gdansk','Zaspa',64,NULL
795EXEC Add_Product 3,'MATA','POWY','FATA',3
796EXEC Add_Product 3,'CASA',NULL,NULL,2
797EXEC Add_Product 3,'CASA',NULL,NULL,2
798
799EXEC CREATE_ORDER 3,5,7,2,'Test4','TEstowy4','124253789','Gdansk','Zaspa',64,NULL
800EXEC Add_Product 4,'MATA','MUWY','FATA',3
801EXEC Add_Product 4,'CASA',NULL,NULL,1
802EXEC Add_Product 4,'CASA',NULL,'',2
803
804EXEC CREATE_ORDER 3,5,7,2,'Test5','TEstowy5','124253789','SOPOT','MONCIAK',22,47
805EXEC Add_Product 5,'FONA','MUWY','FATA',3
806EXEC Add_Product 5,'FONA',NULL,NULL,1
807EXEC Add_Product 5,'FONA',NULL,NULL,2
808
809EXEC CREATE_ORDER 3,10,7,3,'Test6','TEstowy6','124253789','SOPOT','3-maja',22,47
810EXEC Add_Product 6,'FONA','MUWY','SPTE',2
811
812EXEC CREATE_ORDER 1,10,9,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL
813EXEC Add_Product 7,'FONA','MUWY','SPTE',2
814
815EXEC CREATE_ORDER 1,8,9,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL
816EXEC Add_Product 8,'FONA','MUWY','SPTE',1
817
818EXEC CREATE_ORDER 2,8,3,3,'TEST9','Testowy9','861214753',NULL,NULL,NULL,NULL
819EXEC Add_Product 9,'PORA','MUWY','SPTE',1
820EXEC Add_Product 9,'PORA',NULL,NULL,2
821EXEC Add_Product 9,'PORA',NULL,NULL,2
822
823 ---------------------
824 --VIEWS--
825
826 --Employee_INFO--
827SELECT * from Employee_INFO
828 --Menu_pizza--
829SELECT * FROM Menu_pizza
830 --pizza_popularity--
831SELECT * FROM pizza_popularity
832 --drink_popularity--
833SELECT * FROM drink_popularity
834 --sauce_popularity--
835SELECT * FROM sauce_popularity
836 --ORDER_INFO--
837SELECT * FROM ORDER_INFO
838 --car_info--
839SELECT * FROM car_info
840 --DELIVERY_POPULARITY_CITY--
841SELECT * FROM DELIVERY_POPULARITY_CITY
842 --Monthly_Income--
843SELECT * FROM Monthly_Income
844 --BELOW_AVG--
845SELECT * FROM BELOW_AVG