Regular Batch (Database)
Trigger, Sequence, Function
Return square of a number
CREATE OR REPLACE FUNCTION square_num(p_num NUMBER)
RETURN NUMBER IS
BEGIN
RETURN p_num * p_num;
END;
/
Check if number is even or odd
CREATE OR REPLACE FUNCTION even_or_odd(n NUMBER)
RETURN VARCHAR2 IS
BEGIN
IF MOD(n,2)=0 THEN
RETURN 'EVEN';
ELSE
RETURN 'ODD';
END IF;
END;
/
Find factorial of a number
CREATE OR REPLACE FUNCTION factorial(n NUMBER)
RETURN NUMBER IS
fact NUMBER := 1;
BEGIN
FOR i IN 1..n LOOP
fact := fact * i;
END LOOP;
RETURN fact;
Complexity IT Job Care Page 1
Regular Batch (Database)
END;
/
Return grade based on marks
CREATE OR REPLACE FUNCTION get_grade(marks NUMBER)
RETURN VARCHAR2 IS
BEGIN
IF marks >= 80 THEN
RETURN 'A+';
ELSIF marks >= 70 THEN
RETURN 'A';
ELSIF marks >= 60 THEN
RETURN 'B';
ELSE
RETURN 'F';
END IF;
END;
/
Calculate age from birthdate
CREATE OR REPLACE FUNCTION calc_age(dob DATE)
RETURN NUMBER IS
BEGIN
RETURN TRUNC(MONTHS_BETWEEN(SYSDATE, dob)/12);
END;
/
SELECT calc_age(TO_DATE('2000-01-01','YYYY-MM-DD')) FROM dual;
Complexity IT Job Care Page 2
Regular Batch (Database)
Reverse a string
CREATE OR REPLACE FUNCTION reverse_text(p_text VARCHAR2)
RETURN VARCHAR2 IS
result VARCHAR2(200);
BEGIN
FOR i IN REVERSE 1..LENGTH(p_text) LOOP
result := result || SUBSTR(p_text, i, 1);
END LOOP;
RETURN result;
END;
/
Check leap year
CREATE OR REPLACE FUNCTION is_leap_year(y NUMBER)
RETURN VARCHAR2 IS
BEGIN
IF MOD(y,400)=0 OR (MOD(y,4)=0 AND MOD(y,100)<>0) THEN
RETURN 'LEAP YEAR';
ELSE
RETURN 'NOT LEAP YEAR';
END IF;
END;
/
Calculate simple interest
CREATE OR REPLACE FUNCTION simple_interest(p NUMBER, r NUMBER, t NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (p * r * t) / 100;
END;/
Complexity IT Job Care Page 3
Regular Batch (Database)
Return Employee Name with Salary
CREATE OR REPLACE FUNCTION emp_info(p_emp_id NUMBER)
RETURN VARCHAR2 IS
v_name VARCHAR2;
v_salary NUMBER;
BEGIN
SELECT emp_name, basic_salary + NVL(commission,0) + NVL(bonus,0)
INTO v_name, v_salary
FROM employee
WHERE emp_id = p_emp_id;
RETURN 'Employee: ' || v_name || ' | Total Salary: ' || v_salary;
END;
/
Calculate total salary of all employees
CREATE OR REPLACE FUNCTION total_company_salary
RETURN NUMBER IS
v_total NUMBER := 0;
BEGIN
FOR rec IN (SELECT basic_salary + NVL(commission,0) + NVL(bonus,0) AS salary
FROM employee) LOOP
v_total := v_total + [Link];
END LOOP;
RETURN v_total;
END;
/
Complexity IT Job Care Page 4
Regular Batch (Database)
Written Question
1. Write a function that takes an employee ID and returns their total salary from the
EMPLOYEE table.
2. Write a function to find the name of an employee given their ID.
3. Write a function to count the number of employees in the EMPLOYEE table.
4. Write a function that returns the highest salary among all employees.
5. Write a function that returns the average salary of all employees.
6. Write a function that returns “Employee Found” or “Employee Not Found” based on the
ID entered.
7. Write a function to calculate the total bonus of all employees.
8. Write a function to return both name and total salary of an employee in one string.
9. Write a function to calculate the sum of all salaries.
10. Write a function that calculates the number of employees above a given salary limit.
Complexity IT Job Care Page 5