Oracle Oracle PL 1z0-071 New Questions

• A: True, the name of a column can be changed in Oracle using the ALTER TABLE ... RENAME COLUMN command.
• B: False, you cannot change a column's data type from NUMBER to VARCHAR2 if the table contains data, unless the change does not result in data loss or inconsistency.
• C: True, it is possible to change a DATE data type column to TIMESTAMP because TIMESTAMP is an extension of DATE that includes fractional seconds. This operation is allowed if there is no data loss.
• D: False, any column that is not part of a primary key or does not have a non-deferrable constraint can generally be dropped unless it contains data that does not allow for such a change.
• E: False, the DEFAULT clause can be added to a column provided there is no data that contradicts the default value or it doesn't violate any constraints.

These statements are verified against the Oracle Database 12c SQL documentation, specifically the sections on data types, the ALTER TABLE command, and the use of literals in SQL expressions.

In a CROSS JOIN (also known as a Cartesian join), each row from the first table (emp) is joined to every row of the second table (dept). When we apply the filter for job = 'MANAGER' and dept.deptno IN (10, 20), we are restricting the results to managers in departments 10 and 20.

From the given data:

• There are 2 managers in department 10 (CLARK and KING).
• There is 1 manager in department 20 (JONES).

With a cross join, each of these emp records will join with each dept record where deptno is 10 or 20. Since there are two departments (10 and 20) in the dept table, each employee will match with two departments.

Therefore, the result set will be:

• CLARK with accounting (dept 10) and research (dept 20)
• KING with accounting (dept 10) and research (dept 20)
• JONES with accounting (dept 10) and research (dept 20)

That gives us a total of 6 rows, which means the correct answer is B: 6.

B. True. Oracle databases guarantee read consistency at the transaction level, meaning that each query can only see data that has been committed before the query began executing.

C. True. When an UPDATE statement is executed, Oracle locks each row as it is updated to prevent other transactions from modifying it until the transaction is committed or rolled back.

A is incorrect because updates made by one user cannot be rolled back by another user. D is incorrect because a query can access tables in other schemas if proper permissions are granted. E is incorrect because a user can own only one schema, which has the same name as the user in an Oracle database.

In reference to the attributes of the PRODUCT_DETAILS table:

A. This statement is false. If PRODUCT_PRICE has no value (i.e., it is NULL), it cannot be used directly in arithmetic expressions because any operation with NULL results in NULL.

B. This statement is true. PRODUCT_ID has the NOT NULL constraint and is of type NUMBER, making it eligible to be a PRIMARY KEY. Primary keys require all values to be unique and not null.

C. This statement is true. EXPIRY_DATE is of type DATE, and it cannot be used in arithmetic expressions directly without a date function that operates on dates.

D. This statement is false. By default, columns of type DATE do not have a default value unless explicitly assigned using the DEFAULT keyword in the column definition.

E. This statement is false. By default, numeric columns do not have a default value unless specified with the DEFAULT keyword.

F. This statement is false. The statement about PRODUCT_NAME not containing duplicate values would be true if it were a UNIQUE or PRIMARY KEY, but there is no such constraint indicated in the provided table description.