You want to create a view employee_summary that shows each department's name and the count of employees. The tables are departments(id, name) and employees(id, department_id). Which is the correct statement?
ACREATE VIEW employee_summary AS SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
BCREATE VIEW employee_summary AS SELECT name, COUNT(id) FROM employees GROUP BY name;
CCREATE VIEW employee_summary AS SELECT d.name, COUNT(e.id) FROM departments d LEFT JOIN employees e ON d.id = e.department_id GROUP BY d.name;
DCREATE VIEW employee_summary AS SELECT d.name, e.id FROM departments d JOIN employees e ON d.id = e.department_id;
Step-by-Step Solution
Solution:
Step 1: Understand the requirement
We need department name and count of employees per department, including departments with zero employees.
Step 2: Choose correct join and aggregation
CREATE VIEW employee_summary AS SELECT d.name, COUNT(e.id) FROM departments d LEFT JOIN employees e ON d.id = e.department_id GROUP BY d.name; uses LEFT JOIN to include all departments and groups by department name with COUNT of employees.
Final Answer:
CREATE VIEW employee_summary AS SELECT d.name, COUNT(e.id) FROM departments d LEFT JOIN employees e ON d.id = e.department_id GROUP BY d.name; -> Option C
Quick Check:
LEFT JOIN with GROUP BY counts employees per department [OK]
Quick Trick:Use LEFT JOIN to include all departments even with zero employees [OK]
Common Mistakes:
Using INNER JOIN excludes departments without employees
Grouping by wrong columns
Not joining tables properly
Master "Views and Materialized Views" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
