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 IDENTITY(1,1) PRIMARY KEY,
customer_name NVARCHAR(100) NOT NULL,
email NVARCHAR(100) UNIQUE,
phone NVARCHAR(15),
address NVARCHAR(MAX),
created_at DATETIME DEFAULT GETDATE()
);
3. Create Tabel products
CREATE TABLE products (
product_id INT IDENTITY(1,1) PRIMARY KEY,
product_name NVARCHAR(100) NOT NULL,
description NVARCHAR(MAX),
price DECIMAL(10, 2) NOT NULL,
stock INT DEFAULT 0,
created_at DATETIME DEFAULT GETDATE()
);
4. create table orders
CREATE TABLE orders (
order_id INT IDENTITY(1,1) PRIMARY KEY,
customer_id INT,
customer_name NVARCHAR(100) NOT NULL,
product_id INT,
product_name NVARCHAR(100) NOT NULL,
order_date DATETIME DEFAULT GETDATE(),
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-12-01' AND '2024-12-31'
ORDER BY
o.order_date;
6. with hostname
SELECT @@servername AS ServerName,
o.order_id,
c.customer_name,
p.product_name,
o.order_date,
o.quantity,
o.total_amount,@@VERSION AS VersionSQL
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-12-01' AND '2024-12-31'
ORDER BY
o.order_date;
No comments:
Post a Comment