logo
Product
Product Tour
Aptitude Tests Coding Tests Psychometric Tests Personality Tests
Campus Hiring Features Proctoring Enterprise
Test Library Questions Pricing
Resources
Blog Case studies Books Tools
About us
Login
Log In

Search test library by skills or roles
⌘ K
logo
Assessment Platform Aptitude Tests Coding Tests Psychometric Tests Personality Tests

TRY FOR FREE

PL/SQL Interview Questions
  1. What are Constraints?
  2. What is ROLLBACK?
  3. What is the mutating table?
  4. What is Exception handling in PL/SQL?
  5. What are expressions?
  6. What is database trigger?
  7. What are sequences?
  8. What is Data Consistency?
  9. What are Anonymous blocks?
  10. What is a Join?
  11. What is a View?
  12. What is a Literal?
  13. What is a Dual Table?
  14. What is a Sub Query?
  15. What is ALL operator?
  16. What is a MERGE statement?
  17. What are Joins?
  18. What is a Tuple?
  19. What is locking in SQL?
  20. What is a SET UNUSED option?
  21. Explain Truncate.
  22. What are Constraints?
  23. What is a Candidate Key?
  24. What are Triggers?
  25. What is the Discard File?
  26. What are Global Variables?
  27. What is Forward Declaration?
  28. What is an index?
  29. What are predefined exceptions?
  30. What are Character Functions?
  31. What is Merge?
  32. What is rollback?


Interview Questions

PL/SQL Interview Questions and Answers (2023)

In this post, we put together the top PL/SQL interview questions and answers for beginner, intermediate and experienced candidates. These most important questions are categorized for quick browsing before the interview or to act as a detailed guide on different topics in PL/SQL interviewers look for.

PL/SQL Online Test

PL/SQL Interview Questions

What are Constraints?

View answer

Hide answer

Constraints are the rules enforced on the data columns of a table. These are used to limit the type of data that can go into a table.

What is ROLLBACK?

View answer

Hide answer

The ROLLBACK statement is the inverse of the COMMIT statement. It undoes some or all database changes made during the current transaction.

What is the mutating table?

View answer

Hide answer

A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint.

What is Exception handling in PL/SQL?

View answer

Hide answer

Exception handling is a mechanism that is implemented to deal with runtime errors. PL/SQL provides the exception block that raises the exception, thus helping the programmer to find the fault and resolve it. When an error occurs, the program’s error handling code is included.

What are expressions?

View answer

Hide answer

An expression is an arbitrarily complex combination of operands (variables, constants, literals, operators, function invocations, and placeholders) and operators.

What is database trigger?

View answer

Hide answer

A PL/SQL trigger is a named database object that encapsulates and defines a set of actions that are to be performed in response to an insert, update, or delete operation against a table.

What are sequences?

View answer

Hide answer

A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

What is Data Consistency?

View answer

Hide answer

Data consistency means that each user sees a consistent view of the data, including visible changes made by the user's own transactions and transactions of other users.

What are Anonymous blocks?

View answer

Hide answer

The PL/SQL anonymous block statement is an executable statement that can contain PL/SQL control statements and SQL statements.

What is a Join?

View answer

Hide answer

Oracle JOINS are used to retrieve data from multiple tables. An Oracle JOIN is performed whenever two or more tables are joined in a SQL statement.

What is a View?

View answer

Hide answer

A View logically represents subsets of data from one or more tables. A View is a logical table based on a table or another view. A View contains no data of its own but is like a window through which data from tables can be viewed or changed.

What is a Literal?

View answer

Hide answer

The terms literal and constant value are synonymous and refer to a fixed data value. For example, 'JANE', 'PINK FLOYD', and '208' are all character literals; 2002 is a numeric literal. Character literals are enclosed in single quotation marks so that Oracle can distinguish them from schema object names.

What is a Dual Table?

View answer

Hide answer

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY , defined to be VARCHAR2(1) , and contains one row with a value X .

What is a Sub Query?

View answer

Hide answer

A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. A subquery can be placed in WHERE HAVING and FROM clause.

What is ALL operator?

View answer

Hide answer

ALL Operator compares value to every value returned by the subquery.

ALL means more than the maximum < ALL means less than the minimum <> ALL is equivalent to NOT IN condition.

What is a MERGE statement?

View answer

Hide answer

The Oracle MERGE statement selects data from one or more source tables and updates or inserts it into a target table. The MERGE statement allows you to specify a condition to determine whether to update data from or insert data into the target table.

What are Joins?

View answer

Hide answer

Oracle JOINS are used to retrieve data from multiple tables. An Oracle JOIN is performed whenever two or more tables are joined in a SQL statement.

