What types of joins do you know?

 (INNER) JOIN – returns only those records that satisfy a defined join
condition in both (or all) tables. It's a default SQL join.

 LEFT (OUTER) JOIN – returns all records from the left table and those
records from the right table that satisfy a defined join condition.

 RIGHT (OUTER) JOIN – returns all records from the right table and
those records from the left table that satisfy a defined join condition.

 FULL (OUTER) JOIN – returns all records from both (or all) tables. It
can be considered as a combination of left and right joins.

What is a unique key?

 A column (or multiple columns) of a table to which
the UNIQUE constraint was imposed to ensure unique values in that
column, including a possible NULL value (the only one).


What is a foreign key?

 A column (or multiple columns) of a table to which the FOREIGN
KEY constraint was imposed to link this column to the primary key in
another table (or several tables). The purpose of foreign keys is to
keep connected various tables of a database.

What types of indexes do you know?

 Unique index – doesn't allow duplicates in a table column and

hence helps maintain data integrity.

 Clustered index – defines the physical order of records of a

database table and performs data searching based on the key
values. A table can have only one clustered index.

 Non-clustered index – keeps the order of the table records that

don't match the physical order of the actual data on the disk. It
means that the data is stored in one place and a non-clustered
index – in another one. A table can have multiple non-clustered
indexes.

What is the DISTINCT statement and how do you use it?

 This statement is used with the SELECT statement to filter out
duplicates and return only unique values from a column of a table.
The syntax is:
 SELECT DISTINCT col_1
 FROM table_name;
What is NULL value? How is it different from zero or a
blank space?
 A NULL value indicates the absence of data for a certain cell of a
table. Instead, zero is a valid numeric value, and an empty string is
a legal string of zero length.

What is a function in SQL, and why use functions?

 A database object representing a set of SQL statements frequently
used for a certain task. A function takes in some input parameters,
performs calculations or other manipulations on them, and returns
the result. Functions help improve code readability and avoid
repetition of the same code snippets.

What types of SQL functions do you know?

 Aggregate functions – work on multiple, usually grouped records

for the provided columns of a table, and return a single value
(usually by group).

 Scalar functions – work on each individual value and return a

single value.

What aggregate functions do you know?

 AVG() – returns the average value

 SUM() – returns the sum of values

 MIN() – returns the minimum value

 MAX() – returns the maximum value

 COUNT() – returns the number of rows, including those with null

values

 FIRST() – returns the first value from a column

 LAST()– returns the last value from a column

What scalar functions do you know?

 LEN() (in other SQL flavors – LENGTH()) – returns the length of a

string, including the blank spaces

 UCASE() (in other SQL flavors – UPPER()) – returns a string converted

to the upper case

 LCASE() (in other SQL flavors – LOWER()) – returns a string

converted to the lower case
  INITCAP() – returns a string converted to the title case (i.e., each
word of the string starts from a capital letter)

 MID() (in other SQL flavors – SUBSTR()) – extracts a substring from a

string

 ROUND() – returns the numerical value rounded to a specified

number of decimals

 NOW() – returns the current date and time

What is the difference between local and global variables?

 Local variables can be accessed only inside the function in which
they were declared. Instead, global variables, being declared
outside any function, are stored in fixed memory structures and can
be used throughout the entire program.

What set operators do you know?

 UNION – returns the records obtained by at least one of two queries

(excluding duplicates)

 UNION ALL – returns the records obtained by at least one of two

queries (including duplicates)

 INTERSECT – returns the records obtained by both queries

 EXCEPT (called MINUS in MySQL and Oracle) – returns only the

records obtained by the first query but not the second one

What is the difference between a primary key and a

unique key?
 While both types of keys ensure unique values in a column of a
table, the first one uniquely identifies each record of the table, and
the second one prevents duplicates in that column.

What is a composite primary key?

 The primary key of a table, based on multiple columns.

What is a view, and why use it?

 A virtual table containing a subset of data retrieved from one or
more database tables (or other views). Views take very little space,
simplify complex queries, limit access to the data for security
reasons, enable data independence, and summarize data from
multiple tables.
What is the difference between nested and correlated
subqueries?
 A correlated subquery is an inner query nested in a bigger (outer)
query that refers to the values from the outer query for its
execution, meaning that a correlated subquery depends on its outer
query. Instead, a non-correlated subquery doesn't rely on the data
from the outer query and can be run independently of it.

What is the difference between clustered and non-

clustered indexes?
 While a clustered index defines the physical order of records of
a table and performs data searching based on the key values, a
non-clustered index keeps the order of records that do not
match the physical order of the actual data on the disk. A table
can have only one clustered index but many non-clustered ones.

