Database and SQL Fundamental MySQL 5

🧩 Pertemuan 5 — ORDER BY, GROUP BY, CASE, Logika & Index di MySQL

🎯 Tujuan Pembelajaran

Setelah pertemuan ini, kamu diharapkan mampu:

• Mengurutkan hasil query menggunakan ORDER BY.
• Mengelompokkan dan merangkum data dengan GROUP BY dan HAVING.
• Menggunakan kondisi logika AND, OR, NOT, IN, BETWEEN.
• Membuat logika percabangan dengan CASE WHEN dan IF().
• Membuat dan mengelola INDEX untuk mempercepat query.
• Menggunakan EXPLAIN untuk menganalisis performa query.

---

📘 1. ORDER BY

ORDER BY digunakan untuk mengurutkan hasil query.

-- Sintaks
SELECT kolom1, kolom2
FROM tabel
ORDER BY kolom1 [ASC|DESC], kolom2 [ASC|DESC];

• ASC = ascending (dari kecil ke besar / A ke Z) — default
• DESC = descending (dari besar ke kecil / Z ke A)

---

📘 2. GROUP BY & HAVING

GROUP BY mengelompokkan baris dengan nilai yang sama.
HAVING memfilter hasil setelah pengelompokan (seperti WHERE tapi untuk GROUP BY).

SELECT kolom, AGGREGATE_FUNCTION(kolom2)
FROM tabel
WHERE kondisi         -- filter SEBELUM grouping
GROUP BY kolom
HAVING kondisi_agregat; -- filter SESUDAH grouping

---

📘 3. CASE WHEN (Logika Percabangan)

CASE WHEN adalah struktur if-else di dalam SQL:

-- Simple CASE
CASE kolom
    WHEN nilai1 THEN hasil1
    WHEN nilai2 THEN hasil2
    ELSE hasil_default
END

-- Searched CASE (lebih fleksibel)
CASE
    WHEN kondisi1 THEN hasil1
    WHEN kondisi2 THEN hasil2
    ELSE hasil_default
END

---

📘 4. INDEX di MySQL

Index adalah struktur data yang mempercepat pencarian di tabel. Ibarat daftar isi di buku — tanpa index, MySQL harus membaca seluruh tabel (full table scan).

Jenis Index	Keterangan
PRIMARY KEY	Index otomatis pada kolom PK (Clustered Index)
INDEX / KEY	Index biasa pada kolom tertentu
UNIQUE INDEX	Index yang memastikan nilai tidak duplikat
FULLTEXT INDEX	Index untuk pencarian teks lengkap
COMPOSITE INDEX	Index pada beberapa kolom sekaligus

Kapan membuat index?

• Kolom yang sering digunakan di WHERE
• Kolom yang sering digunakan di JOIN
• Kolom yang sering digunakan di ORDER BY

Kapan TIDAK perlu index?

• Tabel kecil (< 1000 baris)
• Kolom yang jarang di-query
• Kolom dengan banyak nilai NULL

---

🧑‍💻 SETUP

USE db_perpustakaan;

-- Tambah kolom harga ke tabel buku untuk latihan
ALTER TABLE buku ADD COLUMN harga DECIMAL(10,2) DEFAULT 50000;

-- Update harga buku
UPDATE buku SET harga = 85000 WHERE id_buku = 1;
UPDATE buku SET harga = 120000 WHERE id_buku = 2;
UPDATE buku SET harga = 250000 WHERE id_buku = 3;
UPDATE buku SET harga = 180000 WHERE id_buku = 4;
UPDATE buku SET harga = 150000 WHERE id_buku = 5;
UPDATE buku SET harga = 95000 WHERE id_buku = 6;
UPDATE buku SET harga = 320000 WHERE id_buku = 7;

---

🧑‍💻 LATIHAN 1 — ORDER BY

-- 1. Urutkan buku berdasarkan judul A-Z
SELECT judul, penulis FROM buku ORDER BY judul ASC;

-- 2. Urutkan buku dari harga termahal ke termurah
SELECT judul, harga FROM buku ORDER BY harga DESC;

-- 3. Urutkan peminjaman dari terbaru, lalu tampilkan 5 teratas
SELECT * FROM peminjaman ORDER BY tgl_pinjam DESC LIMIT 5;

-- 4. Urutkan berdasarkan beberapa kolom
-- (pertama berdasarkan kategori, lalu dalam kategori urutkan berdasarkan judul)
SELECT b.judul, k.nama_kat, b.harga
FROM buku b
JOIN kategori k ON b.id_kategori = k.id_kategori
ORDER BY k.nama_kat ASC, b.judul ASC;

