Database Management and SQL Functions

To solve this question, the user needs to know the function NVL2 in SQL.

The NVL2 function in SQL returns one value if an expression is not null, or another value if it is null. The syntax for the NVL2 function is:

NVL2( expression1, expression2, expression3 )

If expression1 is not null, then NVL2 returns expression2. If expression1 is null, then NVL2 returns expression3.

In this case, expression1 is NULL, so NVL2 will return the value of expression3, which is also NULL.

Therefore, the answer is:

The Answer is: A. NULL

PL/SQL blocks have four sections: Header (optional, for named blocks), Declarative (DECLARE section for variables, types, cursors), Executable (BEGIN...END for main logic), and Exception (EXCEPTION section for error trapping and handling). The Exception section specifically contains functions and handlers for errors.

In SQL, NULL represents an unknown value. When two NULL values are compared using operators like =, <>, >, <, etc., the result is NULL (unknown), not TRUE or FALSE. This is because NULL is not a value but a marker for missing information, so comparing two unknowns yields an unknown result.

To answer this question, the user needs to know the basics of PL/SQL and how it handles exceptions.

A. When a SELECT statement returns more than one row: This is one of the cases where PL/SQL raises an exception. If a SELECT statement returns more than one row in a context where a single value is expected, such as in an assignment statement or a SELECT INTO statement, then PL/SQL will raise the TOO_MANY_ROWS exception.

B. When a SELECT statement returns no rows: This is another case where PL/SQL raises an exception. If a SELECT statement returns no rows in a context where a single value is expected, such as in an assignment statement or a SELECT INTO statement, then PL/SQL will raise the NO_DATA_FOUND exception.

C. When INTO statement is missing in the SELECT statement: This is not a case where PL/SQL raises an exception. If an INTO statement is missing in a SELECT statement, then a compilation error will occur, but it will not result in an exception at runtime.

D. Both I and II: This option is correct. PL/SQL raises exceptions in both cases where a SELECT statement returns more than one row (TOO_MANY_ROWS) and where a SELECT statement returns no rows (NO_DATA_FOUND).

Therefore, the correct answer is: D.

NVL2(expr1, expr2, expr3) returns expr2 if expr1 is NOT NULL, otherwise returns expr3. Since the first argument is NULL, NVL2 returns the third argument NULL. The second argument 'NOT NULL' is just a string literal in this context, not the SQL keyword.

To solve this question, the user needs to know the basics of SQL SELECT statements and string manipulation functions. The user should be familiar with the INITCAP, TRIM, SUBSTR, and LOWER functions.

Now, let's go through each option and explain why it is right or wrong:

A. SELECT INITCAP(TRIM ('HelloWorld', 1,1)) FROM dual;

This option is incorrect. The TRIM function removes characters from the beginning and end of a string. In this case, the second and third arguments of the TRIM function are incorrect. INITCAP capitalizes the first letter of a string and converts the rest of the string to lowercase. Therefore, this SELECT statement will not return the correct result.

B. SELECT SUBSTR( 'HelloWorld',1) FROM dual;

This option is incorrect. The SUBSTR function returns a substring from a given string. In this case, the function only returns the first character of the string 'HelloWorld'. Therefore, this SELECT statement will not return the correct result.

C. SELECT LOWER(SUBSTR('HelloWorld', 1, 1) FROM dual;

This option is incorrect. The SUBSTR function returns a substring from a given string. In this case, the function only returns the first character of the string 'HelloWorld'. The LOWER function converts the substring to lowercase. Therefore, this SELECT statement will not return the correct result.

D. SELECT LOWER(TRIM ('H' FROM 'HelloWorld')) FROM dual;

This option is correct. The TRIM function removes the character 'H' from the beginning of the string 'HelloWorld'. The LOWER function converts the remaining string to lowercase. Therefore, this SELECT statement will return the correct result.

The Answer is: D

COALESCE returns the first non-null expression in the list. COALESCE(A, B) is logically identical to a CASE statement that checks if A is NOT NULL; if so, it returns A, otherwise it returns B.

A FULL OUTER JOIN returns all rows when there is a match in either left or right table records. It is specifically used to ensure that unmatched data from both tables is included in the result set.

The mod function returns the remainder of division. -15 ÷ 4 = -3 with remainder -3, since -3 × 4 = -12 and -15 - (-12) = -3. Different languages handle negative operands differently, but -15 and 4 would typically give remainder -3 or 1 depending on implementation.

The instr function searches for a substring. With -3 as start position, it searches 3 characters from the END. In 'Tech on the net', counting back 3 from end: 't' at position 10. Searching for 'e' going backwards: 'e' at position 2 (in 'Tech'). Length parameter 2 doesn't apply when searching backwards.

The power function raises a number to an exponent. power(-5, 3) means -5³ = -5 × -5 × -5 = -125. When raising a negative number to an odd power, the result stays negative. For even powers, the result becomes positive.

TRUNC truncates a number to specified decimal places. With negative decimal parameter -1, it truncates to the tens place. 44.647 truncated to tens = 40. Positive parameters would truncate to decimal places (1 = tenths, 0 = ones).

In SQL Server, stored procedures can be grouped by giving them the same name followed by a semicolon and a distinct number (e.g., 'procname;1', 'procname;2'). This allows dropping the entire group with a single DROP PROCEDURE statement. Options A and B are wrong - the names must be identical, not distinct. Option C is wrong because it needs distinct numbers, not a group name.

High safety mode (also called high safety with automatic failover) is the correct database mirroring operating mode for mission critical databases requiring automatic failover. In this mode, transactions are committed synchronously to both principal and mirror servers before acknowledging success, ensuring zero data loss. High performance mode is asynchronous and doesn't support automatic failover.

By marking a computed column as PERSISTED, SQL Server physically stores the computed values in the table and updates them when dependent columns change, allowing you to create indexes on it.

The TSQL_Grouped template in SQL Server Profiler groups trace information by the application name and login credentials, making it ideal for analyzing queries executed by specific applications.

The TO_CHAR function converts numeric or date values to character strings. Passing the string '10' to TO_CHAR simply returns '10'. Converting words to numbers or vice versa requires other functions or custom logic.

Indexes are most effective on columns with high cardinality (wide range of unique values). Low-cardinality columns (like boolean flags or status enums) benefit less from indexing because the index doesn't significantly reduce the search space.