What is the difference between the HAVING and WHERE

statements?
 The first one works on aggregated data after they are grouped,
while the second one checks each row individually. If both
statements are present in a query, they appear in the following
order: WHERE – GROUP BY – HAVING. The SQL engine interprets them
also in the same order.

What are the different uses of stored procedures?

What are the advantages of stored procedures?

When would you use stored procedures or functions?

Explain the difference between stored procedures and triggers.

What is SQL Query Optimization?

Ans. Query Optimization is the process of writing the query in a way that
it could execute quickly. It is a significant step for any standard
application.

What are some tips to improve the performance of SQL queries?

Ans. Optimizing SQL queries can bring a substantial positive impact on

performance. It also depends on the level of RDBMS knowledge you have.
Let’s now go over some of the tips for tuning SQL queries.

1. Prefer to use views and stored procedures despite writing long queries.
It’ll also help in minimizing network load.
2. It’s better to introduce constraints instead of triggers. They are more
efficient than triggers and can increase performance.

3. Make use of table-level variables instead of temporary tables.

4. For faster results, use UNION ALL. It combines data sets without
removing duplicates, unlike UNION which filters them.

5. Prevent the usage of DISTINCT and HAVING clauses.

6. Avoid excessive use of SQL cursors.

7. Use SET NOCOUNT ON in stored procedures to signify the affected rows

by a T-SQL statement. It would lead to reduced network traffic.

1. What are the features of PL/SQL?

 PL/SQL is a procedural language, which provides the functionality of

decision making, iteration, and numerous further features of
procedural programming languages.

 Using a single command, PL/SQL executes several queries in one

block.

 PL/SQL can handle the exception generated in the PL/SQL block.

That block is called an exception handling block.

 One can create a PL/SQL unit such as procedures, packages,

triggers, functions, and types, which are stored in the database for
reuse by applications.

 Applications that are written in PL/SQL are portable to computer

hardware or operating systems where Oracle is functional.

2. What do you understand by PL/SQL Table?

An ordered collection of the same type of elements is referred to as a

PL/SQL table. position of each element in the ordered collection is
determined by its index number. The position of each element in the
ordered collection is determined by its own index number. A user-defined
type must be declared first for the PL/SQL table, and then it must be
declared as a variable.

DECLARE

TYPE Vehicle_SSN_tabtype IS TABLE OF

integer (9)

INDEX BY binary _integer;

Vehicle_SSN_table Vehicle_SSN_tabtype;
3. Explain the basic structure followed in PL/SQL.

By including elements from procedural languages, PL/SQL expands SQL

and creates a structural language that is more potent than SQL. A block is
PL/SQL’s fundamental building block. Every PL/SQL program is composed
of blocks that can be nested inside of one another.

4. What is a PL/SQL cursor?

PL/SQL cursor controls the context area. A cursor holds one or more than
one row returned by an SQL statement. set of rows which is held by the
cursor is known as an active set.

Two types of cursors exist in PL/SQL.

 Implicit Cursor

 Explicit cursor

5. What is the use of WHERE CURRENT OF in cursors?

LAST FETCHED ROW identify by using WHERE CURRENT OF in cursor. We

can use the WHERE CURRENT OF statement for updating or deleting
records without using the SELECT FOR UPDATE statement. The record that
was last retrieved by the cursor can be updated or deleted using the
WHERE CURRENT OF statement.

Syntax:

UPDATE table_name

SET set_clause

WHERE CURRENT OF cursor_name;

OR

DELETE FROM table_name

WHERE CURRENT OF cursor_name;

6. How can a name be assigned to an unnamed PL/SQL Exception

Block?

 PL/SQL Exception Block name can be assigned by using Pragma

known as EXCEPTION_INIT.

 Our own error message and error number can be defined by using
the Pragma EXCEPTION_INIT function.

Syntax:

DECLARE
exception_name EXCEPTION;

PRAGMA EXCEPTION_INIT(exception_name, error_code);

BEGIN

// Code

EXCEPTION

WHEN exception _name THEN //exception handling steps

END;

7. What is a PL/SQL Trigger? Give some examples of when

“Triggers” might be useful.

When a specific event takes place, the Oracle engine immediately starts.
The trigger is already stored in the database If a certain condition is met,
the trigger is frequently called from a database. Trigger mode can be
activated or deactivated, but running explicitly is not possible.

Syntax:

TRIGGER trigger_name

trigger_event

[ triggers restrictions ]

BEGIN

trigger_action;

END;

In the above syntax, If the restrictions are TRUE or the trigger is