-- 5. Urutkan menggunakan ekspresi/alias
SELECT
    judul,
    harga,
    harga * 1.11 AS harga_after_ppn
FROM buku
ORDER BY harga_after_ppn DESC;

-- 6. Urutkan menggunakan FIELD() untuk urutan kustom
-- Tampilkan buku dengan urutan status tertentu dulu
SELECT judul, stok,
    CASE WHEN stok = 0 THEN 'Habis'
         WHEN stok < 3 THEN 'Hampir Habis'
         ELSE 'Tersedia' END AS status_stok
FROM buku
ORDER BY FIELD(CASE WHEN stok = 0 THEN 'Habis'
                    WHEN stok < 3 THEN 'Hampir Habis'
                    ELSE 'Tersedia' END, 'Habis', 'Hampir Habis', 'Tersedia');

---

🧑‍💻 LATIHAN 2 — GROUP BY & HAVING

-- 1. Hitung jumlah buku per kategori
SELECT
    k.nama_kat          AS kategori,
    COUNT(b.id_buku)    AS jumlah_buku
FROM kategori k
LEFT JOIN buku b ON k.id_kategori = b.id_kategori
GROUP BY k.id_kategori, k.nama_kat
ORDER BY jumlah_buku DESC;

-- 2. Statistik harga buku per kategori
SELECT
    k.nama_kat,
    COUNT(*) AS jumlah,
    MIN(b.harga) AS harga_termurah,
    MAX(b.harga) AS harga_termahal,
    AVG(b.harga) AS rata_harga,
    SUM(b.harga) AS total_nilai
FROM buku b
JOIN kategori k ON b.id_kategori = k.id_kategori
GROUP BY k.id_kategori, k.nama_kat;

-- 3. HAVING: hanya tampilkan kategori dengan lebih dari 1 buku
SELECT
    k.nama_kat,
    COUNT(*) AS jumlah_buku
FROM buku b
JOIN kategori k ON b.id_kategori = b.id_kategori
GROUP BY k.id_kategori, k.nama_kat
HAVING COUNT(*) > 1;

-- 4. Peminjaman per bulan
SELECT
    YEAR(tgl_pinjam)    AS tahun,
    MONTH(tgl_pinjam)   AS bulan,
    MONTHNAME(tgl_pinjam) AS nama_bulan,
    COUNT(*)            AS jumlah_peminjaman
FROM peminjaman
GROUP BY YEAR(tgl_pinjam), MONTH(tgl_pinjam), MONTHNAME(tgl_pinjam)
ORDER BY tahun, bulan;

-- 5. Anggota paling aktif dengan total buku dipinjam > 1
SELECT
    a.nama,
    COUNT(p.id_pinjam)  AS total_pinjam,
    SUM(CASE WHEN p.status = 'dipinjam' THEN 1 ELSE 0 END) AS masih_dipinjam,
    SUM(CASE WHEN p.status = 'dikembalikan' THEN 1 ELSE 0 END) AS sudah_kembali
FROM anggota a
LEFT JOIN peminjaman p ON a.id_anggota = p.id_anggota
GROUP BY a.id_anggota, a.nama
HAVING total_pinjam > 0
ORDER BY total_pinjam DESC;

-- 6. WITH ROLLUP: total keseluruhan
SELECT
    k.nama_kat,
    COUNT(*) AS jumlah_buku,
    SUM(b.stok) AS total_stok
FROM buku b
JOIN kategori k ON b.id_kategori = k.id_kategori
GROUP BY k.nama_kat WITH ROLLUP;

---

🧑‍💻 LATIHAN 3 — Logika AND, OR, NOT

-- 1. AND: buku dari kategori Teknologi DAN harga > 100000
SELECT judul, harga, id_kategori
FROM buku
WHERE id_kategori = 2 AND harga > 100000;

-- 2. OR: buku tahun 2005 ATAU 2009
SELECT judul, tahun_terbit
FROM buku
WHERE tahun_terbit = 2005 OR tahun_terbit = 2009;

-- 3. NOT: buku yang BUKAN ditulis oleh Andrea Hirata
SELECT judul, penulis
FROM buku
WHERE NOT penulis = 'Andrea Hirata';

-- 4. Kombinasi AND + OR (gunakan kurung!)
SELECT judul, penulis, harga, id_kategori
FROM buku
WHERE (id_kategori = 1 AND harga < 100000)
   OR (id_kategori = 2 AND harga > 200000);

-- 5. IN: pengganti banyak OR
SELECT judul, penulis
FROM buku
WHERE id_kategori IN (1, 2, 5);

-- 6. NOT IN
SELECT judul
FROM buku
WHERE id_kategori NOT IN (3, 4);

