Dua proyek sebelumnya membangun database perpustakaan dan inventaris gudang. Proyek ketiga membawa dua tantangan yang belum kita hadapi sekaligus: uang dan waktu. Harga jual sebuah produk berubah dari bulan ke bulan, padahal pesanan lama harus tetap melaporkan angka yang benar; stok terbatas, padahal dua kasir bisa melayani pesanan besar pada saat bersamaan. Pada proyek ini kita membangun database penjualan ritel dari nol: katalog produk dengan dua harga (beli dan jual), data pelanggan, transaksi pesanan multi-item yang atomik, hingga analisis penjualan yang mengubah catatan transaksi menjadi keputusan bisnis.

Yang Akan Kita Bangun #

Toko Maju Jaya adalah toko ritel kecil yang melayani pembeli langsung dan pesanan pelanggan tetap. Kebutuhan hariannya kita tuangkan menjadi database dengan fitur berikut:

  • Katalog produk dengan kode unik yang terbaca manusia, kategori, harga_beli, harga_jual, dan stok — kolom stok dijaga CHECK (stok >= 0) sehingga database tidak pernah menyimpan stok negatif.
  • Data master pelanggan dengan email unik sebagai identitas kontak dan kota untuk segmentasi.
  • Pesanan multi-item: tabel kepala pesanan (siapa, kapan, status apa) dipasangkan dengan tabel detail pesanan_item (produk apa, berapa qty).
  • Pola snapshot harga: kolom harga_saat_transaksi membekukan harga jual saat pesanan dibuat, sehingga perubahan harga di katalog tidak pernah merusak nilai pesanan lama; subtotal dihitung otomatis sebagai generated column.
  • Status pesanan yang tertutup: hanya baru, diproses, selesai, dan batal yang diterima, dijaga CHECK dengan nilai awal baru.
  • Transaksi atomik: kepala pesanan, seluruh item, dan pengurangan stok terjadi bersama-sama atau tidak sama sekali; percobaan menjual melebihi stok ditolak tepat saat transaksi berjalan.
  • Analisis penjualan: omzet bulanan dengan date_trunc dan generate_series, produk terlaris dengan dua ukuran peringkat, pelanggan terbaik, analisis keranjang belanja lewat self-join, dan margin laba kotor per produk.
  • Berkas uji dengan pola LULUS/GAGAL yang membuktikan setiap aturan bisnis ditegakkan oleh constraint, bukan oleh niat baik aplikasi.

Seluruh contoh memakai data deterministik dengan tanggal tetap sepanjang kuartal I 2026, sehingga setiap output yang Anda lihat dapat dicocokkan persis dengan hasil eksekusi Anda sendiri.

Skema Database #

Kebutuhan di atas diterjemahkan menjadi lima tabel. Diagram berikut memperlihatkan relasi antartabelnya:

