Database and SQL

The || operator concatenates strings in SQL. The entire expression address1||','||address2||','||address2 is treated as a single column with the alias 'Adress'. No matter how many columns or literals you concatenate, the result is always one column in the output.

Retrieving a single row by its physical address (ROWID) is the fastest possible access path in Oracle, as it bypasses index lookups and directly targets the block containing the row.

To avoid cartesian products when joining n tables, you need at least n-1 join conditions in the WHERE clause. This ensures each new table is linked to the previous ones in a chain. For 4 tables, you need 4-1 = 3 conditions.

To solve this question, the user needs to understand the function of each element of the SQL statement and how it affects the output.

The SQL statement is selecting a date and formatting it according to the given format string. Specifically:

• sysdate returns the current date and time.
• NEXT_DAY(sysdate, 'MONDAY') returns the next occurrence of the day specified as the second argument, after the current date and time (sysdate). In this case, it returns the next Monday after 10 July 2000, which is 17 July 2000.
• to_char() converts the resulting date into a string using the format specified as the second argument. In this case, the format is 'DD-MON-RR', which returns the day of the month (DD), the abbreviated month name (MON), and the 2-digit year (RR).

Therefore, the correct answer is E. 17-JUL-00, as the SQL statement returns the formatted date of the next Monday after 10 July 2000, which is 17 July 2000.

In SQL*Plus, the hyphen (-) character is used as a continuation character when a SQL statement spans multiple lines. The asterisk (*) is for comments, forward slash (/) executes the buffer, and (@) runs script files.

The NVL function wraps the indexed column EMPNO, which prevents Oracle from using the index on EMPNO. When a function is applied to a column in the WHERE clause, the index on that column cannot be used unless a function-based index exists. Options B, C, and D directly compare EMPNO, allowing index usage.

The TRUNCATE command is a DDL operation that quickly deletes all rows from a table without logging individual row deletions in the rollback segment, unlike DELETE which writes to rollback.

NUMBER(10,2) means total 10 digits with 2 after decimal, so max 8 digits before decimal (10-2=8). Option B has 9 digits before the decimal (871039453), which exceeds the allowed precision and causes an error. Option C has 3 decimal places but only 8 integer digits, so it would round to 97234512.12 without error. Oracle rounds values that exceed scale but fits within precision, only errors when integer part exceeds allowed digits.

Subqueries in SQL cannot use ORDER BY clause because subqueries return a set of rows used by the outer query, and the outer query determines the final ordering. However, subqueries can use WHERE for row filtering and HAVING for group filtering. ORDER BY is only allowed in the outermost query or in subqueries that use TOP/LIMIT/FETCH FIRST clauses (not in this question's context).

The LENGTH function in Oracle returns the character count of its argument after converting it to a string. When passed -1, Oracle implicitly converts it to the string '-1', which contains 2 characters: the minus sign and the digit 1. The LENGTH function counts all characters including signs and decimal points. This is not an error - Oracle allows implicit type conversion from number to varchar2.

COUNT(column_name) ignores NULL values and only counts non-NULL entries. With 14 total records and 1 NULL in MGR column, COUNT(MGR) returns 13, which is <14. COUNT(*) would return 14 because it counts all rows regardless of NULLs. The question tests understanding that aggregate functions like COUNT, SUM, AVG skip NULL values when operating on a specific column.

A composite primary key involves multiple columns and must be declared using table-level constraint syntax (outside the column definitions). Single-column constraints like NOT NULL, UNIQUE, and CHECK can be declared inline with the column. For example: CREATE TABLE foo (a INT, b INT, PRIMARY KEY (a,b)) - the PRIMARY KEY clause at table level creates a composite key. Inline column syntax cannot specify multiple columns for a single constraint.

In Oracle, deferred constraints are validated only at transaction commit time, not after each DML operation. This allows intermediate constraint violations during transaction processing. Option A is incorrect because constraints ARE checked - just deferred. Option C describes behavior for initially deferred vs. immediate constraints. Option D is incorrect because there's no time-interval-based checking.

In Oracle SQL, hints are passed to the optimizer using specially formatted comments starting with a plus sign, such as /*+ HINT */. Thus, the statement is true.