Chapter 8 — Quiz

13 questions. Answers at the bottom.

Multiple choice

Q1. What does 'a' || NULL || 'b' return? - A) 'ab' - B) NULL - C) 'a b' - D) An error

Q2. Which concatenation ignores NULL arguments? - A) || - B) concat(...) - C) + - D) add()

Q3. SELECT 7 / 2; (both integers) returns: - A) 3.5 - B) 3 - C) 4 - D) 3.50

Q4. To get 3.5 from 7 and 2, you can write: - A) 7 / 2 - B) 7.0 / 2 - C) round(7/2) - D) 7 // 2

Q5. date_trunc('month', order_date) is most useful for: - A) Deleting old orders - B) Grouping a time series by month - C) Converting to text - D) Removing duplicates

Q6. The safest way to query "all of March 2024" is: - A) BETWEEN '2024-03-01' AND '2024-03-31' - B) >= '2024-03-01' AND < '2024-04-01' - C) extract(month) = 3 - D) LIKE '2024-03%'

Q7. CAST('42' AS integer) and '42'::integer are: - A) Different operations - B) Equivalent (standard vs. PostgreSQL shorthand) - C) Both invalid - D) Only valid in WHERE

Q8. COALESCE(phone, 'none') returns: - A) Always 'none' - B) phone if not NULL, otherwise 'none' - C) NULL if phone is NULL - D) The longer of the two

Q9. total / NULLIF(count, 0) protects against: - A) NULL totals - B) Division by zero (returns NULL instead of erroring) - C) Negative numbers - D) Rounding errors

Q10. CASE WHEN price >= 1000 THEN 'premium' WHEN price >= 200 THEN 'mid' ELSE 'budget' END evaluates conditions: - A) All at once - B) Top to bottom, returning the first match - C) Bottom to top - D) Randomly

Q11. Applying lower(email) in a WHERE clause can: - A) Speed up the query always - B) Prevent use of a plain index on email - C) Cause a syntax error - D) Change the result's meaning

True/False

Q12. SQL string positions are 0-based (the first character is index 0). (True / False)

Q13. timestamp and timestamptz are the same type. (True / False)

Short answer

Q14. Why is total_items BETWEEN '2024-03-01' AND '2024-03-31' likely to miss orders placed late on March 31 when the column is a timestamptz?

---

Answer key

Q1 — B. || propagates NULL: any NULL operand makes the whole result NULL.

Q2 — B. concat() skips NULL arguments; || does not.

Q3 — B. Integer ÷ integer = integer (3), remainder discarded.

Q4 — B. Make an operand non-integer: 7.0 / 2 → 3.5.

Q5 — B. date_trunc rounds timestamps down to a unit — ideal for monthly/daily grouping.

Q6 — B. A half-open range >= start AND < next-start includes all times in the period; BETWEEN ... '2024-03-31' stops at midnight and misses that day's later times.

Q7 — B. CAST(x AS t) (standard) and x::t (PostgreSQL) are equivalent.

Q8 — B. COALESCE returns the first non-NULL argument.

Q9 — B. NULLIF(count, 0) turns a 0 divisor into NULL, so the division yields NULL instead of erroring.

Q10 — B. CASE checks conditions in order and returns the first match (or ELSE/NULL).

Q11 — B. A function on the column means a plain index on the raw column can't be used; you'd need an expression index (Chapter 23).

Q12 — False. SQL strings are 1-based; the first character is position 1.

Q13 — False. They're distinct types; timestamptz is timezone-aware. Mixing them causes time bugs.

Q14. A timestamptz column stores a time of day, not just a date. BETWEEN ... AND '2024-03-31' treats the upper bound as 2024-03-31 00:00:00, so any order timestamped later on March 31 is excluded. Use a half-open range >= '2024-03-01' AND < '2024-04-01' instead.

Scoring: 11–13 you've got the toolkit; 8–10 re-drill integer division and date ranges; below 8, redo Exercises B–C.