CTE vs subquery vs view decision in SQL - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

When to use a CTE instead of a subquery?

Which situation best justifies using a Common Table Expression (CTE) instead of a subquery in SQL?

A When you want to reuse the same result multiple times within a query for better readability.

B When you need to permanently store the result for future queries.

C When you want to create an index on the result set.

D When you want to avoid using temporary tables for performance reasons.

💡 Hint

Think about readability and reusability inside a single query.

A CTE is useful when you want to write a query once and reuse its result multiple times within the same query, improving readability and maintainability.

❓ query_result

intermediate

Output difference between subquery and view

Given a table Employees with columns id, name, and department, what is the output of the following queries?

Query 1 (using subquery):

SELECT name FROM Employees WHERE department = (SELECT department FROM Employees WHERE id = 3);

Query 2 (using view):

CREATE VIEW DeptView AS SELECT department FROM Employees WHERE id = 3;
SELECT name FROM Employees WHERE department = (SELECT department FROM DeptView);

Assuming employee with id=3 belongs to 'Sales' department, what will both queries return?

A Both queries return names of all employees in the 'Sales' department.

B Query 1 returns names in 'Sales', Query 2 returns an error because views cannot be used in WHERE.

C Query 1 returns only the employee with id=3, Query 2 returns all employees in 'Sales'.

D Both queries return an empty set because subqueries and views behave differently.

💡 Hint

Think about how subqueries and views work in filtering data.

Both queries filter employees by the department of employee with id=3, which is 'Sales'. Both return all employees in 'Sales'. Views can be used in subqueries.

📝 Syntax

advanced

Identify the correct syntax for CTE usage

Which of the following SQL statements has correct syntax when defining a CTE?

SQL

WITH SalesCTE AS (SELECT * FROM Sales WHERE amount > 1000) SELECT * FROM SalesCTE;

A WITH SalesCTE (SELECT * FROM Sales WHERE amount > 1000) SELECT * FROM SalesCTE;

B WITH SalesCTE AS (SELECT * FROM Sales WHERE amount > 1000) SELECT * FROM SalesCTE;

C WITH SalesCTE AS SELECT * FROM Sales WHERE amount > 1000; SELECT * FROM SalesCTE;

D WITH SalesCTE AS (SELECT * FROM Sales WHERE amount > 1000); SELECT * FROM SalesCTE;

💡 Hint

Check the placement of AS and parentheses in CTE syntax.

Option B is the correct syntax for a CTE: WITH name AS (query) SELECT .... Other options miss AS, parentheses, or have misplaced semicolons.

❓ optimization

advanced

Performance impact of views vs CTEs

Which statement about performance is true when comparing views and CTEs in SQL?

A Views and CTEs both create temporary tables that slow down queries.

B CTEs are always faster than views because they are temporary and inline.

C Views always improve query performance because they store precomputed results.

D Neither views nor CTEs store data physically; performance depends on query complexity and indexing.

💡 Hint

Think about how views and CTEs are processed by the database engine.

Views and CTEs are query definitions, not physical tables. Performance depends on how the database optimizes the query and indexes available.

🔧 Debug

expert

Why does this query with a view fail?

Consider this SQL code:

CREATE VIEW RecentOrders AS SELECT * FROM Orders WHERE order_date > '2024-01-01';
SELECT customer_id, COUNT(*) FROM RecentOrders GROUP BY customer_id HAVING COUNT(*) > 5;

Why might this query fail in some SQL systems?

A Because the view does not include a GROUP BY clause, causing aggregation errors.

B Because views cannot be used in GROUP BY clauses.

C Because some SQL systems require explicit schema qualification when querying views.

D Because the view is not materialized and the query optimizer cannot handle aggregation on views.

💡 Hint

Consider database-specific requirements for referencing views.

Some SQL systems require the view to be referenced with schema name (e.g., dbo.RecentOrders). Without it, the query may fail due to object not found.