Dari perpustakaan dan gudang, kita kini melangkah ke ujung rantai niaga: toko online itu sendiri. Proyek Database Penjualan membangun penjualan_db, sistem yang mencatat pelanggan beserta kota pengirimannya, katalog produk dengan harga dan stok, serta pesanan yang mengalir dari status baru sampai selesai — atau berhenti di dibatalkan. Ciri khas domain ini ada dua: satu pesanan memuat beberapa produk sekaligus lewat tabel rincian ber-PK komposit, dan harga disimpan sebagai snapshot saat pemesanan sehingga riwayat omzet tidak terdistorsi oleh perubahan harga. Proyek ini dikerjakan dalam empat bagian berurutan; setiap bagian membangun lapisan baru di atas bagian sebelumnya, dan setiap bagian dapat diuji ulang dari awal karena selalu membangun kembali database-nya sendiri.

Tujuan Proyek #

Sistem yang kita bangun harus mampu menjawab kebutuhan nyata pemilik toko online kecil:

  • menyimpan pelanggan dengan nama, email unik, dan kota pengiriman;
  • menyimpan katalog produk dengan SKU unik, kategori, harga DECIMAL, dan stok;
  • mencatat pesanan multi-produk yang harga per itemnya di-snapshot saat pemesanan;
  • membuat pesanan secara atomik: rincian masuk, stok berkurang, keduanya terjadi bersama-sama atau tidak sama sekali;
  • mengikuti daur hidup pesanan lewat status ENUM — baru, dibayar, dikirim, selesai, dibatalkan — dengan pembatalan yang memulihkan stok;
  • menyajikan analisis: omzet per bulan, produk terlaris dengan peringkat, pelanggan teratas, dan sebaran status pesanan.

Setiap kebutuhan tersebut dipetakan ke fitur MySQL yang sudah kita pelajari: skema relasional dengan PK komposit dan ENUM untuk struktur data, INSERT/UPDATE/DELETE untuk pengelolaan katalog, transaksi eksplisit untuk alur pesanan, dan JOIN + agregat + window function untuk analisis.

Prasyarat #

Proyek ini memakai materi dari beberapa section sebelumnya. Sebelum mulai, pastikan kita sudah nyaman dengan topik berikut:

MateriDipakai untuk
dasar-sql — membuat database dan tabelBagian 1: perancangan skema
relational-database-design — relasi dan junction tableBagian 1: relasi banyak-ke-banyak produk-pesanan
manipulasi-data — INSERT, UPDATE, DELETEBagian 2: pengelolaan produk dan pelanggan
transactions — COMMIT dan ROLLBACKBagian 3: transaksi penjualan
agregasi-data dan window-functionsBagian 4: analisis penjualan

Peta Bagian #

Proyek dikerjakan dalam empat bagian. Setiap bagian merujuk ke berkas SQL yang sama sehingga kondisi proyek tetap utuh di setiap tahap:

  1. Bagian 1: Perancangan Database — menganalisis kebutuhan toko online, mengidentifikasi entitas dan relasinya, lalu menulis skema penjualan_db lengkap dengan PK komposit, ENUM status, dan snapshot harga.
  2. Bagian 2: Pengelolaan Produk dan Pelanggan — mengisi data awal katalog dan pelanggan, menjalankan operasi CRUD sehari-hari, dan membuktikan constraint bekerja menolak data yang tidak valid.
  3. Bagian 3: Transaksi Penjualan — menjalankan alur pesanan dalam transaksi: rincian masuk dan stok berkurang dalam satu blok, total dihitung, status naik tahap, dan pembatalan memulihkan stok.
  4. Bagian 4: Analisis Penjualan — menyusun omzet per bulan, produk terlaris, pelanggan teratas, dan distribusi status, lalu menjalankan berkas pemeriksaan akhir seluruh proyek.

Struktur Berkas Proyek #

