DEA-C01 Question 335
Single answerYou are working with an Amazon Redshift cluster and have a table named 'sales' that stores transactional sales data, and another table named 'customers' that stores customer information. You need to write a query that retrieves the total sales amount for each customer who has made purchases, along with their customer name, but only if the total sales exceed $500. Which of the following SQL queries achieves this objective?
- A
SELECT c.customer_name, SUM(s.sale_amount) AS total_sales FROM sales s JOIN customers c ON s.customer_id = c.customer_id WHERE total_sales > 500 GROUP BY c.customer_name;
- B
SELECT c.customer_name, SUM(s.sale_amount) AS total_sales FROM sales s JOIN customers c ON s.customer_id = c.customer_id GROUP BY c.customer_name HAVING SUM(s.sale_amount) > 500;
- C
SELECT c.customer_name, SUM(s.sale_amount) AS total_sales FROM sales s JOIN customers c ON s.customer_id = c.customer_id WHERE SUM(s.sale_amount) > 500 GROUP BY c.customer_name;
- D
SELECT c.customer_name, s.sale_amount AS total_sales FROM sales s JOIN customers c ON s.customer_id = c.customer_id GROUP BY c.customer_name HAVING SUM(sale_amount) > 500;
Show answer and explanation
Correct answer: B
Explanation
In SQL, when working with aggregate functions like SUM(), filtering based on the results of these functions must be done using the 'HAVING' clause instead of 'WHERE'. The correct query accurately groups by customer_name, calculates the total sales using SUM(), and filters customers whose total sales exceed $500 using 'HAVING'.
- A. Incorrect.
This option is incorrect because filtering using 'WHERE' with an alias like 'total_sales' is not valid in SQL. Aggregations like SUM must be filtered using 'HAVING', not 'WHERE'.
- B. Correct.
This option is correct because it properly uses the 'HAVING' clause to filter aggregated results where the total sales exceed $500. It also correctly groups by 'customer_name' and calculates the SUM of 'sale_amount'.
- C. Incorrect.
This option is incorrect because 'WHERE' cannot be used to filter aggregated results like SUM(s.sale_amount). The 'HAVING' clause must be used for such conditions.
- D. Incorrect.
This option is incorrect because it incorrectly refers to 's.sale_amount' directly as 'total_sales' in the SELECT statement, which would lead to a syntax error. Additionally, the calculation of the total sales is not done correctly.