LIMIT clause behavior in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this query with LIMIT?

Consider a table Employees with columns id and name. It has 5 rows with ids from 1 to 5.
What rows will this query return?

SELECT * FROM Employees ORDER BY id DESC LIMIT 3;

SQL

SELECT * FROM Employees ORDER BY id DESC LIMIT 3;

A Rows with ids 5, 4, 3

B Rows with ids 3, 4, 5

C Rows with ids 1, 2, 3

D Rows with ids 1, 5, 3

💡 Hint

LIMIT returns the first N rows after sorting.

The query orders rows by id descending (5 to 1) and then returns the first 3 rows, which are ids 5, 4, and 3.

🧠 Conceptual

intermediate

What happens if LIMIT is zero?

What will this query return?

SELECT * FROM Employees LIMIT 0;

A Raises a syntax error

B Returns all rows

C Returns one row

D Returns no rows

💡 Hint

Think about what limiting to zero means.

LIMIT 0 means the query returns zero rows, so the result set is empty.

📝 Syntax

advanced

Which query correctly uses LIMIT with OFFSET?

You want to skip the first 2 rows and get the next 3 rows from a table.
Which query is correct?

A SELECT * FROM Employees LIMIT 3 OFFSET 2;

B SELECT * FROM Employees OFFSET 2 LIMIT 3;

C SELECT * FROM Employees LIMIT 2, 3;

D SELECT * FROM Employees LIMIT 3, OFFSET 2;

💡 Hint

Standard SQL uses LIMIT then OFFSET.

The correct syntax is LIMIT followed by OFFSET. Option A uses this correctly.

❓ optimization

advanced

How does LIMIT improve query performance?

You have a large table with millions of rows. You only want to display the first 10 rows.
How does adding LIMIT 10 affect the query execution?

A It increases query time because of extra processing

B It scans the entire table then returns 10 rows

C It stops scanning after finding 10 rows, reducing work

D It causes the database to sort all rows before limiting

💡 Hint

Think about how databases can stop early when limited.

LIMIT allows the database to stop scanning once it finds enough rows, improving speed.

🔧 Debug

expert

Why does this query cause an error?

Given this query:

SELECT * FROM Employees LIMIT -5;

What error will it cause?

A Runtime error due to invalid offset

B Syntax error because LIMIT cannot be negative

C Returns zero rows

D Returns all rows ignoring the negative limit

💡 Hint

LIMIT expects a non-negative integer.

LIMIT with a negative number is invalid syntax and causes an error.