Bagian 3: Query dan Laporan

Data sudah berada di tempatnya; sekarang database harus menjawab pertanyaan kerja pustakawan. Pertanyaan-pertanyaan itu berulang setiap hari, sehingga pantas disimpan sebagai berkas query yang bisa dijalankan sekali jalan, bukan diketik ulang dari nol. Pada bagian ini kita menyusun laporan.sql — enam laporan siap baca — lalu membedah query-query kuncinya. Materi intinya berasal dari Chapter Menggabungkan Tabel dengan JOIN, Fungsi Agregat, Mengelompokan Hasil dengan GROUP BY dan HAVING, dan Bekerja dengan Tanggal dan Waktu.

Enam Laporan untuk Pustakawan #

Laporan	Pertanyaan yang dijawab	Teknik utama
1. Daftar buku per kategori	koleksi apa saja yang dimiliki?	`JOIN` + `ORDER BY`
2. Buku sedang dipinjam	buku mana yang tidak ada di rak, dan siapa memegangnya?	`JOIN` + `IS NULL`
3. Riwayat per anggota	buku apa saja yang pernah dipinjam tiap anggota?	`JOIN` + `COALESCE`
4. Statistik kategori	seberapa besar koleksi tiap kategori?	`LEFT JOIN` + `GROUP BY`
5. Tersedia vs total	berapa eksemplar di rak, dipinjam, dan totalnya?	`LEFT JOIN` + syarat di `ON`
6. Peminjaman terlambat	siapa yang melewati jatuh tempo 14 hari?	aritmetika tanggal

Berkas dibuka dengan tiga baris dot commands: .headers on mencetak nama kolom, .mode column merapikan tata letak, dan .print menyisipkan judul laporan di antara hasil — semuanya materi Chapter Dot Commands pada sqlite3 CLI. Setiap query ditutup ORDER BY yang eksplisit; tanpa itu, urutan baris tidak dijamin dan hasil laporan bisa berbeda antar eksekusi.

Kode Lengkap Bagian 3 #

sql
-- laporan.sql — Katalog Perpustakaan
-- Bagian 3: kumpulan query laporan untuk pekerjaan harian pustakawan.
-- Jalankan: sqlite3 perpustakaan.db < laporan.sql

.headers on
.mode column

-- Laporan 1: daftar buku per kategori.
.print Laporan 1: Daftar Buku per Kategori
SELECT k.nama AS kategori, b.judul, b.penulis, b.tahun
FROM buku b
JOIN kategori k ON k.id = b.kategori_id
ORDER BY k.nama, b.judul;

.print
-- Laporan 2: buku yang sedang dipinjam beserta peminjamnya.
.print Laporan 2: Buku Sedang Dipinjam
SELECT b.judul, a.nama AS peminjam, p.tanggal_pinjam
FROM peminjaman p
JOIN buku b    ON b.id = p.buku_id
JOIN anggota a ON a.id = p.anggota_id
WHERE p.tanggal_kembali IS NULL
ORDER BY p.tanggal_pinjam;

.print
-- Laporan 3: riwayat peminjaman setiap anggota.
.print Laporan 3: Riwayat Peminjaman per Anggota
SELECT a.nama AS anggota, b.judul, p.tanggal_pinjam,
       COALESCE(p.tanggal_kembali, 'belum kembali') AS tanggal_kembali
FROM peminjaman p
JOIN anggota a ON a.id = p.anggota_id
JOIN buku b    ON b.id = p.buku_id
ORDER BY a.nama, p.tanggal_pinjam;

.print
-- Laporan 4: statistik koleksi per kategori.
.print Laporan 4: Statistik Kategori
SELECT k.nama AS kategori,
       COUNT(b.id) AS jumlah_buku,
       MIN(b.tahun) AS tahun_tertua,
       MAX(b.tahun) AS tahun_terbaru
FROM kategori k
LEFT JOIN buku b ON b.kategori_id = k.id
GROUP BY k.nama
ORDER BY jumlah_buku DESC, k.nama;

