Most Recent Oracle 1Z0-071 Exam Questions & Answers

SQL (Structured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system.

A . False. SQL does not have graphical capabilities; it is a textual language for database interaction.

B . False. SQL supports variable definition, but it is not a core feature of the language. Variables are more commonly defined in procedural extensions to SQL, such as PL/SQL in Oracle.

C . True. SQL provides database transaction control through statements like COMMIT, ROLLBACK, and SAVEPOINT.

D . True. SQL is designed for processing sets of data, allowing for operations such as selection, projection, and joins on sets of rows.

E . False. SQL does not provide capabilities to update data in external files. It operates on data within the database.

Oracle Documentation on SQL Language: https://docs.oracle.com/database/121/SQLRF/toc.htm

After creation and initial inserts, the pcode for 'pen' is updated to 10, and then committed.

The 'pencil' row is deleted and not yet committed.

A savepoint b is set after the deletion of the 'pencil' row.

The 'fountain pen' pcode is updated to 30, followed by setting savepoint c.

The 'pen' row (now with pcode 10) is deleted.

A rollback to savepoint b reverts the deletion of 'pen' and the update to 'fountain pen', but not the deletion of 'pencil', which was committed earlier due to the scope of the savepoint.

Therefore, after the final commit:

A: The code for 'pen' is 10, since the update was committed and the subsequent delete was rolled back.

C: There is no row containing 'pencil' because its deletion was committed.

F: There is a row containing 'pen' because the deletion was rolled back to savepoint b which was set after the deletion of 'pencil'.

For the provided DELETE statement with an EXISTS clause:

Option B: The subquery is executed before the DELETE statement is executed.

Subqueries with EXISTS are typically executed before the outer DELETE statement to determine which rows of the outer query satisfy the condition.

Option D: The DELETE statement executes successfully even if the subquery selects multiple rows.

The EXISTS condition is used to check for the existence of rows returned by the subquery, regardless of how many rows there are. It returns TRUE if the subquery returns at least one row.

Options A, C, and E are incorrect because:

Option A: This statement is incorrect; the subquery is indeed a correlated subquery because it references the employee_id from the outer query (employees).

Option C: This is incorrect because not all existing rows in the EMPLOYEES table will be deleted, only those for which an associated record exists in the emp_history table.

Option E: While technically the subquery may be evaluated multiple times, it is only executed for those rows in EMPLOYEES that satisfy the condition of the EXISTS clause.

For displaying unique promotion costs in each promotion category from the PROMOTIONS table:

A . SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1: This query correctly retrieves unique combinations of promo_category and promo_cost, sorting the results based on the promo_category.

D . SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1: This query correctly combines the category and cost into a single string for each unique combination, using string concatenation and the DISTINCT keyword to ensure uniqueness, sorting by the concatenated string.

Incorrect options:

B: This does not ensure that the combinations of promo_cost and promo_category are unique because DISTINCT was not specified for both columns together.

C: The syntax is incorrect; DISTINCT cannot be applied to a single column partway through a SELECT clause.

E: This is syntactically incorrect as DISTINCT cannot be used twice in the way shown; it must apply to all columns if used at the beginning of a SELECT statement.

Regarding INTERVAL data types in Oracle Database 12c:

A . INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years. This is true. The INTERVAL YEAR TO MONTH data type stores a period of time using years and months.

E . INTERVAL DAY TO SECOND columns support fractions of seconds. This is true. The INTERVAL DAY TO SECOND data type can store days, hours, minutes, seconds, and fractional seconds.

Options B, C, D, and F are incorrect:

B is incorrect because data types between INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH are not compatible.

C is incorrect as it incorrectly limits INTERVAL YEAR TO MONTH to a single year.

D is incorrect; the YEAR field can be negative to represent a past interval.

F is incorrect as INTERVAL YEAR TO MONTH supports intervals that can span multiple years, not just annual increments.