Oracle 1Z0-071: Use Subqueries to Solve Queries

Topic snapshot

Sample questions

These questions are original IT Mastery practice items aligned to this topic area. They are designed for self-assessment and are not official exam questions.

Question 1

Topic: Use Subqueries to Solve Queries

You need a query against employees that:

• returns employee_id and last_name
• returns only employees whose salary is greater than the average salary in their own department
• excludes employees whose salary is exactly equal to that department average

Which SQL statement best meets the requirement?

Options:

• A. SELECT e.employee_id, e.last_name FROM employees e JOIN employees e2 ON e.department_id = e2.department_id WHERE e.salary > e2.salary;

• B. SELECT e.employee_id, e.last_name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees);

• C. SELECT e.employee_id, e.last_name FROM employees e WHERE e.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e.department_id);

• D. SELECT e.employee_id, e.last_name FROM employees e WHERE e.salary > ANY (SELECT AVG(e2.salary) FROM employees e2 GROUP BY e2.department_id);

Best answer: C

Explanation: The best query is the one that uses a correlated subquery tied to the current outer row through e.department_id. That makes Oracle compute the average salary for each employee’s own department before applying the > comparison.

A correlated subquery references a column from the outer query and is evaluated for each row processed by that outer query. Here, e.department_id from the outer employees e row is passed into the subquery, so AVG(e2.salary) is calculated only for that employee’s department. The outer row is returned only when its salary is greater than that department average.

This also correctly excludes employees whose salary equals the department average, because the requirement uses > rather than >=. The key takeaway is that the subquery must be linked to the current outer row; otherwise, the comparison is not department-specific.

• Global average fails because it compares each employee to one companywide average instead of the average for that employee’s department.
• ANY with grouped averages fails because being greater than at least one department average is not the same as being greater than your own department average.
• Self-join comparison fails because it finds employees who earn more than some coworker, and it can also return duplicate rows.

Question 2

Topic: Use Subqueries to Solve Queries

A developer runs this query to list employees who work in departments at location 1700:

SELECT employee_id, last_name
FROM employees
WHERE department_id = (
  SELECT department_id
  FROM departments
  WHERE location_id = 1700
);

The statement raises ORA-01427: single-row subquery returns more than one row. What is the best fix?

Options:

• A. Wrap the subquery with MAX(department_id).

• B. Add DISTINCT inside the subquery.

• C. Add ORDER BY department_id inside the subquery.

• D. Replace = with IN in the outer query.

Best answer: D

Explanation: ORA-01427 occurs when a comparison like = expects one value but the subquery returns more than one row. Because one location can have several departments, this is a multi-row subquery and should be matched with IN for the stated requirement.

In Oracle SQL, the operator used with a subquery depends on how many rows the subquery can legitimately return. A single-row subquery can be compared with operators such as =, <, or >. A multi-row subquery must use a multi-row operator such as IN, ANY, or ALL.

Here, filtering departments by location_id = 1700 can return several department_id values. That makes the inner query a multi-row subquery, so department_id = (subquery) is invalid when more than one row is returned and Oracle raises ORA-01427.

For the requirement “employees in any department at location 1700,” the correct fix is to use IN, not to force the subquery down to one row artificially.

• DISTINCT misconception removes duplicate values only; it does not guarantee that just one department ID will be returned.
• Ordering misconception ORDER BY changes display order, not the number of rows returned by the subquery.
• Aggregate shortcut using MAX(department_id) forces one value and changes the meaning from matching any qualifying department to matching one chosen department.

Question 3

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. Add DISTINCT to the outer SELECT list.

• B. Replace the subquery with a cross join to departments.

• C. Remove the outer reference so the subquery is noncorrelated.

• D. Add GROUP BY department_id inside the subquery.

Best answer: C

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.
• Join confusion a cross join to departments is unrelated to computing one overall salary average and can create extra rows.

Question 4

Topic: Use Subqueries to Solve Queries

A developer is troubleshooting a report query that was copied from a multi-row subquery example. The requirement is to list employees whose salary is greater than the average salary of department 50.

