PL SQL Questions HandsOn
Table of Contents
📘 PL/SQL Practice Guide: Functions, Procedures, Triggers⌗
📌 Functions in PL/SQL⌗
🔹 Syntax⌗
CREATE OR REPLACE FUNCTION function_name (
param1 IN data_type,
param2 IN data_type
) RETURN return_data_type IS
BEGIN
-- logic
RETURN some_value;
END;
/
✅ Practice Questions with Solutions⌗
1. Check Even or Odd⌗
CREATE OR REPLACE FUNCTION check_even_odd(n IN NUMBER) RETURN VARCHAR2 IS
BEGIN
IF MOD(n, 2) = 0 THEN
RETURN 'EVEN';
ELSE
RETURN 'ODD';
END IF;
END;
/
2. Get Maximum of Two Numbers⌗
CREATE OR REPLACE FUNCTION get_max(a IN NUMBER, b IN NUMBER) RETURN NUMBER IS
BEGIN
IF a > b THEN
RETURN a;
ELSE
RETURN b;
END IF;
END;
/
3. Count Vowels in a String⌗
CREATE OR REPLACE FUNCTION count_vowels(str IN VARCHAR2) RETURN NUMBER IS
count NUMBER := 0;
ch CHAR;
BEGIN
FOR i IN 1..LENGTH(str) LOOP
ch := LOWER(SUBSTR(str, i, 1));
IF ch IN ('a', 'e', 'i', 'o', 'u') THEN
count := count + 1;
END IF;
END LOOP;
RETURN count;
END;
/
4. Check Palindrome⌗
CREATE OR REPLACE FUNCTION is_palindrome(str IN VARCHAR2) RETURN VARCHAR2 IS
reversed_str VARCHAR2(1000) := '';
BEGIN
FOR i IN REVERSE 1..LENGTH(str) LOOP
reversed_str := reversed_str || SUBSTR(str, i, 1);
END LOOP;
IF LOWER(str) = LOWER(reversed_str) THEN
RETURN 'YES';
ELSE
RETURN 'NO';
END IF;
END;
/
5. Sum of Digits⌗
CREATE OR REPLACE FUNCTION sum_of_digits(n IN NUMBER) RETURN NUMBER IS
sum NUMBER := 0;
digit NUMBER;
num NUMBER := n;
BEGIN
WHILE num > 0 LOOP
digit := MOD(num, 10);
sum := sum + digit;
num := FLOOR(num / 10);
END LOOP;
RETURN sum;
END;
/
🔧 Procedures in PL/SQL⌗
🔹 Syntax⌗
CREATE OR REPLACE PROCEDURE procedure_name (param1 IN/OUT data_type) IS
BEGIN
-- logic
END;
/
✅ Practice Questions with Solutions⌗
1. Procedure to Print Square⌗
CREATE OR REPLACE PROCEDURE print_square(n IN NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Square: ' || (n * n));
END;
/
2. Procedure to Update Employee Salary⌗
CREATE OR REPLACE PROCEDURE increase_salary(empId IN NUMBER) IS
BEGIN
UPDATE employees
SET salary = salary * 1.10
WHERE emp_id = empId;
COMMIT;
END;
/
3. Procedure with OUT Parameter for Factorial⌗
CREATE OR REPLACE PROCEDURE find_factorial(n IN NUMBER, result OUT NUMBER) IS
fact NUMBER := 1;
BEGIN
FOR i IN 1..n LOOP
fact := fact * i;
END LOOP;
result := fact;
END;
/
🔥 Triggers in PL/SQL⌗
🔹 Syntax⌗
CREATE OR REPLACE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name
FOR EACH ROW
BEGIN
-- logic
END;
/
✅ Practice Questions with Solutions⌗
1. BEFORE INSERT Trigger⌗
CREATE OR REPLACE TRIGGER trg_set_created_at
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
:NEW.created_at := SYSDATE;
END;
/
2. AFTER UPDATE Trigger for Salary Audit⌗
CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_audit(emp_id, old_salary, new_salary, change_date)
VALUES(:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/
3. BEFORE DELETE Trigger to Prevent Deletion⌗
CREATE OR REPLACE TRIGGER trg_prevent_delete
BEFORE DELETE ON departments
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Deletion is not allowed on departments table.');
END;
/
📚 Additional Practice Questions⌗
🔧 Functions⌗
🔹 6. Reverse a String⌗
Question: Write a function to reverse a given string.
CREATE OR REPLACE FUNCTION reverse_string(str IN VARCHAR2) RETURN VARCHAR2 IS
reversed_str VARCHAR2(1000) := '';
BEGIN
FOR i IN REVERSE 1..LENGTH(str) LOOP
reversed_str := reversed_str || SUBSTR(str, i, 1);
END LOOP;
RETURN reversed_str;
END;
/
🔹 7. Prime Number Check⌗
Question: Write a function to check if a number is prime. Return ‘YES’ or ‘NO’.
CREATE OR REPLACE FUNCTION is_prime(n IN NUMBER) RETURN VARCHAR2 IS
i NUMBER;
BEGIN
IF n <= 1 THEN
RETURN 'NO';
END IF;
FOR i IN 2..FLOOR(SQRT(n)) LOOP
IF MOD(n, i) = 0 THEN
RETURN 'NO';
END IF;
END LOOP;
RETURN 'YES';
END;
/
🔧 Procedures⌗
🔹 4. Display Employee Details⌗
Question: Write a procedure that takes employee ID and displays employee details.
CREATE OR REPLACE PROCEDURE show_employee(empId IN NUMBER) IS
emp_name employees.emp_name%TYPE;
emp_salary employees.salary%TYPE;
BEGIN
SELECT emp_name, salary INTO emp_name, emp_salary
FROM employees
WHERE emp_id = empId;
DBMS_OUTPUT.PUT_LINE('Name: ' || emp_name || ', Salary: ' || emp_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found.');
END;
/
🔹 5. Delete Department by ID⌗
Question: Write a procedure that deletes a department by ID.
CREATE OR REPLACE PROCEDURE delete_department(deptId IN NUMBER) IS
BEGIN
DELETE FROM departments WHERE dept_id = deptId;
COMMIT;
END;
/
🔥 Triggers⌗
🔹 4. AFTER INSERT Trigger to Log Insertions⌗
Question: Create a trigger that logs new employee entries into a table employee_log.
CREATE TABLE employee_log (
emp_id NUMBER,
emp_name VARCHAR2(100),
log_time DATE
);
Trigger:
CREATE OR REPLACE TRIGGER trg_log_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_log(emp_id, emp_name, log_time)
VALUES(:NEW.emp_id, :NEW.emp_name, SYSDATE);
END;
/
🚀 Intermediate and Hard PL/SQL Questions⌗
🔧 Functions⌗
🔹 8. Count Words in a String⌗
Question: Write a function to count the number of words in a given string (words are separated by spaces).
CREATE OR REPLACE FUNCTION count_words(str IN VARCHAR2) RETURN NUMBER IS
word_count NUMBER := 0;
i NUMBER;
BEGIN
FOR i IN 1..LENGTH(str) LOOP
IF SUBSTR(str, i, 1) = ' ' THEN
word_count := word_count + 1;
END IF;
END LOOP;
RETURN word_count + 1;
END;
/
🔹 9. Find GCD of Two Numbers⌗
Question: Write a function to return the greatest common divisor (GCD) of two integers.
CREATE OR REPLACE FUNCTION find_gcd(a IN NUMBER, b IN NUMBER) RETURN NUMBER IS
BEGIN
WHILE b != 0 LOOP
DECLARE
temp NUMBER := b;
BEGIN
b := MOD(a, b);
a := temp;
END;
END LOOP;
RETURN a;
END;
/
🔧 Procedures⌗
🔹 6. Transfer Funds Between Accounts⌗
Question: Write a procedure that transfers money from one account to another.
CREATE TABLE accounts (
acc_id NUMBER PRIMARY KEY,
acc_name VARCHAR2(100),
balance NUMBER
);
CREATE OR REPLACE PROCEDURE transfer_funds(
from_acc IN NUMBER,
to_acc IN NUMBER,
amount IN NUMBER
) IS
BEGIN
UPDATE accounts SET balance = balance - amount WHERE acc_id = from_acc;
UPDATE accounts SET balance = balance + amount WHERE acc_id = to_acc;
COMMIT;
END;
/
🔹 7. Generate Fibonacci Series⌗
Question: Write a procedure to print the first N terms of the Fibonacci series.
CREATE OR REPLACE PROCEDURE print_fibonacci(n IN NUMBER) IS
a NUMBER := 0;
b NUMBER := 1;
temp NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(a);
DBMS_OUTPUT.PUT_LINE(b);
FOR i IN 3..n LOOP
temp := a + b;
DBMS_OUTPUT.PUT_LINE(temp);
a := b;
b := temp;
END LOOP;
END;
/
🔥 Triggers⌗
🔹 5. Restrict High Salary Update⌗
Question: Create a trigger that prevents updating salary to more than 100000.
CREATE OR REPLACE TRIGGER trg_salary_limit
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary > 100000 THEN
RAISE_APPLICATION_ERROR(-20002, 'Salary cannot exceed 100000');
END IF;
END;
/
🔹 6. Audit All DML Actions⌗
Question: Write a trigger to log all INSERT, UPDATE, and DELETE operations on the employees table.
CREATE TABLE employee_dml_log (
action_type VARCHAR2(10),
emp_id NUMBER,
action_date DATE
);
CREATE OR REPLACE TRIGGER trg_employee_dml_log
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO employee_dml_log VALUES ('INSERT', :NEW.emp_id, SYSDATE);
ELSIF UPDATING THEN
INSERT INTO employee_dml_log VALUES ('UPDATE', :NEW.emp_id, SYSDATE);
ELSIF DELETING THEN
INSERT INTO employee_dml_log VALUES ('DELETE', :OLD.emp_id, SYSDATE);
END IF;
END;
/
FUNCTIONS⌗
Write a PL/pgSQL function named get_product_price that returns the price of a product based on its product ID.⌗
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name VARCHAR(100),
price INTEGER
);
INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Laptop', 80000),
(2, 'Smartphone', 40000),
(3, 'Tablet', 25000),
(4, 'Headphones', 5000);
CREATE OR REPLACE FUNCTION GET_PRODUCT_PRICE(P_ID INTEGER)
RETURNS INTEGER
LANGUAGE PLPGSQL
AS $$
DECLARE
PRODUCT_PRICE INTEGER;
BEGIN
SELECT PRICE INTO PRODUCT_PRICE
FROM PRODUCTS
WHERE PRODUCT_ID = P_ID;
RETURN PRODUCT_PRICE;
END;
$$;
SELECT GET_PRODUCT_PRICE(1)
Write a PL/pgSQL function named update_product_stock that updates the stock quantity of a product given its product ID.⌗
CREATE TABLE products (
product_id NUMERIC PRIMARY KEY,
product_name VARCHAR(100),
price NUMERIC,
stock_quantity NUMERIC
);
INSERT INTO products (product_id, product_name, price, stock_quantity) VALUES
(1, 'Laptop', 80000, 20),
(2, 'Smartphone', 40000, 50),
(3, 'Tablet', 25000, 30);
CREATE OR REPLACE FUNCTION UPDATE_PRODUCT_STOCK(P_ID NUMERIC, UPDATED_STOCK NUMERIC)
RETURNS VOID
LANGUAGE PLPGSQL
AS $$
BEGIN
UPDATE PRODUCTS
SET STOCK_QUANTITY = UPDATED_STOCK
WHERE PRODUCT_ID = P_ID;
END;
$$;
SELECT UPDATE_PRODUCT_STOCK(1,60);
SELECT * FROM PRODUCTS;
Write a PL/pgSQL function named get_total_stock_value that returns the total monetary value of all products in stock.⌗
CREATE TABLE products (
product_id NUMERIC PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL,
stock_quantity NUMERIC
);
INSERT INTO products (product_id, product_name, price, stock_quantity) VALUES
(1, 'Laptop', 80000, 10),
(2, 'Smartphone', 40000, 20),
(3, 'Tablet', 25000, 15);
CREATE OR REPLACE FUNCTION GET_TOTAL_STOCK_VALUE()
RETURNS DECIMAL
LANGUAGE PLPGSQL
AS $$
DECLARE
TOTAL DECIMAL;
BEGIN
SELECT SUM(PRICE * STOCK_QUANTITY) INTO TOTAL
FROM PRODUCTS;
RETURN TOTAL;
END;
$$;
SELECT GET_TOTAL_STOCK_VALUE();
Write a PL/pgSQL function named count_products_by_category that returns the number of products in a specific category.⌗
CREATE TABLE products (
product_id NUMERIC PRIMARY KEY,
product_name VARCHAR(100),
category_id NUMERIC,
price DECIMAL,
stock_quantity NUMERIC
);
INSERT INTO products (product_id, product_name, category_id, price, stock_quantity) VALUES
(1, 'Laptop', 101, 80000, 10),
(2, 'Smartphone', 101, 40000, 20),
(3, 'Tablet', 102, 25000, 15),
(4, 'Monitor', 103, 15000, 5),
(5, 'Keyboard', 103, 2000, 50);
CREATE OR REPLACE FUNCTION count_products_by_category(p_category_id NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
c_count NUMERIC;
BEGIN
SELECT COUNT(*) INTO c_count
FROM products
WHERE category_id = p_category_id;
RETURN c_count;
END;
$$;
SELECT count_products_by_category(101);
Write a PL/pgSQL function get_products_below_price(p INTEGER) that returns a comma-separated string of product names whose price is less than or equal to the given price.⌗
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name VARCHAR(100),
price INTEGER,
stock_quantity INTEGER
);
INSERT INTO products (product_id, product_name, price, stock_quantity) VALUES
(1, 'Laptop', 80000, 10),
(2, 'Smartphone', 40000, 20),
(3, 'Tablet', 25000, 15),
(4, 'Monitor', 15000, 8),
(5, 'Keyboard', 2000, 30),
(6, 'Mouse', 1000, 40);
--SELECT * FROM PRODUCTS;
CREATE OR REPLACE FUNCTION get_products_below_price(P INTEGER)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
P_NAMES TEXT;
BEGIN
SELECT STRING_AGG(PRODUCT_NAME,',' ORDER BY PRODUCT_NAME ASC) INTO P_NAMES
FROM PRODUCTS
WHERE PRICE <= P;
RETURN P_NAMES;
END;
$$;
SELECT get_products_below_price(8000);
Procedure⌗
Write a PL/pgSQL procedure add_new_book(p_title, p_author, p_published_year, p_genre) that inserts a new book into the books table using the provided parameters.⌗
CREATE TABLE books (
book_id SERIAL PRIMARY KEY,
title TEXT,
author TEXT,
published_year INTEGER,
genre TEXT
);
SELECT * FROM BOOKS;
CREATE OR REPLACE PROCEDURE ADD_NEW_BOOK(
P_TITLE TEXT,
P_AUTHOR TEXT,
P_PUBLISHED_YEAR INTEGER,
P_GENRE TEXT
)
LANGUAGE PLPGSQL
AS
$$
BEGIN
INSERT INTO BOOKS(TITLE,AUTHOR,PUBLISHED_YEAR,GENRE)
VALUES(P_TITLE,P_AUTHOR, P_PUBLISHED_YEAR,P_GENRE);
END;
$$;
CALL ADD_NEW_BOOK('GAME OF THRONES','ADITYA',2002,'STRATAGY');
SELECT * FROM BOOKS;
Write a PL/pgSQL procedure loan_book(p_book_id INTEGER, p_member_id INTEGER) that records a book being loaned to a member by inserting a row into a loans table. The current date should be recorded as the loan date, and the return date should initially be NULL.⌗
CREATE TABLE books (
book_id SERIAL PRIMARY KEY,
title TEXT,
author TEXT
);
CREATE TABLE members (
member_id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE loans (
loan_id SERIAL PRIMARY KEY,
book_id INTEGER REFERENCES books(book_id),
member_id INTEGER REFERENCES members(member_id),
loan_date DATE,
return_date DATE
);
CREATE OR REPLACE PROCEDURE loan_book(
p_book_id INTEGER,
p_member_id INTEGER
)
LANGUAGE plpgsql
AS
$$
BEGIN
INSERT INTO loans(book_id, member_id, loan_date, return_date)
VALUES (p_book_id, p_member_id, now()::date, NULL);
END;
$$;
-- Insert sample books
INSERT INTO books (title, author) VALUES
('1984', 'George Orwell'),
('The Alchemist', 'Paulo Coelho');
-- Insert sample members
INSERT INTO members (name) VALUES
('Alice'),
('Bob');
-- Call the procedure to loan a book
CALL loan_book(1, 1); -- Loans book_id=1 to member_id=1
-- Check loan record
SELECT * FROM loans;
Write a PL/pgSQL procedure return_book(p_loan_id INTEGER) that updates the return_date of a loan to a fixed date (‘2025-03-18’).⌗
CREATE TABLE loans (
loan_id SERIAL PRIMARY KEY,
book_id INTEGER REFERENCES books(book_id),
member_id INTEGER REFERENCES members(member_id),
loan_date DATE,
return_date DATE
);
CREATE OR REPLACE PROCEDURE return_book(p_loan_id INTEGER)
LANGUAGE plpgsql
AS
$$
BEGIN
UPDATE loans
SET return_date = '2025-03-18'
WHERE loan_id = p_loan_id;
END;
$$;
CALL return_book(1); -- Set return_date for loan_id = 1
SELECT * FROM loans; -- Check result
Write a PL/pgSQL function get_member_loans(p_member_id INTEGER) that returns a list of books (title and loan date) borrowed by a member.⌗
CREATE OR REPLACE FUNCTION get_member_loans(p_member_id INTEGER)
RETURNS TABLE(title TEXT, loan_date DATE)
LANGUAGE plpgsql
AS
$$
BEGIN
RETURN QUERY
SELECT books.title, loans.loan_date
FROM books
JOIN loans ON loans.book_id = books.book_id
WHERE loans.member_id = p_member_id;
END;
$$;
SELECT * FROM get_member_loans(1); -- Show books loaned by member_id = 1
TRIGGERS⌗
1. Prevent insertion of negative marks using a trigger⌗
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name TEXT,
marks INTEGER
);
CREATE OR REPLACE FUNCTION check_negative_marks()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.marks < 0 THEN
RAISE EXCEPTION 'Marks cannot be negative';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE TRIGGER prevent_negative_marks
BEFORE INSERT OR UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION check_negative_marks();
-- Example:
-- INSERT INTO students(student_id, student_name, marks) VALUES (1, 'ABC', 80); -- Works
-- INSERT INTO students(student_id, student_name, marks) VALUES (2, 'DEF', -10); -- Fails
2. Automatically apply 10% discount after a customer makes a purchase⌗
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name TEXT,
purchase_amount DECIMAL,
discount DECIMAL
);
CREATE OR REPLACE FUNCTION calculate_discount()
RETURNS TRIGGER AS $$
DECLARE
disc DECIMAL;
BEGIN
disc := NEW.purchase_amount * 0.1;
UPDATE customers SET discount = disc WHERE customer_id = NEW.customer_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE TRIGGER apply_discount
AFTER INSERT ON customers
FOR EACH ROW
EXECUTE FUNCTION calculate_discount();
3. Log salary updates using a trigger⌗
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name TEXT,
salary INT
);
CREATE TABLE log_table (
log_message TEXT,
log_timestamp TIMESTAMP
);
CREATE OR REPLACE FUNCTION log_salary_update()
RETURNS TRIGGER AS $$
DECLARE
message TEXT;
BEGIN
message := 'Salary updated for employee ID ' || NEW.emp_id || ': from ' || OLD.salary || ' to ' || NEW.salary;
INSERT INTO log_table(log_message, log_timestamp) VALUES (message, current_timestamp);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE TRIGGER salary_update_trigger
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (NEW.salary <> OLD.salary)
EXECUTE FUNCTION log_salary_update();
4. Prevent price updates on shopping table⌗
CREATE TABLE shopping (
item_id INT PRIMARY KEY,
item_name TEXT,
price DECIMAL
);
CREATE OR REPLACE FUNCTION prevent_price_update()
RETURNS TRIGGER AS $$
BEGIN
RAISE EXCEPTION 'Price updates are not allowed.';
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE TRIGGER prevent_price_update_trigger
AFTER UPDATE ON shopping
FOR EACH ROW
WHEN (NEW.price <> OLD.price)
EXECUTE FUNCTION prevent_price_update();
5. Log booking deletion events using a trigger⌗
CREATE TABLE bookings (
booking_id INT PRIMARY KEY,
customer_name TEXT,
booking_date DATE,
booking_status TEXT
);
CREATE TABLE log_table (
log_message TEXT,
log_timestamp TIMESTAMP DEFAULT current_timestamp
);
CREATE OR REPLACE FUNCTION log_booking_deletion()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO log_table (log_message)
VALUES (
CONCAT('Booking deleted: ID ', OLD.booking_id,
', Customer: ', OLD.customer_name,
', Date: ', OLD.booking_date,
', Status: ', OLD.booking_status)
);
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE TRIGGER booking_deletion_trigger
AFTER DELETE ON bookings
FOR EACH ROW
EXECUTE FUNCTION log_booking_deletion();
6. Create a function to get employee salary⌗
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name TEXT,
salary INTEGER
);
CREATE OR REPLACE FUNCTION get_employee_salary(emp_id INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS
$$
DECLARE
emp_salary INTEGER;
BEGIN
SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
RETURN emp_salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
$$;
-- Example usage:
SELECT get_employee_salary(101);
Read other posts