Which of these is the correct way to create this view assuming tables customers(id, name) and orders(customer_id, amount)?
hard📝 Application Q15 of 15
PostgreSQL - Views and Materialized Views
You want to create a view customer_orders that shows each customer's name and the total amount they spent. Which of these is the correct way to create this view assuming tables customers(id, name) and orders(customer_id, amount)?
ACREATE VIEW customer_orders AS SELECT c.name, o.amount FROM customers c, orders o WHERE c.id = o.customer_id;
BCREATE VIEW customer_orders AS SELECT name, amount FROM customers JOIN orders ON id = customer_id;
CCREATE VIEW customer_orders AS SELECT c.name, SUM(o.amount) FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.name;
DCREATE VIEW customer_orders AS SELECT name, SUM(amount) FROM orders GROUP BY name;
Step-by-Step Solution
Solution:
Step 1: Understand the requirement
We need each customer's name and total amount spent, so we must join customers and orders and sum amounts grouped by customer name.
Step 2: Check each option
CREATE VIEW customer_orders AS SELECT c.name, SUM(o.amount) FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.name; correctly joins tables, sums amounts, and groups by customer name. Others miss grouping or join conditions.
Final Answer:
CREATE VIEW customer_orders AS SELECT c.name, SUM(o.amount) FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.name; -> Option C
Quick Check:
Join + SUM + GROUP BY = correct view [OK]
Quick Trick:Use JOIN with GROUP BY and SUM for aggregation views [OK]
Common Mistakes:
Missing GROUP BY when using SUM
Not joining tables properly
Selecting columns not in GROUP BY
Master "Views and Materialized Views" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
