Database and SQL Fundamental MySQL 3

🧩 Pertemuan 3 — SQL CRUD & JOIN (Studi Kasus: Database Perpustakaan)

🎯 Tujuan Pembelajaran

Setelah pertemuan ini, kamu diharapkan mampu:

• Melakukan operasi CRUD: INSERT, SELECT, UPDATE, DELETE.
• Menggunakan klausa WHERE, LIKE, IN, BETWEEN untuk filter data.
• Memahami dan mempraktikkan semua jenis JOIN di MySQL.
• Membuat VIEW untuk menyimpan query yang sering digunakan.

---

📘 1. Operasi CRUD

CRUD adalah singkatan dari empat operasi dasar manipulasi data:

Huruf	Operasi	Perintah SQL
C	Create	INSERT INTO
R	Read	SELECT
U	Update	UPDATE ... SET
D	Delete	DELETE FROM

---

📘 2. Jenis-jenis JOIN

JOIN digunakan untuk menggabungkan data dari dua atau lebih tabel berdasarkan kolom yang berelasi.

Jenis JOIN	Fungsi
INNER JOIN	Hanya baris yang cocok di kedua tabel
LEFT JOIN	Semua baris dari tabel kiri + baris cocok dari kanan
RIGHT JOIN	Semua baris dari tabel kanan + baris cocok dari kiri
CROSS JOIN	Semua kombinasi baris dari kedua tabel

---

🧑‍💻 SETUP — Database Perpustakaan

Jalankan script berikut untuk membuat database yang akan digunakan sepanjang latihan:

CREATE DATABASE db_perpustakaan;
USE db_perpustakaan;

-- Tabel anggota
CREATE TABLE anggota (
    id_anggota  INT             AUTO_INCREMENT PRIMARY KEY,
    kode_ang    VARCHAR(10)     NOT NULL UNIQUE,
    nama        VARCHAR(100)    NOT NULL,
    alamat      VARCHAR(200),
    email       VARCHAR(100)    UNIQUE,
    tgl_daftar  DATE            DEFAULT (CURDATE())
);

-- Tabel kategori buku
CREATE TABLE kategori (
    id_kategori INT             AUTO_INCREMENT PRIMARY KEY,
    nama_kat    VARCHAR(50)     NOT NULL UNIQUE
);

-- Tabel buku
CREATE TABLE buku (
    id_buku     INT             AUTO_INCREMENT PRIMARY KEY,
    isbn        VARCHAR(20)     NOT NULL UNIQUE,
    judul       VARCHAR(200)    NOT NULL,
    penulis     VARCHAR(100)    NOT NULL,
    penerbit    VARCHAR(100),
    tahun_terbit YEAR,
    stok        INT             DEFAULT 3 CHECK (stok >= 0),
    id_kategori INT,
    FOREIGN KEY (id_kategori) REFERENCES kategori(id_kategori)
);

-- Tabel peminjaman
CREATE TABLE peminjaman (
    id_pinjam   INT             AUTO_INCREMENT PRIMARY KEY,
    id_anggota  INT             NOT NULL,
    id_buku     INT             NOT NULL,
    tgl_pinjam  DATE            NOT NULL DEFAULT (CURDATE()),
    tgl_kembali DATE,
    tgl_kembali_aktual DATE,
    status      ENUM('dipinjam','dikembalikan','terlambat') DEFAULT 'dipinjam',
    FOREIGN KEY (id_anggota) REFERENCES anggota(id_anggota),
    FOREIGN KEY (id_buku) REFERENCES buku(id_buku)
);

-- === INSERT DATA ===
INSERT INTO kategori (nama_kat) VALUES
('Novel'), ('Teknologi'), ('Sains'), ('Sejarah'), ('Bisnis');

INSERT INTO anggota (kode_ang, nama, alamat, email) VALUES
('ANG001', 'Andi Wijaya', 'Jl. Merdeka No. 10, Jakarta', '[email protected]'),
('ANG002', 'Budi Santoso', 'Jl. Sudirman No. 25, Bandung', '[email protected]'),
('ANG003', 'Citra Lestari', 'Jl. Gatot Subroto No. 5, Jakarta', '[email protected]'),
('ANG004', 'Dewi Rahayu', 'Jl. Diponegoro No. 15, Surabaya', '[email protected]'),
('ANG005', 'Eko Prasetyo', 'Jl. Imam Bonjol No. 7, Semarang', '[email protected]'),
('ANG006', 'Fani Kurnia', 'Jl. Ahmad Yani No. 3, Yogyakarta', '[email protected]');

