Practice Oracle 1Z0-071 Database SQL with free sample questions, timed mock exams, topic drills, and detailed answer explanations in IT Mastery.
Oracle Database SQL (1Z0-071) tests whether you can read SQL precisely, predict the real result set, and choose the statement that matches the requirement instead of the one that only looks familiar. If you are searching for 1Z0-071 sample questions, a practice test, mock exam, or simulator, this is the main IT Mastery page to start on web and continue on iOS or Android with the same account.
Start a practice session for Oracle Database SQL (1Z0-071) below, or open the full app in a new tab. For the best experience, open the full app in a new tab and navigate with swipes/gestures or the mouse wheel—just like on your phone or tablet.
Open Full App in a New TabA small set of questions is available for free preview. Subscribers can unlock full access by signing in with the same account they use on web and mobile.
Prefer to practice on your phone or tablet? Download the IT Mastery – AWS, Azure, GCP & CompTIA exam prep app for iOS or IT Mastery app on Google Play (Android) and use the same account across web and mobile.
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 | Weight |
|---|---|
| Relational Database Concepts | 4% |
| Retrieve Data by Using SQL SELECT Statements | 8% |
| Restrict and Sort Data | 7% |
| Use Single-Row Functions to Customize Output | 8% |
| Use Conversion Functions and Conditional Expressions | 7% |
| Report Aggregated Data by Using Group Functions | 5% |
| Display Data from Multiple Tables | 8% |
| Use Subqueries to Solve Queries | 8% |
| Use Set Operators | 6% |
| Manage Tables by Using DML Statements | 9% |
| Manage Indexes, Synonyms, and Sequences | 4% |
| Manage Tables and Relationships by Using DDL | 10% |
| Manage Views | 4% |
| Control User Access | 5% |
| Manage Objects with Data Dictionary Views | 3% |
| Manage Data in Different Time Zones | 4% |
These questions are pulled from the current Oracle Database SQL bank in IT Mastery and sampled across the live topic set so the preview reflects real query-reading, clause-order, and object-management work rather than a thin syntax-only slice.
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?
employee_id to DEPARTMENTS as a foreign key referencing EMPLOYEES(employee_id).department_id as the primary key in both tables.department_id to EMPLOYEES as a foreign key referencing DEPARTMENTS(department_id).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:
DEPARTMENTSdepartment_idEMPLOYEESEMPLOYEES.department_idA 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.
employee_id in DEPARTMENTS incorrectly makes the parent depend on individual employee rows.department_id as the primary key in both tables does not model separate employee identities.Related terms: entity relationship diagram, primary key, foreign key, one-to-many relationship, parent table
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?
SELECT * and specify three view column aliasese.employee_id, e.last_name, and d.department_nameDISTINCT to the SELECT * queryNATURAL JOINBest 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:
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.SELECT * column list.Related terms: projection, SELECT *, CREATE VIEW, column alias, NATURAL JOIN
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?
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.
q form fails because Oracle alternative quoting starts with q', not q".Related terms: alternative quoting mechanism, string literal, single quotes, DUAL, column alias
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?
code LIKE '%AB'code LIKE 'AB_'code LIKE 'AB%'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.
% after AB fails because % allows zero, one, or many characters, not exactly one._ before AB matches one character followed by AB, which is a different pattern.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
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?
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.
ORDER BY 2 can point to a different item after the select list is rearranged.salary is not the same rule as sorting by the displayed computed annual_pay.annual_pay expression is later changed.Related terms: ORDER BY, column alias, column position, sort expression
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?
short_name, name_len, and dept_code are character; only monthly_pay is numeric.short_name and name_len are character; monthly_pay and dept_code are numeric.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.Related terms: SUBSTR, LENGTH, ROUND, LPAD, single-row functions
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?
hire_date <= ADD_MONTHS(TRUNC(SYSDATE), -6) and NEXT_DAY(hire_date, 'FRIDAY').MONTHS_BETWEEN(TRUNC(SYSDATE), hire_date) >= 6 and NEXT_DAY(ADD_MONTHS(hire_date, 6), 'FRIDAY').TRUNC(SYSDATE) - hire_date >= 180 and ADD_MONTHS(NEXT_DAY(hire_date, 'FRIDAY'), 6).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.
NEXT_DAY(hire_date, 'FRIDAY') is tied to the hire date, not the 6-month anniversary.Related terms: MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, DATE arithmetic, TRUNC
Topic: Use Conversion Functions and Conditional Expressions
Which expression raises an error because the source value does not match the expected conversion format model?
TO_NUMBER('1250','9999')TO_DATE('18/APR/2026','YYYY-MM-DD')TO_DATE('2026-04-18','YYYY-MM-DD')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.
2026-04-18 matches YYYY-MM-DD, so that conversion succeeds.18-APR-2026 14:30 matches DD-MON-YYYY HH24:MI, so Oracle can convert it.1250 matches the number format 9999, so it does not fail.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
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?
NVL(NULLIF(override_rate, dept_rate), default_rate)NULLIF(override_rate, dept_rate)COALESCE(override_rate, dept_rate, default_rate)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.NVL(NULLIF(...), default_rate) expression skips dept_rate when override_rate is NULL.Related terms: COALESCE, NVL, NULLIF, NULL handling, conditional expressions
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?
DISTINCT department_idORDER BY department_idHAVING MAX(salary) > 0GROUP BY department_idBest 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
Topic: Display Data from Multiple Tables
Which requirement is best implemented with a non-equi join in Oracle SQL?
department_id values.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.
department_id values describes a standard equi join because the relationship uses =.Related terms: non-equi join, equi join, BETWEEN, outer join, Cartesian product
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?
AND e.department_id = d.department_id to the WHERE clause.DISTINCT to the SELECT clause.GROUP BY e.employee_id, e.last_name, d.department_name.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
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?
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.
= fails if the middle subquery returns more than one department in the Seattle location.> ANY checks numeric ordering against at least one returned department ID, not set membership.< 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
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?
AVG(salary) in the correlated subquery so it returns one value per department.ORDER BY salary so Oracle picks one salary first.DISTINCT to the subquery to remove repeated salaries.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
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?
UNION and UNION ALL cannot be mixedBest 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: 10UNION ALL SELECT 10 FROM dualUNION ALL does not remove duplicates, so the extra 10 is keptThe 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.
UNION ALL keeps the additional 10 instead of removing it.UNION already removes the duplicate between the first two queries.UNION and UNION ALL in the same compound query.Related terms: UNION, UNION ALL, set operators, DUAL, duplicate elimination
Topic: Manage Tables by Using DML Statements
Which statement correctly defines when an Oracle transaction starts and when its changes become permanent?
SAVEPOINT is issued, and its changes become permanent when the savepoint is reached.COMMIT, and its changes become permanent when ROLLBACK is issued.COMMIT.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:
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
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?
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:
SHIPPEDThen 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.s1, so it is rolled back.PAID happened before s1 and is still committed later.Related terms: SAVEPOINT, ROLLBACK TO SAVEPOINT, COMMIT, transaction control, DML
Topic: Manage Indexes, Synonyms, and Sequences
Which statement correctly describes the basic purpose of an index in Oracle Database?
INSERT statements.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.
Related terms: index, schema object, full table scan, sequence, view
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?
ALTER TABLE employees MODIFY COLUMN email VARCHAR2(50)ALTER TABLE employees MODIFY (email VARCHAR2(50))ALTER TABLE employees CHANGE email VARCHAR2(50)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:
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
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?
ALTER TABLE orders SET UNUSED (promo_code);ALTER TABLE orders DROP UNUSED COLUMNS;ALTER TABLE orders DROP COLUMN promo_code;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.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.
DROP COLUMN removes the column now instead of deferring the physical cleanup.DROP UNUSED COLUMNS works only after a column has already been marked unused.TRUNCATE TABLE deletes all table data, not a single obsolete column.Related terms: ALTER TABLE, SET UNUSED, DROP UNUSED COLUMNS, DROP COLUMN, TRUNCATE TABLE
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?
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.
Related terms: view, CREATE VIEW, OR REPLACE VIEW, synonym, object privilege
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?
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.
READ is too weak because READ does not allow SELECT ... FOR UPDATE.UPDATE is broader than required because it allows updates to all updatable columns in HR.EMPLOYEES.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
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?
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
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?
DATE and TIMESTAMPINTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTHINTERVAL YEAR TO MONTH and INTERVAL DAY TO SECONDTIMESTAMP WITH TIME ZONE and INTERVAL DAY TO SECONDBest 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.
DATE and TIMESTAMP represent actual date/time values, not elapsed durations.INTERVAL DAY TO SECOND first would assign the wrong type to 3 years 2 months.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