1. The E-Commerce Test Schema
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
order_date TEXT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO customers VALUES
(1, 'Alice Corp', 'New York'),
(2, 'Bob Logistics', 'Chicago'),
(3, 'Charlie Retail', 'Austin');
INSERT INTO orders VALUES
(101, 1, 450.00, '2026-06-01'),
(102, 1, 120.00, '2026-06-15'),
(103, 2, 890.00, '2026-06-20');
-- Notice: Charlie Retail has zero orders.
2. INNER JOIN: Customers with Active Orders
Returns records only when the customer ID matches in both tables:
SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
3. LEFT JOIN: Audit Inactive Customers
Returns all customers, including those with zero orders (e.g. Charlie Retail with NULL):
SELECT c.name, COUNT(o.order_id) AS total_orders
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
