How can you create a view EmployeeInfo that combines Employees and Departments tables to show employee name and department name?
hard📝 Application Q9 of 15
SQL - Views
How can you create a view EmployeeInfo that combines Employees and Departments tables to show employee name and department name?
ACREATE VIEW EmployeeInfo AS SELECT name, department_name FROM Employees LEFT JOIN Departments;
BCREATE VIEW EmployeeInfo AS SELECT e.name, d.department_name FROM Employees e JOIN Departments d ON e.department_id = d.id;
CCREATE VIEW EmployeeInfo AS SELECT name, department_name FROM Employees, Departments;
DCREATE VIEW EmployeeInfo AS SELECT e.name, d.department_name FROM Employees e JOIN Departments d WHERE e.department_id = d.id;
Step-by-Step Solution
Solution:
Step 1: Understand the join requirement
We need to join Employees and Departments on department_id to get employee and department names.
Step 2: Evaluate each option
CREATE VIEW EmployeeInfo AS SELECT e.name, d.department_name FROM Employees e JOIN Departments d ON e.department_id = d.id; uses explicit JOIN with ON clause correctly. CREATE VIEW EmployeeInfo AS SELECT name, department_name FROM Employees LEFT JOIN Departments; has incomplete JOIN syntax. CREATE VIEW EmployeeInfo AS SELECT name, department_name FROM Employees, Departments; lacks join condition causing Cartesian product. CREATE VIEW EmployeeInfo AS SELECT e.name, d.department_name FROM Employees e JOIN Departments d WHERE e.department_id = d.id; uses JOIN without ON clause, which is invalid syntax.
Final Answer:
CREATE VIEW EmployeeInfo AS SELECT e.name, d.department_name FROM Employees e JOIN Departments d ON e.department_id = d.id; -> Option B
Quick Check:
Use explicit JOIN with ON for combining tables in views [OK]
Quick Trick:Use explicit JOIN with ON for clear view joins [OK]
Common Mistakes:
MISTAKES
Missing join condition
Using incomplete JOIN syntax
Confusing implicit and explicit joins
Master "Views" in SQL
9 interactive learning modes - each teaches the same concept differently