INSERT INTO buku (isbn, judul, penulis, penerbit, tahun_terbit, stok, id_kategori) VALUES
('978-602-01-0001', 'Laskar Pelangi', 'Andrea Hirata', 'Bentang', 2005, 3, 1),
('978-602-01-0002', 'Bumi Manusia', 'Pramoedya Ananta Toer', 'Hasta Mitra', 1980, 2, 4),
('978-602-01-0003', 'Clean Code', 'Robert C. Martin', 'Prentice Hall', 2008, 4, 2),
('978-602-01-0004', 'Sapiens', 'Yuval Noah Harari', 'Harper', 2011, 3, 3),
('978-602-01-0005', 'Rich Dad Poor Dad', 'Robert Kiyosaki', 'Warner Books', 1997, 2, 5),
('978-602-01-0006', 'Negeri 5 Menara', 'Ahmad Fuadi', 'Gramedia', 2009, 5, 1),
('978-602-01-0007', 'The Pragmatic Programmer', 'David Thomas', 'Addison Wesley', 1999, 2, 2);

INSERT INTO peminjaman (id_anggota, id_buku, tgl_pinjam, tgl_kembali) VALUES
(1, 1, '2026-06-01', '2026-06-08'),
(1, 3, '2026-06-01', '2026-06-08'),
(2, 2, '2026-06-03', '2026-06-10'),
(3, 4, '2026-06-05', '2026-06-12'),
(4, 5, '2026-06-07', '2026-06-14'),
(2, 6, '2026-06-08', '2026-06-15'),
(5, 3, '2026-06-09', '2026-06-16');

---

🧑‍💻 LATIHAN 1 — SELECT Dasar (READ)

-- 1. Tampilkan semua data buku
SELECT * FROM buku;

-- 2. Tampilkan hanya kolom tertentu
SELECT judul, penulis, tahun_terbit FROM buku;

-- 3. Tampilkan buku dengan alias kolom
SELECT
    judul      AS 'Judul Buku',
    penulis    AS 'Nama Penulis',
    tahun_terbit AS 'Tahun'
FROM buku;

-- 4. Tampilkan buku yang stoknya lebih dari 2
SELECT judul, stok FROM buku WHERE stok > 2;

-- 5. Tampilkan anggota dari Jakarta (menggunakan LIKE)
SELECT nama, alamat FROM anggota WHERE alamat LIKE '%Jakarta%';

-- 6. Tampilkan buku yang diterbitkan antara tahun 2000-2010 (BETWEEN)
SELECT judul, tahun_terbit FROM buku
WHERE tahun_terbit BETWEEN 2000 AND 2010;

-- 7. Tampilkan buku kategori Novel atau Teknologi (IN)
SELECT judul, id_kategori FROM buku
WHERE id_kategori IN (1, 2);

-- 8. Tampilkan buku yang penulisnya BUKAN Andrea Hirata (NOT)
SELECT judul, penulis FROM buku
WHERE penulis != 'Andrea Hirata';

-- 9. Tampilkan buku yang stoknya NULL atau 0
SELECT judul, stok FROM buku WHERE stok = 0 OR stok IS NULL;

-- 10. Tampilkan 3 buku terbaru (LIMIT)
SELECT judul, tahun_terbit FROM buku
ORDER BY tahun_terbit DESC
LIMIT 3;

---

🧑‍💻 LATIHAN 2 — INSERT (CREATE)

-- 1. INSERT satu baris data
INSERT INTO anggota (kode_ang, nama, alamat, email)
VALUES ('ANG007', 'Gita Permata', 'Jl. Veteran No. 12, Bandung', '[email protected]');

-- 2. INSERT beberapa baris sekaligus
INSERT INTO buku (isbn, judul, penulis, penerbit, tahun_terbit, stok, id_kategori) VALUES
('978-602-01-0008', 'Atomic Habits', 'James Clear', 'Avery', 2018, 4, 5),
('978-602-01-0009', 'Python Crash Course', 'Eric Matthes', 'No Starch', 2019, 3, 2);

-- 3. INSERT dengan subquery (salin data dari tabel lain)
-- Misalkan kita punya tabel buku_backup
-- INSERT INTO buku SELECT * FROM buku_backup WHERE tahun_terbit > 2020;

-- 4. Verifikasi INSERT
SELECT * FROM anggota WHERE kode_ang = 'ANG007';
SELECT * FROM buku ORDER BY id_buku DESC LIMIT 2;

