Advanced SQL Queries Flashcards
Subqueries and nested SELECTs Correlated subqueries Common Table Expressions (CTEs) with WITH Recursive CTEs CASE statements Window functions: RANK, DENSE_RANK, ROW_NUMBER, LAG/LEAD Advanced filtering and pagination (34 cards)
What is a subquery in SQL?
A subquery is a SELECT query nested inside another SQL query, often used in WHERE, FROM, or SELECT clauses.
What is a correlated subquery?
A correlated subquery references columns from the outer query and is executed once per row of the outer query.
What is a Common Table Expression (CTE)?
A CTE is a temporary named result set defined using the WITH keyword, used for better query readability and reusability.
What is a recursive CTE?
A CTE that calls itself to perform hierarchical or recursive operations like traversing trees or graphs.
What does the CASE statement do in SQL?
CASE adds conditional logic to queries, returning specific values based on when-then clauses.
What are SQL window functions?
Functions like RANK(), ROW_NUMBER(), LAG(), and LEAD() that perform calculations across a result set partition without collapsing rows.
What does the RANK() function do?
Assigns a unique rank to rows within a partition, allowing for gaps in ranking if there are ties.
What does the DENSE_RANK() function do?
Similar to RANK(), but assigns the next consecutive rank without gaps for tied values.
What does ROW_NUMBER() do in SQL?
Assigns a unique sequential number to each row in a result set partition.
What is the purpose of LAG() and LEAD()?
They access preceding (LAG) or following (LEAD) row values without using self-joins.
What is advanced pagination in SQL?
Using OFFSET and FETCH/LIMIT clauses to return subsets of rows for paginated results.
What is a benefit of using CTEs?
Improves readability and helps break complex queries into modular parts.
What is a downside of recursive CTEs?
They can be inefficient or lead to infinite loops if not properly limited with termination conditions.
What is a best practice when using subqueries?
Use them for filtering and aggregation only when necessary, and prefer joins or CTEs for readability and performance.
What is a common use case for window functions?
Calculating running totals, rankings, time-based comparisons, or pagination without grouping.
How do advanced queries impact system design?
Enable powerful analytics directly in SQL, reducing the need for application-side processing.
What’s the architectural implication of using CTEs heavily?
Overuse can lead to performance hits if the database engine doesn’t optimize them as reusable expressions.
What are performance concerns with nested SELECTs?
They may result in inefficient execution plans if not optimized or indexed correctly.
What’s a good monitoring practice for advanced SQL?
Use EXPLAIN or query plans to understand and optimize execution paths.
What is a tradeoff of using CASE statements?
Improves logic control within SQL but can make queries harder to maintain and debug.
What is a real-world use case for recursive CTEs?
Finding hierarchical paths like employee-manager chains or category-subcategory trees.
What’s a gotcha with OFFSET in pagination?
Large offsets can degrade performance because the database still processes skipped rows.
How can LAG/LEAD help in time series data?
By comparing current rows to previous/following rows without joins, simplifying trends or deltas.
What’s a potential gotcha with correlated subqueries?
They can lead to poor performance because they execute once per outer query row.
