level3.sql

.headers on
.mode column

-- 1. CREATE Table 1: The Customers
CREATE TABLE customers (
    id INTEGER, 
    name TEXT
);

-- 2. CREATE Table 2: The Orders
CREATE TABLE orders (
    order_id INTEGER, 
    customer_id INTEGER, 
    item TEXT, 
    price INTEGER
);

-- 3. INSERT Customers (Notice their ID numbers!)
INSERT INTO customers VALUES (1, 'Rahul');
INSERT INTO customers VALUES (2, 'Priya');
INSERT INTO customers VALUES (3, 'Amit');

-- 4. INSERT Orders (Notice we use the customer_id numbers instead of names!)
INSERT INTO orders VALUES (101, 1, 'Laptop', 1200);
INSERT INTO orders VALUES (102, 1, 'Mouse', 25);
INSERT INTO orders VALUES (103, 2, 'Keyboard', 50);

-- 5. THE MAGIC JOIN: Smashing the two tables together!
SELECT '--- WHO BOUGHT WHAT? ---' AS '';

SELECT 
    customers.name AS Customer_Name, 
    orders.item AS Item_Bought, 
    orders.price AS Price
FROM orders
JOIN customers ON orders.customer_id = customers.id;