← All lessons

Subqueries

Cities above average.

A SELECT inside another SELECT.

Goal

Cities above average units.

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
  city TEXT,
  units INTEGER,
  shillings INTEGER
);
INSERT INTO sales VALUES
  ('Nairobi', 12, 480),
  ('Mombasa', 8, 320),
  ('Kisumu', 5, 200);

SELECT city, units FROM sales
WHERE units > (SELECT AVG(units) FROM sales);
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
  city TEXT,
  units INTEGER,
  shillings INTEGER
);
INSERT INTO sales VALUES
  ('Nairobi', 12, 480),
  ('Mombasa', 8, 320),
  ('Kisumu', 5, 200);

SELECT city FROM sales
WHERE shillings = (SELECT MAX(shillings) FROM sales);
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
  city TEXT,
  units INTEGER,
  shillings INTEGER
);
INSERT INTO sales VALUES
  ('Nairobi', 12, 480),
  ('Mombasa', 8, 320),
  ('Kisumu', 5, 200);

SELECT city, units,
  (SELECT SUM(units) FROM sales) AS all_units
FROM sales;
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
  city TEXT,
  units INTEGER,
  shillings INTEGER
);
INSERT INTO sales VALUES
  ('Nairobi', 12, 480),
  ('Mombasa', 8, 320),
  ('Kisumu', 5, 200);

SELECT city FROM sales
WHERE city IN (SELECT city FROM sales WHERE units >= 8);