.print
-- Laporan 5: eksemplar tersedia dibandingkan total eksemplar.
.print Laporan 5: Eksemplar Tersedia vs Total
SELECT b.judul,
       b.jumlah_tersedia AS tersedia,
       COUNT(p.id)       AS sedang_dipinjam,
       b.jumlah_tersedia + COUNT(p.id) AS total
FROM buku b
LEFT JOIN peminjaman p
       ON p.buku_id = b.id
      AND p.tanggal_kembali IS NULL
GROUP BY b.id
ORDER BY b.judul;

.print
-- Laporan 6: peminjaman yang melewati jatuh tempo 14 hari
-- dihitung terhadap tanggal referensi 2025-06-20.
.print Laporan 6: Peminjaman Terlambat
SELECT b.judul, a.nama AS peminjam, p.tanggal_pinjam,
       date(p.tanggal_pinjam, '+14 days') AS jatuh_tempo,
       CAST(julianday('2025-06-20') - julianday(p.tanggal_pinjam, '+14 days')
            AS INTEGER) AS hari_terlambat
FROM peminjaman p
JOIN buku b    ON b.id = p.buku_id
JOIN anggota a ON a.id = p.anggota_id
WHERE p.tanggal_kembali IS NULL
  AND julianday('2025-06-20') - julianday(p.tanggal_pinjam) > 14;

Menjalankan Seluruh Laporan #

bash

sqlite3 perpustakaan.db < laporan.sql

Output yang Dihasilkan:

text
Laporan 1: Daftar Buku per Kategori
kategori   judul                                  penulis                tahun
---------  -------------------------------------  ---------------------  -----
Biografi   Long Walk to Freedom                   Nelson Mandela         1994 
Biografi   Steve Jobs                             Walter Isaacson        2011 
Fiksi      Atheis                                 Achdiat K. Mihardja    1949 
Fiksi      Bumi Manusia                           Pramoedya Ananta Toer  1980 
Fiksi      Cantik Itu Luka                        Eka Kurniawan          2002 
Fiksi      Laskar Pelangi                         Andrea Hirata          2005 
Puisi      Deru Campur Debu                       Chairil Anwar          1949 
Puisi      Hujan Bulan Juni                       Sapardi Djoko Damono   1994 
Sains      A Brief History of Time                Stephen Hawking        1988 
Sains      Kosmos                                 Carl Sagan             1980 
Sejarah    Sapiens: Riwayat Singkat Umat Manusia  Yuval Noah Harari      2011 
Sejarah    Sejarah Indonesia Modern               M.C. Ricklefs          1981 
Teknologi  Clean Code                             Robert C. Martin       2008 
Teknologi  The Mythical Man-Month                 Frederick P. Brooks    1975 
Teknologi  The Pragmatic Programmer               David Thomas           1999 

Laporan 2: Buku Sedang Dipinjam
judul   peminjam      tanggal_pinjam
------  ------------  --------------
Kosmos  Budi Santoso  2025-05-26    

Laporan 3: Riwayat Peminjaman per Anggota
anggota        judul                                  tanggal_pinjam  tanggal_kembali
-------------  -------------------------------------  --------------  ---------------
Agus Prasetyo  Clean Code                             2025-05-15      2025-05-29     
Budi Santoso   Bumi Manusia                           2025-05-05      2025-05-12     
Budi Santoso   Kosmos                                 2025-05-26      belum kembali  
Eko Nugroho    Hujan Bulan Juni                       2025-06-10      2025-06-14     
Rina Wijaya    Sapiens: Riwayat Singkat Umat Manusia  2025-06-02      2025-06-05     
Siti Rahayu    Laskar Pelangi                         2025-05-10      2025-05-24     

Laporan 4: Statistik Kategori
kategori   jumlah_buku  tahun_tertua  tahun_terbaru
---------  -----------  ------------  -------------
Fiksi      4            1949          2005         
Teknologi  3            1975          2008         
Biografi   2            1994          2011         
Puisi      2            1949          1994         
Sains      2            1980          1988         
Sejarah    2            1981          2011         

