Oracle SQL Functions and Queries

ROWNUM IN (1,3,3,2) behaves similarly to ROWNUM IN (1,2,3) after deduplication. The first row gets ROWNUM=1 (matches), second row gets ROWNUM=2 (matches), third row gets ROWNUM=3 (matches). Rows 4-30 get ROWNUM values >3 and don't match. Thus, 3 rows are returned.

In Oracle, the '+' operator with strings causes implicit conversion to numbers. Both '1' strings are converted to numbers, then added. The result is 2 (numeric), not '11' (string concatenation would use ||). The implicit numeric conversion makes this a valid arithmetic operation.

Oracle implicitly converts the string '1' to a number when used in arithmetic with + operator. So '1'+1 becomes 1+1, which equals 2. The second query 1+1 also equals 2. Both return the same result.

In Oracle SQL, a single quote within a string literal is escaped by doubling it. Two consecutive single quotes ('') represent one literal quote character. The string 'a''a' contains characters a, ', a - outputting a'a.

In Oracle, double quotes are for identifiers like column/table names, not string literals. String literals require single quotes. "aa" with no column named aa causes invalid identifier error. This is standard Oracle SQL behavior.

Oracle's DUAL table contains a pseudo-column called DUMMY. The DUMMY column always contains the value 'X'. Selecting this column returns X - this is documented Oracle behavior for backwards compatibility.

In Oracle SQL, the DUAL table is a dummy table containing a single row with a single column DUMMY that has the value 'X'. Querying it with select * retrieves this default row and returns 'X'.

The DUAL table contains exactly one column named DUMMY and one row with the value 'X'. Because select dummy from dual and select * from dual both return exactly the same single row (X), subtracting one from the other via MINUS yields an empty set with no rows.

VERSION is not a column in the DUAL table. Selecting a non-existent column causes ORA-00904 invalid identifier error. To get version info, use V$VERSION or PRODUCT_COMPONENT_VERSION from the dictionary views, not DUAL.

NVL returns the first argument if it is not null, otherwise returns the second argument. Since 'abc' is not null, both NVL('abc','a') and NVL('abc',0) return 'abc'. The second argument's type is irrelevant when the first is non-null.

Both NVL queries return 'abc' (non-null value). The REPLACE query also returns 'abc'. UNION ALL combines the two 'abc' rows, then MINUS removes rows matching the REPLACE result. Since both are 'abc', MINUS removes everything, leaving no rows.

INSTR's fourth parameter (occurrence) cannot be negative - valid values are positive integers. The function throws ORA-01428 'argument out of range' when occurrence is -1. Negative values work for the third parameter (start position) to search backwards, but not for occurrence.

INSTR searches for 'a' starting at position 5. The string is 'Hi Partha Sarathi Ojha'. Characters at positions 5 is the 'a' in 'Partha' (H-i- -P-a). The first 'a' from position 5 is at position 5 itself, so it returns 5.

INSTR with a negative start position (third parameter) searches backwards from the end of the string. Setting start to -1 means start searching from the last character. This finds the last occurrence of 'a' in the string.

TRANSLATE does character-by-character replacement following position mapping: 'a'→'e', 'b'→'f', remaining characters unchanged. So 'abcda' becomes 'efcde'. The 'a' at positions 1 and 5 both become 'e'; 'b' becomes 'f'; 'c' and 'd' stay unchanged.

The REPLACE function replaces all occurrences of the search substring with the replacement substring. Since 'ab' is only at the beginning of 'abcda', it is replaced by 'ef', resulting in 'efcda'. The 'a' at the end is left unchanged because it is not part of an 'ab' pair.

Both queries replace 'a' with 'd' in 'abcd'. TRANSLATE('abcd','a','d') converts single character 'a' to 'd', giving 'dbcd'. REPLACE('abcd','a','d') replaces substring 'a' with 'd', also giving 'dbcd'. With single-character replacement, they produce identical output.

Attempting to divide by zero in Oracle SQL raises a runtime exception with the message 'ORA-01476: divisor is equal to zero' rather than returning NULL or Infinity.

In SQL, 0 divided by 1 equals 0. The GROUP BY clause is valid here because DUAL has a DUMMY column. Oracle performs integer arithmetic, so 0/1 evaluates to 0, not a decimal.