JOIN

DROP TABLE IF EXISTS cities;
CREATE TABLE cities (
  id INTEGER PRIMARY KEY,
  name TEXT,
  region TEXT
);
INSERT INTO cities VALUES
  (1, 'Nairobi', 'Capital'),
  (2, 'Mombasa', 'Coast'),
  (3, 'Kisumu', 'Lakeside');

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
  city_id INTEGER,
  units INTEGER,
  shillings INTEGER
);
INSERT INTO sales VALUES
  (1, 12, 480),
  (2, 8, 320),
  (3, 5, 200);
SELECT cities.name, sales.units
FROM sales
JOIN cities ON cities.id = sales.city_id;

DROP TABLE IF EXISTS cities;
CREATE TABLE cities (
  id INTEGER PRIMARY KEY,
  name TEXT,
  region TEXT
);
INSERT INTO cities VALUES
  (1, 'Nairobi', 'Capital'),
  (2, 'Mombasa', 'Coast'),
  (3, 'Kisumu', 'Lakeside');

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
  city_id INTEGER,
  units INTEGER,
  shillings INTEGER
);
INSERT INTO sales VALUES
  (1, 12, 480),
  (2, 8, 320),
  (3, 5, 200);
SELECT cities.region, SUM(sales.shillings) AS kes
FROM sales
JOIN cities ON cities.id = sales.city_id
GROUP BY cities.region;

DROP TABLE IF EXISTS cities;
CREATE TABLE cities (
  id INTEGER PRIMARY KEY,
  name TEXT,
  region TEXT
);
INSERT INTO cities VALUES
  (1, 'Nairobi', 'Capital'),
  (2, 'Mombasa', 'Coast'),
  (3, 'Kisumu', 'Lakeside');

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
  city_id INTEGER,
  units INTEGER,
  shillings INTEGER
);
INSERT INTO sales VALUES
  (1, 12, 480),
  (2, 8, 320),
  (3, 5, 200);
SELECT cities.name, sales.shillings
FROM sales
JOIN cities ON cities.id = sales.city_id
WHERE sales.units >= 8;

DROP TABLE IF EXISTS cities;
CREATE TABLE cities (
  id INTEGER PRIMARY KEY,
  name TEXT,
  region TEXT
);
INSERT INTO cities VALUES
  (1, 'Nairobi', 'Capital'),
  (2, 'Mombasa', 'Coast'),
  (3, 'Kisumu', 'Lakeside');

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
  city_id INTEGER,
  units INTEGER,
  shillings INTEGER
);
INSERT INTO sales VALUES
  (1, 12, 480),
  (2, 8, 320),
  (3, 5, 200);
SELECT cities.name
FROM cities
LEFT JOIN sales ON sales.city_id = cities.id
WHERE sales.city_id IS NOT NULL;