2.1 Create a Scalar-valued function to get the total of the UnitCost, in the Products table. 2.2 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. Note: Use  Database and tables created 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);

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

 2.1 Create a Scalar-valued function to get the total of the UnitCost, in the Products table.

2.2 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.

Note: Use  Database and tables created 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);

Expert Solution
steps

Step by step

Solved in 2 steps with 1 images

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