Laporan 5: Eksemplar Tersedia vs Total
judul                                  tersedia  sedang_dipinjam  total
-------------------------------------  --------  ---------------  -----
A Brief History of Time                1         0                1    
Atheis                                 1         0                1    
Bumi Manusia                           3         0                3    
Cantik Itu Luka                        2         0                2    
Clean Code                             2         0                2    
Deru Campur Debu                       1         0                1    
Hujan Bulan Juni                       1         0                1    
Kosmos                                 2         1                3    
Laskar Pelangi                         2         0                2    
Long Walk to Freedom                   1         0                1    
Sapiens: Riwayat Singkat Umat Manusia  2         0                2    
Sejarah Indonesia Modern               1         0                1    
Steve Jobs                             2         0                2    
The Mythical Man-Month                 1         0                1    
The Pragmatic Programmer               1         0                1    

Laporan 6: Peminjaman Terlambat
judul   peminjam      tanggal_pinjam  jatuh_tempo  hari_terlambat
------  ------------  --------------  -----------  --------------
Kosmos  Budi Santoso  2025-05-26      2025-06-09   11            

Bedah Query Kunci #

Laporan 2 — JOIN tiga tabel dengan IS NULL #

Untuk menjalankan satu laporan saja, salin query-nya ke sesi interaktif. Perintah .headers on dan .mode column tetap perlu agar hasilnya rapi:

text
sqlite> .headers on
sqlite> .mode column
sqlite> SELECT b.judul, a.nama AS peminjam, p.tanggal_pinjam
   ...> FROM peminjaman p
   ...> JOIN buku b ON b.id = p.buku_id
   ...> JOIN anggota a ON a.id = p.anggota_id
   ...> WHERE p.tanggal_kembali IS NULL
   ...> ORDER BY p.tanggal_pinjam;
judul   peminjam      tanggal_pinjam
------  ------------  --------------
Kosmos  Budi Santoso  2025-05-26    
sqlite>

Alias p, b, a — tiga tabel dirangkai tanpa menulis nama panjang berulang. Alias dibuat di klausa FROM, lalu dipakai di seluruh query.
Dua kondisi ON — b.id = p.buku_id dan a.id = p.anggota_id menyambungkan peminjaman ke buku dan ke anggota; inilah cara mengikuti foreign key dalam bentuk query (Chapter Menggabungkan Tabel dengan JOIN).
WHERE p.tanggal_kembali IS NULL — menyaring hanya peminjaman yang belum ditutup. Bandingkan dengan = NULL yang selalu gagal: NULL menandakan nilai tak diketahui, bukan nilai yang bisa disamakan (Chapter Operator Lanjutan: IN, BETWEEN, IS NULL).
ORDER BY p.tanggal_pinjam — keluaran menjadi deterministik: satu baris untuk setiap peminjaman aktif, terurut tanggal pinjam.

Laporan 5 — syarat penggabungan ada di ON, bukan WHERE #

Query ini menghitung eksemplar yang tersedia, yang sedang dipinjam, dan totalnya per judul. Kuncinya: syarat p.tanggal_kembali IS NULL diletakkan pada klausa ON, bukan pada WHERE. Sebagai pembuktian, berikut varian yang salah — syarat dipindah ke WHERE:

sql
SELECT b.judul, b.jumlah_tersedia AS tersedia, COUNT(p.id) AS sedang_dipinjam
FROM buku b
LEFT JOIN peminjaman p ON p.buku_id = b.id
WHERE p.tanggal_kembali IS NULL
GROUP BY b.id
ORDER BY b.judul;

Output yang Dihasilkan:

text
judul                     tersedia  sedang_dipinjam
------------------------  --------  ---------------
A Brief History of Time   1         0              
Atheis                    1         0              
Cantik Itu Luka           2         0              
Deru Campur Debu          1         0              
Kosmos                    2         1              
Long Walk to Freedom      1         0              
Sejarah Indonesia Modern  1         0              
Steve Jobs                2         0              
The Mythical Man-Month    1         0              
The Pragmatic Programmer  1         0              

