1z0-071 Exam Dumps - Oracle PL Questions and Answers

The COUNT function is one of the most commonly used aggregate functions in SQL for determining the number of items in a group. Here's why the correct answers are B and C:

A: Incorrect. COUNT can be used with any data type, not just NUMBER. It counts rows without regard to data type.

B: Correct. COUNT(DISTINCT inv_amt) counts the number of unique non-null values in the column inv_amt. It excludes duplicates and ignores NULL values.

C: Correct. COUNT(*) counts all rows in a table, including those with duplicates and those with NULLs in any column. It is a total row count regardless of content.

D: Incorrect. You can use a WHERE clause with COUNT. The WHERE clause filters rows before counting, which is standard in SQL.

E: Incorrect. COUNT(inv_amt) counts the rows where inv_amt is not NULL. Rows with NULL in the inv_amt column are not counted.

To find customers whose last names start with "Mc" or "MC", we need to ensure our SQL query correctly identifies and compares these prefixes regardless of case variations. Let's analyze the given options:

Option B: WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE UPPER('MC%')This clause uses UPPER to convert both the extracted substring (starting just after the first space, assuming it indicates the start of the last name) and the comparison string 'MC%' to uppercase. This ensures case-insensitive comparison. The LIKE operator is used to match any last names starting with "MC", which will correctly capture both "Mc" and "MC". This option is correct.

Option C: WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'Mc%'This clause applies INITCAP to the substring, which capitalizes the first letter of each word and makes other letters lowercase. The result is compared to 'Mc%', assuming only the last name follows the space. This approach will match last names starting with "Mc" (like "McEwen"), but not "MC". However, considering we're looking for "Mc" specifically, this clause works under the assumption that "Mc" is treated as proper capitalization for these last names. Thus, it can also be considered correct, albeit less inclusive than option B.

The other options either use incorrect syntax or apply case-sensitive matches without ensuring that both "Mc" and "MC" are captured:

Option A: Contains syntax errors (unmatched quotes and wrong use of IN).

Option D: Uses case-sensitive match without combining both "Mc" and "MC".

Option E: Only matches "Mc", which is too specific.

Regarding privileges in Oracle:

Option A: Dropping user FINANCE will automatically revoke SELECT on SCOTT.EMP from user FIN_CLERK.

Dropping a user will revoke all privileges that user has granted to others.

Option B: Revoking SELECT on SCOTT.EMP from user FINANCE will also revoke the privilege from user FIN_CLERK.

Since FINANCE granted SELECT on SCOTT.EMP to FIN_CLERK with grant option, revoking the privilege from FINANCE will also cascade and revoke it from FIN_CLERK.

Options C, D, and E are incorrect because:

Option C: User FINANCE can grant privileges it owns, but CREATE SESSION is a system privilege; unless FINANCE has the 'WITH ADMIN OPTION' for CREATE SESSION, it cannot grant this privilege.

Option D: User FIN_CLERK can only grant privileges to others if it has been given those privileges with the 'WITH GRANT OPTION' and it was not given in this case.

Option E: User FINANCE can grant privileges on SCOTT.EMP to other users only if it has those privileges with 'WITH GRANT OPTION'. If the privilege was just SELECT with GRANT OPTION, it cannot grant ALL privileges.

When performing DML operations on a view, certain restrictions apply:

D. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword: If the defining query of a view contains the DISTINCT keyword, it cannot be used for certain DML operations, such as INSERT and UPDATE, because the set of rows it represents is not directly modifiable.

E. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains aggregating functions: Aggregating functions create a result that summarises multiple rows and cannot be reversed to point to a single row for modification.

F. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view: If a column with a NOT NULL constraint is not included in the view's defining query, and it does not have a default value, it is not possible to insert through the view because it would violate the NOT NULL constraint.

References:

Oracle Database SQL Language Reference 12c, particularly the sections on views and the restrictions on DML operations through views.

A. Correct. Foreign key constraints can be nullable. This allows the possibility of a row not having a link to another table. B. Incorrect. Unique key constraints do not allow multiple rows to have null values unless the constraint is defined on multiple columns. C. Incorrect. Primary key columns must be NOT NULL and unique across the table. D. Incorrect. Primary or unique key values do not refer to foreign keys; it's the foreign keys that refer to primary or unique keys. E. Correct. Foreign key constraints enforce referential integrity by requiring that each foreign key value matches a primary or unique key value in the related table.

These are standard rules in relational databases, which can be confirmed in the Oracle Database Concepts Guide.

C. True. If you have a table named 123, which is not a standard naming convention because it begins with a digit, you can reference it in SQL by using double quotation marks. The correct syntax to reference a table with a name that does not follow standard naming conventions is to enclose the name in double quotation marks.

A, B, and D are incorrect because they do not use the proper quoting mechanism for a table named with non-standard characters. In Oracle, double quotation marks are used for case-sensitive table names or those that do not follow the usual naming rules.

In the context of the MERGE statement, certain syntax rules and logical sequences must be observed:

Option B:

Correct use of the MERGE statement includes specifying the source and target correctly, and handling cases for matched and unmatched rows. This option properly structures the MERGE command with correct conditions for both matching (on prod_id) and actions (insert for unmatched, and delete under certain conditions for matched rows).

Option D:

While this option has a typographical error with "WHEN NOT NATCHED", assuming it's intended as "WHEN NOT MATCHED", it correctly describes the action for inserting into new_prices when no match is found in the target table based on the condition specified (product cost less than 200).

Options A and C contain syntax errors and logical contradictions (such as attempting to both update and delete in the same matched clause without proper separation or conditions), which are not allowed in a single WHEN MATCHED clause in Oracle SQL.

For the EMPLOYEES table and given that NLS_DATE_FORMAT is set to DD-MON-YY, the query that requires explicit data type conversion is:

D. SELECT join_date FROM employees where join_date > '10-02-2018'; This query compares the JOIN_DATE column of type DATE with a string '10-02-2018'. The string must be explicitly converted to a DATE using the TO_DATE function with a format mask that matches the NLS_DATE_FORMAT or the string literal.

Options A, B, C, and E do not necessarily require explicit data type conversion:

A does not require conversion because adding a number to a number column is a valid operation.

B is syntactically incorrect; the correct function to extract a substring is SUBSTR(join_date, 1, 2) and that would require an explicit conversion since JOIN_DATE is a DATE and SUBSTR expects a string, but it seems to be a typo.

C uses an invalid concatenation operation with the pipe symbols, which could be a typo or misunderstanding, but would not typically require a data type conversion.

E requires implicit data type conversion because adding a number to a date is valid in Oracle, which adds the number as days to the date.

To calculate the length of employment, you would compare the current date to the HIRE_DATE and calculate the difference in years.

A. This is the correct answer. The expression calculates the number of days between the current date (SYSDATE) and the HIRE_DATE, then divides by 365 to convert it to years, and checks if it is greater than 5.

B. SYSTIMESTAMP includes a time component including fractional seconds and time zone information, making this comparison incorrect.

C. There are typos in this option (CUARENT_DATE should be CURRENT_DATE and hire_data should be hire_date). Even with the correct function and column names, CURRENT_DATE returns the current date in the session time zone, not in years.

D. There are typos in this option (SYSNAYW should be SYSDATE). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.

E. This is a repetition of option D with the same issues.

F. There are typos in this option (CUNACV_DATE should be CURRENT_DATE and hire_data should be hire_date). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Date Functions"

In SQL, when you want to count occurrences of null values using the COUNT function, you must remember that COUNT ignores null values. So, if you want to count rows with null list_price, you have to replace nulls with some value that can be counted. This is what the NVL function does. It replaces a null value with a specified value, in this case, 0.

A, C, and D options attempt to count list_price directly where it is null, but this will always result in a count of zero because COUNT does not count nulls. Option B correctly uses the NVL function to convert null list_price values to 0, which can then be counted. The WHERE list_price IS NULL clause ensures that only rows with null list_price are considered.

The SQL documentation confirms that COUNT does not include nulls in its count and NVL is used to substitute a value for nulls in an expression. So option B will give us the correct count of rows with a null list_price.