---

🧑‍💻 LATIHAN 3 — UPDATE

-- 1. UPDATE data berdasarkan kondisi
UPDATE anggota
SET alamat = 'Jl. Merdeka Baru No. 20, Jakarta'
WHERE id_anggota = 1;

-- 2. UPDATE beberapa kolom sekaligus
UPDATE buku
SET stok = 5, penerbit = 'Bentang Pustaka'
WHERE id_buku = 1;

-- 3. UPDATE dengan kondisi gabungan (AND)
UPDATE peminjaman
SET status = 'dikembalikan', tgl_kembali_aktual = CURDATE()
WHERE id_anggota = 1 AND id_buku = 1;

-- 4. UPDATE menggunakan nilai dari kolom itu sendiri
UPDATE buku
SET stok = stok - 1
WHERE id_buku = 3;

-- 5. UPDATE banyak baris sekaligus (tanpa WHERE = semua baris terkena!)
-- HATI-HATI! Contoh: naikkan semua stok sebesar 1
UPDATE buku SET stok = stok + 1;

-- 6. Verifikasi UPDATE
SELECT * FROM peminjaman WHERE id_anggota = 1;
SELECT judul, stok FROM buku;

⚠️ PENTING: Selalu gunakan WHERE saat UPDATE kecuali memang ingin mengubah semua baris!

---

🧑‍💻 LATIHAN 4 — DELETE

-- 1. DELETE satu baris berdasarkan PK
-- (tambah dulu data percobaan sebelum dihapus)
INSERT INTO anggota (kode_ang, nama) VALUES ('ANG099', 'Anggota Percobaan');
DELETE FROM anggota WHERE kode_ang = 'ANG099';

-- 2. DELETE dengan kondisi LIKE
-- Hapus anggota yang emailnya berisi domain tertentu
-- DELETE FROM anggota WHERE email LIKE '%@test.com';

-- 3. DELETE dengan kondisi lebih dari satu
DELETE FROM peminjaman
WHERE status = 'dikembalikan' AND tgl_kembali_aktual < '2026-01-01';

-- 4. Coba DELETE yang terkena Foreign Key (harusnya ERROR)
-- Tidak bisa hapus anggota yang masih punya peminjaman aktif
-- DELETE FROM anggota WHERE id_anggota = 2;
-- Error: Cannot delete or update a parent row: a foreign key constraint fails

-- 5. Solusi: hapus data anak dulu, baru hapus data induk
-- DELETE FROM peminjaman WHERE id_anggota = 2;
-- DELETE FROM anggota WHERE id_anggota = 2;

-- Verifikasi
SELECT * FROM anggota;

---

🧑‍💻 LATIHAN 5 — JOIN

INNER JOIN

-- Tampilkan nama anggota, judul buku, dan tanggal pinjam
SELECT
    a.nama          AS 'Nama Anggota',
    b.judul         AS 'Judul Buku',
    p.tgl_pinjam    AS 'Tanggal Pinjam',
    p.status        AS 'Status'
FROM peminjaman p
INNER JOIN anggota a ON p.id_anggota = a.id_anggota
INNER JOIN buku b ON p.id_buku = b.id_buku;

LEFT JOIN — Anggota yang belum pernah meminjam

-- Tampilkan SEMUA anggota, termasuk yang belum pernah meminjam
SELECT
    a.nama,
    a.kode_ang,
    COUNT(p.id_pinjam) AS 'Total Pinjam'
FROM anggota a
LEFT JOIN peminjaman p ON a.id_anggota = p.id_anggota
GROUP BY a.id_anggota, a.nama, a.kode_ang;

-- Hanya anggota yang BELUM pernah meminjam
SELECT a.nama, a.email
FROM anggota a
LEFT JOIN peminjaman p ON a.id_anggota = p.id_anggota
WHERE p.id_pinjam IS NULL;

RIGHT JOIN — Buku yang belum pernah dipinjam

-- Tampilkan buku yang belum pernah dipinjam
SELECT b.judul, b.penulis
FROM peminjaman p
RIGHT JOIN buku b ON p.id_buku = b.id_buku
WHERE p.id_pinjam IS NULL;

Multi-table JOIN dengan filter

