Database and SQL

The UNIQUE constraint allows null values (unless combined with NOT NULL). A UNIQUE index is automatically created for columns defined with PRIMARY KEY and UNIQUE constraints, making that statement correct, while NOT NULL ensures no nulls are allowed.

Subqueries can return zero, one, or multiple rows depending on context and operators used (B is true, disproving A). They can be nested beyond two levels in most databases (C is false). Subqueries can appear in various SQL statement types including DML and DDL, not just queries (D is false). They work with group functions (E is false). When using inequality operators with subqueries, single-row subqueries must be ensured or the ANY/ALL modifiers must be used (F is true, describing the inequality constraint requirement).

USER_CONS_COLUMNS displays the columns that participate in constraints defined on tables owned by the current user. USER_CONSTRAINTS shows constraint definitions but not column details. USER_OBJECTS lists all objects owned by the user. ALL_CONSTRAINTS includes constraints on accessible objects owned by others. USER_COLUMNS shows all table columns, not specifically constraint-related ones.

To grant all users query privileges, use GRANT select ON dept TO PUBLIC. PUBLIC is a reserved word that represents all database users. Option A incorrectly uses ALL_USERS which is not a valid grantee. Option B incorrectly uses ALL without PUBLIC. Option C incorrectly uses QUERY instead of SELECT and ALL_USERS. Option E incorrectly uses QUERY and incorrectly capitalizes PUBLIC.

The NOT NULL constraint can only be defined at the column level because it applies to a single column's values. Constraints like UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY can be defined at either the column level or table level. Table-level constraints allow specification across multiple columns, which is essential for composite keys and multi-column conditions.

Outer joins preserve all rows from one or both tables, filling in NULLs where there are no matches. Use them when you need to keep unmatched rows (like finding customers with no orders) or when NULL values in join columns indicate non-matches rather than missing data. Inner joins only return matched rows, so outer joins are essential when unmatched data matters.

In version control systems, changes are propagated from one branch to another by performing a merge operation, which integrates the history and modifications.

To store values up to 9999999.999, you need 7 digits before the decimal point and 3 digits after, requiring a total precision of 10. Thus, DECIMAL(10,3) is the correct data type.

When an application holding a Share lock needs to update a row, DB2 automatically converts (promotes) the row-level Share lock to an Update lock. The application does not need to explicitly release and reacquire locks - lock conversion is handled transparently by the database manager. Option D is incorrect because escalation is to table-level locks, not row-level Update locks.

Views are used to restrict access to specific rows or columns (A), simplify complex queries (D), and ensure data integrity through CHECK OPTION (B). Producing an action in response to table changes is the purpose of triggers, not views. Views are passive queries - they select data but cannot execute procedural logic in response to changes.

DB2 does not necessarily fetch all rows when opening a cursor. The behavior depends on cursor type and optimization - with a declared cursor, rows may be fetched one at a time as needed (FETCH operations), not all at once. The result set is materialized progressively, not entirely at cursor open time. This approach improves performance and reduces memory usage for large result sets.

Scalar functions in DB2 can be invoked using VALUES clause (A) for direct function calls returning a single value, or within a SELECT statement (B) to apply the function to each row. Option C (CALL) is used for stored procedures, not scalar functions. Option D is correct because both A and B demonstrate proper scalar function invocation syntax.

In DB2, a SYNONYM is automatically dropped when its underlying table or tablespace is dropped. An ALIAS, however, is preserved even if the referenced table is dropped.

A schema is a collection of named database objects such as tables, views, and indexes. It provides a logical grouping and namespace for these objects. Tables are individual objects within a schema, not collections themselves. An alias is just an alternate name for an object.

B37 abend is a specific error code in mainframe systems indicating a DASD space issue - the dataset or work file has run out of space during SPUFI (SQL Processor Using File Input) execution. Data issues or missing records would result in different error codes like SQLCODEs, not B37.