Sunday, August 18, 2024

Top 20 PL/SQL Interview Questions with Answers for 1 to 3 years

Hello guys, if you are preparing for Oracle or PL/SQL Job or a Developer job where PL/SQL skills are needed and looking for frequently asked PL/SQL Interview Questions then you have come to the right place. Earlier, I have shared both best PL/SQL courses and best PL/SQL books and in this article, I am going to share popular PL/SQL Interview Questions you can revise before your interview. Preparing for PL/SQL Interview is not a rocket science and you can prepare for it the same way you prepare for other interviews. 


There is no shortcut here, in fact you should do more when it comes to PL/SQL interview so that you can reap well. Doing more means getting to know and understand the types of questions that are usually asked during PL/SQL interviews. 

You will not know the questions if you just sit there and relax but you will know them if you go through an article like this. 

Going through such an article is not enough if you can’t be able to understand and grasp all the ideas or the questions and answers. Please go through the following questions keenly.



20 PL/SQL Interview Questions with Answers for 1 to 2 Years Experienced

Without wasting anymore of your time, here is a list of common PL/SQL Interview questions and their answers you can revise. These are basic PL/SQL questions and if you mention PL/SQL in your resume then you are most likely familiar with these questions and answer them confidently. 


1. What is PL SQL?

Answer: PL SQL is a procedural language which has interactive SQL. It also has procedural programming language constructs like conditional branching and iteration.

 

2. What are the uses of database trigger?

Answer: a database trigger is a PL/SQL program unit associated with a particular database. Its uses are as follows:

  • Enforcing complex business rules
  • Audit data modifications
  • Maintaining replica tables
  • Deriving column values
  • Implementing complex security authorizations


3. What are the three basic parts of a trigger?

Answer:

  • A restriction
  • A triggering statement
  • An action


4. What does PLV (PL/Vision) package offer?

Answer:

  • Pre-defined datatypes
  • Set of assertion routines
  • Set of constants used throughout PL vision
  • Miscellaneous utilities
  • Null substitution value


5. What are the rules to be applied to NULLs during comparison?

Answer:

  • NULL cannot be equal or unequal to other values.
  • Where a value in an expression is NULL, then the expression itself evaluates to NULL except for concatenation operator.
  • NULL is never TRUE or FALSE


6. What is the difference between syntax and runtime errors?

Answer: syntax errors are the errors which can be easily identified by a PL/SQL compiler while runtime errors are errors in PL/SQL for which an exception handling section is to be included for handling the errors


PL/SQL Interview Questions with Answers


 

7. What are the two types of cursors?

Answer:

Explicit Cursors – a programmer declares and names an explicit cursor for queries that return more than one row. The following 4 steps are followed in using explicit cursor in PL/SQL:

  • Declare the cursor
  • Open the cursor
  • Fetch rows from the cursor
  • Close the cursor


Implicit Cursors – these are cursors that are automatically created by PL/SQL without defining, when any SQL statement is executed. The following are statements where implicit cursors are employed:

  • UPDATE
  • SELECT
  • DELETE
  • INSERT


8. Name the predefined data types available in PL/SQL

Answer:

  • Composite Data Types
  • Large Object Data Types
  • Scalar Data Types
  • Reference Data Types


9. What are the examples of Predefined Exceptions?

Answer:

  • TOO MANY ROWS – refers to a single row SELECT statement that returns many rows.
  • ZERO DIVIDE – this is an attempt at zero division.
  • NO DATA FOUND – a single row SELECT statement that returns no data.
  • INVALID CURSOR – an incorrect cursor operation is performed


10. What are the types of SQL statements?

Answer:

  • DML (Data Manipulation Language): makes it possible for you to change, insert and delete data from a database instance.
  • DDL (Data Definition Language): it helps in the creation of a database structure or schema.
  • DCL (Data Control Language): GRANT and REVOKE are the commands used here to grant rights and permissions.
  • DQL (Data Query Language): it is used to retrieve data from the database. There is only one command here, which is SELECT.
  • TCL (Transaction Control Language): it has commands which deal with database transactions. Some of those commands are ROLLBACK, SAVEPOINT and COMMIT.


