Membuat koneksi database dengan Rstudio

library(DBI)
## Warning: package 'DBI' was built under R version 4.5.2
library(RMySQL)
## Warning: package 'RMySQL' was built under R version 4.5.2

Menghubungkan server database ke Rstudio

dbb <- dbConnect(
  RMySQL::MySQL(),
  host = "127.0.0.1",
  user = "root",
  password = "")

Membuat dan Mengakses Database Baru

dbExecute(dbb, "CREATE DATABASE IF NOT EXISTS LombaMatematikaUniv")
## [1] 1
dbExecute(dbb, "USE LombaMatematikaUniv")
## [1] 0

Membuat Tabel Data Baru Dalam Database

1. Tabel Kampus

#Tabel Kampus
dbExecute(dbb, "
CREATE TABLE kampus (
 id_kampus INT PRIMARY KEY,
 nama_kampus VARCHAR(100) NOT NULL,
 kota VARCHAR (50) NOT NULL
);
 ")
## [1] 0

2. Tabel Jurusan

#Tabel Jurusan
dbExecute(dbb, "
CREATE TABLE jurusan (
 id_jurusan INT PRIMARY KEY,
 nama_jurusan VARCHAR(100) NOT NULL
);
 ")
## [1] 0

3. Tabel peserta

#Tabel Peserta
dbExecute(dbb, "
CREATE TABLE peserta (
    id_peserta INT PRIMARY KEY,
    nama_peserta VARCHAR (50) NOT NULL,
    id_kampus INT NOT NULL,
    id_jurusan INT NOT NULL,
    FOREIGN KEY (id_kampus) REFERENCES kampus(id_kampus),
    FOREIGN KEY (id_jurusan) REFERENCES jurusan(id_jurusan)

);
 ")
## [1] 0

4. Tabel Penilai

#Tabel Jurusan
dbExecute(dbb, "
CREATE TABLE  penilai (
    id_penilai INT PRIMARY KEY,
    nama_penilai VARCHAR(50) NOT NULL,
    jabatan VARCHAR(50) NOT NULL
);
 ")
## [1] 0

5. Tabel Penilaian

#Tabel Jurusan
dbExecute(dbb, "
CREATE TABLE penilaian (
   id_penilaian INT PRIMARY KEY,
    id_peserta INT NOT NULL,
    id_penilai INT NOT NULL,
    nilai INT NOT NULL,
    tanggal DATE NOT NULL,
    FOREIGN KEY (id_peserta) REFERENCES peserta(id_peserta),
    FOREIGN KEY (id_penilai) REFERENCES penilai(id_penilai)

);
 ")
## [1] 0

Memasukkan Data ke Dalam Tabel

1. Memasukkan Data ke Dalam Tabel Kampus

#Tabel Kampus
dbExecute(dbb, "
  INSERT INTO kampus (id_kampus, nama_kampus, kota) VALUES
    (1, 'Universitas Indonesia', 'Depok'),
    (2, 'Universitas Gadjah Mada', 'Yogyakarta'),
    (3, 'Institut Teknologi Bandung', 'Bandung'),
    (4, 'Universitas Airlangga', 'Surabaya'),
    (5, 'Universitas Diponegoro', 'Semarang');
")
## [1] 5

2. Memasukkan Data ke Dalam Tabel Jurusan

#Tabel Jurusan
dbExecute(dbb, "
INSERT INTO jurusan (id_jurusan, nama_jurusan) VALUES
   (1,'Matematika'), (2,'Statistika'), (3,'Fisika'), (4,'Kimia'),(5,'Biologi'), (6,'Teknik Industri'), (7,'Teknik Informatika'),(8,'Sistem Informasi'), (9,'Ekonomi'), (10,'Akuntansi'),(11,'Manajemen'), (12,'Arsitektur'), (13,'Farmasi'), (14,'Teknik Mesin'), (15,'Ilmu Komputer');
")
## [1] 15

3. Memasukkan Data ke Dalam tabel Peserta

#Tabel Peserta
dbExecute(dbb, "
INSERT INTO peserta (id_peserta, nama_peserta, id_kampus, id_jurusan) VALUES
    (1,'Ahmad Rizki',1,1),
    (2,'Budi Hartono',1,2),
    (3,'Citra Lestari',2,3),
    (4,'Dewi Anjani',2,4),
    (5,'Eko Saputra',3,5),
    (6,'Fanny Widya',3,6),
    (7,'Gilang Pratama',4,7),
    (8,'Hilda Nur',4,8),
    (9,'Intan Permata',5,9),
    (10,'Joko Santoso',5,10),
    (11,'Kiki Amelia',1,11),
    (12,'Lutfi Ramadhan',2,12),
    (13,'Mira Susanti',3,13),
    (14,'Nanda Putri',4,14),
    (15,'Oki Pradana',5,15);
")
## [1] 15

4. Memasukkan Data ke Dalam tabel Penilai

#Tabel Penilai
dbExecute(dbb, "
  INSERT INTO penilai(id_penilai, nama_penilai, jabatan) VALUES
    (1,'Dr. Rina Marlina', 'Dosen Matematika'),
    (2,'Prof. Andi Wijaya', 'Ketua Departemen'),
    (3,'Dr. Yusuf Maulana', 'Dewan Juri');
")
## [1] 3

5. Memasukkan Data ke Dalam Tabel Penilaian

#Tabel Penilaian
dbExecute(dbb, "
  INSERT INTO penilaian (id_penilaian, id_peserta, id_penilai, nilai, tanggal) VALUES
    (1,1,1,85,'2025-02-01'),
    (2,2,2,90,'2025-02-01'),
    (3,3,1,88,'2025-02-02'),
    (4,4,3,91,'2025-02-02'),
    (5,5,2,87,'2025-02-03'),
    (6,6,1,92,'2025-02-03'),
    (7,7,3,89,'2025-02-04'),
    (8,8,2,94,'2025-02-04'),
    (9,9,1,86,'2025-02-05'),
    (10,10,2,90,'2025-02-05'),
    (11,11,3,88,'2025-02-06'),
    (12,12,1,93,'2025-02-06'),
    (13,13,2,95,'2025-02-07'),
    (14,14,3,89,'2025-02-07'),
    (15,15,1,91,'2025-02-08');
")
## [1] 15

Studi Kasus

Berdasarkan database yang telah dibuat, maka selanjutnya tabel tersebut akan ditampilkan.

Menampilkan isi Tabel

1. Menampilkan tabel kampus

#tabel kampus
data_kampus <- dbGetQuery(dbb, "SELECT * FROM kampus")
DT::datatable(
data_kampus,
options = list(pageLength = 10, autoWidth = TRUE),
rownames = FALSE
)

2. Menampilkan tabel jurusan

#tabel jurusan
data_jurusan <- dbGetQuery(dbb, "SELECT * FROM jurusan")
DT::datatable(
data_jurusan,
options = list(pageLength = 10, autoWidth = TRUE),
rownames = FALSE
)

3. Menampilkan tabel Peserta

#tabel peserta
data_peserta <- dbGetQuery(dbb, "SELECT * FROM peserta")
DT::datatable(
data_peserta,
options = list(pageLength = 10, autoWidth = TRUE),
rownames = FALSE
)

4. Menampilkan tabel penilai

#tabel penilai
data_penilai <- dbGetQuery(dbb, "SELECT * FROM penilai")
DT::datatable(
data_penilai,
options = list(pageLength = 10, autoWidth = TRUE),
rownames = FALSE
)

5. Menampilkan tabel penilaian

#tabel penilaian
data_penilaian <- dbGetQuery(dbb, "SELECT * FROM penilaian")
DT::datatable(
data_penilaian,
options = list(pageLength = 10, autoWidth = TRUE),
rownames = FALSE
)

Mengurutkan Tabel dengan ORDER BY

#urutan dari terkecil
data_penilaian <- dbGetQuery(dbb, "SELECT * FROM Penilaian
ORDER BY nilai ASC;")
DT::datatable(
data_penilaian,
options = list(pageLength = 10, autoWidth = TRUE),
rownames = FALSE
)
#urutan dari terbesar
data_penilaian <- dbGetQuery(dbb, "SELECT * FROM Penilaian
ORDER BY nilai DESC;")
DT::datatable(
data_penilaian,
options = list(pageLength = 10, autoWidth = TRUE),
rownames = FALSE
)

Menampilkan Data Penilaian tertentu

data_penilaian <- dbGetQuery(dbb, "SELECT * FROM Penilaian WHERE nilai > 90;")
DT::datatable(
data_penilaian,
options = list(pageLength = 10, autoWidth = TRUE),
rownames = FALSE
)

Memperbarui Data (UPDATE)

dbExecute(dbb, "
    UPDATE penilaian
    SET nilai = nilai + 2
    WHERE id_peserta = 5;")
## [1] 1
data_penilaian <- dbGetQuery(dbb, "SELECT * FROM Penilaian WHERE id_peserta = 5;")
DT::datatable(
data_penilaian,
options = list(pageLength = 10, autoWidth = TRUE),
rownames = FALSE
)

Menghapus Data (DELETE)

dbExecute(dbb, "
    DELETE FROM penilaian
    WHERE id_penilaian = 15;")
## [1] 1
data_penilaian <- dbGetQuery(dbb, "SELECT * FROM Penilaian WHERE id_penilaian = 15;")
DT::datatable(
data_penilaian,
options = list(pageLength = 10, autoWidth = TRUE),
rownames = FALSE
)

Menampilkan Data Gabungan (INNER JOIN)

data_penilaian <- dbGetQuery(dbb, "
   SELECT p.nama_peserta, k.nama_kampus, j.nama_jurusan,
         pn.nilai, pn.tanggal, pl.nama_penilai
   FROM penilaian pn
   INNER JOIN peserta p ON pn.id_peserta = p.id_peserta
   INNER JOIN kampus k ON p.id_kampus = k.id_kampus
   INNER JOIN jurusan j ON p.id_jurusan = j.id_jurusan
   INNER JOIN penilai pl ON pn.id_penilai = pl.id_penilai;
")

DT::datatable(
  data_penilaian,
  options = list(pageLength = 10, autoWidth = TRUE),
  rownames = FALSE
)

Menggunakan Fungsi Agregat (SUM,AVG)

data_penilaian <- dbGetQuery(dbb, "
   SELECT SUM(nilai) AS total_nilai,
          AVG(nilai) AS rata_rata_nilai
   FROM penilaian;
")
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 0 imported
## as numeric
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 1 imported
## as numeric
DT::datatable(
  data_penilaian,
  options = list(pageLength = 10, autoWidth = TRUE),
  rownames = FALSE
)

Tampilan Laporan Akhir

data_penilaian <- dbGetQuery(dbb, "
   SELECT p.nama_peserta, k.nama_kampus, j.nama_jurusan,
          pn.nilai, pn.tanggal, pl.nama_penilai
   FROM penilaian pn
   JOIN peserta p ON pn.id_peserta = p.id_peserta
   JOIN kampus k ON p.id_kampus = k.id_kampus
   JOIN jurusan j ON p.id_jurusan = j.id_jurusan
   JOIN penilai pl ON pn.id_penilai = pl.id_penilai;
")
DT::datatable(
  data_penilaian,
  options = list(pageLength = 10, autoWidth = TRUE),
  rownames = FALSE
)