WHERE salary > ANY (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = 50
)

Because the subquery returns a single value, which is the best fix?

Options:

• A. Replace > ANY with >.

• B. Add GROUP BY department_id to the subquery.

• C. Replace > ANY with IN.

• D. Rewrite the predicate with EXISTS.

Best answer: A

Explanation: The subquery uses AVG with no GROUP BY, so it returns exactly one row and one value. For a single-row subquery, Oracle SQL uses single-row comparison operators such as =, >, <, >=, <=, or <>, so > is the best match here.

This is a single-row subquery because AVG(salary) is an aggregate and there is no GROUP BY clause. That means the subquery produces one numeric value: the average salary for department 50. When the outer query compares a column to one returned value, the predicate should use a single-row comparison operator such as >.

Operators such as IN, ANY, and ALL are associated with set-style comparisons and are mainly used when a subquery can return multiple rows. In this case, the requirement is simply “salary greater than the department average,” so salary > (subquery) expresses the logic directly and correctly. If the subquery were changed to return multiple rows, then a multi-row operator would be needed instead.

• IN changes the test because it checks equality against a set of values, not whether salary is greater than one returned value.
• Adding GROUP BY hurts here because it can turn the subquery into a multi-row result, which no longer matches the stated requirement.
• EXISTS is the wrong tool because it tests whether rows are returned, not whether one numeric value is greater than another.

Question 5

Topic: Use Subqueries to Solve Queries

Examine the exhibit:

SELECT employee_name
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
)
ORDER BY employee_name;

employees contains these rows:

Which result does the query return?

Options:

• A. Cara only

• B. Ben, Cara, Eli

• C. Cara, Eli

• D. Cara, Eli, Fay

Best answer: C

Explanation: This is a correlated subquery because the inner query uses e.department_id from the outer row. For each employee, Oracle calculates the average salary in that employee’s department and keeps only rows whose salary is greater than that average.

The inner query runs once for each outer-row employee and uses that employee’s department_id to find the matching departmental average.

• Department 10 average = (5000 + 7000 + 9000) / 3 = 7000; only Cara is greater than 7000.
• Department 20 average = (4000 + 6000) / 2 = 5000; only Eli is greater than 5000.
• Department 30 average = 8000; Fay is not greater than 8000.

So the outer query returns the employees whose salaries exceed their own department average: Cara and Eli. The closest trap is treating > like >=, which would incorrectly include employees equal to the average.

• Including Ben fails because Ben’s salary equals the department 10 average, and the condition is > not >=.
• Only Cara fails because Eli is also above the average salary in department 20.
• Including Fay fails because Fay’s salary equals the department 30 average, so the condition is false.

Question 6

Topic: Use Subqueries to Solve Queries

A developer must populate DEPARTMENTS.MANAGER_LAST_NAME from the related row in EMPLOYEES. Only departments with a matching manager row should be updated; departments without a match must remain unchanged.

Exhibit:

DESC departments
 Name                 Null? Type
 -------------------- ----- ------------
 DEPARTMENT_ID              NUMBER
 DEPARTMENT_NAME            VARCHAR2(30)
 MANAGER_ID                 NUMBER
 MANAGER_LAST_NAME          VARCHAR2(25)

DESC employees
 Name                 Null? Type
 -------------------- ----- ------------
 EMPLOYEE_ID                NUMBER
 LAST_NAME                  VARCHAR2(25)

Which statement correctly meets the requirement?

Options:

• A. UPDATE departments d JOIN employees e ON (e.employee_id = d.manager_id) SET d.manager_last_name = e.last_name;

• B. UPDATE departments d SET manager_last_name = (SELECT e.last_name FROM employees e WHERE e.employee_id = d.manager_id);

• C. UPDATE departments d SET manager_last_name = (SELECT last_name FROM employees);

• D. UPDATE departments d SET manager_last_name = (SELECT e.last_name FROM employees e WHERE e.employee_id = d.manager_id) WHERE EXISTS (SELECT 1 FROM employees e WHERE e.employee_id = d.manager_id);

Best answer: D

