Complete 1z0-071 Oracle Materials

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

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

• Option A:
• Option D:
• Option F:

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.

When performing DML operations on a view, certain restrictions apply:

• D. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword: If the defining query of a view contains the DISTINCT keyword, it cannot be used for certain DML operations, such as INSERT and UPDATE, because the set of rows it represents is not directly modifiable.
• E. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains aggregating functions: Aggregating functions create a result that summarises multiple rows and cannot be reversed to point to a single row for modification.
• F. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view: If a column with a NOT NULL constraint is not included in the view's defining query, and it does not have a default value, it is not possible to insert through the view because it would violate the NOT NULL constraint.

References:

• Oracle Database SQL Language Reference 12c, particularly the sections on views and the restrictions on DML operations through views.

D. True, after setting the DEPARTMENT_ID column to UNUSED, you can add a new column with the name DEPARTMENT_ID to the EMPLOYEES table. The UNUSED clause does not delete the column, it only marks it as no longer being used.E. True, once a column is marked as UNUSED, you cannot make updates to it. It becomes inaccessible for DML operations.

A, B, C, and F are not correct because: A. Once a column is set to UNUSED, it is not available for queries. B. The storage space is not immediately released after issuing a COMMIT; instead, the actual removal and space reclamation happen when you subsequently issue the DROP UNUSED COLUMNS operation. C. The DEPARTMENT_ID column is not set to null; instead, it's marked as UNUSED, which means it is no longer available for use. F. UNUSED columns are not placed into the recycle bin; they are just marked for deletion, and space can be reclaimed with the DROP UNUSED COLUMNS command.

References:

• Oracle documentation on ALTER TABLE: Oracle Database SQL Language Reference
• Understanding ALTER TABLE SET UNUSED: Oracle Database Administrator’s Guide