Relational Database Concepts and SQL Fundamentals

VARCHAR2 is the correct choice for text up to 4000 bytes. At 3,500 characters, VARCHAR2 is efficient and appropriate. CLOB is for large text (up to 4GB) and has overhead. BLOB is for binary data. BFILE stores file pointers, not actual text. VARCHAR2(4000) is the standard for moderate-length text in Oracle.

Transaction control language (TCL) commands manage changes made by DML statements. COMMIT, SAVEPOINT, and SET TRANSACTION are TCL commands, whereas UPDATE is a Data Manipulation Language (DML) command.

In numeric types, precision is the total number of digits, while scale is the number of digits after the decimal point. For example, NUMBER(8,2) has precision 8 and scale 2. Scale is NOT the total length - that's precision. This is a common confusion point.

Normalization eliminates redundancy but is not always the best approach. Denormalization is often used to improve read performance by reducing joins. Real-world databases frequently balance normalization (for write integrity) with strategic denormalization (for query performance). The statement is false because 'always' makes it incorrect.

A ROLLBACK command only undoes uncommitted changes in the current transaction. Any transactions that have already been committed are permanent and cannot be undone by a subsequent rollback.

A SAVEPOINT does not commit or permanently save transactions to the database. Instead, it sets a logical marker within the current transaction to which you can later roll back if needed.

The SQL AVG aggregate function ignores NULL values entirely when calculating the average. It does not include them in either the sum or the count of rows, making the statement false.

HAVING can be used without GROUP BY when filtering on aggregate totals of the entire result set. For example, 'SELECT COUNT() FROM table HAVING COUNT() > 10' is valid SQL - it applies HAVING to the single aggregate row. GROUP BY is only required when HAVING is used to filter groups within partitions.

The HAVING clause filters grouped results after aggregation, specifying which groups should be included in the final output. It operates on the grouped data, unlike WHERE which filters rows before grouping.

Composite indexes are primarily used to optimize queries that filter or sort on multiple columns together. They improve performance for multi-column WHERE clauses and ORDER BY operations, not for aggregate functions.

An OUTER JOIN returns all records from one table regardless of whether there are matching records in the related table. INNER JOIN only returns matching records, while LEFT/RIGHT JOINs are specific types of outer joins.

In database security, roles can be granted to other roles (option A), privileges can be granted to roles (option B), and roles can be granted to users (option D). However, roles cannot be granted to synonyms (option C is false) - synonyms are just aliases, not securable objects like roles or users.

ALL_IND_COLUMNS provides information about index columns, including their position within the index. Since primary keys are implemented using indexes in Oracle, this view shows column positions within primary keys. Option A doesn't exist, option B shows table metadata, and option D shows constraint info but not column positions.

Locks are the transaction control mechanism that prevents concurrent updates - they 'lock' data so only one transaction can modify it at a time. Commits finalize changes, savepoints mark rollback points, and rollbacks undo changes - none prevent concurrent access like locks do.

To add nullable columns to a table, you use ALTER TABLE ADD COLUMN - columns are nullable by default unless you specify NOT NULL. Option D would work for a NOT NULL column, but it's unnecessarily complex for nullable columns. Options B and C are incorrect approaches.

In SQLPlus, user-defined variables (created via DEFINE or ACCEPT) persist for the duration of the current SQLPlus session. They are cleared only when explicitly undefined or when the session completes (exits).

DB2 UDB Personal Edition (PE) is designed for single-user desktop environments and does not accept inbound remote database requests from other client machines.

The DB2 Configuration Advisor analyzes database configuration parameters (buffer pool sizes, sort memory, lock limits, etc.) and recommends optimized settings based on workload patterns. It doesn't recommend specific SQL statements, views, or DDL scripts.

The exclamation mark (!) is the escape character in DB2 Command Line Processor that prefixes operating system commands, allowing you to execute shell commands without exiting the database interface. This is a common convention in command-line tools.

In DB2, the Command Center allows users to execute SQL queries and interactive commands to display and manipulate table data directly.