Try 78 free Oracle 1Z0-071 questions across the exam domains, with explanations, then continue with full IT Mastery practice.
This free full-length Oracle 1Z0-071 practice exam includes 78 original IT Mastery questions across the exam domains.
These questions are for self-assessment. They are not official exam questions and do not imply affiliation with the exam sponsor.
Count note: this page uses the full-length practice count maintained in the Mastery exam catalog. Some certification vendors publish total questions, scored questions, duration, or unscored/pretest-item rules differently; always confirm exam-day rules with the sponsor.
Need concept review first? Read the Oracle 1Z0-071 Cheat Sheet on Tech Exam Lexicon, then return here for timed mocks and full IT Mastery practice.
Open the matching IT Mastery practice page for timed mocks, topic drills, progress tracking, explanations, and full practice.
Try Oracle 1Z0-071 on Web View full Oracle 1Z0-071 practice page
| Domain | 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% |
Use this as one diagnostic run. IT Mastery gives you timed mocks, topic drills, analytics, code-reading practice where relevant, and full practice.
Topic: Retrieve Data by Using SQL SELECT Statements
In Oracle SQL, what is the result of an arithmetic expression such as salary + bonus when bonus is NULL for a row?
Options:
A. NULL
B. 0
C. The value of salary
D. An error occurs
Best answer: A
Explanation: In Oracle SQL, NULL means an unknown or missing value. When any operand in a normal arithmetic expression is NULL, the expression result is also NULL because Oracle cannot determine the numeric outcome.
The core concept is Oracle’s NULL propagation in arithmetic expressions. NULL does not mean zero, and it does not mean an empty numeric value; it means the value is missing or unknown. Because of that, an expression like salary + bonus cannot be fully evaluated when bonus is NULL, so Oracle returns NULL for that row.
If you want a missing value to behave like zero, you must explicitly convert it, for example with NVL(bonus, 0).
This is why arithmetic with a missing operand returns NULL, not the other operand and not an error.
NULL is not treated as 0 unless a function such as NVL is used.NULL operand in arithmetic normally produces NULL, not an exception.Topic: Use Single-Row Functions to Customize Output
Which statement about ROUND, TRUNC, and MOD is correct in Oracle SQL?
Options:
A. ROUND(45.927) returns 45, and MOD(11, 4) returns 4.
B. TRUNC(45.927, 2) returns 45.93, and ROUND(45.927, 1) returns 45.92.
C. TRUNC(45.927, 2) returns 45.92, and MOD(11, 4) returns 3.
D. ROUND(45.927, 2) returns 45.92, and MOD(11, 4) returns 2.
Best answer: C
Explanation: TRUNC(45.927, 2) keeps two decimal places without rounding, so the result is 45.92. MOD(11, 4) returns the remainder from dividing 11 by 4, which is 3.
In Oracle SQL, ROUND and TRUNC both accept a number and an optional precision value, but they behave differently. ROUND changes the value based on the next digit, while TRUNC simply cuts off digits beyond the specified position. So TRUNC(45.927, 2) becomes 45.92, but ROUND(45.927, 2) would become 45.93.
MOD(m, n) returns the remainder when m is divided by n. For 11 / 4, the quotient is 2 with a remainder of 3, so MOD(11, 4) is 3.
The key takeaway is that TRUNC never rounds, ROUND may change the kept digit, and MOD returns a remainder, not a quotient.
ROUND(45.927, 2) gives 45.92 fails because the third decimal digit causes the second decimal digit to round up.TRUNC(45.927, 2) gives 45.93 is wrong because truncation does not increase the retained digits.ROUND(45.927) gives 45 fails because omitting the precision rounds to 0 decimal places, producing 46.Topic: Manage Tables by Using DML Statements
new_orders contains order_id and customer_id. You must copy every row from new_orders into both order_history and order_audit in a single SQL statement. No row-routing conditions are required. Which statement is the best solution?
Options:
- B. ```sql
INSERT FIRST
WHEN 1 = 1 THEN
INTO order_history (order_id, customer_id)
VALUES (order_id, customer_id)
WHEN 1 = 1 THEN
INTO order_audit (order_id, customer_id)
VALUES (order_id, customer_id)
SELECT order_id, customer_id
FROM new_orders;
- D. ```sql
INSERT ALL
WHEN 1 = 1 THEN
INTO order_history (order_id, customer_id)
VALUES (order_id, customer_id)
ELSE
INTO order_audit (order_id, customer_id)
VALUES (order_id, customer_id)
SELECT order_id, customer_id
FROM new_orders;
Best answer: A
Explanation: An unconditional multi-table INSERT ALL is used when each source row must be inserted into every listed target table. Because no branching logic is needed here, the statement with only INTO clauses is the correct choice.
Oracle supports two multi-table insert styles. An unconditional multi-table insert uses INSERT ALL followed by one or more INTO clauses and a single source query; Oracle applies every INTO clause to each row returned by the subquery. A conditional multi-table insert uses WHEN and optional ELSE logic to route rows, and INSERT FIRST stops at the first true condition.
Here, the requirement is simple duplication: every row from new_orders must go to both order_history and order_audit. That is exactly what unconditional INSERT ALL does in one statement. The version using INSERT FIRST is the closest trap, but it inserts each row into only the first matching branch.
INSERT FIRST trap fails because Oracle inserts into only the first true WHEN branch for each source row.WHEN ... ELSE sends a row down one branch, not automatically to both target tables.MERGE misuse targets one table for synchronization logic and does not satisfy inserting every row into both tables.Topic: Manage Tables and Relationships by Using DDL
Which constraint definition must be created at the table level in Oracle SQL?
Options:
A. salary NUMBER CONSTRAINT emp_sal_ck CHECK (salary > 0)
B. dept_id NUMBER CONSTRAINT emp_dept_fk REFERENCES departments(dept_id)
C. CONSTRAINT ord_item_pk PRIMARY KEY (order_id, product_id)
D. email CONSTRAINT emp_email_uk UNIQUE
Best answer: C
Explanation: A constraint that involves more than one column must be defined at the table level. The composite primary key on order_id and product_id fits that rule, while the other examples are single-column constraints that can be written at the column level.
In Oracle SQL, column-level constraints are attached directly to one column definition and are appropriate when the rule applies to that single column. Table-level constraints are used when the rule involves multiple columns or when you want to declare the constraint separately from any one column.
A composite primary key uses more than one column to identify each row uniquely, so it must be defined at the table level, as in PRIMARY KEY (order_id, product_id). By contrast, a single-column foreign key, a single-column CHECK, and a single-column UNIQUE constraint can all be written at the column level.
A useful memory aid is: if the constraint references multiple columns, use table-level syntax.
Topic: Manage Indexes, Synonyms, and Sequences
A team wants users to reference HR.EMPLOYEES by the shorter name EMP in SQL statements. The requirement is only to provide an alternate name for the existing table, with no change to data access path or table structure. Which object should be created?
Options:
A. A view named EMP on HR.EMPLOYEES
B. A synonym for HR.EMPLOYEES
C. A sequence named EMP
D. An index on HR.EMPLOYEES
Best answer: B
Explanation: A synonym is the Oracle object used to give another name to an existing table, view, sequence, or other schema object. Because the requirement is only an alternate object name, a synonym fits exactly without changing performance or table design.
The core concept is object purpose. In Oracle, an index is used to improve row access performance, while a synonym is used to provide an alternate name for an existing schema object. Here, the team only wants users to refer to HR.EMPLOYEES as EMP, so the correct object is a synonym.
A synonym does not store data and does not change the table definition. It simply lets SQL reference the target object by another name. By contrast, an index affects access paths, a sequence generates numeric values, and a view is a stored query rather than just an alias.
When the requirement is naming convenience or hiding schema-qualified object names, think synonym.
Topic: Retrieve Data by Using SQL SELECT Statements
The employees table has FIRST_NAME, LAST_NAME, and JOB_ID. Which query correctly returns one displayed column in the format LastName, FirstName (JobId) for each row?
Options:
A. SELECT ’last_name, first_name (job_id)’ AS emp_info FROM employees
B. SELECT CONCAT(last_name, ‘, ‘, first_name, ’ (’, job_id, ‘)’) AS emp_info FROM employees
C. SELECT last_name || ‘, ’ || first_name || ’ (’ || job_id || ‘)’ AS emp_info FROM employees
D. SELECT last_name + ‘, ’ + first_name + ’ (’ + job_id + ‘)’ AS emp_info FROM employees
Best answer: C
Explanation: In Oracle SQL, || is the concatenation operator. Place it between columns and single-quoted literals to build one displayed value, then assign an alias to that expression.
The core rule is that Oracle concatenates character expressions with ||, not with +. To combine several columns and fixed text, write each column or literal as part of one expression, such as last_name || ', ' || first_name || ' (' || job_id || ')'. Single quotes create literal text like the comma, space, and parentheses, and AS emp_info names the resulting output column. Oracle also provides CONCAT, but that function accepts only two arguments per call, so it is not the correct syntax here as written. The key takeaway is to chain multiple columns and literals with || when you want one displayed value.
CONCAT fails because Oracle CONCAT accepts only two expressions per call.+ fails because Oracle does not use + for string concatenation.'last_name, first_name (job_id)' returns the same literal text for every row instead of column values.Topic: Report Aggregated Data by Using Group Functions
A payroll report shows an unexpected average.
SELECT SUM(allowance) total_allowance,
AVG(allowance) avg_allowance
FROM emp_allowances;
The allowance column currently has these three values: 1000, 2000, and NULL.
Oracle returns SUM = 3000 and AVG = 1500, but the business rule says a missing allowance must be treated as zero in both calculations. What is the best fix?
Options:
A. Add DISTINCT inside both aggregate functions.
B. Add WHERE allowance IS NOT NULL to the query.
C. Use SUM(NVL(allowance,0)) and AVG(NVL(allowance,0)).
D. Replace AVG(allowance) with COUNT(allowance).
Best answer: C
Explanation: In Oracle, SUM and AVG ignore NULL values. If the requirement is to treat missing data as zero, convert NULL to 0 before applying the aggregate functions, such as with NVL.
The core concept is that Oracle group functions like SUM and AVG do not include NULL values in their calculations. In the sample data, SUM(allowance) returns 3000 because Oracle adds 1000 + 2000 and skips NULL. AVG(allowance) returns 1500 because Oracle divides 3000 by the count of non-NULL values, which is 2.
If the business rule says a missing allowance is really zero, you must substitute 0 before aggregation:
SUM(NVL(allowance,0))AVG(NVL(allowance,0))That changes the effective values to 1000, 2000, and 0, so the average becomes 1000.
The key takeaway is that NULL is ignored by these aggregates unless you explicitly convert it to a value.
DISTINCT confusion Removing duplicates does not make NULL contribute 0; it addresses repeated values, not missing ones.COUNT mismatch COUNT(allowance) returns how many non-NULL rows exist, not the numeric average.NULLs Adding WHERE allowance IS NOT NULL preserves the current behavior because SUM and AVG already ignore NULL.Topic: Manage Objects with Data Dictionary Views
A developer runs this query to check what EMP refers to:
SELECT owner, object_name, object_type
FROM all_objects
WHERE object_name = 'EMP';
Result:
| OWNER | OBJECT_NAME | OBJECT_TYPE |
|---|---|---|
| HR | EMP | TABLE |
| PUBLIC | EMP | SYNONYM |
Based on the exhibit, which interpretation is correct?
Options:
A. EMP is a table owned by PUBLIC and a synonym owned by HR.
B. EMP is one object in HR whose type is both TABLE and SYNONYM.
C. EMP is a table owned by HR, and a public synonym named EMP also exists.
D. EMP is a synonym owned by HR that points to a table in PUBLIC.
Best answer: C
Explanation: ALL_OBJECTS identifies each accessible object by its owner and type. The exhibit shows two separate rows for EMP: a table owned by HR and a synonym owned by PUBLIC.
The core concept is reading data dictionary rows independently. In ALL_OBJECTS, each row represents one object the current user can access, and the OWNER and OBJECT_TYPE columns describe that specific row.
Here, HR / EMP / TABLE means HR owns a table named EMP. PUBLIC / EMP / SYNONYM means there is also a public synonym named EMP. These are two distinct objects with the same name, not one object with two types.
This output does not show what object the synonym points to; for that, you would inspect a synonym dictionary view such as ALL_SYNONYMS. The key takeaway is that object name alone is not enough; owner and type must be read together.
HR owns a synonym reverses the owner and type shown in the result.PUBLIC owns the table swaps the two rows; the PUBLIC row is a synonym.EMP as one object ignores that ALL_OBJECTS returns one row per object.Topic: Manage Tables and Relationships by Using DDL
A developer runs the following statement:
CREATE TABLE project_docs (
doc_id NUMBER(6),
doc_name VARCHAR2(40),
created_on DATE(7),
comments CLOB
);
Which explanation correctly describes the Oracle SQL behavior?
Options:
A. It fails because VARCHAR2(40) must specify BYTE or CHAR.
B. It fails because NUMBER(6) must specify both precision and scale.
C. It fails because CLOB must specify a maximum size.
D. It fails because DATE(7) is invalid; DATE cannot include a length.
Best answer: D
Explanation: The statement fails because DATE does not accept a length or precision declaration in parentheses. The other column definitions shown are valid Oracle column definitions as written.
This tests whether the type declaration matches what that Oracle data type actually allows. VARCHAR2(40) is valid because character types accept a length. NUMBER(6) is also valid because numeric precision can be specified without an explicit scale. CLOB is declared without a size, so comments CLOB is valid. The problem is created_on DATE(7): Oracle DATE is a fixed datetime type and is declared only as DATE, with no length or precision clause.
When reviewing CREATE TABLE statements, check the parentheses after each type carefully. A size or precision that is valid for one type does not automatically apply to another type. That is the key rule being tested here.
VARCHAR2(40) must include BYTE or CHAR fails because those keywords are optional, not required.NUMBER(6) must include scale fails because scale is optional in Oracle numeric definitions.CLOB needs a maximum size fails because CLOB is declared without a length specification.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?
Options:
A. A synonym
B. A view
C. A sequence
D. An index
Best answer: B
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.
Topic: Use Subqueries to Solve Queries
A developer wants to list employees whose salary is above the companywide average salary. They run this query:
SELECT employee_id, department_id, salary
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);
The result differs by department, and the developer says the subquery should be evaluated only once for the statement. What is the best fix?
Options:
A. Replace the subquery with a cross join to departments.
B. Add GROUP BY department_id inside the subquery.
C. Add DISTINCT to the outer SELECT list.
D. Remove the outer reference so the subquery is noncorrelated.
Best answer: D
Explanation: The inner query is correlated because it references a column from the outer query, e.department_id. In Oracle, that means the subquery is reevaluated once for each outer row, so it returns a department average rather than one companywide average.
A correlated subquery depends on values from the current row of the outer query. Here, department_id = e.department_id ties the inner query to each employee row, so Oracle reevaluates the subquery for each outer row and compares each employee to that employee’s department average.
If the requirement is a single companywide average, the subquery must be noncorrelated, such as:
SELECT employee_id, department_id, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
The key takeaway is simple: an outer-query column inside the subquery makes it correlated and therefore row-by-row in effect.
GROUP BY confusion would produce multiple averages, not one companywide value for the comparison.DISTINCT confusion removes duplicate result rows; it does not change how often the subquery is evaluated.departments is unrelated to computing one overall salary average and can create extra rows.Topic: Manage Objects with Data Dictionary Views
A developer can query two tables in other schemas: HR.EMP_STAGE and SALES.EMPLOYEES. This join fails:
SELECT *
FROM hr.emp_stage s
JOIN sales.employees e
ON s.employee_id = e.employee_id;
Oracle returns ORA-01722: invalid number. You suspect the EMPLOYEE_ID columns are defined differently. What is the best next step to confirm the column names, data types, and nullability for both accessible tables?
Options:
A. Query USER_TAB_COLUMNS for the two tables.
B. Query ALL_CONSTRAINTS for the two tables.
C. Query ALL_TAB_COLUMNS for the two tables.
D. Query DBA_OBJECTS for the two tables.
Best answer: C
Explanation: ALL_TAB_COLUMNS is the correct dictionary view when you need column metadata for objects in other schemas that you can access. It lets you inspect column names, data types, and the NULLABLE setting to verify whether the join columns are defined consistently.
The core concept is choosing the correct data dictionary view for the metadata you need. Because the tables are in other schemas, USER_TAB_COLUMNS is too narrow; it only shows columns for objects in your own schema. ALL_TAB_COLUMNS is the right view because it exposes column-level metadata for objects accessible to the current user, including OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, and NULLABLE.
In this case, comparing the EMPLOYEE_ID definitions in HR.EMP_STAGE and SALES.EMPLOYEES is the right troubleshooting step for an ORA-01722 suspicion. If one side is character data and the other is numeric, Oracle may attempt an implicit conversion during the join and fail. Object-level or constraint-focused views do not directly give the full column datatype/nullability details needed here.
USER_TAB_COLUMNS is too limited because it does not show column metadata for tables in other schemas unless they are your own objects.DBA_OBJECTS is the wrong metadata level because it lists objects, not column definitions such as datatype or nullability.ALL_CONSTRAINTS is incomplete for this task because it describes constraints, not the full column metadata needed to compare join-column definitions.Topic: Display Data from Multiple Tables
A developer wants one row per employee earning more than 5,000, along with that employee’s department name. All 35 qualifying employees have a valid department_id, and DEPARTMENTS contains 12 rows. The query returns 420 rows.
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.salary > 5000;
What is the best fix?
Options:
A. Replace the table list with CROSS JOIN.
B. Move the salary filter to a HAVING clause.
C. Add DISTINCT to the SELECT list.
D. Add e.department_id = d.department_id as the join predicate.
Best answer: D
Explanation: The 420-row result matches 35 - 12, so each qualifying employee is being paired with every department row. That is an accidental Cartesian product caused by a missing join predicate, so the query must link the two tables by department_id.
This query lists two tables in the FROM clause but supplies no condition relating them, so Oracle forms a Cartesian product. Because there are 35 qualifying employees and 12 department rows, the row count becomes 420. The intended result is one row per employee with that employee’s department name, so the tables must be joined on the common key.
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.salary > 5000;
Use CROSS JOIN only when every row from one table should pair with every row from the other.
CROSS JOIN would only make the wrong row multiplication intentional, not fix it.DISTINCT can hide symptoms in some queries, but it does not restore the missing relationship between tables.HAVING is for grouped results, not for connecting rows from related tables.Topic: Manage Data in Different Time Zones
Which statement correctly describes Oracle CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP?
Options:
A. CURRENT_DATE returns DATE in the database time zone; CURRENT_TIMESTAMP returns TIMESTAMP in the session time zone; LOCALTIMESTAMP returns TIMESTAMP WITH TIME ZONE.
B. CURRENT_DATE returns DATE without session time-zone adjustment; CURRENT_TIMESTAMP and LOCALTIMESTAMP both return TIMESTAMP WITH TIME ZONE in the database time zone.
C. CURRENT_DATE returns TIMESTAMP in the session time zone; CURRENT_TIMESTAMP returns TIMESTAMP WITH LOCAL TIME ZONE; LOCALTIMESTAMP returns DATE.
D. CURRENT_DATE returns DATE in the session time zone; CURRENT_TIMESTAMP returns TIMESTAMP WITH TIME ZONE in the session time zone; LOCALTIMESTAMP returns TIMESTAMP in the session time zone.
Best answer: D
Explanation: In Oracle, these current-datetime expressions are evaluated in the session time zone, not the database server time zone. The key distinction is the returned datatype: CURRENT_DATE returns DATE, CURRENT_TIMESTAMP returns TIMESTAMP WITH TIME ZONE, and LOCALTIMESTAMP returns TIMESTAMP.
The core rule is to separate time-zone behavior from datatype. CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP all reflect the current date/time in the session time zone. They differ in what datatype they return.
CURRENT_DATE → DATECURRENT_TIMESTAMP → TIMESTAMP WITH TIME ZONELOCALTIMESTAMP → TIMESTAMPSo if you need the session-based current timestamp and also need the time-zone offset in the value, use CURRENT_TIMESTAMP. If you want the session-based current timestamp without a time-zone field in the datatype, use LOCALTIMESTAMP. The closest trap is confusing session time zone with database time zone or assuming LOCALTIMESTAMP includes time-zone data.
CURRENT_DATE is not a TIMESTAMP, and LOCALTIMESTAMP is not DATE.CURRENT_TIMESTAMP does not return TIMESTAMP WITH LOCAL TIME ZONE.CURRENT_TIMESTAMP and LOCALTIMESTAMP do not return the same timestamp datatype.Topic: Manage Indexes, Synonyms, and Sequences
User SALESAPP already has SELECT on HR.EMPLOYEES and wants to query that table without typing the schema name each time.
Exhibit:
SELECT employee_id, last_name
FROM hr.employees;
Which statement is the best next step to let SALESAPP reference the object with a shorter name in future queries?
Options:
A. CREATE SYNONYM emp FOR hr.employees;
B. CREATE SEQUENCE emp START WITH 1;
C. CREATE VIEW emp AS SELECT * FROM hr.employees;
D. CREATE INDEX emp ON hr.employees(employee_id);
Best answer: A
Explanation: A synonym is the Oracle object used to provide an alternate name for a table or view. Because SALESAPP already has object access, creating a synonym lets future queries use the shorter name instead of HR.EMPLOYEES.
The core concept is that a synonym is an alias for another schema object, such as a table or view. In this case, SALESAPP wants to stop repeating the fully qualified name HR.EMPLOYEES, so the correct action is to create a synonym that points to that table.
After that, queries can use the synonym name directly, for example FROM emp, as long as the underlying privilege on HR.EMPLOYEES exists. A view would create a separate schema object based on a query, which is not necessary when the only goal is a shorter object reference.
The key takeaway is that synonyms simplify object naming; they do not replace object privileges.
Topic: Retrieve Data by Using SQL SELECT Statements
Given this query:
SELECT first_name || ' ' || last_name AS full_name,
salary * 12 AS annual_salary
FROM employees;
Which columns appear in the result set?
Options:
A. full_name and annual_salary
B. All columns from employees plus annual_salary
C. first_name, last_name, and salary
D. full_name, salary, and annual_salary
Best answer: A
Explanation: A SELECT statement returns only the expressions listed in its projection. Here, two expressions are selected, and both have aliases, so the result set contains full_name and annual_salary.
The core concept is projection: Oracle returns the items named in the SELECT list, not every underlying source column. In this query, first_name || ' ' || last_name is one projected expression and salary * 12 is a second projected expression. Because each expression has an alias, those aliases identify the result columns as full_name and annual_salary.
The original base columns are used only to build the expressions. They do not appear separately unless they are also listed in the SELECT clause. A common confusion is to assume referenced columns are automatically included, but Oracle returns only what is explicitly projected.
first_name, last_name, and salary are inputs to expressions, not separate projected columns.salary is not selected by itself; only salary * 12 is selected.* assumption fails because the query does not use *, so Oracle does not return all columns from employees.Topic: Manage Views
An existing view named emp50_v already exists. You must redefine it to return employee_id, last_name, and department_id from employees only for rows where department_id = 50, using the same view name and without dropping the view first. Which SQL statement is the best solution?
Options:
A. ALTER VIEW emp50_v AS SELECT employee_id, last_name, department_id FROM employees WHERE department_id = 50;
B. CREATE VIEW emp50_v AS SELECT employee_id, last_name, department_id FROM employees WHERE department_id = 50;
C. CREATE OR REPLACE VIEW emp50_v AS SELECT employee_id, last_name, department_id FROM employees WHERE department_id = 50;
D. DROP VIEW emp50_v; CREATE VIEW emp50_v AS SELECT employee_id, last_name, department_id FROM employees WHERE department_id = 50;
Best answer: C
Explanation: To redefine an existing view without dropping it first, Oracle uses CREATE OR REPLACE VIEW. This updates the stored query for the same view name in one statement.
The core concept is view replacement. In Oracle, CREATE OR REPLACE VIEW lets you change the SELECT statement of an existing view while keeping the same object name and without issuing DROP VIEW first.
This fits the requirement because the statement:
A plain CREATE VIEW works only when the view does not already exist. ALTER VIEW is used for operations such as recompiling a view, not for replacing its defining query. Dropping and recreating the view changes it in two steps and violates the stated requirement.
CREATE VIEW alone is not the correct replacement method.ALTER VIEW ... AS SELECT ... to redefine a view query.Topic: Restrict and Sort Data
A SQL*Plus script contains:
SELECT employee_id, last_name
FROM employees
WHERE job_id = '&job_code';
Each time the script runs, SQL*Plus prompts for a value for job_code. A developer wants the script to run without prompting and to use SA_REP until a different value is assigned in the session. What is the best fix?
Options:
A. Use DEFINE job_code = SA_REP before running the script.
B. Use SET VERIFY ON before running the script.
C. Use UNDEFINE job_code before running the script.
D. Replace &job_code with &&job_code in the script.
Best answer: A
Explanation: DEFINE is used to create or assign a value to a substitution variable in the current SQL*Plus session. Because the variable already has a value, the script can substitute it directly instead of prompting the user.
The core concept is that DEFINE stores a value for a substitution variable in the SQLPlus session. In this case, setting job_code to SA_REP lets SQLPlus replace &job_code with that value when the statement runs, so no prompt appears.
A simple pattern is:
DEFINE&variable_nameUNDEFINE does the opposite: it removes the variable definition. SET VERIFY ON only displays old and new substitution text, and && can persist a prompted value after the first prompt, but it does not preassign the value required here.
The key takeaway is that DEFINE is the command used to preset substitution variable values.
UNDEFINE clears an existing substitution value and would still lead to prompting later.SET VERIFY ON controls display of substitution text, not whether a value is prompted.&&job_code still prompts the first time when no value has been predefined in the session.Topic: Use Subqueries to Solve Queries
Given this EMPLOYEES data:
| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 1 | Ana | 10 | 5000 |
| 2 | Ben | 10 | 7000 |
| 3 | Cara | 20 | 4000 |
| 4 | Dev | 20 | 4000 |
| 5 | Eli | 30 | 9000 |
What is returned by this query?
SELECT emp_name
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE dept_id = e.dept_id
)
ORDER BY emp_name;
Options:
A. No rows are returned
B. Ana and Ben
C. Ben and Eli
D. Ben only
Best answer: D
Explanation: This is a correlated subquery because the inner query uses e.dept_id from the current outer row. Oracle computes the average salary within each employee’s department, and only Ben has a salary strictly greater than that department average.
A correlated subquery is evaluated for each outer row using that row’s values. Here, each employee is compared to the AVG(salary) for employees in the same dept_id.
Ben (7,000) qualifies and Ana (5,000) does not.Cara nor Dev qualifies because the condition is > not >=.Eli does not qualify because his salary equals the average.The key point is that the comparison is per department, not against one overall company average.
Ana fails because 5,000 is below her department average of 6,000.Eli fails because a one-person department makes the average equal to that employee’s salary, and > excludes equality.Ben is above the average for department 10.Topic: Use Conversion Functions and Conditional Expressions
orders.shipped_date is a DATE column and can contain NULL. A report must display the date as YYYY-MM-DD, but show Not shipped when the date is NULL.
Which SELECT-list expression correctly applies Oracle’s nested-function rules to produce that result?
Options:
A. TO_CHAR(NVL(shipped_date, 'Not shipped'), 'YYYY-MM-DD')
B. NVL(TO_CHAR(shipped_date, 'YYYY-MM-DD'), 'Not shipped')
C. NVL(shipped_date, TO_CHAR('Not shipped', 'YYYY-MM-DD'))
D. NVL2(shipped_date, 'Not shipped', TO_CHAR(shipped_date, 'YYYY-MM-DD'))
Best answer: B
Explanation: The date must be converted to character before substituting the text Not shipped. In Oracle, nested functions evaluate from the inside out, and NVL must return compatible datatypes, so the formatted date string has to be the first argument to NVL.
This tests the order of nested function evaluation and datatype compatibility. shipped_date is a DATE, but the required fallback value is text. Because NVL returns a value compatible with its first argument, the DATE must be converted to character first with TO_CHAR, and then NVL can replace a null result with the character string Not shipped.
So the correct pattern is:
DATE to text firstNVL to supply the text fallbackIf NVL is applied to the DATE before conversion, Oracle must try to reconcile a DATE with a text literal, which does not produce the intended result. The key takeaway is that function order matters when null handling and datatype conversion are combined.
TO_CHAR outside NVL fails because it tries to mix a DATE value with the text literal Not shipped before formatting.NVL on the DATE column fails because the replacement value is character data, not a compatible DATE value.NVL2 reversed logic fails because it returns Not shipped when a date exists and tries to format the date only when it is null.Topic: Retrieve Data by Using SQL SELECT Statements
Which Oracle SQL statement correctly displays each employee last_name prefixed by the fixed text Employee: in a single output column?
Options:
A. SELECT 'Employee: ' || last_name FROM employees;
B. SELECT "Employee: " || last_name FROM employees;
C. SELECT 'Employee: ' + last_name FROM employees;
D. SELECT 'Employee: ' AS last_name FROM employees;
Best answer: A
Explanation: Oracle string literals use single quotes, and character values are combined with the concatenation operator ||. To show fixed text together with a column value in one result column, place the literal in the select list and concatenate it to the column.
The core rule is that Oracle treats text enclosed in single quotes as a character literal. When you want fixed text and a column value to appear together in one displayed column, use the concatenation operator ||.
In this case, the correct pattern is:
So SELECT 'Employee: ' || last_name FROM employees; returns values such as Employee: King.
Using double quotes does not create a string literal in Oracle; double quotes are for quoted identifiers. Using + is not the Oracle operator for character concatenation, and selecting only the literal does not include the employee name.
"Employee: " is treated as an identifier, not a character literal.|| for string concatenation, not +.last_name alongside it.Topic: Report Aggregated Data by Using Group Functions
Given this SALES data:
| REGION_ID | BONUS |
|---|---|
| 10 | 500 |
| 10 | NULL |
| 10 | 500 |
| 20 | NULL |
| 20 | NULL |
A query groups by REGION_ID and returns this output:
| REGION_ID | RESULT |
|---|---|
| 10 | 2 |
| 20 | 0 |
Which aggregate expression most likely produced RESULT?
Options:
A. COUNT(BONUS)
B. SUM(BONUS)
C. COUNT(DISTINCT BONUS)
D. COUNT(*)
Best answer: A
Explanation: The result matches COUNT(BONUS) because Oracle counts only non-NULL values for a specific column. Region 10 has two non-NULL bonus values, while region 20 has none, so it returns 0.
In Oracle SQL, COUNT(*) counts all rows in each group, but COUNT(column_name) counts only rows where that column is not NULL. Here, BONUS has values 500, NULL, and 500 in region 10, so the count of non-NULL bonuses is 2. Region 20 has only NULL bonuses, so the count is 0.
COUNT(DISTINCT BONUS) would count distinct non-NULL bonus values, not all non-NULL rows. Since region 10 has only one distinct non-NULL bonus value (500), that result would be 1, not 2. SUM(BONUS) would add numeric values rather than count rows.
The key takeaway is that COUNT(column) ignores NULL values, while COUNT(*) does not.
COUNT(*) would return 3 for region 10 and 2 for region 20 because it includes every row.COUNT(DISTINCT BONUS) would return 1 for region 10 because both non-NULL values are 500.SUM(BONUS) would total bonus amounts, not produce a row count.Topic: Use Subqueries to Solve Queries
A developer needs a query that returns employees whose salary is greater than every salary earned in department 30. The current statement fails with ORA-01427: single-row subquery returns more than one row.
SELECT employee_id, salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE department_id = 30
);
Which change best fixes the query while preserving the requirement?
Options:
A. Use > ANY with the subquery.
B. Add DISTINCT inside the subquery.
C. Use > ALL with the subquery.
D. Use IN with the subquery.
Best answer: C
Explanation: The subquery returns multiple salaries, so a single-row comparison with > is invalid. Because the requirement says greater than every returned salary, the correct fix is to use > ALL with the multi-row subquery.
This is a multi-row subquery problem. A comparison such as salary > (subquery) expects the subquery to return exactly one value, but department 30 can return many salary values, which causes ORA-01427.
To preserve the stated requirement, Oracle needs a multi-row comparison operator:
> ALL means the outer salary must be greater than every value returned.> ANY means greater than at least one returned value.IN tests equality to one of the returned values.So the corrected predicate is salary > ALL (SELECT salary ... ). The closest distractor is > ANY, but that would return employees whose salary is higher than just one department 30 salary, not all of them.
> ANY is too weak because it matches being greater than at least one salary, not every salary.IN changes the test from a greater-than comparison to equality against returned values.DISTINCT does not help because the subquery can still return multiple rows even after duplicate salaries are removed.Topic: Display Data from Multiple Tables
You have these tables:
DEPARTMENTS(department_id PK, department_name)
EMPLOYEES(employee_id PK, last_name, department_id FK)
EMPLOYEES.department_id can be NULL.
Which query returns each employee last name with the matching department name by correctly joining the child table to its parent table? Only rows with a matching department must be returned.
Options:
A. SELECT e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id
B. SELECT e.last_name, d.department_name FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id
C. SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id
D. SELECT e.last_name, d.department_name FROM employees e JOIN departments d ON e.employee_id = d.department_id
Best answer: A
Explanation: The correct join matches the child table’s foreign key, employees.department_id, to the parent table’s primary key, departments.department_id. Because the requirement says only matching rows should appear, an inner join is the best choice.
In a parent-child relationship, the child table stores the foreign key that points to the parent table’s primary key. Here, EMPLOYEES is the child and DEPARTMENTS is the parent, so the join condition must be e.department_id = d.department_id.
An inner join returns only rows where that relationship actually matches, which fits the requirement exactly. Since EMPLOYEES.department_id can be NULL, any outer join that preserves employee rows could include employees with no department, and any outer join that preserves department rows could include departments with no employees. The key idea is to match related key columns first, then choose the join type that returns the required row set.
employee_id to department_id compares unrelated keys, so parent and child rows are not matched correctly.employees preserves employees with NULL or nonmatching department values.(+) on employees preserves all departments, including those without employees.Topic: Use Subqueries to Solve Queries
A developer must update each sales representative to the average quota of representatives in that rep’s own region_id.
Exhibit:
UPDATE sales_reps r
SET quota = (
SELECT AVG(quota)
FROM sales_reps
GROUP BY region_id
);
The statement fails with ORA-01427: single-row subquery returns more than one row. Which change best fixes the problem?
Options:
A. Correlate the subquery with WHERE region_id = r.region_id.
B. Use AVG(DISTINCT quota) in the subquery.
C. Add AND ROWNUM = 1 to the subquery.
D. Remove GROUP BY region_id from the subquery.
Best answer: A
Explanation: The subquery currently returns one average per region, so the UPDATE cannot assign a single value to each row. A correlated subquery fixes this by referencing the current outer row and calculating the average only for that row’s region.
In an UPDATE, a scalar subquery must return exactly one value for each row being updated. Here, GROUP BY region_id produces multiple rows overall, so Oracle raises ORA-01427. The correct fix is to make the subquery correlated so it uses the current row from the outer UPDATE and returns one average for that row’s region_id.
UPDATE sales_reps r
SET quota = (
SELECT AVG(quota)
FROM sales_reps
WHERE region_id = r.region_id
);
Removing the grouping would return a single overall average, but that would not satisfy the per-region requirement.
AVG(DISTINCT quota) changes how duplicates are treated, not how many grouped rows the subquery returns.ROWNUM = 1 hides the error by choosing one arbitrary row, which does not match each representative’s own region.Topic: Report Aggregated Data by Using Group Functions
In Oracle SQL, which clause restricts grouped results after a GROUP BY, such as returning only departments whose COUNT(*) is greater than 5?
Options:
A. ORDER BY
B. HAVING
C. DISTINCT
D. WHERE
Best answer: B
Explanation: HAVING is used to filter the results of groups formed by GROUP BY. When a condition depends on an aggregate like COUNT(*), Oracle evaluates that condition with HAVING, not WHERE.
The core concept is that WHERE filters individual rows before grouping, while HAVING filters groups after Oracle has applied aggregate functions. In the example, the condition uses COUNT(*), which exists only after rows have been grouped, so the restriction must be placed in HAVING.
A typical pattern is:
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;
Use WHERE for row-level conditions such as salary > 5000, and use HAVING for group-level conditions such as AVG(salary) > 5000. The closest confusion is WHERE, but it cannot directly filter on aggregate results in this grouped context.
WHERE confusion applies before grouping, so it filters detail rows rather than aggregated groups.ORDER BY confusion only sorts the final result set; it does not remove groups.DISTINCT confusion removes duplicate rows or expressions, not groups based on aggregate conditions.Topic: Manage Views
A developer creates this object:
CREATE VIEW recent_orders AS
SELECT order_id, order_date, amount
FROM orders
WHERE order_date >= DATE '2026-01-01';
Later, another session inserts a row into orders with order_date = DATE '2026-03-10' and commits. The view is not replaced.
Which statement correctly applies the Oracle SQL rule?
Options:
A. Querying recent_orders can return the new row.
B. Querying recent_orders cannot return the new row until the view is recreated.
C. The view stored qualifying rows when it was created.
D. The new row exists only in recent_orders, not in orders.
Best answer: A
Explanation: In Oracle, a regular view does not store its own copy of table rows. It stores a SELECT statement, so when the base table changes and the change is committed, a query against the view can show the new qualifying row.
The key rule is that a regular view is a stored query, not a separate data store. Oracle saves the view definition and evaluates it against the current contents of the base table when the view is queried. In this case, the inserted row is committed in orders, and its order_date satisfies the view predicate, so it can appear in the result of recent_orders.
This means you usually do not recreate a view just because base-table data changed. Replacing or recreating a view is for changing the view definition, not for refreshing ordinary query results. The common confusion is mixing a regular view with an object that stores data separately.
orders, the base table.Topic: Manage Tables by Using DML Statements
A user executes these statements in one session and has not issued COMMIT:
UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 50;
DELETE FROM employees
WHERE employee_id = 300;
The user now wants to discard all changes made in the current transaction and return the data to its last committed state. Which statement should be executed?
Options:
A. ROLLBACK;
B. COMMIT;
C. SAVEPOINT undo_pt;
D. ROLLBACK TO undo_pt;
Best answer: A
Explanation: ROLLBACK is the Oracle transaction control statement that cancels uncommitted DML in the current transaction. Because no commit has occurred, it reverses both the UPDATE and the DELETE and returns the session to the last committed state.
In Oracle, a transaction includes DML changes such as INSERT, UPDATE, and DELETE until they are ended by COMMIT or undone by ROLLBACK. Here, the session made two DML changes and has not committed them, so they are still uncommitted.
Using ROLLBACK without a savepoint name reverses the entire current transaction:
COMMIT would make the changes permanent, not discard them. A savepoint only marks a position inside a transaction, and ROLLBACK TO works only when that savepoint already exists.
COMMIT persists changes because it ends the transaction by making the UPDATE and DELETE permanent.SAVEPOINT marks only a position and does not undo any work by itself.ROLLBACK TO undo_pt needs an existing savepoint and would target only work after that point, not necessarily the whole transaction.Topic: Manage Tables by Using DML Statements
Examine the exhibit:
ORDER_ID ORDER_STATUS SHIPPED_DATE
-------- ------------ ------------
101 PAID NULL
102 PAID 10-APR-26
103 NEW NULL
104 PAID NULL
UPDATE orders
SET shipped_date = SYSDATE
WHERE shipped_date IS NULL
AND order_status = 'PAID';
Which result best describes this UPDATE statement?
Options:
A. Only order 103 is updated.
B. All four orders are updated.
C. Orders 101, 103, and 104 are updated.
D. Only orders 101 and 104 are updated.
Best answer: D
Explanation: An UPDATE changes only rows that satisfy its WHERE clause. Here, a row must meet both conditions: SHIPPED_DATE must be NULL and ORDER_STATUS must be PAID, so only orders 101 and 104 qualify.
The core concept is that UPDATE ... WHERE affects only the targeted rows that evaluate to true for the full search condition. In this statement, Oracle checks each row against both predicates joined by AND.
order_status = 'PAID'shipped_date IS NULLOrder 101 matches both conditions, so it is updated. Order 104 also matches both conditions, so it is updated. Order 102 is PAID but already has a shipped date, so it is excluded. Order 103 has a NULL shipped date but its status is NEW, so it is excluded.
The key takeaway is that a precise WHERE clause prevents unintended row changes.
NEW order included fails because order 103 does not satisfy order_status = 'PAID'.NULL dates included fails because SHIPPED_DATE IS NULL alone is not enough; the status condition must also match.WHERE clause, so Oracle does not update every row.Topic: Manage Tables and Relationships by Using DDL
A developer runs this statement and gets ORA-02291: integrity constraint (HR.EMPLOYEES_DEPT_FK) violated - parent key not found.
CREATE TABLE departments (
department_id NUMBER CONSTRAINT departments_pk PRIMARY KEY
);
CREATE TABLE employees (
employee_id NUMBER CONSTRAINT employees_pk PRIMARY KEY,
department_id NUMBER CONSTRAINT employees_dept_fk
REFERENCES departments(department_id),
salary NUMBER CONSTRAINT employees_sal_ck CHECK (salary > 0)
);
INSERT INTO employees VALUES (100, 50, 6000);
What is the best fix?
Options:
A. Insert department 50 first, or use an existing department_id.
B. Increase the precision of employees.department_id.
C. Commit the transaction before inserting the employee row.
D. Set salary to NULL in the insert statement.
Best answer: A
Explanation: This insert fails because employees.department_id is a foreign key that references departments.department_id. Oracle rejects the child row when the referenced parent key value does not already exist.
The core concept is referential integrity. A foreign key in a child table must match an existing primary key or unique key value in the parent table unless the foreign key is NULL. Here, employees.department_id references departments.department_id, but the insert uses department 50 and no parent row for 50 exists.
To fix this, either insert the parent department row first or change the employee row to use a department ID that is already present in DEPARTMENTS. The failure is not about data type, column size, or the salary check condition. When Oracle raises ORA-02291, the next step is to verify the referenced parent key value.
6000 already satisfies CHECK (salary > 0) and is not the failing constraint.Topic: Use Conversion Functions and Conditional Expressions
A developer passes the value '2026-04-18' as a character string. The orders.order_date column is DATE, and user sessions may have different NLS_DATE_FORMAT settings. Which WHERE clause explicitly converts that character value to DATE instead of relying on Oracle’s implicit conversion?
Options:
A. WHERE TO_CHAR(order_date,'YYYY-MM-DD') = '2026-04-18'
B. WHERE order_date = DATE '2026-04-18'
C. WHERE order_date = '2026-04-18'
D. WHERE order_date = TO_DATE('2026-04-18','YYYY-MM-DD')
Best answer: D
Explanation: Explicit conversion in Oracle SQL uses a conversion function such as TO_DATE with a format model. Comparing a DATE column to a character literal without that function causes Oracle to attempt an implicit conversion based on session settings.
The key rule is that implicit conversion happens automatically when Oracle must reconcile unlike data types, but its result can depend on session settings such as NLS_DATE_FORMAT. In this case, a character value is being compared to a DATE column, so the safest and clearest approach is to explicitly convert the string with TO_DATE and a matching format model.
TO_DATE('2026-04-18','YYYY-MM-DD') tells Oracle exactly how to interpret the incoming character data before the comparison. That makes the statement stable across sessions. Converting the column with TO_CHAR changes the comparison into character logic instead, and a date literal is valid syntax but is not converting the passed character string.
DATE, so it can fail or change behavior with different NLS_DATE_FORMAT values.TO_CHAR is explicit conversion, but it converts DATE to character rather than converting the incoming character value to DATE.Topic: Display Data from Multiple Tables
Given these table definitions:
DEPARTMENTS(department_id PK, department_name)
EMPLOYEES(employee_id PK, last_name,
department_id FK REFERENCES DEPARTMENTS(department_id))
Which join condition correctly matches each parent department row to its child employee rows in an inner join?
Options:
A. DEPARTMENTS.department_id = EMPLOYEES.employee_id
B. DEPARTMENTS.department_name = EMPLOYEES.department_id
C. DEPARTMENTS.department_id = EMPLOYEES.manager_id
D. DEPARTMENTS.department_id = EMPLOYEES.department_id
Best answer: D
Explanation: To join parent and child tables correctly, use the parent table’s primary key and the child table’s foreign key. Here, EMPLOYEES.department_id references DEPARTMENTS.department_id, so those two columns form the correct join condition.
The core rule is that a parent-child join uses the referenced key relationship: the parent table supplies the primary key, and the child table stores that value in its foreign key column. In this schema, DEPARTMENTS.department_id is the parent key, and EMPLOYEES.department_id is the child foreign key.
So the correct inner join condition is:
DEPARTMENTS.department_id = EMPLOYEES.department_id
Joining department_id to employee_id, department_name, or manager_id does not follow the declared relationship and would either return incorrect matches or rely on unrelated data. The key takeaway is to join on the defined PK-FK pair, not just on columns with similar names or compatible data types.
department_id to employee_id ignores the foreign key relationship.department_name to department_id compares unrelated columns.department_id to manager_id does not represent the parent-child link shown in the schema.Topic: Manage Tables by Using DML Statements
In Oracle Database, when must an INSERT statement include an explicit target-column list to avoid value-position errors?
Options:
A. When it does not provide values for every table column
B. When the table includes a foreign key constraint
C. When one of the inserted values is generated from a sequence
D. When the table contains a primary key column
Best answer: A
Explanation: An INSERT without a column list must supply a value for every column in the table, in the table’s column order. If you want to omit any columns and let them take defaults or NULL, you must name the target columns explicitly.
The core rule is that Oracle matches VALUES items by position when no target-column list is given. That means the statement must provide one value for every column, in the exact table column order. If you want to skip any column—such as one that can use a default value or accept NULL—you must specify the columns you are inserting into so Oracle can align each value correctly.
For example, omitting the column list is valid only when the VALUES clause covers all columns in order. If fewer columns are being populated, the safe and required approach is to list those target columns explicitly. Primary keys, foreign keys, and sequences do not by themselves force a column list; the deciding factor is whether every table column is being supplied by position.
sequence_name.NEXTVAL supplies a value expression, but it does not itself require naming columns.INSERT statement.Topic: Use Set Operators
Table current_staff has column emp_id NUMBER, and table project_assignments has column emp_id NUMBER.
Which statement returns only the emp_id values that exist in both tables and sorts the final result by emp_id?
Options:
A. SELECT emp_id FROM current_staff ORDER BY emp_id INTERSECT SELECT emp_id FROM project_assignments
B. SELECT emp_id FROM current_staff INTERSECT SELECT emp_id FROM project_assignments ORDER BY emp_id
C. SELECT emp_id FROM current_staff UNION SELECT emp_id FROM project_assignments ORDER BY emp_id
D. SELECT emp_id FROM current_staff MINUS SELECT emp_id FROM project_assignments ORDER BY emp_id
Best answer: B
Explanation: Use INTERSECT when you need only the rows returned by both SELECT statements. In Oracle, when a set operator is used, the ORDER BY clause applies to the combined result and is written once at the end.
INTERSECT compares the result sets of two queries and returns only the distinct rows that appear in both. Here, that matches the requirement to find emp_id values present in both current_staff and project_assignments.
MINUS does something different: it returns rows from the first query that are not returned by the second. Also, with Oracle set operators, ORDER BY is placed after the entire compound query, not between the individual SELECT statements.
The key takeaway is: use INTERSECT for common rows, use MINUS for rows in the first query only, and sort only the final combined result.
MINUS fails because it returns IDs found only in current_staff, not IDs common to both tables.INTERSECT fails because ORDER BY is not placed between component queries in a compound query.UNION fails because it returns IDs from either table, not just the shared IDs.Topic: Use Subqueries to Solve Queries
Examine the exhibit:
EMPLOYEES
- EMPLOYEE_ID
- LAST_NAME
- SALARY
- DEPARTMENT_ID
DEPARTMENTS
- DEPARTMENT_ID
- DEPARTMENT_NAME
Requirement:
Return LAST_NAME and SALARY for employees who work in the
department named 'Sales'.
Assume DEPARTMENTS.DEPARTMENT_NAME is unique.
Based only on the exhibit, which Oracle SQL approach is most appropriate?
Options:
A. Use an inner join, because the result must display columns from both tables.
B. Use a single-row subquery with = to get the department_id for Sales, then filter EMPLOYEES.
C. Use a self-join on EMPLOYEES, because the department name is derived from employee rows.
D. Use a multi-row subquery with IN, because Sales can return several department_id values.
Best answer: B
Explanation: A single-row subquery is the best fit because the requirement uses one table to look up a single value in another table, then filters the main table by that value. Since DEPARTMENT_NAME is unique, the subquery returns exactly one department_id, so = is appropriate.
This requirement describes a lookup pattern: find the department_id for the department named Sales, then return employee data for rows with that department_id. In Oracle SQL, that is a classic use of a single-row subquery.
A suitable shape is:
SELECT last_name, salary
FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE department_name = 'Sales'
);
The key fact is that DEPARTMENTS.DEPARTMENT_NAME is unique, so the subquery returns one value. That makes = correct. A join could also retrieve the data, but it is not required just to perform a single-value lookup from another table.
DEPARTMENT_NAME is unique, so IN is unnecessary.DEPARTMENTS, not from another copy of EMPLOYEES.Topic: Relational Database Concepts
A developer created this object and expected duplicate email values to be rejected:
CREATE INDEX cust_email_ix ON customers(email);
Later, both statements succeed:
INSERT INTO customers (customer_id, email)
VALUES (101, 'ana@example.com');
INSERT INTO customers (customer_id, email)
VALUES (102, 'ana@example.com');
What is the best fix?
Options:
A. Rebuild cust_email_ix so Oracle rechecks duplicates.
B. Create a synonym for customers and insert through it.
C. Define a UNIQUE constraint on customers.email.
D. Add another nonunique index including customer_id.
Best answer: C
Explanation: The problem is a data-integrity rule, not an access-path issue. A normal index can speed lookups, but it does not state that email must be unique; that rule belongs in a UNIQUE constraint.
In Oracle, tables and constraints represent the relational structure and rules of the data. Indexes are separate storage/access objects created mainly to improve retrieval performance. A normal index can contain repeated key values, so duplicate emails are still allowed.
If the business rule is that each email must appear only once, define that rule with a UNIQUE constraint on email (or use a primary key if that column is the row identifier). Oracle may create or use an index to support the constraint, but the constraint is the logical definition of uniqueness. That is why rebuilding or adding nonunique indexes does not solve the problem.
The key takeaway is that integrity rules belong to relational structures, while indexes support access efficiency.
Topic: Retrieve Data by Using SQL SELECT Statements
Examine the query.
SELECT DISTINCT department_id AS dept_id,
last_name,
salary * 12 AS annual_salary
FROM employees;
Which columns appear in the result set?
Options:
A. DEPARTMENT_ID, LAST_NAME, ANNUAL_SALARY
B. DEPARTMENT_ID, LAST_NAME, SALARY
C. DEPT_ID, LAST_NAME, SALARY
D. DEPT_ID, LAST_NAME, ANNUAL_SALARY
Best answer: D
Explanation: The result set columns come from the SELECT list. department_id is displayed as DEPT_ID, last_name keeps its name, and salary * 12 is displayed as ANNUAL_SALARY; DISTINCT affects rows, not columns.
In Oracle SQL, each item in the SELECT list becomes one output column. If an item has an alias, the alias is used as the column heading; if it does not, the column keeps its original name or expression heading. In this query, the projection is:
department_id AS dept_idlast_namesalary * 12 AS annual_salarySo the result set has exactly three columns: DEPT_ID, LAST_NAME, and ANNUAL_SALARY. The DISTINCT keyword removes duplicate rows based on all selected columns, but it does not change which columns are returned.
DEPARTMENT_ID fails because that column is explicitly aliased as dept_id.SALARY fails because the query selects the expression salary * 12, not the original salary column.DEPARTMENT_ID with ANNUAL_SALARY is still wrong because the first projected column is aliased.DISTINCT changes duplicate row handling only; it does not add, remove, or rename projected columns.Topic: Manage Indexes, Synonyms, and Sequences
Exhibit:
CREATE SEQUENCE ord_seq START WITH 100 INCREMENT BY 1;
A developer wants to consume only one new value from ORD_SEQ. Based on the exhibit, which statement would advance the sequence by two instead?
Options:
A. SELECT ord_seq.NEXTVAL, ord_seq.NEXTVAL FROM dual;
B. SELECT ord_seq.NEXTVAL, ord_seq.CURRVAL FROM dual;
C. SELECT ord_seq.NEXTVAL FROM dual CONNECT BY LEVEL <= 2;
D. SELECT ord_seq.NEXTVAL FROM dual;
Best answer: C
Explanation: Oracle advances a sequence once per row produced by the outer query block, not once per column reference. The statement using CONNECT BY LEVEL <= 2 returns two rows from DUAL, so NEXTVAL is consumed twice.
The key rule is that NEXTVAL increments a sequence once for each row returned by the outer query block. A query against DUAL normally returns one row, so a single-row query consumes only one sequence value. If the query generates two rows, NEXTVAL is evaluated for each row and the sequence advances twice.
In the statement with CONNECT BY LEVEL <= 2, Oracle produces two rows from DUAL, so ORD_SEQ.NEXTVAL is fetched two times. By contrast, multiple references to NEXTVAL or CURRVAL in the same single returned row do not cause extra increments for that row.
The main takeaway is to watch row generation, not just how many times NEXTVAL appears in the select list.
NEXTVAL from plain DUAL returns one row, so it advances the sequence once.NEXTVAL with CURRVAL still uses one generated value for that one returned row.NEXTVAL references in the same single row do not consume two different sequence numbers.Topic: Use Set Operators
Examine the exhibit:
Table: departments_current
Columns: department_id NUMBER, department_name VARCHAR2(30)
Table: departments_audit
Columns: department_id NUMBER, department_name VARCHAR2(30)
Requirement: Return the rows that exist in departments_current
but not in departments_audit, sorted by department_id.
Which SQL statement correctly satisfies the requirement?
Options:
A. SELECT department_id, department_name FROM departments_current ORDER BY department_id MINUS SELECT department_id, department_name FROM departments_audit
B. SELECT department_id, department_name FROM departments_audit MINUS SELECT department_id, department_name FROM departments_current ORDER BY department_id
C. SELECT department_id, department_name FROM departments_current MINUS SELECT department_id, department_name FROM departments_audit ORDER BY department_id
D. SELECT department_id, department_name FROM departments_current INTERSECT SELECT department_id, department_name FROM departments_audit ORDER BY department_id
Best answer: C
Explanation: Use MINUS when you need rows returned by the first SELECT that do not appear in the second. In Oracle, when set operators are used, the ORDER BY clause belongs at the end of the entire compound query, not inside the first branch.
The requirement is a set difference: rows in departments_current that are not in departments_audit. In Oracle SQL, MINUS performs exactly that comparison, returning distinct rows from the first query that are absent from the second query. Because both queries project the same two columns with compatible datatypes, the set operator is valid.
ORDER BY is applied only once, after the full set operation is complete. Reversing the query order would return the opposite difference, and using INTERSECT would return only rows common to both tables.
The key rule is: choose MINUS for “in first query but not second,” and place ORDER BY at the end of the compound statement.
departments_audit that are missing from departments_current, which is the opposite of the requirement.INTERSECT finds only rows present in both tables, not rows unique to the current table.ORDER BY before MINUS, but Oracle applies ordering after the complete set operation.Topic: Use Single-Row Functions to Customize Output
The job_title column currently stores the value sALES manager. A report must display this value as Sales Manager. Which expression returns the required result?
Options:
A. UPPER(job_title)
B. LOWER(job_title)
C. INITCAP(job_title)
D. LOWER(INITCAP(job_title))
Best answer: C
Explanation: Use INITCAP when each word must start with an uppercase letter and the remaining letters must be lowercase. For sALES manager, Oracle returns Sales Manager, which matches the requirement exactly.
UPPER, LOWER, and INITCAP perform different character-case transformations in Oracle SQL. UPPER converts all letters to uppercase, LOWER converts all letters to lowercase, and INITCAP converts the first letter of each word to uppercase while lowercasing the other letters in each word.
In this case, the stored value is sALES manager, and the required output is Sales Manager. That is exactly the transformation produced by INITCAP(job_title). Applying LOWER after INITCAP would undo the capitalization and return all lowercase text instead.
The key takeaway is to match the function to the display requirement: full uppercase, full lowercase, or initial capitals for each word.
UPPER(job_title) returns SALES MANAGER, not mixed case.LOWER(job_title) returns sales manager, so the initial letters are not capitalized.LOWER(INITCAP(job_title)) first creates initial caps, then converts the whole result back to lowercase.Topic: Display Data from Multiple Tables
A developer runs this query and gets many more rows than expected:
SELECT e.employee_id, d.department_name
FROM employees e, departments d;
Why did the result set expand unexpectedly?
Options:
A. The query omitted the join condition between the tables.
B. The query did not include an ORDER BY clause.
C. The query selected columns from both tables without DISTINCT.
D. The query used table aliases in the FROM clause.
Best answer: A
Explanation: A Cartesian product occurs when multiple tables are listed in the FROM clause but no join condition relates them. Oracle then combines each row from one table with every row from the other table, which can greatly increase the row count.
The core rule is that a multi-table query needs a join condition to relate rows between the tables. In the example, employees and departments both appear in the FROM clause, but there is no condition such as e.department_id = d.department_id in a WHERE clause and no ANSI JOIN ... ON clause. Because no relationship is specified, Oracle returns all possible row combinations between the two tables.
If one table has 100 rows and the other has 10 rows, the result contains 1,000 rows. That unexpected multiplication is the classic sign of a Cartesian product. The closest distractor is the DISTINCT idea, but DISTINCT only removes duplicate result rows; it does not create or fix missing join logic.
ORDER BY changes output order, not the number of joined rows.DISTINCT is not the cause because missing join criteria produce extra combinations, not merely duplicate copies of the same row values.Topic: Use Conversion Functions and Conditional Expressions
Which statement about NVL, NULLIF, and COALESCE is correct in Oracle SQL?
Options:
A. NVL returns NULL when its two expressions are equal, and COALESCE accepts exactly two expressions.
B. COALESCE returns the last non-NULL expression, and NULLIF returns the second expression when the two expressions are equal.
C. COALESCE returns the first non-NULL expression, NVL checks only two expressions, and NULLIF returns NULL when its two expressions are equal.
D. NULLIF returns the first non-NULL expression, and NVL can accept any number of expressions.
Best answer: C
Explanation: NVL, NULLIF, and COALESCE are similar but not interchangeable. NVL works with two expressions, COALESCE returns the first non-NULL value from a list, and NULLIF returns NULL when two expressions compare as equal.
The core distinction is what each function tests and what it returns. NVL(expr1, expr2) checks whether expr1 is NULL; if it is, Oracle returns expr2, otherwise it returns expr1. COALESCE(expr1, expr2, ...) can evaluate two or more expressions and returns the first one that is not NULL. NULLIF(expr1, expr2) is different from both: it compares two expressions and returns NULL if they are equal; otherwise it returns expr1.
So the correct statement is the one that assigns first-non-NULL behavior to COALESCE, two-argument null replacement to NVL, and equality-to-NULL behavior to NULLIF. A common trap is mixing up NULLIF with NVL because both use two arguments, but they solve different problems.
NVL returns NULL when values are equal confuses NVL with NULLIF.NULL behavior to NULLIF confuses it with COALESCE.COALESCE returns the last non-NULL value reverses its actual behavior; it returns the first one found.Topic: Control User Access
Which statement correctly distinguishes an Oracle object privilege from a system privilege?
Options:
A. An object privilege allows administrative actions such as CREATE USER.
B. A system privilege is the same thing as a role assigned to a user.
C. An object privilege controls access to a specific object, such as SELECT on HR.EMPLOYEES.
D. A system privilege controls access only to one named table or view.
Best answer: C
Explanation: Oracle separates privileges by scope. Object privileges apply to specific schema objects like tables, views, or sequences, while system privileges allow broader actions in the database, such as creating objects or users.
The core distinction is scope. An object privilege lets a user perform an action on a specific object owned by a schema, such as SELECT, INSERT, or UPDATE on a table or view. A system privilege grants authority to perform a type of action across the database, such as CREATE TABLE or CREATE USER, subject to Oracle rules.
When a question asks about access to one table, view, or other object, think object privilege. When it asks about administrative capability or the right to create or manage database structures, think system privilege.
A role is different again: it is a container that can hold privileges, not a privilege type itself.
CREATE USER is a classic system privilege, not an object privilege.Topic: Use Conversion Functions and Conditional Expressions
A report must display a contact number for each customer using this fallback order: use mobile_phone if it is not null; otherwise use office_phone; otherwise display 'Not available'. Which expression correctly applies this rule without replacing a non-null mobile number?
Options:
A. COALESCE(mobile_phone, office_phone, 'Not available')
B. CASE mobile_phone WHEN NULL THEN office_phone ELSE 'Not available' END
C. NVL(mobile_phone, office_phone, 'Not available')
D. NVL2(mobile_phone, office_phone, 'Not available')
Best answer: A
Explanation: The required logic is “first non-null value from a list.” In Oracle SQL, COALESCE is designed for exactly that pattern, so it keeps a non-null mobile_phone, otherwise checks office_phone, then uses the literal fallback.
COALESCE evaluates its arguments from left to right and returns the first one that is not null. That matches the stated fallback order exactly: preferred value first, secondary value next, and a final default last.
In this case:
mobile_phone has a value, that value is returnedmobile_phone is null, Oracle checks office_phone'Not available' is returnedThis is the safest compact choice when you need more than one fallback and must preserve an existing non-null value. The closest trap is NVL2, which does test for null, but it returns a different expression when the first argument is not null, so it does not preserve mobile_phone itself.
NVL with three arguments fails because NVL accepts only two expressions in Oracle SQL.NVL2 fails because a non-null mobile_phone would return office_phone, not the mobile number.CASE ... WHEN NULL form fails because NULL is not matched that way, and the expression also omits the needed preserve-mobile behavior.Topic: Report Aggregated Data by Using Group Functions
A report must show the number of employees in each department who have a recorded value in commission_pct. Which aggregate expression meets this requirement?
Options:
A. COUNT(commission_pct)
B. AVG(commission_pct)
C. COUNT(*)
D. SUM(commission_pct)
Best answer: A
Explanation: Use COUNT(column) when you need the number of non-NULL values in a specific column. Because the requirement is to count only employees with a recorded commission_pct, COUNT(commission_pct) is the correct Oracle SQL expression.
The core concept is that Oracle group functions treat NULL values differently depending on the function. COUNT(*) counts all rows in the group, but COUNT(column) counts only rows where that column is not NULL. Here, the report needs the number of employees who actually have a commission recorded, so the count must ignore rows where commission_pct is NULL.
SUM(commission_pct) would add the commission values, and AVG(commission_pct) would calculate their average, so neither returns a row count. A common trap is choosing COUNT(*), but that would include employees without a commission value.
COUNT(*) counts every row in each department, even when commission_pct is NULL.SUM(commission_pct) returns a total commission amount, not how many employees have one.AVG(commission_pct) returns the mean commission value, not a count.Topic: Control User Access
read_role already contains the SELECT privilege on HR.EMPLOYEES. Which statement gives user APP_USER that access by a direct privilege grant, not by granting the role?
Options:
A. GRANT read_role TO PUBLIC
B. GRANT SELECT ON hr.employees TO read_role
C. GRANT read_role TO app_user
D. GRANT SELECT ON hr.employees TO app_user
Best answer: D
Explanation: A direct privilege grant names the privilege and grants it straight to the user. For an object privilege, the Oracle syntax is GRANT privilege ON object TO user, so granting SELECT on HR.EMPLOYEES to APP_USER is the only direct grant here.
The core distinction is between granting a privilege itself and granting a role that happens to contain privileges. A direct grant gives the user the privilege explicitly, while a role grant gives the user whatever privileges are bundled inside that role.
For an object privilege, the direct form is:
GRANT privilege ON schema.object TO userIn the stem, read_role already has SELECT on HR.EMPLOYEES, but granting read_role to APP_USER would still be indirect access through a role. Granting SELECT on HR.EMPLOYEES to read_role also does not give the privilege directly to APP_USER.
The key takeaway is that GRANT ... TO user is not always a direct privilege grant; it is direct only when the thing being granted is the privilege itself, not a role name.
APP_USER access indirectly because the privilege comes from read_role, not from a direct privilege entry.read_role, but APP_USER still would not receive it directly.PUBLIC makes the role available broadly and still does not create a direct privilege grant to APP_USER.Topic: Restrict and Sort Data
A developer wants all orders for sales rep 10 that are either NEW or PENDING, but the query returns extra rows for other reps.
SELECT order_id, sales_rep_id, status
FROM orders
WHERE sales_rep_id = 10
AND status = 'NEW'
OR status = 'PENDING';
What is the best fix?
Options:
A. Add parentheses so the rep filter applies to both statuses.
B. Sort the rows first so only rep 10 remains.
C. Add DISTINCT to remove the unintended rows.
D. Leave the filter as written because OR is evaluated first.
Best answer: A
Explanation: Oracle evaluates AND before OR. Without parentheses, the predicate is treated as (sales_rep_id = 10 AND status = 'NEW') OR status = 'PENDING', so any PENDING row is returned. Grouping the two status conditions fixes the row count.
This is an operator-precedence problem in the WHERE clause. In Oracle SQL, AND is evaluated before OR, so the original filter does not mean “rep 10 and either of these statuses.” It means “rep 10 with NEW, or any row with PENDING.”
The intended logic should be written so the status alternatives are evaluated together:
WHERE sales_rep_id = 10
AND (status = 'NEW' OR status = 'PENDING')
Parentheses make the intended grouping explicit and prevent extra rows from being returned. The key takeaway is that when AND and OR are mixed, add parentheses whenever the business rule depends on a specific grouping.
OR before AND.DISTINCT fails because it removes duplicates, not logically incorrect matches.ORDER BY affects presentation, not which rows satisfy the WHERE clause.Topic: Manage Data in Different Time Zones
A user session sets TIME_ZONE to -05:00. The database server clock is at +00:00. In that same session, this query is executed:
SELECT CURRENT_DATE,
CURRENT_TIMESTAMP,
LOCALTIMESTAMP,
SYSDATE,
SYSTIMESTAMP
FROM dual;
It returns values equivalent to these:
CURRENT_DATE = 18-APR-26 09:15:00CURRENT_TIMESTAMP = 18-APR-26 09:15:00.000000 -05:00LOCALTIMESTAMP = 18-APR-26 09:15:00.000000SYSDATE = 18-APR-26 14:15:00SYSTIMESTAMP = 18-APR-26 14:15:00.000000 +00:00Which statement correctly explains the result?
Options:
A. SYSDATE changes when the session time zone changes.
B. CURRENT_* uses session time zone; SYS* uses server clock.
C. CURRENT_DATE and LOCALTIMESTAMP use database time zone.
D. CURRENT_TIMESTAMP omits time zone; LOCALTIMESTAMP includes it.
Best answer: B
Explanation: The CURRENT_* datetime expressions reflect the session time zone, so they show 09:15 for a session at -05:00. The SYS* expressions reflect the database server’s clock, so they show 14:15 at +00:00 in the same session.
Oracle has two different reference points for “current” datetime values. CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP are session-based, so their displayed date and time are adjusted to the session time zone. By contrast, SYSDATE and SYSTIMESTAMP come from the database server operating system clock.
In this example, the server is at +00:00 and the session is at -05:00, so the same moment appears as 14:15 on the server and 09:15 in the session.
CURRENT_TIMESTAMP includes a time-zone offset, while LOCALTIMESTAMP does not. CURRENT_DATE is still session-based even though its data type is DATE.
The key takeaway is that CURRENT_* follows the session time zone, while SYS* follows the server clock.
CURRENT_DATE and LOCALTIMESTAMP are session-based, not based on the database time zone.SYSDATE fails because changing the session time zone does not make SYSDATE use session-local time.CURRENT_TIMESTAMP includes the offset, while LOCALTIMESTAMP returns a timestamp without time zone.Topic: Use Conversion Functions and Conditional Expressions
In the EMPLOYEES table, employee 205 has commission_pct = NULL. This query returns HAS COMMISSION for that row:
SELECT CASE commission_pct
WHEN NULL THEN 'NO COMMISSION'
ELSE 'HAS COMMISSION'
END AS comm_status
FROM employees
WHERE employee_id = 205;
Which Oracle SQL rule explains this result?
Options:
A. Simple CASE uses equality comparison, and NULL never equals NULL.
B. CASE converts a numeric NULL to 0 before testing WHEN clauses.
C. Oracle implicitly converts NULL so WHEN NULL works only for character data.
D. The ELSE branch is evaluated first whenever the CASE expression is NULL.
Best answer: A
Explanation: This is a simple CASE expression, which compares commission_pct to each WHEN value using equality. Because NULL is not equal to anything, including another NULL, WHEN NULL does not match and Oracle returns the ELSE result.
Oracle has two CASE forms. A simple CASE uses the pattern CASE expr WHEN value THEN ..., which behaves like equality testing between expr and each WHEN value. For the shown row, Oracle is effectively checking whether commission_pct = NULL, and that condition is never true because comparisons to NULL return unknown, not true.
To test for nulls, use a searched CASE instead:
CASE
WHEN commission_pct IS NULL THEN 'NO COMMISSION'
ELSE 'HAS COMMISSION'
END
The key takeaway is that WHEN NULL in a simple CASE does not match a null column value.
NULL with 0 inside CASE.ELSE is not first: Oracle checks WHEN conditions first and uses ELSE only when none match.WHEN NULL match a null value in a simple CASE.Topic: Manage Data in Different Time Zones
A user runs this query in one session after setting the session time zone to -05:00:
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI') AS sd,
TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD HH24:MI') AS cd,
TO_CHAR(SYSTIMESTAMP, 'TZH:TZM') AS st_tz,
TO_CHAR(CURRENT_TIMESTAMP, 'TZH:TZM') AS ct_tz
FROM dual;
The output is:
SD CD ST_TZ CT_TZ
2026-04-18 14:00 2026-04-18 09:00 +00:00 -05:00
Which statement correctly explains the result?
Options:
A. SYSDATE and SYSTIMESTAMP are converted automatically to the session time zone.
B. CURRENT_TIMESTAMP has no time-zone information unless TO_CHAR adds it.
C. CURRENT_DATE and CURRENT_TIMESTAMP use the session time zone.
D. CURRENT_DATE differs from SYSDATE because it stores fractional seconds.
Best answer: C
Explanation: In Oracle, CURRENT_DATE and CURRENT_TIMESTAMP are session time-zone aware. SYSDATE and SYSTIMESTAMP reflect the database server’s current date and time, so their displayed values can differ in the same session.
The key rule is that Oracle has two families of current-datetime expressions. SYSDATE and SYSTIMESTAMP come from the database server’s operating system clock and time zone. CURRENT_DATE and CURRENT_TIMESTAMP are evaluated in the current session time zone.
In the exhibit, the session time zone is -05:00, so CURRENT_DATE shows 09:00 and CURRENT_TIMESTAMP shows -05:00. The server-side values still reflect 14:00 and +00:00. This is why different current-datetime expressions can return different results in the same session.
A common near-miss is assuming all current time functions automatically shift to the session time zone; only the CURRENT_... expressions do.
SYSDATE and SYSTIMESTAMP do not automatically change to the session time zone.CURRENT_DATE is a DATE value and does not store fractional seconds.CURRENT_TIMESTAMP is a TIMESTAMP WITH TIME ZONE, so the time-zone data is part of the value.Topic: Manage Tables by Using DML Statements
EMP_TEMP initially contains:
EMP_ID SALARY
1 1000
2 2000
3 3000
In one session, Oracle executes:
UPDATE emp_temp SET salary = salary + 100 WHERE emp_id = 1;
SAVEPOINT s1;
DELETE FROM emp_temp WHERE emp_id = 2;
INSERT INTO emp_temp VALUES (4, 4000);
ROLLBACK TO s1;
UPDATE emp_temp SET salary = salary + 500 WHERE emp_id = 3;
COMMIT;
DELETE FROM emp_temp WHERE emp_id = 1;
ROLLBACK;
After the last ROLLBACK, which rows and salaries remain persisted in EMP_TEMP?
Options:
A. (1,1100), (2,2000), (3,3500)
B. (1,1100), (2,2000), (3,3000)
C. (2,2000), (3,3500)
D. (1,1000), (3,3500), (4,4000)
Best answer: A
Explanation: ROLLBACK TO s1 undoes only the delete of EMP_ID 2 and the insert of EMP_ID 4, not the earlier update to EMP_ID 1. After the later update to EMP_ID 3, COMMIT makes both updates permanent, and the final ROLLBACK cancels only the post-commit delete.
Oracle transaction control depends on statement order. A savepoint lets you undo work done after that savepoint, while COMMIT ends the current transaction and makes its changes permanent.
EMP_ID 1 is updated to 1100 before s1, so ROLLBACK TO s1 does not undo it.EMP_ID 2 and insert of EMP_ID 4 occur after s1, so they are undone.EMP_ID 3 is then updated to 3500.COMMIT permanently saves EMP_ID 1 = 1100 and EMP_ID 3 = 3500.EMP_ID 1 starts a new transaction and is undone by the final ROLLBACK.So the persisted table keeps rows 1, 2, and 3, with salaries 1100, 2000, and 3500.
EMP_ID 4 was inserted after s1 and was undone by ROLLBACK TO s1.COMMIT and was reversed by the final ROLLBACK.COMMIT, so it was permanently saved.Topic: Manage Tables and Relationships by Using DDL
Examine the CREATE TABLE statement:
CREATE TABLE meetings (
meeting_id NUMBER(5),
subject VARCHAR2(40 CHAR) NOT NULL,
start_time TIMESTAMP WITH TIME ZONE,
organizer_id NUMBER,
CONSTRAINT meetings_pk PRIMARY KEY (meeting_id)
);
Which interpretation of the resulting column structure is correct?
Options:
A. Five columns are created, including MEETINGS_PK as the primary key column.
B. SUBJECT is created as CHAR(40) because CHAR length semantics were specified.
C. START_TIME is created as DATE because the time zone is stored separately from the column datatype.
D. Four columns are created: MEETING_ID, SUBJECT, START_TIME, and ORGANIZER_ID with the declared datatypes.
Best answer: D
Explanation: CREATE TABLE creates one column for each column definition in the statement. Here, the table has four columns, and the primary key constraint named MEETINGS_PK applies to MEETING_ID without creating an extra column.
In Oracle SQL, the resulting table structure comes from the column definitions inside CREATE TABLE. This statement defines four columns: MEETING_ID, SUBJECT, START_TIME, and ORGANIZER_ID.
A table-level constraint such as CONSTRAINT meetings_pk PRIMARY KEY (meeting_id) enforces a rule on an existing column or columns; it does not create a new column. Also, VARCHAR2(40 CHAR) is still a VARCHAR2 column, with length measured in characters rather than bytes. TIMESTAMP WITH TIME ZONE remains that full datatype and is not reduced to DATE.
The key takeaway is to separate column definitions from constraints when interpreting the final table structure.
TIMESTAMP WITH TIME ZONE is a distinct Oracle datetime datatype.VARCHAR2(40 CHAR) uses character length semantics but is still VARCHAR2, not CHAR.Topic: Use Single-Row Functions to Customize Output
A report selects cust_name values that are stored in inconsistent case, such as jANE DOE and mARY ann. The output must display each word with its first letter uppercase and the remaining letters lowercase. Which expression should be used in the SELECT clause?
Options:
A. LOWER(cust_name)
B. UPPER(cust_name)
C. INITCAP(cust_name)
D. TRIM(cust_name)
Best answer: C
Explanation: Use INITCAP when you need display formatting that capitalizes each word in a character value. It is the Oracle single-row character function designed for names or titles stored in mixed or inconsistent case.
INITCAP is the correct Oracle character function for this requirement because it reformats a string so that the first character of each word is uppercase and the remaining characters are lowercase. Applied to values like jANE DOE or mARY ann, it produces Jane Doe and Mary Ann in the SELECT list.
This is a formatting task, not a filtering task, so the function should be applied in the projection:
INITCAP formats word case for display.UPPER converts all letters to uppercase.LOWER converts all letters to lowercase.TRIM removes leading or trailing characters, usually spaces.The closest distractors change case too, but they do not produce title-style output for each word.
JANE DOE.jane doe.Topic: Relational Database Concepts
Two tables already exist: EMPLOYEES and DEPARTMENTS. A business rule states that every non-NULL value in EMPLOYEES.DEPARTMENT_ID must match an existing DEPARTMENTS.DEPARTMENT_ID. Improving query speed or providing an alternate object name is not the goal.
Which Oracle SQL choice correctly applies this rule?
Options:
A. Create a synonym for DEPARTMENTS.
B. Create an index on EMPLOYEES(DEPARTMENT_ID).
C. Create a view that joins EMPLOYEES to DEPARTMENTS.
D. Add a foreign key on EMPLOYEES(DEPARTMENT_ID) referencing DEPARTMENTS(DEPARTMENT_ID).
Best answer: D
Explanation: This requirement is about enforcing a relational rule, not improving access or performance. In Oracle, a foreign key constraint is the object that enforces referential integrity between child and parent tables.
The core concept is the difference between logical relational structures and supporting access objects. A foreign key is a relational constraint: it defines and enforces the relationship between a child column and a parent key. Here, that means any non-NULL EMPLOYEES.DEPARTMENT_ID value must already exist in DEPARTMENTS.DEPARTMENT_ID.
Indexes, synonyms, and views serve different purposes:
None of those objects enforce referential integrity by themselves. The key takeaway is that relationships are enforced with constraints, while performance or access convenience is handled with supporting objects.
DEPARTMENT_ID, but it does not require a matching parent row.Topic: Relational Database Concepts
A junior developer created an index on employees(employee_id) named EMP_ID_IDX. He then runs SELECT * FROM emp_id_idx; and gets ORA-00942: table or view does not exist. He says the index should contain the employee records. What is the best fix or next step?
Options:
A. Rebuild EMP_ID_IDX so its entries can be selected directly.
B. Query EMPLOYEES; table rows and columns store the data.
C. Export the index to a file, then query that file.
D. Grant SELECT on EMP_ID_IDX before querying it.
Best answer: B
Explanation: The problem is a conceptual mix-up between a table and an index. In Oracle SQL, relational data is represented logically by tables, with rows for records and columns for attributes, so the developer should query EMPLOYEES, not the index name.
The symptom occurs because EMP_ID_IDX is an index name, not a table or view. In Oracle’s relational model, application data is represented logically in tables: each row is one record, and each column is an attribute of that record. An index is a separate structure Oracle can use to locate table rows more efficiently, but it is not the relational object you query to return employee data. Likewise, files are part of physical storage, not the logical row-and-column model exposed through SQL. If you want employee records, query the EMPLOYEES table; if you want index metadata, query the data dictionary. The key takeaway is that tables hold relational data, while indexes and files support storage and access.
SELECT on an index object.Topic: Display Data from Multiple Tables
Which Oracle SQL construct is used to compare rows within the same table by joining the table to itself, such as matching each employee to that employee’s manager?
Options:
A. Correlated subquery
B. Cross join
C. Outer join
D. Self join
Best answer: D
Explanation: A self join is the correct construct because it treats one table as two logical copies in the same query. This is the standard way to compare related rows stored in one table, such as employee and manager rows in EMPLOYEES.
A self join occurs when the same table appears more than once in the FROM clause with different aliases, and the aliases are joined using a relationship column. In Oracle SQL, this is commonly used for hierarchical-style row comparisons stored in one table, such as an employee row containing a manager_id that points to another employee row.
For example, one alias can represent employees and the other alias can represent managers, even though both aliases refer to the same base table. The key idea is not the table name itself, but using separate aliases and a join condition to compare related rows within that single table.
A correlated subquery can also compare rows, but it is not a join of the table to itself; the core concept here is specifically a self join.
Topic: Use Conversion Functions and Conditional Expressions
A table stores a timestamp-like value in a VARCHAR2 column named created_txt using this format:
2026/04/18 19:05:33
You need a query expression that converts created_txt to a DATE explicitly and correctly. Which expression applies the Oracle SQL rule?
Options:
A. TO_DATE(created_txt, 'YYYY/MM/DD HH24:MI:SS')
B. TO_DATE(created_txt, 'YYYY/DD/MM HH24:MI:SS')
C. TO_DATE(created_txt, 'YYYY/MM/DD HH:MM:SS')
D. TO_DATE(created_txt, 'DD/MM/YYYY HH24:MI:SS')
Best answer: A
Explanation: TO_DATE converts character data to a DATE only when the format model matches the input string correctly. Here the value is year/month/day followed by 24-hour time, and Oracle uses MI for minutes, not MM.
The core rule is that the TO_DATE format model must match the character string’s order and time elements. In 2026/04/18 19:05:33, the components are YYYY/MM/DD and then a 24-hour time HH24:MI:SS. Oracle format element MM means month, while MI means minute, so using HH:MM:SS is incorrect for the time portion. Also, changing the date order to day/month or year/day/month does not match the stored character data.
A DATE in Oracle can store both date and time to the second, so TO_DATE is appropriate here. The safest practice is explicit conversion with the exact format model rather than relying on implicit conversion or session settings.
HH:MM:SS treats MM as month, not minutes.YYYY/DD/MM does not match the stored year/month/day order.DD/MM/YYYY expects the day first, but the string begins with the year.Topic: Use Set Operators
Consider these Oracle SQL statements:
-- Query 1
SELECT 1 AS n FROM dual
UNION
SELECT 1 FROM dual;
-- Query 2
SELECT 1 AS n FROM dual
UNION ALL
SELECT 1 FROM dual;
Which statement is correct about the returned rows?
Options:
A. Both queries return two rows because each SELECT reads from DUAL.
B. Both queries return one row because the selected values are identical.
C. Query 1 returns one row; Query 2 returns two identical rows.
D. Query 1 returns two identical rows; Query 2 returns one row.
Best answer: C
Explanation: UNION and UNION ALL differ only in duplicate handling. With identical rows from both SELECT statements, UNION collapses them to one row, while UNION ALL preserves both rows.
In Oracle, UNION combines result sets and removes duplicate rows from the final combined output. UNION ALL also combines result sets, but it does not eliminate duplicates.
In the exhibit, each SELECT returns the same single row: the value 1. Because those rows are identical:
UNION query returns one rowUNION ALL query returns two rowsThe key distinction is simple: use UNION for distinct combined results, and use UNION ALL when duplicate rows must remain in the output.
UNION is the duplicate-removing operator, not UNION ALL.UNION performs duplicate elimination.DUAL having one row per SELECT does not force both queries to return two rows.Topic: Use Set Operators
A developer runs this statement and gets ORA-01790: expression must have same datatype as corresponding expression.
SELECT department_id, hire_date
FROM employees
UNION
SELECT department_id, manager_id
FROM departments;
Assume hire_date is DATE and manager_id is NUMBER. What is the best conceptual fix?
Options:
A. Add DISTINCT to both queries before the set operator.
B. Give both second columns the same alias name.
C. Make corresponding column positions datatype-compatible, using explicit conversion if needed.
D. Replace UNION with UNION ALL to allow different datatypes.
Best answer: C
Explanation: UNION and UNION ALL require the same number of expressions, and each matching position must be datatype-compatible. In this statement, the first query returns a DATE in the second position, while the second query returns a NUMBER, so Oracle raises ORA-01790.
For Oracle set operations, compatibility is checked by column position, not by column name. Both queries already return two columns, so the problem is not the column count; it is the datatype mismatch in the second expression. The second column from employees is hire_date (DATE), while the second column from departments is manager_id (NUMBER).
To fix this, make the corresponding expressions compatible by either:
Changing from UNION to UNION ALL only affects duplicate elimination; it does not remove datatype compatibility rules.
UNION ALL confusion fails because UNION ALL still requires matching SELECT-list positions to be datatype-compatible.DISTINCT confusion fails because duplicate handling is unrelated to the DATE versus NUMBER mismatch.Topic: Control User Access
User REPORT_APP must be able to query all rows in HR.SALES_ORDERS and update only the ORDER_TOTAL column.
Exhibit:
CREATE TABLE hr.sales_orders (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER,
order_total NUMBER(8,2),
status VARCHAR2(20)
);
Which statement should HR execute?
Options:
A. GRANT SELECT, UPDATE ON hr.sales_orders TO report_app
B. GRANT UPDATE(order_total) ON hr.sales_orders TO report_app
C. GRANT SELECT ON hr.sales_orders TO report_app WITH GRANT OPTION
D. GRANT SELECT, UPDATE(order_total) ON hr.sales_orders TO report_app
Best answer: D
Explanation: To let a user query a table and update only one column, Oracle uses an object privilege grant with SELECT plus column-level UPDATE(column_name). The correct statement grants both required capabilities without allowing updates to other columns.
This tests table object privileges in Oracle. A table owner can grant SELECT on the table and can also limit UPDATE to specific columns by naming them in parentheses. In this case, REPORT_APP needs to read rows from HR.SALES_ORDERS and modify only ORDER_TOTAL, so the grant must include both SELECT and UPDATE(order_total).
A full UPDATE privilege on the table would allow changes to any updatable column, which is broader than required. WITH GRANT OPTION only lets the grantee pass along a privilege they already received; it does not add missing privileges such as UPDATE. The key takeaway is that Oracle supports column-level control for UPDATE, INSERT, and REFERENCES on tables.
ORDER_TOTAL.SELECT, but it still does not allow updating the table.UPDATE(order_total) does not satisfy the requirement to query all rows.Topic: Manage Tables and Relationships by Using DDL
While designing a table, you must decide whether to define a numeric column as NUMBER or as NUMBER(p,s). Which requirement most clearly means the column definition should include both precision and scale?
Options:
A. Store row counts with no stated upper limit
B. Store sequence-generated order IDs
C. Store prices with at most 6 digits total and 2 decimal places
D. Store numeric values used only for sorting results
Best answer: C
Explanation: Use NUMBER(p,s) when Oracle must enforce both the total number of digits and the number of digits after the decimal point. A price column with a fixed numeric shape is the clearest case for specifying precision and scale.
In Oracle, NUMBER(p,s) is used when the business rule requires a specific numeric format. Precision (p) is the total number of significant digits, and scale (s) is the number of digits to the right of the decimal point. So a value such as a price that must fit within a known size and keep exactly two decimal places should be defined with both precision and scale, such as NUMBER(6,2).
If a column does not have a stated digit limit or decimal-place rule, plain NUMBER is usually enough. For whole-number limits, precision alone may be sufficient, such as NUMBER(8). Usage alone—such as being generated by a sequence or appearing in ORDER BY—does not create a need for precision and scale.
NUMBER is more appropriate than forcing precision and scale.Topic: Control User Access
A role named REPORTING_R was granted query access to HR.EMPLOYEES with this statement:
GRANT SELECT ON hr.employees TO reporting_r;
Which statement removes that access from the role?
Options:
A. REVOKE SELECT ON hr.employees FROM reporting_r;
B. DROP SELECT ON hr.employees FROM reporting_r;
C. REVOKE SELECT FROM reporting_r ON hr.employees;
D. DENY SELECT ON hr.employees TO reporting_r;
Best answer: A
Explanation: Oracle uses REVOKE <privilege> ON <object> FROM <user|role> to remove an object privilege. Since REPORTING_R was granted SELECT on HR.EMPLOYEES, the matching REVOKE statement removes that access.
REVOKE is the Oracle SQL command used to remove privileges that were previously granted to a user or role. For an object privilege such as SELECT on a table, the required syntax is REVOKE privilege ON object FROM grantee.
In this case, the privilege is SELECT, the object is HR.EMPLOYEES, and the grantee is the role REPORTING_R. So the correct statement is:
REVOKE SELECT ON hr.employees FROM reporting_r;
The key distinction is that object privileges use the ON object clause, while Oracle does not use DENY, and DROP is for removing objects, not privileges.
ON hr.employees before FROM reporting_r in a REVOKE statement.DROP removes database objects, not granted access rights.GRANT and REVOKE, not DENY, for privilege control.Topic: Retrieve Data by Using SQL SELECT Statements
In the query below, annual_pay is a column alias:
SELECT employee_id, salary * 12 AS annual_pay
FROM employees
Within the same query block, which clause can reference annual_pay by alias name?
Options:
A. GROUP BY
B. HAVING
C. WHERE
D. ORDER BY
Best answer: D
Explanation: In Oracle, a column alias created in the SELECT list is available for sorting in ORDER BY within the same query block. It is not available there for filtering or grouping clauses such as WHERE, GROUP BY, or HAVING.
The core rule is Oracle’s logical handling of a query block: the SELECT list alias is assigned when rows are projected, so it can be used for final output ordering. That is why an alias like annual_pay can be referenced in ORDER BY.
It cannot be used in these earlier clauses of the same query block:
WHEREGROUP BYHAVINGThose clauses are evaluated before the SELECT list alias is established. If you need to filter or group by that expression, use the full expression or place the query in an inline view and reference the alias from the outer query. The key takeaway is that, in the same query block, alias reuse is allowed for ordering, not for earlier clause processing.
WHERE confusion fails because filtering happens before the SELECT-list alias is assigned.GROUP BY confusion fails because grouping must use the underlying expression, not the alias in the same query block.HAVING confusion fails because it filters grouped rows before the alias is available there.ORDER BY usage is valid because sorting can reference the projected column alias.Topic: Use Single-Row Functions to Customize Output
A developer needs a query that returns one row from DUAL with FINAL_VALUE = 31.6.
Requirements:
15.762Which SQL statement meets the requirement?
Options:
A. SELECT ROUND(15.76 * 2, 1) AS final_value FROM dual
B. SELECT TRUNC(15.76, 1) * 2 AS final_value FROM dual
C. SELECT ROUND(15.76, 1) * 2 AS final_value FROM dual
D. SELECT ROUND(15.76, 0) * 2 AS final_value FROM dual
Best answer: C
Explanation: The correct query applies ROUND to the original number before the arithmetic operation. In Oracle, ROUND(15.76, 1) returns 15.8, and multiplying that by 2 gives 31.6.
This tests how a numeric single-row function behaves when nested inside an arithmetic expression. In Oracle, the placement of ROUND matters because it changes whether rounding happens before or after the math.
ROUND(15.76, 1) gives 15.815.8 * 2 gives 31.6If the multiplication is placed inside ROUND, Oracle evaluates 15.76 * 2 first, which is 31.52, and then rounds that to one decimal place as 31.5. Using TRUNC removes digits without rounding, and rounding to zero decimal places changes the starting value too much. The key takeaway is: when the requirement says “round first, then calculate,” the function must wrap the original value, not the full arithmetic expression.
15.76 * 2 first, so it returns 31.5, not 31.6.TRUNC cuts the value to 15.7 instead of rounding it, so the result becomes 31.4.15.76 to 16, so multiplying gives 32.Topic: Restrict and Sort Data
An analyst is troubleshooting this query because it returns some department 50 employees with salaries below 7,000:
SELECT employee_id, last_name, department_id, salary
FROM employees
WHERE department_id = 50 OR department_id = 80
AND salary >= 7000;
The requirement is to return only employees in departments 50 or 80 whose salary is at least 7,000, sorted by department_id ascending and salary descending. Which statement is the best fix?
Options:
A. SELECT employee_id, last_name, department_id, salary FROM employees WHERE department_id = 50 OR department_id = 80 AND salary >= 7000 ORDER BY department_id, salary DESC
B. SELECT employee_id, last_name, department_id, salary FROM employees WHERE department_id IN (50, 80) OR salary >= 7000 ORDER BY department_id, salary DESC
C. SELECT employee_id, last_name, department_id, salary FROM employees WHERE department_id IN (50, 80) AND salary >= 7000 ORDER BY department_id, salary DESC
D. SELECT employee_id, last_name, department_id, salary FROM employees WHERE department_id IN (50, 80) AND salary >= 7000 ORDER BY salary DESC, department_id
Best answer: C
Explanation: The fix is to make the department filter and salary filter both mandatory, then apply the required sort sequence. Using IN (50, 80) with AND salary >= 7000 satisfies the row restriction, and ORDER BY department_id, salary DESC matches the required ordering.
This issue is caused by logical-condition precedence in the original WHERE clause. In Oracle SQL, AND is evaluated before OR, so department_id = 50 OR department_id = 80 AND salary >= 7000 is treated as department_id = 50 OR (department_id = 80 AND salary >= 7000). That allows all department 50 rows through, even when salary is below 7,000.
A correct statement must do two things:
salary >= 7000 for those rowsdepartment_id ascending, then salary descendingUsing department_id IN (50, 80) AND salary >= 7000 ORDER BY department_id, salary DESC meets both the filtering and sorting requirements. The closest trap is a valid query that sorts by salary first, which changes the requested order.
department_id.department_id = 50 OR department_id = 80 AND salary >= 7000 still lets low-salary department 50 rows pass.OR salary >= 7000 returns rows outside departments 50 and 80 whenever salary is at least 7,000.Topic: Display Data from Multiple Tables
Consider this Oracle query:
SELECT e.employee_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE d.location_id = 1700;
The query no longer returns employees with no matching department. Why?
Options:
A. A LEFT OUTER JOIN preserves unmatched rows only when USING is used.
B. The department filter must be written in a HAVING clause.
C. Adding any filter after an outer join always causes a Cartesian product.
D. The WHERE filter on the outer-joined table removes the null-extended rows.
Best answer: D
Explanation: A left outer join preserves unmatched rows by returning NULL for columns from the right-side table. When you then filter that right-side table in the WHERE clause, those NULL-extended rows fail the condition and are removed.
The core concept is that an outer join preserves unmatched rows only until later filtering removes them. In this query, employees without a matching department are returned with NULL values for d.department_name and d.location_id. The condition d.location_id = 1700 is evaluated in the WHERE clause after the join result is formed, so rows where d.location_id is NULL do not satisfy the predicate and are discarded.
To keep unmatched employees while limiting matched departments to location 1700, place that filter in the ON clause of the outer join. That way, the join condition controls which department rows match, while still preserving employees that have no department row at all. The key takeaway is that a WHERE predicate on the null-supplying side can make an outer join behave like an inner join.
USING confusion fails because ON and USING are both valid join syntaxes; neither determines row preservation by itself.HAVING misuse fails because HAVING filters groups after aggregation, while this query is filtering joined rows.Topic: Manage Tables by Using DML Statements
A session executes this statement and does not issue COMMIT or ROLLBACK:
UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 50;
Which next statement will implicitly end the current transaction in Oracle Database?
Options:
A. DELETE FROM job_history WHERE employee_id = 200
B. CREATE TABLE bonus_audit (emp_id NUMBER)
C. SAVEPOINT before_bonus
D. SELECT COUNT(*) FROM employees
Best answer: B
Explanation: In Oracle Database, DDL statements such as CREATE TABLE implicitly commit the current transaction. The earlier UPDATE is therefore ended without an explicit COMMIT or ROLLBACK when that DDL statement runs.
The core rule is that Oracle ends a transaction explicitly with COMMIT or ROLLBACK, and it also ends the current transaction implicitly when certain DDL statements run. In this scenario, the session has uncommitted changes from the UPDATE, so issuing CREATE TABLE causes an implicit commit of those changes.
A few stable transaction rules help here:
CREATE, ALTER, and DROP implicitly commits.INSERT, UPDATE, and DELETE stays in the current transaction until committed or rolled back.SAVEPOINT marks a position inside a transaction; it does not end it.SELECT statement does not commit pending changes.So the DDL statement is the only choice that ends the current transaction implicitly.
DELETE adds work to the same uncommitted unit.SELECT can read without ending the transaction.Topic: Use Single-Row Functions to Customize Output
The ORDER_REF column stores values such as 2026-EMEA-5841. A report must display only the region code (EMEA) for each row, regardless of the region’s length. Which expression should be used in the SELECT list?
Options:
A. SUBSTR(order_ref, INSTR(order_ref, ‘-’), INSTR(order_ref, ‘-’, 1, 2) - INSTR(order_ref, ‘-’))
B. SUBSTR(order_ref, 1, INSTR(order_ref, ‘-’, 1, 2))
C. SUBSTR(order_ref, INSTR(order_ref, ‘-’, 1, 2) + 1)
D. SUBSTR(order_ref, INSTR(order_ref, ‘-’) + 1, INSTR(order_ref, ‘-’, 1, 2) - INSTR(order_ref, ‘-’) - 1)
Best answer: D
Explanation: To return text between two delimiters in Oracle SQL, use INSTR to locate each delimiter and SUBSTR to extract the characters between those positions. The correct expression begins after the first hyphen and uses the distance to the second hyphen as the substring length.
INSTR returns the position of a character occurrence, and SUBSTR returns characters starting at a given position for a given length. Here, the first hyphen marks where the region starts, and the second hyphen marks where it ends.
INSTR(order_ref, '-')INSTR(order_ref, '-', 1, 2)+ 11That extracts only the middle segment, such as EMEA, without including either hyphen or the trailing number.
1 returns the beginning of the string through the second hyphen, so it includes unwanted characters.Topic: Use Set Operators
Two tables, current_staff and former_staff, both contain first_name and last_name. You need one query that:
full_nameWhich statement is the best solution?
Options:
- B. ```sql
SELECT first_name, last_name AS full_name
FROM current_staff
UNION
SELECT first_name, last_name
FROM former_staff
ORDER BY full_name
- D. ```sql
SELECT first_name || ' ' || last_name
FROM current_staff
UNION
SELECT first_name || ' ' || last_name AS full_name
FROM former_staff
ORDER BY full_name
Best answer: C
Explanation: In an Oracle compound query, the output column names come from the first SELECT. When the result column is an expression and you want to sort by its displayed name, define the alias in the first SELECT; UNION is also required here because the result must be distinct.
This tests two set-operator rules at once. In a UNION or UNION ALL query, Oracle derives the final result-column names from the first SELECT. So if the final ORDER BY must use an alias for an expression such as first_name || ' ' || last_name, that alias must be defined in the first SELECT.
Here, the query must return one column named full_name, sort by that name, and eliminate duplicates. The correct statement does all three: it builds the full-name expression, assigns full_name in the first SELECT, and uses UNION to remove duplicate names across both tables.
A later alias does not rename the compound-query column, and UNION ALL would keep duplicates.
SELECT does not define the compound query’s output column name.UNION ALL preserves duplicate full names instead of removing them.first_name and last_name separately does not return one column named full_name.Topic: Use Subqueries to Solve Queries
Which statement correctly describes a single-row subquery used in a WHERE clause in Oracle SQL?
Options:
A. It returns one row and is compared with operators such as =, >, or <.
B. It must reference a column from the outer query to be valid.
C. It returns multiple rows and must be compared with IN or ANY.
D. It can be used only in the HAVING clause, not in the WHERE clause.
Best answer: A
Explanation: A single-row subquery returns exactly one row to the outer query. Because only one value is expected, Oracle uses single-value comparison operators such as =, <, >, <=, or >= in the WHERE clause.
The core idea is that a single-row subquery produces one row, so the outer query can compare a column to that one returned value. In Oracle SQL, this is the correct subquery type when the condition expects a single comparison value in the WHERE clause.
For example, a query can compare an employee’s salary to the salary returned for one specific employee or one aggregate result. If a subquery may return more than one row, operators such as IN, ANY, or ALL are needed instead. A correlated subquery is different: it depends on values from the outer query and may run once per outer row.
The key takeaway is to match the subquery result cardinality to the comparison operator.
IN and ANY are typically for multi-row subqueries, not single-row comparisons.WHERE, not only in HAVING.Topic: Manage Tables and Relationships by Using DDL
DEPARTMENTS(department_id) already has a primary key. You are creating EMPLOYEES(employee_id, department_id) and want Oracle to enforce that any non-NULL value in EMPLOYEES.department_id matches an existing DEPARTMENTS.department_id. Which constraint definition does this?
Options:
A. CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id)
B. CONSTRAINT emp_dept_fk PRIMARY KEY (department_id)
C. CONSTRAINT emp_dept_fk UNIQUE (department_id)
D. CONSTRAINT emp_dept_fk CHECK (department_id > 0)
Best answer: A
Explanation: A foreign key enforces referential integrity by requiring child-table values to exist in the referenced parent key. Here, EMPLOYEES.department_id must reference DEPARTMENTS.department_id, so the definition using FOREIGN KEY ... REFERENCES is the correct choice.
The core concept is referential integrity. In Oracle, a foreign key is defined on the child table and points to a parent table column that is typically a primary key or unique key. That tells Oracle to allow only values in EMPLOYEES.department_id that already exist in DEPARTMENTS.department_id, unless the child value is NULL.
A primary key or unique constraint only controls uniqueness within the same table; it does not validate values against another table. A check constraint can validate a condition on the local column value, but it cannot enforce that the value exists in a different table. For parent-child table relationships, FOREIGN KEY ... REFERENCES ... is the correct DDL mechanism.
The key takeaway is that relationship integrity between tables is enforced with a foreign key, not with primary, unique, or check constraints alone.
department_id a primary key would force uniqueness in EMPLOYEES, not validate it against DEPARTMENTS.Topic: Restrict and Sort Data
A SQL*Plus-style report script must ask the user for a department number at runtime.
ACCEPT p_deptno PROMPT 'Enter department ID: '
SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id = &p_deptno
ORDER BY last_name;
What happens when this script runs?
Options:
A. It fails because &p_deptno must be written as :p_deptno.
B. It prompts twice because ACCEPT and &p_deptno request separate inputs.
C. It fails unless &p_deptno is enclosed in single quotes.
D. It prompts once, stores the input in p_deptno, and substitutes it in the WHERE clause.
Best answer: D
Explanation: ACCEPT captures a runtime value into a SQL*Plus substitution variable, and &variable inserts that value into the statement text. Because p_deptno is already defined by ACCEPT, the user is prompted only once.
This script uses a SQLPlus substitution variable workflow. ACCEPT p_deptno asks the user for input and stores it in a user variable named p_deptno. When SQLPlus processes &p_deptno, it substitutes the stored text into the SQL statement before Oracle executes the query.
For a numeric department ID such as 10, the statement effectively becomes:
SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id = 10
ORDER BY last_name;
The key point is that ACCEPT captures the runtime value first, so the later &p_deptno reference does not cause a second prompt. Bind variables use :name, which is a different feature.
ACCEPT.: is for bind variables, not SQL*Plus substitution variables.Topic: Use Single-Row Functions to Customize Output
Which statement correctly distinguishes Oracle date arithmetic from date formatting?
Options:
A. TO_CHAR(start_date, 'DD') + 7 adds seven days to start_date.
B. You must apply TO_CHAR before subtracting two DATE values.
C. SYSDATE - start_date returns the difference in days as a NUMBER.
D. TO_CHAR(start_date, 'DD-MON-YYYY') returns a DATE value in a different format.
Best answer: C
Explanation: In Oracle, subtracting one DATE from another performs date arithmetic and returns a numeric day difference. By contrast, TO_CHAR converts a date to character data for display, so it formats output rather than preserving a DATE datatype.
The core distinction is datatype and purpose. Oracle date arithmetic works directly on DATE values. When you subtract two DATE expressions, Oracle returns a NUMBER representing the difference in days, including fractional days if time components exist. SYSDATE is a DATE, so expressions like SYSDATE - start_date are valid date arithmetic.
TO_CHAR, however, is a formatting function. It converts a DATE to a VARCHAR2 using the format model you specify, such as 'DD-MON-YYYY'. Once formatted with TO_CHAR, the result is character data, not a DATE, so it is no longer the same kind of value used for normal date arithmetic.
The key takeaway is: use direct DATE expressions for arithmetic, and use TO_CHAR only when you need display formatting.
TO_CHAR changes a DATE into character data, not another DATE.DATE values directly without formatting them first.TO_CHAR(start_date, 'DD') extracts a character representation of the day of month, not the full date.Topic: Restrict and Sort Data
A developer must return employees whose salary is from 5,000 through 7,000, including both values.
Exhibit:
SELECT employee_id, salary
FROM employees
WHERE salary > 5000
AND salary < 7000;
Which predicate should replace the current WHERE condition to meet the requirement?
Options:
A. salary BETWEEN 5000 AND 7000
B. salary BETWEEN 7000 AND 5000
C. salary >= 5000 OR salary <= 7000
D. salary > 5000 AND salary < 7000
Best answer: A
Explanation: In Oracle SQL, BETWEEN includes both boundary values. So a predicate using BETWEEN 5000 AND 7000 satisfies a requirement that explicitly includes 5,000 and 7,000.
The key concept is that Oracle BETWEEN is inclusive at both ends. If the requirement says values from 5,000 through 7,000 must be included, the predicate must accept rows where salary = 5000, salary = 7000, and any value between them.
BETWEEN 5000 AND 7000 is equivalent to using salary >= 5000 AND salary <= 7000. By contrast, strict comparison operators with > and < exclude the endpoints. Also, using OR instead of AND makes the condition far too broad, and reversing the lower and upper bounds does not create the intended range.
When both boundary values must be included, BETWEEN is the clearest Oracle SQL choice.
OR is too broad because almost every salary will be either at least 5,000 or at most 7,000.> and < fails because it excludes rows where salary is exactly 5,000 or exactly 7,000.Topic: Manage Tables and Relationships by Using DDL
A developer runs this statement and Oracle rejects it because one column definition uses a data type with an inconsistent length declaration:
CREATE TABLE shipments (
shipment_id NUMBER(8),
shipped_on DATE(7),
tracking_code VARCHAR2(30),
status CHAR(1)
);
Which change fixes the statement while preserving the intended data type for shipped_on?
Options:
A. Change shipped_on DATE(7) to shipped_on DATE
B. Change status CHAR(1) to status CHAR(0)
C. Change tracking_code VARCHAR2(30) to tracking_code VARCHAR2
D. Change shipped_on DATE(7) to shipped_on TIMESTAMP(7)
Best answer: A
Explanation: In Oracle, DATE is declared without a length or precision. The fix is to remove (7) from shipped_on so the column remains a DATE column and the table definition becomes valid.
This question tests valid Oracle column definitions in CREATE TABLE. A DATE column cannot be declared with a length, precision, or scale, so DATE(7) is invalid. To keep the same data type, declare the column simply as DATE.
By contrast, character types such as CHAR and VARCHAR2 use a size declaration in column definitions. VARCHAR2(30) is valid, while omitting the size for VARCHAR2 is not valid in this context. CHAR(1) is also valid, but CHAR(0) is not because the size must be at least 1.
The key takeaway is to match each Oracle data type with the kind of length declaration it actually supports.
TIMESTAMP(7) uses valid syntax, but it changes the data type instead of correcting the invalid DATE definition.VARCHAR2(30) makes that column invalid, because VARCHAR2 requires a length in a table definition.CHAR(0) is invalid because a CHAR column size cannot be 0.Topic: Manage Tables and Relationships by Using DDL
Examine the exhibit:
CREATE TABLE departments (
dept_id NUMBER CONSTRAINT departments_pk PRIMARY KEY
);
CREATE TABLE employees (
emp_id NUMBER CONSTRAINT employees_pk PRIMARY KEY,
dept_id NUMBER CONSTRAINT employees_dept_fk
REFERENCES departments(dept_id)
);
-- Existing rows
-- DEPARTMENTS: 10, 20
-- EMPLOYEES: (1,10), (2,10), (3,20)
DELETE FROM departments
WHERE dept_id = 10;
Oracle raises an error when the DELETE statement runs. What is the best explanation?
Options:
A. The primary key on DEPARTMENTS.DEPT_ID prevents deleting any parent row.
B. The foreign key requires EMPLOYEES.DEPT_ID to be set to NULL automatically.
C. DEPARTMENTS.DEPT_ID is referenced by child rows in EMPLOYEES.
D. The delete fails because EMPLOYEES.DEPT_ID must be unique.
Best answer: C
Explanation: The statement fails because department 10 is still referenced by rows in the child table. A parent row cannot be deleted while matching foreign key rows exist unless the child rows are removed first or the relationship was defined with ON DELETE CASCADE.
This is a referential integrity violation. EMPLOYEES.DEPT_ID is a foreign key that references DEPARTMENTS.DEPT_ID, so each employee row must point to an existing department row. Because employees (1,10) and (2,10) still reference department 10, Oracle rejects the attempt to delete that parent row.
To make the delete succeed, one of these must be true:
ON DELETE CASCADEA primary key identifies rows in the parent table, but it is the foreign key in the child table that blocks this delete.
NULL automatically unless the design explicitly supports a different action.EMPLOYEES.DEPT_ID is unique; many employees can belong to the same department.Topic: Manage Tables by Using DML Statements
Consider this target data, source data, and MERGE statement:
BONUSES target
EMP_ID BONUS_AMT
101 500
102 600
104 700
SALES_BONUS source
EMP_ID BONUS_AMT
102 650
103 400
104 750
105 450
MERGE INTO bonuses b
USING sales_bonus s
ON (b.emp_id = s.emp_id)
WHEN MATCHED THEN
UPDATE SET b.bonus_amt = s.bonus_amt
WHEN NOT MATCHED THEN
INSERT (emp_id, bonus_amt)
VALUES (s.emp_id, s.bonus_amt);
Which rows are updated, and which rows are inserted?
Options:
A. Update 101 and 102; insert 104 and 105.
B. Update 102, 103, 104, and 105; insert none.
C. Update 102 and 104; insert 101 and 103.
D. Update 102 and 104; insert 103 and 105.
Best answer: D
Explanation: In a MERGE, the ON condition decides whether each source row is treated as matched or not matched. Here, source EMP_ID values 102 and 104 match target rows, so they are updated, while 103 and 105 do not match and are inserted.
The key concept in MERGE is that Oracle compares each source row to the target using the ON condition. If a source row finds a target row with the same EMP_ID, Oracle processes the WHEN MATCHED action; otherwise it processes the WHEN NOT MATCHED action.
In this statement, source rows 102 and 104 match existing rows in BONUSES, so those rows are updated. Source rows 103 and 105 have no matching target row, so they are inserted. Target row 101 is unchanged because there is no source row with EMP_ID 101.
A useful takeaway is that unmatched source rows are candidates for insert, while target-only rows are not inserted or updated unless separately matched by a source row.
WHEN NOT MATCHED insert branch.Topic: Restrict and Sort Data
The orders table has a order_total column of data type NUMBER. Which predicate in a WHERE clause returns rows whose order_total is from 1,000 through 5,000, including both boundary values?
Options:
A. order_total >= 1000 OR order_total <= 5000
B. order_total BETWEEN 5000 AND 1000
C. order_total > 1000 AND order_total < 5000
D. order_total BETWEEN 1000 AND 5000
Best answer: D
Explanation: In Oracle SQL, BETWEEN low AND high includes both endpoints. For a range that must include 1,000 and 5,000, the correct predicate is order_total BETWEEN 1000 AND 5000.
The core rule is that Oracle evaluates expr BETWEEN low AND high as inclusive on both sides: expr >= low AND expr <= high. That makes it the correct choice when the requirement explicitly says to include both boundary values.
For this case, Oracle treats the predicate like this:
The closest trap is using > and <, which creates an exclusive range and removes the endpoints.
> and < excludes exactly 1,000 and 5,000.OR is too broad and matches almost every value.BETWEEN 5000 AND 1000 does not define the required low-to-high inclusive range.Use the Oracle 1Z0-071 Practice Test page for the full IT Mastery route, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.
Try Oracle 1Z0-071 on Web View Oracle 1Z0-071 Practice Test
Read the Oracle 1Z0-071 Cheat Sheet on Tech Exam Lexicon for concept review before another timed run.