Notasi ||--o{ dibaca “satu ke nol-atau-banyak”: satu kategori menampung banyak produk, satu pelanggan boleh berkali-kali memesan. Relasi yang paling penting justru relasi tak langsung antara pelanggan dan produk — keduanya hanya bertemu di pesanan_item, tabel detail yang menjadi bukti transaksi. Dua kolom di tabel inilah yang membedakan proyek ini dari dua proyek sebelumnya: harga_saat_transaksi merekam harga saat pesanan terjadi (harga di katalog boleh berubah setelahnya), dan subtotal dihitung PostgreSQL sendiri dari qty * harga_saat_transaksi sehingga tidak ada dua sumber kebenaran yang bisa saling bertentangan.

Prasyarat #

Proyek ini tidak memperkenalkan fitur baru; ia menyusun ulang materi yang sudah Anda pelajari. Pastikan Anda nyaman dengan chapter berikut:

Tidak ada extension atau tool tambahan yang dibutuhkan; cukup psql dan PostgreSQL 17 yang sudah terpasang sejak Instalasi PostgreSQL .

Peta Bagian #

Proyek dikerjakan dalam empat bagian berurutan. Setiap bagian menghasilkan database yang benar-benar bekerja pada tahapnya:

BagianFokus
Bagian 1: Perancangan DatabaseDari cerita kebutuhan ke lima tabel: constraint bisnis, pola snapshot harga, index, dan uji skema
Bagian 2: Pengelolaan Produk dan PelangganCRUD data master: RETURNING, pencarian ILIKE, upsert dari sistem pusat, validasi masukan, dan aturan penghapusan
Bagian 3: Transaksi PenjualanPesanan multi-item atomik, penolakan overselling, bukti pemulihan, dan alur status pesanan
Bagian 4: Analisis PenjualanOmzet bulanan, produk terlaris, pelanggan terbaik, analisis keranjang, dan margin laba kotor

Setiap bagian mandiri: berkas SQL-nya membangun ulang tabel yang dibutuhkan di awal, sehingga Anda bisa masuk dari bagian mana pun tanpa membawa bekas eksekusi bagian sebelumnya.

Cara Mengikuti Proyek #

Kerjakan tiap bagian sambil mengetik ulang atau menyalin berkas SQL-nya, lalu jalankan dengan psql seperti pada chapter sebelumnya:

bash 
psql -X -f bagian/perancangan-database/buat-tabel.sql -d penjualan

Aturan mainnya sama dengan seluruh Course: setiap blok kode pada bagian-bagian berikut adalah berkas nyata yang dieksekusi sungguhan, dan blok Output yang Dihasilkan di bawahnya adalah hasil eksekusi tersebut, bukan tulisan tangan. Nilai-nilai kunci sengaja dipaku — tanggal pesanan memakai literal tetap seperti DATE '2026-04-05', harga ditulis dalam rupiah penuh — agar hasil Anda identik dengan hasil di halaman.

Selain berkas per bagian, halaman overview ini juga membawa artefak lengkap hasil akhir proyek yang dapat diunduh dari bagian Berkas Sumber di bawah halaman ini: database/skema.sql (skema final), database/isi-data.sql (data master dan seluruh riwayat pesanan kuartal I 2026), queries/ (lima query analisis siap pakai), dan tests/ (dua berkas uji LULUS/GAGAL). Berkas-berkas itu berguna setelah Anda menyelesaikan keempat bagian: terapkan kedua berkas database/ ke database kosong, jalankan berkas uji, lalu bandingkan hasilnya.

Skema adalah fondasi semuanya — dan keputusan termahal proyek ini, pola snapshot harga, diambil tepat di sana. Mari kita mulai dari Bagian 1.

Referensi #

# Bagian 1: Perancangan Database

Rancang tabel produk, pelanggan, dan transaksi dengan detail order.

Buka → # Bagian 2: Pengelolaan Produk dan Pelanggan

Kelola data master produk dan pelanggan beserta kategorinya.

Buka → # Bagian 3: Transaksi Penjualan

Proses order multi-item dengan transaksi, cek stok, dan perhitungan total.

Buka → # Bagian 4: Analisis Penjualan

Analisis penjualan dengan agregasi, window function, dan laporan periodik.

Buka →

Berkas Sumber

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

database/isi-data.sql

Unduh berkas
sql 
-- isi-data.sql — data contoh lengkap: data master + riwayat dua belas
-- pesanan sepanjang kuartal I 2026 (29 item). Hasil akhirnya identik
-- dengan kondisi Bagian 4: stok sudah dikurangi penjualan kuartal.
-- Jalankan setelah database/skema.sql pada database kosong:
--   psql -X -f database/skema.sql -f database/isi-data.sql
-- Stok yang dimuat adalah stok akhir kuartal ditambah total terjual,
-- sehingga setelah pengurangan di akhir berkas papan stok berada pada
-- kondisi akhir kuartal. Pesanan 21 Februari memuat harga promo Kopi
-- 11500 sebagai contoh snapshot harga_saat_transaksi.

INSERT INTO kategori (nama) VALUES
    ('Sembako'),
    ('Minuman'),
    ('Makanan'),
    ('Kebersihan');

INSERT INTO produk (kode, nama, kategori_id, harga_beli, harga_jual, stok) VALUES
    ('PRD-001', 'Beras Premium 5kg',        1, 61000.00, 68000.00,  48),
    ('PRD-002', 'Gula Pasir 1kg',           1, 15000.00, 17000.00,  80),
    ('PRD-003', 'Minyak Goreng 2L',         1, 32000.00, 36500.00,  42),
    ('PRD-004', 'Telur Ayam 1kg',           1, 27000.00, 31000.00,  34),
    ('PRD-005', 'Kopi Sachet Instan',       2,  9000.00, 12000.00, 126),
    ('PRD-006', 'Teh Celup 50 kantong',     2, 11000.00, 13500.00,  47),
    ('PRD-007', 'Air Mineral 600ml',        2,  2500.00,  4000.00, 246),
    ('PRD-008', 'Mie Instan Goreng',        3,  2800.00,  3500.00, 170),
    ('PRD-009', 'Biskuit Cokelat',          3,  7500.00,  9500.00,  35),
    ('PRD-010', 'Sabun Cuci Piring 780ml',  4, 16000.00, 19500.00,  27);

INSERT INTO pelanggan (nama, email, kota) VALUES
    ('Budi Santoso', '[email protected]',  'Bandung'),
    ('Siti Rahayu',  '[email protected]',  'Bandung'),
    ('Andi Wijaya',  '[email protected]',  'Jakarta'),
    ('Dewi Lestari', '[email protected]',  'Surabaya'),
    ('Rudi Hartono', '[email protected]',  'Bandung'),
    ('Maya Putri',   '[email protected]',  'Jakarta');

BEGIN;

INSERT INTO pesanan (pelanggan_id, tanggal, status) VALUES
    (2, DATE '2026-01-05', 'selesai'),
    (1, DATE '2026-01-12', 'selesai'),
    (3, DATE '2026-01-25', 'selesai'),
    (2, DATE '2026-02-03', 'selesai'),
    (4, DATE '2026-02-08', 'selesai'),
    (1, DATE '2026-02-15', 'selesai'),
    (5, DATE '2026-02-21', 'selesai'),
    (2, DATE '2026-03-02', 'selesai'),
    (6, DATE '2026-03-09', 'selesai'),
    (3, DATE '2026-03-16', 'selesai'),
    (4, DATE '2026-03-23', 'selesai'),
    (1, DATE '2026-03-30', 'selesai');

INSERT INTO pesanan_item (pesanan_id, produk_id, qty, harga_saat_transaksi)
SELECT p.id, pr.id, v.qty, v.harga
FROM (VALUES
    ('2026-01-05', 2, 'PRD-005', 10, 12000.00),
    ('2026-01-05', 2, 'PRD-002',  5, 17000.00),
    ('2026-01-12', 1, 'PRD-001',  2, 68000.00),
    ('2026-01-12', 1, 'PRD-003',  3, 36500.00),
    ('2026-01-25', 3, 'PRD-008', 20, 3500.00),
    ('2026-01-25', 3, 'PRD-007', 24, 4000.00),
    ('2026-02-03', 2, 'PRD-005', 12, 12000.00),
    ('2026-02-03', 2, 'PRD-002',  4, 17000.00),
    ('2026-02-03', 2, 'PRD-006',  2, 13500.00),
    ('2026-02-08', 4, 'PRD-010',  3, 19500.00),
    ('2026-02-08', 4, 'PRD-007', 12, 4000.00),
    ('2026-02-15', 1, 'PRD-001',  1, 68000.00),
    ('2026-02-15', 1, 'PRD-004',  5, 31000.00),
    ('2026-02-15', 1, 'PRD-008', 10, 3500.00),
    ('2026-02-21', 5, 'PRD-005',  8, 11500.00),
    ('2026-02-21', 5, 'PRD-007', 30, 4000.00),
    ('2026-03-02', 2, 'PRD-008', 25, 3500.00),
    ('2026-03-02', 2, 'PRD-007', 30, 4000.00),
    ('2026-03-09', 6, 'PRD-001',  3, 68000.00),
    ('2026-03-09', 6, 'PRD-002',  6, 17000.00),
    ('2026-03-09', 6, 'PRD-005',  6, 12000.00),
    ('2026-03-16', 3, 'PRD-008', 15, 3500.00),
    ('2026-03-16', 3, 'PRD-009',  5, 9500.00),
    ('2026-03-23', 4, 'PRD-003',  4, 36500.00),
    ('2026-03-23', 4, 'PRD-004',  4, 31000.00),
    ('2026-03-23', 4, 'PRD-010',  2, 19500.00),
    ('2026-03-30', 1, 'PRD-001',  2, 68000.00),
    ('2026-03-30', 1, 'PRD-005', 10, 12000.00),
    ('2026-03-30', 1, 'PRD-002',  5, 17000.00)
) AS v(tanggal, pelanggan_id, kode_produk, qty, harga)
JOIN pesanan p ON p.pelanggan_id = v.pelanggan_id
              AND p.tanggal = v.tanggal::date
JOIN produk pr ON pr.kode = v.kode_produk;

-- kurangi stok seluruh produk yang terjual (pola set-based)
UPDATE produk pr
SET stok = pr.stok - d.qty_terjual
FROM (
    SELECT produk_id, SUM(qty) AS qty_terjual
    FROM pesanan_item
    GROUP BY produk_id
) d
WHERE d.produk_id = pr.id;

COMMIT;

-- ringkasan kondisi akhir untuk pemeriksaan cepat
SELECT count(DISTINCT p.id) AS jumlah_pesanan,
       count(i.id)          AS jumlah_item,
       sum(i.subtotal)      AS total_omzet
FROM pesanan p
JOIN pesanan_item i ON i.pesanan_id = p.id;

database/skema.sql

Unduh berkas
sql 
-- skema.sql — skema lengkap database penjualan ritel Toko Maju Jaya.
-- Terapkan pada database kosong:
--   psql -X -f database/skema.sql
-- Isi data contoh dengan database/isi-data.sql setelahnya.
-- Skema ini adalah hasil Bagian 1: lima tabel, constraint bisnis,
-- index jalur laporan, dan dokumentasi kolom lewat COMMENT ON.

CREATE TABLE kategori (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text   NOT NULL UNIQUE
);

CREATE TABLE produk (
    id          integer        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kode        text           NOT NULL UNIQUE,
    nama        text           NOT NULL,
    kategori_id integer        NOT NULL REFERENCES kategori (id),
    harga_beli  numeric(12, 2) NOT NULL CHECK (harga_beli > 0),
    harga_jual  numeric(12, 2) NOT NULL CHECK (harga_jual > 0),
    stok        integer        NOT NULL CHECK (stok >= 0)
);

CREATE TABLE pelanggan (
    id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama  text   NOT NULL,
    email text   NOT NULL UNIQUE,
    kota  text   NOT NULL
);

CREATE TABLE pesanan (
    id           integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pelanggan_id integer NOT NULL REFERENCES pelanggan (id),
    tanggal      date    NOT NULL,
    status       text    NOT NULL DEFAULT 'baru'
                 CHECK (status IN ('baru', 'diproses', 'selesai', 'batal'))
);

CREATE TABLE pesanan_item (
    id                   integer        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pesanan_id           integer        NOT NULL REFERENCES pesanan (id) ON DELETE CASCADE,
    produk_id            integer        NOT NULL REFERENCES produk (id),
    qty                  integer        NOT NULL CHECK (qty > 0),
    harga_saat_transaksi numeric(12, 2) NOT NULL,
    subtotal             numeric(12, 2) GENERATED ALWAYS AS (qty * harga_saat_transaksi) STORED
);

CREATE INDEX idx_pesanan_tanggal       ON pesanan (tanggal);
CREATE INDEX idx_pesanan_pelanggan     ON pesanan (pelanggan_id);
CREATE INDEX idx_pesanan_item_pesanan  ON pesanan_item (pesanan_id);
CREATE INDEX idx_pesanan_item_produk   ON pesanan_item (produk_id);

COMMENT ON TABLE kategori IS 'Kelompok produk, mis. Sembako atau Minuman.';
COMMENT ON TABLE produk IS 'Katalog produk beserta harga beli, harga jual, dan stok.';
COMMENT ON COLUMN produk.kode IS 'Kode produk yang terbaca manusia pada label rak, mis. PRD-001.';
COMMENT ON COLUMN produk.harga_beli IS 'Harga modal terakhir dari pemasok.';
COMMENT ON COLUMN produk.stok IS 'Stok tersedia kini; CHECK (stok >= 0) menolak overselling.';
COMMENT ON TABLE pelanggan IS 'Pelanggan terdaftar Toko Maju Jaya.';
COMMENT ON TABLE pesanan IS 'Kepala transaksi: siapa memesan, kapan, dan statusnya.';
COMMENT ON COLUMN pesanan.status IS 'Salah satu dari baru/diproses/selesai/batal; awal baru.';
COMMENT ON TABLE pesanan_item IS 'Baris produk dalam satu pesanan.';
COMMENT ON COLUMN pesanan_item.harga_saat_transaksi IS 'Snapshot harga jual saat pesanan dibuat; tidak ikut berubah saat harga katalog dikoreksi.';
COMMENT ON COLUMN pesanan_item.subtotal IS 'Dihitung PostgreSQL: qty * harga_saat_transaksi.';

queries/analisis-keranjang.sql

Unduh berkas
sql 
-- analisis-keranjang.sql — pasangan produk yang paling sering dibeli
-- bersama dalam satu pesanan (market basket analysis).
-- Triknya: SELF JOIN pesanan_item ke dirinya sendiri pada pesanan yang
-- sama, dengan syarat i1.produk_id < i2.produk_id agar tiap pasangan
-- hanya dihitung sekali dan produk tidak dipasangkan dengan dirinya.
-- Jalankan pada database hasil database/skema.sql + database/isi-data.sql.

SELECT p1.nama AS produk_a,
       p2.nama AS produk_b,
       count(DISTINCT i1.pesanan_id) AS sering_dibeli_bersama
FROM pesanan_item i1
JOIN pesanan_item i2
     ON i1.pesanan_id = i2.pesanan_id
    AND i1.produk_id < i2.produk_id
JOIN produk p1 ON p1.id = i1.produk_id
JOIN produk p2 ON p2.id = i2.produk_id
GROUP BY p1.nama, p2.nama
HAVING count(DISTINCT i1.pesanan_id) >= 2
ORDER BY sering_dibeli_bersama DESC, p1.nama, p2.nama;

queries/margin-laba-kotor.sql

Unduh berkas
sql 
-- margin-laba-kotor.sql — laba kotor per produk: selisih harga jual saat
-- transaksi dengan harga beli. Pesanan promo ikut terhitung benar karena
-- margin memakai kolom snapshot harga_saat_transaksi, bukan harga jual
-- terkini di tabel produk.
-- Jalankan pada database hasil database/skema.sql + database/isi-data.sql.

SELECT pr.kode,
       pr.nama,
       k.nama AS kategori,
       sum(i.qty)      AS qty_terjual,
       sum(i.subtotal) AS omzet,
       sum((i.harga_saat_transaksi - pr.harga_beli) * i.qty) AS margin,
       round(100.0
             * sum((i.harga_saat_transaksi - pr.harga_beli) * i.qty)
             / sum(i.subtotal), 1) AS rasio_margin_pct
FROM pesanan_item i
JOIN produk pr  ON pr.id = i.produk_id
JOIN kategori k ON k.id = pr.kategori_id
JOIN pesanan p  ON p.id = i.pesanan_id AND p.status = 'selesai'
GROUP BY pr.id, pr.kode, pr.nama, k.nama
ORDER BY margin DESC, pr.kode;

queries/omzet-bulanan.sql

Unduh berkas
sql 
-- omzet-bulanan.sql — omzet per bulan dengan date_trunc, rata-rata per
-- pesanan, omzet kumulatif dengan window function, dan laporan semester
-- yang menampilkan bulan tanpa penjualan (generate_series + LEFT JOIN).
-- Jalankan pada database hasil database/skema.sql + database/isi-data.sql.

WITH bulanan AS (
    SELECT date_trunc('month', p.tanggal) AS bulan,
           count(DISTINCT p.id)            AS jumlah_pesanan,
           sum(i.subtotal)                 AS omzet
    FROM pesanan p
    JOIN pesanan_item i ON i.pesanan_id = p.id
    WHERE p.status = 'selesai'
    GROUP BY 1
)
SELECT to_char(bulan, 'YYYY-MM')      AS bulan,
       jumlah_pesanan,
       omzet,
       round(omzet / jumlah_pesanan, 2) AS rata_per_pesanan,
       sum(omzet) OVER (ORDER BY bulan) AS omzet_kumulatif
FROM bulanan
ORDER BY bulan;

-- Laporan semester: bulan tanpa penjualan tetap tampil dengan angka nol.
SELECT to_char(b.bulan, 'YYYY-MM')     AS bulan,
       count(DISTINCT p.id)            AS jumlah_pesanan,
       COALESCE(sum(i.subtotal), 0.00) AS omzet
FROM generate_series(DATE '2026-01-01', DATE '2026-06-01', interval '1 month') AS b(bulan)
LEFT JOIN pesanan p ON date_trunc('month', p.tanggal) = b.bulan
                   AND p.status = 'selesai'
LEFT JOIN pesanan_item i ON i.pesanan_id = p.id
GROUP BY b.bulan
ORDER BY b.bulan;

queries/pelanggan-terbaik.sql

Unduh berkas
sql 
-- pelanggan-terbaik.sql — pelanggan dengan total belanja terbesar,
-- lengkap dengan jumlah pesanan dan peringkatnya.
-- Jalankan pada database hasil database/skema.sql + database/isi-data.sql.

SELECT pl.nama,
       pl.kota,
       count(DISTINCT p.id) AS jumlah_pesanan,
       sum(i.subtotal)      AS total_belanja,
       rank() OVER (ORDER BY sum(i.subtotal) DESC) AS peringkat
FROM pelanggan pl
JOIN pesanan p     ON p.pelanggan_id = pl.id AND p.status = 'selesai'
JOIN pesanan_item i ON i.pesanan_id = p.id
GROUP BY pl.id, pl.nama, pl.kota
ORDER BY peringkat, pl.nama;

queries/produk-terlaris.sql

Unduh berkas
sql 
-- produk-terlaris.sql — peringkat produk dengan dua ukuran sekaligus:
-- jumlah unit terjual dan omzet. Dua kolom RANK memperlihatkan bahwa
-- peringkat bisa berbeda tergantung ukuran yang dipakai.
-- Jalankan pada database hasil database/skema.sql + database/isi-data.sql.

SELECT pr.kode,
       pr.nama,
       k.nama AS kategori,
       sum(i.qty)      AS qty_terjual,
       sum(i.subtotal) AS omzet,
       rank() OVER (ORDER BY sum(i.qty) DESC)      AS peringkat_qty,
       rank() OVER (ORDER BY sum(i.subtotal) DESC) AS peringkat_omzet
FROM pesanan_item i
JOIN produk pr   ON pr.id = i.produk_id
JOIN kategori k  ON k.id = pr.kategori_id
JOIN pesanan p   ON p.id = i.pesanan_id AND p.status = 'selesai'
GROUP BY pr.id, pr.kode, pr.nama, k.nama
ORDER BY peringkat_omzet, pr.kode;

tests/uji-integritas.sql

Unduh berkas
sql 
-- uji-integritas.sql — uji constraint bisnis pada database terisi.
-- Jalankan pada database yang memuat database/skema.sql dan database/isi-data.sql:
--   psql -X -f database/skema.sql -f database/isi-data.sql -f tests/uji-integritas.sql
-- Lulus bila setiap baris kolom status bernilai LULUS.
-- Kode produk uji memakai rentang PRD-9xx agar mudah dideteksi bila bocor.

CREATE TEMP TABLE hasil_uji (
    nomor      integer,
    kasus      text,
    diharapkan text,
    aktual     text,
    status     text
);

DO $$
DECLARE
    v_state text;
BEGIN
    -- Kasus 1: email pelanggan duplikat (UNIQUE)
    BEGIN
        INSERT INTO pelanggan (nama, email, kota)
        VALUES ('Budi Kembar', '[email protected]', 'Bandung');
        INSERT INTO hasil_uji VALUES (1, 'email pelanggan duplikat', '23505', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (1, 'email pelanggan duplikat', '23505', v_state,
            CASE WHEN v_state = '23505' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 2: kode produk duplikat (UNIQUE)
    BEGIN
        INSERT INTO produk (kode, nama, kategori_id, harga_beli, harga_jual, stok)
        VALUES ('PRD-001', 'Beras palsu', 1, 61000.00, 68000.00, 5);
        INSERT INTO hasil_uji VALUES (2, 'kode produk duplikat', '23505', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (2, 'kode produk duplikat', '23505', v_state,
            CASE WHEN v_state = '23505' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 3: harga jual nol (CHECK)
    BEGIN
        INSERT INTO produk (kode, nama, kategori_id, harga_beli, harga_jual, stok)
        VALUES ('PRD-901', 'Produk gratis', 3, 5000.00, 0, 5);
        INSERT INTO hasil_uji VALUES (3, 'harga jual nol', '23514', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (3, 'harga jual nol', '23514', v_state,
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 4: stok produk negatif (CHECK, penjaga anti-overselling)
    BEGIN
        UPDATE produk SET stok = -1 WHERE kode = 'PRD-008';
        INSERT INTO hasil_uji VALUES (4, 'stok produk negatif', '23514', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (4, 'stok produk negatif', '23514', v_state,
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 5: status pesanan tidak dikenal (CHECK)
    BEGIN
        INSERT INTO pesanan (pelanggan_id, tanggal, status)
        VALUES (1, DATE '2026-04-01', 'menghilang');
        INSERT INTO hasil_uji VALUES (5, 'status pesanan tidak dikenal', '23514', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (5, 'status pesanan tidak dikenal', '23514', v_state,
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 6: pesanan merujuk pelanggan yang tidak ada (FOREIGN KEY)
    BEGIN
        INSERT INTO pesanan (pelanggan_id, tanggal)
        VALUES (999, DATE '2026-04-01');
        INSERT INTO hasil_uji VALUES (6, 'pesanan merujuk pelanggan 999', '23503', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (6, 'pesanan merujuk pelanggan 999', '23503', v_state,
            CASE WHEN v_state = '23503' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 7: menghapus produk yang sudah pernah terjual (FOREIGN KEY)
    BEGIN
        DELETE FROM produk WHERE kode = 'PRD-005';
        INSERT INTO hasil_uji VALUES (7, 'hapus produk yang sudah terjual', '23503', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (7, 'hapus produk yang sudah terjual', '23503', v_state,
            CASE WHEN v_state = '23503' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 8: qty item pesanan nol (CHECK)
    BEGIN
        INSERT INTO pesanan_item (pesanan_id, produk_id, qty, harga_saat_transaksi)
        VALUES (1, 1, 0, 68000.00);
        INSERT INTO hasil_uji VALUES (8, 'qty item pesanan nol', '23514', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (8, 'qty item pesanan nol', '23514', v_state,
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL' END);
    END;
END $$;

SELECT nomor, kasus, diharapkan, aktual, status
FROM hasil_uji
ORDER BY nomor;

-- pastikan tidak ada baris uji yang bocor ke data asli
SELECT count(*) AS jumlah_produk, count(*) FILTER (WHERE kode LIKE 'PRD-9%') AS produk_uji_bocor
FROM produk;

tests/uji-transaksi.sql

Unduh berkas
sql 
-- uji-transaksi.sql — uji angka-angka kunci kuartal I 2026 dan penjaga
-- anti-overselling. Jalankan pada database yang memuat database/skema.sql
-- dan database/isi-data.sql:
--   psql -X -f database/skema.sql -f database/isi-data.sql -f tests/uji-transaksi.sql
-- Lulus bila setiap baris kolom hasil bernilai LULUS.

CREATE TEMP TABLE hasil_uji (
    nomor      integer,
    pengujian  text,
    hasil      text
);

INSERT INTO hasil_uji
SELECT 1, 'jumlah pesanan kuartal = 12',
       CASE WHEN count(*) = 12 THEN 'LULUS' ELSE 'GAGAL: aktual ' || count(*)::text END
FROM pesanan;

INSERT INTO hasil_uji
SELECT 2, 'jumlah baris item = 29',
       CASE WHEN count(*) = 29 THEN 'LULUS' ELSE 'GAGAL: aktual ' || count(*)::text END
FROM pesanan_item;

INSERT INTO hasil_uji
SELECT 3, 'total omzet kuartal = 2767500.00',
       CASE WHEN sum(i.subtotal) = 2767500.00
            THEN 'LULUS' ELSE 'GAGAL: aktual ' || sum(i.subtotal)::text END
FROM pesanan_item i
JOIN pesanan p ON p.id = i.pesanan_id AND p.status = 'selesai';

INSERT INTO hasil_uji
SELECT 4, 'laba kotor kuartal = 523000.00',
       CASE WHEN sum((i.harga_saat_transaksi - pr.harga_beli) * i.qty) = 523000.00
            THEN 'LULUS' ELSE 'GAGAL: aktual berbeda' END
FROM pesanan_item i
JOIN produk pr ON pr.id = i.produk_id;

INSERT INTO hasil_uji
SELECT 5, 'stok akhir Air Mineral = 150',
       CASE WHEN stok = 150 THEN 'LULUS' ELSE 'GAGAL: aktual ' || stok::text END
FROM produk WHERE kode = 'PRD-007';

INSERT INTO hasil_uji
SELECT 6, 'stok akhir Kopi Sachet = 80',
       CASE WHEN stok = 80 THEN 'LULUS' ELSE 'GAGAL: aktual ' || stok::text END
FROM produk WHERE kode = 'PRD-005';

INSERT INTO hasil_uji
SELECT 7, 'snapshot harga promo 11500 untuk Kopi tersimpan',
       CASE WHEN count(*) = 1 THEN 'LULUS' ELSE 'GAGAL: aktual ' || count(*)::text END
FROM pesanan_item i
JOIN produk pr ON pr.id = i.produk_id
WHERE pr.kode = 'PRD-005' AND i.harga_saat_transaksi = 11500.00;

-- Kasus 8: percobaan overselling ditolak CHECK (stok >= 0), dan seluruh
-- transaksinya digulung balik tanpa meninggalkan jejak.
DO $$
DECLARE
    v_state text;
BEGIN
    BEGIN
        INSERT INTO pesanan (pelanggan_id, tanggal)
        VALUES (1, DATE '2026-04-06');

        INSERT INTO pesanan_item (pesanan_id, produk_id, qty, harga_saat_transaksi)
        SELECT p.id, pr.id, 200, pr.harga_jual
        FROM pesanan p
        JOIN produk pr ON pr.kode = 'PRD-007'
        WHERE p.tanggal = DATE '2026-04-06';

        UPDATE produk pr
        SET stok = pr.stok - i.qty
        FROM pesanan_item i
        WHERE i.pesanan_id = (SELECT id FROM pesanan WHERE tanggal = DATE '2026-04-06')
          AND i.produk_id = pr.id;

        INSERT INTO hasil_uji VALUES (8, 'overselling 200 dari stok 150 ditolak', 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (8, 'overselling 200 dari stok 150 ditolak',
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL: SQLSTATE ' || v_state END);
    END;
END $$;

INSERT INTO hasil_uji
SELECT 9, 'tidak ada jejak pesanan 6 April setelah penolakan',
       CASE WHEN count(*) = 0 THEN 'LULUS' ELSE 'GAGAL: aktual ' || count(*)::text END
FROM pesanan WHERE tanggal = DATE '2026-04-06';

INSERT INTO hasil_uji
SELECT 10, 'stok Air Mineral tidak berubah oleh percobaan yang ditolak',
       CASE WHEN stok = 150 THEN 'LULUS' ELSE 'GAGAL: aktual ' || stok::text END
FROM produk WHERE kode = 'PRD-007';

SELECT nomor, pengujian, hasil
FROM hasil_uji
ORDER BY nomor;