SQL & PL/SQL Programming Flashcards

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

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.

Which PL/SQL block is used to handle exceptions?

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.

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

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.

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

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.

Which command is used to exit from SQL*Plus?

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.

What is the use of the SQL IN operator?

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.

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

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.

What is a sequence in SQL?

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.

What does the PL/SQL keyword 'EXCEPTION' handle?

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.