.::: Sample Database ERP (Enterprise Resource Planning) and sample query using Join in PostgreSQL EDB :::.

Berikut ini adalah contoh pembuatan database ERP yang lebih sederhana dengan fokus pada customers dan products, serta tabel tambahan orders untuk mengaitkan data pelanggan dengan produk yang mereka pesan. Setiap tabel akan diisi dengan minimal 10 baris data.

A. Sample DB ERP

1. Create Database ERP
CREATE DATABASE TEGUHTH_ERPDB;
USE TEGUHTH_ERPDB;

2. Create Tabel customers
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(15),
    address TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

3. Create Tabel products
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

4. create table  orders

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    customer_name VARCHAR(100) NOT NULL,
    product_id INT REFERENCES products(product_id),
    product_name VARCHAR(100) NOT NULL,
    order_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    quantity INT NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

5. insert table customers

INSERT INTO customers (customer_name, email, phone, address) VALUES
('John Doe', 'johndoe@teguhth.com', '1234567890', '123 Main St'),
('Jane Smith', 'janesmith@teguhth.com', '0987654321', '456 Park Ave'),
('Alice Brown', 'aliceb@teguhth.com', '5432109876', '789 Sunset Blvd'),
('Bob White', 'bobwhite@teguhth.com', '1231231234', '101 Broadway St'),
('Charlie Black', 'charlieb@teguhth.com', '4564564567', '102 Main St'),
('Diana Green', 'dianag@teguhth.com', '7897897890', '103 Park Ave'),
('Ella Blue', 'ellab@teguhth.com', '3213213210', '104 Sunset Blvd'),
('Fred Yellow', 'fredy@teguhth.com', '6546546543', '105 Broadway St'),
('Grace Orange', 'graceo@teguhth.com', '9879879871', '106 Main St'),
('Harry Purple', 'harryp@teguhth.com', '7418529630', '107 Park Ave');

select * from customers;
 

6. insert table products
INSERT INTO products (product_name, description, price, stock) VALUES
('Laptop', 'High performance laptop', 750.00, 50),
('Smartphone', 'Latest model smartphone', 500.00, 100),
('Tablet', '10-inch screen tablet', 300.00, 75),
('Monitor', '27-inch LED monitor', 200.00, 30),
('Keyboard', 'Mechanical keyboard', 50.00, 150),
('Mouse', 'Wireless mouse', 25.00, 200),
('Headphones', 'Noise-cancelling headphones', 150.00, 60),
('Speaker', 'Bluetooth speaker', 100.00, 90),
('Printer', 'Laser printer', 250.00, 40),
('Router', 'Wi-Fi router', 70.00, 110);

select * from products;
 

7. insert table order
INSERT INTO orders (customer_id, customer_name, product_id, product_name, quantity, total_amount) VALUES
(1, 'John Doe', 1, 'Laptop', 1, 750.00),
(2, 'Jane Smith', 2, 'Smartphone', 2, 1000.00),
(3, 'Alice Brown', 3, 'Tablet', 1, 300.00),
(4, 'Bob White', 4, 'Monitor', 3, 600.00),
(5, 'Charlie Black', 5, 'Keyboard', 2, 100.00),
(6, 'Diana Green', 6, 'Mouse', 4, 100.00),
(7, 'Ella Blue', 7, 'Headphones', 1, 150.00),
(8, 'Fred Yellow', 8, 'Speaker', 1, 100.00),
(9, 'Grace Orange', 9, 'Printer', 2, 500.00),
(10, 'Harry Purple', 10, 'Router', 5, 350.00);

select * from orders;
 

B. Sample DB ERP

Berikut adalah beberapa contoh query menggunakan JOIN untuk menggabungkan data dari tabel customers, products, dan orders di PostgreSQL:

1. Menampilkan Detail Pesanan dengan Informasi Pelanggan dan Produk
Query ini menggabungkan data dari tabel orders, customers, dan products untuk menampilkan detail pesanan beserta nama pelanggan dan nama produk yang dipesan.

SELECT
    o.order_id,
    c.customer_name,
    p.product_name,
    o.order_date,
    o.quantity,
    o.total_amount
FROM
    orders o
JOIN
    customers c ON o.customer_id = c.customer_id
JOIN
    products p ON o.product_id = p.product_id
ORDER BY
    o.order_date DESC;
 

   
2. Menampilkan Total Pembelian Setiap Pelanggan
Query ini menghitung total pembelian yang dilakukan oleh setiap pelanggan berdasarkan data di tabel orders dan customers.

SELECT
    c.customer_name,
    SUM(o.total_amount) AS total_spent
FROM
    orders o
JOIN
    customers c ON o.customer_id = c.customer_id
GROUP BY
    c.customer_name
ORDER BY
    total_spent DESC;
 

   
3. Menampilkan Produk yang Paling Banyak Dipesan
Query ini menghitung total kuantitas pesanan untuk setiap produk, sehingga Anda bisa mengetahui produk mana yang paling banyak dipesan.

SELECT
    p.product_name,
    SUM(o.quantity) AS total_ordered
FROM
    orders o
JOIN
    products p ON o.product_id = p.product_id
GROUP BY
    p.product_name
ORDER BY
    total_ordered DESC;
 

   
4. Menampilkan Daftar Pesanan Terkini dari Setiap Pelanggan
Query ini menampilkan daftar pesanan terbaru dari setiap pelanggan berdasarkan data di tabel orders, customers, dan products.

SELECT
    c.customer_name,
    p.product_name,
    o.order_date,
    o.quantity,
    o.total_amount
FROM
    orders o
JOIN
    customers c ON o.customer_id = c.customer_id
JOIN
    products p ON o.product_id = p.product_id
WHERE
    o.order_date = (SELECT MAX(order_date) FROM orders WHERE customer_id = o.customer_id)
ORDER BY
    o.order_date DESC;
 

   
5. Menampilkan Pesanan yang Dilakukan dalam Rentang Tanggal Tertentu
Query ini menampilkan pesanan yang dibuat dalam rentang tanggal tertentu (misalnya, bulan Oktober 2024), termasuk nama pelanggan dan produk.

SELECT
    o.order_id,
    c.customer_name,
    p.product_name,
    o.order_date,
    o.quantity,
    o.total_amount
FROM
    orders o
JOIN
    customers c ON o.customer_id = c.customer_id
JOIN
    products p ON o.product_id = p.product_id
WHERE
    o.order_date BETWEEN '2024-10-01' AND '2024-10-31'
ORDER BY
    o.order_date;
 

6. with hostname

SELECT (select pg_read_file('/etc/hostname') as hostname),
    o.order_id,
    c.customer_name,
    p.product_name,
    o.order_date,
    o.quantity,
    o.total_amount,(select VERSION())
FROM
    orders o
JOIN
    customers c ON o.customer_id = c.customer_id
JOIN
    products p ON o.product_id = p.product_id
WHERE
    o.order_date BETWEEN '2024-10-01' AND '2024-10-31'
ORDER BY
    o.order_date;