Task_3_Final, Sql Server

Task_3_Final

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

CREATE TABLE ProductA(
ProductID int PRIMARY KEY,
ProductName varchar(100),
Price Money
);

CREATE TABLE OrderA(
OrderID int PRIMARY KEY,
Date datetime
);

CREATE TABLE OrderDetailsA(
ProductID int FOREIGN KEY REFERENCES ProductA(ProductID),
OrderID int FOREIGN KEY REFERENCES OrderA(OrderID),
ProductPrice Money,
Quantity int
);
go
INSERT INTO ProductA(ProductID,ProductName,Price)
VALUES(1,'Laptop',500)
INSERT INTO ProductA(ProductID,ProductName,Price)
VALUES(2,'Bread',15.50)
INSERT INTO ProductA(ProductID,ProductName,Price)
VALUES(3,'Egg',2.50)
INSERT INTO ProductA(ProductID,ProductName,Price)
VALUES(4,'Milk',30);
go
INSERT INTO OrderA(OrderID,Date)
VALUES(1,'09.01.2020 12:58:58')
INSERT INTO OrderA(OrderID,Date)
VALUES(2,'08.01.2020 10:43:32')
INSERT INTO OrderA(OrderID,Date)
VALUES(3,'07.01.2020 7:00:05')
INSERT INTO OrderA(OrderID,Date)
VALUES(4,'01.01.2020 12:00:00');
go
INSERT INTO OrderDetailsA(ProductID,OrderID,ProductPrice,Quantity)
VALUES((SELECT ProductID FROM ProductA WHERE ProductName='Laptop'),1,(SELECT Price FROM ProductA WHERE ProductName='Laptop'),10)
INSERT INTO OrderDetailsA(ProductID,OrderID,ProductPrice,Quantity)
VALUES((SELECT ProductID FROM ProductA WHERE ProductName='Bread'),2,(SELECT Price FROM ProductA WHERE ProductName='Bread'),4)
INSERT INTO OrderDetailsA(ProductID,OrderID,ProductPrice,Quantity)
VALUES((SELECT ProductID FROM ProductA WHERE ProductName='Egg'),3,(SELECT Price FROM ProductA WHERE ProductName='Egg'),20)
INSERT INTO OrderDetailsA(ProductID,OrderID,ProductPrice,Quantity)
VALUES((SELECT ProductID FROM ProductA WHERE ProductName='Milk'),4,(SELECT Price FROM ProductA WHERE ProductName='Milk'),7);
go
SELECT * FROM OrderDetailsA;
go
CREATE TABLE ProductB(
ProductID int PRIMARY KEY,
ProductName varchar(100)
);

CREATE TABLE OrderB(
OrderID int PRIMARY KEY    
);

CREATE TABLE Date(
DateID int IDENTITY PRIMARY KEY,
Year int,
Month int,
Day int
);

CREATE TABLE DateWareHouse(
DWID int IDENTITY PRIMARY KEY,
OrderID int FOREIGN KEY REFERENCES OrderB(OrderID),
ProductID int FOREIGN KEY REFERENCES ProductB(ProductID),
DateID int FOREIGN KEY REFERENCES Date(DateID),
ProductPrice Money,
SellingPrice Money,
Quantity int
);
go
CREATE TABLE ProductC(
ProductID int PRIMARY KEY,
ProductName varchar(100),
Price Money
);

CREATE TABLE OrderC(
OrderID int PRIMARY KEY,
Date datetime
);

CREATE TABLE OrderDetailsC(
ProductID int FOREIGN KEY REFERENCES ProductC(ProductID),
OrderID int FOREIGN KEY REFERENCES OrderC(OrderID),
ProductPrice Money,
Quantity int
);
go
INSERT INTO ProductC(ProductID,ProductName,Price)
VALUES (5,'BT Speakers',400)
INSERT INTO ProductC(ProductID,ProductName,Price)
VALUES (6,'Fridge',4000)
INSERT INTO ProductC(ProductID,ProductName,Price)
VALUES (7,'Washing Machine',4500);
go
INSERT INTO OrderC(OrderID,Date)
VALUES (5,'09.01.2019 02:12:12')
INSERT INTO OrderC(OrderID,Date)
VALUES (6,'05.30.2019 10:10:45')
INSERT INTO OrderC(OrderID,Date)
VALUES (7,'10.24.2019 04:00:45');
go
INSERT INTO OrderDetailsC(ProductID,OrderID,ProductPrice,Quantity)
VALUES ((SELECT ProductID FROM ProductC WHERE ProductName='BT Speakers'),5,(SELECT Price FROM ProductC WHERE ProductName='BT Speakers'),1)
INSERT INTO OrderDetailsC(ProductID,OrderID,ProductPrice,Quantity)
VALUES ((SELECT ProductID FROM ProductC WHERE ProductName='Fridge'),6,(SELECT Price FROM ProductC WHERE ProductName='Fridge'),2)
INSERT INTO OrderDetailsC(ProductID,OrderID,ProductPrice,Quantity)
VALUES ((SELECT ProductID FROM ProductC WHERE ProductName='Washing Machine'),7,(SELECT Price FROM ProductC WHERE ProductName='Washing Machine'),3);
go
SELECT * FROM OrderDetailsC;
go
CREATE PROCEDURE transfer_db_data
AS
INSERT INTO ProductB(ProductID,ProductName)
SELECT ProductID,ProductName FROM ProductA
INSERT INTO ProductB(ProductID,ProductName)
SELECT ProductID,ProductName FROM ProductC;

INSERT INTO OrderB(OrderID)
SELECT OrderID FROM OrderA
INSERT INTO OrderB(OrderID)
SELECT OrderID FROM OrderC;

INSERT INTO Date(Year,Month,Day)
SELECT YEAR(Date),MONTH(Date),DAY(Date) FROM OrderA
INSERT INTO Date(Year,Month,Day)
SELECT YEAR(Date),MONTH(Date),DAY(Date) FROM OrderC;

INSERT INTO DateWareHouse(OrderID,ProductID,DateID,ProductPrice,SellingPrice,Quantity)
SELECT OrderDetailsA.OrderID,OrderDetailsA.ProductID,Date.DateID,OrderDetailsA.ProductPrice,OrderDetailsA.ProductPrice*OrderDetailsA.Quantity+3*OrderDetailsA.ProductPrice*OrderDetailsA.Quantity,OrderDetailsA.Quantity
FROM OrderDetailsA LEFT JOIN OrderA ON OrderA.OrderID=OrderDetailsA.OrderID LEFT JOIN Date ON DAY(OrderA.Date)=DAY(Date) AND MONTH(OrderA.Date)=MONTH(Date) AND YEAR(OrderA.Date)=YEAR(Date)
INSERT INTO DateWareHouse(OrderID,ProductID,DateID,ProductPrice,SellingPrice,Quantity)
SELECT OrderDetailsC.OrderID,OrderDetailsC.ProductID,Date.DateID,OrderDetailsC.ProductPrice,OrderDetailsC.ProductPrice*OrderDetailsC.Quantity+3*OrderDetailsC.ProductPrice*OrderDetailsC.Quantity,OrderDetailsC.Quantity
FROM OrderDetailsC LEFT JOIN OrderC ON OrderC.OrderID=OrderDetailsC.OrderID LEFT JOIN Date ON DAY(OrderC.Date)=DAY(Date) AND MONTH(OrderC.Date)=MONTH(Date) AND YEAR(OrderC.Date)=YEAR(Date);
go
EXEC transfer_db_data;
go
SELECT * FROM DateWareHouse;
go


run \| edit \| history \| help	0