11. What are the advantages of stored procedures?

Answer:

  • Advance security
  • Easy to use
  • Improved performance
  • Higher productivity
  • Replication
  • Interoperability
  • Increased scalability


12. What are the various types of Schema objects that can be created by PL/SQL?

Answer:

  • View
  • Database links
  • Cursors
  • Table
  • Stored procedures, functions and packages
  • Object types, object views and object tables
  • Database triggers


13. Define a subquery and name its types

Answer: a subquery is a query within another query. There are two types of subqueries:

  • Correlated
  • Non-correlated


14. What are the 3 modes of parameters?

Answer:

  • IN parameters – allows you to pass values to the procedure being called and can be initialized to default values.
  • OUT parameters – they return value to the caller and they must be specified.
  • IN OUT parameters – passes initial values to a procedure and return updated values to the caller.


15. What is the difference between SQL and PL/SQL?

Answer:

SQL

PL/SQL

Does not support error handling mechanism

Supports error handling mechanism

It is a natural language responsible for the interactive processing of data in the database

It is a procedural extension of SQL

Decision-making and looping are not allowed

It supports all features of procedural language such as conditional and looping statements

Statements are executed one at a time

Statements are executed one block at a time



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

Answer: a constraining table is a table that is used for reading with the intention of referential integrity constraint while mutating table is a table that is being currently modified by the usage of the DML statement.

 

17. What is the purpose of WHEN clause in the trigger?

Answer: WHEN clause specifies for what reason or condition the trigger has to be triggered.

 

18. What are the 3 types of exceptions?

Answer

  • Pre-defined exceptions – these are common errors that are already defined.
  • Undefined exceptions – are errors that do not have predefined names.
  • User-defined exceptions – errors handled by the code written by the user.


19. What are the differences between constraints and triggers?

Answer:

Constraints

Triggers

Stored along with the table definition

Stored as separate objects

Is for a column of the table

Is for entire table

Are fired as soon as the table is used

Are fired upon an event

 
20. What are the different types of constraints?

Answer:
  • Check
  • Primary key
  • Not NULL
  • Foreign key
  • Unique

I hope you have enjoyed going through the above mentioned questions and you have indeed acquired the exact knowledge you have been yearning to acquire. PL/SQL is something that you can learn with ease in your comfort zone and I think by going through those questions you can completely agree with me.

Just keep on practicing and you will be at that level you desire and deserve to be as far as PL/SQL interview is concerned. Now it is up to you to make it big for yourself. I wish you nothing but good luck in your interview.


Other related SQL queries, Interview questions, and articles:
  • How to find the second highest salary in a table? (solution)
  • 50 Microsoft SQL Server interview questions (SQL server questions)
  • 5 Courses to learn Database and SQL Better (courses)
  • Top 5 Websites to learn SQL online for FREE? (resource)
  • Write a SQL query to copy or backup a table in MySQL (solution)
  • How to migrate SQL queries from Oracle to SQL Server 2008? (answer)
  • 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
  • 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
  • What is the difference between UNION and UNION ALL in SQL? (answer)
  • Difference between clustered and non-clustered indexes in SQL? (answer)
  • Top 5 Courses to learn MySQL Database for Beginners (Courses)
  • Difference between the Unique and Primary keys in the table? (answer)
  • 5 Best PostgreSQL Courses for Beginners (online courses)
  • Difference between View and Materialized View in Database? (answer)
  • Top 5 Books to learn Advanced SQL and Database Design (books)
  • 10 Free SQL and Database Courses for Beginners (free courses)

Thanks for reading this article so far. If you like these  common PL/SQL Interview questions answers then please share them with your friends and colleagues. If you have any questions or feedback then please drop a note.

No comments:

Post a Comment