SQL CTE, ROW_NUMBER(), and Subqueries

Question 1

Q: What does CTE stand for in SQL?

Answer 1

A: Common Table Expression.

Question 2

Q: What keyword is used to define a CTE?

Answer 2

A: WITH.

Question 3

Q: True or False: A CTE is a temporary result set that can only be used within the query it’s defined in.

Answer 3

A: True.

Question 4

Q: How do you structure a basic CTE in SQL?

Answer 4

WITH CTE_name AS (
SELECT column1, column2, …
FROM table_name
WHERE conditions
)
SELECT column1, column2
FROM CTE_name;

Question 5

Q: True or False: A CTE can reference itself in a recursive query.

Answer 5

A: True.

Question 6

Q: What is the purpose of the ROW_NUMBER() function in SQL?

Answer 6

A: It assigns a unique, sequential integer to rows within a partition of a result set.

Question 7

Q: What SQL clause is required with ROW_NUMBER() to define how the rows should be numbered?

Answer 7

A: ORDER BY.

Question 8

Q: What is the purpose of the PARTITION BY clause with ROW_NUMBER()?

Answer 8

A: It divides the result set into partitions, and the row numbers reset for each partition.

Question 9

Q: Write a SQL query using ROW_NUMBER() to rank employees by salary in each department.

Answer 9

SELECT department, name, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

Question 10

Q: True or False: A subquery is a query embedded within another SQL query.

Answer 10

A: True.

Question 11

Q: Name three places where you can use a subquery in SQL.

Answer 11

A: In the SELECT, FROM, and WHERE clauses.

Question 12

Q: What’s a scalar subquery?

Answer 12

A: A subquery that returns a single value, often used in the SELECT or WHERE clause.

Question 13

Q: Write an example of a subquery to find employees with salaries above the average salary.

Answer 13

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Question 14

Q: What is a correlated subquery?

Answer 14

A: A subquery that references columns from the outer query, recalculating for each row in the outer query.

Question 15

Q: True or False: A correlated subquery may run multiple times, impacting performance.

Answer 15

A: True.

Question 16

Q: How do you find the top 3 salaries in each department using a CTE and ROW_NUMBER()?

Answer 16

WITH RankedEmployees AS (
SELECT department, name, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees
)
SELECT department, name, salary
FROM RankedEmployees
WHERE row_num <= 3;

Question 17

Q: In what scenario would you use a subquery in the FROM clause?

Answer 17

A: When you need to treat the result of the subquery as a temporary table for further queries.

Question 18

Q: Explain the purpose of a table subquery.

Answer 18

A: A table subquery returns multiple rows and columns and is often used in the FROM clause as a temporary result set.

Question 19

Q: Write a SQL query with a correlated subquery that finds employees who earn more than the average salary of their department.

Answer 19

SELECT name, salary
FROM employees AS e1
WHERE salary > (SELECT AVG(salary) FROM employees AS e2 WHERE e2.department_id = e1.department_id);

Question 20

Q: What’s one major benefit of using CTEs over subqueries?

Answer 20

A: CTEs improve readability by allowing you to break down complex queries into smaller, manageable parts and reuse them in the main query.