2.1 Create a view that shows all the Orders, with the customer name, for a specific order date. (Coder will choose an OrderDate as per rows in their Orders table) 2.2 Create a Scalar-valued function to get the total of the UnitCost, in the Products table. 2.3 Use the Scalar-valued function created in question 2.2 to print "The Total cost of the products we have is R", return the Total cost from the function in rands. 2.4 Write a query against OrderDetails. 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);
Questions in image
Step by step
Solved in 3 steps
