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

C: True. A UNIQUE constraint in Oracle SQL allows for the inclusion of NULL values; specifically, it permits multiple NULLs in a column or set of columns but ensures that all non-NULL values are unique.

D: True. A PRIMARY KEY constraint can indeed be added to a table after it has been created and even after it has been populated, as long as the existing data does not violate the primary key constraint rules (i.e., all values must be unique and not NULL).

F: True. A UNIQUE constraint can utilize a pre-existing index on the columns it covers. If a suitable index already exists, Oracle can use this index to enforce the constraint, optimizing performance and resource utilization.

G: True. Columns that are part of a FOREIGN KEY constraint can contain NULL values. This is permissible under SQL standards and Oracle implementation, as a NULL foreign key value is considered to not refer to any row in the referenced table and thus does not violate referential integrity.

To find customer names where the last name starts with Mc or MC, the correct queries are:

A. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'MC%'This query converts the substring of cust_name that comes after the first space (which should correspond to the last name) to uppercase and checks if it starts with 'MC', which will match both 'Mc' and 'MC'.

E. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1) LIKE 'Mc%'This query checks if the substring of cust_name after the first space starts with 'Mc'. However, this query will only match last names that start with 'Mc' and not 'MC' unless the database is using a case-insensitive collation.

Options B, C, and D are incorrect:

B is incorrect because the syntax is not valid in Oracle SQL; the LIKE clause cannot be used to match multiple patterns in that way.

C is incorrect because INITCAP would not only capitalize the first letter of 'mc' or 'Mc' but would lowercase all other letters, which is not the intended action.

D is incorrect because, like option C, INITCAP is not the appropriate function for this use case and it will not correctly identify names that start with 'MC'.

In Oracle SQL, date arithmetic can be performed directly, subtracting one date from another to get the number of days between them:

Option A:

SELECT (SYSDATE - DATE '2019-01-01') / 1 FROM DUAL; This query successfully calculates the difference between the current date and a specific date.

Option D:

SELECT SYSDATE - DATE '2019-01-01' - 1 FROM DUAL; This query subtracts a specific date from the current date and then subtracts 1 more day, which will execute successfully.

Option F:

SELECT SYSDATE - 1 - DATE '2019-01-01' FROM DUAL; Similar to D, this will successfully compute the date difference minus one day.

Options B, C, and E are incorrect because:

Option B and Option C: You cannot divide by a date or divide a date by a number.

Option E: You cannot subtract a date from a number.

Roles are named collections of privileges in Oracle databases.

A. False. Roles cannot be assigned to other roles using the ALTER ROLE statement.

B. False. Roles can be assigned to both users and other roles.

C. True. Roles are assigned to users using the ALTER USER statement, but this is not the only method.

D. True. A single role can be assigned to multiple users, simplifying the management of user privileges.

E. True. A single user can be assigned multiple roles.

F. False. Privileges are not assigned to a role using the ALTER ROLE statement.

G. True. Privileges are assigned to a role using the GRANT statement.

The requirements specify that we need to select book titles based on a price condition and a date condition, and then sort the results by the date of purchase.

A. This query will not execute successfully because there is a syntax error in the price condition. It should be price < 500 OR price > 1000.

B. This query is incorrect. The requirement specifies that the price must be less than 500 or more than 1000, not in a list of values.

C. This query is correct. It selects books where the price is not between 500 and 1000 (thus, less than 500 or more than 1000) and the purchase date is before January 17, 2007, ordered by purchase date in descending order.

D. This query is incorrect because it selects books with prices between 500 and 1000, which is not what the requirement specifies.

Given the requirement to list PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for products with QUANTITY_ON_HAND less than five:

B. This query correctly joins the two tables on PRODUCT_ID and filters rows where QUANTITY_ON_HAND is less than five. However, the syntax presented here might be incorrect due to improper placement of the condition within the ON clause; it should be in a WHERE clause.

D. This query correctly joins the two tables on PRODUCT_ID and applies the condition in a WHERE clause, which is the proper way to filter rows after performing the join.

Incorrect options:

A: The syntax uses NATURAL JOIN and AND incorrectly; also, conditions should be in a WHERE clause, not combined with the JOIN clause.