Seluruh berkas SQL proyek hidup di dalam bundle ini dan tampil pada bagian Berkas Sumber di bawah halaman:

text 
database-penjualan/
├── database/skema.sql              -- CREATE DATABASE + empat tabel
├── database/data-awal.sql          -- data contoh 10 produk dan 8 pelanggan
├── queries/omzet-per-bulan.sql     -- omzet bulanan dari harga snapshot
├── queries/produk-terlaris.sql     -- peringkat unit terjual dengan RANK()
├── queries/pelanggan-teratas.sql   -- pelanggan berdasarkan total belanja
├── queries/distribusi-status.sql   -- sebaran status seluruh pesanan
└── tests/pemeriksaan.sql           -- pemeriksaan akhir, mandiri dan deterministik

Diagram berikut merangkum struktur data yang kita bangun — dua relasi bertemu di tabel detail_pesanan sebagai jembatan:

Relasi pelanggan–pesanan berjenis satu-ke-banyak: satu pelanggan boleh membuat banyak pesanan. Sebaliknya, produk dan pesanan berhubungan banyak-ke-banyak — satu pesanan memuat banyak produk, dan satu produk muncul di banyak pesanan — sehingga membutuhkan tabel detail_pesanan sebagai perantara. Tabel perantara inilah yang menyimpan kolom harga_satuan, snapshot harga yang disepakati saat pesanan dibuat; kolom ini menjadi kunci kejujuran laporan keuangan toko. Detail rancangan tiap kolom kita bahas tuntas di Bagian 1.

Mulailah dari Bagian 1 dan ikuti urutannya, karena setiap bagian dibangun di atas konsep dan data dari bagian sebelumnya.

# Bagian 1: Perancangan Database

Analisis kebutuhan toko online kecil, identifikasi entitas pelanggan, produk, pesanan, dan detail pesanan, lalu tulis skema penjualan_db lengkap dengan PK komposit, ENUM status, foreign key, dan snapshot harga — diverifikasi dengan SHOW TABLES dan DESC.

Buka → # Bagian 2: Pengelolaan Produk dan Pelanggan

Isi data awal katalog produk dan pelanggan, jalankan operasi CRUD sehari-hari toko online, dan buktikan constraint skema menolak email ganda serta pesanan dari pelanggan yang tidak terdaftar.

Buka → # Bagian 3: Transaksi Penjualan

Jalankan alur pesanan toko online dalam transaksi MySQL: buat pesanan beserta rinciannya dan kurangi stok dalam satu blok, hitung total dari detail, ubah status dengan guard, batalkan pesanan dengan memulihkan stok, dan uji penolakan stok negatif.

Buka → # Bagian 4: Analisis Penjualan

Susun analisis bisnis dari dua bulan penjualan — omzet per bulan dengan DATE_FORMAT, produk terlaris dengan RANK(), pelanggan teratas, dan distribusi status pesanan — lalu jalankan berkas pemeriksaan akhir yang menguji seluruh fitur proyek.

Buka →

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

database/data-awal.sql

Unduh berkas
sql 
-- data-awal.sql — Proyek Database Penjualan
-- Tujuan: mengisi data contoh deterministik (10 produk dan 8 pelanggan
--   fiktif) sebagai titik awal konsisten untuk seluruh bagian proyek.
-- Cara pakai: jalankan setelah skema.sql:
--   mysql -u root -p penjualan_db < database/data-awal.sql

INSERT INTO pelanggan (nama, email, kota) VALUES
  ('Budi Santoso', '[email protected]', 'Bandung'),
  ('Siti Rahmawati', '[email protected]', 'Jakarta'),
  ('Andi Wijaya', '[email protected]', 'Surabaya'),
  ('Dewi Lestari', '[email protected]', 'Yogyakarta'),
  ('Rina Marlina', '[email protected]', 'Bandung'),
  ('Tono Sutrisno', '[email protected]', 'Medan'),
  ('Sari Puspita', '[email protected]', 'Semarang'),
  ('Joko Prasetyo', '[email protected]', 'Denpasar');