Explanation: A correlated subquery in an UPDATE can derive a new value row by row from a related table. Here, the extra EXISTS clause is needed because rows without a matching employee must stay unchanged rather than having MANAGER_LAST_NAME set to NULL.

The core concept is a correlated subquery in the SET clause: for each row in DEPARTMENTS, Oracle evaluates a subquery that uses that row’s MANAGER_ID to find the related EMPLOYEES.LAST_NAME. That correctly derives the new value from related data.

When the requirement says unmatched departments must remain unchanged, the statement should also restrict the updated rows. Without that filter, Oracle still updates every department row, and any row with no matching employee receives NULL from the subquery result.

• SET subquery: returns the manager’s last name for the current department row
• EXISTS filter: updates only departments that actually have a matching employee row

A join-style UPDATE ... JOIN syntax is not Oracle SQL, and an uncorrelated subquery from EMPLOYEES would not return a single value per department.

• Missing row filter fails because unmatched departments would be updated and MANAGER_LAST_NAME could become NULL.
• Uncorrelated subquery fails because selecting LAST_NAME from all employees does not return one value per department row.
• Join syntax confusion fails because Oracle does not support MySQL-style UPDATE ... JOIN syntax.

Question 7

Topic: Use Subqueries to Solve Queries

Which WHERE clause correctly returns employees whose salary is greater than every salary returned by this multi-row subquery?

Assume salary is NOT NULL.

SELECT salary
FROM employees
WHERE department_id = 50

Options:

• A. salary > ALL (SELECT salary FROM employees WHERE department_id = 50)

• B. salary > ANY (SELECT salary FROM employees WHERE department_id = 50)

• C. salary IN (SELECT salary FROM employees WHERE department_id = 50)

• D. salary = ALL (SELECT salary FROM employees WHERE department_id = 50)

Best answer: A

Explanation: Use ALL when one value must satisfy the comparison against every row returned by a multi-row subquery. Here, salary > ALL (...) means the employee salary must be greater than each salary from department 50.

ANY, ALL, and IN have different meanings with multi-row subqueries. For this requirement, the comparison must be true against every value returned, so ALL is the correct operator.

• > ALL means greater than every returned value.
• > ANY means greater than at least one returned value.
• IN checks for equality to one of the returned values.
• = ALL would require equality to every returned value.

So salary > ALL (subquery) is the proper Oracle SQL form when you need rows whose value exceeds the full result set from the subquery. The closest distractor is > ANY, but that is much less restrictive.

• > ANY is weaker because being greater than just one returned salary does not mean being greater than all of them.
• IN tests membership and would find matching salaries, not salaries higher than the entire subquery result.
• = ALL is too restrictive because it would require one salary to equal every returned salary.

Question 8

Topic: Use Subqueries to Solve Queries

A developer wants to list employees whose department row exists in DEPARTMENTS, but this query returns no rows:

SELECT e.employee_id, e.last_name
FROM employees e
WHERE EXISTS (
  SELECT 1
  FROM departments d
  WHERE d.location_id = e.department_id
);

In this schema, employees.department_id references departments.department_id, and no location_id values match any employee department_id values. Which change best fixes the problem?

Options:

• A. Remove the correlation from the subquery

• B. Correlate on d.department_id = e.department_id

• C. Add DISTINCT inside the subquery

• D. Replace EXISTS with ANY

Best answer: B

Explanation: The query returns no rows because the correlated predicate uses unrelated columns. In a correlated subquery, the inner query must be linked to the outer row through the correct relationship, which here is department_id to department_id.

This is a correlated subquery troubleshooting case. EXISTS itself is not the problem; it simply checks whether the inner query finds at least one matching row for each outer-row employee. Here, the match condition is wrong because d.location_id = e.department_id compares unrelated columns.

Since employees.department_id is the foreign key to departments.department_id, the correlation must follow that relationship:

WHERE EXISTS (
  SELECT 1
  FROM departments d
  WHERE d.department_id = e.department_id
)

With the wrong join relationship, the subquery finds no matching department row for any employee, so EXISTS is false for every outer row. The key takeaway is to verify the correlated columns before changing subquery operators or adding duplicate-control logic.

