Oracle Database 12c SQL 1z0-071 Exam Questions with Experts Answers Updated Recently

A: This statement is correct. It uses the TO_DATE function with a proper date string, format mask, and NLS_DATE_LANGUAGE setting.

B: This statement will not execute successfully because the syntax of the TO_CHAR function is incorrect. The date string should be a DATE data type when used with TO_CHAR, and the format mask and NLS parameter are incorrectly specified.

C: This statement will not execute successfully because it is redundant to use TO_CHAR and then immediately convert it back to a date with TO_DATE without specifying a proper format mask.

D: This statement is correct. It converts a string to a DATE using TO_DATE and then back to a string with TO_CHAR, without specifying a format which defaults to the session’s NLS_DATE_FORMAT.

E: This statement will not execute successfully because TO_CHAR is used incorrectly; the first argument must be of DATE data type when you're using a date format mask.

References for the TO_DATE and TO_CHAR functions and their proper usage can be found in the Oracle Database SQL Language Reference 12c documentation.

The query's goal is to return a list of countries that have no departments linked to them.

Option B and Option D are the correct answers because they use set operations that will effectively return countries that do not have a corresponding entry in the departments table:

Option B uses the NOT IN subquery to exclude countries that have departments linked to them. It looks for country_id values in the countries table that are not present in the list of country_id values associated with locations that are, in turn, associated with departments. This will correctly return countries that have no departments.

Option D uses the MINUS set operator, which subtracts the results of the second SELECT statement from the results of the first. This statement will return all countries from the countries table minus those that have an associated department_id in the departments table, effectively listing countries with no departments.

Option A and Option C are incorrect because:

Option A will not execute successfully as it tries to join tables using a column (country_id) that doesn't exist in the departments table, which will lead to an error.

Option C's use of INTERSECT is incorrect for this requirement. INTERSECT returns only the rows that exist in both queries. Since we want countries with no departments, using INTERSECT would actually return the opposite of what is required.

References:

Oracle Documentation on NOT IN clause: SQL Language Reference - Subquery

Oracle Documentation on MINUS operator: SQL Language Reference - Set Operators

Therefore, the correct options are B and D, which use subquery exclusion and the MINUS set operator, respectively, to accurately identify and return countries without departments.

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.