Oracle 1Z0-071 Practice Test & Mock Exam

Use IT Mastery for interactive practice with timed mocks, topic drills, progress tracking, and detailed explanations across web and mobile. Treat the diagnostic page and public sample questions as optional one-pass style checks for how this exam handles precise SQL reading, joins, grouping, subqueries, DML, DDL, views, privileges, data dictionary views, and time-zone behavior.

24 1Z0-071 sample questions with detailed explanations

These are original IT Mastery practice questions aligned to Oracle Database SQL query-reading, clause-order, object-management, and permission-aware database decisions. Use them for study and self-assessment; the preview reflects real SQL reasoning rather than a thin syntax-only slice. They are not official Oracle questions, copied live-exam content, or exam dumps.

Question 1

Topic: Relational Database Concepts

An ERD shows a one-to-many relationship between DEPARTMENTS and EMPLOYEES: one department can have many employees, and each employee belongs to one department. DEPARTMENTS.department_id is the primary key, and EMPLOYEES.employee_id is the primary key. Which mapping correctly implements this relationship in Oracle SQL?

• A. Add employee_id to DEPARTMENTS as a foreign key referencing EMPLOYEES(employee_id).
• B. Use department_id as the primary key in both tables.
• C. Add department_id to EMPLOYEES as a foreign key referencing DEPARTMENTS(department_id).
• D. Create a third table with department_id and employee_id to store the relationship.

Best answer: C

Explanation: A one-to-many relationship is implemented by placing the parent table’s key in the child table as a foreign key. Here, DEPARTMENTS is the parent and EMPLOYEES is the many-side child, so department_id belongs in EMPLOYEES.

The core concept is foreign key placement for a one-to-many relationship. In an ERD, the table on the “one” side is the parent, and its primary key is referenced by a foreign key in the table on the “many” side. In this case, one department can be related to many employees, so each employee row must store the department it belongs to.

That means Oracle SQL should map the relationship like this:

• Parent table: DEPARTMENTS
• Parent key: department_id
• Child table: EMPLOYEES
• Foreign key column: EMPLOYEES.department_id

A separate intersection table is used for many-to-many relationships, not for a simple one-to-many design. The key takeaway is that the child table stores the foreign key that points to the parent table’s primary key.

• Reversed relationship placing employee_id in DEPARTMENTS incorrectly makes the parent depend on individual employee rows.
• Wrong key role using department_id as the primary key in both tables does not model separate employee identities.
• Unneeded bridge table a third table is appropriate for many-to-many relationships, not for one department to many employees.

Related terms: entity relationship diagram, primary key, foreign key, one-to-many relationship, parent table

Question 2

Topic: Retrieve Data by Using SQL SELECT Statements

A developer must create a view for the HR team that should expose only EMPLOYEE_ID, LAST_NAME, and DEPARTMENT_NAME.

The current statement fails with a duplicate column name error:

CREATE VIEW emp_dept_v AS
SELECT *
FROM employees e
JOIN departments d
  ON e.department_id = d.department_id;

What is the best fix?

• A. Keep SELECT * and specify three view column aliases
• B. List only e.employee_id, e.last_name, and d.department_name
• C. Add DISTINCT to the SELECT * query
• D. Change the join to NATURAL JOIN

Best answer: B

Explanation: The task requires a limited projection, so SELECT * is the wrong choice. Listing only the needed columns both matches the view requirement and avoids duplicate column names that can occur when joining tables in a view definition.

This is a projection problem, not a duplicate-row problem. When a view should expose only a few columns, the correct fix is to replace SELECT * with an explicit column list. In a join, SELECT * pulls every column from both tables, which can introduce duplicate column names into the view definition and also returns far more data than the requirement allows.

Using explicit columns helps because it:

• returns only the columns the user needs
• avoids duplicate-name issues in joined views
• keeps the view stable if base tables later gain new columns

The closest distractors try to preserve SELECT *, but the real issue is that the task calls for a limited projection.

• DISTINCT confusion removes duplicate rows, not duplicate column definitions or unnecessary columns.
• NATURAL JOIN misuse changes join behavior based on same-named columns and still does not enforce the required limited projection.
• View alias shortcut fails because three aliases cannot rename a much larger SELECT * column list.

Related terms: projection, SELECT *, CREATE VIEW, column alias, NATURAL JOIN

Question 3

Topic: Retrieve Data by Using SQL SELECT Statements

A developer needs a query that returns one row from DUAL with the column alias NOTE. The value must be exactly:

Manager's note: "Close today's file (draft)" now.

To keep the statement readable, the solution must use Oracle alternative quoting syntax. Which statement is the best choice?

• A. SELECT ‘Manager’’s note: “Close today’’s file (draft)” now.’ AS note FROM dual
• B. SELECT q"!Manager’s note: "Close today’s file (draft)" now.!" AS note FROM dual
• C. SELECT q’[Manager’s note: “Close today’s file (draft)” now.)’ AS note FROM dual
• D. SELECT q’!Manager’s note: “Close today’s file (draft)” now.!’ AS note FROM dual

Best answer: D

Explanation: Oracle alternative quoting uses the form q'<delimiter>text<matching delimiter>'. Choosing ! works well here because it does not appear in the literal, so the apostrophes in Manager's and today's do not need to be doubled.

The core concept is Oracle’s alternative quoting mechanism for string literals that already contain quote characters. Instead of escaping every embedded apostrophe with doubled single quotes, you can write the literal as q'<delimiter>text<matching delimiter>' and choose a delimiter that does not appear in the text.

In this case, ! is a clean choice because the target string contains apostrophes, double quotes, spaces, and parentheses, but no exclamation marks. That lets Oracle treat the entire text as one literal exactly as written.

A traditionally escaped string can still work, but it does not satisfy the requirement to use alternative quoting syntax. Malformed q syntax or mismatched delimiters makes the statement invalid.