• Changing the operator to ANY does not fix a bad relationship between columns.
• Adding DISTINCT only removes duplicates; it cannot create missing matches.
• Removing correlation breaks the row-by-row relationship that a correlated subquery needs here.

Question 9

Topic: Use Subqueries to Solve Queries

Examine the exhibit:

ALTER TABLE departments
  ADD CONSTRAINT dept_name_uk UNIQUE (department_name);

SELECT employee_id, last_name
FROM employees
WHERE department_id ___ (
  SELECT department_id
  FROM departments
  WHERE department_name = 'Sales'
);

Because department_name is unique, the subquery can return only one department_id. Which operator should replace the blank?

Options:

• A. =

• B. > ANY

• C. <> ALL

• D. IN

Best answer: A

Explanation: A subquery guaranteed to return one value should be compared with a single-row comparison operator. Here the query needs employees whose department_id exactly matches the one department_id returned for Sales, so = fits the requirement.

This is a single-row subquery case. The UNIQUE constraint on department_name means the predicate department_name = 'Sales' can identify at most one row in departments, so the subquery produces one department_id value to compare against employees.department_id.

For an exact one-value comparison, Oracle uses single-row comparison operators such as =, >, >=, <, <=, and <>. In this statement, the business meaning is equality: return employees whose department matches the single department returned by the subquery.

IN, ANY, and ALL are generally chosen for multi-row or group-comparison logic, even though IN can still work if only one row happens to be returned.

• IN is broader than needed; it is usually used when a subquery may return multiple values.
• > ANY changes the logic to a greater-than comparison, not an exact department match.
• <> ALL also changes the logic by excluding the returned value instead of matching it.

Question 10

Topic: Use Subqueries to Solve Queries

Table EMPLOYEES has columns EMPLOYEE_ID, DEPARTMENT_ID, and SALARY. Which Oracle SQL statement correctly updates only those employees whose SALARY is less than the average SALARY of their own DEPARTMENT_ID?

Options:

• A. UPDATE employees e SET salary = salary * 1.05 WHERE e.salary < (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e.department_id)

• B. UPDATE employees SET salary = salary * 1.05 WHERE salary < (SELECT AVG(salary) FROM employees)

• C. UPDATE employees SET salary = salary * 1.05 WHERE salary < AVG(salary) GROUP BY department_id

• D. UPDATE employees e SET salary = salary * 1.05 FROM employees e2 WHERE e.salary < AVG(e2.salary) AND e2.department_id = e.department_id

Best answer: A

Explanation: The valid statement uses a correlated subquery in the WHERE clause. It compares each employee’s salary with the average salary for that employee’s own department before applying the update.

A correlated subquery references a value from the current row being processed by the outer UPDATE. In this case, e.department_id from the target row is used inside the subquery, so Oracle calculates the average salary for that specific department and checks whether the employee’s salary is below it. Only rows that satisfy that condition are updated.

This is the standard Oracle pattern for subquery-based row qualification in UPDATE. By contrast, an aggregate like AVG cannot be placed directly in the WHERE clause without a subquery, and Oracle does not use UPDATE ... FROM syntax in this form. The key distinction is whether the subquery returns the correct value for each current row.

• The option using AVG(salary) directly in WHERE fails because aggregates must be produced in a subquery here.
• The option using FROM employees e2 is not valid Oracle UPDATE ... FROM syntax.
• The option comparing to the overall average is valid SQL, but it applies a company-wide average instead of each employee’s department average.

Continue with full practice

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

Related focused pages

• Free Oracle 1Z0-071 Full-Length Practice Exam
• Relational Database Concepts
• SQL SELECT
• Restrict and Sort Data
• Single-Row Functions
• Conversion and CASE
• Group Functions
• Display Data from Multiple Tables
• Use Set Operators
• Manage Tables by Using DML Statements
• Indexes and Sequences
• DDL Tables and Relationships
• Manage Views
• Control User Access
• Data Dictionary Views
• Manage Data in Different Time Zones

Free review resource

Read the Oracle 1Z0-071 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.