Important PL-SQL Programs Asked in Interview:-
In This Section We will Discuss and Understand Some Important PL-SQL Programs that are most frequently asked in Exam, Viva, and in Interview Also.
PL-SQL is the Procedure Language Extension For Structured Query Language. Suppose If we want to execute a query let suppose 10 times, so writing SQL Statement 10 times will not be a feasible option.
Instead, We can declare SQL Statement in PL-SQL Block using Looping Statement, by doing this we can increase processing speed and decrease the query traffic.
Table Of Contents
- PL-SQL Definition and Block Overview
- Important PL-SQL Programs
- Q1. Write a PL-SQL block to find out if a year is leap year (A leap year is divisible by 4 but not by 100, or it is divisible by 400).
- Q2. Write a PL-SQL Program block to Input a number with a substitution variable, and then print its multiplication table using a while loop.
- Q3. Write a PL-SQL block to print all odd numbers between 1 and 10 using a basic loop.
- Q4. Using a for loop, print the value 10 to 1 in reverse order.
- Q5. Write a PL-SQL program to swap the values of two variables. Print the variables before and after swapping.
- Q6. An employee no is entered from the keyboard, write a PL-SQL program to find empno, ename, deptno, sal from emp table. Raise suitable exception, if employee no does not exist.
- Q8. Write a PL_SQL program to compute employee name with fourth largest salary.
- Q9. Write a PL-SQL block to ask a user to input an employee Id. Retrieve the employee’s name, Sal & commission. Print the name and sum of salary and commission. Also write exception, if employee Id is invalid.
- Q10. Write PL-SQL program to compute the highest salary in the EMP table, also print the name of employee earning highest salary.
- Q11. Write a PL-SQL block to find greatest among three given numbers.
- Related Articles:
PL-SQL Definition and Block Overview
Definition: PL-SQL is Structured Language whose code is well organized in Blocks. We have three blocks in PL-SQL i.e : Declaration, Execution, and Exception Handling.
1. Declaration Section
This is the Section in PL-SQL where we declare variables, Define datatype and allocate memory for cursor.
2. Execution Section
An executable block starts with the BEGIN keyword and end with End Keyword. In this section we write our programming logic and it must have at least one executable statement.
3. Exception- Handling Section
This Section start with Exception Keyword, In Exception Handling we catch and handle exception that occurs in execution b
Difference Between SQL and PL-SQL?
| SQL | PL-SQL |
|---|---|
| It Stands for Structured Query Language. | It stands for Procedural Extension Language for SQL. |
| It Executes a single command or a query at a time. | Executes a complete Block at the same time. |
| Control Structures are not available. | Control Structures are available- Like For, While Loop. |
| SQL is a Declarative Language. | PL-SQL is a Procedural Language. |
| It Directly Interacts with the database server. | It doesn’t interact directly with the database server. |
| It is used to Write queries, DDL, and DML Statements. | With the Help of PL_SQL we can write Program blocks, functions, and procedures triggers. |
Important PL-SQL Programs
Q1. Write a PL-SQL block to find out if a year is leap year (A leap year is divisible by 4 but not by 100, or it is divisible by 400).
DECLARE
year number(4):=&year;
BEGIN
if(mod(year,100)=0) then
if(mod(year,400)=0) then
dbms_output.put_line('Leap year');
else
dbms_output.put_line('Non Leap year');
end if;
elsif(mod(year,4)=0) then
dbms_output.put_line('Leap year');
else
dbms_output.put_line('Non Leap Year');
end if;
end;
/Enter value for year: 1800 old 2: year number(4):=&year; new 2: year number(4):=1800; Non Leap year
Q2. Write a PL-SQL Program block to Input a number with a substitution variable, and then print its multiplication table using a while loop.
DECLARE
n number(2):=&n;
i integer(2):=1;
BEGIN
while i < 11 LOOP
dbms_output.put_line(i || 'x'|| n || '=' || i*n);
i:=i+1;
END LOOP;
END;
/Enter value for n: 9 old 2: n number(2):=n; new 2: n number(2):=9; 1x9=9 2x9=18 3x9=27 4x9=36 5x9=45 6x9=54 7x9=63 8x9=72 9x9=81 10x9=90
Q3. Write a PL-SQL block to print all odd numbers between 1 and 10 using a basic loop.
DECLARE
i integer(2):=1;
BEGIN
LOOP
dbms_output.put_line(i);
i:=i+2;
if(i > 10) then
exit;
end if;
END LOOP;
END;
/Q4. Using a for loop, print the value 10 to 1 in reverse order.
DECLARE
i number(2):=10;
BEGIN
for i in reverse 1 .. 10 LOOP
dbms_output.put_line(i);
END LOOP;
END;
/Q5. Write a PL-SQL program to swap the values of two variables. Print the variables before and after swapping.
DECLARE
a number:=&a;
b number:=&b;
temp number;
BEGIN
dbms_output.put_line('Value of a before swapping ' || a);
dbms_output.put_line('Value of b before swapping ' || b);
temp:=a;
a:=b;
b:=temp;
dbms_output.put_line('Value of a after swapping ' || a);
dbms_output.put_line('Value of b after swapping ' || b);
END;
/Enter value for a: 6 old 2: a number:=&a; new 2: a number:=6; Enter value for b: 8 old 3: b number:=&b; new 3: b number:=8; Value of a before swapping 6 Value of b before swapping 8 Value of a after swapping 8 Value of b after swapping 6
SCOTT/TIGER SCHEMA
Q6. An employee no is entered from the keyboard, write a PL-SQL program to find empno, ename, deptno, sal from emp table. Raise suitable exception, if employee no does not exist.
set serveroutput on;
DECLARE
v_empno number(4):= &v_empno;
v_ename varchar2(10);
v_sal number(7,2);
v_deptno number(2);
BEGIN
select empno,ename,sal,deptno
into v_empno,v_ename,v_sal,v_deptno
from emp
where empno=v_empno;
dbms_output.put_line('Employee Details:' ||v_empno ||' '|| v_ename ||' '|| v_sal || ' '||v_deptno);
EXCEPTION
when no_data_found then
dbms_output.put_line('Employee doesnt exist!!!!');
END;
/Enter value for v_empno: 7902 old 2: v_empno number(4):= &v_empno; new 2: v_empno number(4):= 7902; Employee Details:7902 FORD 3000 20
Q7. An employee name is entered from keyboard; Write a PL-SQL program to find grade of an employee in emp relation based on employee salary.
- If sal>3000$ then the grade is A
- If sal>2000$ then the grade is B
- sal >1000$ then the grade is C
- Otherwise grade is D
DECLARE
v_empno number(4);
v_ename varchar2(10) := &v_ename;
v_sal number(7,2);
v_deptno number(2);
BEGIN
select empno,ename,sal,deptno
into v_empno,v_ename,v_sal,v_deptno
from emp
where upper(ename)=upper(v_ename);
dbms_output.put_line('Employee Details:' ||v_empno ||' '|| v_ename ||' '|| v_sal || ' '||v_deptno);
if( v_sal > 3000) then
dbms_output.put_line('GRADE A');
ELSIF ( v_sal > 2000) then
dbms_output.put_line('GRADE B');
ELSIF ( v_sal > 1000) then
dbms_output.put_line('GRADE C');
else
dbms_output.put_line('GRADE D');
end if;
EXCEPTION
when no_data_found then
dbms_output.put_line('Employee doesnt exist!!!!');
END;
/
Q8. Write a PL_SQL program to compute employee name with fourth largest salary.
DECLARE
v_ename varchar2(10);
v_sal number(7,2);
BEGIN
select ename,sal
into v_ename,v_sal
from emp e1
where 3=(select count(distinct sal) from emp e2
where e2.sal > e1.sal );
dbms_output.put_line(v_ename);
EXCEPTION
when no_data_found then
dbms_output.put_line('Employee doesnt exist!!!!');
END;
/
op
BLAKEQ9. Write a PL-SQL block to ask a user to input an employee Id. Retrieve the employee’s name, Sal & commission. Print the name and sum of salary and commission. Also write exception, if employee Id is invalid.
DECLARE
v_ename varchar2(10);
v_empno number(4):=&v_empno;
v_sal number(7,2);
v_comm number(7,2);
BEGIN
select ename,sal,comm
into v_ename,v_sal,v_comm
from emp
where empno=v_empno;
dbms_output.put_line(v_ename || v_sal+v_comm);
EXCEPTION
when no_data_found then
dbms_output.put_line('Employee ID doesnt exist!!!!');
END;
/Q10. Write PL-SQL program to compute the highest salary in the EMP table, also print the name of employee earning highest salary.
DECLARE
v_sal number(7,2);
v_ename varchar2(10);
BEGIN
select ename,sal
into v_ename,v_sal
from emp
where sal=(select max(sal) from emp);
dbms_output.put_line(v_ename ||' '|| v_sal);
EXCEPTION
when no_data_found then
dbms_output.put_line('Employee ID doesnt exist!!!!');
END;
/
KING 5000 Q11. Write a PL-SQL block to find greatest among three given numbers.
DECLARE
v_num1 number:=&v_num1;
v_num2 number:=&v_num2;
v_num3 number:=&v_num3;
BEGIN
IF((v_num1>v_num2) and (v_num1>v_num3)) then
dbms_output.put_line('NUMBER 1 MAX: ' || v_num1);
ELSIF ( (v_num2>v_num1) and (v_num2>v_num3)) then
dbms_output.put_line('NUMBER 2 MAX: ' || v_num2);
ELSE
dbms_output.put_line('NUMBER 3 MAX: ' || v_num3);
END IF;
END;
/Enter value for v_num1: 7 old 2: v_num1 number:=&v_num1; new 2: v_num1 number:=7; Enter value for v_num2: 4 old 3: v_num2 number:=&v_num2; new 3: v_num2 number:=4; Enter value for v_num3: 3 old 4: v_num3 number:=&v_num3; new 4: v_num3 number:=3; NUMBER 1 MAX: 7
Related Articles:
- Sum of all Array Elements Program
- Submit Articles on Noteshacker
- Design Patterns Interview Questions | Java
- 50 Top Networking Interview Questions and Answers
- HTTP POST vs. HTTP PUT | Key Differences
- SQL Tutorial Summary Notes
- Resignation Letter | How to write one?
- SQL Queries on emp table
- Linux Commands Tutorial | Complete Guide
- ArrayList vs Vector in Java Collection Framework