INSERT INTO produk (sku, nama, kategori, harga, stok) VALUES
  ('TS-001', 'Kaos Polos Katun Combed 30s', 'Pakaian', 45000.00, 50),
  ('TS-002', 'Kemeja Flanel Lengan Panjang', 'Pakaian', 95000.00, 30),
  ('JA-001', 'Jaket Hoodie Zipper Unisex', 'Pakaian', 125000.00, 20),
  ('CP-001', 'Celana Chino Slim Fit', 'Pakaian', 85000.00, 35),
  ('SN-001', 'Sneakers Kanvas Klasik', 'Sepatu', 120000.00, 25),
  ('SN-002', 'Sandal Gunung Dewasa', 'Sepatu', 65000.00, 40),
  ('TA-001', 'Tas Ransel Laptop 15.6 inch', 'Tas', 145000.00, 15),
  ('TA-002', 'Totebag Kanvas Belajar', 'Tas', 35000.00, 60),
  ('AK-001', 'Topi Baseball Hitam', 'Aksesoris', 28000.00, 45),
  ('AK-002', 'Ikat Pinggang Kulit Sintetis', 'Aksesoris', 55000.00, 25);

database/skema.sql

Unduh berkas
sql 
-- skema.sql — Proyek Database Penjualan
-- Tujuan: membuat database penjualan_db beserta keempat tabelnya
--   (pelanggan, produk, pesanan, detail_pesanan) lengkap dengan primary key,
--   kolom unik email/sku, foreign key bernama, ENUM status, PK komposit,
--   dan check constraint stok/jumlah.
-- Cara pakai: mysql -u root -p < database/skema.sql

DROP DATABASE IF EXISTS penjualan_db;
CREATE DATABASE penjualan_db;
USE penjualan_db;

-- Pelanggan toko online; email unik sebagai identitas kontak.
CREATE TABLE pelanggan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  email VARCHAR(80) NOT NULL UNIQUE,
  kota VARCHAR(30) NOT NULL
);

-- Katalog produk; harga DECIMAL agar nilai rupiah pasti.
CREATE TABLE produk (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(15) NOT NULL UNIQUE,
  nama VARCHAR(100) NOT NULL,
  kategori VARCHAR(30) NOT NULL,
  harga DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  stok INT NOT NULL DEFAULT 0,
  CONSTRAINT chk_produk_stok CHECK (stok >= 0)
);

-- Satu baris per pesanan; status mengikuti daur hidup pesanan.
CREATE TABLE pesanan (
  no_pesanan INT AUTO_INCREMENT PRIMARY KEY,
  pelanggan_id INT NOT NULL,
  tanggal DATE NOT NULL,
  status ENUM('baru','dibayar','dikirim','selesai','dibatalkan') NOT NULL DEFAULT 'baru',
  CONSTRAINT fk_pesanan_pelanggan
    FOREIGN KEY (pelanggan_id) REFERENCES pelanggan(id)
);

-- Rincian produk per pesanan; harga_satuan adalah snapshot harga saat
-- pesanan dibuat, bukan harga terbaru di tabel produk.
CREATE TABLE detail_pesanan (
  pesanan_id INT NOT NULL,
  produk_id INT NOT NULL,
  jumlah INT NOT NULL,
  harga_satuan DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (pesanan_id, produk_id),
  CONSTRAINT chk_detail_jumlah CHECK (jumlah > 0),
  CONSTRAINT fk_detail_pesanan
    FOREIGN KEY (pesanan_id) REFERENCES pesanan(no_pesanan),
  CONSTRAINT fk_detail_produk
    FOREIGN KEY (produk_id) REFERENCES produk(id)
);

queries/distribusi-status.sql