• Traditional quoting can return the same text, but it fails the stated requirement to use alternative quoting syntax.
• Wrong q form fails because Oracle alternative quoting starts with q', not q".
• Mismatched delimiters fail because the opening and closing alternative quote delimiters must match correctly.

Related terms: alternative quoting mechanism, string literal, single quotes, DUAL, column alias

Question 4

Topic: Restrict and Sort Data

In Oracle SQL, which predicate matches character values that begin with AB and contain exactly one additional character after AB?

• A. code LIKE '%AB'
• B. code LIKE 'AB_'
• C. code LIKE 'AB%'
• D. code LIKE '_AB'

Best answer: B

Explanation: In Oracle SQL, the LIKE wildcard _ represents exactly one character, while % represents zero or more characters. So the pattern AB_ matches values starting with AB and having one, and only one, trailing character.

The core concept is the meaning of Oracle SQL LIKE wildcards. Use _ when you need exactly one character in a position, and use % when you need any number of characters, including none. For a value that must start with AB and then have only one more character, the correct pattern is AB_.

A few examples:

• ABC matches AB_
• AB1 matches AB_
• AB does not match AB_
• ABCD does not match AB_

The key takeaway is that _ is the single-character wildcard, whereas % is the multi-character wildcard.

• Using % after AB fails because % allows zero, one, or many characters, not exactly one.
• Placing _ before AB matches one character followed by AB, which is a different pattern.
• Ending with AB matches strings whose final characters are AB, not strings that start with AB and have one extra character.

Related terms: LIKE, wildcard, underscore wildcard, percent wildcard, predicate

Question 5

Topic: Restrict and Sort Data

A developer uses this query:

SELECT last_name,
       salary * 12 AS annual_pay
FROM employees

The result must always be sorted from highest to lowest displayed annual_pay. Developers may later reorder the select list or change the displayed expression for annual_pay while keeping the alias name. Which ORDER BY clause best applies the Oracle SQL rule?

• A. ORDER BY salary * 12 DESC
• B. ORDER BY salary DESC
• C. ORDER BY 2 DESC
• D. ORDER BY annual_pay DESC

Best answer: D

Explanation: In Oracle, ORDER BY can use a select-list alias. Using the alias annual_pay keeps the sort tied to the displayed computed column, so reordering select-list positions does not change the intended order.

The core rule is that Oracle lets ORDER BY reference a column alias, a column position, or an expression. If the requirement is to keep sorting by the displayed computed column even after maintenance changes, the alias is the safest choice here. A positional sort such as ORDER BY 2 depends on where the column appears in the select list, so reordering columns can change the sort target. Sorting by the raw expression works only as long as that exact expression stays the same.

Using ORDER BY annual_pay DESC keeps the sort aligned with the item presented to users as annual_pay. That makes the final order more stable than relying on column position or a hard-coded original expression.

• Column position fails because ORDER BY 2 can point to a different item after the select list is rearranged.
• Base column only fails because sorting by salary is not the same rule as sorting by the displayed computed annual_pay.
• Hard-coded expression is less stable because it no longer matches the displayed column if the annual_pay expression is later changed.

Related terms: ORDER BY, column alias, column position, sort expression

Question 6

Topic: Use Single-Row Functions to Customize Output

Examine this query:

SELECT SUBSTR(last_name, 1, 3) short_name,
       LENGTH(last_name)      name_len,
       ROUND(salary / 12, 2)  monthly_pay,
       LPAD(department_id, 4, '0') dept_code
FROM employees;

Which statement correctly identifies the output types of the expressions in the SELECT list?

• A. All four expressions return character data because they appear in a SELECT list.
• B. short_name, name_len, and dept_code are character; only monthly_pay is numeric.
• C. short_name and name_len are character; monthly_pay and dept_code are numeric.
• D. short_name and dept_code are character; name_len and monthly_pay are numeric.

Best answer: D

Explanation: Oracle single-row functions keep their own return types in a mixed SELECT list. Here, SUBSTR and LPAD return character values, while LENGTH and ROUND return numbers.

The key idea is that each expression in a SELECT list returns its own datatype based on the function used; Oracle does not make every selected value character data just because it is displayed together.

In this query:

• SUBSTR(last_name, 1, 3) returns part of a character string, so it is character output.
• LENGTH(last_name) returns the number of characters, so it is numeric output.
• ROUND(salary / 12, 2) returns a rounded number, so it is numeric output.
• LPAD(department_id, 4, '0') returns a padded string, so it is character output.

A common trap is assuming that functions working with character columns always return character data; LENGTH is a classic exception because it returns a number.

• LENGTH confusion fails because LENGTH returns a number, not text.
• LPAD confusion fails because padding produces a character result even when the source value is numeric.
• Display assumption fails because SELECT-list position does not change an expression’s datatype.

Related terms: SUBSTR, LENGTH, ROUND, LPAD, single-row functions

Question 7

Topic: Use Single-Row Functions to Customize Output

A report should show employees who have completed 6 months of service and the first Friday after each employee’s 6-month anniversary. hire_date values contain no time component.

The current query returns employees hired only 7 days ago, and review_date is often only one week after hire_date:

SELECT employee_id,
       NEXT_DAY(hire_date + 6, 'FRIDAY') AS review_date
FROM employees
WHERE TRUNC(SYSDATE) - hire_date >= 6;

Which change is the best fix?

• A. Use hire_date <= ADD_MONTHS(TRUNC(SYSDATE), -6) and NEXT_DAY(hire_date, 'FRIDAY').
• B. Use MONTHS_BETWEEN(TRUNC(SYSDATE), hire_date) >= 6 and NEXT_DAY(ADD_MONTHS(hire_date, 6), 'FRIDAY').
• C. Use TRUNC(SYSDATE) - hire_date >= 180 and ADD_MONTHS(NEXT_DAY(hire_date, 'FRIDAY'), 6).
• D. Use ROUND(MONTHS_BETWEEN(TRUNC(SYSDATE), hire_date)) >= 6 and NEXT_DAY(hire_date + 180, 'FRIDAY').