Hanya sepuluh judul yang muncul; lima hilang. Yang lenyap justru buku-buku yang seluruh peminjamannya sudah selesai — Bumi Manusia, Laskar Pelangi, Clean Code, Sapiens: Riwayat Singkat Umat Manusia, dan Hujan Bulan Juni. Penyebabnya: WHERE menyaring setelah penggabungan selesai. Buku dengan peminjaman yang semuanya sudah dikembalikan hanya punya baris gabungan bernilai tanggal_kembali non-NULL, dan baris-baris itu tersaring habis.

Dengan syarat di ON, pencocokan baris peminjaman dibatasi sejak awal hanya pada peminjaman aktif; buku yang tidak punya peminjaman aktif tetap menghasilkan baris (berekstensi NULL) sehingga COUNT(p.id) menghitung 0. Semua lima belas judul tampil pada laporan yang benar.

Laporan 6 — aritmetika tanggal untuk jatuh tempo #

Kebijakan perpustakaan kita: masa pinjam 14 hari. Jatuh tempo dihitung dengan modifier '+14 days', dan keterlambatan dihitung sebagai selisih hari terhadap tanggal referensi:

date(p.tanggal_pinjam, '+14 days') — menghasilkan kolom jatuh_tempo dengan menggeser tanggal pinjam 14 hari ke depan (Chapter Bekerja dengan Tanggal dan Waktu).
julianday('2025-06-20') - julianday(p.tanggal_pinjam, '+14 days') — julianday() mengubah tanggal menjadi jumlah hari (pecahan); selisihnya adalah usia keterlambatan dalam satuan hari, lalu CAST(... AS INTEGER) membulatkannya.
WHERE ... > 14 — peminjaman aktif yang usianya melewati 14 hari dianggap terlambat. Peminjaman Kosmos pada 2025-05-26 jatuh tempo 2025-06-09, sehingga pada referensi 2025-06-20 terlambat 11 hari — persis baris pada laporan.

Database kini menjawab pertanyaan harian pustakawan dalam satu perintah. Bagian 4 menutup proyek dengan kecepatan dan kenyamanan: index dan view. Berkas lengkap bagian ini (schema.sql, seed.sql, dan laporan.sql) tersedia pada bagian Berkas Sumber di bawah halaman.

Berkas Sumber

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

sql
-- laporan.sql — Katalog Perpustakaan
-- Bagian 3: kumpulan query laporan untuk pekerjaan harian pustakawan.
-- Jalankan: sqlite3 perpustakaan.db < laporan.sql

.headers on
.mode column

-- Laporan 1: daftar buku per kategori.
.print Laporan 1: Daftar Buku per Kategori
SELECT k.nama AS kategori, b.judul, b.penulis, b.tahun
FROM buku b
JOIN kategori k ON k.id = b.kategori_id
ORDER BY k.nama, b.judul;

.print
-- Laporan 2: buku yang sedang dipinjam beserta peminjamnya.
.print Laporan 2: Buku Sedang Dipinjam
SELECT b.judul, a.nama AS peminjam, p.tanggal_pinjam
FROM peminjaman p
JOIN buku b    ON b.id = p.buku_id
JOIN anggota a ON a.id = p.anggota_id
WHERE p.tanggal_kembali IS NULL
ORDER BY p.tanggal_pinjam;

.print
-- Laporan 3: riwayat peminjaman setiap anggota.
.print Laporan 3: Riwayat Peminjaman per Anggota
SELECT a.nama AS anggota, b.judul, p.tanggal_pinjam,
       COALESCE(p.tanggal_kembali, 'belum kembali') AS tanggal_kembali
FROM peminjaman p
JOIN anggota a ON a.id = p.anggota_id
JOIN buku b    ON b.id = p.buku_id
ORDER BY a.nama, p.tanggal_pinjam;

.print
-- Laporan 4: statistik koleksi per kategori.
.print Laporan 4: Statistik Kategori
SELECT k.nama AS kategori,
       COUNT(b.id) AS jumlah_buku,
       MIN(b.tahun) AS tahun_tertua,
       MAX(b.tahun) AS tahun_terbaru
FROM kategori k
LEFT JOIN buku b ON b.kategori_id = k.id
GROUP BY k.nama
ORDER BY jumlah_buku DESC, k.nama;

