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.

Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
icon
Related questions
Question

Note: Use  Database and tables created in given code to answer the following questions. 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);

Questions in image

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 QrderDetails Table, that returns the running quantity for each order
using subqueries.
2.5 Write a join query between the derived table and the Orders table to return the Orders with
the maximum order date for each customer.
Transcribed Image Text: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 QrderDetails Table, that returns the running quantity for each order using subqueries. 2.5 Write a join query between the derived table and the Orders table to return the Orders with the maximum order date for each customer.
Expert Solution
steps

Step by step

Solved in 3 steps

Blurred answer
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY