Cursor + Trigger Assignment

Cursor with Condition

Quesn: Create a cursor that displays employee names whose salary is greater than 50,000 from the Employee table.

-- Creating the table
CREATE TABLE Employee (
  emp_id     NUMBER PRIMARY KEY,
  emp_name   VARCHAR2(100),
  salary     NUMBER
);

-- Initialising the table
BEGIN
  INSERT INTO Employee VALUES (1, 'Alice', 45000);
  INSERT INTO Employee VALUES (2, 'Bob', 52000);
  INSERT INTO Employee VALUES (3, 'Charlie', 60000);
  INSERT INTO Employee VALUES (4, 'Diana', 48000);
  INSERT INTO Employee VALUES (5, 'Ethan', 75000);
  COMMIT;
END;

SELECT * FROM Employee;

-- Creating the cursor and giving output
DECLARE
  CURSOR high_salary_cursor IS
    SELECT emp_name FROM Employee WHERE salary > 50000;

  v_name Employee.emp_name%TYPE;
BEGIN
  FOR emp_rec IN high_salary_cursor LOOP
    DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_rec.emp_name);
  END LOOP;
END;

AFTER INSERT Trigger (Student Table)

Quesn: Whenever a new student is added to the Students table, we want to automatically insert a log entry into the Student_Audit table to keep track of the registration.

-- Creating table Students
CREATE TABLE Students (
  student_id   NUMBER PRIMARY KEY,
  student_name VARCHAR2(100),
  course       VARCHAR2(50)
);

-- Creating  Audit table for tracking registrations
CREATE TABLE Student_Audit (
  audit_id     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  student_id   NUMBER,
  student_name VARCHAR2(100),
  registration_date DATE
);

-- Inserting sample data into students table
BEGIN
  INSERT INTO Students (student_id, student_name, course) VALUES (1, 'Aarav', 'Physics');
  INSERT INTO Students (student_id, student_name, course) VALUES (2, 'Meera', 'Chemistry');
  INSERT INTO Students (student_id, student_name, course) VALUES (3, 'Ravi', 'Mathematics');
  COMMIT;
END;

SELECT * FROM Students;

-- Creating the AFTER INSERT Trigger
CREATE OR REPLACE TRIGGER trg_student_registration
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
  INSERT INTO Student_Audit (student_id, student_name, registration_date)
  VALUES (:NEW.student_id, :NEW.student_name, SYSDATE);
END;

-- Testing the trigger
BEGIN
  INSERT INTO Students (student_id, student_name, course) VALUES (4, 'Lakshmi', 'Biology');
  COMMIT;
END;

-- Viewing the Audit log
SELECT * FROM Student_Audit;