Oracle Updated 1z0-071 Exam Questions and Answers by olaf

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

The data dictionary contains metadata (data about data) about the database and its objects.

A. False. DBA_ views display metadata for objects accessible to the database administrator, not only for the SYS schema.

B. True. ALL_ views display information about all the objects that the current user has access to. This does not necessarily mean the user has privileges on these objects, only that the user can see them.

C. False. The data dictionary is not accessible when the database is closed because it requires the database to be open in order to access the system tables.

D. True. Not all types of metadata are available in all three prefixes (ALL_, DBA_, USER_). Some specific metadata might only be available in one or two of these view types.

E. False. The data dictionary stores metadata in tables. The various views (ALL_, DBA_, USER_, etc.) provide different perspectives on this data.

References:

Oracle Documentation on Data Dictionary and Dynamic Performance Views: https://docs.oracle.com/cd/B28359_01/server.111/b28310/datadict.htm

The SQL query must find all customers with last names starting with A or B, regardless of case, and a credit limit below 1000:

B. WHERE (INITCAP(cust_last_name) LIKE ‘A%' OR INITCAP(cust_last_name) LIKE ‘B%') AND cust_credit_limit < 1000: The INITCAP function initializes the first letter to uppercase for comparison. However, it should be noted that using INITCAP is not necessary when using the LIKE operator with a wildcard % following a single character, because it will not correctly filter all last names that start with an upper or lower case A or B.

D. WHERE (UPPER(cust_last_name) LIKE 'A%’ OR UPPER(cust_last_name) LIKE ‘B%’) AND cust_credit_limit < 1000: This correctly filters last names beginning with A or B in any case and includes only those with a credit limit below 1000. The UPPER function is used to convert cust_last_name to uppercase before comparison.

References:

Oracle Database SQL Language Reference 12c, especially sections on string functions and conditions.

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.