TCS Oracle PL/SQL Wings1 Preparation: Top DCA MCQs with Detailed Explanation
Q 1. What is a function in
PL/SQL?
A) A database object used to store query results
B) A temporary work area used to store a set of rows from a SQL statement
C) A PL/SQL program that accepts input parameters and returns a single value
D) A database object that fires in response to a specific event or change in
the database
Answer: C
Explanation: A function in PL/SQL is a reusable program that accepts input parameters
and returns a single value. It is often used for calculations and processing on
data.
Q 2. What is the purpose of a join in
PL/SQL?
A) To combine data from two or more tables based on
a related column between them
B) To create a temporary work area used to store a set of rows from a SQL
statement
C) To execute a PL/SQL program that accepts input parameters and returns a
single value
D) To create a database trigger that fires when a specified event occurs
Answer: A
Explanation: The purpose of a join in PL/SQL is to combine data from two or
more tables based on a related column, allowing you to retrieve related
information from multiple tables in a single query.
Q 3. What is a cursor in PL/SQL?
A) A database object used to manipulate data
B) A control structure used to iterate over a set of rows returned by a SELECT
statement
C) A function used to perform calculations on a set of data
D) A trigger used to enforce constraints on data
Answer: B
Explanation: A cursor in PL/SQL is a control structure that allows you to
retrieve and process a set of rows returned by a SELECT statement. It provides
a way to work with query results row by row.
Q 4. What is a correlated subquery in
PL/SQL?
A) A subquery that can reference a column from a
table in the outer query
B) A subquery that cannot reference any columns from the outer query
C) A subquery that can only be used with the EXISTS operator
D) A subquery that can only be used with the IN operator
Answer: A
Explanation: A correlated subquery in PL/SQL is a subquery that can reference
columns from a table in the outer query. This allows the subquery to be
dependent on the outer query, enabling more advanced filtering and comparisons.
Q 5. What is a trigger in PL/SQL?
A) A database object used to store query results
B) A temporary work area used to store a set of rows from a SQL statement
C) A trigger is a stored PL/SQL program unit associated with a specific
database table, view, schema, or the database.
D) A trigger is a stored PL/SQL program unit associated with a specific
database table, view, schema, or the database.
Answer: C
Explanation: A trigger in PL/SQL is a stored program unit that is associated
with a specific database table, view, schema, or the database itself. It is
automatically executed when a specified event occurs.
Q 6. What is a bulk collect in PL/SQL?
A) A feature used to execute a single SQL statement
B) A feature used to iterate through a set of rows returned by a SELECT
statement
C) A feature used to retrieve multiple rows at once from a cursor or query
D) A feature used to create a temporary work area used to store a set of rows
from a SQL statement
Answer: C
Explanation: A bulk collect in PL/SQL is a feature that allows you to retrieve
multiple rows from a cursor or query into a collection all at once. This can
improve performance by reducing the number of context switches between the
database and PL/SQL engine.
Q 7. What is a cursor FOR loop in
PL/SQL used for?
A) To execute a single SQL statement
B) To execute a PL/SQL program that accepts input parameters and returns a
single value
C) To iterate through a set of rows returned by a SELECT statement
D) To create a temporary work area used to store a set of rows from a SQL
statement
Answer: C
Explanation: A cursor FOR loop in PL/SQL is used to iterate through a set of
rows returned by a SELECT statement. It simplifies the process of fetching and
processing rows without explicitly opening and closing cursors.
Q 8. What is a mutating table error in
PL/SQL?
A) A mutating table error occurs when a trigger
tries to modify a table that is currently being modified by another
transaction.
B) A mutating table error occurs when a function returns a value that is not
compatible with the calling program.
C) A mutating table error occurs when a cursor is used to retrieve data from a
table that has been modified by another transaction.
D) A mutating table error occurs when a join operation returns too many rows.
Answer: A
Explanation: A mutating table error in PL/SQL occurs when a trigger tries to
modify a table that is currently being modified by another transaction. It's a
safeguard to prevent inconsistent data caused by simultaneous modifications.
Q 9. Which keyword is used to define a
trigger in PL/SQL?
a) BEGIN
b) TRIGGER
c) DECLARE
d) PROCEDURE
Answer: b)
Explanation: The keyword used to define a trigger in PL/SQL is
"TRIGGER." It is followed by the trigger's name, event, and
associated code that should be executed when the trigger event occurs.
Q 10. What is the difference between an
implicit cursor and an explicit cursor in PL/SQL?
A) An implicit cursor is created automatically by
the database when a SQL statement is executed, while an explicit cursor is
defined by the programmer.
B) An implicit cursor is used to retrieve a single row from a table, while an
explicit cursor is used to retrieve multiple rows.
C) An implicit cursor is used for DML statements, while an explicit cursor is
used for SELECT statements.
D) An implicit cursor is closed automatically by the database when the SQL
statement completes, while an explicit cursor must be closed by the programmer.
Answer: A
Explanation: An implicit cursor is automatically created and managed by the
database when executing a SQL statement. An explicit cursor, on the other hand,
is defined by the programmer and offers more control over fetching and
processing query results.
Q 11. Which of the following is not a
type of join in PL/SQL?
a) INNER JOIN
b) OUTER JOIN
c) CROSS JOIN
d) UNION JOIN
Answer: d)
Explanation: "UNION JOIN" is not a valid type of join in PL/SQL. The
correct types are INNER JOIN, OUTER JOIN (which includes LEFT, RIGHT, and FULL
OUTER), and CROSS JOIN.
Q 12. Which of the following is not a
type of cursor in PL/SQL?
A. Implicit cursor
B. Explicit cursor
C. Dynamic cursor
D. Static cursor
Answer: C
Explanation: All the options provided are types of cursors in PL/SQL except for
"Dynamic cursor."
Q 13. What is a cursor in PL/SQL?
a) A database object used to manipulate data
b) A control structure used to iterate over a set of rows returned by a SELECT
statement
c) A function used to perform calculations on a set of data
d) A trigger used to enforce constraints on data
Answer: b
Explanation: In PL/SQL, a cursor is a control structure that enables you to
retrieve and process rows returned by a SELECT statement. It provides a way to
work with query results row by row.
Q 14. What is a trigger in PL/SQL?
A) A database object used to store query results
B) A temporary work area used to store a set of rows from a SQL statement
C) A trigger is a stored PL/SQL program unit associated with a specific
database table, view, schema, or the database.
D) A trigger is a stored PL/SQL program unit associated with a specific
database table, view, schema, or the database.
Answer: C
Explanation: A trigger in PL/SQL is a stored program unit associated with a
specific database table, view, schema, or the database itself. It automatically
executes when a defined event occurs, like an INSERT, UPDATE, DELETE, or other
database actions.
Q 15. What is a correlated subquery in
PL/SQL?
A) A subquery that can reference a column from a
table in the outer query
B) A subquery that cannot reference any columns from the outer query
C) A subquery that can only be used with the EXISTS operator
D) A subquery that can only be used with the IN operator
Answer: A
Explanation: A correlated subquery in PL/SQL is a subquery that can reference
columns from the outer query. It is used for complex filtering and comparison
between the outer query and the subquery's results.
Q 16. What is a bulk collect in PL/SQL?
A) A feature used to execute a single SQL statement
B) A feature used to iterate through a set of rows returned by a SELECT
statement
C) A feature used to retrieve multiple rows at once from a cursor or query
D) A feature used to create a temporary work area used to store a set of rows
from a SQL statement
Answer: C
Explanation: In PL/SQL, a bulk collect is a feature that lets you retrieve
multiple rows from a cursor or query into a collection at once. This enhances
performance by reducing context switches between the database and PL/SQL
engine.
Q 17. What is a mutating table error in
PL/SQL?
A) A mutating table error occurs when a trigger
tries to modify a table that is currently being modified by another
transaction.
B) A mutating table error occurs when a function returns a value that is not
compatible with the calling program.
C) A mutating table error occurs when a cursor is used to retrieve data from a
table that has been modified by another transaction.
D) A mutating table error occurs when a join operation returns too many rows.
Answer: A
Explanation: A mutating table error in PL/SQL happens when a trigger attempts
to modify a table that's being changed by another transaction simultaneously.
It prevents data inconsistency due to concurrent modifications.
Q 18. Which keyword is used to define a
trigger in PL/SQL?
a) BEGIN
b) TRIGGER
c) DECLARE
d) PROCEDURE
Answer: b)
Explanation: The keyword used to define a trigger in PL/SQL is
"TRIGGER." It is followed by the trigger's name, event, and
associated code that should be executed when the trigger event occurs.
Q 19. What is the difference between an
implicit cursor and an explicit cursor in PL/SQL?
A) An implicit cursor is created automatically by
the database when a SQL statement is executed, while an explicit cursor is
defined by the programmer.
B) An implicit cursor is used to retrieve a single row from a table, while an
explicit cursor is used to retrieve multiple rows.
C) An implicit cursor is used for DML statements, while an explicit cursor is
used for SELECT statements.
D) An implicit cursor is closed automatically by the database when the SQL
statement completes, while an explicit cursor must be closed by the programmer.
Answer: A
Explanation: An implicit cursor is automatically created and managed by the
database when executing a SQL statement. An explicit cursor, on the other hand,
is defined by the programmer and offers more control over fetching and
processing query results.
Q 20. Which of the following is not a
type of join in PL/SQL?
a) INNER JOIN
b) OUTER JOIN
c) CROSS JOIN
d) UNION JOIN
Answer: d)
Explanation: "UNION JOIN" is not a valid type of join in PL/SQL. The
correct types are INNER JOIN, OUTER JOIN (which includes LEFT, RIGHT, and FULL
OUTER), and CROSS JOIN.