PLSQL Advanced Interview Questions

1.What is query to Find Second Highest Salary Of Employee? | Find 2nd Highest salary examples

Select distinct Salary from Employee e1 where 2=Select count(distinct Salary) from Employee e2 where e1.salary<=e2.salary;

Alternative:

select min(salary)from(select distinct salary from emp order by salary desc)where rownum<=2;

2.Query to find Duplicate Records in Table?

Select * from Employee a where rowid <>( select max(rowid) from Employee b where a.Employee_num=b.Employee_num);

3.Query to Find Monthly Salary of Employee if Annual salary is Given?

select Employee_name,Salary/12 as ‘Monthly Salary’ from employee;

4.What is the Query to fetch first record from Employee table?

Select * from Employee where rownum=1;

5.What is Query to display first 5 Records from Employee table?(90% asked Complex SQL Queries Examples)

Answer:

Select * from Employee where Rownum <= 5;

6.What is Query to display last 5 Records from Employee table?(90% asked Complex SQL Queries Examples)

Answer:

Select * from Employee e where rownum <=5
union
select * from (Select * from Employee e order by rowid desc) where rownum <=5;

7.What is Query to display Nth Record from Employee table?

Answer :select * from ( select a.*, rownum rnum from ( YOUR_QUERY_GOES_HERE — including the order by ) a where rownum <= N_ROWS ) where rnum >= N_ROWS

8.How to get 3 Highest salaries records from Employee table?

Answer: select distinct salary from employee a where 3 >= (select count(distinct salary) from employee b where a.salary <= b.salary) order by a.salary desc;

Alternative solution:

select min(salary)from(select distinct salary from emp order by salary desc)where rownum<=3;

How to Display ODD rows in Employee table?(90% asked Complex SQL Queries Examples)

Answer: Select * from(Select rownum as rno,E.* from Employee E) where Mod(rno,2)=1;

10.How to Display Even rows in Employee table?

Answer: Select * from(Select rownum as rno,E.* from Employee) where Mod(rno,2)=0;

11.How to fetch 3rd highest salary using Rank Function?

Answer: select * from (Select Dense_Rank() over ( order by salary desc) as Rnk,E.* from Employee E) where Rnk=3;

12.How Can i create table with same structure of Employee table?(90% asked Complex SQL Queries Examples)

Answer: Create table Employee_1 as Select * from Employee where 1=2;

13.Display first 50% records from Employee table?

Answer: select rownum, e.* from emp e where rownum<=(select count(*)/2 from emp);

14.Display last 50% records from Employee table?

Answer:

Select rownum,E.* from Employee E
minus
Select rownum,E.* from Employee E where rownum<=(Select count(*)/2) from Employee);

15.How Can i create table with same structure with data of Employee table?

Answer: Create table Employee1 as select * from Employee;

16.How do i fetch only common records between 2 tables.

Answer:

Select * from Employee;
Intersect
Select * from Employee1;

17.Find Query to get information of Employee where Employee is not assigned to the department

Answer: Select * from Employee where Dept_no Not in(Select Department_no from Department);

18.How to get distinct records from the table without using distinct keyword.

Answer:

select * from Employee a where rowid = (select max(rowid) from Employee b where a.Employee_no=b.Employee_no);

19.Select all records from Employee table whose name is ‘Amit’ and ‘Pradnya’

Answer: Select * from Employee where Name in(‘Amit’,’Pradnya’);

20.Select all records from Employee table where name not in ‘Amit’ and ‘Pradnya’

Answer: select * from Employee where name Not in (‘Amit’,’Pradnya’);

21.how to write sql query for the below scenario
I/p:ORACLE

O/p:
O
R
A
C
L
E
i.e, splitting into multiple columns a string using sql.

Answer:

Select Substr(‘ORACLE’,Level,1) From Dual
Connect By Level<= Length(‘ORACLE’);

22.How to fetch all the records from Employee whose joining year is 2017?

Answer:

Oracle:

select * from Employee where To_char(Joining_date,’YYYY’)=’2017′;

MS SQL:

select * from Employee where substr(convert(varchar,Joining_date,103),7,4)=’2017′;

23.What is SQL Query to find maximum salary of each department?

Answer:

Select Dept_id,max(salary) from Employee group by Dept_id;

24.How Do you find all Employees with its managers?(Consider there is manager id also in Employee table)

Answer:

Select e.employee_name,m.employee name from Employee e,Employee m where e.Employee_id=m.Manager_id;

25.Display the name of employees who have joined in 2016 and salary is greater than 10000?

Answer:

