04pallav
9/13/2017 - 9:54 PM

WITH/Save a table for use/CTE

WITH/Save a table for use/CTE

WITH ALL_PRODUCTS
AS
(SELECT MODEL,PRICE FROM PC
UNION
SELECT MODEL,PRICE FROM LAPTOP
UNION
SELECT MODEL,PRICE FROM PRINTER)

OR

CREATE VIEW ALL_PRODUCTS
AS
(SELECT MODEL,PRICE FROM PC
UNION
SELECT MODEL,PRICE FROM LAPTOP
UNION
SELECT MODEL,PRICE FROM PRINTER)


SELECT
MODEL FROM ALL_PRODUCTS
WHERE PRICE = (SELECT MAX(PRICE) FROM ALL_PRODUCTS)


SELECT AVG(PRICE) FROM (select PC.price from PRODUCT P,PC 
WHERE P.MODEL=PC.MODEL AND P.MAKER='A'
UNION ALL 
SELECT L.PRICE FROM PRODUCT P, LAPTOP L WHERE P.MODEL=L.MODEL AND P.MAKER='A') B