CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
price INTEGER
);
CREATE TABLE transactions (
id SERIAL PRIMARY KEY,
date DATE
);
CREATE TABLE transaction_items (
id SERIAL PRIMARY KEY,
transaction_id INTEGER REFERENCES transactions(id),
product_id INTEGER REFERENCES products(id),
quantity INTEGER
);
INSERT INTO products (id, name, price) VALUES
(1, 'Espresso', 18000),
(2, 'Latte', 22000),
(3, 'Americano', 20000),
(4, 'Cappuccino', 23000),
(5, 'Matcha Latte', 25000);
INSERT INTO transactions (id, date) VALUES
(1, '2025-07-25'),
(2, '2025-07-25'),
(3, '2025-07-26'),
(4, '2025-07-27'),
(5, '2025-07-27');
INSERT INTO transaction_items (id, transaction_id, product_id, quantity) VALUES
(1, 1, 1, 2),
(2, 1, 2, 1),
(3, 2, 3, 1),
(4, 3, 4, 3),
(5, 4, 1, 1),
(6, 4, 5, 2),
(7, 5, 2, 1);
SELECT * FROM products;
SELECT * FROM transactions;
SELECT * FROM transaction_items;
---3. Produk terlaris:
SELECT
products.name,
SUM(transaction_items.quantity) AS total_terjual
FROM transaction_items
JOIN products ON transaction_items.product_id = products.id
GROUP BY products.name
ORDER BY total_terjual DESC;
Telegram: DB developers channel