1. What order do rows come back in without ORDER BY?
- Primary key order
- Insertion order
- No guaranteed order
- Alphabetical order
Show answer
Answer: No guaranteed order
Always add ORDER BY when order matters.
Lesson: Thinking in Sets
25 questions from the SQL Deep Dive course, each with the correct answer and an explanation. Try to answer before you open the answer.
Answer: No guaranteed order
Always add ORDER BY when order matters.
Lesson: Thinking in Sets
Answer: HAVING
HAVING runs after GROUP BY.
Lesson: Logical Order of Evaluation
Answer: UNKNOWN (NULL)
Use IS NULL or IS NOT DISTINCT FROM.
Lesson: NULL and Three-Valued Logic
Answer: Unmatched left rows are removed, like an inner join
NULL fails the WHERE condition.
Lesson: Inner and Outer Joins
Answer: If the subquery returns a NULL, every NOT IN comparison is unknown
Use NOT EXISTS instead.
Lesson: Semi-Joins and Anti-Joins
Answer: Aggregate at the order grain before joining, or avoid the unneeded join
Match the grain to the metric.
Lesson: Fan-Out and Double Counting
Answer: The number of rows in each group hired since 2022
Conditional aggregation.
Lesson: GROUP BY, HAVING and FILTER
Answer: 1
It identifies aggregated levels.
Answer: Half-open ranges avoid gaps and double counting at boundaries
Works for dates and timestamps.
Answer: RANK
1, 2, 2, 4.
Lesson: Ranking and Top-N per Group
Answer: LAG
LEAD looks forward.
Answer: The total salary of that row's department
No ORDER BY means the whole partition.
Lesson: Shares, Comparisons and Bands
Answer: An anchor query and a recursive query joined to the previous results
Combined with UNION ALL.
Lesson: CTEs and Recursive Queries
Answer: LEFT JOIN LATERAL (...) ON true
Like any left join.
Lesson: LATERAL Joins
Answer: EXCEPT
Set difference.
Lesson: UNION, INTERSECT and EXCEPT
Answer: LEFT JOIN orders to a generated list of days and COALESCE the sum to 0
Start from the complete calendar.
Lesson: Filling Gaps in Time Series
Answer: The date minus its row number
That difference is the island key.
Lesson: Gaps and Islands
Answer: A unique constraint or unique index on the key
Let the database enforce it.
Lesson: Finding and Removing Duplicates
Answer: INSERT ... ON CONFLICT DO UPDATE
Atomic with respect to concurrent inserts.
Lesson: MERGE and Upserts
Answer: Returns the modified rows, like a SELECT
Great for audits and verification.
Answer: Undoes all changes made since BEGIN
Nothing is permanent until COMMIT.
Lesson: A Safe Workflow for Data Changes
Answer: The function changes the column, so the index order on raw values cannot be used directly
Use a range or an expression index.
Answer: WHERE kind = 'buy'
Leftmost prefix rule.
Lesson: Composite Index Column Order
Answer: It executes the query and reports actual row counts
Estimated versus actual.
Lesson: Reading EXPLAIN ANALYZE
Answer: Use NOT EXISTS rather than NOT IN with nullable subqueries
NULL-safe anti-joins.
Lesson: An SQL Review Checklist