Unduh berkas
sql 
-- distribusi-status.sql — Proyek Database Penjualan
-- Tujuan: menghitung banyaknya pesanan pada tiap status daur hidup
--   (baru, dibayar, dikirim, selesai, dibatalkan).
-- Cara pakai: mysql -u root -p penjualan_db < queries/distribusi-status.sql

SELECT status, COUNT(*) AS jumlah_pesanan
FROM pesanan
GROUP BY status
ORDER BY status;

queries/omzet-per-bulan.sql

Unduh berkas
sql 
-- omzet-per-bulan.sql — Proyek Database Penjualan
-- Tujuan: menjumlahkan omzet per bulan dari rincian pesanan yang statusnya
--   sudah pasti menghasilkan uang (dibayar, dikirim, selesai) memakai
--   DATE_FORMAT atas tanggal literal dan harga_satuan snapshot.
-- Cara pakai: mysql -u root -p penjualan_db < queries/omzet-per-bulan.sql

SELECT DATE_FORMAT(p.tanggal, '%Y-%m') AS bulan,
       SUM(d.jumlah * d.harga_satuan) AS omzet
FROM pesanan p
JOIN detail_pesanan d ON d.pesanan_id = p.no_pesanan
WHERE p.status IN ('dibayar','dikirim','selesai')
GROUP BY DATE_FORMAT(p.tanggal, '%Y-%m')
ORDER BY bulan;

queries/pelanggan-teratas.sql

Unduh berkas
sql 
-- pelanggan-teratas.sql — Proyek Database Penjualan
-- Tujuan: menampilkan pelanggan berdasarkan jumlah pesanan aktif dan total
--   belanja (jumlah x harga_satuan snapshot); pesanan dibatalkan dikecualikan.
--   Pelanggan tanpa transaksi tetap tampil dengan nilai nol.
-- Cara pakai: mysql -u root -p penjualan_db < queries/pelanggan-teratas.sql

SELECT pl.nama, pl.kota,
       COUNT(DISTINCT p.no_pesanan) AS jumlah_pesanan,
       COALESCE(SUM(d.jumlah * d.harga_satuan), 0) AS total_belanja
FROM pelanggan pl
LEFT JOIN pesanan p ON p.pelanggan_id = pl.id AND p.status <> 'dibatalkan'
LEFT JOIN detail_pesanan d ON d.pesanan_id = p.no_pesanan
GROUP BY pl.id, pl.nama, pl.kota
ORDER BY total_belanja DESC, pl.nama;

queries/produk-terlaris.sql

Unduh berkas
sql 
-- produk-terlaris.sql — Proyek Database Penjualan
-- Tujuan: memeringkat produk berdasarkan total unit terjual (pesanan aktif,
--   termasuk yang berstatus baru) memakai window function RANK().
--   Produk tanpa penjualan tetap tampil dengan angka nol.
-- Cara pakai: mysql -u root -p penjualan_db < queries/produk-terlaris.sql

SELECT p.sku, p.nama,
       COALESCE(SUM(dj.jumlah), 0) AS total_terjual,
       RANK() OVER (ORDER BY COALESCE(SUM(dj.jumlah), 0) DESC) AS peringkat
FROM produk p
LEFT JOIN (
  SELECT d.produk_id, d.jumlah
  FROM detail_pesanan d
  JOIN pesanan pe ON pe.no_pesanan = d.pesanan_id
  WHERE pe.status <> 'dibatalkan'
) dj ON dj.produk_id = p.id
GROUP BY p.id, p.sku, p.nama
ORDER BY total_terjual DESC, p.sku;

tests/pemeriksaan.sql

