Lesson 9 - Subqueries

Lesson Overview

Nest SELECT statements to create complex queries.

Understanding Subqueries

A subquery is a query nested inside another query. The subquery is executed first, and its result is used by the outer query. Subqueries can be used in various places such as:

In the WHERE clause to filter results
In the FROM clause as a temporary table
In the SELECT clause to calculate values

Task: Practice Subqueries

Let's explore different types of subqueries:

Find students who scored above the average:

SELECT name, score
FROM students
WHERE score > (SELECT AVG(score) FROM students);

Find the department with the highest average salary:

SELECT department
FROM employees
GROUP BY department
HAVING AVG(salary) = (
  SELECT MAX(avg_salary)
  FROM (
    SELECT AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
  )
);

Find products that cost more than the average in their category:

SELECT product_name, price, category
FROM products p1
WHERE price > (
  SELECT AVG(price)
  FROM products p2
  WHERE p2.category = p1.category
);

Correlated vs Non-Correlated Subqueries

There are two types of subqueries:

Non-correlated subqueries: Execute independently of the outer query. They run once and produce a result that the outer query uses.
Correlated subqueries: Reference columns from the outer query. They execute once for each row processed by the outer query.

Correlated subqueries are more powerful but can be slower than non-correlated subqueries.

Task: Compare Subqueries with Joins

Many queries that use subqueries can also be written using joins. Try rewriting this subquery as a join:

-- Using a subquery
SELECT name
FROM students
WHERE id IN (
  SELECT student_id
  FROM enrollments
  WHERE course_id = 101
);

-- Using a join
SELECT DISTINCT students.name
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
WHERE enrollments.course_id = 101;

Lesson Summary

In this lesson, we've learned how to use subqueries to create more complex and powerful SQL queries. Subqueries allow you to break down complex problems into smaller, more manageable pieces and can make your queries more readable and maintainable.

Congratulations!

You've completed all the SQL lessons! You've learned:

CRUD operations with DB Browser
SQL query syntax (SELECT, WHERE, FROM)
Using Python to interact with SQLite databases
Sorting and limiting results (ORDER BY, LIMIT)
Modifying data (UPDATE, DELETE, INSERT INTO)
Aggregate functions (SUM, AVG, COUNT, MAX, MIN)
Joining tables (INNER JOIN, LEFT JOIN)
Complex queries with subqueries

These skills form the foundation of working with databases. Keep practicing and exploring more advanced topics like transactions, indexes, and database normalization!

Back to Databases Home