PL/SQL Block Examples [ Version 1]

Some of the Pl/SQL example questions are:-

1. Write a PL/SQL block to accept the marks of three subjects from a student, and calculate their average. If the average <50 then print fail, the Average is between 50 to 60 then the second division, the Average is between 60 to 75 then the first division, and if the average is between 75 and above then print distinction.

Ans:-

DECLARE

    m1 INTEGER := &m1 ;

    m2 INTEGER := &m2 ;

    m3 INTEGER := &m3 ;

    avg INTEGER;

BEGIN

    avg := (m1+m2+m3)/3;

    dbms_output.put_line('Average marks' || avg);

   IF avg < 50 THEN

      dbms_output.put_line(' Fail ');

   ELSIF avg >= 50 and avg < 60 THEN

      dbms_output.put_line(' 2nd Division ');

   ELSIF avg >= 60 and avg < 75 THEN

      dbms_output.put_line(' 1st Division ');

   ELSE

      dbms_output.put_line(' Distinction ');

   END IF;

END;

/

2. Write a PL/SQL block to accept the emp number from the employee table and calculate the tax on salary based on the following:

Join Basic                Tax

Less than 1500            5% of salary

1500 to 2500               7% of salary

2501 to 3500               9% of salary

3501 and above         10% of salary.

Ans:-

create or replace function tax_calculate

          return integer

    as

    tax integer := 0

    sal integer:=0

    begin

          select salary into sal from employee;

        IF sal < 1500 THEN

          tax := (1500 * 5) / 100;

        ELSIF sal < 2500 and sal >=1500 THEN

          tax := (1500 * 7) / 100;

        ELSIF sal < 3500 and sal >=2500 THEN

          tax := (1500 * 9) / 100;

        ELSE

          tax := (1500 * 10) / 100;

        END IF;

        return tax;

     END;

     /

 3. Write a PL/SQL block that accepts department number and output the total salary of that department.

Ans:-

CREATE or REPLACE function getTotalSalary

    return INTEGER

    as

    tot_sal INTEGER(6) := 0

    deptno INTEGER(2)

BEGIN

    SELECT sum(salary) into tot_sal from Dept where deptno := 5

    RETURN tot_sal;

END;

/

 

DECLARE

    total INTEGER(6)

BEGIN

    total := getTotalSalary();

    dbms_output.put_line(' Department Total Salary : ' || total );

END;

/

4. Write a PL/SQL block for inserting a row into a table.

Ans:-

Suppose one row is taken from one table and inserted into another table then:-

BEGIN
    INSERT INTO <table_name>(<columnl>,<column2>,...,<column_n>)
    SELECT <columnl>,<column2>,.. <column_n> FROM <table_name2>;
END;

5. Write a PL/SQL block to display the multiplication tables up to a given number.

Ans:-

DECLARE

    num INTEGER(2) := &num;

    i INTEGER(2) := 1;

BEGIN

    WHILE i<=num LOOP

        dbms_output.put_line(num || ' x ' || i || ' = ' || num*i);

        i := i + 1;

    END LOOP

END;