Kursus MariaDB / Latihan Laporan Penjualan Bulanan, Pembelian Pelanggan, dan Produk Belum Terjual
Penjualan bulanan, pembelian pelanggan, dan produk belum terjual mempunyai arti baris hasil yang berbeda walaupun membaca data yang sama. Tetapkan status dibayar, cakupan nilai, periode, dan arti belum terjual, lalu cocokkan total hanya ketika cakupan data yang dibandingkan memang sama.
Tulis definisi metrik dan periode setiap laporan
Penjualan pada latihan berarti quantity × unit_price saat pembelian dari rincian pesanan dibayar. Ongkos kirim serta pesanan dibatalkan tidak termasuk. Ini merupakan latihan laporan barang, bukan kebijakan akuntansi pendapatan, pengembalian dana, atau pajak yang lengkap.
Produk belum terjual berarti produk terdaftar yang belum pernah muncul pada pesanan dibayar. Laporan bulanan dibatasi ke 2026, sedangkan kueri pelanggan dan produk belum terjual di bawah membaca seluruh riwayat yang tersedia. Pada data awal seluruh pesanan dibayar berada pada 2026, sehingga totalnya dapat dicocokkan; kesamaan periode tidak otomatis berlaku setelah data ditambah.
| Laporan | Arti satu baris | Cakupan |
|---|---|---|
| Penjualan bulanan | Tahun dan bulan | Rincian dibayar dalam 2026 |
| Pembelian pelanggan | Satu pelanggan | Seluruh riwayat dibayar; pelanggan tanpa pembelian mendapat nol |
| Produk belum terjual | Satu produk | Tidak ada riwayat rincian dibayar kapan pun |
Panduan GROUP BY dan HAVING menjelaskan satuan kelompok, sedangkan panduan penggandaan JOIN membantu memeriksa nilai yang terhitung berulang. Referensi GROUP BY MariaDB dan referensi EXISTS menjelaskan sintaks yang dipakai.
Siapkan data kecil yang dapat dicocokkan secara manual
Hubungkan ke server belajar sebagai course_user dan pilih shopcourse. Panduan klien dan berkas SQL UTF-8 menjelaskan penyimpanan serta pelaksanaan dengan SOURCE C:/mariadb-lab/lesson.sql;. Ganti jalur dengan berkas sebenarnya dan jalankan SQL di dalam klien MariaDB.
Sebelum membuat c42_customers, c42_products, c42_orders, dan c42_items, periksa SELECT VERSION(), DATABASE(); serta SHOW TABLES;. Jika objek sudah ada, periksa definisi dan barisnya dahulu. Gunakan hanya objek latihan yang boleh dibuang dan pertahankan tabel aplikasi. Seluruh tabel disediakan agar latihan sebelumnya tidak mengubah hasil awal ini.
USE shopcourse;
CREATE TABLE c42_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c42_order_customer FOREIGN KEY (customer_id)
REFERENCES c42_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c42_item_order FOREIGN KEY (order_id)
REFERENCES c42_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c42_item_product FOREIGN KEY (product_id)
REFERENCES c42_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c42_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c42_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c42_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c42_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
SQL mempertahankan nama dan kota Korea dari sumber. Produk 1–4 adalah buku catatan, pena, cangkir, dan lampu. Pelanggan 1 serta 3 berada di Seoul, pelanggan 2 di Busan, dan pelanggan 4 di Daejeon. Simpan teks dengan UTF-8 dan gunakan ID untuk membandingkan hasil.
Nilai barang dibayar adalah 11500 pada pesanan 101, 8000 pada 102, dan 19500 pada 103: total 39000. Pesanan dibatalkan 104 dengan nilai 1500 dikecualikan. Cocokkan per pesanan dahulu agar jumlah besar yang terlihat masuk akal tidak menyembunyikan salah cakupan.
Kelompokkan tahun–bulan dan hitung pesanan pada tingkat yang benar
SELECT DATE_FORMAT(o.ordered_at,'%Y-%m') AS month_label,
COUNT(DISTINCT o.order_id) AS paid_orders,
SUM(i.quantity*i.unit_price) AS goods_sales
FROM c42_orders o JOIN c42_items i ON i.order_id=o.order_id
WHERE o.status='paid' AND o.ordered_at>='2026-01-01' AND o.ordered_at<'2027-01-01'
GROUP BY DATE_FORMAT(o.ordered_at,'%Y-%m') ORDER BY month_label;
| Bulan | Pesanan dibayar | Nilai barang |
|---|---|---|
| 2026-01 | 2 | 19500.00 |
| 2026-02 | 1 | 19500.00 |
Penggabungan rincian mempunyai lima baris barang dibayar, tetapi hanya tiga pesanan. Karena itu, metrik pesanan memakai COUNT(DISTINCT order_id). Menghitung COUNT(*) pada Januari menghasilkan tiga rincian, bukan dua pesanan.
Mengelompokkan hanya nomor bulan dapat mencampurkan bulan yang sama dari tahun berbeda. Kueri ini tidak otomatis membuat baris nol untuk bulan tanpa penjualan; diperlukan kumpulan kalender yang jelas jika semua bulan harus tampil. INNER JOIN juga membatasi hasil ke pesanan yang memiliki rincian.
Pertahankan pelanggan tanpa pembelian dalam total pelanggan
SELECT c.customer_id,c.customer_name,
COALESCE(SUM(CASE WHEN o.status='paid' THEN i.quantity*i.unit_price ELSE 0 END),0) AS paid_goods
FROM c42_customers c LEFT JOIN c42_orders o ON o.customer_id=c.customer_id
LEFT JOIN c42_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name ORDER BY c.customer_id;
Total untuk pelanggan ID 1–4 adalah 19500.00, 19500.00, 0.00, serta 0.00. LEFT JOIN mempertahankan pelanggan dan CASE hanya memasukkan rincian dibayar. Memindahkan syarat paid ke WHERE dapat menghilangkan pelanggan tanpa pesanan atau yang hanya mempunyai pesanan dibatalkan.
Kueri ini tidak mempunyai batas tanggal, sehingga totalnya mencakup seluruh riwayat dibayar. Jika tujuannya laporan pelanggan 2026, tentukan batas periode yang sama sambil tetap mempertahankan pelanggan nol; jangan menambahkan filter sisi kanan ke WHERE tanpa memeriksa pengaruhnya terhadap LEFT JOIN.
Jangan menjumlahkan shipping_fee langsung melalui baris rincian karena biaya per pesanan dapat berulang. Laporan ini hanya menjumlahkan nilai barang. Biaya pada tingkat pesanan membutuhkan perhitungan terpisah atau peringkasan rincian dahulu.
Cari produk tanpa riwayat penjualan dibayar
SELECT p.product_id,p.product_name FROM c42_products p
WHERE NOT EXISTS(SELECT 1 FROM c42_items i JOIN c42_orders o ON o.order_id=i.order_id
WHERE i.product_id=p.product_id AND o.status='paid') ORDER BY p.product_id;
Hanya produk 4, lampu, yang memenuhi syarat pada data awal. NOT EXISTS memeriksa apakah ada rincian terkait dari pesanan dibayar. Produk yang hanya muncul pada pesanan dibatalkan tetap dianggap belum terjual menurut definisi ini. Kebutuhan yang menghitung semua status memerlukan kondisi berbeda.
Ketiadaan ini mencakup seluruh riwayat, bukan hanya 2026. Produk yang terjual pada tahun sebelumnya tidak termasuk walaupun tidak terjual tahun ini. Bedakan “belum pernah terjual” dari “tidak terjual pada periode laporan” sebelum memilih filter.
Cocokkan total, baris yang hilang, dan batas waktu
Pada data awal, dua jumlah bulanan berjumlah 39000 dan empat jumlah pelanggan juga 39000. Jika ada pesanan dibayar tahun 2025 senilai 25000, laporan bulanan 2026 tetap 39000 sedangkan total pelanggan seluruh riwayat menjadi 64000. Perbedaan tersebut benar menurut cakupan kueri yang berbeda, bukan otomatis kesalahan penjumlahan.
Jika laporan dengan cakupan sama berbeda, bandingkan kunci, batas periode, status, serta definisi harga satuan, kemudian periksa penggandaan JOIN dan filter sesudah LEFT JOIN. Satu total keseluruhan yang terlihat benar tidak membuktikan setiap laporan benar.
Laporan operasional juga memerlukan waktu pengamatan konsisten, kebijakan zona waktu, dan penanganan pembatalan serta pengembalian. Nilai DATETIME tetap pada contoh menjaga batas stabil terlepas dari tanggal pelaksanaan. Gunakan parameter untuk periode yang diminta aplikasi daripada menggabungkan teks masukan ke SQL.
Sebelum mengulang latihan, periksa basis data yang dipilih, SHOW CREATE TABLE, serta isi keempat tabel dan cadangkan data yang perlu dipertahankan. Hapus hanya objek c42_ milik artikel ini, dimulai dari tabel anak lalu induk. DROP TABLE menghapus struktur dan baris sekaligus serta tidak dibalik dengan ROLLBACK biasa; pisahkan pembersihan dari percobaan transaksi.
DROP TABLE shopcourse.c42_items;
DROP TABLE shopcourse.c42_orders;
DROP TABLE shopcourse.c42_products;
DROP TABLE shopcourse.c42_customers;




