Oracle SQL Advanced Subqueries: Scalar, WITH Clause and DML Subqueries — 1Z0-071 Exam Guide

1Z0-071 Exam Prep Chapters 16 & 17 Volume 4

Advanced subqueries build on the basic subquery patterns from Chapter 8. Chapters 16 and 17 go deeper — scalar subqueries in unexpected places, the WITH clause for reusable query blocks, and subqueries used as the target of INSERT, UPDATE, and DELETE statements.

These are not the most common exam questions, but they do appear — and candidates who haven't studied them lose marks that are relatively easy to pick up.

In this post

1. Scalar subqueries — one value in unexpected places
2. Scalar subquery in SELECT list
3. Scalar subquery in ORDER BY
4. Multiple-column subqueries
5. Pairwise vs non-pairwise comparison
6. The WITH clause — named query blocks
7. Subqueries as DML targets — UPDATE with subquery
8. INSERT into a subquery target
9. DELETE using a subquery
10. Quick reference
11. Practice questions

01 — Scalar subqueries — one value in unexpected places

A scalar subquery returns exactly one row and one column — a single value. What makes it useful is where it can be placed. Beyond the WHERE clause, scalar subqueries can appear in the SELECT list, the ORDER BY clause, the HAVING clause, and even the FROM clause of another query.

-- Standard scalar subquery in WHERE (covered in Post 03)
SELECT last_name, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees);

-- Scalar subquery in HAVING
SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id
HAVING   AVG(salary) > (SELECT AVG(salary) FROM employees);

ORA-01427 still applies: A scalar subquery must return exactly one row. If it returns more than one row, Oracle raises ORA-01427 regardless of where the subquery appears — in SELECT, ORDER BY, or anywhere else. If it returns zero rows, it returns NULL.

02 — Scalar subquery in SELECT list

A scalar subquery in the SELECT list returns one value per outer row. Oracle executes the inner query once for each row of the outer query — making it behave like a correlated subquery when it references the outer table.

-- Show each employee's salary vs their department average
SELECT e.last_name,
       e.salary,
       e.department_id,
       (SELECT ROUND(AVG(salary), 0)
        FROM   employees i
        WHERE  i.department_id = e.department_id) AS dept_avg
FROM   employees e;
-- For each employee row, the inner query calculates the
-- average salary for that employee's specific department
-- This is a correlated scalar subquery

Non-correlated scalar subquery in SELECT: If the scalar subquery in the SELECT list does not reference the outer query, Oracle runs it once and reuses the same value for every row — not once per row. This is more efficient. The same average-company-salary value appears on every output row.

-- Non-correlated: company average runs once, same value on every row
SELECT last_name,
       salary,
       (SELECT AVG(salary) FROM employees) AS company_avg
FROM   employees;

03 — Scalar subquery in ORDER BY

A scalar subquery can be used in ORDER BY to sort results based on a value that is not in the main query's SELECT list or base table.

-- Sort employees by their department's average salary
-- The department average is not in the SELECT list
SELECT   e.last_name, e.salary, e.department_id
FROM     employees e
ORDER BY (SELECT AVG(salary)
           FROM   employees d
           WHERE  d.department_id = e.department_id) DESC;
-- Employees in the highest-paid department appear first

04 — Multiple-column subqueries

A subquery can return multiple columns and multiple rows. When used with a multiple-column comparison in the WHERE clause, Oracle compares all the columns together as a unit.

-- Find employees who have the same job and salary as employee 141 or 143
SELECT last_name, job_id, salary
FROM   employees
WHERE  (job_id, salary) IN
       (SELECT job_id, salary
        FROM   employees
        WHERE  employee_id IN (141, 143));
-- Both columns must match — job_id AND salary together
-- This is a pairwise comparison

Column count must match: The number of columns on the left side of IN must match the number of columns returned by the subquery. (job_id, salary) IN (SELECT job_id FROM ...) raises an error — one column on the left, one in the subquery is fine, but mismatches fail.

05 — Pairwise vs non-pairwise comparison

