Task2, Sql Server - rextester

Task2

--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 PRIMARY KEY,
Year int,
Month int,
Day int
);

CREATE TABLE DateWareHouse(
DWID int 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


run \| edit \| history \| help	0

Please log in to post a comment.