· 8 years ago · Jun 09, 2018, 10:42 PM
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
56
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) NOT 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);
217
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
237
238 --SPRADZ FORMAT MAILOWY--
239drop function checkEmail
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
315
316 --BIG PICA promocja get two another 25% off
317
318CREATE TRIGGER proc_OFF_BIG_PIZZA
319ON dbo.Products
320FOR INSERT
321AS
322BEGIN
323DECLARE @Promo int;
324DECLARE @poz INT;
325
326SET @poz = (SELECT COUNT(*) FROM Products
327WHERE Order_ID = (SELECT Order_ID From Inserted) AND Size_ID = 3)
328
329SET @Promo = (SELECT Price FROM Size Where ID = 3) * 0.25
330
331IF (SELECT Size_ID From Inserted) = 3 AND @poz % 2 = 0
332 UPDATE Orders
333 SET Order_price = Order_price - @Promo WHERE Order_ID = (SELECT Order_ID FROM INSERTED)
334
335END
336
337
338 --NIEPRAWIDLOWY FORMAT EMAIL--
339
340CREATE TRIGGER email_checkT
341ON dbo.Employee
342FOR INSERT
343AS
344BEGIN
345DECLARE @CHECK varchar(40);
346SET @CHECK = (SELECT E_mail from INSERTED)
347IF dbo.checkEmail(@CHECK) = 0
348 BEGIN
349 RAISERROR('ERROR - bad E-mail format', 1, 2)
350 DELETE FROM Employee WHERE Employee_ID = (select Employee_ID from inserted)
351 END
352END
353
354
355 --PRODUCT ALREADY EXISTS--
356
357CREATE TRIGGER exists_pizza
358ON dbo.pizza
359FOR INSERT
360AS
361BEGIN
362
363IF (SELECT COUNT(Pizza_Name) FROM pizza WHERE Pizza_Name = (SELECT Pizza_Name FROM INSERTED)) > 1
364 BEGIN
365 RAISERROR('ERROR - THIS PIZZA ALREADY EXISTS IN DATABASE', 1, 2)
366 DELETE FROM Pizza WHERE Pizza_Name = (SELECT Pizza_Name FROM INSERTED)
367 END
368
369END
370----------
371CREATE TRIGGER exists_drink
372ON dbo.Drink
373FOR INSERT
374AS
375BEGIN
376
377IF (SELECT COUNT(Drink_Name) FROM Drink WHERE Drink_Name = (SELECT Drink_Name FROM INSERTED)) > 1
378 BEGIN
379 RAISERROR('ERROR - THIS DRINK ALREADY EXISTS IN DATABASE', 1, 2)
380 DELETE FROM Drink WHERE Drink_Name = (SELECT Drink_Name FROM INSERTED)
381 END
382
383END
384----------
385CREATE TRIGGER exists_sauce
386ON dbo.Sauce
387FOR INSERT
388AS
389BEGIN
390
391IF (SELECT COUNT(Sauce_Name) FROM Sauce WHERE Sauce_Name = (SELECT Sauce_Name FROM INSERTED)) > 1
392 BEGIN
393 RAISERROR('ERROR - THIS SAUCE ALREADY EXISTS IN DATABASE', 1, 2)
394 DELETE FROM Sauce WHERE Sauce_Name = (SELECT Sauce_Name FROM INSERTED)
395 END
396
397END
398
399--==========================================================================================================--
400 --PROCEDURES--
401
402 --TWORZENIE ZAMOWIENIA--
403
404IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'CREATE_ORDER')
405DROP PROCEDURE CREATE_ORDER
406GO
407
408CREATE PROCEDURE dbo.CREATE_ORDER
409 @Order_type int,
410 @Employee_1 int, @Employee_2 int,@Payment_Method_ID int,
411 @Name varchar(100), @Surname varchar(100),@Phone_Nr int,
412 @City varchar(100), @Street_Name varchar(100), @Street_Number int, @Door_Number int
413AS
414BEGIN
415 INSERT INTO Customer(Name,Surname,phone_Nr) VALUES(@Name,@Surname,@Phone_Nr)
416
417 DECLARE @Customer_ID int
418 SET @Customer_ID = (SELECT TOP 1 Customer_ID From Customer ORDER BY Customer_ID DESC)
419
420 INSERT INTO Customer_Adress(City,Street_Name,Street_Number,Door_Number,Customer_ID)
421 VALUES(@City,@Street_Name,@Street_Number,@Door_Number,@Customer_ID)
422
423 INSERT INTO Orders(Customer_ID,Payment_Method_ID,Order_type)
424 Values(@Customer_ID,@Payment_Method_ID,@Order_type)
425
426 DECLARE @Order_ID int;
427 SET @Order_ID = (SELECT TOP 1 ORDER_ID From Orders ORDER BY Order_ID DESC)
428 INSERT INTO EmployeeOrders(Employee_ID,Order_ID)
429 VALUES(@Employee_1,@Order_ID)
430 INSERT INTO EmployeeOrders(Employee_ID,Order_ID)
431 VALUES(@Employee_2,@Order_ID)
432END
433GO
434
435
436 --DODAJ PRODUKT/Y DO ZAMOWIENIA--
437
438IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Add_Product')
439DROP PROCEDURE Add_Product
440GO
441
442CREATE PROCEDURE dbo.Add_Product
443 @Order_ID int,
444 @Pizza_ID varchar(10),
445 @Sauce_ID varchar(10),
446 @Drink_ID varchar(10),
447 @Size_ID int
448AS
449insert into Products(Order_ID,Pizza_ID,Sauce_ID,Drink_ID,Size_ID) VALUES(@Order_ID,@Pizza_ID,@Sauce_ID,@Drink_ID,@Size_ID)
450GO
451
452
453 --DAWANIE I ODEJMOWANIE SAMOCHODA PRACOWNIKU--
454
455IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'give_take_car')
456DROP PROCEDURE give_take_car
457GO
458
459CREATE PROCEDURE dbo.give_take_car
460@Employee_ID int,
461@Car_ID int
462AS
463BEGIN
464if (select Employee_ID from Car where car_ID=@Car_ID) = @Employee_ID
465UPDATE Car
466SET Employee_ID = NULL
467WHERE car_ID = @Car_ID
468
469else
470UPDATE Car
471SET Employee_ID = @Employee_ID
472WHERE car_ID = @Car_ID
473END
474GO
475
476
477 --DAWANIE PREMII PRACOWNIKU--
478
479IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'salary_up')
480DROP PROCEDURE salary_up
481GO
482
483CREATE PROCEDURE salary_up
484@Employee_ID int,
485@salary money
486AS
487IF @salary < 0
488 RAISERROR ('Wrong argument with salary',1,2);
489else
490Update Employee set salary = salary + @salary
491WHERE Employee_ID = @Employee_ID;
492GO
493
494 --POKAZ WSZYSTKIE ZAMOWIENIA WYPELNIONE PRZEZ EMPLOYEE_ID--
495
496IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Show_Employee_Orders')
497DROP PROCEDURE Show_Employee_Orders
498GO
499
500CREATE PROCEDURE Show_Employee_Orders
501@Employee_ID int
502AS
503SELECT Employee_ID,Name,Surname,Job_position,Date_hired,E_mail,Phone_Nr,Salary FROM Employee
504WHERE Employee_ID = @Employee_ID
505
506SELECT o.Order_ID,o.Date_order, o.Customer_ID,o.Payment_Method_ID,o.Order_Type,o.Order_Price FROM Orders as o
507JOIN EmployeeOrders as eo ON o.Order_ID=eo.Order_ID
508WHERE eo.Employee_ID = @Employee_ID
509GO
510
511--==========================================================================================================--
512 --VIEWS--
513
514 --INFORMACJA O ROBOTNIKACH--
515
516CREATE VIEW number
517AS
518select e.Employee_ID,count(e.Employee_ID) as number from Employee as e
519INNER JOIN EmployeeOrders as o ON e.Employee_ID = o.Employee_ID
520Group by e.Employee_ID
521GO
522
523CREATE VIEW Employee_INFO
524AS
525
526SELECT 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,
527ea.Country, ea.City, ea.Street_Name, ea.Street_Number, ea.Door_Number, n.number
528FROM Employee as e JOIN Employee_Adress as ea
529ON e.Employee_ID = ea.Employee_ID
530LEFT JOIN number as n ON e.Employee_ID = n.Employee_ID
531GROUP 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,
532ea.Country, ea.City, ea.Street_Name, ea.Street_Number, ea.Door_Number,n.number
533ORDER BY e.Employee_ID ASC
534
535GO
536
537
538 --PIZZA MENU--
539
540CREATE VIEW Menu_pizza
541AS
542select Pizza_ID,Pizza_Name, Toppings,Pizza_Type,
543(select price as '20 cm' from size where id = 1) as 'Price of Small',
544(select price as '20 cm' from size where id = 2) as 'Price of Medium',
545(select price as '20 cm' from size where id = 3) as 'Price of Large'
546FROM pizza
547
548GO
549
550 --POPULAR PIZZA--
551
552CREATE VIEW pizza_popularity
553AS
554SELECT TOP 100 PERCENT p.Pizza_ID,p.Pizza_Name,p.Toppings,p.Pizza_Type,
555(select count(pa.Pizza_ID) from products as pa where pa.Pizza_ID = p.Pizza_ID) as 'Pizza number'
556FROM Pizza as p
557ORDER BY 'Pizza number' DESC
558GO
559
560-------------------------
561
562CREATE VIEW drink_popularity
563AS
564SELECT TOP 100 PERCENT d.Drink_ID,d.Drink_Name,d.Drink_type,d.Drink_description,d.Price,
565(select count(pa.Drink_ID) from products as pa where pa.Drink_ID = d.Drink_ID) as 'Drink number'
566FROM Drink as d
567ORDER BY 'Drink number' DESC
568GO
569
570----------------------------
571
572CREATE VIEW sauce_popularity
573AS
574SELECT TOP 100 PERCENT s.Sauce_ID,s.Sauce_Name,s.Sauce_type,s.Sauce_description,s.Price,
575(select count(pa.Sauce_ID) from products as pa where pa.Sauce_ID = s.sauce_ID) as 'Sauce number'
576FROM sauce as s
577ORDER BY 'Sauce number' DESC
578GO
579
580 --ORDER INFO--
581
582CREATE VIEW ORDER_INFO
583AS
584select 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,
585(select count(pa.Pizza_ID) from products as pa where pa.Order_ID = o.Order_ID) as 'Pizza count',
586(select count(p.Drink_ID) from products as p where p.Order_ID = o.Order_ID) as 'Drink count',
587(select count(s.sauce_ID) from products as s where s.Order_ID = o.Order_ID) as 'Sauce count'
588from orders as o
589join EmployeeOrders as eo on eo.Order_ID=o.Order_ID
590join Employee as e on e.Employee_ID = eo.Employee_ID
591join Payment_Method as pm on pm.Payment_Method_ID = o.Payment_Method_ID
592join Order_Type as ot on o.Order_type = ot.Order_Type_ID
593ORDER BY Order_ID DESC
594GO
595
596
597 --CAR INFO--
598
599CREATE VIEW car_info
600AS
601select 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
602left join Employee as e on c.Employee_ID = e.Employee_ID
603join car_status as cs on c.car_ID = cs.car_ID
604GO
605
606 --NAJPOPULARNIEJSZE MIASTA DOSTAWY--
607
608CREATE VIEW DELIVERY_POPULARITY_CITY
609AS
610select TOP 100 PERCENT Street_Name, count(Street_Name) as 'Number of Orders' FROM Customer_Adress
611WHERE Street_Name IS NOT NULL
612Group by Street_Name
613ORDER BY 'Number of Orders' DESC
614GO
615
616 --DOCHOD ZA MIESIAC--
617
618CREATE VIEW Monthly_Income
619AS
620SELECT TOP 100 PERCENT Date_Order, SUM(Order_price) as 'Income' FROM Orders
621GROUP BY Date_Order
622ORDER BY Date_Order ASC
623GO
624
625 --OSOBY KTORE ZARABIAJA MNIEJ NIZ SREDNIA--
626
627CREATE VIEW BELOW_AVG
628AS
629SELECT TOP 100 PERCENT Employee_ID,Name,Surname,Birthday,Job_position,Date_Hired,PESEL,Salary FROM Employee
630GROUP BY Employee_ID,Name,Surname,Birthday,Job_position,Date_Hired,PESEL,Salary
631HAVING salary < (select avg(salary) from Employee)
632ORDER BY Salary DESC
633GO
634
635--==========================================================================================================--
636 --INSERT TO DATABASE--
637
638 --EMPLOYEE--
639insert into dbo.Employee values('Dariusz','Rodzewicz','1998-12-18','Piekarz','2013-05-15','20054788612',120,'DRodzewicz@mgmail.com','861214043')
640insert into dbo.Employee values('Robert','Radulewicz','1998-04-07','Piekarz','2013-05-15','15937846132',600,'RobRAdulew@gmail.com','861234557')
641insert into dbo.Employee values('Lukasz','Dowgialo','1997-06-01','Kurier','2013-05-15','98712314785',1000,'DowgiaLukas@gmail.com','86856984')
642insert into dbo.Employee values('Ryszard','Granicki','1997-06-28','Piekarz','2013-05-15','65412398752',800,'RysiukGran@gmail.com','861523698')
643insert into dbo.Employee values('Lukasz','Baniukiewicz','1998-12-18','Kurier','2013-05-15','12589637415',1200,'Baniuk1999@gmail.com','86141586')
644insert into dbo.Employee values('Grzegorz','Wojtkiewicz','1999-01-16','Kasier','2013-05-15','12356987452',450,'WojtGrisha@gmail.com','861740012')
645insert into dbo.Employee values('Andrzej','Tichonow','1986-03-27','Piekarz','2013-05-15','20054788614',2000,'Lenkas89@gmail.com','861115225')
646insert into dbo.Employee values('Julia','Stanulyte','1998-10-11','Piekarz','2013-05-15','20111552386',600,'HarcJulia@gmail.com','861158996')
647insert into dbo.Employee values('Adriana','Wiszniewska','1996-08-22','Kasier','2013-05-15','12000254550',850,'MilionAdri@gmail.com','8614311258')
648insert into dbo.Employee values('Barbara','Zinkiewicz','1998-02-28','Kurier','2013-05-15','25555521587',775,'BasiutaZ@gmail.com','8645558555')
649--select * from Employee
650
651 --EMPLOYEE ADRESS--
652insert into dbo.Employee_Adress values('Lithuania','Vilnius','Erfuro','122','44',1)
653insert into dbo.Employee_Adress values('Poland','Gdansk','Pomorska','44',NULL,2)
654insert into dbo.Employee_Adress values('Poland','Gdansk','3-Maja','144','75',3)
655insert into dbo.Employee_Adress values('Poland','Gdynia','Armii_Krajowej','98','B',4)
656insert into dbo.Employee_Adress values('Poland','Sopot','Bitwy_Pod_Plowcami','64','209',5)
657insert into dbo.Employee_Adress values('Poland','Sopot','Bitwy_Pod_Plowcami','64','209',6)
658insert into dbo.Employee_Adress values('Poland','Gdansk','3-Maja','75',NULL,7)
659insert into dbo.Employee_Adress values('Poland','Gdynia','Mickiewicza','47','65',8)
660insert into dbo.Employee_Adress values('Belarus','Grodno','Zapackaja','44','56',9)
661insert into dbo.Employee_Adress values('Poland','Gdansk','Reja','69','A',10)
662--select * from Employee_Adress
663
664 --CAR--
665insert into dbo.Car values('Toyota','Auris','2014-08-15','GDN-45378',NULL),
666 ('Opel','Astra','2015-09-24','GDN-00218',NULL),
667 ('Volkswagen','Golf','2014-10-02','GDN-95734',NULL);
668--select * from Car
669
670 --CAR STATUS--
671insert into dbo.car_status values(0,150000,'2020-12-15',1),
672 (1,192025,'2020-12-15',2),
673 (0,134008,'2020-12-15',3);
674--select * from car_status
675
676 --PAYMENT METHOD--
677insert into dbo.Payment_Method values('cash'),
678 ('credit'),
679 ('PayPal'),
680 ('Bank Transfer');
681--select * from Payment_Method
682
683 --ORDER TYPE--
684insert into dbo.Order_Type values('Restaurant'),
685 ('Take-Away'),
686 ('Delivery');
687--select * from Order_Type
688
689 --PIZZA SIZE--
690insert into dbo.size values('Small',25,20),
691 ('Medium',30,26),
692 ('Large',40,34);
693--select * from size
694
695 --SAUCE--
696insert into dbo.Sauce values('CZWY','Czosnakowy','Nie ma','lagodny',2)
697insert into dbo.Sauce values('POWY','Pomidorowy','Nie ma','lagodny',1.5)
698insert into dbo.Sauce values('ARSKI','Arabski','Nie ma','ostry',2)
699insert into dbo.Sauce values('MIWY','Miodowy','Nie ma','slodki',2)
700insert into dbo.Sauce values('MUWY','Musztardowy','Nie ma','Gorzki',1.5)
701insert into dbo.Sauce values('PIWY','Piepszowy','Nie ma','Gorzki',2)
702--select * from Sauce
703
704 --PIZZA--
705insert into dbo.Pizza values('AlNO','Albano','ser, oregano, papryka, boczek, szynka, cebula','tradycyjna')
706insert into dbo.Pizza values('CASA','Calabresa','ser, oregano, salami, oliwki, papryczki apalenos, tabasco','ostra')
707insert into dbo.Pizza values('MACO','Marco','ser, oregano, salami, pieczarki','tradycyjna')
708insert into dbo.Pizza values('BAMA','Bahama','ser,oregano, szynka, ananas','slodka')
709insert into dbo.Pizza values('MATA','Margherita','ser, oregano','tradycyjna')
710insert into dbo.Pizza values('FONA','Foggiana',' ser, oregano, pieczarki','tradycyjna')
711insert into dbo.Pizza values('ONE','1x1','sos, ser, oregano, szynka','tradycyjna')
712insert into dbo.Pizza values('PORA','Povera','sos, ser, oregano, szynka, pieczarki','tradycyjna')
713insert into dbo.Pizza values('UNDO','Un Ricordo','sos, ser, oregano, szynka, kukurydza','tradycyjna')
714insert into dbo.Pizza values('UUGI','Quatro Formaggi','sos, ser, oregano, ser feta, ser pleśniowy, ser gouda','serowa')
715--select* from Pizza
716
717 --DRIKNS--
718insert into dbo.Drink values('CZHE','Czarna Herbata','Herbata','aroma',4)
719insert into dbo.Drink values('ZIHE','Zielona Herbata','Herbata','aroma',4)
720insert into dbo.Drink values('COLA','Co-Ca-Cola','gazowane','smczna',5)
721insert into dbo.Drink values('SPTE','Sprite','gazowane','smczna',5)
722insert into dbo.Drink values('FATA','Fanta','gazowane','smczna',5)
723--select* from Drink
724
725--==========================================================================================================--