Write a query against QrderDetails Table, that returns the running quantity for each order using subqueries. Note: Use Database and tables created in given code . In Tsql CREATE TABLE Categories ( CategoryID int NOT NULL, CategoryName varchar(255), PRIMARY KEY (CategoryID) ); CREATE TABLE Products ( ProductID int NOT NULL, ProductName varchar(255), MobileNumber varchar(255), ProductImage image, UnitCost int , Description varchar(255), CategoryId int, PRIMARY KEY (ProductID), FOREIGN KEY (CategoryId) REFERENCES Categories(CategoryId) ); CREATE TABLE Review( ReviewID int NOT NULL, ProductID int NOT NULL, CustomerName varchar(255), CustomerEmail varchar(255), Rating int, Comments varchar(255), PRIMARY KEY (ReviewID), FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ); CREATE TABLE ShoppingCart( RecordID int NOT NULL, CartID int NOT NULL, ProductID int NOT NULL, quantity int, DataCreated varchar(255), PRIMARY KEY (RecordID), FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ); CREATE TABLE Orders( OrderID int NOT NULL, CustomerName varchar(255), OrderDate date, ShipDate date, PRIMARY KEY (OrderID) ); CREATE TABLE OrderDetails( ID int NOT NULL, OrderID int NOT NULL, ProductID int NOT NULL, quantity int, UnitCost int, PRIMARY KEY (ID), FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ); /*Table 1*/ INSERT INTO Categories (CategoryID, CategoryName) VALUES (100,'apple'); INSERT INTO Categories (CategoryID, CategoryName) VALUES (101,'Banana'); INSERT INTO Categories (CategoryID, CategoryName) VALUES (102,'Mango'); INSERT INTO Categories (CategoryID, CategoryName) VALUES (103,'Grapes'); INSERT INTO Categories (CategoryID, CategoryName) VALUES (104,'apple'); /*Table 2*/ INSERT INTO Products (ProductID, ProductName,MobileNumber,ProductImage,UnitCost,Description,CategoryId) VALUES (1,'Basheer',12345678,'',24,'hbhvjdkh',101); INSERT INTO Products (ProductID, ProductName,MobileNumber,ProductImage,UnitCost,Description,CategoryId) VALUES (2,'ABbas',12345678,'',34,'hbhvjdkh',102); INSERT INTO Products (ProductID, ProductName,MobileNumber,ProductImage,UnitCost,Description,CategoryId) VALUES (3,'Shaik',12345678,'',33,'hbhvjdkh',103); INSERT INTO Products (ProductID, ProductName,MobileNumber,ProductImage,UnitCost,Description,CategoryId) VALUES (4,'bhvhds',12345678,'',78,'hbhvjdkh',104); INSERT INTO Products (ProductID, ProductName,MobileNumber,ProductImage,UnitCost,Description,CategoryId) VALUES (5,'absj',12345678,'',67,'hbhvjdkh',101); /*Table 3*/ INSERT INTO Review (ReviewID, ProductID, CustomerName, CustomerEmail, Rating, Comments ) VALUES (201,1,'john','abc@gmail.com',4,'jbdkv'); INSERT INTO Review (ReviewID, ProductID, CustomerName, CustomerEmail, Rating, Comments ) VALUES (202,2,'john1','abc1@gmail.com',2,'jbdkv'); INSERT INTO Review (ReviewID, ProductID, CustomerName, CustomerEmail, Rating, Comments ) VALUES (203,3,'john2','abc3@gmail.com',3,'jbdkv'); INSERT INTO Review (ReviewID, ProductID, CustomerName, CustomerEmail, Rating, Comments ) VALUES (204,1,'john4','abc4@gmail.com',5,'jbdkv'); INSERT INTO Review (ReviewID, ProductID, CustomerName, CustomerEmail, Rating, Comments ) VALUES (205,5,'john5','abc0@gmail.com',5,'jbdkv'); /*Table 4*/ INSERT INTO ShoppingCart (RecordID, CartID, ProductID, quantity, DataCreated) VALUES (501,123,'1','50','02-08-2000'); INSERT INTO ShoppingCart (RecordID, CartID, ProductID, quantity, DataCreated) VALUES (502,124,'2','51','03-08-2000'); INSERT INTO ShoppingCart (RecordID, CartID, ProductID, quantity, DataCreated) VALUES (503,125,'3','52','04-08-2000'); INSERT INTO ShoppingCart (RecordID, CartID, ProductID, quantity, DataCreated) VALUES (504,125,'4','53','05-08-2000'); INSERT INTO ShoppingCart (RecordID, CartID, ProductID, quantity, DataCreated) VALUES (508,128,'5','55','06-08-2000'); /*Table 5*/ INSERT INTO Orders (OrderID,CustomerName, OrderDate, ShipDate) VALUES (1001,2000,'06-08-1999','02-09-1999'); INSERT INTO Orders (OrderID,CustomerName, OrderDate, ShipDate) VALUES (1002,2001,'05-08-1999','03-09-1999'); INSERT INTO Orders (OrderID,CustomerName, OrderDate, ShipDate) VALUES (1003,2002,'04-08-1999','04-09-1999'); INSERT INTO Orders (OrderID,CustomerName, OrderDate, ShipDate) VALUES (1004,2005,'06-08-1999','02-09-1999'); INSERT INTO Orders (OrderID,CustomerName, OrderDate, ShipDate) VALUES (1005,2006,'06-08-1999','02-09-1999'); /*Table 6*/ INSERT INTO OrderDetails (ID,OrderID, ProductID, quantity, UnitCost) VALUES (111,1001,1,50,123); INSERT INTO OrderDetails (ID,OrderID, ProductID, quantity, UnitCost) VALUES (112,1002,2,51,123); INSERT INTO OrderDetails (ID,OrderID, ProductID, quantity, UnitCost) VALUES (113,1003,3,52,123); INSERT INTO OrderDetails (ID,OrderID, ProductID, quantity, UnitCost) VALUES (114,1001,1,56,121); INSERT INTO OrderDetails (ID,OrderID, ProductID, quantity, UnitCost) VALUES (115,1001,1,50,123);
Write a query against QrderDetails Table, that returns the running quantity for each order using subqueries.
Note: Use
CREATE TABLE Categories (
CategoryID int NOT NULL,
CategoryName varchar(255),
PRIMARY KEY (CategoryID)
);
CREATE TABLE Products (
ProductID int NOT NULL,
ProductName varchar(255),
MobileNumber varchar(255),
ProductImage image,
UnitCost int ,
Description varchar(255),
CategoryId int,
PRIMARY KEY (ProductID),
FOREIGN KEY (CategoryId) REFERENCES Categories(CategoryId)
);
CREATE TABLE Review(
ReviewID int NOT NULL,
ProductID int NOT NULL,
CustomerName varchar(255),
CustomerEmail varchar(255),
Rating int,
Comments varchar(255),
PRIMARY KEY (ReviewID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
CREATE TABLE ShoppingCart(
RecordID int NOT NULL,
CartID int NOT NULL,
ProductID int NOT NULL,
quantity int,
DataCreated varchar(255),
PRIMARY KEY (RecordID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
CREATE TABLE Orders(
OrderID int NOT NULL,
CustomerName varchar(255),
OrderDate date,
ShipDate date,
PRIMARY KEY (OrderID)
);
CREATE TABLE OrderDetails(
ID int NOT NULL,
OrderID int NOT NULL,
ProductID int NOT NULL,
quantity int,
UnitCost int,
PRIMARY KEY (ID),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);
/*Table 1*/
INSERT INTO Categories (CategoryID, CategoryName) VALUES (100,'apple');
INSERT INTO Categories (CategoryID, CategoryName) VALUES (101,'Banana');
INSERT INTO Categories (CategoryID, CategoryName) VALUES (102,'Mango');
INSERT INTO Categories (CategoryID, CategoryName) VALUES (103,'Grapes');
INSERT INTO Categories (CategoryID, CategoryName) VALUES (104,'apple');
/*Table 2*/
INSERT INTO Products (ProductID, ProductName,MobileNumber,ProductImage,UnitCost,Description,CategoryId) VALUES (1,'Basheer',12345678,'',24,'hbhvjdkh',101);
INSERT INTO Products (ProductID, ProductName,MobileNumber,ProductImage,UnitCost,Description,CategoryId) VALUES (2,'ABbas',12345678,'',34,'hbhvjdkh',102);
INSERT INTO Products (ProductID, ProductName,MobileNumber,ProductImage,UnitCost,Description,CategoryId) VALUES (3,'Shaik',12345678,'',33,'hbhvjdkh',103);
INSERT INTO Products (ProductID, ProductName,MobileNumber,ProductImage,UnitCost,Description,CategoryId) VALUES (4,'bhvhds',12345678,'',78,'hbhvjdkh',104);
INSERT INTO Products (ProductID, ProductName,MobileNumber,ProductImage,UnitCost,Description,CategoryId) VALUES (5,'absj',12345678,'',67,'hbhvjdkh',101);
/*Table 3*/
INSERT INTO Review (ReviewID, ProductID, CustomerName, CustomerEmail, Rating, Comments ) VALUES (201,1,'john','abc@gmail.com',4,'jbdkv');
INSERT INTO Review (ReviewID, ProductID, CustomerName, CustomerEmail, Rating, Comments ) VALUES (202,2,'john1','abc1@gmail.com',2,'jbdkv');
INSERT INTO Review (ReviewID, ProductID, CustomerName, CustomerEmail, Rating, Comments ) VALUES (203,3,'john2','abc3@gmail.com',3,'jbdkv');
INSERT INTO Review (ReviewID, ProductID, CustomerName, CustomerEmail, Rating, Comments ) VALUES (204,1,'john4','abc4@gmail.com',5,'jbdkv');
INSERT INTO Review (ReviewID, ProductID, CustomerName, CustomerEmail, Rating, Comments ) VALUES (205,5,'john5','abc0@gmail.com',5,'jbdkv');
/*Table 4*/
INSERT INTO ShoppingCart (RecordID, CartID, ProductID, quantity, DataCreated) VALUES (501,123,'1','50','02-08-2000');
INSERT INTO ShoppingCart (RecordID, CartID, ProductID, quantity, DataCreated) VALUES (502,124,'2','51','03-08-2000');
INSERT INTO ShoppingCart (RecordID, CartID, ProductID, quantity, DataCreated) VALUES (503,125,'3','52','04-08-2000');
INSERT INTO ShoppingCart (RecordID, CartID, ProductID, quantity, DataCreated) VALUES (504,125,'4','53','05-08-2000');
INSERT INTO ShoppingCart (RecordID, CartID, ProductID, quantity, DataCreated) VALUES (508,128,'5','55','06-08-2000');
/*Table 5*/
INSERT INTO Orders (OrderID,CustomerName, OrderDate, ShipDate) VALUES (1001,2000,'06-08-1999','02-09-1999');
INSERT INTO Orders (OrderID,CustomerName, OrderDate, ShipDate) VALUES (1002,2001,'05-08-1999','03-09-1999');
INSERT INTO Orders (OrderID,CustomerName, OrderDate, ShipDate) VALUES (1003,2002,'04-08-1999','04-09-1999');
INSERT INTO Orders (OrderID,CustomerName, OrderDate, ShipDate) VALUES (1004,2005,'06-08-1999','02-09-1999');
INSERT INTO Orders (OrderID,CustomerName, OrderDate, ShipDate) VALUES (1005,2006,'06-08-1999','02-09-1999');
/*Table 6*/
INSERT INTO OrderDetails (ID,OrderID, ProductID, quantity, UnitCost) VALUES (111,1001,1,50,123);
INSERT INTO OrderDetails (ID,OrderID, ProductID, quantity, UnitCost) VALUES (112,1002,2,51,123);
INSERT INTO OrderDetails (ID,OrderID, ProductID, quantity, UnitCost) VALUES (113,1003,3,52,123);
INSERT INTO OrderDetails (ID,OrderID, ProductID, quantity, UnitCost) VALUES (114,1001,1,56,121);
INSERT INTO OrderDetails (ID,OrderID, ProductID, quantity, UnitCost) VALUES (115,1001,1,50,123);
Step by step
Solved in 6 steps