Unduh berkas
sql 
-- pemeriksaan.sql — Proyek Database Penjualan
-- Tujuan: pemeriksaan akhir seluruh fitur proyek (pembuatan pesanan atomik,
--   penghitungan total, perubahan status, pembatalan yang memulihkan stok,
--   omzet per bulan, produk terlaris, dan distribusi status).
--   Berkas ini MANDIRI: membangun skenario uji kecilnya sendiri,
--   lalu membersihkan database setelah selesai.
-- Cara pakai: mysql -u root -p < tests/pemeriksaan.sql
-- Ekspektasi tiap hasil ditulis sebagai komentar di atas query terkait.

DROP DATABASE IF EXISTS penjualan_db;
CREATE DATABASE penjualan_db;
USE penjualan_db;

CREATE TABLE pelanggan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  email VARCHAR(80) NOT NULL UNIQUE,
  kota VARCHAR(30) NOT NULL
);
CREATE TABLE produk (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(15) NOT NULL UNIQUE,
  nama VARCHAR(100) NOT NULL,
  kategori VARCHAR(30) NOT NULL,
  harga DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  stok INT NOT NULL DEFAULT 0,
  CONSTRAINT chk_produk_stok CHECK (stok >= 0)
);
CREATE TABLE pesanan (
  no_pesanan INT AUTO_INCREMENT PRIMARY KEY,
  pelanggan_id INT NOT NULL,
  tanggal DATE NOT NULL,
  status ENUM('baru','dibayar','dikirim','selesai','dibatalkan') NOT NULL DEFAULT 'baru',
  CONSTRAINT fk_pesanan_pelanggan
    FOREIGN KEY (pelanggan_id) REFERENCES pelanggan(id)
);
CREATE TABLE detail_pesanan (
  pesanan_id INT NOT NULL,
  produk_id INT NOT NULL,
  jumlah INT NOT NULL,
  harga_satuan DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (pesanan_id, produk_id),
  CONSTRAINT chk_detail_jumlah CHECK (jumlah > 0),
  CONSTRAINT fk_detail_pesanan
    FOREIGN KEY (pesanan_id) REFERENCES pesanan(no_pesanan),
  CONSTRAINT fk_detail_produk
    FOREIGN KEY (produk_id) REFERENCES produk(id)
);

INSERT INTO pelanggan (nama, email, kota) VALUES
  ('Budi Santoso', '[email protected]', 'Bandung'),
  ('Siti Rahmawati', '[email protected]', 'Jakarta'),
  ('Andi Wijaya', '[email protected]', 'Surabaya');
INSERT INTO produk (sku, nama, kategori, harga, stok) VALUES
  ('TS-001', 'Kaos Polos Katun Combed 30s', 'Pakaian', 45000.00, 20),
  ('TA-002', 'Totebag Kanvas Belajar', 'Tas', 35000.00, 15),
  ('AK-001', 'Topi Baseball Hitam', 'Aksesoris', 28000.00, 10);

-- Pemeriksaan 1: pesanan dibuat atomik - rincian masuk, stok berkurang.
START TRANSACTION;
INSERT INTO pesanan (pelanggan_id, tanggal, status)
  VALUES (2, '2026-01-10', 'baru');
INSERT INTO detail_pesanan (pesanan_id, produk_id, jumlah, harga_satuan) VALUES
  (LAST_INSERT_ID(), 1, 2, 45000.00),
  (LAST_INSERT_ID(), 3, 1, 28000.00);
UPDATE produk SET stok = stok - 2 WHERE id = 1;
UPDATE produk SET stok = stok - 1 WHERE id = 3;
COMMIT;
-- Ekspektasi: TS-001 stok 18, AK-001 stok 9
SELECT sku, stok FROM produk WHERE id IN (1, 3) ORDER BY id;

-- Pemeriksaan 2: total pesanan dihitung dari rincian (SUM jumlah x harga).
-- Ekspektasi: pesanan 1 total 118000.00
SELECT d.pesanan_id, SUM(d.jumlah * d.harga_satuan) AS total
FROM detail_pesanan d
GROUP BY d.pesanan_id
ORDER BY d.pesanan_id;

