Database and SQL

ROWID is the direct physical address of a row's location on disk, making it the fastest access method. Primary key and unique index accesses require an index lookup first, then ROWID access. Full table scan reads every row in the table, making it the slowest method.

SQL's declarative nature allows the same query to be written in multiple syntactically different ways, which can result in vastly different execution plans and performance. This is a well-known challenge in SQL optimization. SQL does support object-oriented features through extensions, its syntax is widely used by non-programmers, and locking behavior is configurable.

SEARCH operations do not require an index - a database can perform a full table scan to find matching rows. However, searches are much faster and more efficient with an index. Without an index, the database must examine every row in the table, which is slow for large tables but still functional.

Running validation on the APPS schema is most critical after applying patches (when schema changes are most likely) and when encountering run-time errors suggesting AD_DDL package issues (indicating potential corruption). The other options like before export/import or during organizational conversion are not the primary validation triggers - validation should be done after changes, not before them.

DECLARE TABLE in DCLGEN is not mandatory because programmers can manually write host variable declarations in their COBOL or PL/I programs. DCLGEN is a convenience tool that generates declarations automatically, but it's optional.

In DB2 SQL, the SQLCODE -922 indicates an authorization failure, typically signifying that the user does not have permission to perform the requested operation or connect to the plan.

DECLARE CURSOR is a declarative SQL statement, not executable. It only defines the cursor and its associated SELECT statement for later use. The actual execution happens when you OPEN the cursor, not when you declare it.

In DB2 and many other SQL implementations, subqueries cannot directly contain UNION operations. This is a syntax restriction. To combine results from multiple queries within a subquery, you would typically need to use alternative approaches like UNION ALL in a derived table or restructure the query.

SQLCODE -530 in DB2 indicates a foreign key constraint violation. This error occurs when you try to insert or update a child row with a foreign key value that doesn't exist in the parent table, or when deleting a parent row that has dependent child rows.

The INSERT statement uses incorrect syntax - it should use VALUES keyword: INSERT INTO temp VALUES ('abc',100). Without VALUES, Oracle will throw a syntax error. The savepoint and rollback to savepoint would work correctly if the INSERT syntax was proper, but the error occurs before any savepoint operations.

INTERSECT returns rows common to both queries. INNER JOIN returns matching rows from both tables based on a join condition. When you add DISTINCT to INNER JOIN, it removes duplicates, making it functionally equivalent to INTERSECT if the join conditions match on all columns. The other options are incorrect - UNION combines all rows (including duplicates), and joins are not equivalent to basic set operations.

To delete duplicates, you keep one instance (usually the one with max rowid) and delete the rest. Option A correctly identifies rows where rowid is NOT IN the maximum rowid for each duplicate group. Options B and C would keep the wrong records or delete all records. Option D is incorrect because selecting rowid directly without an aggregate in the GROUP BY clause won't identify the max per group.

MOD(rownum,2) returns 1 for odd-numbered rows (1,3,5...) and 0 for even-numbered rows (2,4,6...). By checking if (rowid,1) exists in the subquery that pairs each rowid with mod(rownum,2), we select only rows where rownum is odd. Option B checks for 0 (even rows), Option C uses mod(rownum,1) which is always 0, and Option D uses mod(rownum,3) which doesn't identify odd/even.

When a subquery returns multiple rows, using the equals operator (=) causes an error. Changing to IN allows matching against any value in the returned set. Option A's DISTINCT would affect duplicates but not the multi-row error. Option C's MAX would change the logic to only match one city. Option D's ORDER BY change doesn't address the core issue.

A primary key is a column or set of columns that uniquely identifies each row in a table. By definition, primary key columns cannot contain NULL values. This is a fundamental constraint enforced by databases to ensure data integrity and uniqueness.