Database Fundamentals: Keys, Cursors, and PL/SQL

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.

EXISTS is generally faster than IN for subquery performance, especially with large datasets. EXISTS stops processing as soon as it finds a match (short-circuit evaluation), while IN may evaluate the entire list. Additionally, EXISTS handles NULL values more predictably - IN can return unexpected results when the subquery contains NULLs. Note: 'EXIST' is not valid SQL syntax.

A CURSOR returns a set of rows (a result set), not a single scalar value. Scalar data types like NUMBER, VARCHAR2, or DATE can hold individual values, but a cursor is inherently a multi-row construct. To use a cursor, you must FETCH rows into appropriate variables or data structures. Therefore, a scalar data type cannot be the return type for a CURSOR.

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.

Functions cannot take OUT parameters as actual arguments. OUT parameters are used to return values from procedures or functions, so they must be variables with l-values (storage locations). A function call itself is an expression that returns a value and cannot be used as an OUT parameter destination. This is a fundamental constraint in parameter passing modes.

Default values can be assigned to formal parameters in a function/procedure declaration. When the function is called without providing those arguments, the default values are used for the actual parameters. This allows optional parameters with sensible defaults. The statement refers to this common language feature.

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.

You can call a stored procedure from an anonymous PL/SQL block. Anonymous blocks can invoke procedures using the EXECUTE statement or direct procedure calls. This is a standard way to test or execute procedures without creating a named stored block. The procedure executes within the block's scope.

A database table can have multiple columns defined with the BLOB (Binary Large Object) data type, allowing storage of various binary files such as images, audio, or documents within different columns of the same record.

DDL (Data Definition Language) contains the SQL commands that define database structures like CREATE TABLE. Generating DDL allows you to duplicate table structure along with constraints and indexes. Copy/Alter/Export table operate on data, not structure.

DB2 isolation levels trade off concurrency vs consistency. Cursor Stability locks only the current row, allowing higher concurrency. Repeatable Read locks all rows read, preventing changes and providing higher consistency. Both statements correctly describe these characteristics.

Statement i is false because unique indexes can be created on any column or combination of columns that require uniqueness, not exclusively on primary keys. Statement ii is true because indexes allow the database engine to locate rows quickly when predicates are applied to indexed columns.

DB2 stores indexes using B-tree structures, which provide efficient search, insert, and delete operations. B-trees are the industry standard for database indexes because they maintain sorted data and allow logarithmic time complexity. Hash tables, AVL trees, and Red-Black trees are not used for DB2 indexes.

A cursor must always be opened before fetching rows. In standard embedded SQL, each execution of the FETCH statement retrieves exactly one row into host variables. Both statements are true, making the selected option correct.

Deadlock is a situation where two or more transactions are stuck waiting for each other to release locks, creating a circular dependency that prevents any transaction from proceeding. The other options are different concurrency issues: lost update occurs when one transaction overwrites uncommitted changes, uncommitted dependency involves reading uncommitted data, and infinite loop is a programming error unrelated to database locking.

By definition, a primary key uniquely identifies each row in a table and therefore cannot contain NULL values. NULL would violate the uniqueness constraint since NULL = NULL is not true in SQL. Option A is false because primary key values must be unique. Option B is incorrect because LONG is not a standard SQL data type (though CLOB/BLOB exist).