Database and SQL

In SQL*Plus, the hyphen character (-) at the end of a line indicates statement continuation. This allows you to break long SQL statements across multiple lines. The hyphen must be the last character on the line (before the newline). Other characters listed: * is a wildcard, / executes the previous buffer, and -- begins a comment (not a continuation character).

In relational databases like DB2, when a parent view is dropped, any views that depend on it are not automatically deleted but are instead marked as "inoperative" (or invalid), meaning they cannot be used until they are recreated or regenerated.

The RESTRICT referential integrity rule prevents the deletion of a row in the parent table if any dependent rows exist in the child table. CASCADE would delete the child rows, SET NULL would set the foreign keys to null, and DELETE is not a standard delete rule.

In SQL, a SELECT query without an ORDER BY clause returns rows in an ambiguous, non-guaranteed order. The database is free to return rows in whatever order is most efficient, which depends on the access path chosen by the optimizer. This order is not based on primary key, insertion order, or any specific column value unless explicitly specified. Option A is correct because relational databases make no guarantees about row order without ORDER BY.

Database locking mechanisms can be applied at row level, table level, and index key level, but NOT at individual column level. Column-level locking would be extremely granular and inefficient - most database systems lock at the row or page level instead. When you need to prevent concurrent access to specific columns, you use row-level locks which protect the entire row including all its columns. This is a fundamental design decision in database concurrency control.

The SELECT DISTINCT clause eliminates duplicate rows from a result set, returning only one instance of each unique row. The DISTINCT keyword is applied to the entire row (all columns), so rows must match in every column to be considered duplicates. The UNIQUE keyword and parentheses syntaxes are not valid SQL - the correct form is DISTINCT without parentheses around the asterisk.

When an order is revised in the Order Entry (OE) system, the existing order record is updated rather than generating a completely new row. This maintains data integrity and preserves the order's history within the same database record.

In this order entry system, revising an existing order updates the current record's details or status rather than generating a completely new row in the main interface. Therefore, the statement is false. The "True" option is incorrect because creating a new row for every revision would cause unnecessary duplication of order records.

The 'ISRT' (Insert) function code is used to add new records to a GSAM dataset. DL/I uses abbreviated 4-character codes for its functions, making ISRT the correct form rather than INSERT or other variations.

The RSA (Record Storage Address) field holds the address of the record that was just retrieved. This register is used in IBM mainframe environments to track the location of the most recently accessed record.

SELECT COUNT(*) is the standard SQL command to count the total number of rows in a table. This works in DB2 as well as other SQL databases. SELECT DISTINCT returns unique rows, SELECT MAX returns the maximum value of a column, and ROWNUM is an Oracle pseudocolumn, not standard DB2 syntax.

SELECT DISTINCT is the correct SQL clause to eliminate duplicate values from query results. It returns only unique rows. UNION combines result sets, UNION ALL keeps duplicates, and DISTINCT ALL(*) is not valid SQL syntax.

In DB2, a single table can span multiple tablespaces. This is especially true for partitioned tables where different partitions can be stored in different tablespaces. Even non-partitioned tables can have their data and indexes in different tablespaces. The statement is true.

By default, stored procedures execute with the privileges of the user who invokes them (invoker's rights), not the definer's privileges. When User A executes DELETE_TEMP_TABLE, the operations run under User A's privileges, unless explicitly defined with AUTHID DEFINER to use the creator's rights instead.