DROP TABLE IF EXISTS OrderItems;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Customers;

CREATE TABLE Customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE Orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    FOREIGN KEY(customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE OrderItems (
    item_id INTEGER PRIMARY KEY,
    order_id INTEGER,
    product TEXT,
    qty INTEGER,
    unit_price INTEGER,
    FOREIGN KEY(order_id) REFERENCES Orders(order_id)
);

INSERT INTO Customers VALUES
(1, 'Ana'),
(2, 'Ben'),
(3, 'Cara'),
(4, 'Dan');

INSERT INTO Orders VALUES
(101, 1, '2026-10-01'),
(102, 1, '2026-10-02'),
(103, 2, '2026-10-02'),
(104, 3, '2026-10-03');

INSERT INTO OrderItems VALUES
(1, 101, 'Notebook', 2, 50),
(2, 101, 'Pen', 3, 10),
(3, 102, 'Folder', 4, 25),
(4, 103, 'Notebook', 1, 50),
(5, 103, 'Pen', 5, 10),
(6, 104, 'Folder', 2, 25);

SELECT COUNT(*) AS total_customers FROM Customers;
SELECT COUNT(*) AS total_orders FROM Orders;
SELECT COUNT(*) AS total_items FROM OrderItems;
SELECT SUM(qty * unit_price) AS total_sales FROM OrderItems;

SELECT c.name, o.order_id
FROM Customers AS c
INNER JOIN Orders AS o
    ON c.customer_id = o.customer_id
ORDER BY o.order_id;

SELECT c.name, o.order_id
FROM Customers AS c
LEFT JOIN Orders AS o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;

SELECT c.customer_id, c.name
FROM Customers AS c
LEFT JOIN Orders AS o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

SELECT c.name, o.order_id, i.product, (i.qty * i.unit_price) AS line_sales
FROM Customers AS c
JOIN Orders AS o
    ON c.customer_id = o.customer_id
JOIN OrderItems AS i
    ON o.order_id = i.order_id
ORDER BY o.order_id, i.item_id;

SELECT 
    c.name, 
    o.order_id, 
    i.product, 
    i.qty, 
    (i.qty * i.unit_price) AS line_sales
FROM Customers AS c
JOIN Orders AS o 
    ON c.customer_id = o.customer_id
JOIN OrderItems AS i 
    ON o.order_id = i.order_id
ORDER BY o.order_id, i.item_id;
SELECT 
    c.customer_id, 
    c.name,
    COUNT(DISTINCT o.order_id) AS order_count,
    COALESCE(SUM(i.qty * i.unit_price), 0) AS total_sales
FROM Customers AS c
LEFT JOIN Orders AS o 
    ON c.customer_id = o.customer_id
LEFT JOIN OrderItems AS i 
    ON o.order_id = i.order_id
GROUP BY c.customer_id, c.name
ORDER BY total_sales DESC, c.customer_id;

SELECT 
    product,
    SUM(qty) AS total_qty,
    SUM(qty * unit_price) AS total_sales
FROM OrderItems
GROUP BY product
HAVING total_sales >= 100
ORDER BY total_sales DESC;