Mixed Deck — All Oracle SQL Topics Flashcards

What is a deadlock in Oracle SQL?

Answer: A situation where two or more transactions are each waiting for the other to release locks, creating a circular wait

A deadlock occurs when two or more transactions each hold locks that the other needs, creating a circular wait that Oracle detects and resolves by rolling back one transaction.

Which of the following statements about subquery operators is correct?

Answer: The <ANY operator means less than the maximum.

The `<ANY` operator in SQL subqueries means that the value being compared is less than at least one of the values returned by the subquery. This effectively means the value is less than the maximum value present in the subquery's result set. For example, `salary < ANY (SELECT salary FROM employees WHERE department_id = 10)` would return employees whose salary is less than the highest salary in department 10.

What does the NULLIF(expr1, expr2) function return in Oracle SQL?

Answer: NULL if expr1 equals expr2, otherwise expr1

NULLIF returns NULL when both arguments are equal; otherwise it returns the first argument.

What does the TRUNC(45.789) function return in Oracle SQL?

Answer: 45

TRUNC without a second argument removes all decimal places, returning the integer portion 45 without rounding.

Which read phenomenon is prevented by Oracle's READ COMMITTED isolation level?

Answer: Dirty reads

READ COMMITTED prevents dirty reads because queries only see data that was committed before the query started, never uncommitted data from other transactions.

Which Oracle SQL analytic function calculates a running total?

Answer: SUM() OVER (ORDER BY)

SUM() used as an analytic function with OVER (ORDER BY column) computes a cumulative running total up to each row.

What does the LAG analytic function do in Oracle SQL?

Answer: Retrieves data from a previous row without a self-join

LAG(column, n) accesses data from a row n positions before the current row in the result set, useful for comparing sequential values.

Which type of JOIN returns only rows where there is a match in BOTH tables?

Answer: INNER JOIN

An INNER JOIN returns only those rows where the join condition is satisfied in both tables, excluding unmatched rows.

Which Oracle SQL multi-table INSERT directs rows to different tables based on conditions?

Answer: INSERT ALL WHEN

INSERT ALL with WHEN clauses conditionally inserts rows into different tables based on evaluated conditions, similar to a CASE statement.

Which privilege is required to create a table in another user's schema in Oracle SQL?

Answer: CREATE ANY TABLE

CREATE ANY TABLE privilege allows a user to create tables in any schema, not just their own.

Which Oracle SQL feature allows you to give a table a shorter name within a query?

Answer: Table alias

A table alias provides a short alternative name for a table within the query, improving readability and required for self-joins.

Which Oracle SQL function pads the left side of a string with a specified character to a given length?

Answer: LPAD

LPAD(string, length, pad_char) adds the pad character to the left of the string until it reaches the specified total length.

What does the PIVOT clause do in Oracle SQL?

Answer: Rotates rows into columns, converting distinct row values into column headers

The PIVOT clause transforms rows into columns by rotating distinct values of one column into separate column headings with aggregated values.

Which Oracle SQL clause creates a window frame for analytic functions based on a range of rows?

Answer: ROWS BETWEEN

ROWS BETWEEN n PRECEDING AND n FOLLOWING (or CURRENT ROW/UNBOUNDED) defines the sliding window of rows for analytic function calculations.

What does a LEFT OUTER JOIN return in Oracle SQL?

Answer: All rows from the left table and matching rows from the right

A LEFT OUTER JOIN returns all rows from the left table and NULL for unmatched columns from the right table.

Which Oracle SQL function converts a string to uppercase?

Answer: UPPER

The UPPER function converts all characters in a string to uppercase letters.

Choose the most appropriate response from the options provided below. What comprises a package?

Answer: Specification and body

An Oracle PL/SQL package is a schema object that groups related PL/SQL types, items, and subprograms. It is composed of two distinct parts: the package specification and the package body. The specification declares the public items and subprograms, while the body provides the detailed implementation for the subprograms declared in the specification.

What does SELECT * FROM employees WHERE manager_id IS NULL return?

Answer: Rows where manager_id has no value stored

IS NULL checks for the absence of a value; rows where manager_id has never been set (top-level managers) are returned.

What is a synonym in Oracle SQL?

Answer: An alternate name for a database object

A synonym is an alternative name for a database object (table, view, sequence, etc.) that simplifies access and hides the actual object name or owner.

What happens to all row-level locks held by a transaction when COMMIT is issued in Oracle?

Answer: All locks are immediately released

When a COMMIT is issued, Oracle releases all row-level and table-level locks acquired during the transaction, allowing other sessions to modify those rows.