Query Database dengan SQL di Software R

Pendahuluan

Package rmarkdown di R menfasilitasi penggunaan SQL engine, untuk melakukan eksekusi syntax SQL dan menampilkan hasilnya.

Untuk menggunakannya, Anda harus membuat suatu obyek connection terlebih dahulu, lalu kemudian menggunakan connection ini bersama engine sql

Berikut ini adalah proses membentuk suatu obyek db.

db<-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/company.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.

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

Adapun company.db dari El Masri, dapat Anda buat dengan Syntax SQL Halaman Github berikut ini : El Masri Company Database

Syntax SQL

Commands SQL

Commands Keterangan
SELECT Memilih Data dari Database
FROM Memilih table darimana data diambil
WHERE Menyaring query dengan mencocokkan suatu 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

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
  7. LIMIT

Contoh

Untuk Contoh ini, akan digunakan Table EMPLOYEE dari Database company.db

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

1. 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

2. 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

3. 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

4. Seperti Poin 3, gaji diurutkan dari yang 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

Anda, juga bisa menuliskan Syntax di atas, sebagai berikut

SELECT
  FNAME, LNAME, SALARY
FROM
  EMPLOYEE
WHERE
  SALARY > 30000
  AND
  SEX = 'M'
ORDER BY
  3 DESC;
3 records
Fname Lname Salary
James Borg 55000
Franklin Wong 40000
Ramesh Narayan 38000

5. Menghitung Jumlah Baris

SELECT
  COUNT(*)
FROM
  EMPLOYEE;
1 records
COUNT(*)
8

6. Menghitung Total Gaji

SELECT
  SUM(SALARY)
FROM
  EMPLOYEE;
1 records
SUM(SALARY)
281000

7. Menampilkan Nama dan Gaji, dari Employee dengan Gaji Terbesar

SELECT
  FNAME, LNAME, MAX(SALARY)
FROM
  EMPLOYEE;
1 records
Fname Lname MAX(SALARY)
James Borg 55000

8. Menghitung Gaji rata-rata dari masing-masing Jenis Kelamin

SELECT
  SEX, AVG(SALARY)
FROM
  EMPLOYEE
GROUP BY
  SEX;
2 records
Sex AVG(SALARY)
F 31000
M 37600

  1. Badan Informasi Geospasial, ↩︎

  2. IPB University,↩︎