SQL Subquery Practice with 20 Correlated Query Challenges

This capstone lesson brings together every subquery concept from this module through 20 progressively challenging practice questions, with special emphasis on correlated subqueries, since these are consistently the most difficult subquery pattern for learners to master and the most frequently tested in technical interviews.

Key Definitions

• Correlated-focused practice: A practice set specifically weighted toward correlated subquery problems, since they require a different, per-row reasoning pattern than most other subquery types.
• Interview-pattern question: A practice question deliberately modeled after the phrasing and structure commonly seen in real technical interviews.

What You'll Learn

• Apply single-row, multi-row, correlated, EXISTS, ANY/ALL, and nested subqueries in realistic scenarios.
• Practice choosing between a subquery and an equivalent JOIN for a given problem.
• Build interview-ready fluency with correlated subquery reasoning.
• Complete a capstone-style project connecting the entire subqueries module.

Detailed Explanation

The 20 questions are organized into two tiers of 10 questions each. Tier 1 covers foundational patterns: single-row and multi-row subqueries, WHERE and FROM clause subqueries, and basic EXISTS checks. Tier 2 is deliberately correlated-subquery-heavy, covering per-group maximum/minimum problems, correlated EXISTS checks, ANY/ALL comparisons, and one deliberately three-level nested question that combines everything from this module into a single query.

For each question, follow the same disciplined approach taught throughout this module: identify whether the problem needs a single value or a list, decide if the subquery must reference the outer row (making it correlated), and build complex questions from the innermost logic outward rather than attempting to write the entire nested structure in one pass.

Visual Summary

A two-column layout. Left column labeled 'Tier 1 — Foundational (Q1-Q10)': single-row, multi-row, WHERE/FROM subqueries, basic EXISTS. Right column labeled 'Tier 2 — Correlated Focus (Q11-Q20)': per-group MAX/MIN, correlated EXISTS, ANY/ALL, one 3-level nested capstone question.

Quick Reference

Tier	Question Range	Primary Focus
Tier 1 — Foundational	Q1 – Q10	Single-row, multi-row, WHERE/FROM subqueries, basic EXISTS
Tier 2 — Correlated Focus	Q11 – Q20	Per-group MAX/MIN, correlated EXISTS, ANY/ALL, 3-level nesting

SQL Example

CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctors (
  doctor_id       INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name     VARCHAR(100) NOT NULL,
  department_id   INT,
  salary          INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE patients (
  patient_id      INT PRIMARY KEY AUTO_INCREMENT,
  patient_name    VARCHAR(100) NOT NULL,
  city            VARCHAR(80)
);

CREATE TABLE appointments (
  appointment_id    INT PRIMARY KEY AUTO_INCREMENT,
  patient_id        INT,
  doctor_id         INT,
  appointment_date  DATE,
  status            VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id)  REFERENCES doctors(doctor_id)
);

