PL/SQL Interview Questions and Answers
Share This Post
Best PL/SQL Interview Questions and Answers
Welcome to CourseJet – A world’s leading online IT training provider. Are you in search of the top PL/ SQL Interview Questions and Answers? Then your search ends here! We at CourseJet have committed to provide the top PL/ SQL interview questions and answers that are frequently asked in any PL/ SQL interview. Getting a quality PL/SQL training is not enough to clear the PL/SQL interview. You have to prepare yourself with the top PL/SQL interview questions to crack the interview in the very first attempt. This blog has been designed to help you out and make you an expert in answering all type of PL/SQL interview questions.
PL/SQL is abbreviated as a Procedural Language extension to the Structured Query Language”. SQL is a widely-used programming language across the world for updating and querying data in relational database systems. PL/SQL acts as an advanced version of SQL and helps to overcome the limitations of SQL with advanced features. All these PL/ SQL interview questions and answers are grouped here based on the opinion of industry experts. These Frequently asked PL/SQL interview questions and answers blog covers all the essential area’s which include architecture, data types, Performance and integrity, Designing and building applications, Data security, Stored procedures and triggers, Exception Handling, etc. These most frequently asked PL/ SQL interview questions and answers are suitable for the experienced candidates like Database Administrators, Software Developers, BI and Data Warehousing Professionals, SQL, and Analytics Professionals.
PL/SQL Interview Questions for Freshers and experienced
In general, in any PL/ SQL interview, the interviewer starts asking questions from the basic level and moves onto the next level based on your answers. To help you out, we have designed this blog to cover the basic to advance level PL/ SQL interview questions. No matter whether you are a fresher or experienced, these mostly asked PL/SQL interview questions and answers would definitely help you in clearing the interview. In the Tech world, a good number of job opportunities for the well trained and sound knowledgeable professionals in the PL/SQL segment. Starting your career in PL/SQL would definitely help you in achieving your greater heights in your career. Without wasting much time let’s dive into the popular PL/SQL interview questions and answers part.
Basic PL/SQL Interview Questions and Answers
PL/SQL is abbreviated as a Procedural Language extension to the Structured Query Language”. It was developed by Oracle corporation at the beginning of the ‘90s to overcome the limitations of SQL. SQL is a widely-used programming language across the world for updating and querying data in relational database systems. PL/SQL acts as an advanced version of SQL and helps to overcome the limitations of SQL with advanced features such as close integration with SQL, high performance, high productivity, manageability, scalability, object-oriented programming language support, and much more.
SQL stands for Structured Query Language and works as a medium to communicate with a database. SQL was recognized and approved as a standard language for managing relational databases by ANSI (American National Standard Institute). The statements available in SQL would help you in performing tasks such as update, and retrieve data from a database. There are some important commands in SQL such as “Select”, “Insert”, “Create”, “Delete”, “Update”, and “Drop” that can be used to perform almost all the tasks in the SQL database.
An Alias is an advanced feature available in SQL and is supported by the majority of the database management systems. It helps the database users and administrators in minimizing the code required for executing a query and transforms the queries simply to understand.
Important characteristics of PL/SQL are:
- Stored Procedures help in the enhancement of the application sharing process.
- Block-structured language
- Easily portable to all environments
- Integration with Oracle data dictionary
Generally, Oracle takes the help of workspaces to run SQL commands. In a simple way to get this, when an Oracle executes an SQL command, it allows a space in the memory called Private SQL Area. In PL/SQL Cursor will help us in spotting this free area. Cursors also enable the programmers to give a name to this area and allow access to the information.
A Literal is defined as a string that consists of a number, a character, or a date and included in the SELECT list. All the character and date literals must be enclosed using single quotation marks (‘ ’) and the number literals are not required any closings.
The following are the core differences between the SQL and PL/SQL.
Criteria | SQL | PL/SQL |
Definition | A single command or Query execution | A well-developed programming language |
Characteristic | Declarative | Procedural |
What does it consist of? | Data sources for web pages, reports, etc. | A programming language that helps in building web pages, display reports, etc. |
Used for | Modifying data | For developing applications |
A program written using PL/SQL language and associated with a specific table is called a database trigger. The database triggers are used for:
- Log events transparently
- Audit data modifications
- Maintain replica tables
- Execute complex business rules
- To apply complex security authorizations
A PL/SQL Package consists of below elements:
- Procedures and functions
- Record TYPE statements and PL/SQL statements
- Variables (scalars, tables, records, etc.) and constants.
- Exception names
- Pragmas related to an error number
- Cursors
Some of the important benefits offered by PL/SQL Packages are:
- Enforced Information Hiding: This feature enables the users to choose whether to make the data available to the public or keep it private.
- Object Persistence: Whatever the objects declared in a package specification acts as global information for the objects in a PL/SQL application. We have an option to make changes to a package in one module and make these changes available in all other modules.
- Top-down design: we have an option to design the code for the hidden package before even implementing the modules.
- Object-oriented design: The packages give strong knowledge to the developers over the usage process of data structures and modules available inside a package.
- Performance improvement: The RDBMS easily stores the expiry date of all the programming objects resided in a database and helps in improving the performance of packages.
- Transaction integrity: It gives a high-level of transaction integrity.
Looking for Best PL/SQL Hands-On Training?
Get PL/SQL Practical Assignments and Real time projects
Yes, we have different methods to trace the PL/SQL code. It is very essential to trace the code to track the code performance during run time. The following are the various methods available for tracing the code.
- DBMS_TRACE
- DBMS_APPLICATION_INFO
- trcsess and tkprof utilities
- DBMS_SESSION and DBMS_MONITOR
Function: The major functionality of a PL/SQL function is to compute and return a value. A function comes with a return type in its configuration and returns the same value specified in that type.
Package: It is a schema object that combines all the logically related PL/SQL types, subprograms, and items. It can also be said that a package is a group of variables, procedure, and record TYPE statement. Its modularity feature aids in the application development process. Packages also help in restricting unauthorized users from accessing the information.
Procedure: A procedure does not consist of any return type and returns nothing. But the procedure has a return statement that stops its execution. The common purpose of the procedure is to return multiple values, otherwise, it is the same as a procedure.
A stored procedure is defined as a series of statements or a PL/SQL named block that is intended to perform one or more particular functions. It works similar to a procedure in all other programming languages. The stored procedure resides in the database and is executed continuously. It is stored in the form of a schema object and invoked, nested, and parameterized.
Following are the day to day tasks involved in PL/SQL:
- Database objects creation- synonyms, tables, sequences, etc.
- To create procedures, implement business rules, functions, etc.
- To create constraints, impose business rules, triggers, etc.
- To make changes to the data, and to create cursors.
SQL join is a clause that helps you in combining rows from multiple tables, by taking the base of related columns between them. Keys play a vital role in the working process of Joins.
A View is defined as a virtual table in the Oracle database because you can make use of this table in your SQL queries. Each view comes with columns with different data types so you can perform a query against a view or manage their contents using UPDATE, INSERT, MERGE, and DELETE statements.
It works similarly to a table but it does not store any data. A view is also termed as a named query stored in the database. Whenever you query data from the view, Oracle uses this stored query to get back the data from the underlying tables.
A subquery is a query with the main query. Whatever the query that holds a subquery is called outer query and another name for the subquery is inner query. Often the subquery is executed at first and the result will be displayed in the main query. In PL/SQL we have two different types of subqueries that are correlated and non-correlated.
The SQL functions are nothing but sub-programs that are usable and reusable throughout the applications of databases for manipulating or processing data. We have two types of SQL functions: they are single-row functions and multiple-row functions.
Single-row functions:
- Character
- Conversion
- Number
- Date
- Conversion
Multiple-row functions:
- MAX
- MIN
- COUNT
- AVG
- VARIANCE
- STDDEV
- SUM
The DUAL table is a default table available in Oracle and all other database installations. It comes as a one-row and one-column table. In Oracle, the Dual table consists of a single VARCHAR2 column also known as DUMMY, and has a value of ‘X’. It helps the users in the selection of pseudo columns such as USER or SYSDATE. The Dual table is owned by the SYS user and can be accessible to all the users.
A merge statement helps you in inserting and updating the rows in a table by deploying data from the other table. It plays a crucial role in data warehousing applications.
Become PL/SQL Certified Expert in 35 Hours
Get PL/SQL Practical Assignments and Real time projects
Natural Joins: This clause is being used for joining two tables automatically when there is a match of data types and names and it uses a keyword called NATURAL JOIN to join. It works similar to Equi-Join. If the columns have different data types but the same names, then the natural syntax will display you an error.
Cross Join: This Join is mainly deployed to develop the cross-product or a vector product of two different tables. This works similar to the Cartesian product between the two tables.
Join with the USING clause: If multiple columns have similar names but the data types do not match, then the next thing is to modify the NATURAL JOIN clause to declare the columns used for equi Join. We can use the USING clause to declare a single column when the match happens between more than one column.
Left/ Right/ Full Outer Joins: Left Outer Join shows all the rows from a table that is on the left side to the LEFT OUTER JOIN. when it comes to the right outer join it presents you with all the rows from a table which is on the right side of the RIGHT OUTER JOIN clause. The full outer join is different from the above two tables and displays rows from both the tables whether the rows may be left or right to the FULL OUTER JOIN clause.
Join with the ON clause: The ON clause is generally used to declare a join condition. It makes the code simple to understand. In some scenarios, the ON clause is used to join columns of different names.
An entity is defined as a real-world project that is very easy to identify. Let’s consider an example here: in a school database teachers, students, classes and courses offered can be treated as entities. All these entities consist of properties or attributes that give them identity.
An Attribute defines the properties or characteristics of an entity. An entity is a database table that is defined with a set of fixed attributes. For instance, if we want to define a student entity then we can define it with a set of attributes such as name, roll number, course, etc.
The following are some of the features or constructs supported by PL/SQL:
- Embedded SQL support
- Variables and constants
- Cursor management
- Flow control
- Triggers
- Exception handling
- Stored packages and procedures
- Execution section
- Declaration section
- Exception section
Typically triggers are used to create access restriction, implement securities, and create a consensus to the database. Triggers are also useful in:
- To create logs for registering the use of a table
- To develop validation techniques to search in multiple tables
- To update tables in regards to modifications or inclusions
The transaction is defined as a group of DML statements that constructs a unit of work.
The following are the general errors that prevail while happening the transaction execution:
- Value is too wide
- Data type mismatch
- The server gets down or system crashes
- Locking takes place
- The session killed
Locking creates a hurdle free environment between concurrent transactions. Locking is in progress till the Rollback or commit takes place. There are two types of Locking which are:
- Implicit locking: It is applicable for all SQL statement but not SELECT
- Explicit locking: It can be implemented manually by the user
Further, there are two other Lockings also there which are:
- Exclusive: Locks all the users
- Share: Accepts access from other users
ROLLBACK: Stops the on-going transaction by discarding the unfinished data changes.
COMMIT: Stops the on-going transaction by making all the unfinished data modifications permanent.
SAVEPOINT: It segregates a transaction into small parts. Here you can rollback a transaction till a specifically named savepoint.
The following are the advantages of Commit and rollback statements:
- Can group logically related operations
- Can inspect the data modifications before making them permanent
- Makes data consistency
Become a master in PL/SQL Course
Get PL/SQL Practical Assignments and Real time projects
Following are the rules to consider while creating a naming table:
- The name of the table must start with a letter
- The name of the table should be between 1-30 characters
- Table name should only contain A-Z, 0-9,_, a-z, $, #.
- The table name should be unique and cannot copy the name of another object owned by the same cursor.
- The table name should not use the Oracle server reserved word.
We have an option to give a default value to a table by using the DEFAULT option. The DEFAULT option also helps in preventing null values from entering if a row is inserted without having a value for that column. A DEFAULT value can be an expression, literal, or a SQL function like USER and SYSDATE. But the point is the value should be unique and can not be the name of a pseudo column or other columns such as CURRVAL or NEXTVAL.
DATA DICTIONARY: It is a group of tables developed and managed by Oracle Server and contains the information of the database. These data dictionary tables are owned and maintained by SYS users.
USER TABLES: The name itself defines that these tables are specially developed and maintained by the user. A USER table contains the USER information.
The data type is nothing but a storage format using which we can store column values. The following are the different data types used in SQL:
- CHAR(size)
- VARCHAR2(size)
- LONG: 2GB
- DATE
- NUMBER(P, S)
- LONG RAW: 2GB
- RAW (size): Maximum size is 2000
- CLOB: 4GB
- BFILE: 4GB
- BLOB: 4GB
- ROWID
The following are the restrictions to use LONG data type:
- When you create a table using subquery then you can not copy a LONG column.
- It is not possible to include the LONG column in the ORDER BY or GROUP BY clause.
- It is not possible to use a single LONG column per table.
- One cannot define any constraint on a LONG column.
The SET UNUSED feature is used for marking columns as unused and these columns can be deployed when the demand for the system resources is lower. Once a column has been named as unmarked then you no longer have access to that column. Using the select * query you can stop retrieving data from unused columns. Moreover, the columns and names marked unused will not be shown during a DESCRIBE, you can add the same name of the unused column to a new table.
The following table contains a list of variations between truncate and delete:
SQL TRUNCATE | SQL DELETE |
It deletes all the rows from a table and releases the space. | It deletes all the rows from a table but does not free up the space used by that table. |
It is a DDL cannot be Rollback. | It is a DDL statement and can be Rollback. |
Truncate is a fast command | Delete command is a bit slower compared to Truncate |
It is not possible to fire triggers on Truncate | Here it is possible to fire triggers on DELETE |
Constraints are defined as rules or preventive measure which stops users from invalid data entry or from executing data deletion tasks. These are also called as a set of rules defined at the table level. We can create the constraints at the time of creating a table or in the future. We can define the constraints at the table or column level. We can find the contents defined for a specific table in the USER-CONSTRAINTS data dictionary table. You are not allowed to define NOT NULL constraint at the table level but you can do it at the column level.
Following are the 5 different types of constraints:
- Unique Key Constraint
- Not Null Constraint
- Foreign Key Constraint
- Primary Key Constraint
- Check Key Constraint.
The following are the considerable differences between the Unique key and primary key:
Unique Key | Primary Key |
A table can consist of multiple unique keys | Here a table should have only one primary key |
Unique key accepts NULL values | Null values are not accepted to store in the Primary key |
It has the ability to identify each value in a column | Uniquely identifies every single row of a table |
For the following reasons to use Views:
- To convert complex queries into simple ones
- To restrict the data accessibility
- To make the data independent
- Views allow the group of users to access data based on the need.
Looking for PL/SQL Hands-On Training?
Get PL/SQL Practical Assignments and Real time projects
When an SQL*Loader executes, this process may generate a file called discard file. In general, this file is created when there is a requirement and you need to specify that a discard file should be enabled. The discard file stores the data that was filtered out of the load. The out of load happens because of not making any record-selection criteria declared in the control file.
The Discard file contains the files which were not stored in any table of a database. You can declare as many numbers of records as possible till they are accepted by a discard file. The single most difference is that the data inserted into any database is not included in the discard file.
The cursor acts as a memory area for executing the oracle and SQL statements:
The following are the advantages of using the Cursor:
- Help us in retrieving multiple records with a single query
- It boosts the retrieval process by using RAM
- We can retrieve a bundle of data using a cursor.
- It works the same as a 2D array
- It’s an oracle Array.
- Open the Cursor
- Declaring a Cursor
- Close the Cursor
- FETCH rows from the Cursor
Following are the five types of integrity constraints available in Oracle:
- Not Null
- Unique Key
- Primary Key
- Foreign key
- Check constraints
We have two parts in the Package and they are:
- Package body: it is a statement that replaces the package body and is a collection of stored functions, related procedures, program objects, etc, stored as a group in a database.
- Package specification: It is a place where you are allowed to declare public items. You can use the schema to access the specifications declared in a package. You can also use other packages to access items in it.
The predefined exceptions are stored in the Oracle Server.
Syntax Error: This error is spotted by PL/SQL compiler before executing the code or while typing. Examples of Syntax errors include missing semicolon, incorrect spelling, etc.
Runtime Error: This Error is spotted by the exception handler in PL/SQL block. This error occurs once you are done with the execution of the program.
The maximum number of tables allowed per table is 12.
Following are the two types of data types in PL/SQL:
- Scalar data types – number, int, VARCHAR, DATE, Boolean, CHAR, etc.
- Composite data types -Tables, Records, etc.
An exception is looking after the error part of PL/SQL. We have two types of exceptions available in PL/SQL. Which are:
- Pre-defined exception
- User-defined exception
With this, we have come to the end of the frequently asked Oracle PL/SQL interview questions and answers for freshers and experienced candidates’ blogs. Preparing these Oracle PL/SQL top interview questions would definitely help you in clearing your interview in the very first attempt. Hope you have found this blog useful. Stay tuned to this blog for the latest Oracle PL/SQL interview questions and answers. Happy learning!
Our Recent Blogs
Related Searches
