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