Best answer: B

Explanation: The problem is that adding 6 to a DATE means 6 days, not 6 months. The correct fix is to measure elapsed months with MONTHS_BETWEEN, shift the anniversary with ADD_MONTHS, and then apply NEXT_DAY to that anniversary date.

In Oracle, numeric DATE arithmetic is day-based, so both hire_date + 6 and TRUNC(SYSDATE) - hire_date >= 6 work in days, not months. For this requirement, the query must use calendar-month logic.

MONTHS_BETWEEN(TRUNC(SYSDATE), hire_date) checks whether at least 6 months of service have elapsed. ADD_MONTHS(hire_date, 6) computes the exact 6-month anniversary. NEXT_DAY(...) must then be applied to that anniversary date to return the first Friday after it.

Using 180 days is only an approximation of 6 months, and applying NEXT_DAY directly to hire_date finds a Friday after the hire date instead of after the 6-month milestone.

• 180-day shortcut fails because 180 days is not the same as 6 calendar months in Oracle date logic.
• Rounded month difference can include rows that have not yet completed 6 full months, and it still anchors the Friday to a day-count approximation.
• Correct filter, wrong anchor partially works for eligibility, but NEXT_DAY(hire_date, 'FRIDAY') is tied to the hire date, not the 6-month anniversary.
• Wrong order of functions shifts a Friday related to the hire date by 6 months, which is not the same as the first Friday after the 6-month anniversary.

Related terms: MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, DATE arithmetic, TRUNC

Question 8

Topic: Use Conversion Functions and Conditional Expressions

Which expression raises an error because the source value does not match the expected conversion format model?

• A. TO_NUMBER('1250','9999')
• B. TO_DATE('18/APR/2026','YYYY-MM-DD')
• C. TO_DATE('2026-04-18','YYYY-MM-DD')
• D. TO_TIMESTAMP('18-APR-2026 14:30','DD-MON-YYYY HH24:MI')

Best answer: B

Explanation: Oracle conversion functions succeed only when the source text matches the format model Oracle is told to use. The failing expression supplies a day, month name, and slashes, while the format model expects year first, numeric month, and hyphens.

The core concept is explicit conversion with a format model. In Oracle, functions such as TO_DATE and TO_TIMESTAMP compare the input text to the format elements you provide. If the text structure does not align with that model, the conversion fails.

Here, 18/APR/2026 is written as day, abbreviated month name, and year with / separators. The format model YYYY-MM-DD expects year first, then numeric month, then day with - separators. Because both the order and the data type of the month element differ, Oracle raises a conversion error rather than returning a date.

A close distractor is the timestamp conversion, but its text and format model match correctly.

• The date value 2026-04-18 matches YYYY-MM-DD, so that conversion succeeds.
• The timestamp value with 18-APR-2026 14:30 matches DD-MON-YYYY HH24:MI, so Oracle can convert it.
• The numeric text 1250 matches the number format 9999, so it does not fail.
• The date value with 18/APR/2026 fails because the model expects a different order, month style, and separator.

Related terms: TO_DATE, TO_TIMESTAMP, TO_NUMBER, format model, explicit conversion

Question 9

Topic: Use Conversion Functions and Conditional Expressions

A payroll query must derive effective_rate from three NUMBER columns in this order: override_rate, dept_rate, then default_rate. The expression must return the first non-NULL value found. All three columns have the same datatype. Which expression correctly applies the Oracle SQL rule?

• A. NVL(NULLIF(override_rate, dept_rate), default_rate)
• B. NULLIF(override_rate, dept_rate)
• C. COALESCE(override_rate, dept_rate, default_rate)
• D. NVL(override_rate, dept_rate)

Best answer: C

Explanation: COALESCE is the correct choice when you need the first non-NULL value from two or more expressions in a defined order. Here, it can check override_rate, then dept_rate, then default_rate in one expression.

Use COALESCE for ordered fallback logic across multiple expressions. In Oracle SQL, COALESCE returns the first expression in its argument list that is not NULL, so it fits the stated requirement exactly: use override_rate; if that is NULL, use dept_rate; if that is also NULL, use default_rate.

NVL handles only two expressions at a time, so a single NVL call cannot implement this full three-level fallback. NULLIF serves a different purpose: it compares two expressions and returns NULL when they are equal; otherwise it returns the first expression. That means NULLIF is not a general first-non-NULL selector.

The closest trap is combining NULLIF with NVL, because that still does not return dept_rate when override_rate is NULL.

• NVL(override_rate, dept_rate) is incomplete because it never uses default_rate.
• NULLIF(override_rate, dept_rate) compares two values for equality; it does not search for the first non-NULL value.
• The NVL(NULLIF(...), default_rate) expression skips dept_rate when override_rate is NULL.

Related terms: COALESCE, NVL, NULLIF, NULL handling, conditional expressions

Question 10

Topic: Report Aggregated Data by Using Group Functions

You need an Oracle SQL query that returns one row per department_id and shows the maximum salary in each department. Which construct creates the required grouped result?

• A. DISTINCT department_id
• B. ORDER BY department_id
• C. HAVING MAX(salary) > 0
• D. GROUP BY department_id

Best answer: D

Explanation: GROUP BY is the Oracle SQL clause that partitions rows into groups before aggregate functions are applied. For a result such as one row per department with MAX(salary), it is the clause that creates those department-level groups.

In Oracle SQL, GROUP BY creates grouped result sets by collecting rows that share the same value in the listed column or expression. Aggregate functions such as MAX, MIN, SUM, AVG, and COUNT then return one result per group. For the stated requirement, department_id must define the groups so the query can produce one row for each department.

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id;

