Database and SQL

Oracle databases use 4 main types of segments: data segments (table data), index segments (index data), undo segments (for rollback/undo), and temporary segments (for temporary operations). These are the core segment types in Oracle's storage architecture. Options A, B, and D don't represent the complete set.

EXECUTE IMMEDIATE is the statement used to execute dynamic SQL statements that are constructed and executed at runtime. Option A uses static SQL with a host variable. Option C declares a cursor for a prepared statement. Option D has incorrect syntax.

The PROC SQL step contains two statements: the PROC SQL statement and the SELECT statement. The SELECT clause, FROM clause, and ORDER BY clause are all part of the SELECT statement. The question asks for the number of statements, not clauses.

ORDER BY is an optional clause when creating tables and views, used only to sort the result set. Omitting it can improve performance by avoiding unnecessary sorting. WHERE clauses are recommended to limit result sizes. Using joins instead of subqueries is a performance best practice.

The RESET statement in PROC SQL is used to reset specific options or settings, not to add, drop, or change options dynamically. It can reset certain parameters but does not provide the comprehensive option management described in the question.

The MODIFY statement cannot modify descriptor information like variable names, labels, or attributes - only data values. Options A and C are false. Option D is incorrect as SAS does not automatically save copies during abnormal termination. Descriptor changes require other statements like RENAME.

The WHERE clause uses 'ge' which is a SAS operator, not a SQL operator. In PROC SQL, the correct operator is '>='. The SELECT and FROM clauses are written correctly with proper syntax for column selection and table reference.

A table can have only one primary key constraint, though that primary key can consist of multiple columns (a composite key). The statement asks about multiple primary keys, which is not allowed in standard SQL - hence the answer is False.

Unique constraints in SQL allow multiple NULL values because NULL represents unknown or missing data, not an actual value. The uniqueness check only applies to non-NULL values - each NULL is considered distinct from other NULLs. Therefore, you can insert as many NULL values as needed into a column with a unique constraint.

SQL allows multiple CHECK constraints on a single column because each constraint is evaluated independently. For example, you could have one CHECK ensuring age >= 18 and another ensuring age <= 65 on the same column. All CHECK constraints must be satisfied for any INSERT or UPDATE operation on that column.

Oracle data dictionary views provide metadata about database objects. USER_CONSTRAINTS displays constraint definitions for the current user's tables. USER_CONS_COLUMNS shows which columns participate in which constraints. USER_TABLES and ALL_TAB_COLS contain table and column information but not constraint details.

DML statements (INSERT, UPDATE, DELETE) are executed using Statement.execute() for general SQL or Statement.executeUpdate() which returns the row count. The other options describe non-existent classes and methods - there are no separate InsertStatement/DeleteStatement classes, nor executeInsert/executeDelete methods on Statement.

In JDBC, ResultSet.TYPE_SCROLL_INSENSITIVE indicates a scrollable result set that is not sensitive to changes made by others while it is open. Distractors confuse general scrolling ability with sensitivity to modifications.

TYPE_SCROLL_INSENSITIVE creates a scrollable ResultSet that allows bidirectional navigation (first, last, absolute, relative). The 'INSENSITIVE' means it doesn't reflect changes made by others after the ResultSet was created - it shows a static snapshot. Option A is wrong - 'insensitive' refers to change sensitivity, not scrolling ability. Option B has the sensitivity backwards.

In PowerCenter Update Strategy, DD_INSERT has the numeric value 4. Other constants include DD_UPDATE (1), DD_DELETE (2), and DD_REJECT (3). The value 4 is used when flagging rows for insertion.