JOIN

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE cities (id INTEGER PRIMARY KEY, name TEXT)")
con.execute("CREATE TABLE sales (city_id INTEGER, units INTEGER)")
con.executemany("INSERT INTO cities VALUES (?, ?)", [(1, "Nairobi"), (2, "Mombasa")])
con.executemany("INSERT INTO sales VALUES (?, ?)", [(1, 12), (1, 7), (2, 9)])
print(con.execute("SELECT c.name, s.units FROM sales s JOIN cities c ON c.id = s.city_id").fetchall())
con.close()

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE cities (id INTEGER PRIMARY KEY, name TEXT)")
con.execute("CREATE TABLE sales (city_id INTEGER, units INTEGER)")
con.executemany("INSERT INTO cities VALUES (?, ?)", [(1, "Nairobi"), (2, "Mombasa"), (3, "Kisumu")])
con.executemany("INSERT INTO sales VALUES (?, ?)", [(1, 12), (2, 9)])
print(con.execute("SELECT c.name, s.units FROM cities c LEFT JOIN sales s ON c.id = s.city_id").fetchall())
con.close()

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE cities (id INTEGER PRIMARY KEY, name TEXT)")
con.execute("CREATE TABLE sales (city_id INTEGER, units INTEGER)")
con.executemany("INSERT INTO cities VALUES (?, ?)", [(1, "Nairobi"), (2, "Mombasa")])
con.executemany("INSERT INTO sales VALUES (?, ?)", [(1, 12), (2, 9)])
print(con.execute("SELECT c.name, SUM(s.units) FROM sales s JOIN cities c ON c.id = s.city_id GROUP BY c.name").fetchall())
con.close()

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE a (id INTEGER, n TEXT)")
con.execute("CREATE TABLE b (id INTEGER, n TEXT)")
con.execute("INSERT INTO a VALUES (1, 'Nairobi')")
con.execute("INSERT INTO b VALUES (1, 'HQ')")
print(con.execute("SELECT a.n, b.n FROM a JOIN b USING (id)").fetchall())
con.close()