The exam distinguishes between pairwise and non-pairwise multiple-column subquery comparisons. The two approaches can produce different results.

-- PAIRWISE: columns must match as a combined pair
SELECT last_name, department_id, salary
FROM   employees
WHERE  (department_id, salary) IN
       (SELECT department_id, MIN(salary)
        FROM   employees
        GROUP BY department_id);
-- Returns employees who earn the minimum salary IN THEIR OWN department
-- dept_id AND salary must match the same row from the subquery

-- NON-PAIRWISE: each column compared independently
SELECT last_name, department_id, salary
FROM   employees
WHERE  department_id IN
           (SELECT department_id FROM employees GROUP BY department_id)
  AND  salary IN
           (SELECT MIN(salary) FROM employees GROUP BY department_id);
-- Returns employees whose salary equals ANY department's minimum
-- NOT necessarily the minimum of their own department
-- Can return more rows than the pairwise version

Pairwise vs non-pairwise gives different results: Pairwise requires both columns to match in the same subquery row. Non-pairwise checks each column against its own subquery independently — a row passes if department_id matches any subquery row AND salary matches any subquery row, even different rows. The exam tests whether you know which approach correctly answers the stated question.

06 — The WITH clause — named query blocks

The WITH clause (also called a Common Table Expression or CTE) lets you define a named subquery at the top of a statement and reference it by name later. It is particularly useful when the same subquery would otherwise need to be written multiple times.

-- WITH clause defines a named query block
WITH dept_costs AS (
    SELECT   department_id,
             SUM(salary) AS dept_total
    FROM     employees
    GROUP BY department_id
),
avg_cost AS (
    SELECT AVG(dept_total) AS avg_total
    FROM   dept_costs
)
SELECT d.department_id, d.dept_total
FROM   dept_costs d, avg_cost a
WHERE  d.dept_total > a.avg_total
ORDER BY d.dept_total DESC;
-- dept_costs is defined once but could be referenced multiple times
-- avg_cost references dept_costs inside the WITH block itself

Feature	WITH clause	Inline view (subquery in FROM)
Defined where?	Once at the top of the statement	Inline inside the FROM clause
Can be referenced multiple times?	Yes — by name anywhere in the main query	No — each inline view is used once
Readability	Higher for complex queries	Can get deeply nested
Stored permanently?	No — exists only for that statement	No

WITH clause syntax: Multiple named query blocks are separated by commas inside one WITH keyword. Each block can reference previously defined blocks within the same WITH clause. The main SELECT statement follows immediately after the last closing parenthesis.

07 — Subqueries as DML targets — UPDATE with subquery

Chapter 17 covers using subqueries not just as filters inside DML but as the actual target of DML — the table that UPDATE or INSERT operates on. This is a more advanced pattern than the subquery-in-WHERE cases from Chapter 8.

-- UPDATE using a subquery in the FROM clause as target (inline view update)
-- This updates the underlying base table through a subquery
UPDATE (SELECT e.salary, e.department_id
        FROM   employees e
        JOIN   departments d
          ON   e.department_id = d.department_id
        WHERE  d.department_name = 'Sales')
SET    salary = salary * 1.1;
-- The subquery produces the key-preserved row set
-- Oracle updates the base EMPLOYEES table directly

Key-preserved table required: For an UPDATE or DELETE through a subquery target, the subquery must produce a key-preserved result — the primary key of the table being modified must be unique in the subquery's result set. If it is not key-preserved, Oracle raises ORA-01779.

08 — INSERT into a subquery target

INSERT can use a subquery as the target table — inserting into a view or an inline view that points back to the base table. The rules are the same as INSERT through a view from Chapter 14.

-- INSERT using a subquery in VALUES (standard multi-row insert)
INSERT INTO emp_backup
SELECT employee_id, last_name, salary, hire_date
FROM   employees
WHERE  department_id = 50;

-- Multi-table INSERT — insert one source row into multiple tables
INSERT ALL
    INTO emp_history   (employee_id, last_name, salary)
         VALUES (employee_id, last_name, salary)
    INTO sal_history   (employee_id, salary, change_date)
         VALUES (employee_id, salary, SYSDATE)