-- Tampilkan detail peminjaman lengkap dengan kategori buku
SELECT
    a.nama                  AS 'Anggota',
    b.judul                 AS 'Judul Buku',
    k.nama_kat              AS 'Kategori',
    b.penulis               AS 'Penulis',
    p.tgl_pinjam            AS 'Tgl Pinjam',
    p.tgl_kembali           AS 'Tgl Kembali',
    p.status                AS 'Status'
FROM peminjaman p
JOIN anggota a  ON p.id_anggota = a.id_anggota
JOIN buku b     ON p.id_buku = b.id_buku
JOIN kategori k ON b.id_kategori = k.id_kategori
ORDER BY p.tgl_pinjam DESC;

---

🧑‍💻 LATIHAN 6 — VIEW

VIEW adalah query yang disimpan dan bisa diperlakukan seperti tabel.

-- Buat VIEW untuk laporan peminjaman lengkap
CREATE VIEW v_laporan_peminjaman AS
SELECT
    p.id_pinjam,
    a.kode_ang,
    a.nama          AS nama_anggota,
    b.isbn,
    b.judul         AS judul_buku,
    k.nama_kat      AS kategori,
    p.tgl_pinjam,
    p.tgl_kembali,
    p.tgl_kembali_aktual,
    p.status,
    CASE
        WHEN p.status = 'dipinjam' AND CURDATE() > p.tgl_kembali
        THEN DATEDIFF(CURDATE(), p.tgl_kembali)
        ELSE 0
    END AS hari_terlambat
FROM peminjaman p
JOIN anggota a  ON p.id_anggota = a.id_anggota
JOIN buku b     ON p.id_buku = b.id_buku
JOIN kategori k ON b.id_kategori = k.id_kategori;

-- Gunakan VIEW seperti tabel biasa
SELECT * FROM v_laporan_peminjaman;
SELECT * FROM v_laporan_peminjaman WHERE status = 'dipinjam';
SELECT * FROM v_laporan_peminjaman WHERE hari_terlambat > 0;

-- Lihat semua VIEW yang ada
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- Hapus VIEW
-- DROP VIEW v_laporan_peminjaman;

---

🧑‍💻 LATIHAN 7 — Tantangan SQL

Tulis query untuk menjawab pertanyaan-pertanyaan berikut menggunakan database perpustakaan:

-- 1. Siapa anggota yang paling sering meminjam buku?
--    (tampilkan nama dan jumlah peminjaman, urutkan dari terbanyak)


-- 2. Buku apa yang paling sering dipinjam?
--    (tampilkan judul, penulis, dan jumlah peminjaman)


-- 3. Berapa rata-rata lama peminjaman (dalam hari) per anggota?
--    Gunakan DATEDIFF(tgl_kembali, tgl_pinjam)


-- 4. Tampilkan daftar buku beserta status stok:
--    - Stok = 0: 'Habis'
--    - Stok 1-2: 'Hampir Habis'
--    - Stok >= 3: 'Tersedia'
--    (Gunakan CASE WHEN)


-- 5. Tampilkan anggota yang meminjam buku dari kategori 'Teknologi'


-- 6. Hitung total peminjaman per bulan di tahun 2026
--    (gunakan MONTH(tgl_pinjam) dan YEAR(tgl_pinjam))


-- 7. Tampilkan buku yang tidak pernah dipinjam sama sekali


-- 8. Tampilkan 3 anggota yang paling aktif meminjam beserta total bukunya

---

🧩 TUGAS PERORANGAN

Estimasi waktu: ±2,5 jam

Buat database db_perpustakaan_<Nama> yang berisi:

1. Minimal 5 tabel termasuk tabel junction/perantara
2. Minimal 10 data per tabel
3. Tulis 15 query minimum yang mencakup:
   • SELECT dengan WHERE, LIKE, BETWEEN, IN (min. 4 query)
   • INSERT, UPDATE, DELETE masing-masing min. 2 query
   • INNER JOIN, LEFT JOIN, RIGHT JOIN masing-masing min. 1 query
   • Multi-table JOIN (3+ tabel) min. 1 query
   • VIEW min. 1 buah
4. Setiap query harus disertai komentar yang menjelaskan tujuannya

💾 Kumpulkan: Pertemuan3_Nama.sql

---

🔍 Refleksi

1. Apa perbedaan antara INNER JOIN dan LEFT JOIN?
2. Kapan kita menggunakan WHERE p.id IS NULL setelah LEFT JOIN?
3. Apa keuntungan membuat VIEW dibanding menjalankan query langsung?
4. Mengapa penting selalu menyertakan WHERE saat DELETE atau UPDATE?