New Release 1z0-071 Oracle PL Questions

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.

A look at the queries and how Oracle Database handles data type conversion:

A: This statement takes a substring of the JOIN_DATE column. Since JOIN_DATE is a DATE type and SUBSTR function expects a string, implicit conversion will occur from DATE to string based on the NLS_DATE_FORMAT setting. However, this will not require explicit conversion in the query itself.

B: This statement tries to add '20' to JOIN_DATE. Oracle Database does not support adding a string to a DATE implicitly. An explicit conversion of '20' to a number is required, or using the INTERVAL keyword to add days or years as appropriate.

C: This statement concatenates the date with an empty string and the salary. The database will implicitly convert the JOIN_DATE from a DATE to a string using the NLS_DATE_FORMAT setting and then perform the concatenation.

D: This query attempts to compare a DATE column with a string literal '*10-02-2018'. An explicit conversion using TO_DATE is needed to convert the string to a DATE data type with the appropriate date format mask. In this case, because the string does not follow the NLS_DATE_FORMAT 'DD-MON-YY', it will not be implicitly converted correctly.

E: This statement adds '120.50' to SALARY. Oracle Database will implicitly convert the string '120.50' to a number before performing the arithmetic addition, as SALARY is a NUMBER data type.

Regarding creating tables in an Oracle database:

B. The same table name can be used for tables in different schemas: In Oracle, a schema is essentially a namespace within the database; thus, the same table name can exist in different schemas without conflict, as each schema is distinct.

C. A system privilege is required: To create tables, a user must have the necessary system privileges, typically granted explicitly or through roles such as CREATE TABLE or administrative privileges depending on the environment setup.

Incorrect options for all three repeated questions:

A: Oracle SQL does not allow specifying the maximum number of rows directly in a CREATE TABLE statement; this is controlled by storage allocation and database design rather than table creation syntax.

D: Creating an external table does not create the physical file. It merely creates a table structure that allows access to data stored in an external file specified in the directory; the file itself must already exist or be managed outside of Oracle.

E: A primary key constraint is not mandatory for creating tables. While it is a common practice to define a primary key to enforce entity integrity, it is not required by the Oracle SQL syntax for table creation.

These answers and explanations are aligned with Oracle Database 12c SQL documentation and standard practices.

In Oracle Database 12c, when dealing with NULL values in arithmetic expressions, the functions NVL, NVL2, and equivalent techniques using NULLIF or arithmetic operations with NULL can be crucial.

Option A: SELECT * FROM employees WHERE salary + NULLIF(commission, 0) >= 20000;

NULLIF(value1, value2) returns NULL if value1 is equal to value2, otherwise it returns value1. In this case, if commission is NULL, it treats it as 0.

This query correctly adds salary and commission (assuming 0 when commission is NULL) and checks if the total is at least 20000.

Option B: SELECT * FROM employees WHERE salary + NVL2(commission, commission, 0) >= 20000;

NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not NULL; otherwise, it returns expr3. Here, if commission is not NULL, it uses commission, otherwise 0.

This condition correctly calculates salary + commission (assuming commission as 0 if it is NULL) and checks if the total is at least 20000.

Option D: SELECT * FROM employees WHERE salary + NVL(commission, 0) >= 20000;

NVL(expr1, expr2) returns expr2 if expr1 is NULL; otherwise, it returns expr1. This query treats commission as 0 if it is NULL.

It correctly sums salary and commission and compares the result against 20000.

Options C, E, and F contain either syntax errors or logical errors in handling NULLs and comparisons.