OCP SQL & PL/SQL Programming 1 — Questions and Answers

Question 1: What is the function of a cursor in PL/SQL?

It stores query results
It holds user inputs
It points to the data being queried (Correct answer)
It handles exceptions

Correct answer: It points to the data being queried

In PL/SQL, a cursor is a pointer or a handle to a private SQL area in memory where a SQL statement is processed. When a SQL query is executed, a cursor is implicitly or explicitly declared to manage the rows returned by the query. It allows the program to fetch and process these rows one by one, providing control over the result set.

Question 2: Which PL/SQL block is used to handle exceptions?

BEGIN-END
EXCEPTION (Correct answer)
DECLARE-BEGIN
LOOP-END LOOP

Correct answer: EXCEPTION

In PL/SQL, the `EXCEPTION` keyword introduces the exception handling section of a block. This section is where you define specific actions to be taken when a runtime error occurs within the `BEGIN-END` block. It allows for graceful error management, preventing the program from terminating abruptly and ensuring robust application behavior.

Question 3: Which SQL clause is used to filter results based on specific conditions?

FROM
WHERE (Correct answer)
ORDER BY
HAVING

Correct answer: WHERE

The `WHERE` clause in SQL is fundamental for filtering rows returned by a query. It evaluates a specified condition for each row in the table(s) and includes only those rows for which the condition is true. This allows users to retrieve a subset of data that meets specific criteria, making queries more precise.

Question 4: What is the purpose of a trigger in PL/SQL?

To display results
To modify data manually
To automatically perform actions based on certain events (Correct answer)
To create a new table

Correct answer: To automatically perform actions based on certain events

A trigger in PL/SQL is a stored program that automatically executes or 'fires' in response to specific database events. These events can include DML operations (INSERT, UPDATE, DELETE) on a table, DDL operations, or database system events. Triggers are used to enforce complex business rules, audit data changes, or maintain data integrity automatically.

Question 5: Which command is used to exit from SQL*Plus?

EXIT (Correct answer)
QUIT
STOP
END

Correct answer: EXIT

The `EXIT` command is the standard and most direct way to terminate a SQL*Plus session. When executed, it closes the connection to the Oracle database and exits the SQL*Plus command-line interface. This returns control to the operating system, effectively ending the interactive session.

Question 6: What is the use of the SQL IN operator?

To filter by a range of values
To group similar records
To check if a value matches any in a list (Correct answer)
To find patterns in text data

Correct answer: To check if a value matches any in a list

The SQL `IN` operator is a logical operator used in a `WHERE` clause to check if a value matches any value in a specified list of values or a subquery. It provides a concise way to filter records where a column's value is one of several possible options. This makes queries more readable and efficient than using multiple `OR` conditions.

Question 7: What is the difference between a LEFT JOIN and a RIGHT JOIN in SQL?

There is no difference
LEFT JOIN returns all from left, RIGHT JOIN returns all from right (Correct answer)
LEFT JOIN is used for smaller datasets
RIGHT JOIN is used for data filtering

Correct answer: LEFT JOIN returns all from left, RIGHT JOIN returns all from right

A `LEFT JOIN` (or `LEFT OUTER JOIN`) returns all rows from the left table and the matching rows from the right table. If there's no match in the right table, NULLs are returned for its columns. Conversely, a `RIGHT JOIN` (or `RIGHT OUTER JOIN`) returns all rows from the right table and the matching rows from the left table, with NULLs for unmatched left table columns.

Question 8: What is a sequence in SQL?

A set of data from a table
A collection of rows in a database
A generator of unique numbers (Correct answer)
A temporary storage for data

Correct answer: A generator of unique numbers

In SQL, a sequence is a database object that automatically generates unique sequential numbers. These numbers are often used to populate primary key columns, ensuring that each new record has a distinct identifier. Sequences provide a simple, efficient, and concurrent way to generate unique values without manual intervention.

Question 9: What does the PL/SQL keyword 'EXCEPTION' handle?

It terminates the program
It stores data in a backup file
It handles runtime errors (Correct answer)
It stores user inputs

Correct answer: It handles runtime errors

The PL/SQL keyword `EXCEPTION` is used to define a section within a PL/SQL block dedicated to handling runtime errors. When an error occurs during the execution of the `BEGIN` section, control is transferred to the `EXCEPTION` section. This allows the program to gracefully manage the error, log it, or take corrective actions instead of crashing.

OCP SQL & PL/SQL Programming 1

What is the function of a cursor in PL/SQL?