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

Check Yourself: Thinking in Sets

Five scenario questions on CTE steps, materialization, UNION ALL, INTERSECT and EXCEPT. If you miss more than one, reread the lessons in this section.

5 min SQLDatabasesPerformance Optimization Intermediate
Prev Next
Fullscreen Share
Course content

Sign in to save your progress.

Course Home
1. A long query is written with WITH order_totals AS (...), and the final answer looks wrong. How do you check just the first step?
Run EXPLAIN on the whole query. Keep the WITH part and replace the final SELECT with SELECT * FROM order_totals. Add DISTINCT to the final SELECT. Delete the WITH part and rewrite the query.
2. A CTE is used in two places in the same statement. By default, what does PostgreSQL do?
It stores the CTE in a permanent table. It computes the CTE again at each place. It refuses, because a CTE can be used only once. It computes the CTE once and reuses the result.
3. Two tables hold sales for different years and you add the amounts. Two real sales on the same day have the same customer and amount. Which operator is right?
INTERSECT EXCEPT UNION UNION ALL
4. You have a query for September buyers and a query for August buyers. How do you list the customers who bought in September and not in August?
The September query, then EXCEPT, then the August query. The August query, then EXCEPT, then the September query. The two queries with UNION. The two queries with INTERSECT.
5. Two queries each return a row where the city is NULL. What does INTERSECT do with those rows?
It returns both rows, because they are different. It returns an error. It returns one NULL row, because set operations treat NULLs as equal. It drops them, because NULL is never equal to NULL.

About

Five scenario questions on CTE steps, materialization, UNION ALL, INTERSECT and EXCEPT. If you miss more than one, reread the lessons in this section.

Duration
5 min
Last update
10/07/2026
Was this helpful?
​ 0 ​ 0

Comments (0)

Sign in to comment

No comments yet.