Database and SQL

Memo is NOT an Oracle SQL data type. While some databases like Microsoft Access have a Memo type for large text, Oracle uses different data types for text storage: CLOB (Character Large Object) for large character data, VARCHAR2 for variable-length strings, and LONG (deprecated) for large text. The statement correctly identifies Memo as not being an Oracle data type.

DB2 Sequences are database objects specifically designed to generate numeric values according to defined rules, and they can be directly referenced in INSERT statements (via NEXT VALUE FOR or PREVIOUS VALUE FOR) to populate columns. While identity columns also generate values, sequences are more flexible and reusable across tables. Triggers execute logic rather than generating values, and table functions return result sets.

In database systems, triggers are event-driven programs that automatically execute in response to specific data manipulation operations. The standard trigger events are INSERT, UPDATE, and DELETE operations which modify data. SELECT operations only read data and do not modify it, therefore they cannot activate triggers. This design ensures triggers are only fired when actual data changes occur, maintaining data integrity and preventing unnecessary trigger executions during read-only queries.

Database triggers are specifically designed to respond to Data Manipulation Language (DML) operations that modify data: INSERT, UPDATE, and DELETE. These operations represent actual data changes that might require automatic business logic execution. ALTER is a Data Definition Language (DDL) operation that modifies table structure (like adding columns, changing constraints) rather than the data itself. DDL operations do not fire triggers because triggers are meant to respond to data content changes, not structural changes. This separation maintains clear boundaries between data modification and structural modification.

DB2 imposes a practical limit on subquery nesting depth to prevent overly complex queries that would impact performance and parser complexity. The maximum allowed nesting level is 15 subqueries deep. This means you can have a query within a query within a query, up to 15 levels deep. While the other options (13, 78, 255) might seem like technical numbers, 15 is the actual documented limit in DB2 for subquery nesting. This architectural limit balances query flexibility with system manageability.

Database constraint types include PRIMARY KEY, UNIQUE, NOT NULL, CHECK, FOREIGN KEY, and others. 'null' by itself is not a constraint - it describes the absence of a value. 'NOT NULL' would be a valid constraint type, but the option simply says 'null'.

Database constraints can be defined at two levels: column level (specified inline with column definition like 'name VARCHAR2(50) NOT NULL') and table level (specified separately like 'CONSTRAINT pk_emp PRIMARY KEY (emp_id)'). Primary keys and foreign keys often require table-level definition when they involve multiple columns.

The CASCADE option in Oracle (e.g., 'DISABLE CONSTRAINT pk_emp CASCADE') disables a constraint and automatically disables any dependent integrity constraints. This prevents situations where a primary key constraint remains enabled while its dependent foreign key constraints are disabled, which would violate referential integrity rules.

The USER_CONSTRAINTS data dictionary view in Oracle contains information about all constraints owned by the current user, including constraint names, types, status, and related details. This is the standard view for querying constraint definitions and names in your schema.

USER_CONS_COLUMNS is the Oracle view that shows the mapping between constraints and the columns they reference. It contains constraint names, table names, column names, and position - essential for understanding which columns participate in multi-column constraints like composite primary keys.

A primary key can definitely contain more than one column - this is called a composite primary key. For example, a table storing order line items might use (order_id, line_item_id) together as the primary key. Composite primary keys are common when no single column uniquely identifies each row.

In PL/SQL, cursor variables (REF CURSORs) cannot be stored in collections such as PL/SQL tables (associative arrays) or nested tables. This restriction makes the statement false, so the stored answer is correct.

You cannot use a COMMIT statement within a database trigger. This is because triggers execute within the context of the triggering transaction. Allowing a COMMIT would violate transaction atomicity and could cause data inconsistencies. The trigger shares the transaction of the statement that fired it.