Package

Package yang digunakan untuk mengolah database menggunakan R software adalah:

DBI digunakan untuk berinteraksi dengan berbagai database. Package ini bertindak sebagai jembatan antara R dan berbagai sistem manajemen database (DBMS) seperti MySQL, PostgreSQL, SQLite, dan lainnya. DBI menyediakan cara menjalankan SQL querry, dan export hasil SQL querry struktur data R seperti data.frame
RSQLite digunakan untuk bekerja dengan database SQLite menggunakan R.

library(dplyr)

## 
## 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

library(tidyverse)

## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ forcats   1.0.0     ✔ readr     2.1.4
## ✔ ggplot2   3.4.3     ✔ stringr   1.5.0
## ✔ lubridate 1.9.2     ✔ tibble    3.2.1
## ✔ purrr     1.0.2     ✔ tidyr     1.3.0

## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(DBI)
library(RSQLite)

Database

Database yang digunakan sebagai contoh adalah Chinook Database. Database berikut bisa didowload pada link berikut:

Download Database

Koneksi ke SQLite

Cara Pertama

Secara umum, koneksi R ke SQLite dapat menggunakan syntax berikut:

dbConnect(RSQLite::SQLite(), path = ":dbname:")

dbConnect berasal dari package DBI dan SQLite berasal dari package RSQLite

chinook <-dbConnect(SQLite(), "~/Desktop/ASPRAK MANDAREL/Chinook_Sqlite.sqlite")

Kemudian, dengan fungsi dbListTables dari package RSQLitedapat digunakan untuk melihat tabel-tabel yang terdapat di dalam database SQLite.

dbListTables(chinook)

##  [1] "Album"         "Artist"        "Customer"      "Employee"     
##  [5] "Genre"         "Invoice"       "InvoiceLine"   "MediaType"    
##  [9] "Playlist"      "PlaylistTrack" "Track"

Untuk mengakses tabel pada database dapat digunakan fungsi tbl dari package dplyr. Fungsi tbl berguna untuk melakukan konversi tabel ke bentukdata.frame dalam R. Berikut ilustrasinya

customer <- tbl(src=chinook,"Customer")

Perhatikan bahwa customer merupakan object dengan class tbl yang dapat diperlakukan mirip seperti data.frame.

Cara Kedua

db <-DBI::dbConnect(SQLite(), "~/Downloads/COMPANY R5.db")

Obyek db ini mempunyai Class SQLiteConnection

class(db)

## [1] "SQLiteConnection"
## attr(,"package")
## [1] "RSQLite"

Object db ini Anda gunakan pada opsi connection, kemudian pada opsi engine digunakan sql. Sehingga di dalamnya Anda bisa menulis syntax SQL.

SELECT * FROM EMPLOYEE

8 records
Fname	Minit	Lname	Ssn	Bdate	Address	Sex	Salary	Super_ssn	Dno
John	B	Smith	123456789	1965-01-09	731 Fondren, Houston TX	M	30000	333445555	5
Franklin	T	Wong	333445555	1965-12-08	638 Voss, Houston TX	M	40000	888665555	5
Alicia	J	Zelaya	999887777	1968-01-19	3321 Castle, Spring TX	F	25000	987654321	4
Jennifer	S	Wallace	987654321	1941-06-20	291 Berry, Bellaire TX	F	43000	888665555	4
Ramesh	K	Narayan	666884444	1962-09-15	975 Fire Oak, Humble TX	M	38000	333445555	5
Joyce	A	English	453453453	1972-07-31	5631 Rice, Houston TX	F	25000	333445555	5
Ahmad	V	Jabbar	987987987	1969-03-29	980 Dallas, Houston TX	M	25000	987654321	4
James	E	Borg	888665555	1937-11-10	450 Stone, Houston TX	M	55000	NA	1

Syntax SQL

Commands SQL

