Solution:Step 1: Analyze the subquery in FROM clauseThe subquery counts employees per department and aliases as dept_emp with columns department_id and emp_count.Step 2: Understand the JOIN with departmentsThe main query joins dept_emp with departments on matching department_id to get department nam

[Solved] Given the tables:employees(id, name, department_id)departments(id, name)What will the following query return?SELECT d.... | SQL

SQL - Subqueries

Given the tables:
employees(id, name, department_id)
departments(id, name)
What will the following query return?

SELECT d.name, emp_count FROM (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY department_id) AS dept_emp JOIN departments d ON dept_emp.department_id = d.id;

AList of departments without employee counts

BList of employees with their department names

CSyntax error due to missing alias

DList of department names with the number of employees in each

Step-by-Step Solution

Solution:

Step 1: Analyze the subquery in FROM clause
The subquery counts employees per department and aliases as dept_emp with columns department_id and emp_count.
Step 2: Understand the JOIN with departments
The main query joins dept_emp with departments on matching department_id to get department names and employee counts.
Final Answer:
List of department names with the number of employees in each -> Option D
Quick Check:
Subquery counts employees per department = Department name + count [OK]

Quick Trick: Subquery in FROM can aggregate before join [OK]

Common Mistakes:

MISTAKES

Confusing employee list with counts
Missing alias causing syntax errors
Assuming subquery returns employee details

Master "Subqueries" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

Given the tables:

Step 1: Analyze the subquery in FROM clause

Step 2: Understand the JOIN with departments

Final Answer:

Quick Check:

Want More Practice?