SQL Sorting and Limiting Results 3 — Questions and Answers
Question 1: How are NULL values typically sorted in PostgreSQL ascending order by default?
- NULLs appear last (Correct answer)
- NULLs appear first
- NULLs are excluded
- NULLs cause an error
Correct answer: NULLs appear last
In PostgreSQL ascending order, NULLs are treated as larger and sort last by default.
Question 2: Which clause forces NULL values to appear first in PostgreSQL?
- ORDER BY col NULLS FIRST (Correct answer)
- ORDER BY col NULL TOP
- ORDER BY NULL col
- ORDER BY col ASC NULLS
Correct answer: ORDER BY col NULLS FIRST
NULLS FIRST explicitly places NULL values before non-NULL values.
Question 3: Can you ORDER BY a column that is not in the SELECT list?
- Yes, in most databases (Correct answer)
- No, never
- Only if it is a primary key
- Only with GROUP BY
Correct answer: Yes, in most databases
Most databases allow sorting by columns not included in the SELECT list.
Question 4: Which standard SQL clause limits rows using FETCH?
- FETCH FIRST n ROWS ONLY (Correct answer)
- FETCH TOP n ROWS
- FETCH LIMIT n
- FETCH n ROWS LIMIT
Correct answer: FETCH FIRST n ROWS ONLY
FETCH FIRST n ROWS ONLY is the ANSI SQL standard for limiting rows.
Question 5: What does ORDER BY salary DESC, hire_date ASC do?
- Highest salary first, ties broken by earliest hire date (Correct answer)
- Lowest salary first, ties by latest hire
- Sorts only by salary
- Sorts only by hire date
Correct answer: Highest salary first, ties broken by earliest hire date
Primary sort is salary descending; ties are resolved by hire_date ascending.
Question 6: In MySQL, what does LIMIT 5 with no OFFSET return?
- The first 5 rows (Correct answer)
- The last 5 rows
- Rows 5 through 10
- 5 random rows
Correct answer: The first 5 rows
LIMIT 5 returns the first five rows of the result set.
Question 7: Which is valid MySQL shorthand for OFFSET 20 LIMIT 10?
- LIMIT 20, 10 (Correct answer)
- LIMIT 10, 20
- LIMIT 20 OFFSET 10
- OFFSET 20, 10
Correct answer: LIMIT 20, 10
MySQL's two-argument LIMIT takes offset first, then row count.
SQL Sorting and Limiting Results 3
How are NULL values typically sorted in PostgreSQL ascending order by default?