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 NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_name VARCHAR2(100) NOT NULL,
email VARCHAR2(100) UNIQUE,
phone VARCHAR2(15),
address CLOB,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
3. Create Tabel products
CREATE TABLE products (
product_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
product_name VARCHAR2(100) NOT NULL,
description CLOB,
price NUMBER(10, 2) NOT NULL,
stock NUMBER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
4. create table orders
CREATE TABLE orders (
order_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_id NUMBER,
customer_name VARCHAR2(100) NOT NULL,
product_id NUMBER,
product_name VARCHAR2(100) NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
quantity NUMBER NOT NULL,
total_amount NUMBER(10, 2) NOT NULL,
CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
CONSTRAINT fk_product 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');
INSERT INTO customers (customer_name, email, phone, address) VALUES ('Jane Smith', 'janesmith@teguhth.com', '0987654321', '456 Park Ave');
INSERT INTO customers (customer_name, email, phone, address) VALUES ('Alice Brown', 'aliceb@teguhth.com', '5432109876', '789 Sunset Blvd');
INSERT INTO customers (customer_name, email, phone, address) VALUES ('Bob White', 'bobwhite@teguhth.com', '1231231234', '101 Broadway St');
INSERT INTO customers (customer_name, email, phone, address) VALUES ('Charlie Black', 'charlieb@teguhth.com', '4564564567', '102 Main St');
INSERT INTO customers (customer_name, email, phone, address) VALUES ('Diana Green', 'dianag@teguhth.com', '7897897890', '103 Park Ave');
INSERT INTO customers (customer_name, email, phone, address) VALUES ('Ella Blue', 'ellab@teguhth.com', '3213213210', '104 Sunset Blvd');
INSERT INTO customers (customer_name, email, phone, address) VALUES ('Fred Yellow', 'fredy@teguhth.com', '6546546543', '105 Broadway St');
INSERT INTO customers (customer_name, email, phone, address) VALUES ('Grace Orange', 'graceo@teguhth.com', '9879879871', '106 Main St');
INSERT INTO customers (customer_name, email, phone, address) VALUES ('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);
INSERT INTO products (product_name, description, price, stock) VALUES ('Smartphone', 'Latest model smartphone', 500.00, 100);
INSERT INTO products (product_name, description, price, stock) VALUES ('Tablet', '10-inch screen tablet', 300.00, 75);
INSERT INTO products (product_name, description, price, stock) VALUES ('Monitor', '27-inch LED monitor', 200.00, 30);
INSERT INTO products (product_name, description, price, stock) VALUES ('Keyboard', 'Mechanical keyboard', 50.00, 150);
INSERT INTO products (product_name, description, price, stock) VALUES ('Mouse', 'Wireless mouse', 25.00, 200);
INSERT INTO products (product_name, description, price, stock) VALUES ('Headphones', 'Noise-cancelling headphones', 150.00, 60);
INSERT INTO products (product_name, description, price, stock) VALUES ('Speaker', 'Bluetooth speaker', 100.00, 90);
INSERT INTO products (product_name, description, price, stock) VALUES ('Printer', 'Laser printer', 250.00, 40);
INSERT INTO products (product_name, description, price, stock) VALUES ('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);
INSERT INTO orders (customer_id, customer_name, product_id, product_name, quantity, total_amount) VALUES (2, 'Jane Smith', 2, 'Smartphone', 2, 1000.00);
INSERT INTO orders (customer_id, customer_name, product_id, product_name, quantity, total_amount) VALUES (3, 'Alice Brown', 3, 'Tablet', 1, 300.00);
INSERT INTO orders (customer_id, customer_name, product_id, product_name, quantity, total_amount) VALUES (4, 'Bob White', 4, 'Monitor', 3, 600.00);
INSERT INTO orders (customer_id, customer_name, product_id, product_name, quantity, total_amount) VALUES (5, 'Charlie Black', 5, 'Keyboard', 2, 100.00);
INSERT INTO orders (customer_id, customer_name, product_id, product_name, quantity, total_amount) VALUES (6, 'Diana Green', 6, 'Mouse', 4, 100.00);
INSERT INTO orders (customer_id, customer_name, product_id, product_name, quantity, total_amount) VALUES (7, 'Ella Blue', 7, 'Headphones', 1, 150.00);
INSERT INTO orders (customer_id, customer_name, product_id, product_name, quantity, total_amount) VALUES (8, 'Fred Yellow', 8, 'Speaker', 1, 100.00);
INSERT INTO orders (customer_id, customer_name, product_id, product_name, quantity, total_amount) VALUES (9, 'Grace Orange', 9, 'Printer', 2, 500.00);
INSERT INTO orders (customer_id, customer_name, product_id, product_name, quantity, total_amount) VALUES (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 >= TO_DATE('2024-10-01', 'YYYY-MM-DD')
ORDER BY
o.order_date;
6. with hostname
SELECT (SELECT i.host_name FROM V$INSTANCE i) AS HOST_NAME,
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 >= TO_DATE('2024-10-01', 'YYYY-MM-DD')
ORDER BY
o.order_date;
No comments:
Post a Comment