Database and SQL

With conformed dimensions, the best practice is to query each fact table separately against the shared dimension (individual queries). A single query joining both facts directly would create a snowflake pattern that can cause performance issues and incorrect results due to fact table explosion (cartesian product of fact rows).

AUD$ is the audit trail table in Oracle that can be modified, typically for maintenance purposes like truncating old audit records. Other SYS tables like COL$ and ROW$ are data dictionary tables that should never be directly modified.

In Oracle SQL, table/column names cannot be reserved words (like SELECT, FROM, etc.) without quoting. Column aliases (defined in SELECT clause) cannot be used in WHERE clause because WHERE is processed before SELECT. Names are case-insensitive and must start with a letter.

DML (Data Manipulation Language) commands operate on existing data within database objects. SELECT retrieves data, INSERT adds new rows, UPDATE modifies existing data, and DELETE removes rows. CREATE and ALTER are DDL (Data Definition Language) commands that define or modify database structures, not manipulate data.

Indexes are beneficial when columns contain wide range of values (high cardinality), when tables are large (performance impact is significant), and when columns contain many NULL values (indexes can efficiently skip NULLs). Columns that are infrequently used don't benefit from indexing as the query performance gain wouldn't justify the storage and maintenance overhead.

Column settings () are more specific than row settings (), so they take precedence. If both row and column define conflicting attributes, the column/cell setting wins. This follows standard CSS cascade rules.

EXCPT (Exception Output) in RPG is specifically designed to update only selected fields on a record when certain conditions are met, rather than writing entire records. EXFMT writes formatted display records, UPDAT is not a valid RPG operation code, and WRITE creates new records rather than updating existing ones.

An index scan occurs when DB2 accesses and searches the index structure itself to locate the requested rows, rather than scanning the entire tablespace.

A composite key is a key that consists of two or more columns used together to uniquely identify a row in a table. Unlike a simple key that uses a single column, composite keys combine multiple columns to ensure uniqueness when no single column can uniquely identify records.

The WITH HOLD option in cursor declaration maintains the cursor position across transaction boundaries. When a transaction commits, a WITH HOLD cursor remains open and positioned, allowing continued fetching without repositioning. FOR FETCH is not a valid DB2 cursor option.

SQLCODE -803 in DB2 indicates a duplicate key violation, which occurs when an INSERT or UPDATE tries to create a duplicate value in a column with a unique constraint or index. This error specifically signals that the inserted data violates uniqueness requirements.

The four DML (Data Manipulation Language) statements in DB2 are SELECT (retrieve), UPDATE (modify existing), DELETE (remove), and INSERT (add new). These are the core statements for modifying table data.

Dynamic SQL statements are prepared and bound dynamically at run time, whereas static SQL statements are embedded and bound before execution. Therefore, the statement is false.