Database Fundamentals: Data Types, Cursors & Storage

In Oracle SQL, to include a single quote in a string literal, you escape it by doubling it. The string ' ' 'TCS' ' ' actually parses as: quote-space-quote (space), then 'TCS', then quote-space-quote (space). The doubled quotes represent literal single quote characters. Option B correctly uses this escaping.

In DB2, a Partitioned Table Space can contain only ONE table. This is different from segmented table spaces which can contain multiple tables. The partitioning is designed to distribute a single large table across multiple storage partitions for performance and manageability. Each partition holds a portion of the same table based on partitioning keys.

v$locked_object is the Oracle dynamic performance view that shows detailed information about locked objects in the database, including the session ID, object ID, and lock mode. v$lock provides general lock information but not specific object details, while v$session shows session information without object locking details.

COUNT() is an aggregate function that returns the total number of rows in a table, including null values. When a table is empty (no rows), COUNT() returns 0, not an error or 'No data found' message. This is fundamental SQL behavior.

INSTR('TCS_SQL','S',4) searches for 'S' starting from position 4 in 'TCS_SQL'. Positions 1-3 are 'T', 'C', 'S' (the first S). From position 4, the string is '_SQL', where 'S' is at position 5 of the original string (second S in the string).

The maximum number of rows per page in QTP/UFT data tables is 127. This is a technical limit enforced by the tool for display and performance reasons. Attempting to add more rows beyond 127 creates a new page in the data sheet.

Standard DB2 data page sizes are 4KB, 8KB, 16KB, and 32KB, with 4K to 8K being the most commonly used range for general purpose table spaces. These sizes represent the fundamental I/O units for database storage operations. The other ranges either exclude the standard sizes or include non-standard options.

In DB2, a partitioned table space can contain only one table. The partitioning is used to distribute that single table's data across multiple data partitions based on key ranges. Segmented table spaces can hold multiple tables, but partitioned table spaces are designed for single-table optimization.

DB2 allows joining up to 15 tables in a single SQL statement. This limit balances query flexibility with performance considerations. Some database systems have lower limits (often 10 or fewer tables), but DB2 supports this higher maximum for complex reporting and analysis needs.

DB2 stored procedures can be debugged using the DB2 Debug Tool or similar debugging utilities. This allows developers to set breakpoints, step through code, inspect variables, and trace execution flow within stored procedures, making it easier to identify and fix logic errors.

By default, issuing a COMMIT statement closes all open cursors unless they are explicitly declared using the WITH HOLD option.

DECLARE CURSOR is a declarative statement, not an executable SQL statement. It defines the cursor's characteristics (the query and attributes) but does not actually process any data. The OPEN statement executes the cursor declaration by processing the query, and FETCH retrieves the actual rows.

An application program can have multiple cursors open simultaneously to fetch and process data from different result sets at the same time.

The TIMESTAMP data type in DB2 occupies 10 bytes of physical storage. This size accommodates the full precision needed to store date and time information including year, month, day, hour, minute, second, and fractional seconds. DATE requires 4 bytes and TIME requires 3-5 bytes depending on precision, but TIMESTAMP needs the full 10 bytes.

In DB2, the DATE data type has a fixed physical storage length of 4 bytes, which represents the date internally.

VARCHAR is the ANSI SQL standard character type, while VARCHAR2 and CHAR are Oracle-specific. NCHAR is Oracle's Unicode character type. VARCHAR stands out as the odd one because it's the standard SQL type rather than Oracle's proprietary extension.

Oracle databases only allow one LONG column per table due to legacy design restrictions. This is a well-known Oracle limitation - other listed types (NCHAR, INTEGER, TIMESTAMP) can be used multiple times without restriction.

CHAR is for fixed-length character strings. NCHAR and VARCHAR2 are also character types compatible with CHAR. DATE is compatible in some contexts (implicit conversion). BLOB is for binary data and has no compatibility with character types like CHAR.

CHARACTER, INTEGER, and REAL are standard ANSI SQL data types. NUMBER is an Oracle-specific data type rather than a standard ANSI SQL type.