You want to create a view TopSellers that shows seller_id and total_sales for sellers with sales over 1000. Which SQL is correct?
hard📝 Application Q8 of 15
SQL - Views
You want to create a view TopSellers that shows seller_id and total_sales for sellers with sales over 1000. Which SQL is correct?
ACREATE VIEW TopSellers AS SELECT seller_id, SUM(sales) AS total_sales FROM Sales GROUP BY seller_id HAVING SUM(sales) > 1000;
BCREATE VIEW TopSellers AS SELECT seller_id, sales FROM Sales WHERE sales > 1000;
CCREATE VIEW TopSellers AS SELECT seller_id, SUM(sales) FROM Sales WHERE SUM(sales) > 1000;
DCREATE VIEW TopSellers AS SELECT seller_id, total_sales FROM Sales WHERE total_sales > 1000;
Step-by-Step Solution
Solution:
Step 1: Understand the requirement
The view must show seller_id and total sales, only for sellers with total sales over 1000.
Step 2: Analyze each option
CREATE VIEW TopSellers AS SELECT seller_id, SUM(sales) AS total_sales FROM Sales GROUP BY seller_id HAVING SUM(sales) > 1000; correctly uses GROUP BY seller_id, SUM(sales) as total_sales, and HAVING to filter groups. Others either lack aggregation or use invalid WHERE with aggregate.
Final Answer:
CREATE VIEW TopSellers AS SELECT seller_id, SUM(sales) AS total_sales FROM Sales GROUP BY seller_id HAVING SUM(sales) > 1000; -> Option A
Quick Check:
Use GROUP BY and HAVING for aggregate filters in views [OK]
Quick Trick:Use HAVING to filter groups in CREATE VIEW [OK]
Common Mistakes:
MISTAKES
Using WHERE with aggregates
Not aliasing SUM(sales)
Missing GROUP BY clause
Master "Views" in SQL
9 interactive learning modes - each teaches the same concept differently
