You want to create a view top_customers that shows customers with total purchases over 1000. The orders table has columns customer_id and amount. Which query correctly creates this view?
hard📝 Application Q8 of 15
SQL - Views
You want to create a view top_customers that shows customers with total purchases over 1000. The orders table has columns customer_id and amount. Which query correctly creates this view?
ACREATE VIEW top_customers AS SELECT customer_id, SUM(amount) FROM orders WHERE amount > 1000 GROUP BY customer_id;
BCREATE VIEW top_customers AS SELECT customer_id, amount FROM orders WHERE amount > 1000;
CCREATE VIEW top_customers AS SELECT customer_id, SUM(amount) AS total FROM orders WHERE total > 1000 GROUP BY customer_id;
DCREATE VIEW top_customers AS SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000;
Step-by-Step Solution
Solution:
Step 1: Understand the need for aggregation and filtering
We must sum amounts per customer and filter those sums over 1000.
Step 2: Use GROUP BY and HAVING correctly
HAVING filters aggregated results; WHERE cannot filter aggregates.
Step 3: Check each option
CREATE VIEW top_customers AS SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000; correctly uses GROUP BY and HAVING to filter customers with total purchases over 1000.
Final Answer:
CREATE VIEW top_customers AS SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000; -> Option D
Quick Check:
Use HAVING to filter aggregates in views [OK]
Quick Trick:Use HAVING to filter sums in views [OK]
Common Mistakes:
MISTAKES
Using WHERE to filter aggregates
Omitting GROUP BY
Filtering on alias in WHERE clause
Master "Views" in SQL
9 interactive learning modes - each teaches the same concept differently
