Skip to Content
SQL Intermediate: Queries That Survive Real Data
Intermediate
0 likes
Courses
SQL Intermediate: Queries That Survive Real Data

Final Check: SQL Intermediate

Fifteen scenario questions across the whole course. If you get twelve or more right, you can build reports on messy data, design tables that protect themselves and read what a query is doing.

15 min SQLDatabasesPerformance Optimization Intermediate
Prev Next
Fullscreen Share
Course content

Sign in to save your progress.

Course Home
1. You join orders to order_items and run count star to count orders. There are 12 orders and the result is 20. What should you count instead?
count(DISTINCT order_id), or aggregate the items first. count(order_id), because it skips duplicates. Add a second join to get fewer rows. Use LEFT JOIN, which counts orders only.
2. Which query is the most reliable way to list customers who have never placed an order?
An INNER JOIN of customers and orders WHERE NOT EXISTS (a subquery that looks for the customer's orders) WHERE customer_id NOT IN (SELECT customer_id FROM orders), when that column allows NULL WHERE customer_id = (SELECT customer_id FROM orders)
3. How do you write two CTEs in one statement?
WITH first_step, second_step AS (...) SELECT ... WITH first_step AS (...), second_step AS (...) SELECT ... WITH first_step AS (...); WITH second_step AS (...); WITH first_step AS (...) WITH second_step AS (...) SELECT ...
4. You UNION two queries, and the row count is lower than the sum of the two. Why?
UNION sorts and drops the oldest rows. UNION removes duplicate rows. PostgreSQL limits UNION results. One of the queries was run twice.
5. Which window function gives you the value from the previous row in the same partition?
dense_rank lag rank row_number
6. A column visited_at holds timestamptz values. Which expression gives the day each visit belongs to in Manila?
date_trunc('day', visited_at) alone extract(day FROM visited_at) (visited_at AT TIME ZONE 'Asia/Manila')::date visited_at::date
7. A BETWEEN '2026-09-01' AND '2026-09-30' filter on a timestamp column misses visits from the afternoon of 30 September. What range is safe?
visited_at = '2026-09' visited_at >= '2026-09-01' AND visited_at < '2026-10-01' BETWEEN '2026-09-01' AND '2026-09-31' visited_at > '2026-09-01' AND visited_at < '2026-09-30'
8. In a time zone that moves its clocks forward on one day, what is the difference between adding INTERVAL '1 day' and INTERVAL '24 hours' to a timestamptz?
There is no difference. Both add 24 hours, but a day also changes the date. A day is always 25 hours. A day keeps the same clock time. 24 hours adds exactly 24 hours, so the clock reads one hour later.
9. A customer's city is stored on every order row, and someone updates only one of her three orders. What is the design problem?
The orders table has too few columns. UPDATE should never be used on orders. The city column should be an index. The same fact is stored in several places, so the table can now disagree with itself.
10. When is a copy of a value in another table a good design?
When it records history that must not change, such as the price at the time of the sale. Never. A normalized design never keeps copies. When it saves typing in queries. When both copies are updated by hand.
11. What is the main risk of ON DELETE CASCADE on a foreign key?
It makes the foreign key faster to check. It stops the parent from ever being deleted. It sets the child's column to NULL. It silently deletes the child rows, and their children, when the parent is deleted.
12. What is the cost of adding an index to a table?
It makes every SELECT faster, with no cost. It takes disk space, and each INSERT and UPDATE must also update the index. It locks the table for as long as the index exists. It stores a second copy of every row that you must keep in sync by hand.
13. You want to see the plan of an UPDATE with EXPLAIN ANALYZE. How do you do it safely?
Run it inside BEGIN and finish with ROLLBACK. Run it without ANALYZE, because ANALYZE never changes data. Nothing is needed, because EXPLAIN never changes data. Run it with LIMIT 1.
14. A transaction has been left open for hours after an UPDATE of one row. What do other sessions experience?
Readers are not blocked, and writers to that row wait. Every query on the database waits. Readers see the uncommitted value. The row is deleted when the session disconnects.
15. An application running at REPEATABLE READ gets the error could not serialize access due to concurrent update. What should it do?
Ignore the error and continue the transaction. Switch the server to a different time zone. Roll back and retry the whole transaction. Delete the row and insert it again.

About

Fifteen scenario questions across the whole course. If you get twelve or more right, you can build reports on messy data, design tables that protect themselves and read what a query is doing.

Duration
15 min
Last update
09/26/2026
Was this helpful?
​ 0 ​ 0

Comments (0)

Sign in to comment

No comments yet.