SQL CTE, ROW_NUMBER(), and Subqueries Flashcards
From GPT
Q: What does CTE stand for in SQL?
A: Common Table Expression.
Q: What keyword is used to define a CTE?
A: WITH.
Q: True or False: A CTE is a temporary result set that can only be used within the query it’s defined in.
A: True.
Q: How do you structure a basic CTE in SQL?
WITH CTE_name AS (
SELECT column1, column2, …
FROM table_name
WHERE conditions
)
SELECT column1, column2
FROM CTE_name;
Q: True or False: A CTE can reference itself in a recursive query.
A: True.
Q: What is the purpose of the ROW_NUMBER() function in SQL?
A: It assigns a unique, sequential integer to rows within a partition of a result set.
Q: What SQL clause is required with ROW_NUMBER() to define how the rows should be numbered?
A: ORDER BY.
Q: What is the purpose of the PARTITION BY clause with ROW_NUMBER()?
A: It divides the result set into partitions, and the row numbers reset for each partition.
Q: Write a SQL query using ROW_NUMBER() to rank employees by salary in each department.
SELECT department, name, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;
Q: True or False: A subquery is a query embedded within another SQL query.
A: True.
Q: Name three places where you can use a subquery in SQL.
A: In the SELECT, FROM, and WHERE clauses.
Q: What’s a scalar subquery?
A: A subquery that returns a single value, often used in the SELECT or WHERE clause.
Q: Write an example of a subquery to find employees with salaries above the average salary.
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Q: What is a correlated subquery?
A: A subquery that references columns from the outer query, recalculating for each row in the outer query.
Q: True or False: A correlated subquery may run multiple times, impacting performance.
A: True.
Q: How do you find the top 3 salaries in each department using a CTE and ROW_NUMBER()?
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;
Q: In what scenario would you use a subquery in the FROM clause?
A: When you need to treat the result of the subquery as a temporary table for further queries.
Q: Explain the purpose of a table subquery.
A: A table subquery returns multiple rows and columns and is often used in the FROM clause as a temporary result set.
Q: Write a SQL query with a correlated subquery that finds employees who earn more than the average salary of their department.
SELECT name, salary
FROM employees AS e1
WHERE salary > (SELECT AVG(salary) FROM employees AS e2 WHERE e2.department_id = e1.department_id);
Q: What’s one major benefit of using CTEs over subqueries?
A: CTEs improve readability by allowing you to break down complex queries into smaller, manageable parts and reuse them in the main query.
