How to Use Subqueries in SQL

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT,
  grade TEXT,
  score INTEGER
);

INSERT INTO students VALUES 
  (1, 'Alice', 'A', 95),
  (2, 'Bob', 'B', 82),
  (3, 'Charlie', 'A', 91),
  (4, 'Diana', 'C', 74),
  (5, 'Eve', 'A', 88),
  (6, 'Frank', 'B', 79);

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

-- Subquery with IN
SELECT name FROM students
WHERE grade IN (SELECT DISTINCT grade FROM students WHERE score > 90);

-- Correlated subquery
SELECT name, score,
  (SELECT AVG(score) FROM students) as class_avg,
  score - (SELECT AVG(score) FROM students) as diff_from_avg
FROM students
ORDER BY score DESC;