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 OrderDetails(
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 OrderDetails(ProductID,OrderID,ProductPrice,Quantity)
VALUES((SELECT ProductID FROM ProductA WHERE ProductName='Laptop'),1,(SELECT Price FROM ProductA WHERE ProductName='Laptop'),10)
INSERT INTO OrderDetails(ProductID,OrderID,ProductPrice,Quantity)
VALUES((SELECT ProductID FROM ProductA WHERE ProductName='Bread'),2,(SELECT Price FROM ProductA WHERE ProductName='Bread'),4)
INSERT INTO OrderDetails(ProductID,OrderID,ProductPrice,Quantity)
VALUES((SELECT ProductID FROM ProductA WHERE ProductName='Egg'),3,(SELECT Price FROM ProductA WHERE ProductName='Egg'),20)
INSERT INTO OrderDetails(ProductID,OrderID,ProductPrice,Quantity)
VALUES((SELECT ProductID FROM ProductA WHERE ProductName='Milk'),4,(SELECT Price FROM ProductA WHERE ProductName='Milk'),7);
go
SELECT * FROM OrderDetails;
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 PROCEDURE transfer_db_data
AS
INSERT INTO ProductB(ProductID,ProductName)
SELECT ProductA.ProductID, ProductA.ProductName FROM ProductA

INSERT INTO OrderB(OrderID)
SELECT OrderA.OrderID FROM OrderA

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

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


run \| edit \| history \| help	0