CREATE TABLE bills (
  bill_id         INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id  INT,
  amount          DECIMAL(10,2),
  paid            BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

INSERT INTO departments VALUES
  (1, 'Cardiology'), (2, 'Orthopedics'), (3, 'Neurology'), (4, 'Dermatology');

INSERT INTO doctors (doctor_id, doctor_name, department_id, salary) VALUES
  (101, 'Dr. Verma', 1, 95000), (102, 'Dr. Iyer', 2, 78000),
  (103, 'Dr. Sen', 3, 120000), (104, 'Dr. Khan', 1, 88000);

INSERT INTO patients VALUES
  (201, 'Amit Rao', 'Pune'), (202, 'Neha Joshi', 'Mumbai'),
  (203, 'Karan Mehta', 'Delhi'), (204, 'Divya Nair', 'Pune');

INSERT INTO appointments VALUES
  (301, 201, 101, '2026-05-01', 'Completed'),
  (302, 202, 102, '2026-05-02', 'Completed'),
  (303, 203, 101, '2026-05-03', 'Cancelled'),
  (304, 204, 103, '2026-05-04', 'Completed');

INSERT INTO bills (appointment_id, amount, paid) VALUES
  (301, 1500.00, TRUE), (302, 2200.00, FALSE),
  (303, 800.00, TRUE), (304, 3000.00, TRUE);

-- Sample Q4 (Tier 1): patients who have never had an appointment
SELECT patient_name
FROM patients p
WHERE NOT EXISTS (
  SELECT 1 FROM appointments a WHERE a.patient_id = p.patient_id
);

-- Sample Q13 (Tier 2, correlated): each department's doctor count
-- compared against the overall average department size
SELECT dept.department_name,
  (SELECT COUNT(*) FROM doctors d WHERE d.department_id = dept.department_id) AS doctor_count
FROM departments dept
WHERE (SELECT COUNT(*) FROM doctors d WHERE d.department_id = dept.department_id)
      > (SELECT AVG(dept_count) FROM (
          SELECT COUNT(*) AS dept_count FROM doctors GROUP BY department_id
        ) AS dept_counts);

-- Sample Q20 (Tier 2, capstone): doctors who earn more than every
-- doctor in the department with the fewest total appointments
SELECT doctor_name, salary
FROM doctors
WHERE salary > ALL (
  SELECT d2.salary FROM doctors d2
  WHERE d2.department_id = (
    SELECT a.department_id FROM (
      SELECT doc.department_id, COUNT(*) AS appt_count
      FROM appointments ap
      JOIN doctors doc ON ap.doctor_id = doc.doctor_id
      GROUP BY doc.department_id
      ORDER BY appt_count ASC
      LIMIT 1
    ) AS a
  )
);

Q4 uses the NOT EXISTS pattern from Lesson 7.10 to find patients with zero appointments. Q13 combines a correlated scalar subquery with a derived table (Lesson 7.6) to compare each department's doctor count against the overall average department size. Q20 is a deliberately layered capstone question combining a derived table, a nested subquery, and the ALL operator, requiring you to first find the department with the fewest total appointments, then find doctors who out-earn every doctor in that specific department — a genuine synthesis of nearly every concept covered in this module.

Real-World Examples

• This tiered, correlated-focused practice structure mirrors how many companies structure their SQL take-home assessments for data analyst and backend roles.
• Correlated subquery fluency is specifically called out as a differentiator between junior and senior SQL skill levels in many technical interview rubrics.
• Real production reporting code frequently combines derived tables, correlated subqueries, and ANY/ALL-style logic exactly as demonstrated in the Q13 and Q20 samples above.

Common Mistakes to Avoid

• Skipping correlated subquery practice because it feels harder, when it's precisely the area needing the most repetition.
• Attempting complex, multi-concept practice questions in one pass instead of decomposing them into testable pieces first.
• Not reviewing earlier lessons on EXISTS, ANY/ALL, and derived tables before attempting the more advanced Tier 2 questions.

Interview Questions

Q1. Why do correlated subqueries deserve extra practice compared to other subquery types?

Correlated subqueries require a fundamentally different reasoning pattern — thinking row by row, from the outer query's perspective — which most learners find significantly harder to master than non-correlated subqueries, and they are disproportionately represented in advanced technical interviews.

Q2. What is a good strategy for tackling a complex, multi-concept subquery question in an interview?

Break the question into its component parts explicitly: identify what intermediate values are needed, determine whether each subquery needs to be correlated, and build the query from the innermost logic outward, testing each piece independently before combining them.

Practice MCQs

1. Why is Tier 2 of this practice set weighted toward correlated subqueries?

1. Correlated subqueries are rarely used in practice
2. Correlated subqueries require a harder, row-by-row reasoning pattern that benefits from extra practice
3. Non-correlated subqueries are no longer tested in interviews
4. Correlated subqueries always require JOINs instead

Answer: B. Correlated subqueries require a harder, row-by-row reasoning pattern that benefits from extra practice

Explanation: Correlated subqueries are consistently the most challenging subquery pattern for learners, making focused practice especially valuable for interview readiness.

2. What is the recommended first step when facing a complex, multi-concept subquery question?

1. Write the entire query in one attempt without testing
2. Break the question into component parts and build from the innermost logic outward
3. Always convert it to a JOIN immediately
4. Skip straight to using ALL

Answer: B. Break the question into component parts and build from the innermost logic outward

Explanation: Decomposing a complex question into its underlying logical steps, then building and testing from the inside out, is the most reliable strategy for both learning and interviews.

Quick Revision Points

• This capstone lesson is designed for repetition-based mastery, especially of correlated subqueries.
• Complex synthesis questions (like sample Q20) combine derived tables, nested subqueries, and comparison operators together.
• Decomposition and inside-out construction remain the most reliable strategy even for the hardest practice questions.

Conclusion

• This practice set deliberately emphasizes correlated subqueries, the hardest and most interview-relevant subquery pattern.
• Complex questions should be decomposed into component parts and built from the inside out.
• Genuine subquery fluency comes from repetition across foundational and correlated-focused problems alike.

This capstone practice lesson consolidates every subquery concept covered in Module 7 through 20 questions split into a foundational tier and a correlated-subquery-focused tier, reflecting how disproportionately correlated subqueries appear in real technical interviews. The sample questions shown — from a straightforward NOT EXISTS check to a fully layered capstone combining derived tables, nested subqueries, and the ALL operator — demonstrate the full range of difficulty. Working through this set with a disciplined, inside-out construction approach is the most direct path to genuine subquery fluency.