What is a Tuple?

View answer

Hide answer

A tuple is a way to refer to a member or a member combination from any number of dimensions.

What is locking in SQL?

View answer

Hide answer

Locking prevents destructive interaction between concurrent transactions. Locks held until Commit or Rollback.

Types of locking are:

  • Implicit Locking: This occurs for all SQL statements except SELECT.
  • Explicit Locking: This can be done by the user manually.

What is a SET UNUSED option?

View answer

Hide answer

SET UNUSED option marks one or more columns as unused so that they can be dropped when the demand on system resources is lower. Unused columns are treated as if they were dropped, even though their column data remains in the table’s rows. After a column has been marked as unused, you have no access to that column.

Explain Truncate.

View answer

Hide answer

Truncate removes all rows from a table and releases storage space used by that table.

What are Constraints?

View answer

Hide answer

Constraints are used to prevent invalid data entry or deletion if there are dependencies. Constraints enforce rules at the table level. Constraints can be created either at the same time as the table is created or after the table has been created. Constraints can be defined at the column or table level. Constraint defined for a specific table can be viewed by looking at the USER-CONSTRAINTS data dictionary table. You can define any constraint at the table level except NOT NULL which is defined only at the column level.

There are 5 types of constraints:

  • Not Null Constraint
  • Unique Key Constraint
  • Primary Key Constraint
  • Foreign Key Constraint
  • Check Key Constraint

What is a Candidate Key?

View answer

Hide answer

A candidate key is a key that uniquely identifies rows in a table. Any of the identified candidate keys can be used as the table's primary key. Candidate keys that are not part of the primary key are called alternate keys.

What are Triggers?

View answer

Hide answer

Triggers are stored programs, which are automatically executed or fired when some events occur.

What is the Discard File?

View answer

Hide answer

The DISCARD FILE command removes the definition of a file from the local CICS system, so that the system no longer has access to the file; that is, it revokes the earlier installation of a FILE resource definition of the same name. A file must be closed and disabled for its definition to be discarded.

What are Global Variables?

View answer

Hide answer

A global variable is a data structure that can be referenced outside the scope or block in which it’s declared.

What is Forward Declaration?

View answer

Hide answer

Forward declaration/reference means declaring the private elements separately and defining it in the later part of the package body. Private elements can be referred only if it is already declared in the package body. For this reason, forward declaration is used.

What is an index?

View answer

Hide answer

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.

What are predefined exceptions?

View answer

Hide answer

Predefined exceptions are internally defined exceptions that occur during the execution of a program.

Some examples: TOO_MANY_ROWS: if more than one row is returned using a select statement NO_DATA_FOUND: when there are no rows returned upon a select operation COLLECTION_IS_NULL: when a collection is null CURSOR_ALREADY_OPEN: When a cursor is already open LOGIN_DENIED: When login is incorrect or permission is not there

What are Character Functions?

View answer

Hide answer

What is Merge?

View answer

Hide answer

You can use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view. This statement is a convenient way to combine multiple operations.

What is rollback?

View answer

Hide answer

Rollback erases all the database changes including all the savepoints. It ends a transaction.

Other Interview Questions

ReactJS

Business Analyst

Android

Javascript

Power BI Django .NET Core
Java 8 R PostgreSQL
Spring Boot Drupal TestNG
Check Other Interview Questions
Join 1200+ companies in 75+ countries.
Try the most candidate friendly skills assessment tool today.
GET STARTED FOR FREE
40 min tests.
No trick questions.
Accurate shortlisting.

[email protected]

Product
  • Product Tour
  • Pricing
  • Features
  • Integrations
Usecases
  • Aptitude Tests
  • Coding Tests
  • Psychometric Tests
  • Personality Tests
Helpful Content
  • 52 pre-employment tools compared
  • Compare Adaface
  • Compare Codility vs Adaface
  • Compare HackerRank vs Adaface
  • Compare Mettl vs Adaface
BOOKS & TOOLS
  • Guide to pre-employment tests
  • Check out all tools
Company
  • About Us
  • Join Us
  • Blog
Locations
  • Singapore (HQ)

    32 Carpenter Street, Singapore 059911

    Contact: +65 9447 0488

  • India

    WeWork Prestige Atlanta, 80 Feet Main Road, Koramangala 1A Block, Bengaluru, Karnataka, 560034

    Contact: +91 6305713227

© 2022 Adaface Pte. Ltd.
Terms Privacy Trust Guide

🌎 Pick your language

English Norsk Dansk Deutsche Nederlands Svenska Français Español Chinese (简体中文) Italiano Japanese (日本語) Polskie Português Russian (русский)
Search 500+ tests by skill or role name