Oracle SQL — Practice 7: Subqueries
Rule: Always write the inner query first, test it, then wrap the outer query around it.
Q1 — Same department as a given employee (excluding that employee)
STEP 1 — INNER QUERY (RUN THIS FIRST)
SELECT department_id
FROM employees
WHERE last_name = '&enter_last_name'
STEP 2 — FULL QUERY
SELECT last_name, hire_date
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
WHERE last_name = '&enter_last_name'
)
AND last_name <> '&enter_last_name'
;
💡 Use <> to exclude the person themselves. The & prompts the user to type a name.
Q2 — Employees earning more than average salary
STEP 1 — INNER QUERY (RUN THIS FIRST)
SELECT AVG(salary)
FROM employees;
STEP 2 — FULL QUERY
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
ORDER BY salary ASC;
💡 The inner query returns ONE number (the average). Use = when subquery returns one value.
Q3 — Dept. with any 'u' last name — save as lab_07_03.sql
STEP 1 — INNER QUERY (RUN THIS FIRST)
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%';
STEP 2 — FULL QUERY
SELECT employee_id, last_name
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
);
💡 Use IN (not =) because the subquery may return more than one department.
Q4 — Employees in a location (user-prompted) — save as lab_07_04.sql
STEP 1 — INNER QUERY (RUN THIS FIRST)
SELECT department_id
FROM departments
WHERE location_id = &enter_location_id
;
STEP 2 — FULL QUERY
SELECT last_name, department_id, job_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id = &enter_location_id
);
💡 Inner query checks the DEPARTMENTS table, not employees. Then outer finds the employees in
those departments.
Q5 — Employees who report to King
STEP 1 — INNER QUERY (RUN THIS FIRST)
SELECT employee_id
FROM employees
WHERE last_name = 'King';
STEP 2 — FULL QUERY
SELECT last_name, salary
FROM employees
WHERE manager_id = (
SELECT employee_id
FROM employees
WHERE last_name = 'King'
);
💡 Inner query finds King's employee_id. Outer finds everyone whose manager_id matches it.
Q6 — Everyone in the Executive department
STEP 1 — INNER QUERY (RUN THIS FIRST)
SELECT department_id
FROM departments
WHERE department_name = 'Executive';
STEP 2 — FULL QUERY
SELECT last_name, department_id, job_id
FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE department_name = 'Executive'
);
💡 Look up the department ID from the departments table first, then find the employees.
Q7 — Above-average salary AND dept. has 'u' name — save as
lab_07_07.sql
STEP 1 — INNER QUERY (RUN THIS FIRST)
-- Subquery 1:
SELECT AVG(salary) FROM employees;
-- Subquery 2:
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%';
STEP 2 — FULL QUERY
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
AND department_id IN (
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
);
💡 Combines Q2 and Q3. Two subqueries joined by AND — both conditions must be true at the same
time.
Quick Reference
Use = when the subquery returns ONE value
Use IN when the subquery returns MULTIPLE values
Use &variable_name to prompt the user for input
Files to save: lab_07_03.sql (Q3) | lab_07_04.sql (Q4) | lab_07_07.sql (Q7)