Oracle 1z0-071 Exam Made Easy: Step-by-Step Preparation Guide

Regarding the use of SAVEPOINT in transactions:

C. A SAVEPOINT does not issue a COMMIT: A SAVEPOINT in Oracle SQL is used to mark a point within a transaction. It does not commit the transaction; rather, it allows partial rollbacks to the point of the SAVEPOINT without affecting other uncommitted changes outside it.

E. Rolling back to a SAVEPOINT can undo a DELETE statement: SAVEPOINT allows you to rollback DML changes like INSERT, UPDATE, and DELETE that occur after the SAVEPOINT was established, but before a COMMIT.

Incorrect options:

A: CREATE INDEX is a DDL statement, and like most DDL statements, it implicitly commits the transaction, making it impossible to rollback using a SAVEPOINT.

B: Multiple SAVEPOINTS can be defined within a single transaction, each with unique or reused names.

D: TRUNCATE is a DDL statement that also implicitly commits the transaction; hence it cannot be rolled back to a SAVEPOINT.

In Oracle Database 12c, it is important to understand the behavior and properties of constraints.

A. This statement is false. A FOREIGN KEY column in a child table does not need to have the same name as the referenced PRIMARY KEY column in the parent table. What is required is that the data type is the same and that the values in the FOREIGN KEY column correspond to values in the PRIMARY KEY column of the parent table.

B. This statement is false. A table cannot have multiple PRIMARY KEY constraints. By definition, a PRIMARY KEY is a unique identifier for a row in a table, and there can only be one such identifier.

C. This statement is false for the same reasons as B; a table can have only one PRIMARY KEY. However, it can have multiple FOREIGN KEY constraints that reference PRIMARY KEYS in other tables.

D. This is true. PRIMARY KEY and FOREIGN KEY constraints can indeed be specified at the column level with the column definition or at the table level with the ALTER TABLE statement.

E. This is true. A table can have only one PRIMARY KEY constraint because it defines a unique row identifier. However, it can have multiple FOREIGN KEY constraints referencing keys in other tables, allowing for complex relational structures.

F. This statement is false. NOT NULL constraints are always defined at the column level, as they apply to individual columns. They cannot be specified at the table level.

References:

Oracle Documentation on Constraints: https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52271

Oracle Documentation on NOT NULL Constraints: https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52162

A: LAST_DAY does not only return the last day of the current month; it returns the last day of the month based on the date argument passed to it, which may not necessarily be the current month. Thus, statement A is incorrect.

B: CEIL requires a numeric argument and returns the smallest integer greater than or equal to that number. Thus, statement B is incorrect.

C: ADD_MONTHS function adds a specified number of calendar months to a date. This statement is correct as per the Oracle documentation.

D: ADD_MONTHS can work with a character string if the string can be implicitly converted to a DATE, according to Oracle SQL data type conversion rules. Therefore, statement D is correct.

E: LAST_DAY does not specifically return the last day of the previous month; it returns the last day of the month for any given date. Thus, statement E is incorrect.

F: CEIL returns the smallest integer greater than or equal to the specified number, not the largest integer less than or equal to it. Hence, statement F is incorrect.

G: LAST_DAY returns the last day of the month for the date argument passed to the function, which aligns with the definition in Oracle's SQL reference. Therefore, statement G is correct.

C. True. A transaction that is interrupted by a system failure is automatically rolled back by Oracle to ensure data consistency. This is part of the ACID (Atomicity, Consistency, Isolation, Durability) properties that Oracle adheres to.

D. True. A ROLLBACK without specifying a savepoint rolls back the entire transaction and removes all savepoints within that transaction. This restores the data to the state it was in at the beginning of the transaction.

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

C. True. If you have a table named 123, which is not a standard naming convention because it begins with a digit, you can reference it in SQL by using double quotation marks. The correct syntax to reference a table with a name that does not follow standard naming conventions is to enclose the name in double quotation marks.

A, B, and D are incorrect because they do not use the proper quoting mechanism for a table named with non-standard characters. In Oracle, double quotation marks are used for case-sensitive table names or those that do not follow the usual naming rules.

Multiple INSERT statements allow data insertion into one or more tables based on different conditions or datasets:

Option A: False. Multiple INSERT operations can be performed using direct values, subqueries, or even default values, not exclusively through subqueries.

Option B: True. They can indeed be performed on relational tables, which is the standard use case in most relational databases.

Option C: True. INSERT operations can be performed on updatable views, assuming the view is not complex (involving joins, GROUP BY clauses, etc.).

Option D: True. Oracle allows INSERT operations on remote tables via database links, enabling distributed database interactions.

Option E: False. Direct INSERT statements cannot be performed on external tables. External tables are typically used for read operations, with data loading handled through utilities like SQL*Loader or external data processing tools.

Option F: False. Each INSERT statement inserts data into one table. While a single SQL command block can contain multiple INSERT statements, each one is directed at a single table.

For filtering rows based on the START_DATE within the last 25 months:

C. WHERE MONTHS_BETWEEN(SYSDATE, start_date)<=25: This WHERE clause correctly uses the MONTHS_BETWEEN function to compute the number of months between the current date (SYSDATE) and the START_DATE column. It checks if the difference is 25 months or fewer, correctly applying the condition.

Incorrect options:

A: The TO_NUMBER usage is incorrect for date arithmetic in this context.

B: ADD_MONTHS used in this manner would incorrectly calculate dates in the future rather than comparing past dates.

D: The MONTHS_BETWEEN function should have SYSDATE as the first argument to calculate past months correctly.

A. A customer can exist in many countries. This is true as customers can have multiple addresses or operations in different countries, and a database design can reflect this by allowing multiple country entries for a single customer1.

C. The statement will fail because subquery may not be I contained in a values clause. In Oracle Database 12c, a subquery cannot be used within the VALUES clause of an INSERT statement. The correct approach would be to use the subquery in conjunction with the INSERT INTO … SELECT syntax if multiple rows are derived from a subquery2.

F. A product can have a different unit price at different times. It is common for products to have different unit prices at different times due to various factors such as promotions, discounts, or changes in cost price. This can be represented in a database by having a price history table or a similar mechanism to track the changes in price over time1.

Note: The other options are incorrect because:

B. The statement about the SALES table failing if a row already exists for product 23 is not necessarily true. Oracle allows for multiple rows with the same product ID if the table is designed to handle such cases, like having a composite primary key or no constraints preventing duplicates.

D. Without specific information about the SALES table’s design, we cannot verify the number of foreign keys it has.

E. The statement about the successful execution and insertion of a new row into the SALES table is too vague without the context of the actual SQL statement being referred to.

For the given SQL commands:

Option A: ROLLBACK;

A ROLLBACK will undo all changes made in the transaction up to the last COMMIT.

Option C: ROLLBACK TO SAVEPOINT post_insert;

ROLLBACK TO SAVEPOINT will undo changes back to the named savepoint, which in this case is after the first insert.