HAVING can filter groups after they are formed, while ORDER BY only sorts the final result. DISTINCT removes duplicate returned rows, but it does not create aggregate groups for department-level calculations.

• HAVING confusion filters grouped rows after grouping; it does not create the department groups.
• ORDER BY confusion changes display order only and has no effect on how aggregate groups are formed.
• DISTINCT confusion removes duplicate output combinations, but it does not define groups for aggregate calculations like MAX(salary).

Related terms: GROUP BY, HAVING, aggregate functions, MAX, grouped query

Question 11

Topic: Display Data from Multiple Tables

Which requirement is best implemented with a non-equi join in Oracle SQL?

• A. Match each employee to the salary grade whose range contains the employee’s salary.
• B. Match employees to departments by equal department_id values.
• C. Return all departments, including those with no employees.
• D. Return every possible employee and department combination.

Best answer: A

Explanation: A non-equi join is used when related rows are found by a range or inequality rather than by equal values. Matching employees to salary grades based on whether salary falls within lower and upper bounds is the classic case.

A non-equi join matches rows with a condition other than =. It is appropriate when one row must be compared to a range, threshold, or inequality stored in another table. For salary grades, the employee row should join to the grade row where the salary falls between the grade’s lower and upper limits, so an equality join would not express the relationship correctly.

SELECT e.employee_id, g.grade_level
FROM employees e
JOIN job_grades g
  ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

Matching key columns such as department_id is an equi join, not a non-equi join.

• The option about equal department_id values describes a standard equi join because the relationship uses =.
• The option about including departments with no employees describes an outer join requirement.
• The option about every possible employee and department combination describes a Cartesian product, usually caused by no join condition.

Related terms: non-equi join, equi join, BETWEEN, outer join, Cartesian product

Question 12

Topic: Display Data from Multiple Tables

An analyst expects one row per employee with that employee’s department name, but this query returns 540 rows:

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.salary > 5000;

EMPLOYEES has 20 rows with salary > 5000, and DEPARTMENTS has 27 rows. Which change correctly prevents the unexpected result-set expansion?

• A. Add AND e.department_id = d.department_id to the WHERE clause.
• B. Add DISTINCT to the SELECT clause.
• C. Add GROUP BY e.employee_id, e.last_name, d.department_name.
• D. Replace departments d with departments d CROSS JOIN.

Best answer: A

Explanation: The query lists two tables but never links them, so Oracle produces a Cartesian product. Adding the join condition between employees and departments prevents each employee from being paired with every department row.

A Cartesian product happens when a query includes multiple tables without a join condition relating them. In the shown comma-join syntax, Oracle expects the table relationship to be added in the WHERE clause. Because only e.salary > 5000 appears there, the 20 qualifying employee rows are combined with all 27 department rows, producing 20 × 27 = 540 rows.

To fix the query, add the predicate that matches each employee to the correct department, such as e.department_id = d.department_id. The same rule applies in ANSI syntax, where the relationship would be placed in an ON clause. DISTINCT or GROUP BY may change presentation, but they do not repair missing join semantics.

• DISTINCT is not a join: it removes duplicate output rows only after the Cartesian product has already been formed.
• CROSS JOIN is explicit Cartesian product: it states the unwanted behavior rather than preventing it.
• GROUP BY does not relate tables: it aggregates rows after selection and still leaves the missing table relationship unresolved.

Related terms: Cartesian product, join condition, cross join, WHERE clause, ANSI join

Question 13

Topic: Use Subqueries to Solve Queries

Examine this query:

SELECT last_name
FROM employees
WHERE department_id operator1 (
  SELECT department_id
  FROM departments
  WHERE location_id = (
    SELECT location_id
    FROM locations
    WHERE city = 'Seattle'
  )
);

Assume exactly one row in LOCATIONS has CITY = 'Seattle'. Which comparison should replace operator1 so the query returns employees who work in departments at that city?

• A. IN
• B. > ANY
• C. =
• D. < ALL

Best answer: A

Explanation: IN is required because the innermost subquery returns one location_id, but the middle subquery can return multiple department_id values for that location. The outer query must therefore test whether each employee’s department belongs to that returned set.

Nested subqueries are evaluated from the inside out. Here, the innermost subquery returns a single location_id for Seattle, so the middle subquery correctly uses = with location_id. The middle subquery can then return one or many department_id values for that location. Because the outer query compares employees.department_id to a list of possible department IDs, it needs a multi-row comparison operator that tests membership, which is IN.

Using = would only be valid if the middle subquery returned exactly one row. Operators such as > ANY or < ALL perform ordered comparisons against returned values, not membership testing. The key takeaway is to choose the comparison operator based on whether the subquery returns a single value or multiple rows.

• Single-row assumption using = fails if the middle subquery returns more than one department in the Seattle location.
• Ordered comparison using > ANY checks numeric ordering against at least one returned department ID, not set membership.
• Wrong logic type using < ALL also compares values by order and does not test whether a department ID appears in the returned list.

Related terms: nested subquery, single-row subquery, multi-row subquery, IN operator, ANY and ALL

Question 14

Topic: Use Subqueries to Solve Queries

The employees table has one row per employee and includes department_id, salary, and dept_avg_sal. A developer wants to store each employee’s department average salary in dept_avg_sal and runs:

UPDATE employees e
SET dept_avg_sal = (
  SELECT salary
  FROM employees
  WHERE department_id = e.department_id
);

Oracle returns ORA-01427: single-row subquery returns more than one row.

What is the best fix?

• A. Use AVG(salary) in the correlated subquery so it returns one value per department.
• B. Add ORDER BY salary so Oracle picks one salary first.
• C. Add DISTINCT to the subquery to remove repeated salaries.
• D. Change the assignment to use IN instead of =.

Best answer: A

Explanation: The SET clause expects a scalar subquery result for each row being updated. Because one department usually has many employee salaries, the subquery must be correlated and reduced to a single value, such as the department AVG(salary).