.print
-- Laporan 5: eksemplar tersedia dibandingkan total eksemplar.
.print Laporan 5: Eksemplar Tersedia vs Total
SELECT b.judul,
       b.jumlah_tersedia AS tersedia,
       COUNT(p.id)       AS sedang_dipinjam,
       b.jumlah_tersedia + COUNT(p.id) AS total
FROM buku b
LEFT JOIN peminjaman p
       ON p.buku_id = b.id
      AND p.tanggal_kembali IS NULL
GROUP BY b.id
ORDER BY b.judul;

.print
-- Laporan 6: peminjaman yang melewati jatuh tempo 14 hari
-- dihitung terhadap tanggal referensi 2025-06-20.
.print Laporan 6: Peminjaman Terlambat
SELECT b.judul, a.nama AS peminjam, p.tanggal_pinjam,
       date(p.tanggal_pinjam, '+14 days') AS jatuh_tempo,
       CAST(julianday('2025-06-20') - julianday(p.tanggal_pinjam, '+14 days')
            AS INTEGER) AS hari_terlambat
FROM peminjaman p
JOIN buku b    ON b.id = p.buku_id
JOIN anggota a ON a.id = p.anggota_id
WHERE p.tanggal_kembali IS NULL
  AND julianday('2025-06-20') - julianday(p.tanggal_pinjam) > 14;

sql
-- schema.sql — Katalog Perpustakaan
-- Bagian 1: skema database untuk katalog perpustakaan.
-- Jalankan: sqlite3 perpustakaan.db < schema.sql

PRAGMA foreign_keys = ON;

-- Lokasi fisik buku di ruang perpustakaan.
CREATE TABLE rak (
    kode   TEXT PRIMARY KEY,
    lokasi TEXT NOT NULL
);

-- Kategori buku. Dipisah dari tabel buku agar nama kategori
-- tidak ditulis ulang di setiap baris buku (normalisasi).
CREATE TABLE kategori (
    id   INTEGER PRIMARY KEY,
    nama TEXT NOT NULL UNIQUE
);

-- Data buku. Satu buku masuk tepat satu kategori dan
-- disimpan di tepat satu rak.
CREATE TABLE buku (
    id              INTEGER PRIMARY KEY,
    judul           TEXT    NOT NULL,
    penulis         TEXT    NOT NULL,
    tahun           INTEGER NOT NULL CHECK (tahun >= 1900),
    isbn            TEXT    NOT NULL UNIQUE,
    kategori_id     INTEGER NOT NULL REFERENCES kategori(id)
                             ON DELETE RESTRICT,
    rak_kode        TEXT    NOT NULL REFERENCES rak(kode)
                             ON DELETE RESTRICT,
    jumlah_tersedia INTEGER NOT NULL CHECK (jumlah_tersedia >= 0)
);

-- Anggota perpustakaan.
CREATE TABLE anggota (
    id             INTEGER PRIMARY KEY,
    nama           TEXT NOT NULL,
    email          TEXT NOT NULL UNIQUE,
    tanggal_gabung TEXT NOT NULL
);

-- Catatan peminjaman. tanggal_kembali bernilai NULL
-- selama buku masih di tangan anggota.
CREATE TABLE peminjaman (
    id              INTEGER PRIMARY KEY,
    buku_id         INTEGER NOT NULL REFERENCES buku(id)
                             ON DELETE CASCADE,
    anggota_id      INTEGER NOT NULL REFERENCES anggota(id)
                             ON DELETE CASCADE,
    tanggal_pinjam  TEXT    NOT NULL,
    tanggal_kembali TEXT,
    CHECK (tanggal_kembali IS NULL OR tanggal_kembali >= tanggal_pinjam)
);

sql
-- seed.sql — Katalog Perpustakaan
-- Bagian 2: data awal yang realistis untuk perpustakaan.
-- Jalankan setelah schema.sql: sqlite3 perpustakaan.db < seed.sql

PRAGMA foreign_keys = ON;

BEGIN TRANSACTION;

