Newly Released Oracle 1z0-071 Exam PDF

To achieve the desired output for the query from the MEMBERS table:

• C. ORDER BY 1, LNAME DESC: This clause correctly sorts the results first by the first column (city) in ascending order by default, and then by the alias LNAME (last_name) in descending order.
• E. WHERE city LIKE '%AN%': This clause correctly filters the rows to include only those cities containing the string 'AN' anywhere in the city name, using the LIKE operator which is suitable for pattern matching.

Incorrect options:

• A: This would order both columns in ascending order, which does not meet the requirement for last_name to be in descending order.
• B: This misplaces the order priorities and uses explicit column names that contradict the sorting requirement specified.
• D: Incorrect syntax for a LIKE clause; equality operator cannot be used with wildcards.
• F: The IN operator is incorrectly used here with a pattern, which is not valid syntax.

The question describes a scenario with multiple conditional statements about data manipulation based on certain criteria related to employee_id and salary. The options detail different conditions and corresponding actions (insertions into various tables). Given the conditions specified in each option, here are the accurate answers based on logical deduction as Oracle SQL does not directly define these scenarios; instead, it provides the mechanisms (like IF conditions, WHERE clauses, etc.) to implement such logic:

• Option A: Regardless of salary, only if the employee id is less than 125, insert EMPLOYEE_ID, MANAGER_ID, SALARY into the MGR_HISTORY table.
• Option C: Only if the salary is 20000 or less and the employee id is less than 125, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.
• Option E: If the salary is 20000 or less and the employee id is less than 125, insert EMPLOYEE_ID, HIRE_DATE, and SALARY into the SAL_HISTORY table.

Regarding INTERVAL data types in Oracle Database 12c:

• A. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years. This is true. The INTERVAL YEAR TO MONTH data type stores a period of time using years and months.
• E. INTERVAL DAY TO SECOND columns support fractions of seconds. This is true. The INTERVAL DAY TO SECOND data type can store days, hours, minutes, seconds, and fractional seconds.

Options B, C, D, and F are incorrect:

• B is incorrect because data types between INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH are not compatible.
• C is incorrect as it incorrectly limits INTERVAL YEAR TO MONTH to a single year.
• D is incorrect; the YEAR field can be negative to represent a past interval.
• F is incorrect as INTERVAL YEAR TO MONTH supports intervals that can span multiple years, not just annual increments.

To calculate the number of days between two dates in Oracle SQL, you can simply subtract the earlier date from the later date, and Oracle will return the number of days between them.

Option A uses SYSDATE to get the current date and TO_DATE to convert the string '01-JANUARY-2019' to a date using the default date format, which matches 'DD-MON-RR'. The subtraction here will correctly give the number of days between these two dates.

Option C is similar to A but includes the ROUND function, which is used to round the number to the nearest whole number. Since the difference between two dates in Oracle SQL is a decimal value representing the number of days and fractional days, rounding will give us the whole number of days, which is what we want.

Options B, D, and E are incorrect because they either use an invalid date format (the default format is 'DD-MON-RR', not 'DD/MONTH/YYYY' or 'DD-MON-YYYY'), perform invalid operations (such as subtracting a string from a date), or misuse SYSDATE.