Chapter 5 — Quiz

14 questions. Answers and explanations at the bottom.

Multiple choice

Q1. In SELECT name, price FROM products, the SELECT list performs which algebra operation? - A) Selection (σ) - B) Projection (π) - C) Join (⋈) - D) Union (∪)

Q2. The logical order of evaluation is: - A) SELECT → FROM → WHERE → ORDER BY - B) FROM → WHERE → SELECT → ORDER BY → LIMIT - C) WHERE → FROM → SELECT → LIMIT - D) FROM → SELECT → WHERE → ORDER BY

Q3. Why can't WHERE use an alias defined in the SELECT list? - A) Aliases aren't allowed in WHERE - B) WHERE is evaluated before the SELECT list, so the alias doesn't exist yet - C) Aliases only work with numbers - D) It's a PostgreSQL bug

Q4. Which finds rows where phone has no value? - A) WHERE phone = NULL - B) WHERE phone IS NULL - C) WHERE phone = '' - D) WHERE NOT phone

Q5. WHERE category_id = 3 OR category_id = 4 AND price < 600 is interpreted as: - A) (category_id = 3 OR category_id = 4) AND price < 600 - B) category_id = 3 OR (category_id = 4 AND price < 600) - C) An error - D) category_id = 3 AND category_id = 4 AND price < 600

Q6. price BETWEEN 100 AND 500 is equivalent to: - A) price > 100 AND price < 500 - B) price >= 100 AND price <= 500 - C) price >= 100 AND price < 500 - D) price IN (100, 500)

Q7. Which is the clean way to write "status is paid, shipped, or delivered"? - A) status = 'paid' = 'shipped' = 'delivered' - B) status IN ('paid','shipped','delivered') - C) status BETWEEN 'paid' AND 'delivered' - D) status LIKE 'paid|shipped|delivered'

Q8. WHERE name LIKE 'Ultra%' matches names that: - A) Contain "Ultra" anywhere - B) Start with "Ultra" - C) End with "Ultra" - D) Equal "Ultra" exactly

Q9. The PostgreSQL operator for case-insensitive pattern matching is: - A) LIKE - B) ILIKE - C) MATCH - D) ~~* only

Q10. SELECT ... ORDER BY price DESC LIMIT 3 returns: - A) 3 random rows - B) The 3 rows with the highest price - C) The first 3 rows inserted - D) An error

Q11. What's wrong with SELECT * FROM orders LIMIT 10; for "the 10 newest orders"? - A) LIMIT is invalid - B) Without ORDER BY, which 10 rows you get is undefined - C) You can't use * with LIMIT - D) Nothing — it's correct

True/False

Q12. DISTINCT applies only to the first column in the SELECT list. (True / False)

Q13. For a boolean column is_active, WHERE is_active is equivalent to WHERE is_active = true. (True / False)

Short answer

Q14. Explain why x NOT IN (1, 2, NULL) can return no rows even when you expect matches. What should you use instead?

---

Answer key

Q1 — B. The SELECT list is projection (choosing columns). Selection (σ) is WHERE.

Q2 — B. FROM → WHERE → SELECT → ORDER BY → LIMIT. This governs alias visibility.

Q3 — B. WHERE runs before the SELECT list is computed, so its aliases don't exist yet. (ORDER BY, which runs after, can use them.)

Q4 — B. IS NULL. = NULL is never true (three-valued logic).

Q5 — B. AND binds tighter than OR. Always parenthesize when mixing them.

Q6 — B. BETWEEN is inclusive of both endpoints.

Q7 — B. IN (...). Cleaner than chained ORs; BETWEEN/LIKE don't mean "one of these."

Q8 — B. % matches any trailing characters, so 'Ultra%' = starts with "Ultra".

Q9 — B. ILIKE (PostgreSQL). Elsewhere, use LOWER(col) LIKE LOWER(pattern).

Q10 — B. ORDER BY price DESC defines "highest," and LIMIT 3 takes the top 3.

Q11 — B. Rows have no inherent order; without ORDER BY, "the first 10" is undefined and can vary. Pair LIMIT with ORDER BY.

Q12 — False. DISTINCT dedupes on the entire selected row (all listed columns together).

Q13 — True. A boolean column is already true/false; = true is redundant (though harmless).

Q14. If the list contains NULL, x NOT IN (…, NULL) evaluates to unknown (never true) for every row, so nothing is returned — three-valued logic again. Use NOT EXISTS (Chapter 9), or ensure the list has no NULLs.

Scoring: 12–14 you're fluent in basic queries; 9–11 re-drill WHERE operators and the evaluation order; below 9, redo Exercises A–E before Chapter 6.