SELECT employee_id, last_name, salary
FROM   employees
WHERE  department_id = 50;
-- Each row from the SELECT is inserted into BOTH target tables

INSERT ALL vs INSERT FIRST: INSERT ALL inserts each source row into every INTO clause regardless of conditions. INSERT FIRST inserts each source row into the first INTO clause whose WHEN condition is satisfied, then stops. The exam tests the difference between these two forms.

-- INSERT FIRST: conditional multi-table insert
INSERT FIRST
    WHEN salary < 5000  THEN
        INTO low_sal_emp VALUES (employee_id, last_name, salary)
    WHEN salary < 10000 THEN
        INTO mid_sal_emp VALUES (employee_id, last_name, salary)
    ELSE
        INTO high_sal_emp VALUES (employee_id, last_name, salary)
SELECT employee_id, last_name, salary FROM employees;
-- Each employee goes into exactly one table — the first matching WHEN

09 — DELETE using a subquery

DELETE can use a subquery in its WHERE clause — this was covered in Post 07. Chapter 17 adds the pattern of deleting through a subquery target, which follows the same key-preservation rules as UPDATE.

-- Standard DELETE with subquery in WHERE (from Chapter 10)
DELETE FROM employees
WHERE  department_id = (SELECT department_id
                        FROM   departments
                        WHERE  department_name = 'Shipping');

-- DELETE through a subquery target (Chapter 17 pattern)
DELETE FROM (SELECT e.*
              FROM   employees e
              JOIN   departments d
                ON   e.department_id = d.department_id
              WHERE  d.department_name = 'Shipping');
-- Deletes the rows that satisfy the inline view's filter
-- Equivalent to the standard form above but written as target subquery

10 — Quick reference

Pattern	Key rule
Scalar subquery in SELECT list	Must return exactly one row — ORA-01427 if multiple rows; returns NULL if zero rows
Scalar subquery in ORDER BY	Valid — sorts by the single value returned per outer row
Multiple-column subquery	Column count on left must match column count in subquery
Pairwise comparison	Both columns must match in the same subquery row
Non-pairwise comparison	Each column checked independently — can return more rows than pairwise
WITH clause	Defines named query blocks — reusable within the same statement only
UPDATE through subquery	Requires key-preserved result set — ORA-01779 if not key-preserved
INSERT ALL	Each source row inserted into ALL matching INTO clauses
INSERT FIRST	Each source row inserted into only the FIRST matching WHEN clause

11 — Practice questions

Q1 — A scalar subquery in the SELECT list returns zero rows for one particular outer row. What value appears in the result for that column?

A) Zero B) NULL C) An empty string D) ORA-01427 is raised

Q2 — A developer uses INSERT ALL with three INTO clauses and a SELECT that returns 10 rows. None of the INTO clauses have WHEN conditions. How many total rows are inserted across all three tables?

A) 10 rows — one row per source row, split across tables B) 30 rows — each source row is inserted into all three tables C) 10 rows — INSERT ALL without WHEN inserts into the first table only D) An error — INSERT ALL requires WHEN conditions

Q3 — Which of these correctly uses the WITH clause?

A) SELECT * FROM employees WITH (dept_avg AS (SELECT AVG(salary) FROM employees GROUP BY department_id)); B) WITH dept_avg AS (SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id) SELECT * FROM dept_avg WHERE avg_sal > 8000; C) SELECT * FROM (WITH dept_avg AS (SELECT AVG(salary) FROM employees)) WHERE dept_avg > 8000; D) CREATE WITH dept_avg AS (SELECT AVG(salary) FROM employees GROUP BY department_id);

Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set

19 chapters  ·  1,480 practice questions  ·  1,249 SQL examples
Chapters 16 and 17 cover every advanced subquery pattern on the exam — scalar subqueries, pairwise comparison, WITH clause, and multi-table INSERT. Instant PDF download.

Get the full guide — $25 →

Published by https://www.apps-dba.com — Oracle DBA and certification resources.