BAB III – IMPLEMENTASI DAN ANALISIS SQL

Dokumen ini berisi implementasi basis data untuk studi kasus Toko Elektronik menggunakan bahasa SQL yang dijalankan melalui R dengan paket DBI, RSQLite, dan dplyr.

3.1 Membuat Koneksi Database

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:")

3.2 Membuat Tabel

dbExecute(con, "
  CREATE TABLE pegawai (
    id_pegawai INTEGER PRIMARY KEY,
    nama_pegawai TEXT,
    jabatan TEXT
  );
")
## [1] 0
dbExecute(con, "
  CREATE TABLE pelanggan (
    id_pelanggan INTEGER PRIMARY KEY,
    nama TEXT,
    alamat TEXT
  );
")
## [1] 0
dbExecute(con, "
  CREATE TABLE produk (
    id_produk INTEGER PRIMARY KEY,
    nama_produk TEXT,
    kategori TEXT,
    harga REAL
  );
")
## [1] 0
dbExecute(con, "
  CREATE TABLE transaksi (
    id_transaksi INTEGER PRIMARY KEY,
    id_pelanggan INTEGER,
    id_pegawai INTEGER,
    tanggal TEXT,
    FOREIGN KEY (id_pelanggan) REFERENCES pelanggan(id_pelanggan),
    FOREIGN KEY (id_pegawai) REFERENCES pegawai(id_pegawai)
  );
")
## [1] 0
dbExecute(con, "
  CREATE TABLE detail_transaksi (
    id_detail INTEGER PRIMARY KEY,
    id_transaksi INTEGER,
    id_produk INTEGER,
    jumlah INTEGER,
    subtotal REAL,
    FOREIGN KEY (id_transaksi) REFERENCES transaksi(id_transaksi),
    FOREIGN KEY (id_produk) REFERENCES produk(id_produk)
  );
")
## [1] 0

3.3 Mengisi Data ke Dalam Tabel

dbExecute(con, "
  INSERT INTO pegawai VALUES
    (1, 'Siti Rahma', 'Kasir'),
    (2, 'Andi Maulana', 'Admin Toko');
")
## [1] 2
dbExecute(con, "
  INSERT INTO pelanggan VALUES
    (1, 'Ahmad Yusuf', 'Kendari'),
    (2, 'Budi Saputra', 'Kolaka');
")
## [1] 2
dbExecute(con, "
  INSERT INTO produk VALUES
    (1, 'Laptop Lenovo', 'Laptop', 8500000),
    (2, 'Kipas Angin', 'Elektronik Rumah', 300000),
    (3, 'Speaker Bluetooth', 'Audio', 450000);
")
## [1] 3
dbExecute(con, "
  INSERT INTO transaksi VALUES
    (1, 1, 1, '2025-06-01'),
    (2, 2, 2, '2025-06-05');
")
## [1] 2
dbExecute(con, "
  INSERT INTO detail_transaksi VALUES
    (1, 1, 1, 1, 8500000),
    (2, 1, 3, 2, 900000),
    (3, 2, 2, 1, 300000);
")
## [1] 3

3.4 Menampilkan Isi Tabel

dbListTables(con)
## [1] "detail_transaksi" "pegawai"          "pelanggan"        "produk"          
## [5] "transaksi"
dbGetQuery(con, "SELECT * FROM detail_transaksi")
##   id_detail id_transaksi id_produk jumlah subtotal
## 1         1            1         1      1  8500000
## 2         2            1         3      2   900000
## 3         3            2         2      1   300000

3.5 Mengurutkan Data (ORDER BY)

dbGetQuery(con, "
  SELECT * FROM detail_transaksi
  ORDER BY subtotal ASC;
")
##   id_detail id_transaksi id_produk jumlah subtotal
## 1         3            2         2      1   300000
## 2         2            1         3      2   900000
## 3         1            1         1      1  8500000
dbGetQuery(con, "
  SELECT * FROM detail_transaksi
  ORDER BY subtotal DESC;
")
##   id_detail id_transaksi id_produk jumlah subtotal
## 1         1            1         1      1  8500000
## 2         2            1         3      2   900000
## 3         3            2         2      1   300000

3.6 Menampilkan Data Berdasarkan Kriteria (WHERE)

dbGetQuery(con, "
  SELECT * FROM detail_transaksi
  WHERE subtotal > 1000000
  ORDER BY subtotal ASC;
")
##   id_detail id_transaksi id_produk jumlah subtotal
## 1         1            1         1      1  8500000

3.7 Memperbarui Data (UPDATE)

dbExecute(con, "
  UPDATE produk
  SET harga = 320000
  WHERE id_produk = 2;
")
## [1] 1
dbGetQuery(con, "
  SELECT * FROM produk
  WHERE id_produk = 2;
")
##   id_produk nama_produk         kategori  harga
## 1         2 Kipas Angin Elektronik Rumah 320000

3.8 Menghapus Data (DELETE)

dbExecute(con, "
  DELETE FROM detail_transaksi
  WHERE id_detail = 3;
")
## [1] 1
dbGetQuery(con, "
  SELECT * FROM detail_transaksi;
")
##   id_detail id_transaksi id_produk jumlah subtotal
## 1         1            1         1      1  8500000
## 2         2            1         3      2   900000

3.9 Menggabungkan Data Menggunakan JOIN

dbGetQuery(con, "
  SELECT t.id_transaksi, p.nama AS pelanggan, pr.nama_produk,
         d.jumlah, d.subtotal, pg.nama_pegawai, t.tanggal
  FROM detail_transaksi d
  JOIN transaksi t ON d.id_transaksi = t.id_transaksi
  JOIN pelanggan p ON t.id_pelanggan = p.id_pelanggan
  JOIN produk pr ON d.id_produk = pr.id_produk
  JOIN pegawai pg ON t.id_pegawai = pg.id_pegawai;
")
##   id_transaksi   pelanggan       nama_produk jumlah subtotal nama_pegawai
## 1            1 Ahmad Yusuf     Laptop Lenovo      1  8500000   Siti Rahma
## 2            1 Ahmad Yusuf Speaker Bluetooth      2   900000   Siti Rahma
##      tanggal
## 1 2025-06-01
## 2 2025-06-01

3.10 Menggunakan Fungsi Agregat (SUM dan AVG)

dbGetQuery(con, "
  SELECT SUM(subtotal) AS total_penjualan,
         AVG(subtotal) AS rata_rata_penjualan
  FROM detail_transaksi;
")
##   total_penjualan rata_rata_penjualan
## 1         9400000             4700000

Selesai