CURSOR concept and usage in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a CURSOR in SQL?

A CURSOR is a database object used to retrieve, manipulate, and navigate through rows returned by a query one at a time.

beginner

Why do we use a CURSOR instead of a simple SELECT statement?

We use a CURSOR when we need to process query results row by row, especially when each row requires individual handling or complex logic.

intermediate

Name the main steps to use a CURSOR in SQL.

1. Declare the CURSOR with a SELECT query.<br>2. Open the CURSOR.<br>3. Fetch rows one by one.<br>4. Process each row.<br>5. Close the CURSOR.<br>6. Deallocate the CURSOR.

intermediate

What happens if you forget to CLOSE or DEALLOCATE a CURSOR?

Not closing or deallocating a CURSOR can cause memory leaks and lock resources, slowing down or blocking the database.

beginner

Give a simple example of DECLARE and OPEN a CURSOR in SQL.

DECLARE myCursor CURSOR FOR SELECT id, name FROM employees;<br>OPEN myCursor;

What is the first step when using a CURSOR in SQL?

A Declare the CURSOR

B Fetch data

C Close the CURSOR

D Deallocate the CURSOR

You must first declare the CURSOR with a SELECT query before using it.

Which SQL command moves the CURSOR to the next row?

A OPEN

B CLOSE

C FETCH

D DECLARE

FETCH retrieves the next row from the CURSOR.

What is the purpose of closing a CURSOR?

A To start reading rows

B To fetch the first row

C To declare the CURSOR

D To free resources and release locks

Closing a CURSOR frees up resources and releases any locks held.

Which of these is NOT a typical CURSOR operation?

A OPEN

B INSERT

C DECLARE

D FETCH

INSERT is a data modification command, not a CURSOR operation.

When should you consider using a CURSOR?

A When you need to process rows one by one

B When you want to process all rows at once

C When you want to delete a table

D When creating a new database

CURSORs are useful when processing rows individually.

Explain the main steps to use a CURSOR in SQL and why each step is important.

Think about how you read a book page by page and then close it when done.

Describe a real-life situation where using a CURSOR would be helpful in a database.

Imagine you want to greet each friend by name one at a time.