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);

-- Task 1

-- 1. Match customers to orders
SELECT Customers.name, Orders.order_id
FROM Customers
INNER JOIN Orders
ON Customers.customer_id = Orders.customer_id
ORDER BY Orders.order_id;

-- 2. Include every customer
SELECT Customers.name, Orders.order_id
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer_id;

-- 3. Find customers without orders
SELECT Customers.customer_id, Customers.name
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer_id
WHERE Orders.order_id IS NULL;

-- 4. Join all three tables
SELECT Customers.name, Orders.order_id,
       OrderItems.product,
       OrderItems.qty,
       OrderItems.unit_price
FROM Customers
INNER JOIN Orders
ON Customers.customer_id = Orders.customer_id
INNER JOIN OrderItems
ON Orders.order_id = OrderItems.order_id;


--Task 2

-- Report A
SELECT Customers.name AS customer_name,
       Orders.order_id AS order_id,
       OrderItems.product AS product,
       OrderItems.qty AS quantity,
       OrderItems.qty * OrderItems.unit_price AS item_value
FROM Customers
INNER JOIN Orders
    ON Customers.customer_id = Orders.customer_id
INNER JOIN OrderItems
    ON Orders.order_id = OrderItems.order_id
ORDER BY Orders.order_id, OrderItems.item_id;

-- Report B
SELECT Customers.customer_id,
       Customers.name AS customer_name,
       COUNT(DISTINCT Orders.order_id) AS order_count,
       COALESCE(SUM(OrderItems.qty * OrderItems.unit_price), 0) AS total_sales
FROM Customers
LEFT JOIN Orders
    ON Customers.customer_id = Orders.customer_id
LEFT JOIN OrderItems
    ON Orders.order_id = OrderItems.order_id
GROUP BY Customers.customer_id, Customers.name ORDER BY total_sales DESC;

-- Report C
SELECT OrderItems.product AS product,
       SUM(OrderItems.qty) AS total_quantity,
       SUM(OrderItems.qty * OrderItems.unit_price) AS total_sales
FROM OrderItems
GROUP BY OrderItems.product
HAVING SUM(OrderItems.qty * OrderItems.unit_price) >= 100 ORDER BY total_sales DESC;