PL/SQL is an extension of SQL and can be referred to as Procedural Language/SQL that allows users to build complex database applications with the help of procedures, modules, functions, control structures, and more. In this blog on PL/SQL Interview Questions, we aim to discuss some of the most popular questions that recruiters of major companies generally ask during job interviews. Here is a list of these interview questions and answers:
Q1. Compare between SQL and PL/SQL.
Q2. What is PL/SQL?
Q3. What is the basic structure of PL/SQL?
Q4. Explain the uses of a database trigger.
Q5. How is a process of PL/SQL compiled?
Q6. What does a PL/SQL package consist of?
Q7. What are the benefits of PL/SQL packages?
Q8. What are different methods to trace the PL/SQL code?
Q9. What is the difference between functions, procedures, and packages in PL/SQL?
Q10. What is a stored procedure?
The three parts into which this PL/SQL Interview Questions blog is divided are as follows:
Watch this video on PL/SQL Interview Questions and Answers on YouTube:
Basic Interview Questions
1. Compare between SQL and PL/SQL.
|What is it?
||A single query or command execution
||A full programming language
|What does it comprise?
||The data source for reports, web pages, etc.
||An application language to build, format, and display reports, web pages, etc.
||Declarative in nature
||Procedural in nature
2. What is PL/SQL?
Oracle PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration and conditional branching.
Go through this PL/SQL Tutorial to learn ‘What is PL/SQL?’
3. What is the basic structure of PL/SQL?
PL/SQL uses a block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
4. Explain the uses of a database trigger.
A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for:
- Audit data modifications
- Log events transparently
- Enforce complex business rules
- Maintain replica tables
- Derive column values
- Implement Complex security authorizations
Any constant, variable, or parameter has a data type depending on which the storage constraints, format, and the range of values and operations are determined.
5. How is a process of PL/SQL compiled?
The compilation process includes syntax check, bind, and p-code generation processes. Syntax checking checks the PL/SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL/SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed.
Go through the Handling PL/SQL Errors tutorial page to know how error handling is done in PL/SQL!
Intermediate Interview Questions
6. What does a PL/SQL package consist of?
A PL/SQL package consists of:
- PL/SQL table and record TYPE statements
- Procedures and functions
- Variables ( tables, scalars, records, etc.) and constants
- Exception names and pragmas for relating an error number with an exception
Check out the insightful PL/SQL tutorial to learn more about Pl/SQL Packages!
7. What are the benefits of PL/SQL packages?
PL/SQL packages provide several benefits as follows:
- Enforced information hiding: It offers the liberty to choose whether to keep data private or public.
- Top-down design: We can design the interface to the code hidden in the package before we actually implemented the modules.
- Object persistence: Objects declared in a package specification behave like global data for all PL/SQL objects in the application. We can modify the package in one module and then reference those changes to another module.
- Object-oriented design: The package gives developers stronghold over how the modules and data structures inside the package can be used.
- Guaranteeing transaction integrity: It provides a level of transaction integrity.
- Performance improvement: The RDBMS automatically tracks the validity of all program objects stored in the database and enhance the performance of packages.
Master PL/SQL from this top-rated PL/SQL Certification Training!
8. What are different methods to trace the PL/SQL code?
Tracing the code is a crucial technique to measure its performance during the runtime. Different methods for tracing the code includes:
- DBMS_SESSION and DBMS_MONITOR
- trcsess and tkprof utilities
9. What is the difference between functions, procedures, and packages in PL/SQL?
- Function: The main purpose of a PL/SQL function is to compute and return a single value. A function has a return type in its specification and must return a value specified in that type.
- Procedure: A procedure does not have a return type and should not return any value, but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values; otherwise, it is generally similar to a function.
- Package: A package is a schema object which groups logically related PL/SQL types, items, and subprograms. You can also say that it is a group of functions, procedures, variables, and record TYPE statement. It provides modularity, due to which it aids application development. It is used to hide information from unauthorized users.
10. What is a stored procedure?
A stored procedure is a sequence of statements or a named PL/SQL block that performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as a schema object. It can be nested, invoked, and parameterized.
Wish to learn more? Visit the PL/SQL Collections and Records tutorial page!
Advanced Interview Questions
11. What is a cursor? Why is it required?
A cursor is a temporary work area created in system memory when a SQL statement is executed. A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data, retrieved from the database, to manipulate it. A cursor can hold more than one row but can process only one row at a time. A cursor is required to process rows individually for queries.
12. Explain the Day-to-day activities in PL/SQL.
- Create database objects—tables, synonyms, sequences, etc.
- To implement business rules, create procedures, functions, etc.
- To impose business rules, create constraints, triggers, etc.
- For data manipulation, create cursors
13. How to display records having the maximum salary from an employee table?
Select * from emp where sal= (select max(sal) from emp)
If you have any doubts or queries related to PL/SQL, get them clarified from our PL/SQL experts on our SQL Community!
14. How to display the highest salary from an employee table?
Use the following code for displaying the highest salary from an employee table:
Select max(sal) from emp;
15. How to display the second highest salary from an employee table?
Select max(sal) from emp where sal not in ( select max(sal) from emp
16. What is a Join?
Join is a keyword used to query data from multiple tables based on the relationship between the fields of tables. Keys play a major role in Joins.
17. what is a View?
- A View is a virtual table consisting of data contained in a table.
- Views do not need any memory space.
- Views can be created on multiple tables.
18. What is a subquery? What are its types?
A subquery is a query within another query. The outer query is known as the main query and the inner query is called the subquery. A subquery is executed first, and the result of the subquery is passed to the main query.
There are two types of subqueries: correlated and non-correlated
19. what is a trigger?
A trigger is a database object that automatically executes in response to some events on the tables or views. It is used to apply the integrity constraint to the database objects.