In an UPDATE, the expression on the right side of SET column = (...) must produce one value for each target row. Here, the subquery is correlated correctly by department_id, but it still returns multiple salary rows for departments with more than one employee, causing ORA-01427.

The fix is to keep the correlation and make the subquery scalar by aggregating the related rows, for example:

UPDATE employees e
SET dept_avg_sal = (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

That returns one average salary per outer row’s department. The key takeaway is that correlated subqueries in UPDATE statements must return exactly one derived value for each row being updated.

• DISTINCT is insufficient because multiple different salary values in the same department still produce more than one row.
• ORDER BY does not fix cardinality because sorting rows does not reduce them to a single result for the assignment.
• IN is the wrong operator because SET assigns one value to one column; it does not accept a multi-row list there.

Related terms: correlated subquery, scalar subquery, UPDATE statement, AVG, ORA-01427

Question 15

Topic: Use Set Operators

Examine the compound query:

SELECT 10 AS n FROM dual
UNION
SELECT 10 AS n FROM dual
UNION ALL
SELECT 10 AS n FROM dual;

What does Oracle return?

• A. An error because UNION and UNION ALL cannot be mixed
• B. Three rows, all containing 10
• C. Two rows, both containing 10
• D. One row containing 10

Best answer: C

Explanation: UNION removes duplicate rows, while UNION ALL keeps them. In this query, the first two SELECT statements collapse to one row, and the last SELECT adds another identical row, so two rows remain.

The core concept is that UNION eliminates duplicate rows from the combined result, while UNION ALL preserves all rows. In this compound query, Oracle evaluates the set operators from left to right.

• SELECT 10 FROM dual UNION SELECT 10 FROM dual produces one row: 10
• That intermediate result is then combined with UNION ALL SELECT 10 FROM dual
• UNION ALL does not remove duplicates, so the extra 10 is kept

The final result therefore contains two rows, both with the value 10. The closest wrong interpretation is assuming that all three input rows survive, which would ignore the duplicate-removal effect of the first UNION.

• One row misses that the final UNION ALL keeps the additional 10 instead of removing it.
• Three rows ignores that the first UNION already removes the duplicate between the first two queries.
• Error when mixed is incorrect because Oracle allows UNION and UNION ALL in the same compound query.

Related terms: UNION, UNION ALL, set operators, DUAL, duplicate elimination

Question 16

Topic: Manage Tables by Using DML Statements

Which statement correctly defines when an Oracle transaction starts and when its changes become permanent?

• A. It starts when SAVEPOINT is issued, and its changes become permanent when the savepoint is reached.
• B. It starts after COMMIT, and its changes become permanent when ROLLBACK is issued.
• C. It starts with the first DML statement after a transaction ends, and its changes become permanent only after COMMIT.
• D. It starts with the first SELECT statement, and its changes become permanent when that statement finishes.

Best answer: C

Explanation: In Oracle SQL, a transaction begins when the session issues its first DML statement after the previous transaction ended. The changes remain pending until COMMIT; only then do they become permanent.

The core concept is Oracle transaction control. A transaction starts implicitly with the first executable DML statement such as INSERT, UPDATE, DELETE, or MERGE after a COMMIT or ROLLBACK. Those changes are not permanent immediately; they stay part of the current transaction until COMMIT makes them permanent or ROLLBACK undoes them.

This means:

• DML starts the transaction implicitly.
• COMMIT ends the transaction and saves the changes.
• ROLLBACK ends the transaction and discards uncommitted changes.
• SAVEPOINT only marks a point inside the transaction.

A common confusion is treating SELECT or SAVEPOINT as the start of a transaction, but neither is what makes data changes permanent.

• SELECT confusion fails because queries do not define the point at which DML changes become permanent.
• SAVEPOINT confusion fails because a savepoint is only an internal marker within an existing transaction.
• ROLLBACK confusion fails because ROLLBACK reverses uncommitted changes rather than preserving them.

Related terms: COMMIT, ROLLBACK, SAVEPOINT, DML, transaction control

Question 17

Topic: Manage Tables by Using DML Statements

A session starts with table orders containing:

order_id  status
1         NEW
2         NEW
3         NEW

The session then executes:

UPDATE orders SET status = 'PAID' WHERE order_id = 1;
SAVEPOINT s1;
DELETE FROM orders WHERE order_id = 2;
UPDATE orders SET status = 'SHIPPED' WHERE order_id = 1;
SAVEPOINT s2;
INSERT INTO orders VALUES (4, 'NEW');
ROLLBACK TO s1;
UPDATE orders SET status = 'CLOSED' WHERE order_id = 3;
COMMIT;

Which data state is finally persisted in orders?

• A. 1 PAID, 3 CLOSED, 4 NEW
• B. 1 NEW, 2 NEW, 3 NEW
• C. 1 SHIPPED, 2 NEW, 3 CLOSED
• D. 1 PAID, 2 NEW, 3 CLOSED

Best answer: D

Explanation: ROLLBACK TO SAVEPOINT reverses only the work done after that savepoint. Here, the delete of order 2, the change of order 1 to SHIPPED, and the insert of order 4 are undone, while the earlier change of order 1 to PAID remains. After that, order 3 is updated to CLOSED and the COMMIT makes those remaining changes permanent.

The core rule is that ROLLBACK TO savepoint_name does not cancel the entire transaction; it removes only the uncommitted changes made after that savepoint was created. In this sequence, the update of order 1 to PAID happens before s1, so it survives the rollback.

After s1, these actions occur and are later undone:

• delete order 2
• update order 1 to SHIPPED
• insert order 4

Then the session updates order 3 to CLOSED and issues COMMIT. So the final committed rows are order 1 as PAID, order 2 still present as NEW, and order 3 as CLOSED.

The key takeaway is that a savepoint rollback is partial, not a full transaction rollback.

• SHIPPED remains fails because that update occurred after s1, so ROLLBACK TO s1 undoes it.
• Order 4 stays inserted fails because the insert also occurred after s1, so it is rolled back.
• Everything returns to original fails because the earlier update to PAID happened before s1 and is still committed later.

Related terms: SAVEPOINT, ROLLBACK TO SAVEPOINT, COMMIT, transaction control, DML

Question 18

Topic: Manage Indexes, Synonyms, and Sequences

Which statement correctly describes the basic purpose of an index in Oracle Database?

• A. It stores a query definition as a virtual table.
• B. It is a schema object that can speed row retrieval and is stored separately from table data.
• C. It generates numeric values for new rows during INSERT statements.
• D. It stores the actual table rows in sorted order for every query.

Best answer: B

Explanation: An index is created to improve data access, especially when Oracle needs to find rows by indexed column values. It is managed as a separate schema object and does not contain the table’s full row data.

The core purpose of an index is to provide Oracle with a faster path to locate rows in a table. An index is a schema object separate from the table itself, built on one or more columns, so Oracle can search indexed values more efficiently than scanning every row. The table still stores the actual row data; the index stores lookup information that helps Oracle find those rows. This is why an index can be created, dropped, or rebuilt independently of the table data. The closest confusion is thinking an index stores the table rows themselves, but that describes table storage, not the basic role of an index.

• Sorted row storage confuses an index with the table itself; the table holds the actual row data.
• Generated numbers describes a sequence, which supplies numeric values, often for key columns.
• Virtual table describes a view, which stores a query definition rather than row-locating structures.

Related terms: index, schema object, full table scan, sequence, view

Question 19

Topic: Manage Tables and Relationships by Using DDL

The employees table already has an email column defined as VARCHAR2(30). You need to increase its length to VARCHAR2(50) by using Oracle SQL. Which statement is correct?

• A. ALTER TABLE employees MODIFY COLUMN email VARCHAR2(50)
• B. ALTER TABLE employees MODIFY (email VARCHAR2(50))
• C. ALTER TABLE employees CHANGE email VARCHAR2(50)
• D. ALTER TABLE employees ALTER COLUMN email VARCHAR2(50)

Best answer: B

Explanation: In Oracle, you change an existing column definition with ALTER TABLE ... MODIFY (...). To increase the size of email, you restate the column name and new datatype definition inside the MODIFY clause.

The core Oracle DDL concept is that existing column definitions are changed with ALTER TABLE and the MODIFY clause. When you want to increase a VARCHAR2 column length, Oracle expects the column name and its new definition inside parentheses, such as MODIFY (email VARCHAR2(50)).

This is Oracle-specific syntax for changing column attributes like:

• datatype or length
• default value
• nullability, when allowed

Common confusions come from other database products that use forms like ALTER COLUMN, CHANGE, or MODIFY COLUMN. Those are not the standard Oracle syntax here. The key takeaway is that Oracle column changes use MODIFY (...), not other vendor-specific clause forms.

• MODIFY COLUMN confusion uses wording seen in other SQL dialects, but Oracle does not use COLUMN in this form.
• ALTER COLUMN confusion is not the Oracle clause for changing a column definition.
• CHANGE confusion is MySQL-style syntax and is not valid for Oracle ALTER TABLE column modification.

Related terms: ALTER TABLE, MODIFY clause, VARCHAR2, DDL

Question 20

Topic: Manage Tables and Relationships by Using DDL

A table ORDERS has an obsolete column PROMO_CODE. The application must stop referencing that column immediately, but the DBA wants to postpone the physical column removal and space reclamation until a later maintenance window. Which statement best meets this requirement?

• A. ALTER TABLE orders SET UNUSED (promo_code);
• B. ALTER TABLE orders DROP UNUSED COLUMNS;
• C. ALTER TABLE orders DROP COLUMN promo_code;
• D. TRUNCATE TABLE orders;

Best answer: A

Explanation: Oracle uses ALTER TABLE ... SET UNUSED when a column should disappear from use immediately but its physical removal can wait. The column becomes unavailable to SQL right away, and the actual storage cleanup can be done later with DROP UNUSED COLUMNS.

The key Oracle SQL rule is that SET UNUSED is a two-step approach to column removal. It marks the column as unused so applications can no longer reference it, while delaying the more expensive physical cleanup.

• SET UNUSED hides the column immediately.
• The column data remains in the table segment for now.
• Later, ALTER TABLE ... DROP UNUSED COLUMNS permanently removes those unused columns.

By contrast, DROP COLUMN performs the actual column drop now, DROP UNUSED COLUMNS only affects columns already marked unused, and TRUNCATE TABLE removes rows rather than a column. The best match is the statement that separates logical removal from physical cleanup.

• Immediate full drop fails because DROP COLUMN removes the column now instead of deferring the physical cleanup.
• Later cleanup only fails because DROP UNUSED COLUMNS works only after a column has already been marked unused.
• Remove rows instead fails because TRUNCATE TABLE deletes all table data, not a single obsolete column.

Related terms: ALTER TABLE, SET UNUSED, DROP UNUSED COLUMNS, DROP COLUMN, TRUNCATE TABLE

Question 21

Topic: Manage Views

A team repeatedly runs the same SELECT against EMPLOYEES and wants users to see only employee ID, name, and department data, not salary. Which Oracle database object is best suited for this requirement?

• A. A view
• B. An index
• C. A synonym
• D. A sequence

Best answer: A

Explanation: A view is the correct choice because it encapsulates a query and can expose only the columns you want users to access. This makes repeated query use simpler while also restricting visible data such as salary columns.

In Oracle SQL, a view is a stored query that behaves like a virtual table. It is commonly used when the same query is needed repeatedly or when users should see only a subset of columns or rows from a base table. In this case, the requirement is both to simplify reuse of the same SELECT and to hide salary data, which matches the purpose of a view.

A synonym only provides an alternate name for an object, a sequence generates numeric values, and an index improves query access speed. None of those objects is designed to present a filtered column list from a query.

The key idea is that a view is for query abstraction and controlled data presentation.

• Synonym confusion A synonym is just an alternate name for a table or other object; it does not hide columns or store query logic.
• Sequence confusion A sequence generates number values, typically for keys, and has nothing to do with projecting selected columns.
• Index confusion An index can improve retrieval performance, but it does not define which columns users are allowed to see.

Related terms: view, CREATE VIEW, OR REPLACE VIEW, synonym, object privilege

Question 22

Topic: Control User Access

User APPUSR must run the following statement and then update only SALARY and COMMISSION_PCT in HR.EMPLOYEES. No privilege on other tables or other columns should be granted.

SELECT salary, commission_pct
FROM hr.employees
WHERE employee_id = 205
FOR UPDATE;

Which statement grants the least broad privilege that still meets the requirement?

• A. GRANT SELECT ANY TABLE, UPDATE ANY TABLE TO appusr;
• B. GRANT READ, UPDATE (salary, commission_pct) ON hr.employees TO appusr;
• C. GRANT SELECT, UPDATE (salary, commission_pct) ON hr.employees TO appusr;
• D. GRANT SELECT, UPDATE ON hr.employees TO appusr;

Best answer: C

Explanation: The least broad solution is an object grant on HR.EMPLOYEES with SELECT plus column-level UPDATE for SALARY and COMMISSION_PCT. READ is not enough for SELECT ... FOR UPDATE, and broader table-wide or ANY TABLE privileges exceed the requirement.

This question tests choosing the minimum required Oracle privilege. Because the statement uses SELECT ... FOR UPDATE, the user needs SELECT on HR.EMPLOYEES; READ does not permit FOR UPDATE. The update requirement is narrower still: only SALARY and COMMISSION_PCT must be modifiable, so Oracle should grant UPDATE at the column level rather than for the whole table.

Using an object privilege on one table is less broad than using system privileges, and using a column list for UPDATE is less broad than allowing updates to every column in the table. That combination meets the exact requirement without expanding access beyond what the user needs.

When least privilege is the goal, prefer object privileges and column-specific grants over table-wide or ANY TABLE privileges.

• The option using READ is too weak because READ does not allow SELECT ... FOR UPDATE.
• The option granting table-wide UPDATE is broader than required because it allows updates to all updatable columns in HR.EMPLOYEES.
• The option using SELECT ANY TABLE and UPDATE ANY TABLE is far too broad because those are system privileges affecting many tables, not just one object.

Related terms: object privilege, system privilege, column-level UPDATE, SELECT FOR UPDATE

Question 23

Topic: Manage Objects with Data Dictionary Views

Which data dictionary view should you query to find both the owner and the object type for database objects that are accessible to your account, without requiring DBA privileges?

• A. DBA_OBJECTS
• B. USER_OBJECTS
• C. ALL_TAB_COLUMNS
• D. ALL_OBJECTS

Best answer: D

Explanation: ALL_OBJECTS is the correct view because it shows objects the current user can access and includes both ownership and object-type metadata. It is the standard choice when you need OWNER and OBJECT_TYPE without relying on DBA-only views.

In Oracle, object-lookup data dictionary views differ mainly by scope. USER_ views show only objects owned by the current user, so owner information is not needed there and USER_OBJECTS does not help identify different owners. DBA_ views show all objects in the database, but they typically require elevated privileges. ALL_OBJECTS sits between those two: it shows objects accessible to the current user and includes metadata such as OWNER, OBJECT_NAME, and OBJECT_TYPE.

So when the requirement is to determine who owns an accessible object and what kind of object it is, without DBA privileges, ALL_OBJECTS is the appropriate view. The closest distractor is USER_OBJECTS, but it is limited to the current user’s own objects.

• USER_OBJECTS scope is too narrow because it covers only objects owned by the current user and is not meant for identifying other owners.
• DBA_OBJECTS privilege is the wrong fit because it can show all database objects, but the stem excludes requiring DBA privileges.
• ALL_TAB_COLUMNS purpose is different because it describes table and view columns, not general object ownership and object type.

Related terms: ALL_OBJECTS, USER_OBJECTS, DBA_OBJECTS, OBJECT_TYPE, OWNER

Question 24

Topic: Manage Data in Different Time Zones

A design requires one column to store durations such as 3 years 2 months and another column to store durations such as 4 days 05:30:10. Which Oracle data type pair is appropriate?

• A. DATE and TIMESTAMP
• B. INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH
• C. INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND
• D. TIMESTAMP WITH TIME ZONE and INTERVAL DAY TO SECOND

Best answer: C

Explanation: Oracle uses two interval families for elapsed time. INTERVAL YEAR TO MONTH is for durations measured in years and months, while INTERVAL DAY TO SECOND is for durations measured in days, hours, minutes, and seconds.

The core concept is that Oracle separates interval values into two distinct duration types. Use INTERVAL YEAR TO MONTH when the value represents calendar-style elapsed time in years and months, such as 3 years 2 months. Use INTERVAL DAY TO SECOND when the value represents day-and-time elapsed time, such as 4 days 05:30:10.

DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE store points in time, not durations. Also, the two interval types are not interchangeable: the year-month type does not store days or seconds, and the day-second type does not store years or months.

The key takeaway is to match the duration units to the correct interval family.

• Datetime confusion: DATE and TIMESTAMP represent actual date/time values, not elapsed durations.
• Reversed pair: putting INTERVAL DAY TO SECOND first would assign the wrong type to 3 years 2 months.
• Time zone confusion: TIMESTAMP WITH TIME ZONE stores a time point with zone information, not a year-month duration.

Related terms: CURRENT_TIMESTAMP, TIMESTAMP WITH TIME ZONE, interval literal, NUMTOYMINTERVAL, NUMTODSINTERVAL

Public diagnostic page: a static diagnostic page is available for a one-pass self-check. Use IT Mastery for interactive practice with mixed sets, timed mocks, topic drills, explanations, and progress tracking.

What this 1Z0-071 practice page gives you

• a direct web entry for Oracle Database SQL practice in IT Mastery
• 24 original IT Mastery practice questions aligned to the 1Z0-071 topic set
• targeted drills across joins, grouping, subqueries, DML, DDL, views, privileges, data dictionary views, and time-zone-aware SQL behavior
• detailed explanations that show why the strongest SQL answer is correct and why the tempting alternatives fail
• a clear web preview path for checking style before deeper practice
• the same IT Mastery account across web and mobile

1Z0-071 exam snapshot

• Vendor: Oracle
• Official exam name: Oracle Database SQL
• Exam code: 1Z0-071
• Items: 78 total
• Exam time: 120 minutes
• Question style: multiple-choice SQL reasoning and result-interpretation scenarios
• Assessment style: query correctness, clause behavior, object management, and permission-aware database judgment

1Z0-071 questions usually reward the answer that preserves the intended row shape, filter logic, and object behavior under real Oracle SQL rules instead of the answer that only resembles the right syntax.

Topic coverage for 1Z0-071 practice

How to use the 1Z0-071 simulator efficiently

1. Start with joins, filtering, grouping, and subqueries because those are the areas most likely to create plausible-looking wrong answers.
2. Review every miss until you can explain row shape, null behavior, grouping granularity, or object-permission logic without guessing.
3. Move into mixed sets once you can switch between query writing, object management, and data-dictionary reasoning without losing the requirement.
4. Finish with timed runs so the 78-question pace feels stable before exam day.

1Z0-071 SQL decision filters

Use these filters when two SQL answers look syntactically possible:

• Row shape: identify whether the query should return detail rows, grouped rows, joined rows, set-operator rows, or one row per subquery condition.
• Clause order: check WHERE, GROUP BY, HAVING, ORDER BY, joins, and set operators in terms of logical effect, not just syntax.
• Null behavior: watch for null comparisons, outer joins, aggregate behavior, conversion functions, and conditional expressions.
• Object boundary: separate DML, DDL, views, indexes, sequences, synonyms, privileges, and data dictionary queries.
• Time and conversion rules: confirm explicit conversions, date arithmetic, time zones, and format models before trusting a result.

Final 7-day 1Z0-071 practice sequence

When Oracle SQL practice is enough

If several unseen mixed attempts are above roughly 75% and you can explain the row-shape, clause, object, or permission rule behind each answer, you are likely ready. More practice should improve SQL reasoning, not repeated-query recognition.

Free study resources

Use this IT Mastery page for live practice, topic drills, timed mocks, explanations, and app access.

Web preview and premium practice

• Web/public preview: a smaller web set so you can validate the question style and explanation depth.
• Premium: interactive web-app practice with focused drills, mixed sets, timed mock exams, detailed explanations, and progress tracking across web and mobile.

Free samples and interactive practice

• Live now: the 1Z0-071 bank is available in IT Mastery on web, iOS, and Android.
• On-page sample set: this page includes 24 public sample questions from the current Oracle SQL practice coverage.
• Preview coverage: the public preview is drawn from current live practice coverage, not a guide-only placeholder.

Related Oracle practice banks

• Need the wider Oracle family view? Oracle exam pages
• Need Java instead of database SQL? 1Z0-830
• Want the install or pricing path first? IT Plans & Access or IT web app

1Z0-071 Oracle SQL reasoning map

Use this map after the sample questions to connect individual items to the Oracle SQL query, join, grouping, subquery, DML, and object decisions these practice samples test.

    flowchart LR
	  S1["SQL requirement or query result question"] --> S2
	  S2["Identify tables joins filters and grouping"] --> S3
	  S3["Apply SQL execution and null behavior rules"] --> S4
	  S4["Check functions subqueries or set operations"] --> S5
	  S5["Evaluate DML transaction or object effect"] --> S6
	  S6["Choose exact result or valid statement"]

Mini Glossary

• Aggregate function: Function such as COUNT, SUM, AVG, MIN, or MAX across rows.
• Correlated subquery: Subquery that references a column from the outer query.
• Outer join: Join preserving unmatched rows from one side.
• Set operator: Operator such as UNION, INTERSECT, or MINUS combining query results.
• Transaction: Unit of database work committed or rolled back together.

In this section

• Free Oracle 1Z0-071 Practice Questions: Relational Database Concepts
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on Relational Database Concepts, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Questions: SQL SELECT
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on SQL SELECT, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Questions: Restrict and Sort Data
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on Restrict and Sort Data, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Questions: Single-Row Functions
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on Single-Row Functions, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Questions: Conversion and CASE
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on Conversion and CASE, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Questions: Group Functions
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on Group Functions, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Questions: Display Data from Multiple Tables
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on Display Data from Multiple Tables, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Questions: Use Subqueries to Solve Queries
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on Use Subqueries to Solve Queries, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Questions: Use Set Operators
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on Use Set Operators, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Questions: Manage Tables by Using DML Statements
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on Manage Tables by Using DML Statements, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Questions: Indexes and Sequences
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on Indexes and Sequences, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Questions: DDL Tables and Relationships
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on DDL Tables and Relationships, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Questions: Manage Views
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on Manage Views, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Questions: Control User Access
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on Control User Access, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Questions: Data Dictionary Views
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on Data Dictionary Views, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Questions: Manage Data in Different Time Zones
  Practice 10 free Oracle Database SQL (Oracle 1Z0-071) questions on Manage Data in Different Time Zones, with answers, explanations, and the IT Mastery next step.
• Free Oracle 1Z0-071 Practice Exam: Oracle Database SQL
  Try 78 free Oracle Database SQL (Oracle 1Z0-071) questions across the exam domains, with explanations, then continue with IT Mastery practice.