CTE as readable subquery replacement in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of CTE vs Subquery for Average Salary

Given the table employees with columns id, department, and salary, what is the output of the following query using a CTE that calculates the average salary per department and then selects departments with average salary above 5000?

SQL

WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT department
FROM dept_avg
WHERE avg_salary > 5000
ORDER BY department;

A List of departments where the average salary is greater than 5000, sorted alphabetically

B List of all employees with salary above 5000, sorted by department

C List of departments with total salary above 5000, sorted alphabetically

D Syntax error due to missing GROUP BY clause

💡 Hint

Think about what the CTE calculates first and what the main query filters.

The CTE calculates the average salary per department. The main query selects departments where this average is greater than 5000. The result is a list of department names sorted alphabetically.

📝 Syntax

intermediate

Identify the Syntax Error in CTE Usage

Which option contains a syntax error in the use of a CTE?

SQL

WITH dept_count AS (
  SELECT department, COUNT(*) AS emp_count
  FROM employees
  GROUP BY department
)
SELECT * FROM dept_count WHERE emp_count > 10;

A WITH dept_count AS (SELECT department, COUNT(*) AS emp_count FROM employees GROUP BY department) SELECT * FROM dept_count WHERE emp_count > '10';

B WITH dept_count AS (SELECT department, COUNT(*) AS emp_count FROM employees GROUP BY department) SELECT * FROM dept_count WHERE emp_count > 10;

C WITH dept_count AS SELECT department, COUNT(*) AS emp_count FROM employees GROUP BY department SELECT * FROM dept_count WHERE emp_count > 10;

D ;01 > tnuoc_pme EREHW tnuoc_tped MORF * TCELES )tnemtraped YB PUORG seeyolpme MORF tnuoc_pme SA )*(TNUOC ,tnemtraped TCELES( SA tnuoc_tped HTIW

💡 Hint

Check the parentheses around the CTE definition.

Option C is missing parentheses around the CTE query, which is required syntax for CTEs.

❓ optimization

advanced

Optimizing Query with CTE vs Subquery

Consider a query that calculates total sales per region and then filters regions with sales above 10000. Which option shows the best use of a CTE to improve readability and avoid repeating the aggregation?

A WITH region_sales AS SELECT region, SUM(sales) AS total_sales FROM sales_data GROUP BY region SELECT region FROM region_sales WHERE total_sales > 10000;

B SELECT region FROM (SELECT region, SUM(sales) AS total_sales FROM sales_data GROUP BY region) WHERE total_sales > 10000;

C SELECT region FROM sales_data GROUP BY region HAVING SUM(sales) > 10000;

D WITH region_sales AS (SELECT region, SUM(sales) AS total_sales FROM sales_data GROUP BY region) SELECT region FROM region_sales WHERE total_sales > 10000;

💡 Hint

Look for correct CTE syntax and clear separation of steps.

Option D uses a CTE to calculate total sales per region once, then filters on that result, improving readability and avoiding repetition.

🔧 Debug

advanced

Debugging Incorrect CTE Result

A developer wrote this query to find departments with more than 5 employees, but it returns no rows. What is the likely cause?

SQL

WITH dept_emps AS (
  SELECT department, COUNT(employee_id) AS emp_count
  FROM employees
)
SELECT department FROM dept_emps WHERE emp_count > 5;

A Missing GROUP BY clause in the CTE causing emp_count to be a total count, not per department

B Incorrect alias name emp_count used in WHERE clause

C The WHERE clause should be HAVING clause

D CTE is missing a closing parenthesis

💡 Hint

Check if aggregation is grouped properly.

Without GROUP BY, COUNT(employee_id) returns total count for all rows, so emp_count is a single value and filtering by >5 returns no rows per department.

🧠 Conceptual

expert

Why Use CTE Instead of Subquery?

Which of the following is the best explanation for why using a CTE can be better than a subquery in complex SQL queries?

A CTEs always run faster than subqueries because they are cached automatically by the database engine.

B CTEs improve query readability by naming intermediate results and can be referenced multiple times, avoiding repetition.

C CTEs allow modifying data directly inside the query, unlike subqueries which are read-only.

D CTEs eliminate the need for indexes on tables used in the query.

💡 Hint

Think about how CTEs help organize complex queries.

CTEs make queries easier to read and maintain by breaking them into named parts and can be reused multiple times in the main query. They do not guarantee performance improvements or allow data modification.