import sqlite3

connection = sqlite3.connect('example.sqlite')

c = connection.cursor()

c.executescript('''DROP TABLE IF EXISTS country;  -- multiple statements
                   DROP TABLE IF EXISTS city''')

countries = [('Denmark', 5748769, 42931, 'Copenhagen'),
             ('Germany', 82800000, 357168, 'Berlin'),
             ('USA', 325719178, 9833520, 'Washington, D.C.'), 
             ('Iceland', 334252, 102775, 'Reykjavik')]

cities = [('Copenhagen', 'Denmark', 775033, 800),
          ('Aarhus', 'Denmark', 273077, 750),
          ('Berlin', 'Germany', 3711930, 1237),
          ('Munich', 'Germany', 1464301, 1158),
          ('Reykjavik', 'Iceland', 126100, 874),
          ('Washington, D.C.', 'USA', 693972, 1790),
          ('New Orleans', 'USA', 343829, 1718),
          ('San Francisco', 'USA', 884363, 1776)]

c.execute('CREATE TABLE country (name, population, area, capital)')

c.executemany('INSERT INTO country VALUES (?,?,?,?)', countries)

c.execute('CREATE TABLE city (name, country, population, established)')

c.executemany('INSERT INTO city VALUES (?,?,?,?)', cities)

connection.commit()

for row in c.execute('SELECT * FROM country'):
    print(row)

for row in c.execute('''SELECT * FROM city, country
    WHERE city.name=country.capital AND city.population < 700000'''):
    print(row)

print(*c.execute('''
    SELECT country.name,
           COUNT(city.name) AS cities,
           100 * SUM(city.population) / country.population
    FROM city JOIN country ON city.country = country.name
    WHERE city.population > 500000
    GROUP BY city.country 
    ORDER BY cities DESC, SUM(city.population) DESC'''))

connection.close