unavailable, The trigger_event causes the database to fire the actions of
the trigger if the trigger_name is enabled.

Below are some examples of where triggers might be useful.

Complex integrity constraints must be maintained.

To enforce intricate business regulations.
To audit any table information, if necessary.
Triggers are used whenever changes are made to a table and we need to
signal other actions after the change has been made.
Additionally, it can be applied to stop fraudulent transactions.

8. When does a DECLARE block have to be present?

In PL/SQL anonymous blocks, such as stand-alone and non-stored
procedures, a DECLARE statement is used. The statement in the stand-
alone file should come first when they are used.

9. How should comments be written in PL/SQL code?

Two types of comments we can write in PL/SQL code.

 Single Line Comments

 Multiple Line Comments

Single Line Comments: We can use the — symbol to comment on a

single line in PL/SQL code.

Multiple Line Comments: We can use the/* lines*/ syntax to comment

on multiple lines in PL/SQL code.

DECLARE

— Hi Geeks, This is a single line comment.

BEGIN

/* Hi Geeks,

This is Multiple line comments.*/

END;

10. What is the purpose of the WHEN condition in the trigger?

WHEN condition is used for row level triggers. Trigger fire when a certain
condition is met.

11. What are the Differences between SQL and PL/SQL?

SQL PL/SQL

SQL manages a relational PL/SQL is a programming language

database management system. for databases.

SQL can perform a single PL/SQL can execute multiple

operation at a time. operations at the same time.

SQL is an interpretive language. it is a procedure language.

SQL PL/SQL

PL/SQL consists of variables,

No variable is used in SQL.
datatype, etc.

SQL directly connects with the PL/SQL does not directly connect
database server. with the database server.

it is a data data-oriented It is application application-

language. oriented language.

It can not contain PL/SQL

It can contain SQL inside it.
Language.

It is used to perform DDL and PL/SQL performs blocks, functions,

DML operations. procedures and triggers.

12. Why are SYSDATE and USER keywords used?

SYSDATE:

The local database server’s current time and date are returned by the
SYSDATE keyword.

Example:

SELECT SYSDATE FROM dual;

USER :

The user id of the current session will be returned by using the USER
keyword.

Example:

SELECT USER FROM dual;

13. What is the Difference between implicit cursor and explicit

cursor?
Implicit Cursor Explicit Cursor

Implicit cursor is An explicit cursor is defined by the

automatically created cursor. user.

Implicit cursor can fetch a The explicit cursor can fetch multiple
single row at a time. rows at the same time.

It gives less programmatic The explicit cursor is totally controlled

control to programmers. by programmers.

It is less efficient to explicit

An explicit cursor is more efficient.
cursor.

Implicit cursor attributes

always use the prefix “SQL”
keyword. Explicit cursors structure: curs_name
%attri_name
structure for implicit cursor
defined as SQL%attri_name. Some more explicit cursors are
curs_name%FOUND, curs_name
some mor implicit cursors are %NOTFOUND, curs_name
SQL%FOUND, SQL %ROWCOUNT.
%NOTFOUND, SQL
%ROWCOUNT.

14. Tell the importance of %TYPE and %ROWTYPE data types in

PL/SQL.

%Type:

This datatype is used for specified tables to define the variable as its
column name datatype.

Syntax:

vAttributName Attribute.Attribute_Name%TYPE;

In the above syntax, the datatype of Attribute_Name is assigned to the

variable named vAttributeName.

%ROWTYPE:
Use %ROWTYPE if the programmer doesn’t know the datatype of the
specified column but still needs to assign it to the variable. It is nothing
more than an assignment in an array in which we can specify a variable
and define the entire row datatype.

Syntax:

Rt_var_Student Student%ROWTYPE;

%ROWTYPE assigns the data type of the Student table to the

Rt_var_Student variable.

15. What are the differences between ROLLBACK and ROLLBACK

TO statements in PL/SQL?

 The ROLLBACK command is used to undo any modification made

since the transaction’s start.

 The transaction may only be rolling back using the ROLLBACK TO

command up to a SAVEPOINT. The transaction stays active even
before the command is provided since the transactions cannot be
rolled back before the SAVEPOINT.

16. What are the uses of SYS.ALL_DEPENDENCIES?

The dependencies between all the procedures, packages, triggers, and

functions that the current user can access are described by
SYS.ALL_DEPENDENCIES.

17. What the virtual tables exist during the execution of the
database trigger?

 OLD and NEW two are virtual tables that exist during the execution
of the database trigger.

 OLD and NEW both are accessible by UPDATE statement.

 INSERT statement can access only NEW value.

18. What is the Difference between the cursors declared in

