Database and SQL

In SQL, group functions (like SUM or AVG) cannot be evaluated in the WHERE clause, and the WHERE clause itself cannot filter grouped results. The HAVING clause must be used instead, making both selected options correct descriptions of illegal queries.

When using group functions (aggregate functions like COUNT, SUM, AVG), you cannot use the WHERE clause to restrict groups: use HAVING for that. Also, group functions cannot appear in the WHERE clause because WHERE filters individual rows before grouping. Option B is incorrect because HAVING is specifically designed to restrict groups after aggregation.

The query SELECT address1||','||address2||','||address2 creates only 1 column. The || operator concatenates strings, and the expression address1||','||address2||','||address2 concatenates these fields into a single column named 'Adress'. Even though multiple address fields are used, they are combined into one output column.

ROWID is the physical address of a row in the database, representing its exact disk location. Accessing by ROWID is the fastest method because it bypasses all index lookups and goes directly to the data block. Primary key access and unique index access require first traversing the index structure to find the ROWID, then accessing the row. Full table scan is the slowest as it reads every row in the table.

In Oracle, standard unquoted identifiers must begin with a letter (A-Z) and can contain only letters, digits, and underscores. Option C is the only valid identifier that follows these rules. Option A uses a reserved word (though it would work if quoted). Option B starts with a digit which is invalid. Option D starts with # which is not allowed in unquoted identifiers. E is incorrect since C is valid.

The TRUNCATE command is a DDL statement that removes all data from a table without logging individual row deletions in the transaction rollback segments, making it faster and non-rollbackable. DELETE is a DML statement that writes to rollback segments.

When a column is wrapped in a function like NVL(), the Oracle optimizer cannot use the index on that column because the function modifies the column value before comparison. For options B, C, and D, EMPNO is compared directly to a literal value, allowing index access. Option A applies NVL() to EMPNO first, making the index unusable and forcing a full table scan. This is a common performance anti-pattern.

The hyphen (-) character in SQL*Plus allows you to break long SQL statements across multiple lines. When placed at the end of a line, it signals that the statement continues on the next line. The forward slash (/) is used to execute the current buffer, not for continuation.

The NEXT_DAY function returns the first occurrence of a specified weekday that comes AFTER the current date. Since today is Monday, July 10, NEXT_DAY looks for the NEXT Monday, which is July 17. It doesn't return the current day, but the following instance of that weekday.

When joining n tables in Oracle (or most SQL databases), you need at least n-1 join conditions to avoid Cartesian products between the tables. For 4 tables, this means at least 3 join conditions. Each condition links one table to another, creating a chain that connects all tables.

The SQL concatenation operator || joins multiple column values into a single result column. This query combines name, age, and address with comma separators, creating exactly one output column named 'emp_details'. The number of source columns concatenated doesn't affect the result column count.

Accessing a row by its physical address (ROWID) is the fastest access path in Oracle. It allows Oracle to locate the row directly on disk in a single read. Other methods like index lookups or primary key access require searching the index structure first to find the ROWID.

The TRUNCATE command is a DDL statement that removes all rows from a table immediately. Because it is DDL, it commits automatically and does not write individual row deletes to the rollback segment, making it faster than DELETE. DROP deletes the table structure entirely.

The MAX function can operate on any standard datatype (numeric, character, or date/time). Functions like TO_CHAR require specific input types (typically numeric or date), LOWER operates only on character data, and CEIL works exclusively with numeric values.

OLD and NEW qualifiers (referencing :OLD and :NEW values) are only available in row-level DML triggers. These qualifiers reference the before/after state of the row being modified. Statement-level triggers operate on sets of rows and don't have :OLD/:NEW context. Application triggers (Forms/Reports) have different mechanisms.