R Markdown

1. Instalasi dan import package

install.packages(c(“DBI”, “RSQLite”, “dplyr”)) # jika belum ada library(DBI) library(RSQLite) library(dplyr)

2. Buat koneksi database SQLite

con <- dbConnect(SQLite(), “:memory:”)

3. Buat struktur tabel

dbExecute(con, ” CREATE TABLE pegawai ( id_pegawai INTEGER PRIMARY KEY, nama_pegawai TEXT, jabatan TEXT ); “) dbExecute(con,” CREATE TABLE nasabah ( id_nasabah INTEGER PRIMARY KEY, nama TEXT, alamat TEXT ); “) dbExecute(con,” CREATE TABLE rekening ( no_rekening INTEGER PRIMARY KEY, id_nasabah INTEGER, saldo REAL, FOREIGN KEY (id_nasabah) REFERENCES nasabah(id_nasabah) ); “) dbExecute(con,” CREATE TABLE jenis_transaksi ( id_jenis INTEGER PRIMARY KEY, nama_jenis TEXT ); “) dbExecute(con,” CREATE TABLE transaksi ( id_transaksi INTEGER PRIMARY KEY, no_rekening INTEGER, id_jenis INTEGER, id_pegawai INTEGER, tanggal TEXT, jumlah REAL, FOREIGN KEY (no_rekening) REFERENCES rekening(no_rekening), FOREIGN KEY (id_jenis) REFERENCES jenis_transaksi(id_jenis), FOREIGN KEY (id_pegawai) REFERENCES pegawai(id_pegawai) ); “)

4. Masukkan data awal

dbExecute(con, ” INSERT INTO pegawai VALUES (1, ‘Dina Aulia’, ‘Teller’), (2, ‘Rizal Maulana’, ‘Customer Service’); “) dbExecute(con,” INSERT INTO nasabah VALUES (1, ‘Andi Setiawan’, ‘Jakarta’), (2, ‘Budi Santoso’, ‘Surabaya’), (3, ‘Clara Widya’, ‘Bandung’); “) dbExecute(con,” INSERT INTO rekening VALUES (1001, 1, 5000000), (1002, 2, 3000000), (1003, 3, 7000000); “) dbExecute(con,” INSERT INTO jenis_transaksi VALUES (1, ‘Setoran’), (2, ‘Penarikan’), (3, ‘Transfer’); “) dbExecute(con,” INSERT INTO transaksi VALUES (1, 1001, 1, 1, ‘2025-06-01’, 1000000), (2, 1002, 2, 2, ‘2025-06-03’, 500000), (3, 1003, 3, 1, ‘2025-06-05’, 2000000); “)

5. Tampilkan raw tabel

dbListTables(con) # daftar tabel dbGetQuery(con, “SELECT * FROM transaksi”) # lihat data transaksi

Urutkan transaksi dari jumlah terkecil ke terbesar (ASC)

dbGetQuery(con, ” SELECT * FROM transaksi ORDER BY jumlah ASC; “)

Urutkan transaksi dari jumlah terbesar ke terkecil (DESC)

dbGetQuery(con, ” SELECT * FROM transaksi ORDER BY jumlah DESC; “)

6. Alias + JOIN via SQL

query_full <- ” SELECT t.id_transaksi, n.nama AS nasabah, r.no_rekening, j.nama_jenis AS jenis, t.tanggal, t.jumlah, p.nama_pegawai AS pegawai FROM transaksi t JOIN rekening r ON t.no_rekening = r.no_rekening JOIN nasabah n ON r.id_nasabah = n.id_nasabah JOIN jenis_transaksi j ON t.id_jenis = j.id_jenis JOIN pegawai p ON t.id_pegawai = p.id_pegawai ORDER BY t.tanggal ;” full_df <- dbGetQuery(con, query_full) print(full_df)

7. Contoh analisis: transaksi > 1 juta

high_txn <- dbGetQuery(con, “SELECT * FROM transaksi WHERE jumlah > 1000000;”) print(high_txn)

8. Contoh dplyr: baca tabel ke data frame dan visualisasi interaktif

df_trans <- tbl(con, “transaksi”) %>% left_join(tbl(con, “jenis_transaksi”), by = c(“id_jenis” = “id_jenis”)) %>% mutate(tanggal = as.Date(tanggal)) %>% filter(jumlah > 0) %>% collect() print(df_trans)

9. Update saldo: contoh operasi debit

dbExecute(con, ” UPDATE rekening SET saldo = saldo - 500000 WHERE no_rekening = 1002; “)

10. Tampilkan saldo terbaru

dbGetQuery(con, “SELECT * FROM rekening ORDER BY no_rekening;”)

dbGetQuery(con, ” SELECT * FROM transaksi WHERE jumlah > 1000000 ORDER BY tanggal ASC; “) df\(jumlah <- format(df\)jumlah, scientific = FALSE) print(df)

dbExecute(con, ” UPDATE rekening SET saldo = saldo - 500000 WHERE no_rekening = 1002; “) dbGetQuery(con,”SELECT * FROM rekening WHERE no_rekening = 1002”)

dbExecute(con, ” DELETE FROM transaksi WHERE id_transaksi = 3; “) dbGetQuery(con,”SELECT * FROM transaksi”) dbGetQuery(con, ” SELECT t.id_transaksi, n.nama AS nasabah, j.nama_jenis AS jenis, t.tanggal, t.jumlah, p.nama_pegawai AS pegawai FROM transaksi t INNER JOIN rekening r ON t.no_rekening = r.no_rekening INNER JOIN nasabah n ON r.id_nasabah = n.id_nasabah INNER JOIN jenis_transaksi j ON t.id_jenis = j.id_jenis INNER JOIN pegawai p ON t.id_pegawai = p.id_pegawai; “)

dbGetQuery(con, ” SELECT SUM(jumlah) AS total_transaksi, AVG(jumlah) AS rata_rata_transaksi FROM transaksi; “) query_laporan <-” SELECT t.id_transaksi, n.nama AS nasabah, j.nama_jenis AS jenis, t.tanggal, t.jumlah, p.nama_pegawai AS pegawai FROM transaksi t JOIN rekening r ON t.no_rekening = r.no_rekening JOIN nasabah n ON r.id_nasabah = n.id_nasabah JOIN jenis_transaksi j ON t.id_jenis = j.id_jenis JOIN pegawai p ON t.id_pegawai = p.id_pegawai; ” laporan <- dbGetQuery(con, query_laporan) print(laporan) # View(laporan) # jika di RStudio

summary(cars)
##      speed           dist       
##  Min.   : 4.0   Min.   :  2.00  
##  1st Qu.:12.0   1st Qu.: 26.00  
##  Median :15.0   Median : 36.00  
##  Mean   :15.4   Mean   : 42.98  
##  3rd Qu.:19.0   3rd Qu.: 56.00  
##  Max.   :25.0   Max.   :120.00

Including Plots

You can also embed plots, for example:

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