library(DBI) library(RSQLite) library(dplyr) con <- dbConnect(SQLite(), “:memory:”)

dbExecute(con, ” CREATE TABLE calon_taruna ( NO_CASIS INTEGER PRIMARY KEY, nama TEXT, asal_daerah TEXT, tanggal_lahir TEXT ); “)

dbExecute(con, ” CREATE TABLE jenis_dokumen ( id_dokumen INTEGER PRIMARY KEY, nama_dokumen TEXT ); “)

dbExecute(con, ” CREATE TABLE berkas_peserta ( id_berkas INTEGER PRIMARY KEY, NO_CASIS INTEGER, id_dokumen INTEGER, status TEXT, FOREIGN KEY (NO_CASIS) REFERENCES calon_taruna(NO_CASIS), FOREIGN KEY (id_dokumen) REFERENCES jenis_dokumen(id_dokumen) ); “)

dbExecute(con, ” CREATE TABLE panitia_verifikasi ( id_panitia INTEGER PRIMARY KEY, nama_panitia TEXT, jabatan TEXT ); “)

dbExecute(con, ” CREATE TABLE hasil_verifikasi ( id_hasil INTEGER PRIMARY KEY, id_berkas INTEGER, id_panitia INTEGER, catatan TEXT, tanggal_verifikasi TEXT, FOREIGN KEY (id_berkas) REFERENCES berkas_peserta(id_berkas), FOREIGN KEY (id_panitia) REFERENCES panitia_verifikasi(id_panitia) ); “)

dbExecute(con, ” INSERT INTO calon_taruna VALUES (16, ‘Teguh Febrian Laksana’, ‘Sulawesi Tenggara’, ‘2005-02-21’), (21, ‘Anang Saputra’, ‘Sulawesi Selatan’, ‘2006-10-22’), (440, ‘Nelson Alom’, ‘Papua’, ‘2005-06-01’); “)

dbExecute(con, ” INSERT INTO jenis_dokumen VALUES (1, ‘Ijazah SMA’), (2, ‘KTP’), (3, ‘Kartu Keluarga’), (4, ‘SKCK’), (5, ‘SKBS’); “)

dbExecute(con, ” INSERT INTO berkas_peserta VALUES (1, 16, 1, ‘Lengkap’), (2, 16, 4, ‘Lengkap’), (3, 21, 2, ‘Lengkap’), (4, 21, 3, ‘Tidak Lengkap’), (5, 440, 5, ‘Tidak Lengkap’); “)

dbExecute(con, ” INSERT INTO panitia_verifikasi VALUES (1, ‘Kompol Budi Santoso’, ‘Ketua Verifikasi’), (2, ‘Ipda Sari Putri’, ‘Verifikator’); “)

dbExecute(con, ” INSERT INTO hasil_verifikasi VALUES (1, 1, 1, ‘Valid’, ‘2025-01-10’), (2, 2, 2, ‘SKCK Tidak Sah’, ‘2025-01-11’), (3, 5, 1, ‘Dokumen Tidak Lengkap’, ‘2025-01-12’); “)

dbListTables(con) dbGetQuery(con, “SELECT * FROM calon_taruna”) dbGetQuery(con, “SELECT * FROM jenis_dokumen”) dbGetQuery(con, ” SELECT * FROM hasil_verifikasi ORDER BY tanggal_verifikasi ASC; “)

dbGetQuery(con, ” SELECT * FROM berkas_peserta WHERE status = ‘Tidak Lengkap’; “)

dbExecute(con, ” UPDATE berkas_peserta SET status = ‘Lengkap’ WHERE id_berkas = 2; “)

dbExecute(con, ” DELETE FROM hasil_verifikasi WHERE id_hasil IN (2, 3); “)

dbGetQuery(con, ” SELECT c.NO_CASIS, c.nama AS nama_taruna, j.nama_dokumen AS dokumen, b.status AS status_berkas, p.nama_panitia AS verifikator, h.catatan, h.tanggal_verifikasi FROM hasil_verifikasi h INNER JOIN berkas_peserta b ON h.id_berkas = b.id_berkas INNER JOIN calon_taruna c ON b.NO_CASIS = c.NO_CASIS INNER JOIN jenis_dokumen j ON b.id_dokumen = j.id_dokumen INNER JOIN panitia_verifikasi p ON h.id_panitia = p.id_panitia ORDER BY c.NO_CASIS, h.tanggal_verifikasi ASC; “)

teguh <- dbGetQuery(con, ” SELECT c.nama AS nama_taruna, j.nama_dokumen AS dokumen, b.status, p.nama_panitia AS verifikator, h.catatan, h.tanggal_verifikasi FROM hasil_verifikasi h JOIN berkas_peserta b ON h.id_berkas = b.id_berkas JOIN calon_taruna c ON b.NO_CASIS = c.NO_CASIS JOIN jenis_dokumen j ON b.id_dokumen = j.id_dokumen JOIN panitia_verifikasi p ON h.id_panitia = p.id_panitia; “) print(teguh) View(teguh) dbGetQuery(con,” SELECT COUNT(*) AS jumlah_tidak_lengkap FROM berkas_peserta WHERE status = ‘Tidak Lengkap’; “)