Oracle SQL and Date/Time Functions

SQL's ORDER BY clause can use column aliases defined in the SELECT statement. The aliases are created when SELECT processes the columns, making them available to ORDER BY in the same query level. This makes queries more readable by allowing descriptive names instead of expressions or column positions.

SQL's ORDER BY clause defaults to ascending (ASC) order when no sort direction is explicitly specified. This means numbers sort from lowest to highest, dates from earliest to latest, and text alphabetically. To reverse this behavior, you must explicitly specify the DESC keyword.

In SQL, NULL values are treated as the lowest possible values in sorting operations. When using ORDER BY with DESC (descending), NULL values appear first at the top of the result set. This behavior is consistent across most major database systems, though some databases provide options to control NULL positioning in sort order.

SQL arithmetic functions follow NULL propagation semantics, meaning any operation involving a NULL input returns NULL. This is because NULL represents an unknown value, and mathematical operations on unknown values cannot produce determinate results. This behavior applies to arithmetic operations and most built-in SQL functions.

Oracle can use password files for authentication, but they are not mandatory disk components. Oracle can authenticate through operating system authentication, network authentication, or other methods without requiring password files.

A RIGHT OUTER JOIN returns all rows from the right table (B) and matching rows from the left table (A), with NULL for non-matching left-side rows. LEFT OUTER JOIN would return all from A, CROSS JOIN is a Cartesian product, and ANY OUTER JOIN isn't valid SQL syntax.

CHECK is a valid Oracle constraint that enforces domain integrity by limiting values to a Boolean condition. CASCADE is an action (not a constraint type), CONSTANT isn't an Oracle constraint, and NONUNIQUE isn't a valid constraint (though it describes an index type). Oracle supports PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK constraints.

DESCRIBE is an iSQLPlus command that displays the structure of a table or other database object. RENAME, DELETE, and INSERT are SQL statements (DDL/DML), not iSQLPlus-specific commands. DESCRIBE can be abbreviated as DESC and provides column names, data types, and constraints.

MERGE is a DML (Data Manipulation Language) statement that combines insert and update operations based on a condition. DROP and CREATE are DDL (Data Definition Language), and COMMIT is a transaction control statement. DML includes SELECT, INSERT, UPDATE, DELETE, and MERGE.

The REPLACE function is a character manipulation function used to replace occurrences of a substring within a string. CASE is a conditional expression, TRUNC is a numeric or date truncation function, and MOD is a mathematical modulo function.

SYSDATE returns the current date and time of the database server, not the client machine. This is a common misconception - the server's system clock determines SYSDATE, regardless of where the client connects from.

CAST is a SQL function that converts an expression from one datatype to another. For example, CAST('123' AS NUMBER) converts a string to a number. It's standard SQL syntax available in Oracle.

MONTHS_BETWEEN(date1, date2) returns the number of months between two dates. If date1 is earlier than date2, the result is negative. For example, MONTHS_BETWEEN('01-Jan-2024', '01-Mar-2024') returns -2.

The DUMP function in Oracle returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of an expression - it's for debugging, not backup. Oracle backup is done using Data Pump (EXPDP/IMPDP) or older export/import utilities.

NEW_TIME is an Oracle function that converts a date from one time zone to another. It takes three parameters: the date, the source timezone (like AST, GMT, etc.), and the target timezone. For example, NEW_TIME(SYSDATE, 'GMT', 'EST') converts from GMT to EST.

National Language Support (NLS) parameters in Oracle are used to customize the display of date, time, currency, and language settings. International, Oracle, and SQL-99 compliant Language Sets are not standard terms for configuring these display formats.

TO_CHAR, TO_NUMBER, and TO_DATE are all valid Oracle conversion functions. TO_STRING is not a standard Oracle function - the correct function name for converting to string is TO_CHAR. This question tests knowledge of exact function names.

V$TIMEZONE_NAMES is the dynamic performance view in Oracle that contains valid time zone names. This view can be queried to see all supported time zones. The other options (v$timezone, user_timezone_names, dba_timezone_names) are not valid Oracle views.

TZ_OFFSET is an Oracle function that takes a time zone region name or abbreviation as input and returns the offset from UTC. For example, TZ_OFFSET('US/Eastern') returns the timezone offset. SESSIONTIMEZONE and SYSDATE don't take input parameters, and SYSTEMSTAMP isn't a valid function.