Optimize the query

You have two tables Sales and Products as follows:

SALES(SALE_ID, YEAR, PRODUCT_ID, PRICE);
PRODUCTS(PRODUCT_ID, PRODUCT_NAME);

How can you rewrite the query given below such that reductant logic is eliminated?

SELECT T.YEAR,
 T.TOT_SAL,
 P.PROD_10_SAL
(
 SELECT YEAR,
 SUM(PRICE) TOT_SAL
 FROM SALES
 GROUP BY YEAR
) T
LEFT OUTER JOIN
(
 SELECT YEAR,
 SUM(PRICE) PROD_10_SAL
 FROM SALES
 WHERE PRODUCT_ID = 10
) P
ON (T.YEAR = P.YEAR);
Options
1.SELECT YEAR,
 SUM(CASE WHEN PRODUCT_ID = 10
 THEN PRICE
 ELSE NULL
 ) PROD_10_SAL,
 SUM(SALES) TOT_SAL
FROM SALES;
2.SELECT P.PRODUCT_ID,
 P.PRODUCT_NAME
FROM PRODUCTS P
 LEFT OUTER JOIN
 SALES S
ON (P.PRODUCT_ID = S.PRODUCT_ID)
WHERE S.SALE_ID IS NULL;
3.SELECT YEAR,
 SUM(CASE WHEN PRODUCT_ID = 10
 THEN PRICE
 ELSE NULL
 END ) PROD_10_SAL,
 SUM(SALES) TOT_SAL
FROM SALES
GROUP BY YEAR;
4.None of these

Related Posts