pl sql scenario based interview questions for experienced

Basic PL/SQL Interview Questions
  • Tell us one way to compile a PL/SQL code.
  • Name some data types in PL/SQL.
  • Differentiate between %ROWTYPE and %TYPE.
  • What are some schema objects that you can create using PL/SQL?
  • What are predefined exceptions?
  • Name some packages PL/SQL Developers can use.
  • What are Character Functions?

This is a list of the top PL/SQL interview questions and answers compiled by industry experts working in the PL/SQL domain. From these interview questions, you will be able to understand the importance of PL/SQL and the concepts of database triggers, compilation process, packages, tracing a code, functions, procedures, joins, and views in PL/SQL. Learn PL/SQL from Intellipaats PL/SQL Certification Training to excel in your career!

PL/SQL is an extension of SQL and can be referred to as Procedural Language/SQL. It allows users to build complex database applications with the help of procedures, modules, functions, control structures, and more. This blog aims to discuss some of the most popular questions that recruiters of major companies generally ask during job interviews. Here is a list of the interview questions along with their answers:

Scenario Based Question | Oracle Database Tricky Interview Questions

How to select UNIQUE records from a table using a SQL Query?

Consider below EMPLOYEE table as the source data

EMPLOYEE_ID NAME SALARY
100 Jennifer 4400
100 Jennifer 4400
101 Michael 13000
101 Michael 13000
101 Michael 13000
102 Pat 6000
102 Pat 6000
103 Den 11000

METHOD-1: Using GROUP BY Function

GROUP BY clause is used with SELECT statement to collect data from multiple records and group the results by one or more columns. The GROUP BY clause returns one row per group. By applying GROUP BY function on all the source columns, unique records can be queried from the table.

Below is the query to fetch the unique records using GROUP BY function.

Query:

Result:

EMPLOYEE_ID NAME SALARY
100 Jennifer 4400
101 Michael 13000
102 Pat 6000
103 Den 11000

METHOD-2: Using ROW_NUMBER Analytic Function

The ROW_NUMBER Analytic function is used to provide consecutive numbering of the rows in the result by the ORDER selected for each PARTITION specified in the OVER clause. It will assign the value 1 for the first row and increase the number of the subsequent rows.

Using ROW_NUMBER Analytic function, assign row numbers to each unique set of records.

Query:

Result:

EMPLOYEE_ID NAME SALARY ROW_NUMBER
100 Jennifer 4400 1
100 Jennifer 4400 2
101 Michael 13000 1
101 Michael 13000 2
101 Michael 13000 3
102 Pat 6000 1
102 Pat 6000 2
103 Den 11000 1

Once row numbers are assigned, by querying the rows with row number 1 will give the unique records from the table.

Query:

Result:

EMPLOYEE_ID NAME SALARY
101 Michael 13000
100 Jennifer 4400
102 Pat 6000
103 Den 11000

Related Article: SQL Analytic Functions Interview Questions

How to delete DUPLICATE records from a table using a SQL Query?

Consider the same EMPLOYEE table as source discussed in previous question

METHOD-1: Using ROWID and ROW_NUMBER Analytic Function

An Oracle server assigns each row in each table with a unique ROWID to identify the row in the table. The ROWID is the address of the row which contains the data object number, the data block of the row, the row position and data file.

STEP-1: Using ROW_NUMBER Analytic function, assign row numbers to each unique set of records. Select ROWID of the rows along with the source columns

Query:

Result:

ROWID EMPLOYEE_ID NAME SALARY ROW_NUMBER
AAASnBAAEAAACrWAAA 100 Jennifer 4400 1
AAASnBAAEAAACrWAAB 100 Jennifer 4400 2
AAASnBAAEAAACrWAAC 101 Michael 13000 1
AAASnBAAEAAACrWAAD 101 Michael 13000 2
AAASnBAAEAAACrWAAE 101 Michael 13000 3
AAASnBAAEAAACrWAAF 102 Pat 6000 1
AAASnBAAEAAACrWAAG 102 Pat 6000 2
AAASnBAAEAAACrWAAH 103 Den 11000 1

STEP-2: Select ROWID of records with ROW_NUMBER > 1

Query:

Result:

ROWID
AAASnBAAEAAACrWAAB
AAASnBAAEAAACrWAAD
AAASnBAAEAAACrWAAE
AAASnBAAEAAACrWAAG

STEP-3: Delete the records from the source table using the ROWID values fetched in previous step

Query:

Result:

The table EMPLOYEE will have below records after deleting the duplicates

ROWID EMPLOYEE_ID NAME SALARY
AAASnBAAEAAACrWAAA 100 Jennifer 4400
AAASnBAAEAAACrWAAC 101 Michael 13000
AAASnBAAEAAACrWAAF 102 Pat 6000
AAASnBAAEAAACrWAAH 103 Den 11000

METHOD-2: Using ROWID and Correlated subquery

Correlated subquery is used for row-by-row processing. With a normal nested subquery, the inner SELECT query runs once and executes first. The returning values will be used by the main query. A correlated subquery, however, executes once for every row of the outer query. In other words, the inner query is driven by the outer query.

In the below query, we are comparing the ROWIDs’ of the unique set of records and keeping the record with MIN ROWID and deleting all other rows.

Query:

Result:

The table EMPLOYEE will have below records after deleting the duplicates

ROWID EMPLOYEE_ID NAME SALARY
AAASnBAAEAAACrWAAA 100 Jennifer 4400
AAASnBAAEAAACrWAAC 101 Michael 13000
AAASnBAAEAAACrWAAF 102 Pat 6000
AAASnBAAEAAACrWAAH 103 Den 11000

The opposite of above discussed case can be implemented by keeping the record with MAX ROWID from the unique set of records and delete all other duplicates by executing below query.

Query:

Result:

The table EMPLOYEE will have below records after deleting the duplicates

ROWID EMPLOYEE_ID NAME SALARY
AAASnBAAEAAACrWAAA 100 Jennifer 4400
AAASnBAAEAAACrWAAC 101 Michael 13000
AAASnBAAEAAACrWAAF 102 Pat 6000
AAASnBAAEAAACrWAAH 103 Den 11000

Scenario 1:

Select numeric & character values in separate columns using data present in single column.

Input Data – One column having both numeric & character values in it. See below sample data.

pl sql scenario based interview questions for experienced

Output – Show numeric & character values in two separate columns using select query only. See below output.

pl sql scenario based interview questions for experienced

What is PL SQL?

Oracle PL/SQL is an extension of SQL language that combines the data manipulation power of SQL with the processing power of procedural language to create super powerful SQL queries. PL/SQL ensures seamless processing of SQL statements by enhancing the security, portability, and robustness of the Database. PL/SQL stands for “Procedural Language extensions to the Structured Query Language.”

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *