Pass the Oracle Oracle PL 1z0-071 Questions and answers with CertsForce

Single-row functions in SQL operate on each row independently and can modify the returned value:

Option A: Incorrect. Single row functions can be used in multiple parts of a SELECT statement, including SELECT, WHERE, and ORDER BY clauses.

Option B: Incorrect. Single row functions can accept more than one argument, such as the CONCAT function, which can accept multiple string arguments.

Option C: Incorrect. They return one result for each row processed, not per table.

Option D: Correct. Single row functions can take various types of arguments including column names, literals, variables, and other expressions.

Option E: Correct. Functions can be nested within other functions, allowing complex expressions and calculations.

Option F: Correct. The data type of the result can differ from the arguments’ data types, such as the SUBSTR function returning a VARCHAR2 even when used on a number after converting it to a string.

Regarding valid INSERT statements into the ORDERS table:

B. This is a valid statement because it uses TO_DATE for potentially setting a NULL date and provides defaults implicitly where not specified, adhering to the column constraints and requirements.

D. This statement is correctly formatted and provides values for non-optional fields, using DEFAULT implicitly for unspecified optional fields.

Incorrect options:

A: The subquery format and values provided do not match the expected column count and types for direct insert.

C: Incorrect format due to an improper string for the ORDER_MODE and possibly incomplete values.

E: Incorrect syntax; DEFAULT should be correctly capitalized, and the value order and type must match the table structure.

A: LAST_DAY does not only return the last day of the current month; it returns the last day of the month based on the date argument passed to it, which may not necessarily be the current month. Thus, statement A is incorrect.

B: CEIL requires a numeric argument and returns the smallest integer greater than or equal to that number. Thus, statement B is incorrect.

C: ADD_MONTHS function adds a specified number of calendar months to a date. This statement is correct as per the Oracle documentation.

D: ADD_MONTHS can work with a character string if the string can be implicitly converted to a DATE, according to Oracle SQL data type conversion rules. Therefore, statement D is correct.

E: LAST_DAY does not specifically return the last day of the previous month; it returns the last day of the month for any given date. Thus, statement E is incorrect.

F: CEIL returns the smallest integer greater than or equal to the specified number, not the largest integer less than or equal to it. Hence, statement F is incorrect.

G: LAST_DAY returns the last day of the month for the date argument passed to the function, which aligns with the definition in Oracle's SQL reference. Therefore, statement G is correct.

A. A customer can exist in many countries. This is true as customers can have multiple addresses or operations in different countries, and a database design can reflect this by allowing multiple country entries for a single customer1.

C. The statement will fail because subquery may not be I contained in a values clause. In Oracle Database 12c, a subquery cannot be used within the VALUES clause of an INSERT statement. The correct approach would be to use the subquery in conjunction with the INSERT INTO … SELECT syntax if multiple rows are derived from a subquery2.

F. A product can have a different unit price at different times. It is common for products to have different unit prices at different times due to various factors such as promotions, discounts, or changes in cost price. This can be represented in a database by having a price history table or a similar mechanism to track the changes in price over time1.

Note: The other options are incorrect because:

B. The statement about the SALES table failing if a row already exists for product 23 is not necessarily true. Oracle allows for multiple rows with the same product ID if the table is designed to handle such cases, like having a composite primary key or no constraints preventing duplicates.

D. Without specific information about the SALES table’s design, we cannot verify the number of foreign keys it has.

E. The statement about the successful execution and insertion of a new row into the SALES table is too vague without the context of the actual SQL statement being referred to.

The || operator is used in Oracle SQL to concatenate strings, and the q quote operator allows you to use alternative quoting mechanisms to avoid conflicts with single quotes within a string.

In options A, B, C, D, E, and F, the q-quote operator is used in different ways to define string literals.

The correct use of the q quote operator requires the format q'[...]', where [...] is the quote delimiter of your choice and can be any single character such as {, [, (, <, or others.

Option D is the correct answer because it follows the correct syntax for the q quote operator and concatenates the literal string correctly with the value of PROD_ID.

Here is the corrected syntax from option D:

SELECT PROD_ID || q'[('s not available)]' "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';

This query will execute successfully and is the only one that correctly uses the q quote operator.

The WITH GRANT OPTION clause in Oracle SQL allows the grantee to grant the privilege they have received to another user or role.

E. It cannot be used to pass on privileges to PUBLIC by the grantee: The WITH GRANT OPTION does not allow the grantee to pass on privileges to PUBLIC. Only the object's owner or a user with the GRANT ANY OBJECT PRIVILEGE system privilege can grant privileges to PUBLIC.

F. It can be used to pass on privileges to other users by the grantee: This is true. When a user receives privileges with the WITH GRANT OPTION, they can grant that privilege to another user or role.

References:

Oracle Database SQL Language Reference 12c, specifically sections on user privileges and the WITH GRANT OPTION.

To format a date in Oracle SQL, TO_CHAR function is used to convert date types to string with a specified format:

Option A: SELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;

This correctly applies the date format model 'FMDAY, DD MONTH, YYYY', which will display the date as requested: "MONDAY, 21 MAY, 2019". FM modifier is used to remove padding blanks or leading zeros.

Options B, C, and D do not achieve the desired output:

Option B is incorrect because TO_DATE converts a string to a date, not format a date.

Option C and D have incorrect format strings that do not match the required output format.

The function ADD_MONTHS(hire_date, 6) adds 6 months to the hire_date. The function NEXT_DAY(date, 'day_name') finds the date of the first specified day_name after the date given. In this case, 'MONDAY' is used to find the date of the first Monday after the hire_date plus 6 months.

Option A is correct as it accurately composes both ADD_MONTHS and NEXT_DAY functions to fulfill the requirement.

Options B, C, and D do not provide a valid use of the NEXT_DAY function, either because of incorrect syntax or incorrect logic in calculating the required date.

The Oracle Database SQL Language Reference for 12c specifies how these date functions should be used.

C: True. A SQL statement may include multiple single row subqueries in different parts of the statement, such as in the SELECT list, WHERE clause, or HAVING clause. Each subquery must independently satisfy the requirement of returning a single row to avoid runtime errors.

D: True. Single row subqueries can be used in the HAVING clause. This allows for filtering groups based on conditions evaluated against individual or aggregated values returned by the subquery. The subquery must return a single value to be valid in this context.

E: True. Single row subqueries are often placed on the right side of the comparison operator in a SQL condition. This positioning is typical because the left side often references a column or an expression related to the main query, while the subquery on the right side dynamically provides a value for comparison.

A. True, the owner of an object in Oracle automatically has all privileges on that object. This means they can perform any operation on the object, including SELECT, INSERT, UPDATE, DELETE, and so forth.C. True, the REFERENCES privilege must be granted explicitly by the table owner to another user or role to allow the grantee to define foreign key constraints that reference the primary or unique key of the owner's table. This is crucial in scenarios involving relational integrity constraints across tables owned by different users.

References:

Oracle documentation on object privileges: Oracle Database SQL Language Reference

Explanation of REFERENCES privilege: Oracle Database Security Guide