Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --DROP TABLES--
- --=====================================================--
- DROP TABLE IF EXISTS car_status
- DROP TABLE IF EXISTS Car
- DROP TABLE IF EXISTS Customer_Adress
- DROP TABLE IF EXISTS Employee_Adress
- DROP TABLE IF EXISTS EmployeeOrders
- DROP TABLE IF EXISTS Employee
- DROP TABLE IF EXISTS Products
- DROP TABLE IF EXISTS Drink
- DROP TABLE IF EXISTS Pizza
- DROP TABLE IF EXISTS Sauce
- DROP TABLE IF EXISTS size
- DROP TABLE IF EXISTS Orders
- DROP TABLE IF EXISTS Order_Type
- DROP TABLE IF EXISTS Payment_Method
- DROP TABLE IF EXISTS Customer
- --DROP FUNCTIONS--
- --=====================================================--
- DROP FUNCTION IF EXISTS znak
- DROP FUNCTION IF EXISTS checkEmail
- --DROP TRIGGERS--
- --=====================================================--
- DROP TRIGGER IF EXISTS Format_Number_E
- DROP TRIGGER IF EXISTS Format_Number_C
- DROP TRIGGER IF EXISTS Order_PriceT
- DROP TRIGGER IF EXISTS proc_OFF_BIG_PIZZA
- DROP TRIGGER IF EXISTS email_checkT
- DROP TRIGGER IF EXISTS exists_pizza
- DROP TRIGGER IF EXISTS exists_drink
- DROP TRIGGER IF EXISTS exists_sauce
- --DROP PROCEDURES--
- --=====================================================--
- DROP PROCEDURE IF EXISTS CREATE_ORDER
- DROP PROCEDURE IF EXISTS Add_Product
- DROP PROCEDURE IF EXISTS give_take_car
- DROP PROCEDURE IF EXISTS salary_up
- DROP PROCEDURE IF EXISTS Show_Employee_Orders
- --DROP VIEW--
- --=====================================================--
- DROP VIEW IF EXISTS number
- DROP VIEW IF EXISTS Employee_INFO
- DROP VIEW IF EXISTS Menu_pizza
- DROP VIEW IF EXISTS pizza_popularity
- DROP VIEW IF EXISTS drink_popularity
- DROP VIEW IF EXISTS sauce_popularity
- DROP VIEW IF EXISTS ORDER_INFO
- DROP VIEW IF EXISTS car_info
- DROP VIEW IF EXISTS DELIVERY_POPULARITY_CITY
- DROP VIEW IF EXISTS Monthly_Income
- DROP VIEW IF EXISTS BELOW_AVG
- --==========================================================================================================--
- --TABLES--
- --EMPLOYEE TBALES--
- --==========================================================--
- --EMPLOYEE--
- CREATE TABLE dbo.Employee (
- Employee_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
- Name varchar(100) NOT NULL,
- Surname varchar(100) NOT NULL,
- Birthday date NOT NULL DEFAULT GETDATE(),
- Job_position varchar(100) NOT NULL,
- Date_hired date NOT NULL DEFAULT GETDATE(),
- PESEL varchar(11) NOT NULL UNIQUE CHECK(LEN(pesel) = 11),
- Salary MONEY NOT NULL DEFAULT (0),
- E_mail varchar(30) NULL DEFAULT (NULL),
- Phone_Nr varchar(14) NULL DEFAULT (NULL),
- );
- --EMPLOYEE ADRESS--
- CREATE TABLE dbo.Employee_Adress(
- Adress_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
- Country varchar(100) NOT NULL DEFAULT (NULL),
- City varchar(100) NOT NULL DEFAULT (NULL),
- Street_Name varchar(100) NOT NULL DEFAULT (NULL),
- Street_Number varchar(50) NOT NULL DEFAULT (NULL),
- Door_Number varchar(10) DEFAULT (NULL),
- Employee_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Employee(Employee_ID) on delete cascade
- );
- -------------------------------------------------------------------
- --ADDITIONAL EMPLOYEE TABLES--
- --CAR--
- CREATE TABLE dbo.Car (
- Car_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
- Brand varchar(200) NOT NULL CHECK(LEN(brand) >=2),
- Model varchar(200) NOT NULL CHECK(LEN(model) >=2),
- Date_bought date NOT NULL DEFAULT (GETDATE()),
- Licens_plate varchar(10) NOT NULL UNIQUE,
- Employee_ID int NULL FOREIGN KEY REFERENCES dbo.Employee(Employee_ID) on delete set default
- );
- --CAR STATUS--
- CREATE TABLE dbo.car_status (
- Status_ID int NOT NULL IDENTITY(1,1),
- Accident_number int NOT NULL DEFAULT (0),
- Mileage int NOT NULL CHECK(mileage >= 0),
- Insurance_end date NOT NULL DEFAULT (NULL),
- Car_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Car(Car_ID) on delete cascade,
- );
- --========================================================--
- --CUSTOMER TABLES--
- --========================================================--
- --CUSTOMER--
- Create table dbo.Customer (
- Customer_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
- Name varchar(200) NOT NULL DEFAULT (NULL),
- Surname varchar(200) NULL DEFAULT (NULL),
- Phone_Nr varchar(14) NULL DEFAULT (NULL)
- );
- --CUSTOMER ADRESS--
- CREATE TABLE dbo.Customer_Adress(
- Adress_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
- City varchar(200) NULL DEFAULT (NULL),
- Street_Name varchar(200) NULL DEFAULT (NULL),
- Street_Number int NULL DEFAULT (NULL),
- Door_Number int NULL DEFAULT (NULL),
- Customer_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Customer(Customer_ID) on delete cascade
- );
- --========================================================--
- --ORDERS TABLES--
- --========================================================--
- --PAYMENT METHOD--
- CREATE TABLE dbo.Payment_Method(
- Payment_Method_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
- Method varchar(50) NULL DEFAULT ('cash'),
- );
- --ORDER TYPE--
- CREATE TABLE dbo.Order_Type(
- Order_Type_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
- Order_type varchar(100) NULL DEFAULT ('reastaurant'),
- );
- --ORDERS--
- CREATE TABLE dbo.Orders(
- Order_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
- Date_order date NOT NULL DEFAULT GETDATE(),
- Order_price MONEY NULL CHECK(Order_price >= 0) DEFAULT (0),
- Customer_ID int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Customer(Customer_ID) on delete set default,
- Payment_Method_ID INT NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Payment_Method(Payment_Method_ID) on delete set default,
- Order_type int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Order_Type(Order_Type_ID) on delete set default,
- );
- --EMPLOYEE ORDERS (BRIDGE TABLE N:N)--
- CREATE TABLE dbo.EmployeeOrders(
- ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
- Employee_ID int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Employee(Employee_ID) on delete cascade,
- Order_ID int NULL DEFAULT (NULL) FOREIGN KEY REFERENCES dbo.Orders(Order_ID) on delete cascade,
- );
- --========================================================--
- --PRODUCTS TABLES--
- --========================================================--
- --DRINK--
- CREATE TABLE dbo.Drink(
- Drink_ID varchar(10) NOT NULL PRIMARY KEY,
- Drink_Name varchar(50) NOT NULL,
- Drink_type varchar(50) NOT NULL,
- Drink_description varchar(200) NOT NULL,
- Price MONEY NULL DEFAULT (0)
- );
- --PIZZA--
- CREATE TABLE dbo.Pizza(
- Pizza_ID varchar(10) NOT NULL PRIMARY KEY,
- Pizza_Name varchar(50) NOT NULL,
- Toppings varchar(200) NOT NULL,
- Pizza_type varchar(50) NOT NULL,
- );
- --SAUCE--
- CREATE TABLE dbo.Sauce(
- Sauce_ID varchar(10) NOT NULL PRIMARY KEY,
- Sauce_Name varchar(50) NOT NULL,
- Sauce_description varchar(300) NOT NULL,
- Sauce_type varchar(50) NOT NULL,
- Price MONEY NULL DEFAULT (0)
- );
- --SIZE--
- CREATE TABLE dbo.size(
- ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
- Size varchar(50) NOT NULL DEFAULT('Medium'),
- Size_cm int NOT NULL DEFAULT(30),
- Price MONEY NULL DEFAULT (0),
- );
- --PRODUCTS--
- CREATE TABLE dbo.Products(
- Product_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
- Pizza_ID varchar(10) NULL FOREIGN KEY REFERENCES Pizza(Pizza_ID)on delete set NULL,
- Sauce_ID varchar(10) NULL FOREIGN KEY REFERENCES Sauce(Sauce_ID)on delete set NULL,
- Drink_ID varchar(10) NULL FOREIGN KEY REFERENCES Drink(Drink_ID)on delete set NULL,
- Size_ID INT NULL FOREIGN KEY REFERENCES dbo.size(ID)on delete set NULL,
- Order_ID int NOT NULL FOREIGN KEY REFERENCES dbo.Orders(Order_ID)on delete cascade,
- );
- --==========================================================================================================--
- --FUNCTIONS--
- --ZMIENIA FORMAT NUMERU TELEFONU--
- create function znak(@znaki char(14))
- returns char(14)
- as
- begin
- declare @a char(3);
- declare @b char(3);
- declare @c char(4);
- declare @d char(14);
- set @a =(select SUBSTRING(@znaki,1,3));
- set @b=(select substring(@znaki,4,3));
- set @c=(select SUBSTRING(@znaki,7,4));
- set @d='('+@a+')'+SPACE(1)+@b+'-'+@c;
- return @d;
- end
- --SPRADZ FORMAT MAILOWY--
- drop function checkEmail
- CREATE FUNCTION checkEmail (@email VARCHAR(50))
- RETURNS INT
- AS
- BEGIN
- IF (@email NOT LIKE '_%@__%.__%')
- RETURN 0
- RETURN 1
- END
- GO
- --==========================================================================================================--
- --TRIGGERS--
- --FORMATOWANIE NUMERU--
- CREATE TRIGGER Format_Number_E
- ON dbo.Employee
- FOR INSERT--update
- AS
- BEGIN
- UPDATE Employee
- SET Phone_Nr = (select dbo.znak(Phone_Nr))
- WHERE Employee_ID = (SELECT Employee_ID FROM INSERTED)
- END
- GO
- CREATE TRIGGER Format_Number_C
- ON dbo.Customer
- FOR INSERT
- AS
- BEGIN
- UPDATE Customer
- SET Phone_Nr = dbo.znak(Phone_Nr)
- WHERE Customer_ID = (SELECT Customer_ID FROM INSERTED)
- END
- GO
- --AUTOMATYCZNE OBLICZNIE CENY--
- CREATE TRIGGER Order_PriceT
- ON dbo.Products
- FOR INSERT
- AS
- BEGIN
- DECLARE @pizza MONEY;
- DECLARE @drink MONEY;
- DECLARE @sauce MONEY
- if (SELECT Price From Drink WHERE Drink_ID = (SELECT Drink_ID FROM INSERTED)) IS NULL
- SET @drink = 0
- ELSE
- SET @drink = (SELECT Price From Drink WHERE Drink_ID = (SELECT Drink_ID FROM INSERTED))
- if (SELECT Price From Sauce WHERE Sauce_ID = (SELECT Sauce_ID FROM INSERTED)) IS NULL
- SET @sauce = 0
- ELSE
- SET @sauce = (SELECT Price From Sauce WHERE Sauce_ID = (SELECT Sauce_ID FROM INSERTED))
- if (SELECT Price From size WHERE ID = (SELECT Size_ID FROM INSERTED)) IS NULL
- SET @pizza = 0
- ELSE
- SET @pizza = (SELECT Price From size WHERE ID = (SELECT Size_ID FROM INSERTED))
- UPDATE Orders
- SET Order_price = Order_price + @drink + @sauce + @pizza
- WHERE Order_ID = (SELECT Order_ID FROM INSERTED)
- END
- --BIG PICA promocja get two another 25% off
- CREATE TRIGGER proc_OFF_BIG_PIZZA
- ON dbo.Products
- FOR INSERT
- AS
- BEGIN
- DECLARE @Promo int;
- DECLARE @poz INT;
- SET @poz = (SELECT COUNT(*) FROM Products
- WHERE Order_ID = (SELECT Order_ID From Inserted) AND Size_ID = 3)
- SET @Promo = (SELECT Price FROM Size Where ID = 3) * 0.25
- IF (SELECT Size_ID From Inserted) = 3 AND @poz % 2 = 0
- UPDATE Orders
- SET Order_price = Order_price - @Promo WHERE Order_ID = (SELECT Order_ID FROM INSERTED)
- END
- --NIEPRAWIDLOWY FORMAT EMAIL--
- CREATE TRIGGER email_checkT
- ON dbo.Employee
- FOR INSERT
- AS
- BEGIN
- DECLARE @CHECK varchar(40);
- SET @CHECK = (SELECT E_mail from INSERTED)
- IF dbo.checkEmail(@CHECK) = 0
- BEGIN
- RAISERROR('ERROR - bad E-mail format', 1, 2)
- DELETE FROM Employee WHERE Employee_ID = (select Employee_ID from inserted)
- END
- END
- --PRODUCT ALREADY EXISTS--
- CREATE TRIGGER exists_pizza
- ON dbo.pizza
- FOR INSERT
- AS
- BEGIN
- IF (SELECT COUNT(Pizza_Name) FROM pizza WHERE Pizza_Name = (SELECT Pizza_Name FROM INSERTED)) > 1
- BEGIN
- RAISERROR('ERROR - THIS PIZZA ALREADY EXISTS IN DATABASE', 1, 2)
- DELETE FROM Pizza WHERE Pizza_Name = (SELECT Pizza_Name FROM INSERTED)
- END
- END
- ----------
- CREATE TRIGGER exists_drink
- ON dbo.Drink
- FOR INSERT
- AS
- BEGIN
- IF (SELECT COUNT(Drink_Name) FROM Drink WHERE Drink_Name = (SELECT Drink_Name FROM INSERTED)) > 1
- BEGIN
- RAISERROR('ERROR - THIS DRINK ALREADY EXISTS IN DATABASE', 1, 2)
- DELETE FROM Drink WHERE Drink_Name = (SELECT Drink_Name FROM INSERTED)
- END
- END
- ----------
- CREATE TRIGGER exists_sauce
- ON dbo.Sauce
- FOR INSERT
- AS
- BEGIN
- IF (SELECT COUNT(Sauce_Name) FROM Sauce WHERE Sauce_Name = (SELECT Sauce_Name FROM INSERTED)) > 1
- BEGIN
- RAISERROR('ERROR - THIS SAUCE ALREADY EXISTS IN DATABASE', 1, 2)
- DELETE FROM Sauce WHERE Sauce_Name = (SELECT Sauce_Name FROM INSERTED)
- END
- END
- --==========================================================================================================--
- --PROCEDURES--
- --TWORZENIE ZAMOWIENIA--
- IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'CREATE_ORDER')
- DROP PROCEDURE CREATE_ORDER
- GO
- CREATE PROCEDURE dbo.CREATE_ORDER
- @Order_type int,
- @Employee_1 int, @Employee_2 int,@Payment_Method_ID int,
- @Name varchar(100), @Surname varchar(100),@Phone_Nr int,
- @City varchar(100), @Street_Name varchar(100), @Street_Number int, @Door_Number int
- AS
- BEGIN
- INSERT INTO Customer(Name,Surname,phone_Nr) VALUES(@Name,@Surname,@Phone_Nr)
- DECLARE @Customer_ID int
- SET @Customer_ID = (SELECT TOP 1 Customer_ID From Customer ORDER BY Customer_ID DESC)
- INSERT INTO Customer_Adress(City,Street_Name,Street_Number,Door_Number,Customer_ID)
- VALUES(@City,@Street_Name,@Street_Number,@Door_Number,@Customer_ID)
- INSERT INTO Orders(Customer_ID,Payment_Method_ID,Order_type)
- Values(@Customer_ID,@Payment_Method_ID,@Order_type)
- DECLARE @Order_ID int;
- SET @Order_ID = (SELECT TOP 1 ORDER_ID From Orders ORDER BY Order_ID DESC)
- INSERT INTO EmployeeOrders(Employee_ID,Order_ID)
- VALUES(@Employee_1,@Order_ID)
- INSERT INTO EmployeeOrders(Employee_ID,Order_ID)
- VALUES(@Employee_2,@Order_ID)
- END
- GO
- --DODAJ PRODUKT/Y DO ZAMOWIENIA--
- IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Add_Product')
- DROP PROCEDURE Add_Product
- GO
- CREATE PROCEDURE dbo.Add_Product
- @Order_ID int,
- @Pizza_ID varchar(10),
- @Sauce_ID varchar(10),
- @Drink_ID varchar(10),
- @Size_ID int
- AS
- insert into Products(Order_ID,Pizza_ID,Sauce_ID,Drink_ID,Size_ID) VALUES(@Order_ID,@Pizza_ID,@Sauce_ID,@Drink_ID,@Size_ID)
- GO
- --DAWANIE I ODEJMOWANIE SAMOCHODA PRACOWNIKU--
- IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'give_take_car')
- DROP PROCEDURE give_take_car
- GO
- CREATE PROCEDURE dbo.give_take_car
- @Employee_ID int,
- @Car_ID int
- AS
- BEGIN
- if (select Employee_ID from Car where car_ID=@Car_ID) = @Employee_ID
- UPDATE Car
- SET Employee_ID = NULL
- WHERE car_ID = @Car_ID
- else
- UPDATE Car
- SET Employee_ID = @Employee_ID
- WHERE car_ID = @Car_ID
- END
- GO
- --DAWANIE PREMII PRACOWNIKU--
- IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'salary_up')
- DROP PROCEDURE salary_up
- GO
- CREATE PROCEDURE salary_up
- @Employee_ID int,
- @salary money
- AS
- IF @salary < 0
- RAISERROR ('Wrong argument with salary',1,2);
- else
- Update Employee set salary = salary + @salary
- WHERE Employee_ID = @Employee_ID;
- GO
- --POKAZ WSZYSTKIE ZAMOWIENIA WYPELNIONE PRZEZ EMPLOYEE_ID--
- IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Show_Employee_Orders')
- DROP PROCEDURE Show_Employee_Orders
- GO
- CREATE PROCEDURE Show_Employee_Orders
- @Employee_ID int
- AS
- SELECT Employee_ID,Name,Surname,Job_position,Date_hired,E_mail,Phone_Nr,Salary FROM Employee
- WHERE Employee_ID = @Employee_ID
- SELECT o.Order_ID,o.Date_order, o.Customer_ID,o.Payment_Method_ID,o.Order_Type,o.Order_Price FROM Orders as o
- JOIN EmployeeOrders as eo ON o.Order_ID=eo.Order_ID
- WHERE eo.Employee_ID = @Employee_ID
- GO
- --==========================================================================================================--
- --VIEWS--
- --INFORMACJA O ROBOTNIKACH--
- CREATE VIEW number
- AS
- select e.Employee_ID,count(e.Employee_ID) as number from Employee as e
- INNER JOIN EmployeeOrders as o ON e.Employee_ID = o.Employee_ID
- Group by e.Employee_ID
- GO
- CREATE VIEW Employee_INFO
- AS
- SELECT 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,
- ea.Country, ea.City, ea.Street_Name, ea.Street_Number, ea.Door_Number, n.number
- FROM Employee as e JOIN Employee_Adress as ea
- ON e.Employee_ID = ea.Employee_ID
- LEFT JOIN number as n ON e.Employee_ID = n.Employee_ID
- GROUP 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,
- ea.Country, ea.City, ea.Street_Name, ea.Street_Number, ea.Door_Number,n.number
- ORDER BY e.Employee_ID ASC
- GO
- --PIZZA MENU--
- CREATE VIEW Menu_pizza
- AS
- select Pizza_ID,Pizza_Name, Toppings,Pizza_Type,
- (select price as '20 cm' from size where id = 1) as 'Price of Small',
- (select price as '20 cm' from size where id = 2) as 'Price of Medium',
- (select price as '20 cm' from size where id = 3) as 'Price of Large'
- FROM pizza
- GO
- --POPULAR PIZZA--
- CREATE VIEW pizza_popularity
- AS
- SELECT TOP 100 PERCENT p.Pizza_ID,p.Pizza_Name,p.Toppings,p.Pizza_Type,
- (select count(pa.Pizza_ID) from products as pa where pa.Pizza_ID = p.Pizza_ID) as 'Pizza number'
- FROM Pizza as p
- ORDER BY 'Pizza number' DESC
- GO
- -------------------------
- CREATE VIEW drink_popularity
- AS
- SELECT TOP 100 PERCENT d.Drink_ID,d.Drink_Name,d.Drink_type,d.Drink_description,d.Price,
- (select count(pa.Drink_ID) from products as pa where pa.Drink_ID = d.Drink_ID) as 'Drink number'
- FROM Drink as d
- ORDER BY 'Drink number' DESC
- GO
- ----------------------------
- CREATE VIEW sauce_popularity
- AS
- SELECT TOP 100 PERCENT s.Sauce_ID,s.Sauce_Name,s.Sauce_type,s.Sauce_description,s.Price,
- (select count(pa.Sauce_ID) from products as pa where pa.Sauce_ID = s.sauce_ID) as 'Sauce number'
- FROM sauce as s
- ORDER BY 'Sauce number' DESC
- GO
- --ORDER INFO--
- CREATE VIEW ORDER_INFO
- AS
- select 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,
- (select count(pa.Pizza_ID) from products as pa where pa.Order_ID = o.Order_ID) as 'Pizza count',
- (select count(p.Drink_ID) from products as p where p.Order_ID = o.Order_ID) as 'Drink count',
- (select count(s.sauce_ID) from products as s where s.Order_ID = o.Order_ID) as 'Sauce count'
- from orders as o
- join EmployeeOrders as eo on eo.Order_ID=o.Order_ID
- join Employee as e on e.Employee_ID = eo.Employee_ID
- join Payment_Method as pm on pm.Payment_Method_ID = o.Payment_Method_ID
- join Order_Type as ot on o.Order_type = ot.Order_Type_ID
- ORDER BY Order_ID DESC
- GO
- --CAR INFO--
- CREATE VIEW car_info
- AS
- select 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
- left join Employee as e on c.Employee_ID = e.Employee_ID
- join car_status as cs on c.car_ID = cs.car_ID
- GO
- --NAJPOPULARNIEJSZE MIASTA DOSTAWY--
- CREATE VIEW DELIVERY_POPULARITY_CITY
- AS
- select TOP 100 PERCENT Street_Name, count(Street_Name) as 'Number of Orders' FROM Customer_Adress
- WHERE Street_Name IS NOT NULL
- Group by Street_Name
- ORDER BY 'Number of Orders' DESC
- GO
- --DOCHOD ZA MIESIAC--
- CREATE VIEW Monthly_Income
- AS
- SELECT TOP 100 PERCENT Date_Order, SUM(Order_price) as 'Income' FROM Orders
- GROUP BY Date_Order
- ORDER BY Date_Order ASC
- GO
- --OSOBY KTORE ZARABIAJA MNIEJ NIZ SREDNIA--
- CREATE VIEW BELOW_AVG
- AS
- SELECT TOP 100 PERCENT Employee_ID,Name,Surname,Birthday,Job_position,Date_Hired,PESEL,Salary FROM Employee
- GROUP BY Employee_ID,Name,Surname,Birthday,Job_position,Date_Hired,PESEL,Salary
- HAVING salary < (select avg(salary) from Employee)
- ORDER BY Salary DESC
- GO
- --==========================================================================================================--
- --INSERT TO DATABASE--
- --EMPLOYEE--
- insert into dbo.Employee values('Dariusz','Rodzewicz','1998-12-18','Piekarz','2013-05-15','20054788612',120,'DRodzewicz@mgmail.com','861214043')
- insert into dbo.Employee values('Robert','Radulewicz','1998-04-07','Piekarz','2013-05-15','15937846132',600,'RobRAdulew@gmail.com','861234557')
- insert into dbo.Employee values('Lukasz','Dowgialo','1997-06-01','Kurier','2013-05-15','98712314785',1000,'DowgiaLukas@gmail.com','86856984')
- insert into dbo.Employee values('Ryszard','Granicki','1997-06-28','Piekarz','2013-05-15','65412398752',800,'RysiukGran@gmail.com','861523698')
- insert into dbo.Employee values('Lukasz','Baniukiewicz','1998-12-18','Kurier','2013-05-15','12589637415',1200,'Baniuk1999@gmail.com','86141586')
- insert into dbo.Employee values('Grzegorz','Wojtkiewicz','1999-01-16','Kasier','2013-05-15','12356987452',450,'WojtGrisha@gmail.com','861740012')
- insert into dbo.Employee values('Andrzej','Tichonow','1986-03-27','Piekarz','2013-05-15','20054788614',2000,'Lenkas89@gmail.com','861115225')
- insert into dbo.Employee values('Julia','Stanulyte','1998-10-11','Piekarz','2013-05-15','20111552386',600,'HarcJulia@gmail.com','861158996')
- insert into dbo.Employee values('Adriana','Wiszniewska','1996-08-22','Kasier','2013-05-15','12000254550',850,'MilionAdri@gmail.com','8614311258')
- insert into dbo.Employee values('Barbara','Zinkiewicz','1998-02-28','Kurier','2013-05-15','25555521587',775,'BasiutaZ@gmail.com','8645558555')
- --select * from Employee
- --EMPLOYEE ADRESS--
- insert into dbo.Employee_Adress values('Lithuania','Vilnius','Erfuro','122','44',1)
- insert into dbo.Employee_Adress values('Poland','Gdansk','Pomorska','44',NULL,2)
- insert into dbo.Employee_Adress values('Poland','Gdansk','3-Maja','144','75',3)
- insert into dbo.Employee_Adress values('Poland','Gdynia','Armii_Krajowej','98','B',4)
- insert into dbo.Employee_Adress values('Poland','Sopot','Bitwy_Pod_Plowcami','64','209',5)
- insert into dbo.Employee_Adress values('Poland','Sopot','Bitwy_Pod_Plowcami','64','209',6)
- insert into dbo.Employee_Adress values('Poland','Gdansk','3-Maja','75',NULL,7)
- insert into dbo.Employee_Adress values('Poland','Gdynia','Mickiewicza','47','65',8)
- insert into dbo.Employee_Adress values('Belarus','Grodno','Zapackaja','44','56',9)
- insert into dbo.Employee_Adress values('Poland','Gdansk','Reja','69','A',10)
- --select * from Employee_Adress
- --CAR--
- insert into dbo.Car values('Toyota','Auris','2014-08-15','GDN-45378',NULL),
- ('Opel','Astra','2015-09-24','GDN-00218',NULL),
- ('Volkswagen','Golf','2014-10-02','GDN-95734',NULL);
- --select * from Car
- --CAR STATUS--
- insert into dbo.car_status values(0,150000,'2020-12-15',1),
- (1,192025,'2020-12-15',2),
- (0,134008,'2020-12-15',3);
- --select * from car_status
- --PAYMENT METHOD--
- insert into dbo.Payment_Method values('cash'),
- ('credit'),
- ('PayPal'),
- ('Bank Transfer');
- --select * from Payment_Method
- --ORDER TYPE--
- insert into dbo.Order_Type values('Restaurant'),
- ('Take-Away'),
- ('Delivery');
- --select * from Order_Type
- --PIZZA SIZE--
- insert into dbo.size values('Small',25,20),
- ('Medium',30,26),
- ('Large',40,34);
- --select * from size
- --SAUCE--
- insert into dbo.Sauce values('CZWY','Czosnakowy','Nie ma','lagodny',2)
- insert into dbo.Sauce values('POWY','Pomidorowy','Nie ma','lagodny',1.5)
- insert into dbo.Sauce values('ARSKI','Arabski','Nie ma','ostry',2)
- insert into dbo.Sauce values('MIWY','Miodowy','Nie ma','slodki',2)
- insert into dbo.Sauce values('MUWY','Musztardowy','Nie ma','Gorzki',1.5)
- insert into dbo.Sauce values('PIWY','Piepszowy','Nie ma','Gorzki',2)
- --select * from Sauce
- --PIZZA--
- insert into dbo.Pizza values('AlNO','Albano','ser, oregano, papryka, boczek, szynka, cebula','tradycyjna')
- insert into dbo.Pizza values('CASA','Calabresa','ser, oregano, salami, oliwki, papryczki apalenos, tabasco','ostra')
- insert into dbo.Pizza values('MACO','Marco','ser, oregano, salami, pieczarki','tradycyjna')
- insert into dbo.Pizza values('BAMA','Bahama','ser,oregano, szynka, ananas','slodka')
- insert into dbo.Pizza values('MATA','Margherita','ser, oregano','tradycyjna')
- insert into dbo.Pizza values('FONA','Foggiana',' ser, oregano, pieczarki','tradycyjna')
- insert into dbo.Pizza values('ONE','1x1','sos, ser, oregano, szynka','tradycyjna')
- insert into dbo.Pizza values('PORA','Povera','sos, ser, oregano, szynka, pieczarki','tradycyjna')
- insert into dbo.Pizza values('UNDO','Un Ricordo','sos, ser, oregano, szynka, kukurydza','tradycyjna')
- insert into dbo.Pizza values('UUGI','Quatro Formaggi','sos, ser, oregano, ser feta, ser pleśniowy, ser gouda','serowa')
- --select* from Pizza
- --DRIKNS--
- insert into dbo.Drink values('CZHE','Czarna Herbata','Herbata','aroma',4)
- insert into dbo.Drink values('ZIHE','Zielona Herbata','Herbata','aroma',4)
- insert into dbo.Drink values('COLA','Co-Ca-Cola','gazowane','smczna',5)
- insert into dbo.Drink values('SPTE','Sprite','gazowane','smczna',5)
- insert into dbo.Drink values('FATA','Fanta','gazowane','smczna',5)
- --select* from Drink
- --==========================================================================================================--
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement