library(DBI)
## Warning: package 'DBI' was built under R version 4.5.2
library(RSQLite)
## Warning: package 'RSQLite' was built under R version 4.5.2
library(dplyr)
## Warning: package 'dplyr' was built under R version 4.5.2
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
con <- dbConnect(SQLite(), ":memory:")

R Markdown

This is an R Markdown document. Markdown is a simple formatting syntax for authoring HTML, PDF, and MS Word documents. For more details on using R Markdown see http://rmarkdown.rstudio.com.

When you click the Knit button a document will be generated that includes both content as well as the output of any embedded R code chunks within the document. You can embed an R code chunk like this:

dbExecute(con, "
  CREATE TABLE calon_taruna (
    NO_CASIS INTEGER PRIMARY KEY,
    nama TEXT,
    asal_daerah TEXT,
    tanggal_lahir TEXT
  );
")
## [1] 0

Including Plots

You can also embed plots, for example:

dbExecute(con, "
  CREATE TABLE jenis_dokumen (
    id_dokumen INTEGER PRIMARY KEY,
    nama_dokumen TEXT
  );
")
## [1] 0

Note that the echo = FALSE parameter was added to the code chunk to prevent printing of the R code that generated the plot.

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)
  );
")
## [1] 0
dbExecute(con, "
  CREATE TABLE panitia_verifikasi (
    id_panitia INTEGER PRIMARY KEY,
    nama_panitia TEXT,
    jabatan TEXT
  );
")
## [1] 0
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)
  );
")
## [1] 0
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');
    ")
## [1] 3
dbExecute(con, "
  INSERT INTO jenis_dokumen VALUES
    (1, 'Ijazah SMA'),
    (2, 'KTP'),
    (3, 'Kartu Keluarga'),
    (4, 'SKCK'),
    (5, 'SKBS');
")
## [1] 5
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');
")
## [1] 5
dbExecute(con, "
  INSERT INTO panitia_verifikasi VALUES
    (1, 'Kompol Budi Santoso', 'Ketua Verifikasi'),
    (2, 'Ipda Sari Putri', 'Verifikator');
")
## [1] 2
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');
")
## [1] 3
dbListTables(con)
## [1] "berkas_peserta"     "calon_taruna"       "hasil_verifikasi"  
## [4] "jenis_dokumen"      "panitia_verifikasi"
dbGetQuery(con, "SELECT * FROM calon_taruna")
##   NO_CASIS                  nama       asal_daerah tanggal_lahir
## 1       16 Teguh Febrian Laksana Sulawesi Tenggara    2005-02-21
## 2       21         Anang Saputra  Sulawesi Selatan    2006-10-22
## 3      440           Nelson Alom             Papua    2005-06-01
dbGetQuery(con, "SELECT * FROM jenis_dokumen")
##   id_dokumen   nama_dokumen
## 1          1     Ijazah SMA
## 2          2            KTP
## 3          3 Kartu Keluarga
## 4          4           SKCK
## 5          5           SKBS
dbGetQuery(con, "
  SELECT * FROM hasil_verifikasi
  ORDER BY tanggal_verifikasi ASC;
")
##   id_hasil id_berkas id_panitia               catatan tanggal_verifikasi
## 1        1         1          1                 Valid         2025-01-10
## 2        2         2          2        SKCK Tidak Sah         2025-01-11
## 3        3         5          1 Dokumen Tidak Lengkap         2025-01-12
dbGetQuery(con, "
  SELECT * FROM berkas_peserta
  WHERE status = 'Tidak Lengkap';
")
##   id_berkas NO_CASIS id_dokumen        status
## 1         4       21          3 Tidak Lengkap
## 2         5      440          5 Tidak Lengkap
dbExecute(con, "
  UPDATE berkas_peserta
  SET status = 'Lengkap'
  WHERE id_berkas = 2;
")
## [1] 1
dbExecute(con, "
  DELETE FROM hasil_verifikasi
  WHERE id_hasil IN (2, 3);
")
## [1] 2
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;
")
##   NO_CASIS           nama_taruna    dokumen status_berkas         verifikator
## 1       16 Teguh Febrian Laksana Ijazah SMA       Lengkap Kompol Budi Santoso
##   catatan tanggal_verifikasi
## 1   Valid         2025-01-10
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)
##             nama_taruna    dokumen  status         verifikator catatan
## 1 Teguh Febrian Laksana Ijazah SMA Lengkap Kompol Budi Santoso   Valid
##   tanggal_verifikasi
## 1         2025-01-10
View(teguh)
dbGetQuery(con, "
  SELECT COUNT(*) AS jumlah_tidak_lengkap
  FROM berkas_peserta
  WHERE status = 'Tidak Lengkap';
")
##   jumlah_tidak_lengkap
## 1                    2