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

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.

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

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

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

4. create table  orders

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100) NOT NULL,
    product_id INT,
    product_name VARCHAR(100) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    quantity INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);
 
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 JOIN untuk menggabungkan data dari tabel customers, products, dan orders di MariaDB.

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 @@hostname AS HostName,
    o.order_id,
    c.customer_name,
    p.product_name,
    o.order_date,
    o.quantity,
    o.total_amount,@@VERSION AS MarinaDBVersion
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;