Select name from Employee where Hire_Date like ‘2016%’ and salary>10000;

26.How to display following using query?

***

Answer:

We cannot use dual table to display output given above. To display output use any table. I am using Student table.

SELECT lpad (‘*’, ROWNUM,’*’) FROM Student WHERE ROWNUM <4;

27.How to add the email validation using only one query?

Answer :

28.How to display 1 to 100 Numbers with query?

Answer:

Select level from dual connect by level <=100;

29.How to remove duplicate rows from table?(100% asked in Complex SQL Queries for Interviews)

Answer:

First Step: Selecting Duplicate rows from table

Select rollno FROM Student WHERE ROWID <>

(Select max (rowid) from Student b where rollno=b.rollno);

Step 2: Delete duplicate rows

Delete FROM Student WHERE ROWID <>
(Select max (rowid) from Student b where rollno=b.rollno);

30.How to find count of duplicate rows? (95% asked in SQL queries for Interviews )

Answer:

Select rollno, count (rollno) from Student
Group by rollno
Having count (rollno)>1
Order by count (rollno) desc;

31.How to Find the Joining date of Employee in YYYY-DAY-Date format.

Select FIRST_NAME, to_char(joining_date,’YYYY’) JoinYear , to_char(joining_date,’Mon’), to_char(joining_date,’dd’) from EMPLOYEES;

Q41) What is the difference between Simple and Complex Views?

Ans: The main differences between the two views are:

Simple Views Vs Complex Views
Simple View	Complex View
Derives data from only one table.	Derives data from many tables.
Contains no functions or group of data	Contain functions or groups of data.
Can perform DML operations through the view.	Does not always allow DML operations through the view.

Q42) What are the restrictions of DML operations on Views?

Ans: Few restrictions of DML operations on Views are:

You cannot DELETE a row if the View contains the following:

Group Functions
A Group By clause
The Distinct Keyword
The Pseudo column ROWNUM Keyword.

You cannot MODIFY data in a View if it contains the following:

Group Functions
A Group By clause
The Distinct Keyword
The Pseudo column ROWNUM Keyword.
Columns defined by expressions (Ex; Salary * 12)

You cannot INSERT data through a view if it contains the following:

Q43) What is PL/SQL, Why do we need PL/SQL instead of SQL, Describe your experience working with PLSQL and What are the difficulties faced while working with PL SQL and How did you overcome them?

PL/SQL is a procedural language extension with SQL Language.
Oracle introduced PL/SQL
It is a combination of SQL and Procedural Statements and used for creating applications.
Basically, PL/SQL is a block-structured programming language whenever we are submitting PL/SQL
Blocks then all SQL statements are executing separately by using SQL engine and also all procedure statements are executed separately.
Explain your current and previous projects along with your roles and responsibilities, mention some of the challenging difficulties you’ve faced in your project while working with PL/SQL.

Q44) What are the different functionalities of a Trigger?

Ans: Trigger is also the same as stored procedure & also it will automatically be invoked whenever DML operation performed against table or view.

There are two types of triggers supported by PL/SQL

Statement Level Trigger.
Row Level Trigger

Statement Level Trigger: In statement-level trigger, the trigger body is executed only once for the DML statement.

Row Level Trigger: In row-level trigger, the trigger body is executed for each row DML statement. It is the reason, we are employing each row clause and internally stored DML transaction in trigger specification, these qualifiers: old, new, are also called records type variables.
These qualifiers are used in trigger specification & trigger body.

Syntax:
:old.column_name
Syntax:
:new column_name

When we use these qualifiers in trigger specification then we are not allowed to use “:” in form of the names of the qualifiers.

Q45) Write a PL/SQL Program that raises a user-defined exception on Thursday?

Ans:

declare
a exception
begin
If to_char(sysdate, ‘DY)=’THU’
then
raise a;
end if;
exception
when a then
dbms_output.put_line(‘my exception raised on thursday’);
end
;

Output: my exception raised on Thursday

Q46) Write a PL/SQL program to retrieve the emp table and then display the salary?

Ans:

declare
v_sal number(10);
begin select max(sal)intr v_sal;
from emp;
dbms_output.put_line(v.sal);
end;
/

(or)

declare
A number(10);
B number(10);
C number(10);
begin
a:=70;
b:=30;
c:=greatest+(a,b);
dbms_output.put_line(c);
end;
/

Output:70

Q47) Write a PL/SQL cursor program which is used to calculate total salary from emp table without using sum() function?

Ans:

