SQL PART 3

VIEWS

===

: Create a view to list all products with price greater than 50?

CREATE VIEW PRICEGT50 AS
SELECT P_DESCRIPT, P_QOH, P_PRICE
FROM PRODUCT
WHERE P_PRICE > 50.00

SELECT * FROM PRICEGT50;

: Create a view to list all products to order, that is the quantity on hand is less that the minimum qty plus 10.

CREATE VIEW PROD_TO_ORDER AS
SELECT P_CODE, P_DESCRIPT, P_QOH, P_PRICE
FROM PRODUCT
WHERE P_QOH < (P_MIN +10)

SELECT * FROM PROD_TO_ORDER

: Create a view to show the total product cost and quantity on hand statistics grouped by vendor.

CREATE VIEW PROD_STATS AS
SELECT V_CODE,
SUM(P_QOH * P_PRICE) AS TOTCOST,
MAX(P_QOH) AS MAXQTY,
MIN(P_QOH) AS MINQTY,
AVG(P_QOH) AS AVGQTY
FROM PRODUCT
GROUP BY V_CODE;

SELECT * FROM PROD_STATS