PL/SQL Explicit Cursor: Fetch Books with Price Above 500

Problem - Explicit Cursor

Write a PL/SQL program that uses an explicit cursor to retrieve and display the details of all books from the BOOKS table where the PRICE is greater than 500. The program should display the following information for each qualifying book:

• BOOK_ID
• TITLE
• AUTHOR
• CATEGORY
• PRICE

Expected Output (Sample)

Sample result layout when the PL/SQL program is executed:

BOOK_ID	TITLE	AUTHOR	CATEGORY	PRICE
201	Bangladesher Itihash	Sirajul Islam	History	750
202	Chharar Puthi	Sukumar Ray	Literature	600
203	Amar Bangladesh	Humayun Azad	Essay	850
204	Shesher Kobita	Rabindranath Tagore	Novel	950

PL/SQL Program (Explicit Cursor)

Ensure SET SERVEROUTPUT ON is enabled.

  -- Create BOOKS table
CREATE TABLE BOOKS (
    BOOK_ID    NUMBER(5)      PRIMARY KEY,
    TITLE      VARCHAR2(100)  NOT NULL,
    AUTHOR     VARCHAR2(100),
    CATEGORY   VARCHAR2(50),
    PRICE      NUMBER(8,2)
);

-- Insert demo data
INSERT INTO BOOKS (BOOK_ID, TITLE, AUTHOR, CATEGORY, PRICE) VALUES 
(201, 'Bangladesher Itihash', 'Sirajul Islam', 'History', 750);

INSERT INTO BOOKS (BOOK_ID, TITLE, AUTHOR, CATEGORY, PRICE) VALUES 
(202, 'Chharar Puthi', 'Sukumar Ray', 'Literature', 600);

INSERT INTO BOOKS (BOOK_ID, TITLE, AUTHOR, CATEGORY, PRICE) VALUES 
(203, 'Amar Bangladesh', 'Humayun Azad', 'Essay', 850);

INSERT INTO BOOKS (BOOK_ID, TITLE, AUTHOR, CATEGORY, PRICE) VALUES 
(204, 'Shesher Kobita', 'Rabindranath Tagore', 'Novel', 950);

INSERT INTO BOOKS (BOOK_ID, TITLE, AUTHOR, CATEGORY, PRICE) VALUES 
(205, 'Programming in C', 'E. Balagurusamy', 'Programming', 450); -- won't appear in PL/SQL (price < 500)

COMMIT;

  
SET SERVEROUTPUT ON;

DECLARE
  CURSOR book_cur IS
    SELECT BOOK_ID, TITLE, AUTHOR, CATEGORY, PRICE
    FROM BOOKS
    WHERE PRICE > 500
    ORDER BY BOOK_ID;

  v_book_id   BOOKS.BOOK_ID%TYPE;
  v_title     BOOKS.TITLE%TYPE;
  v_author    BOOKS.AUTHOR%TYPE;
  v_category  BOOKS.CATEGORY%TYPE;
  v_price     BOOKS.PRICE%TYPE;
BEGIN
  OPEN book_cur;
  LOOP
    FETCH book_cur INTO v_book_id, v_title, v_author, v_category, v_price;
    EXIT WHEN book_cur%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('BOOK_ID   : ' || v_book_id);
    DBMS_OUTPUT.PUT_LINE('TITLE     : ' || NVL(v_title, 'NULL'));
    DBMS_OUTPUT.PUT_LINE('AUTHOR    : ' || NVL(v_author, 'NULL'));
    DBMS_OUTPUT.PUT_LINE('CATEGORY  : ' || NVL(v_category, 'NULL'));
    DBMS_OUTPUT.PUT_LINE('PRICE     : ' || v_price);
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');
  END LOOP;

  CLOSE book_cur;
END;
/
  

PL/SQL program using explicit cursor, BOOKS table, Oracle SQL assignment, fetch records price greater than 500, Bangladesher Itihash book, Humayun Azad, Rabindranath Tagore novel, Sirajul Islam history, Sukumar Ray literature, PL SQL cursor example, database programming practice, university exam question