FETCH FIRST for SQL standard pagination in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL clause FETCH FIRST n ROWS ONLY do?

It limits the query result to return only the first n rows from the result set.

intermediate

How is FETCH FIRST different from LIMIT in PostgreSQL?

FETCH FIRST is part of the SQL standard for pagination, while LIMIT is PostgreSQL-specific. Both limit rows returned, but FETCH FIRST improves portability across databases.

beginner

Write a SQL query to get the first 5 rows from a table named employees using FETCH FIRST.

SELECT * FROM employees FETCH FIRST 5 ROWS ONLY;

intermediate

Can FETCH FIRST be combined with OFFSET for pagination? How?

Yes. Use OFFSET to skip rows, then FETCH FIRST to limit rows returned. Example: SELECT * FROM table OFFSET 10 FETCH FIRST 5 ROWS ONLY; fetches rows 11 to 15.

beginner

Is ROWS keyword mandatory in FETCH FIRST n ROWS ONLY?

No, ROWS is optional. You can write FETCH FIRST 5 ROWS ONLY or FETCH FIRST 5 ONLY. Both work the same.

What does FETCH FIRST 10 ROWS ONLY do in a SQL query?

A Skips the first 10 rows

B Returns all rows except the first 10

C Returns the first 10 rows of the result set

D Deletes the first 10 rows

The clause limits the output to only the first 10 rows.

Which clause is used to skip rows before fetching in SQL pagination?

A OFFSET

B LIMIT

C WHERE

D FETCH FIRST

OFFSET skips a specified number of rows before returning results.

Is FETCH FIRST part of the SQL standard or PostgreSQL-specific?

A Oracle-specific

B PostgreSQL-specific

C MySQL-specific

D SQL standard

FETCH FIRST is part of the SQL standard for pagination.

Which of these is a valid way to limit rows using FETCH FIRST?

A FETCH FIRST ONLY 5 ROWS

B FETCH FIRST 5 ROWS ONLY

C FETCH 5 ROWS FIRST ONLY

D FIRST FETCH 5 ROWS ONLY

The correct syntax is FETCH FIRST 5 ROWS ONLY.

How would you fetch rows 21 to 30 from a table using OFFSET and FETCH FIRST?

A OFFSET 20 FETCH FIRST 10 ROWS ONLY

B OFFSET 10 FETCH FIRST 20 ROWS ONLY

C FETCH FIRST 10 ROWS ONLY OFFSET 20

D LIMIT 10 OFFSET 20

Skip 20 rows, then fetch next 10 rows to get rows 21 to 30.

Explain how to use FETCH FIRST and OFFSET together for pagination in SQL.

Think about skipping some rows and then taking a limited number.

Describe the benefits of using FETCH FIRST over database-specific clauses like LIMIT.

Consider compatibility and standards.