Database and SQL

Oracle SQL supports arithmetic functions like ADD (addition), POWER (raising to a power), and MIN/LEAST (finding lowest value). Subtraction and division operations are typically done with operators (- and /) rather than built-in functions.

Subqueries can include GROUP BY and ORDER BY clauses, can access different tables than the main query, and support comparing multiple columns between main and subquery through multicolumn subqueries.

Views using ROWNUM cannot be updated because the row numbers are generated dynamically, preventing direct row identification. Performing DML on a view with all NOT NULL columns from the base table is allowed because it satisfies the constraint integrity rules of the underlying table.

INTERSECT returns rows common to both SELECT statements. It requires identical column counts and matching data types (D is true). Column names don't need to match (C is false). NULLs are treated like other values (A is false). INTERSECT is commutative (B is false).

By definition, a primary key constraint uniquely identifies each record in a database table. Therefore, the column or set of columns designated as the primary key must contain unique values and cannot contain null values.

Triggers can enforce referential integrity by executing custom logic before or after INSERT/UPDATE/DELETE operations. They can validate data, prevent invalid operations, or cascade changes similar to foreign key constraints. While foreign keys are declarative and preferred, triggers offer programmatic control for complex scenarios.

RAISE_APPLICATION_ERROR in Oracle allows user-defined error numbers from -20000 to -20999, giving developers 1000 error codes for custom exceptions. This range is reserved specifically for application-defined errors. Error numbers outside this range are reserved for Oracle system errors.

ALTER SESSION SET NLS_DATE_FORMAT changes the date format for the current session only. NLS (National Language Support) parameters control locale-specific formatting. The format string uses codes like 'DD-MON-YYYY' or 'MM/DD/YYYY'. This affects how dates are displayed and converted, not how they're stored.

In PL/SQL, VARCHAR2 can hold up to 32767 bytes (32KB), which is much larger than the SQL limit of 4000 bytes. This extended capacity is useful for procedural code processing larger text strings. Options like 1024 or 255 are outdated limits from earlier versions or different contexts.

SELECT and EXCEPTION are both valid PL/SQL reserved words. SELECT is used for querying data, and EXCEPTION is used in exception handling blocks. The other options contain invalid words: INOUT is not a reserved word (it's a parameter mode), PROC is not reserved (PROCEDURE is), and MULTI_ROW is not a PL/SQL reserved term.

SET AUTOTRACE ON displays both the execution plan (EXPLAIN) and statistics (STATISTICS) for SQL queries. The ON option combines both tracing capabilities. TRACEONLY suppresses query output, ON STATISTICS shows only statistics, and ON EXPLAIN shows only the execution plan - but ON gives you everything.

In PL/SQL, <> and >> are used to define label delimiters, which mark named blocks for use with GOTO statements or to label loops for nested loop control. The << marks the start of a label and >> marks the end. This is distinct from comment markers (-- or /* */) or comparison operators like >= or !=.

In PL/SQL, the implicit cursor attribute SQL%ROWCOUNT returns the exact number of rows affected by the last executed DML statement. SQL%FOUND returns a boolean, while SQL%COUNT and SQL%NUM_ROWS are not valid PL/SQL implicit cursor attributes.

In relational databases, the order of rows in a table is arbitrary and not guaranteed - rows have no inherent positional order. Teradata tables can contain only one row format per table, which is a design constraint for performance and simplicity. Option C incorrectly ties row order to Primary Index, and option D is false because Teradata does not support multiple row formats in a single table.