Oracle Database SQL Quiz

DBMS_STATS.UNLOCK_TABLE_STATS takes two parameters: schema name first, then table name. Options A and B incorrectly use UNLOCK_SCHEMA_STATS which locks/unlocks all tables in a schema. Option C uses UNLOCK_DB_STATS which doesn't exist. Option D has the parameter order reversed.

A tablespace is a logical storage container that can hold database objects (tables, indexes) from multiple schemas. Schemas are logical namespaces for users, while tablespaces are physical storage structures - they're independent concepts. This is a fundamental Oracle architecture feature allowing flexible storage management.

Parentheses override the default operator precedence in SQL expressions, just like in standard mathematics. Without parentheses, SQL evaluates operators according to precedence rules (NOT before AND before OR). Colons, commas, and semicolons are statement separators or terminators, not grouping operators.

A single tablespace can span multiple datafiles on disk. This allows tablespaces to grow beyond the size of a single file or filesystem. Datafiles are physical files that store the tablespace's data, and a tablespace can have many of them. This is essential for managing large databases.

A datafile can belong to only one tablespace at a time. This is a fundamental Oracle storage rule - datafiles are allocated to a specific tablespace when created and cannot be shared. To use a datafile's space for another tablespace, you must drop it from the first and reassign it.

Partitioning is a database feature that divides large tables or indexes into smaller, more manageable pieces. Both indexes (A) and tables (C) can be partitioned in database systems like Oracle and MySQL. Bitmaps (D) are index structures, not partitionable objects themselves. Hints (B) are query directives, not database objects that get partitioned.

An outer join (C) extends a simple join by including non-matching rows from one or both tables. A simple join is typically an inner join that only returns matching rows. Left joins (A) and right joins (B) are specific types of outer joins, making 'outer join' the broader, more general answer. An equijoin (D) is a basic inner join based on equality, not an extension.

A RIGHT OUTER JOIN returns all rows from the right table (B), plus matched rows from table A. Unmatched rows from A show NULL. LEFT OUTER JOIN would return all rows from A instead.

CREATE INDEX in Oracle works on table columns, cluster object attributes, and nested table storage tables. Indexes organize data for faster lookups on these structures.

An inner join is called a "simple join" in Oracle SQL because it returns only rows where the join condition matches in both tables. It's not an outer join (which includes non-matching rows).

A Cartesian product (cross join) occurs when two tables are joined without any join condition or WHERE clause linking them. Every row from the first table is paired with every row from the second table.

UNION ALL returns all rows from both queries including duplicates. UNION removes duplicates, while UNION ALL keeps them, making it faster when duplicates don't matter.

ALTER SEQUENCE in Oracle changes sequence properties for future numbers only. Already generated numbers and cached values remain unaffected. This prevents breaking existing references.

In Oracle, BFILE is a datatype that stores a pointer/locator to a binary file stored on the operating system's disk (outside the database). BLOB and CLOB store the actual data within the database, while NCLOB stores Unicode character data internally.

In Oracle, CLOB, BLOB, and NCLOB all support maximum sizes up to 4GB (4 gigabytes). These are large object datatypes designed to store significant amounts of character or binary data.

LONG RAW stores variable-length binary strings (raw binary data), while LONG stores variable-length character data. Both descriptions in the question are accurate Oracle datatype definitions.

BINARY_FLOAT is Oracle's 32-bit floating-point number datatype. BINARY_DOUBLE is the 64-bit version. ROWID is for row identifiers, and NCHAR is for fixed-length national character strings.

Cognos DecisionStream is the odd one out because it is an ETL (Extract, Transform, Load) tool used for data integration and transformation, whereas the other three options (MicroStrategy, Actuate, and Siebel Analytics) are all Business Intelligence and reporting platforms used for data visualization, dashboards, and analytics.