Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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.
Left join producing wrong result
Program 3 Lab Assignment
kirthi
QUAN LY CHUYEN BAY
Create Date list (variable dates) - SQL Server
20181cse0065
nandhu
QLDT_TUAN 7
Delete double entries from a table without primary key
store
Please log in to post a comment.