Cursor — SQL Technical Interview Questions & Answers

Cursor

Cursor

  • A cursor walks a SELECT row by row inside a stored program. DECLARE cur CURSOR FOR SELECT …; OPEN; FETCH; CLOSE. Use it when set-based SQL cannot express the loop. Most student problems should stay set-based: UPDATE … WHERE, not a cursor.
  • Handlers needed for NOT FOUND. Easy to write slow row-by-row logic.
  • Trap: cursor as the first answer for ‘print all students’ — that’s just SELECT.
  • On the example next to this theory: Cursor: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Technical Interview Tip: Cursors are for procedures. Prefer UPDATE/SELECT sets.

Code Examples for Cursor

Cursor — sample query

-- Cursor
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Cursor: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

How to Explain Cursor to an Interviewer

When a cursor?

Row-by-row in a stored program Not for a simple SELECT