-- 7. BETWEEN: harga antara 100000 dan 200000
SELECT judul, harga
FROM buku
WHERE harga BETWEEN 100000 AND 200000;

-- 8. NOT BETWEEN
SELECT judul, harga
FROM buku
WHERE harga NOT BETWEEN 100000 AND 200000;

-- 9. LIKE: cari berdasarkan pola
SELECT judul, penulis
FROM buku
WHERE judul LIKE 'B%';        -- Judul diawali B

SELECT judul, penulis
FROM buku
WHERE penulis LIKE '%Hirata';  -- Nama diakhiri Hirata

SELECT judul
FROM buku
WHERE judul LIKE '%an%';       -- Mengandung 'an'

-- 10. IS NULL / IS NOT NULL
SELECT nama, email
FROM anggota
WHERE email IS NOT NULL;

---

🧑‍💻 LATIHAN 4 — CASE WHEN

-- 1. Kategorikan stok buku
SELECT
    judul,
    stok,
    CASE
        WHEN stok = 0 THEN 'Habis'
        WHEN stok BETWEEN 1 AND 2 THEN 'Hampir Habis'
        WHEN stok BETWEEN 3 AND 4 THEN 'Cukup'
        ELSE 'Stok Banyak'
    END AS status_stok
FROM buku;

-- 2. Kategorikan harga buku
SELECT
    judul,
    harga,
    CASE
        WHEN harga < 75000 THEN 'Murah'
        WHEN harga BETWEEN 75000 AND 200000 THEN 'Sedang'
        ELSE 'Mahal'
    END AS kategori_harga
FROM buku
ORDER BY harga;

-- 3. CASE dalam UPDATE
UPDATE buku
SET stok = CASE
    WHEN id_buku = 1 THEN 5
    WHEN id_buku = 2 THEN 3
    WHEN id_buku = 3 THEN 7
    ELSE stok
END
WHERE id_buku IN (1, 2, 3);

-- 4. CASE dengan agregasi (pivot sederhana)
SELECT
    k.nama_kat,
    SUM(CASE WHEN b.harga < 100000 THEN 1 ELSE 0 END) AS buku_murah,
    SUM(CASE WHEN b.harga BETWEEN 100000 AND 200000 THEN 1 ELSE 0 END) AS buku_sedang,
    SUM(CASE WHEN b.harga > 200000 THEN 1 ELSE 0 END) AS buku_mahal
FROM buku b
JOIN kategori k ON b.id_kategori = k.id_kategori
GROUP BY k.nama_kat;

-- 5. IF() — versi singkat CASE untuk 2 pilihan
SELECT
    judul,
    stok,
    IF(stok > 0, 'Tersedia', 'Habis') AS ketersediaan
FROM buku;

-- 6. IFNULL() — ganti NULL dengan nilai lain
SELECT
    nama,
    IFNULL(email, 'email tidak tersedia') AS email_info
FROM anggota;

-- 7. COALESCE() — ambil nilai pertama yang tidak NULL
SELECT
    nama,
    COALESCE(email, 'tidak ada email') AS kontak_utama
FROM anggota;

---

🧑‍💻 LATIHAN 5 — INDEX

-- Lihat index yang sudah ada
SHOW INDEX FROM buku;
SHOW INDEX FROM peminjaman;

-- 1. Buat Non-Unique Index pada kolom penulis
CREATE INDEX idx_penulis ON buku(penulis);

-- 2. Buat Unique Index pada isbn (sebenarnya sudah ada karena UNIQUE constraint)
-- CREATE UNIQUE INDEX idx_isbn ON buku(isbn);

-- 3. Buat Composite Index pada dua kolom yang sering diquery bersama
CREATE INDEX idx_kat_tahun ON buku(id_kategori, tahun_terbit);

-- 4. Buat Index pada tabel peminjaman
CREATE INDEX idx_pinjam_anggota ON peminjaman(id_anggota);
CREATE INDEX idx_pinjam_buku ON peminjaman(id_buku);
CREATE INDEX idx_pinjam_status ON peminjaman(status);

-- Lihat ulang index setelah ditambahkan
SHOW INDEX FROM buku\G

-- 5. Analisis query dengan EXPLAIN
EXPLAIN SELECT * FROM buku WHERE penulis = 'Andrea Hirata';
-- Perhatikan kolom: type, key, rows

EXPLAIN SELECT * FROM buku WHERE id_kategori = 1 AND tahun_terbit > 2000;
-- Lihat apakah composite index digunakan

EXPLAIN SELECT p.*, a.nama
FROM peminjaman p
JOIN anggota a ON p.id_anggota = a.id_anggota
WHERE p.status = 'dipinjam';

-- 6. Hapus index
DROP INDEX idx_kat_tahun ON buku;

