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
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 |
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.
- 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.
P.S. - If you are interested in learning Database and SQL and looking for some free resources to start your journey then you can also take a look at this list of Free SQL Courses for Beginners to kick-start your learning.
No comments:
Post a Comment
Feel free to comment, ask questions if you have any doubt.