procedures and in the package specifications?

The cursor declared in a package specification is global and can be

accessed by other procedures or procedures in the package. A cursor
declared in a procedure is local that can not be accessed by other
procedures.

19. What is purposes of COMMIT, ROLLBACK and SAVEPOINT

statements in PL/SQL?
COMMIT statement: The changes made during a transaction are saved
permanently by the COMMIT command.

Syntax:

DECLARE

BEGIN

// command;

COMMIT;

END;

ROLLBACK statement: It is used to undo any modification made since

the transaction’s start.

Syntax:

DECLARE

BEGIN

// command;

ROLLBACK;

END;

SAVEPOINT statement: A transaction point that can be utilised to roll

back to a certain point in the transaction is created using the SAVEPOINT
statement.

Syntax:

DECLARE

BEGIN

SAVEPOINT sp;

// command;

ROLLBACK TO sp;

END;

20. How can we debug our PL/SQL code?

To debug our PL/SQL code, we can use the DBMS_OUTPUT and

DBMS_DEBUG statements. The output is printed to the standard console
via DBMS_OUTPUT. The output is printed to the log file by DBMS_DEBUG.

Intermediate PL/SQL Interview Questions

21. What is the main difference between a mutating table and a
constraining table?

A table that can be changed using a DML statement or one with triggers
defined is said to be a mutating table. The table that is read for a
referential integrity constraint is referred to as a constraining table.

22. Describe the data types present in PL/SQL.

There are two types of data types present in PL/SQL.

 Scalar data types: NUMBER, DATE, CHAR, VARCHAR2, BOOLEAN

and LONG are scalar data types.

 Composite data type: TABLE and RECORD are composite data

types.

23. List the types of exceptions in PL/SQL.

Two types of exceptions are present in PL/SQL.

 Pre_defined exception

 User-defined exception

24. What types of commands PL/SQL does not support?

PL/SQL does not support data definition commands like CREATE, ALTER
etc.

25. Name some PL/SQL exceptions.

Below are some PL/SQL exceptions.

 INVALID_NUMBER

 TOO_MANY_ROWS

 ACCESS_INTO_NULL

 CASE_NOT_FOUND

 ZERO_ERROR

 NO_DATA_FOUND

26. What is a PL/SQL package?

Packages are schema objects that group PL/SQL types, variables, and
subprograms that are logically related.

A package will have two parts.

 Package specification
  Package body or definition

Syntax:

package_name.attribute_name;

27. Write a PL/SQL program to find a given string is palindrome.

DECLARE

— Declared variable

string VARCHAR(10):=”abababa”;

letter VARCHAR(20);

recerse_string VARCHAR(10);

BEGIN

FOR i IN REVERSE 1.. LENGTH(string) LOOP

letter := SUBSTR(string, i, 1);

— concatenate letter to reverse_string variable

reverse_string := reverse_string || ” ||letter;

END LOOP;

IF

reverse_string = string THEN dbms_output.Put_line(reverse_string||”||’ is

palindrome’);

ELSE

dbms_output.Put_line(reverse_string||”||’ is not palindrome’);

END IF

END;

28. What command will you use to delete a package?

we use the DROP PACKAGE statement to delete a package.

Syntax:

DROP PACKAGE [BODY] Attribute_Name.Package_Name;

29. How will you execute a stored procedure?

EXECUTE or EXEC keyword can be used to execute stored procedures.

Syntax:
EXECUTE procedure_name;

or

EXEC procedure_name;

30. Explain the IN, OUT and IN OUT parameters.

IN: We can transmit values to the procedure that is being called using the
IN parameter. You can use the default settings for the IN parameter. IN
parameter behaves as a constant.

OUT: The caller receives a value from the OUT parameter. It is an

uninitialized variable.

IN OUT: The IN OUT parameter gives starting values to a procedure and

sends the updated values to the caller. IN OUT parameter should be like
an initialized variable.

31. Differentiate between %ROWTYPE and %TYPE.

%ROWTYPE: It is used to declare a variable that has the structure of the

records in a table.

%TYPE: To declare a column in a table that contains the value of that

column, use the %TYPE property. The variable’s data type and the table’s
column are the same.

32. Discuss SQLERRM and SQLCODE. What is the importance of

PL/SQL?

 SQLCODE returns the error number for the most recent error found.

 SQLERRM returns the error message for the most recent error.

SQLCODE and SQLERRM can be used in exception handling in PL/SQL to

report the error that happened in the code in the error log database.

33. What are PL/SQL records? tell types of records.

A record is a type of data structure that may store several types of data
elements. Like a row in a database table, a record is made up of various
fields.

