Newly Released Oracle 1z0-071 Exam PDF

Synonyms in Oracle are aliases for database objects that can simplify SQL statements for database users.

A. The term "GLOBAL" is not used in the creation of synonyms in Oracle.

B. The statement without the keyword PUBLIC will create a private synonym that is only accessible to the user creating the synonym, not all users.

C. The correct syntax does not include PUBLIC as a prefix to the synonym name itself, making this option incorrect.

D. You cannot specify the SYS schema for creating synonyms, as it is reserved for system objects.

E. This is the correct syntax to create a public synonym, which makes the underlying object accessible to all users.

References:

• Oracle Database SQL Language Reference, 12c Release 1 (12.1): "CREATE SYNONYM"

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.

Scalar subquery expressions in Oracle SQL have specific rules:

• Option C: You must enclose them in parentheses.
• Option D: They can return at most one row.

Options A, B, and E are incorrect based on Oracle SQL functionalities:

• Option A is incorrect because scalar subqueries can indeed be correlated with the parent query.
• Option B is true but not in the context of default constraints for table columns in the CREATE TABLE statement.
• Option E is incorrect because scalar subqueries can only return a single column by definition.

The requirements specify that the data type must support date arithmetic, store a specific period (up to 10 years in terms of days), and be used for day-level calculations. The appropriate data type is:

• A: Incorrect. TIMESTAMP WITH TIMEZONE stores date and time with time zone information, which is not specifically needed for arithmetic or interval storage.
• B: Incorrect. TIMESTAMP stores date and time without considering time zone or providing interval arithmetic functionality.
• C: Incorrect. TIMESTAMP WITH LOCAL TIMEZONE adjusts the time zone but does not inherently support interval arithmetic or storage.
• D: Incorrect. INTERVAL YEAR TO MONTH is used for storing periods in terms of years and months, not suitable for daily calculations.
• E: Correct. INTERVAL DAY TO SECOND allows storage of periods in terms of days, hours, minutes, and seconds, meeting all the given requirements for precision and functionality in date arithmetic related to daily calculations.