import sqlite3

# Connect to the database (creates it if it doesn't already exist)
conn = sqlite3.connect('mydatabase.db')

# Create a cursor object to execute SQL commands
c = conn.cursor()

# Drop all tables
c.execute('''DROP TABLE customers''')
c.execute('''DROP TABLE orders''')

# Create a table for customers
c.execute('''CREATE TABLE customers
             (id INTEGER PRIMARY KEY, 
             name text, 
             email text)''')

# Create a table for orders
c.execute('''CREATE TABLE orders
             (id INTEGER PRIMARY KEY, 
             customer_id INTEGER, 
             product text, 
             quantity INTEGER, 
             FOREIGN KEY (customer_id) REFERENCES customers(id))''')

# Insert some data into customers
c.execute("INSERT INTO customers VALUES (1, 'Alice', 'alice@example.com')")
c.execute("INSERT INTO customers VALUES (2, 'Bob', 'bob@example.com')")
c.execute("INSERT INTO customers VALUES (3, 'Charlie', 'charlie@example.com')")

# Insert some data into orders
c.execute("INSERT INTO orders VALUES (1, 1, 'Product A', 2)")
c.execute("INSERT INTO orders VALUES (2, 2, 'Product B', 1)")
c.execute("INSERT INTO orders VALUES (3, 1, 'Product C', 3)")

# Save (commit) the changes
conn.commit()

# Retrieve data
print("SELECT * FROM customers")
c.execute("SELECT * FROM customers")
rows = c.fetchall()
for row in rows:
    print(row)
print("")

print("SELECT * FROM orders")
c.execute("SELECT * FROM orders")
rows = c.fetchall()
for row in rows:
    print(row)
print("")

print("SELECT orders.product, customers.name FROM orders JOIN customers ON orders.customer_id == customers.id")
c.execute("SELECT orders.product, customers.name FROM orders JOIN customers ON orders.customer_id == customers.id")
rows = c.fetchall()
for row in rows:
    print(row)
print("")


# Close the cursor and the connection
c.close()
conn.close()


# OUTPUT
'''
SELECT * FROM customers
(1, 'Alice', 'alice@example.com')
(2, 'Bob', 'bob@example.com')
(3, 'Charlie', 'charlie@example.com')

SELECT * FROM orders
(1, 1, 'Product A', 2)
(2, 2, 'Product B', 1)
(3, 1, 'Product C', 3)

SELECT orders.product, customers.name FROM orders JOIN customers ON orders.customer_id == customers.id
('Product A', 'Alice')
('Product B', 'Bob')
('Product C', 'Alice')
'''