There are three types of records in PL/SQL.

 Table-based records

 Cursor-based records

 User-defined records are created by programmers.

34 Explain the BTITLE and TTITLE statements.

TTITLE statement is used to define the top title similarly for defining the
bottom title we will use BTITLE statement.

35. What are the valid DateTime values for seconds in PL/SQL?

The following are valid second values:

 00 to 59.9(n), where 9(n) is the accuracy in fractional seconds of

time.

 For DATE, the 9(n) section does not apply.

36 Explain PL/SQL Delimiters.

In PL/SQL, a delimiter is a compound symbol having a unique meaning.

Delimiters are used to indicate arithmetic operations like division, addition
etc.

37. What is the use of a UTL_FILE package in PL/SQL?

UTL_FILE package is used for read/write operating system text files. Both
client-side and server-side PL/SQL are supported, and it offers a
constrained variant of the operating system’s stream file I/O.

DECLARE

FileHandler UTL_FILE.FILE_TYPE;

BEGIN

FileHandler:= UTL_FILE.FOPEN(–file root);

END;

38. What is the use of index in PL/SQL?

A table’s data blocks can be accessed more quickly and effectively with
the help of an index.

39. What does the error ORA-03113 mean?

An ORA-3113 means “end of file on communication channel”. A client

process connected to an Oracle database will typically report an ORA-
3113. these are some following scenarios when ORA-3113 can occur:

 When a server machine crashed

 At the operating system level, our server process was killed.

 Few netwFpackagesork problems

 The client is not handling multiple connections

40. What is a Join?

The join keyword is used to query data from multiple tables. Join is based
on the relationship between the fields of tables. Table keys play an
important role in Joins.

41. How can we write or create multiple tables in PL/SQL?

Nested tables are collection types in PL/SQL. Nested tables are created
either in the PL/SQL block or at the schema level. These are like a 1D
array, but their size can be increased or decreased dynamically.

Syntax:

TYPE type_name IS TABLE OF element_type [NOT NULL];

name_of_table type_name;

Advanced PL/SQL Interview Questions

42. Discuss the concept of Raise_application_error.

From stored subprograms, this procedure can be used to send user-

defined error messages. By informing our application of failures, we can
stop unhandled exceptions from being returned. the executable section
and the exceptional section are two places that appear in it.

Syntax:

raise_application_error(error_number, message[, {TRUE | FALSE}]);

43. What do you know about pragma_exception_init in PL/SQL?

An exception name and Oracle error number are linked together by the
pragma_exception_init command in PL/SQL. This makes it possible to
create a unique handler for any internal exception are refer to it by name.

44. How can you verify whether an Update Statement is Executed

or not, In PL/SQL?

The SQL % NOTFOUND attribute can be used to determine whether or not

the UPDATE statement successfully changed any records. If the last SQL
statement run had no effect on any rows, this variable returns TRUE.

45. What is the use of the || Operator?

The || operator is used to combine the strings. Both DBMS_OUTPUT.put

line and select statements functions use the || operator.

46. Is a definition command like the CREATE command supported

in PL/SQL?

Definition commands like the CREATE command are not supported by

PL/SQL.
47. Explain a view.

A view is generated by combining one or more tables. it is a virtual table

that is based on the outcome of SQL statements; It includes rows and
columns like an actual table.

Syntax:

CREATE VIEW view_name AS SELECT columns FROM tables;

48. What are the basic parts of triggers?

The following three are basic parts of a trigger.

 Trigger statement

 Trigger restriction

 Trigger action

49. What are the Methods to Trace the PL/SQL Code?

We will trace the code to measure its performance during run time. below
are some methods to trace the PL/SQL code.

 DBMS_TRACE

 DBMS_APPLICATION_INFO

 DBMS_SESSION

 DBMS_MONITOR

50. How do you Create Nested Tables in PL/SQL?

One of the collection types is nested tables in PL/SQL. Nested tables can
be created in a PL/SQL block or at the schema level, also similar to a 1D
array but their size can be extended dynamically.

TYPE type _name IS TABLE OF element_type [NOT NULL];

table_name typer_name;

DECLARE

TYPE deptname IS TABLEOF VARVHAR2(10);

TYPE budget IS TABLE OF INTEGER;

names deptname;

deptbudget budget;

BEGIN

names := deptname (‘marketing, ‘development’, ‘sales’);

deptbudget := budget (12567, 4567, 1234);

FOR i IN 1 . . names.count LOOP

dbms_output.put_line(‘Department = ‘| |names(i)| |’, Budget = ‘ | |

deptbudget(i));

end loop;

END;