-- 7. Rename index (MySQL 5.7+)
ALTER TABLE buku RENAME INDEX idx_penulis TO idx_nama_penulis;

---

🧑‍💻 LATIHAN 6 — Optimasi Query

-- KURANG EFISIEN vs LEBIH EFISIEN

-- ❌ Buruk: SELECT * mengambil semua kolom
SELECT * FROM buku WHERE tahun_terbit > 2000;

-- ✅ Baik: ambil hanya kolom yang diperlukan
SELECT judul, penulis, tahun_terbit FROM buku WHERE tahun_terbit > 2000;

-- ❌ Buruk: fungsi pada kolom WHERE (menghalangi penggunaan index)
SELECT * FROM peminjaman WHERE YEAR(tgl_pinjam) = 2026;

-- ✅ Baik: gunakan range agar index bisa digunakan
SELECT * FROM peminjaman
WHERE tgl_pinjam BETWEEN '2026-01-01' AND '2026-12-31';

-- ❌ Buruk: LIKE dengan wildcard di depan (tidak bisa pakai index)
SELECT * FROM buku WHERE judul LIKE '%Pelangi%';

-- ✅ Lebih baik: wildcard hanya di belakang jika memungkinkan
SELECT * FROM buku WHERE judul LIKE 'Laskar%';

-- ❌ Buruk: subquery berulang (correlated subquery)
SELECT judul,
    (SELECT COUNT(*) FROM peminjaman WHERE id_buku = b.id_buku) AS total_pinjam
FROM buku b;

-- ✅ Baik: gunakan JOIN + GROUP BY
SELECT b.judul, COUNT(p.id_pinjam) AS total_pinjam
FROM buku b
LEFT JOIN peminjaman p ON b.id_buku = p.id_buku
GROUP BY b.id_buku, b.judul;

---

🧑‍💻 LATIHAN 7 — Kasus Analitik Lengkap

Tulis query untuk menjawab pertanyaan bisnis berikut:

-- SOAL 1:
-- Buat laporan: untuk setiap anggota, tampilkan:
-- - Nama anggota
-- - Total buku pernah dipinjam
-- - Total buku masih dipinjam
-- - Status keaktifan: 'Aktif' (pernah pinjam), 'Tidak Aktif' (belum pernah pinjam)
-- Urutkan dari yang paling aktif


-- SOAL 2:
-- Buat laporan stok buku lengkap:
-- - Judul, Penulis, Kategori
-- - Stok saat ini
-- - Berapa kali pernah dipinjam
-- - Status: 'Populer' jika dipinjam >= 2x, 'Biasa' jika < 2x, 'Belum pernah dipinjam'
-- Urutkan berdasarkan jumlah peminjaman dari terbanyak


-- SOAL 3:
-- Buat summary per kategori buku:
-- - Nama kategori
-- - Jumlah judul buku
-- - Total stok semua buku dalam kategori
-- - Harga buku termahal dan termurah
-- - Rata-rata harga
-- Hanya tampilkan kategori yang punya setidaknya 1 buku


-- SOAL 4:
-- Buat laporan keterlambatan:
-- - Nama anggota, judul buku
-- - Tanggal pinjam, seharusnya kembali
-- - Jumlah hari terlambat (jika sudah lewat tanggal kembali)
-- - Total denda (Rp 500/hari)
-- Urutkan dari denda terbesar
-- Hanya tampilkan yang masih status 'dipinjam'

---

🧩 TUGAS PERORANGAN

Estimasi waktu: ±2 jam

Gunakan database dari pertemuan sebelumnya, kemudian:

1. Buat 10 query analitik menggunakan kombinasi:

   • ORDER BY dengan multi-kolom (min. 2 query)
   • GROUP BY + HAVING (min. 3 query)
   • CASE WHEN dalam SELECT (min. 2 query)
   • Logika AND/OR/NOT/IN/BETWEEN (min. 3 query)

2. Buat minimal 3 buah INDEX yang relevan pada tabel-tabel yang ada

3. Jalankan EXPLAIN untuk 3 query yang berbeda dan bandingkan dengan/tanpa index

4. Buat 1 laporan ringkasan yang menggabungkan GROUP BY + CASE WHEN + JOIN sekaligus

💾 Kumpulkan: Pertemuan5_Nama.sql

---

🔍 Refleksi

1. Apa perbedaan WHERE dan HAVING? Kapan menggunakan masing-masing?
2. Kenapa index bisa mempercepat query? Apa tradeoff-nya (kekurangannya)?
3. Mengapa wildcard % di awal LIKE (seperti '%kata') tidak efisien?
4. Kapan CASE WHEN lebih berguna dari pada IF()?