C: Similar to A, this option incorrectly places a condition directly in the JOIN clause without using a WHERE clause.

In Oracle Database 12c, the operations related to dropping columns from a table include several behaviors, each specified clearly in Oracle documentation and best practices:

B. A column drop is implicitly committed: Dropping a column in Oracle using the ALTER TABLE ... DROP COLUMN command is a DDL (Data Definition Language) operation, which means it cannot be rolled back. DDL commands in Oracle are automatically and implicitly committed, meaning that once a column is dropped, the action is finalized immediately.

C. A column that is referenced by another column in any other table cannot be dropped: In Oracle, if a column is being referenced by a foreign key constraint or any dependency from another table, you cannot directly drop it until those references are removed or disabled. This ensures data integrity across related tables.

E. A primary key column cannot be dropped: The primary key constraint is critical for identifying unique rows within a table. Oracle does not allow the dropping of columns that are part of a primary key without first dropping the constraint or modifying it to exclude the column you intend to drop.

References:

Oracle Database SQL Language Reference 12c, specifically sections discussing DDL operations and constraints.

In Oracle Database 12c, the operations related to dropping columns from a table include several behaviors, each specified clearly in Oracle documentation and best practices:

B. A column drop is implicitly committed: Dropping a column in Oracle using the ALTER TABLE ... DROP COLUMN command is a DDL (Data Definition Language) operation, which means it cannot be rolled back. DDL commands in Oracle are automatically and implicitly committed, meaning that once a column is dropped, the action is finalized immediately.

C. A column that is referenced by another column in any other table cannot be dropped: In Oracle, if a column is being referenced by a foreign key constraint or any dependency from another table, you cannot directly drop it until those references are removed or disabled. This ensures data integrity across related tables.

E. A primary key column cannot be dropped: The primary key constraint is critical for identifying unique rows within a table. Oracle does not allow the dropping of columns that are part of a primary key without first dropping the constraint or modifying it to exclude the column you intend to drop.

References:

Oracle Database SQL Language Reference 12c, specifically sections discussing DDL operations and constraints.

In the context of SQL statements using SET operations like UNION in Oracle Database 12c:

A. The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query. This is correct. Oracle allows the union of columns as long as their data types are implicitly convertible, not necessarily identical.

C. The number, but not names, of columns must be identical for all SELECT statements in the query. This is correct. For a UNION operation to be valid, all SELECT statements involved must have the same number of columns, although their names and exact data types need not match.

Options B, D, and E are incorrect:

B is incorrect because exact data type matches are not required, only that they be implicitly convertible.

D is also incorrect for the same reason as B; it's enough that the types are compatible, not identical.

E is incorrect as the names

Substitution variables in Oracle are used to replace a value dynamically during the execution of SQL statements. The behavior of these variables is well-documented:

C. A substitution variable prefixed with & always prompts only once for a value in a session: This is true. In a session, when you use a single ampersand (&), SQL*Plus or SQL Developer will prompt for the value the first time the variable is encountered. The value for this variable will then be reused for the remainder of the session unless it is redefined.

D. A substitution variable can be used with any clause in a SELECT statement: Substitution variables can be placed in any part of a SQL statement, including the SELECT, WHERE, GROUP BY, ORDER BY, etc. They are not limited to any specific clause.

References:

Oracle SQL*Plus User's Guide and Reference, which discusses substitution variables.

B: True. The Oracle Database MERGE statement can use a subquery that involves views to generate the source data for the merge operation. This allows for greater flexibility in specifying the data to be merged.

C: True. MERGE can conditionally combine rows from one or more source tables (or views, or the results of a subquery) to insert or update rows in a single target table based on whether or not a match exists.

D: True. MERGE can indeed use subqueries to produce source rows. The source data for a merge operation can be a table, view, or the result of a subquery.

The MERGE statement is a powerful SQL operation in Oracle that allows for conditional INSERT or UPDATE operations in one statement, often referred to as an "upsert" operation. It can handle complex scenarios where the decision to insert or update is based on whether the data matches between the target and source datasets.

References:Oracle SQL documentation on the MERGE statement provides information on how it can use different data sources, including subqueries and views, and perform conditional operations on the target table.