Declare
cursor c1 is select sal from emp;
v_sal number(10);
n.number(10):=0;
begin
open c1;
loop
fetch c1 into v_sal;
exit when c1%not found;
n:=n+v_sal;
end loop;
dbms_output.put_line(‘tool salary is’||’ ‘ ||n);
close c1;
end;
/
Output: total salary is: 36975

Q48) Write a PL/SQL cursor program to display all employee names and their salary from the emp table by using % not found attributes?

Ans:

Declare
Cursor c1 is select ename, sal from emp;
v_ename varchar2(10);
v_sal number(10);
begin
open c1;
loop
fetch c1 into v_ename, v_sal;
exist when c1 % notfound;
dbms_output.put_line(v_name ||’ ‘||v_sal);
end loop;
close c1;
end;
/

Q49) What is a Mutating Trigger?

Ans:

Into a row-level trigger based on a table, the trigger body cannot read data from the same table and also we cannot perform DML operation on the same table.
If we are trying this oracle server returns mutating error oracle-4091: table is mutating.
This error is called a mutating error, and this trigger is called a mutating trigger, and the table is called a mutating table.
Mutating errors are not occurred in statement-level trigger because through this statement-level trigger when we are performing DML operations automatically data committed into the database, whereas in the row-level trigger when we are performing transaction data is not committed and also again we are reading this data from the same table then only mutating errors is occurred.

Q50) What is Triggering Events (or) Trigger Predicate Clauses?

Ans: If we want to perform multiple operations in different tables then we must use triggering events within the trigger body. These are inserting, updating, deleting clauses. These clauses are used in the statement, row-level triggers. These triggers are also called as trigger predicate clauses.

Syntax:

If inserting then stmts;
else if updating then stmts;
else if deleting then stmts;
end if;

Q51) What is the Discard File?

Ans:

This file extension is .dsc
Discard file we must specify within the control file by using the discard file clause.
The discard file also stores reflected records based on when clause condition within the control file. This condition must be satisfied in the table clause.

Q52) What is REF CURSOR (or) CURSOR VARIABLE (or) DYNAMIC CURSOR?

Ans:

Oracle 7.2 introduced ref cursor, This is a user-defined type that is used to process multiple records and also this is a record by record process.

In static cursor database servers execute only one select statement at a time for a single active set area wherein ref cursor database servers execute a number of select statements dynamically for a single active set area that’s why those cursors are also called a dynamical cursor.

Generally, we are not allowed to pass static cursor as parameters to use subprograms whereas we can also pass ref cursor as a parameter to the subprograms because basically precursor is a user-defined type in oracle we can also pass all user-defined type as a parameter to the subprograms.

Generally, the static cursor does not return multiple records into the client application whereas the ref cursor is allowed to return multiple records into the client application (Java, .Net, php, VB, C++).

This is a user-defined type so we are creating it in 2 steps process i.e first we are creating a type then only we are creating a variable from that type that’s why this is also called a cursor variable.

Q53) What are The Types of Ref Cursors?

Ans: In all databases having 2 ref cursors.

Strong ref cursor
Weak ref cursor

A strong ref cursor is a ref cursor that has a return type, whereas a weak ref cursor has no return type.

Syntax:

Type typename is ref cursor return record type data type;
Variable Name typename

Syntax

Type typename is ref cursor
Variable Name typename;

In the Weak ref cursor, we must specify a select statement by using open for clause this clause is used in the executable section of the PL/SQL block.

Syntax:

1	`Open ref cursor varname` `for` `SELECT * FROM table_name condition;`

Q54) What is the Difference Between the trim, delete collection methods?

Ans:

SQL> declare           
type t1 is table of number(10);           
v_t t1;=t1(10,20,30,40,50,60);           
beign           
v_t.trim(2);           
dbms_output.put_line(‘after deleting last two elements’);            
for i in v_t.first.. V_t.last           
 loop           
 dbms_output.put_line(v_t(i));            
End loop;            
vt.delete(2);           
 dbms_output.put_line(‘after deleting second element;);            
for i in v_t.first..v_t.last            
loop  
          
If v_t.exists(i) then            
dbms_output.put_line(v_t(i));            
end if;            
end loop;            
end;             
/

Q55) What is Overloading Procedures?

Ans: Overload refers to the same name that can be used for a different purpose, in oracle we can also implement an overloading procedure through the package. Overloading procedure having the same name with different types or different numbers of parameters.

Q56) What are the Global Variables?

Ans: In oracle, we are declaring global variables in Package Specification only.

Q57) What is Forward Declaration?

