Question

• (#3a){Screenshot.} Create a VIEW named ProductProfit that displays the Category Description, Product Description, and the Profit (SalePrice - Cost). Name the Category description column ‘Category’ and the Product description ‘Item’. The screenshot should show the SQL code to create the view and a SELECT statement displaying the records returned in descending order of the Profit column.
• (#3b){Screenshot.} Using the ProductProfit VIEW, display the records that are in the TOP 10 Percent of Profit. The screenshot should show the SELECT statement and the records returned.
• (#4a){Screen shot.} Create a VIEW named FamilyRide that displays the Category description, the Product Description, the Cost and the SalePrice. Name the Category description column ‘Category’ and the Product description ‘Item’. The screenshot should show the SQL code to create the view and a SELECT statement displaying the records returned in ascending order of the Item column.

Category CID Description Product PID Description CID (fk) Cost SalePrice AvailableOnline (Y, N)

Answer 1

If you have any doubts, please give me comment...

-- #3a

CREATE VIEW ProductProfit AS

SELECT C.Description AS Category, P.Description AS Item, (SalePrice - Cost) AS Profit

FROM Category C, Product P

WHERE C.CID = P.CID;

-- #3b

SELECT *

FROM ProductProfit

ORDER BY Profit DESC

LIMIT 10;

-- #4a

CREATE VIEW FamilyRide AS

SELECT C.Description AS Category, P.Description AS Item, Cost, SalePrice

FROM Category C, Product P

WHERE C.CID = P.CID;

SELECT *

FROM FamilyRide

ORDER BY Item ASC;