Oracle Updated 1z0-071 Exam Questions and Answers by kyan

A. True, this statement will execute successfully. In Oracle SQL, column aliases can be given in double quotes which allows the use of special characters like a hyphen. Additionally, you can perform arithmetic operations such as amount+100 directly in the SELECT clause to return increased values of amount.

C. True, this statement is also valid. It does not use any special characters or reserved keywords as aliases that would require double quotes. The arithmetic operation is the same as in option A, which is permissible.

For B, D, and E, the reasons for failure are: B. In Oracle SQL, single quotes are used for string literals, not for column aliases. Therefore, using single quotes around 'CUSTOMER-ID' and 'DUES' will result in an error. D. The word "DATE" is a reserved keyword in Oracle SQL. When used as a column alias without double quotes, it will lead to an error. In this statement, although "DATE" is in double quotes, it's a best practice to avoid using reserved keywords as aliases. E. There are syntax errors in the statement. "customer id" should be "customer_id", and the alias "CUSTOMER-ID" should be enclosed in double quotes if special characters are used. Also, "DUES AMOUNT" as an alias should be in double quotes to be valid because it contains a space.

References:

• Oracle documentation on column aliases: Oracle Database SQL Language Reference
• Oracle reserved words: Oracle Database SQL Language Reserved Words

A. This statement is false. In NVL2, the first expression is returned if it is not null.

B. This statement is false. NVL2 takes exactly three arguments, not any number of expressions.

C. This statement is false. COALESCE stops evaluating its list of expressions as soon as it finds the first non-null value.

D. This is true. COALESCE returns the first non-null expression in its list.

E. This statement is true. NVL requires the first and second expressions to be of the same or compatible data types because it returns the first expression if it is not null, otherwise it returns the second.

F. This statement is false. NVL takes exactly two arguments, not any number.

References:

• Oracle Documentation on COALESCE: https://docs.oracle.com/database/121/SQLRF/functions040.htm#SQLRF00625
• Oracle Documentation on NVL: https://docs.oracle.com/database/121/SQLRF/functions130.htm#SQLRF00683
• Oracle Documentation on NVL2: https://docs.oracle.com/database/121/SQLRF/functions123.htm#SQLRF00682

For the SELECT statement from two tables without a JOIN clause:

• Option B: You can add a WHERE clause with filtering criteria.
• Option E: It returns the same rows as SELECT * FROM bricks CROSS JOIN colors.

Options A, C, and D are incorrect because:

• Option A: You cannot add an ON clause without changing the comma-separated FROM clause to a proper JOIN.
• Option C: It returns the Cartesian product, not the sum, of the rows from BRICKS and COLORS.
• Option D: A USING clause is not applicable without changing the syntax to a proper JOIN.

Assuming the statement involves creating a new table SALES1 from the existing SALES table:

• B. SALES1 created with 55,000 rows: If the statement involved creating SALES1 by selecting all rows from SALES (such as with a CREATE TABLE AS SELECT operation), then all 55,000 rows would be copied if no WHERE clause limited the selection.
• E. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table: When creating a table using the CREATE TABLE AS SELECT syntax, any NOT NULL constraints on columns in the original table will also apply to the new table.

Incorrect options:

• A: PRIMARY KEY and UNIQUE constraints are not automatically copied with a CREATE TABLE AS SELECT; they must be explicitly redefined.
• C: The table is created with rows if the original SELECT statement (assumed here) selected rows.
• D: The statement about creating a table with 1 row is incorrect based on the assumed full selection of the original table's rows.