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

The correct way to compute the total annual compensation, which includes the monthly salary and the monthly commission (if any), is:

• Option C: SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) AS annual_comp FROM employees;

Options A, B, and D are incorrect because:

• Option A: Does not handle the case where the commission percentage is NULL which would result in NULL for the entire expression when added to the monthly salary.
• Option B: Does not consider that the commission percentage might be NULL which could lead to incorrect calculations (or NULL values if commission is NULL).
• Option D: Incorrectly adds the monthly commission percentage directly to the annual salary without considering that the percentage needs to be applied to the salary.

The statement is trying to create a table with columns of different data types and constraints. Here's what needs to be corrected:

• C: In Oracle, the LONG data type is used for character data of variable length up to 2 Gigabytes, but it is deprecated, and you should use CLOB or VARCHAR2 instead. Furthermore, a table cannot have more than one LONG column.
• D: The NOT NULL constraint should be specified at the column level, not at the table level. The correct syntax for creating a table with a NOT NULL constraint is to define it inline with the column definition, like this:

ename VARCHAR2(15) CONSTRAINT ename_nn NOT NULL,

The other options are incorrect:

• A: The foreign key constraint syntax is correct; the word CONSTRAINT is followed by the constraint name and then the REFERENCES clause.
• B: The foreign key constraint can be defined at the column level.
• E: While it's a good practice to name constraints, it is not mandatory for the primary key constraint to have a name; Oracle will generate one if it's not provided.

References:

• Oracle Documentation on CREATE TABLE: SQL Language Reference - CREATE TABLE
• Oracle Documentation on Data Types: SQL Language Reference - Data Types

In SQL, the correct usage of conversion functions is crucial when performing operations on dates. Oracle uses the TO_DATE function to convert a string to a date, and the TO_CHAR function to convert dates or numbers to strings.

• Statement C is correct: WHERE order_date > TO_DATE('JUL 10 2018','MON DD YYYY'); is a proper use of the TO_DATE function. It converts the string 'JUL 10 2018' to a date type, with the format 'MON DD YYYY', which is then used to compare with the order_date.
• Statements A, B, D, and E are incorrect or misuse conversion functions: