What are Subqueries

A subquery is a SELECT statement that is nested within another SELECT statement which return intermediate results

• SQL executes innermost subquery first, then next level

Nested Subqueries

Select students who have higher GPA than the average of all GPAs

SELECT *
FROM students
WHERE GPA > (
  SELECT AVG(GPA)
  FROM students);

Show average number of students in English or History class

SELECT AVG(number_of_students)
FROM classes
WHERE teacher_id 
IN (
  SELECT id
  FROM teachers
  WHERE subject = 'English' OR subject = 'History'
);