Database Fundamentals and SQL Quiz

A UNIQUE constraint ensures a column will not accept NULL values (in most DBMS implementations) and creates an index that can be referenced by a foreign key. CHECK constraints only validate data values, DEFAULT constraints provide fallback values, and INFORMATIONAL is not a standard constraint type.

In DB2 and similar SQL databases, the specific privilege required to create a table is CREATETAB. General administrative privileges are not strictly necessary, and USERTAB is not a valid privilege.

SYSMAINT is a database-level authority granted by the DB2 Database Manager configuration file (db2.cfg). It allows maintenance operations without full database administrative rights. CONNECT, CONTROL, and EXECUTE are database-level privileges/authorities, not configuration file authorities.

Repeatable Read locks all rows scanned to build the result set, preventing other transactions from updating or deleting them. Uncommitted Read takes no locks, Cursor Stability locks only the current row, and Read Stability locks only rows that are actually returned (not all scanned rows).

In Cursor Stability isolation level, locks are released when the cursor moves to another row. This is the defining behavior of CS - it only locks the row currently positioned on. Deleting the row or update attempts do not release the lock until the cursor moves.

UNION must be declared within a CURSOR in embedded SQL because embedded SQL requires declared cursors for result set processing. This is a restriction specific to embedded SQL environments where result sets must be explicitly defined.

Repeatable Read locks all rows scanned during query execution, preventing phantom reads and non-repeatable reads. UR takes no locks, CS locks only current row, and RS locks only qualifying rows. This is the strongest isolation level before Serializable.

Declared temporary tables are specifically designed for storing intermediate results during complex query processing. They are session-specific, not for backup (use backup utilities), load staging (use staging tables), or sharing between applications (use regular tables).

SELECT COUNT() is the correct SQL syntax for counting all rows in a table. Option A is missing parentheses, C uses incorrect brackets, and D is not valid syntax for counting all rows. COUNT() counts all rows including NULLs.

Repeatable Read isolation level locks all rows scanned to build the result set, ensuring the same query can be repeated with identical results. UR doesn't lock, CS locks only the current cursor position, and RS only locks rows that match the query criteria.

In binary systems, data units use powers of 1024. KB = 1024^1, MB = 1024^2, GB = 1024^3, TB = 1024^4, so PB = 1024^5 bytes. This follows the standard binary prefix progression. Options A and B have incorrect exponents, and option D has the wrong exponent plus a typo ('byes').

Oracle control files are critical metadata files that record the physical structure of the database. They store the names and locations of all physical database components including data files, redo log files, and tablespace information. Parameter files contain initialization parameters, redo logs store transaction changes, and data files hold actual table data - only control files track component locations.

The RUN command in SQLPlus executes the SQL statements stored in the current buffer. It processes the SQL command and displays the output. The other options (jump, walk, sleep) are not valid SQLPlus commands.

SOUNDEX is a SQL function that converts strings to their phonetic representation. It enables finding words that sound similar but have different spellings, which is useful for name matching. The other options (lightex, weightex, lux) are not standard SQL functions.

The VSIZE() function in Oracle SQL returns the number of bytes required to store a column value. This is particularly useful for understanding storage requirements and optimizing database performance. The other options (usize, wsize, xsize) are not valid Oracle SQL functions.

FLOOR is a valid Oracle SQL function that returns the largest integer less than or equal to a given number. For example, FLOOR(5.7) returns 5. The other options (window, door, ground) are not standard SQL functions in Oracle.

The TRIM function in SQL uses keywords like LEADING, TRAILING, or BOTH to specify which characters to remove. 'Trailing' removes characters from the end of a string. The other options are not valid TRIM keywords.