Ans: In oracle declaring procedures within the package body are called forward declaring generally before we are calling private procedures into public procedure first we must implement private procedure within body otherwise use a forward declaration within the package body.

Q58) What is Invalid_number, Value_Error?

Ans: In oracle when we try to convert “string type to number type” or” data string into data type” then the oracle server returns two types of errors.
1. Invalid. number
2. Value_error (or) numeric_error

a) Invalid_number:

When PL/SQL block has a SQL statement and also those SQL statements try to convert string type to number type or data string into data type then oracle server returns an error: ora-1722-Invalid Number

For handling this error oracle provides number exception Invalid_number exception name.

Example:

begin
Insert
 intoemp(empno, ename, sal) 
values(1,’gokul’, ‘abc’)
exception when invalid_number then dbms_output.put_line(‘insert proper data only’);
end;/

b)value_error
Whenever PL/SQL block having procedural statements and also those statements find to convert string type to number type then oracle servers return an error: ora-6502: numeric or value error: character to a number conversion error
For handling, this error oracle provided exception value_error exception name

Example:

begin
declare z number(10);
begin
z:= ‘&x’ + ‘&y’;
dbms_output.put_line(z);
exception when value_error then dbms_output.put_line(‘enter numeric data value for x & y only’);
end;/

Output:

Enter value for x:3
Enter value for y:2
z:=5
Enter value for x:a
Enter value for y:b
Error:enter numeric data value for x & y only.

Q59) What is Flashback Query?

Ans:

Flashback query is handle by the Database Administrator only flashback queries along allows the content of the table to be retrieved with reference to the specific point of time by using as of clause that is flashback queries retrieves accidental data after committing the transaction also.
Flashback queries generally use undo file that is flashback queries retrieve old data before committing the transaction oracle to provide two methods for flashback queries

Method1: using the timestamp
Method2: using scn number

Q60) Explain what PL/SQL package consists of?

Ans: PL/SQL consists of two major parts, they are: package specification and package body.

Package specification: it acts as a public interface for your application which includes procedures, types, etc.

Package Body: It contains the code required to implement the Package Specification

Q61) Explain what the benefits of PL/SQL Packages are?

Ans: These are the benefits of PL/SQL Packages

We can store functions and procedures in a single unit called a package.
Packages provide security to grant privileges.
Functions and procedures, within the package, shares a common variable among them.
Packages support even if the functions are overloaded.
Packages enhance the performance even when multiple objects loaded into memory.

Q62) explain different methods to trace the PL/SQL code?

Ans: Tracing code is a necessary technique to test the performance of the code during runtime. We have different methods in PL/SQL to trace the code, which are,

DBMS_ TRACE
DBMS_ APPLICATION_INFO
Tkproof utilities and trcsess
DBMS_SESSION and DBMS_MONITOR

Q63) What does it mean by PL/SQL Cursors?

Ans: In PL/SQL to retrieve and process more it requires a special resource, and that resource is known as Cursor. A cursor is defined as a pointer to the context area. The context area is an area of memory that contains information and SQL statements for processing the statements.

Q64) what is the difference between Implicit and Explicit Cursors?

Ans: Implicit cursor used in PL/SQL to declare, all SQL data manipulation statements. An implicit cursor is used to declare SQL statements such as open, close, fetch, etc.

An explicit cursor is a cursor and which is explicitly designed to select the statement with the help of a cursor. This explicit cursor is used to execute the multirow select function. An explicit function is used PL/SQL to execute tasks such as update, insert, delete, etc.

Q65) what is a trigger?

Ans: It is a program in PL/SQL, stored in the database, and executed instantly before or after the UPDATE, INSERT and DELETE commands.

Q66) what are the uses of database triggers?

Ans: Triggers are programs which are automatically fired or executed when some events happen and are used for:

To implement complex security authorizations.
To drive column values.
To maintain duplicate tables.
To implement complex business rules.
To bring transparency in log events.

Q67) Name the two exceptions in PL/SQL?

Ans: Error handling part of PL/SQL is called an exception. We have two types of exceptions, and they are User-defined and predefined.

Q68) which command is used to delete the package?

Ans: To delete the ‘Package’ in PL/SQL we use the DROP PACKAGE command.

Q69) what is the process for PL/SQL compilation?

Ans: The compilation process consists of syntax check, bind, and p-code generation. It checks the errors in PL/SQL code while compiling. Once all errors are corrected, a storage address allocated to a variable that stores this data. This process is called binding. P-Code consists of a list of rules for the PL/SQL engine. It is stored in the database and triggered when the next time it is used.

Tech Studio Online