[Solved] Identify the issue in the following query:SELECT d.department_name, emp_count FROM (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY department_id) AS dept_counts JOIN departments... | SQL

SQL - Subqueries

Identify the issue in the following query:

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

ANo error; the query is correct

BThe alias 'dept_counts' is missing for the subquery

CThe subquery lacks a GROUP BY clause

DThe JOIN condition references incorrect column names

Step-by-Step Solution

Solution:

Step 1: Check subquery
The subquery correctly groups employees by department_id and counts them.
Step 2: Check alias usage
The subquery is aliased as 'dept_counts', which is required.
Step 3: Check JOIN condition
The JOIN uses dept_counts.department_id = d.id, which matches the schema.
Final Answer:
No error; the query is correct -> Option A
Quick Check:
Alias and GROUP BY are correctly used [OK]

Quick Trick: Check alias and GROUP BY in subquery [OK]

Common Mistakes:

MISTAKES

Forgetting GROUP BY in aggregation subquery
Missing alias for subquery
Incorrect JOIN condition columns

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

Identify the issue in the following query:

Step 1: Check subquery

Step 2: Check alias usage

Step 3: Check JOIN condition

Final Answer:

Quick Check:

Want More Practice?