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

Primary and foreign key constraints are foundational to relational database design:

Option A: Incorrect. Both primary and foreign key constraints can be defined at the column or table level.

Option B: Incorrect. Foreign key columns do not need to have the same names as the corresponding primary key columns in the parent table. They must be of the same data type and size.

Option C: Incorrect. If a foreign key constraint is enforced without ON DELETE CASCADE, deleting the parent row will either prevent the delete (due to constraint) or require a prior deletion or update of the child rows.

Option D: Correct. A table can indeed have only one primary key, which uniquely identifies each row, but it can have multiple foreign keys linking to primary keys of different tables.

Option E: Correct. Both types of keys can be defined at either level, providing flexibility in how constraints are applied and enforced.

Option F: Incorrect. A table can have multiple foreign keys as stated, each referencing a different parent table.

Option G: Correct. If the ON DELETE CASCADE option is set for a foreign key, then deleting a parent row will automatically delete the corresponding child rows, maintaining referential integrity.

When creating a new table with a query, the statements that are true are:

A. SALES1 is created with 1 row.This is incorrect actually; the given query with WHERE 1=1 will retrieve all rows from the sales table, not just one row. Therefore, SALES1 will be created with the same number of rows as in the SALES table, assuming there are no WHERE clause conditions limiting the rows.

D. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table.This is true. When a table is created using a CREATE TABLE AS SELECT statement, the NOT NULL constraints on the columns in the selected columns are preserved in the new table.

Options B and C are incorrect:

B is incorrect because the primary key and unique constraints are not carried over to the new table when using the CREATE TABLE AS SELECT syntax.

C is incorrect in the context of this scenario. However, if interpreted as a standalone statement (ignoring A), C would be the correct description of the outcome since WHERE 1=1 will not filter out any rows.

When evaluating whether expressions in SQL queries are valid, consider data type compatibility and the operations performed:

Option A: Valid. The expression performs arithmetic operations (multiplication and division) on numeric data types (unit_price, constants). These operations are allowed and make sense in a mathematical context.

Option B: Valid. This query calculates the difference between two dates (expiry_date and delivery_date), which results in a numeric value representing the number of days between the dates. The result is then multiplied by 2, which is a valid operation on a numeric result.

Option C: Invalid. The expression unit_price || 5 attempts to concatenate a numeric value with a number, which is not valid without explicit conversion to a string. Moreover, the use of quotes around "Discount" is syntactically incorrect in this context.

Option D: Invalid. The expression expiry_date * 2 attempts to multiply a DATE datatype by a numeric value, which is not a valid operation.

Option E: Invalid. Similar to Option C, it incorrectly attempts to concatenate a number directly with a numeric value without conversion. Additionally, the aliasing with quotes is incorrectly placed.

Option F: Valid. This query simply adds two numeric columns (unit_price and surcharge), which is a valid and commonly used arithmetic operation in SQL.

A. Schema owners indeed can grant privileges on objects in their schema to other users or roles, making this statement true.

B. Roles in Oracle can be granted both object and system privileges, making this statement true as well.

C. Not all types of schema objects have associated object privileges. For example, synonyms do not have object privileges because they are just aliases for other objects.

D. The DBA role is a powerful role, but creating roles can be done by any user granted the necessary privileges, not just users with the DBA role.

E. Object privileges on a table do not automatically apply to all synonyms for that table. Synonyms are separate objects that must have privileges granted explicitly.

F. The privilege to grant system privileges is controlled by the GRANT ANY PRIVILEGE system privilege, making this statement true.

References:

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

Oracle Database Security Guide, 12c Release 1 (12.1): "Administering User Privileges, Roles, and Profiles"

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.

For the SELECT statement from two tables without a JOIN clause:

Option B: You can add a WHERE clause with filtering criteria.

A WHERE clause can be added to a query to filter the results based on specified conditions.

Option E: It returns the same rows as SELECT * FROM bricks CROSS JOIN colors.

A comma-separated list of tables in a FROM clause is equivalent to a CROSS JOIN, resulting in a Cartesian product of the tables.

Options A, C, and D are incorrect because:

Option A: You cannot add an ON clause without changing the comma-separated FROM clause to a proper JOIN.

Option C: It returns the Cartesian product, not the sum, of the rows from BRICKS and COLORS.

Option D: A USING clause is not applicable without changing the syntax to a proper JOIN.

Regarding single row functions in Oracle Database 12c:

C. CEIL: can be used for positive and negative numbers. This is true. The CEIL function rounds a number up to the nearest integer, and it works for both positive and negative values.

D. TRUNC: can be used with NUMBER and DATE values. This is correct. The TRUNC function can truncate numbers or dates to a specified precision.

Options A, B, and E are incorrect:

A is incorrect because CONCAT function typically concatenates two strings; to concatenate more, you must nest CONCAT calls or use the || operator.

B is incorrect because FLOOR returns the largest integer less than or equal to the specified number, not greater.

E is incorrect because MOD returns the remainder of a division operation, not the quotient.