Database and SQL

PL/SQL subqueries can be nested up to 254 levels deep. This limitation prevents excessive nesting that could impact performance and code maintainability.

PL/SQL program blocks can be nested up to 32 levels deep. This structural limit ensures reasonable program complexity and prevents stack overflow issues.

PL/SQL allows up to 255 levels of block nesting within a program unit. This generous limit accommodates complex logic structures while maintaining system stability.

PL/SQL does not natively support executing operating system commands. Database security prevents direct OS access from within PL/SQL blocks.

SEARCH ALL performs a binary search in COBOL tables. For predictable results, the table must be defined with ASCENDING or DESCENDING KEY, and the key must uniquely identify each table entry. Without these conditions, binary search behavior is undefined.

The SELECT statement in SQL supports three fundamental capabilities: Selection (filtering rows with WHERE), Projection (choosing columns), and Joining (combining data from multiple tables). These are the core operations that make SELECT versatile. Option D is incorrect because all three listed capabilities are valid and fundamental to SQL queries.

To join n tables together, you need a minimum of n-1 join conditions. Each join condition links two tables together, creating a chain. For example, joining 3 tables requires 2 join conditions (Table1 join Table2, Table2 join Table3). Option A (N-2) is insufficient and Option B (N) would over-join. Option D is incorrect because the formula is precisely n-1.

Equijoins, which join tables based on equality of column values, are also referred to as simple joins or inner joins. They return only matching rows from both tables. Option B is incorrect because outer joins return all rows from one or both tables, not just matches. Option D is wrong because equijoins are specifically inner joins, not outer joins.

In SQL, group functions (like SUM or AVG) cannot be evaluated in the WHERE clause, and the WHERE clause itself cannot filter grouped results. The HAVING clause must be used instead, making both selected options correct descriptions of illegal queries.

The query SELECT address1||','||address2||','||address2 creates only 1 column. The || operator concatenates strings, and the expression address1||','||address2||','||address2 concatenates these fields into a single column named 'Adress'. Even though multiple address fields are used, they are combined into one output column.

ROWID is the physical address of a row in the database, representing its exact disk location. Accessing by ROWID is the fastest method because it bypasses all index lookups and goes directly to the data block. Primary key access and unique index access require first traversing the index structure to find the ROWID, then accessing the row. Full table scan is the slowest as it reads every row in the table.

In Oracle, standard unquoted identifiers must begin with a letter (A-Z) and can contain only letters, digits, and underscores. Option C is the only valid identifier that follows these rules. Option A uses a reserved word (though it would work if quoted). Option B starts with a digit which is invalid. Option D starts with # which is not allowed in unquoted identifiers. E is incorrect since C is valid.

The TRUNCATE command is a DDL statement that removes all data from a table without logging individual row deletions in the transaction rollback segments, making it faster and non-rollbackable. DELETE is a DML statement that writes to rollback segments.

When a column is wrapped in a function like NVL(), the Oracle optimizer cannot use the index on that column because the function modifies the column value before comparison. For options B, C, and D, EMPNO is compared directly to a literal value, allowing index access. Option A applies NVL() to EMPNO first, making the index unusable and forcing a full table scan. This is a common performance anti-pattern.