Commands	Keterangan
`SELECT`	Memilih Data dari Database
`FROM`	Memilih table darimana data diambil
`WHERE`	Menyaring query dengan mencocokkansuatu kondisi
`AS`	Mengubah nama kolom atau tabel dengan suatu alias
`JOIN`	Menggabungkan baris dari 2 atau lebih tabel
`AND`	Kombinasi Kondisi Query. Semua Kondisi harus terpenuhi
`OR`	Kombinasi Kondisi Query. Salah satu Kondisi harus terpenuhi
`LIMIT`	Membatasi jumlah baris yang ditampilkan
`IN`	Spesifikasi beberapa nilai ketika menggunakan`WHERE`
`IS NULL`	Mengembalikan baris dengan nilai`NULL`
`LIKE`	Mencari pola tertentu di suatu kolom
`GROUP BY`	Mengelompokkan Data
`ORDER BY`	Mengurutkan Data, default ASC. Gunakan DESC untuk sebaliknya
`HAVING`	Sama dengan `WHERE`tapi untuk Groups
`COUNT`	Menghitung jumlah baris
`SUM`	Menghitung jumlah dari kolom
`AVG`	Menghitung rata-rata dari kolom
`MIN`	Menghitung nilai minimal dari kolom
`MAX`	Menghitung nilai maksimum dari kolom

Order of Execution

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

Contoh

SELECT * FROM PROJECT;

6 records
Pname	Pnumber	Plocation	Dnum
ProductX	1	Bellaire	5
ProductY	2	Sugarland	5
ProductZ	3	Houston	5
Computerization	10	Stafford	4
Reorganization	20	Houston	1
Newbenefits	30	Stafford	4

Memilih Semua Kolom dengan Filter Tertentu

SELECT * FROM  EMPLOYEE
WHERE  SALARY < 30000;

3 records
Fname	Minit	Lname	Ssn	Bdate	Address	Sex	Salary	Super_ssn	Dno
Alicia	J	Zelaya	999887777	1968-01-19	3321 Castle, Spring TX	F	25000	987654321	4
Joyce	A	English	453453453	1972-07-31	5631 Rice, Houston TX	F	25000	333445555	5
Ahmad	V	Jabbar	987987987	1969-03-29	980 Dallas, Houston TX	M	25000	987654321	4

Memilih 5 Baris Pertama dari 2 Kolom
```
SELECT  Fname, Lname
From  EMPLOYEE
LIMIT 5;
```
5 records

Fname Lname

John Smith

Franklin Wong

Alicia Zelaya

Jennifer Wallace

Ramesh Narayan

5 records
Fname	Lname
John	Smith
Franklin	Wong
Alicia	Zelaya
Jennifer	Wallace
Ramesh	Narayan

Memilih Semua Kolom dengan Beberapa Filter

SELECT * FROM EMPLOYEE
WHERE SALARY > 30000
AND SEX = 'M';

3 records
Fname	Minit	Lname	Ssn	Bdate	Address	Sex	Salary	Super_ssn	Dno
Franklin	T	Wong	333445555	1965-12-08	638 Voss, Houston TX	M	40000	888665555	5
Ramesh	K	Narayan	666884444	1962-09-15	975 Fire Oak, Humble TX	M	38000	333445555	5
James	E	Borg	888665555	1937-11-10	450 Stone, Houston TX	M	55000	NA	1

Pengurutan Data dari Terbesar
```
SELECT FNAME, LNAME, SALARY
FROM EMPLOYEE
WHERE SALARY > 30000
AND SEX ='M'
ORDER BY SALARY DESC;
```
3 records

Fname Lname Salary

James Borg 55000

Franklin Wong 40000

Ramesh Narayan 38000

Apabila mau mengurutkan dari besar ke kecil dapat menggunakan fungsi DESC sedangkan untuk mengurutkan dari kecil ke besar dapat menggunakan fungsi ASC
Menghitung Jumlah Baris
```
SELECT COUNT(*)
FROM EMPLOYEE;
```
1 records

COUNT(*)

8
Menghitung Total Gaji
```
SELECT SUM(SALARY)
FROM EMPLOYEE;
```
1 records

SUM(SALARY)

281000
Menampilkan Nama Pegawai dengan Gaji Tertinggi
```
SELECT FNAME, LNAME, MAX(SALARY)
FROM EMPLOYEE;
```
1 records

Fname Lname MAX(SALARY)

James Borg 55000
Menghitung Gaji Rata Rata dari Setiap Gender
```
SELECT SEX, AVG(SALARY)
FROM EMPLOYEE
GROUP BY SEX;
```
2 records

Sex AVG(SALARY)

F 31000

M 37600

PRAKTIKUM 6 - PENGOLAHAN DATABASE MENGGUNAKAN R

2023-09-21