-- Empat rak tempat buku disimpan.
INSERT INTO rak (kode, lokasi) VALUES
    ('R1', 'Lantai 1, dekat pintu masuk'),
    ('R2', 'Lantai 1, sisi kanan'),
    ('R3', 'Lantai 2, ruang baca'),
    ('R4', 'Lantai 2, pojok belakang');

-- Enam kategori buku.
INSERT INTO kategori (id, nama) VALUES
    (1, 'Fiksi'),
    (2, 'Sains'),
    (3, 'Sejarah'),
    (4, 'Teknologi'),
    (5, 'Puisi'),
    (6, 'Biografi');

-- Lima belas judul buku di seluruh kategori.
-- jumlah_tersedia = jumlah eksemplar yang ada di rak saat ini.
INSERT INTO buku (id, judul, penulis, tahun, isbn, kategori_id, rak_kode, jumlah_tersedia) VALUES
    (1,  'Bumi Manusia',                    'Pramoedya Ananta Toer',  1980, '9789799731234', 1, 'R1', 3),
    (2,  'Laskar Pelangi',                  'Andrea Hirata',          2005, '9789793062792', 1, 'R1', 2),
    (3,  'Cantik Itu Luka',                 'Eka Kurniawan',          2002, '9789799105251', 1, 'R1', 2),
    (4,  'Atheis',                          'Achdiat K. Mihardja',   1949, '9789794334017', 1, 'R1', 1),
    (5,  'Kosmos',                          'Carl Sagan',             1980, '9786024240293', 2, 'R3', 2),
    (6,  'A Brief History of Time',          'Stephen Hawking',       1988, '9780553175219', 2, 'R3', 1),
    (7,  'Sapiens: Riwayat Singkat Umat Manusia', 'Yuval Noah Harari', 2011, '9786020427433', 3, 'R4', 2),
    (8,  'Sejarah Indonesia Modern',        'M.C. Ricklefs',          1981, '9789793780824', 3, 'R4', 1),
    (9,  'Clean Code',                      'Robert C. Martin',       2008, '9780132350884', 4, 'R3', 2),
    (10, 'The Pragmatic Programmer',        'David Thomas',           1999, '9780201616224', 4, 'R3', 1),
    (11, 'The Mythical Man-Month',          'Frederick P. Brooks',    1975, '9780201835953', 4, 'R3', 1),
    (12, 'Hujan Bulan Juni',                'Sapardi Djoko Damono',   1994, '9789799145359', 5, 'R2', 1),
    (13, 'Deru Campur Debu',                'Chairil Anwar',           1949, '9789794336516', 5, 'R2', 1),
    (14, 'Steve Jobs',                      'Walter Isaacson',        2011, '9781451648546', 6, 'R4', 2),
    (15, 'Long Walk to Freedom',            'Nelson Mandela',         1994, '9780349106533', 6, 'R4', 1);

-- Lima anggota perpustakaan.
INSERT INTO anggota (id, nama, email, tanggal_gabung) VALUES
    (1, 'Budi Santoso',  '[email protected]',  '2024-01-15'),
    (2, 'Siti Rahayu',   '[email protected]',  '2024-02-03'),
    (3, 'Agus Prasetyo', '[email protected]',  '2024-05-20'),
    (4, 'Rina Wijaya',   '[email protected]',  '2025-01-08'),
    (5, 'Eko Nugroho',   '[email protected]',   '2025-03-12');

-- Enam peminjaman; baris ke-5 belum dikembalikan
-- (tanggal_kembali NULL), itulah sebabnya jumlah_tersedia
-- buku Kosmos tinggal 2 dari 3 eksemplar.
INSERT INTO peminjaman (id, buku_id, anggota_id, tanggal_pinjam, tanggal_kembali) VALUES
    (1, 1,  1, '2025-05-05', '2025-05-12'),
    (2, 2,  2, '2025-05-10', '2025-05-24'),
    (3, 9,  3, '2025-05-15', '2025-05-29'),
    (4, 7,  4, '2025-06-02', '2025-06-05'),
    (5, 5,  1, '2025-05-26', NULL),
    (6, 12, 5, '2025-06-10', '2025-06-14');

COMMIT;