SQL Sorting and Limiting Results 2 — Questions and Answers
Question 1: By default, in which direction does ORDER BY sort results?
- Ascending (ASC) (Correct answer)
- Descending (DESC)
- Random order
- Insertion order
Correct answer: Ascending (ASC)
ORDER BY sorts in ascending order by default unless DESC is specified.
Question 2: Which clause limits the number of rows returned in standard MySQL/PostgreSQL?
- LIMIT (Correct answer)
- TOP
- ROWNUM
- FETCH ONLY
Correct answer: LIMIT
LIMIT restricts the number of rows returned in MySQL and PostgreSQL.
Question 3: How do you sort by 'last_name' ascending and then 'first_name' descending?
- ORDER BY last_name ASC, first_name DESC (Correct answer)
- ORDER BY last_name, first_name
- ORDER BY last_name DESC, first_name
- SORT BY last_name, first_name DESC
Correct answer: ORDER BY last_name ASC, first_name DESC
Each column in ORDER BY can have its own sort direction.
Question 4: In SQL Server, which keyword limits the number of rows returned?
- TOP (Correct answer)
- LIMIT
- FIRST
- MAX
Correct answer: TOP
SQL Server uses TOP n to restrict the number of returned rows.
Question 5: What does ORDER BY 2 mean in a query?
- Sort by the second column in the SELECT list (Correct answer)
- Sort by a column named '2'
- Return only 2 rows
- Sort the second table
Correct answer: Sort by the second column in the SELECT list
A number in ORDER BY refers to the position of a column in the SELECT list.
Question 6: Which query returns rows 11–20 in PostgreSQL?
- LIMIT 10 OFFSET 10 (Correct answer)
- LIMIT 10 SKIP 10
- LIMIT 11 TO 20
- OFFSET 20 LIMIT 10
Correct answer: LIMIT 10 OFFSET 10
OFFSET 10 skips the first 10 rows and LIMIT 10 returns the next 10.
Question 7: Without an ORDER BY clause, the order of returned rows is:
- Not guaranteed (Correct answer)
- Always by primary key
- Always insertion order
- Always alphabetical
Correct answer: Not guaranteed
Row order is undefined unless ORDER BY is used.
SQL Sorting and Limiting Results 2
By default, in which direction does ORDER BY sort results?