Database and SQL

SQL*Plus is Oracle's command-line interface for executing SQL and PL/SQL statements. Option E correctly identifies it as Oracle's proprietary tool. Option C is correct because SQL*Plus allows you to run SQL statements (DML) to manipulate data in tables. Option A is false because SQL*Plus commands CAN be abbreviated (e.g., DESC for DESCRIBE). Option B is false because SQL*Plus is a command-line tool, not browser-based. Option D is misleading because while you can run DDL through SQL*Plus, table definition manipulation is done via SQL DDL commands, not SQL*Plus-specific commands.

In SQL, the ORDER BY clause can appear only once at the end of a compound query (using UNION, INTERSECT, or EXCEPT) to sort the final combined result. The ORDER BY clause accepts column names or aliases defined in the SELECT list. However, you cannot use ORDER BY in the subqueries that are part of set operators - it would only be allowed in the outer query or with TOP/N clauses in some databases.

In Oracle SQL, the UNIQUE and BITMAP keywords cannot be used together when creating an index. UNIQUE enforces that all index key values must be distinct, while BITMAP creates a bitmap index optimized for low-cardinality columns with few distinct values. These are fundamentally different index types with different use cases, so they're mutually exclusive. You must choose one or the other, not both.

SUMIF adds values that meet a specified condition, unlike SUM which adds all values unconditionally. It requires a range to test, a criteria to match, and an optional range to sum.

Referential integrity ensures relationships between tables remain consistent. Primary keys, foreign keys, and cascading actions (like ON DELETE CASCADE) are core mechanisms for this. Unique constraints ensure data uniqueness but do not inherently manage relationships between different tables.

Referential integrity is a database concept that ensures relationships between tables remain consistent. It is maintained by defining a Foreign Key in a child (reference) table that must match a Primary Key in the parent (base) table, ensuring no orphaned records exist.

The OTHERWISE clause in a SELECT instruction is optional, not mandatory. It provides a default case when no WHEN conditions match, but can be omitted if not needed. The statement claims it should be specified, which is false.

In PL/SQL, every block (anonymous blocks, procedures, functions, and packages) can have its own EXCEPTION section to handle errors that occur within that specific block. This allows for modular error handling where different blocks can manage exceptions differently based on their context.

PL/SQL exception messages contain three key components: the exception type (such as NO_DATA_FOUND or VALUE_ERROR), an error code (numeric identifier like ORA-01403), and a descriptive message explaining the error. A FINALLY block is a separate construct used in some languages for cleanup code but is not part of the exception message itself in PL/SQL.

PL/SQL exceptions are categorized into three types: Named System Exceptions (pre-defined by Oracle), Unnamed System Exceptions (handled via Pragma EXCEPTION_INIT), and User-defined Exceptions (declared by the developer for specific business logic). 'Implicit' and 'Explicit' refer to how they are raised, not their type.

Internal exceptions (like NO_DATA_FOUND or ZERO_DIVIDE) are raised implicitly by the Oracle runtime engine whenever a program violates a database rule. User-defined exceptions, conversely, are typically raised explicitly by the programmer using the RAISE statement.

PL/SQL automatically raises NO_DATA_FOUND exception when a SELECT INTO statement returns no rows. This is a predefined exception that triggers when a single-row query fails to find any matching data. Unlike explicit error checking, this built-in mechanism handles empty result sets gracefully.

DENSE_RANK() assigns consecutive ranks (1,2,2,2,3) without gaps after tied values, making it ideal for the requested pattern. ROW_NUMBER() always produces unique sequential numbers (1,2,3,4,5), while RANK() leaves gaps after ties (1,2,2,2,5). NTILE() divides rows into specified number of groups, producing a different pattern entirely.

A Group Left report is a specific layout style where the master (grouping) column is placed to the left of the detail columns. This prevents the master value from repeating for every row of detail data, creating a clean, hierarchical view of the information.

Lexical references are placeholders for text embedded in SELECT statements in reporting tools. Parameters pass values at runtime, fields display data, and lexical references allow dynamic text substitution in queries.