Table SALES

Column Name
STORE_ID
SALES_DATE
SALES_AMOUNT

Which SQL statement lets you list all stores whose total sales amount is over 5000 ?

1. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000;
2. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SALES_AMOUNT > 5000;
3. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
   WHERE SUM(SALES_AMOUNT) > 5000 GROUP BY STORE_ID;
4. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
   WHERE SALES_AMOUNT > 5000 GROUP BY STORE_ID;

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000;

Explanation

1. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000; — This statement selects the store ID and calculates the total sales amount for each store using the SUM() aggregate function. The GROUP BY STORE_ID clause ensures that the results are grouped by store ID. The HAVING SUM(SALES_AMOUNT) > 5000 condition then filters the grouped data, selecting only those stores whose total sales amount is over 5000.
2. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SALES_AMOUNT > 5000; — This option is incorrect because the HAVING clause cannot directly reference SALES_AMOUNT without an aggregate function like SUM() since SUM(SALES_AMOUNT) is used in the SELECT statement.
3. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE SUM(SALES_AMOUNT) > 5000 GROUP BY STORE_ID; — This option is incorrect because aggregate functions like SUM(SALES_AMOUNT) cannot be used directly in the WHERE clause. The WHERE clause is used for filtering individual rows before grouping.
4. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE SALES_AMOUNT > 5000 GROUP BY STORE_ID; — This option is incorrect because it tries to filter individual sales amounts (SALES_AMOUNT) directly without using the SUM() aggregate function to calculate the total sales amount for each store. The GROUP BY STORE_ID clause is used for grouping after the filtering, which is not the correct approach for filtering based on the total sales amount.