Practice: kiosk ledger

DROP TABLE IF EXISTS cities;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS sales;
CREATE TABLE cities (
  id INTEGER PRIMARY KEY,
  name TEXT
);
CREATE TABLE items (
  id INTEGER PRIMARY KEY,
  name TEXT,
  kes INTEGER
);
CREATE TABLE sales (
  city_id INTEGER,
  item_id INTEGER,
  units INTEGER
);
INSERT INTO cities VALUES (1, 'Nairobi'), (2, 'Mombasa'), (3, 'Kisumu');
INSERT INTO items VALUES (1, 'mango', 40), (2, 'soda', 50);
INSERT INTO sales VALUES
  (1, 1, 12),
  (1, 2, 6),
  (2, 1, 8),
  (3, 2, 5);
SELECT cities.name, items.name AS item, sales.units,
       sales.units * items.kes AS shillings
FROM sales
JOIN cities ON cities.id = sales.city_id
JOIN items ON items.id = sales.item_id
ORDER BY shillings DESC;