-- Pemeriksaan 3: status pesanan berubah dari baru menjadi dibayar.
UPDATE pesanan SET status = 'dibayar' WHERE no_pesanan = 1 AND status = 'baru';
-- Ekspektasi: pesanan 1 status dibayar
SELECT no_pesanan, status FROM pesanan WHERE no_pesanan = 1;

-- Pemeriksaan 4: pembatalan pesanan mengembalikan status dan stok.
START TRANSACTION;
INSERT INTO pesanan (pelanggan_id, tanggal, status)
  VALUES (3, '2026-01-15', 'baru');
INSERT INTO detail_pesanan (pesanan_id, produk_id, jumlah, harga_satuan)
  VALUES (2, 2, 1, 35000.00);
UPDATE produk SET stok = stok - 1 WHERE id = 2;
COMMIT;
START TRANSACTION;
UPDATE pesanan SET status = 'dibatalkan' WHERE no_pesanan = 2 AND status = 'baru';
UPDATE produk SET stok = stok + 1 WHERE id = 2;
COMMIT;
-- Ekspektasi: pesanan 2 dibatalkan, TA-002 stok pulih ke 15
SELECT no_pesanan, status FROM pesanan WHERE no_pesanan = 2;
SELECT sku, stok FROM produk WHERE id = 2;

-- Pemeriksaan 5: pesanan ketiga (Budi) selesai di bulan Februari.
START TRANSACTION;
INSERT INTO pesanan (pelanggan_id, tanggal, status)
  VALUES (1, '2026-02-05', 'baru');
INSERT INTO detail_pesanan (pesanan_id, produk_id, jumlah, harga_satuan)
  VALUES (3, 1, 1, 45000.00);
UPDATE produk SET stok = stok - 1 WHERE id = 1;
COMMIT;
UPDATE pesanan SET status = 'selesai' WHERE no_pesanan = 3 AND status = 'baru';
-- Ekspektasi: pesanan 3 selesai
SELECT no_pesanan, status FROM pesanan WHERE no_pesanan = 3;

-- Pemeriksaan 6: omzet per bulan hanya dari pesanan yang menghasilkan uang.
-- Ekspektasi: 2026-01 omzet 118000.00 (pesanan 2 dibatalkan dikecualikan),
--   2026-02 omzet 45000.00
SELECT DATE_FORMAT(p.tanggal, '%Y-%m') AS bulan,
       SUM(d.jumlah * d.harga_satuan) AS omzet
FROM pesanan p
JOIN detail_pesanan d ON d.pesanan_id = p.no_pesanan
WHERE p.status IN ('dibayar','dikirim','selesai')
GROUP BY DATE_FORMAT(p.tanggal, '%Y-%m')
ORDER BY bulan;

-- Pemeriksaan 7: produk terlaris dengan RANK(); pesanan dibatalkan dikecualikan.
-- Ekspektasi: TS-001 3 unit peringkat 1, AK-001 1 unit peringkat 2,
--   TA-002 0 unit peringkat 3
SELECT p.sku,
       COALESCE(SUM(dj.jumlah), 0) AS total_terjual,
       RANK() OVER (ORDER BY COALESCE(SUM(dj.jumlah), 0) DESC) AS peringkat
FROM produk p
LEFT JOIN (
  SELECT d.produk_id, d.jumlah
  FROM detail_pesanan d
  JOIN pesanan pe ON pe.no_pesanan = d.pesanan_id
  WHERE pe.status <> 'dibatalkan'
) dj ON dj.produk_id = p.id
GROUP BY p.id, p.sku
ORDER BY total_terjual DESC, p.sku;

-- Pemeriksaan 8: distribusi status seluruh pesanan.
-- Ekspektasi: dibatalkan 1, dibayar 1, selesai 1
SELECT status, COUNT(*) AS jumlah_pesanan
FROM pesanan
GROUP BY status
ORDER BY status;

DROP DATABASE penjualan_db;