Advanced SQL Query with R

Annebel D Clarissa

Package yang digunakan

library(DBI)
library(RSQLite)
library(tidyverse)
library(DT)

Database

Database yang digunakan adalah database chinook yang terdiri dari 13 tabel seperti dapat terlihat pada diagram berikut

Koneksi ke database

Berikut adalah syntax untuk menyambungkan database chinook dengan R

Chinook <-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/db/Chinook.db")

Melihat tabel dalam Database

Untuk melihat tabel-tabel yang ada dalam database, bisa menggunakan perintah berikut

dbListTables(Chinook)

##  [1] "albums"          "artists"         "customers"       "employees"      
##  [5] "genres"          "invoice_items"   "invoices"        "media_types"    
##  [9] "playlist_track"  "playlists"       "sqlite_sequence" "sqlite_stat1"   
## [13] "tracks"

Akses database

Untuk mengakses data dari database, dapat dilakukan dengan 2 cara yaitu :

Mengambil satu tabel secara keseluruhan

Menggunakan fungsi dbReadTable pada package DBI, dengan memasukkan nama koneksi dan nama tabel yang akan diambil. Misal akan diambil tabel genres

tab_genre <- dbReadTable(Chinook, name="genres")

Menggunakan query

Terdapat 2 cara untuk melakukan query SQL dalam R. Misal akan ditampilkan judul lagu dari album dengan albumId=1. Bisa dilakukan dengan 2 cara sebagai berikut :

Cara 1

Menggunakan fungsi dbgetquery() pada package DBI dengan memasukkan nama koneksi dan statement query.

query <- dbGetQuery(Chinook, sql
            ("SELECT Name
              FROM Tracks
              WHERE AlbumId=1"))

Cara 2

Dengan menambahkan argument pada code chunk, yaitu connection, engine, dan output.var

Berikut adalah hasil yang ditampilkan

Advanced SQL Query

Selanjutnya akan dilakukan beberapa operasi SQL Query pada database chinook sebagai berikut

1. Operations on NULL values

NULL digunakan untuk mewakili nilai yang hilang dan bisa diinterpretasikan ke dalam 3 kondisi yaitu :
1. Tidak diketahui (unknown value)
2. Ada, namun tidak ingin diketahui (unavailable)
3. Tidak memiliki (Not applicable attribute)
Seringkali tidak mungkin untuk menentukan makna dari NULL, sehingga SQL tidak membedakan arti dari NULL

SQL memungkinkan query untuk mengecek adanya nilai NULL dengan IS NULL atau IS NOT NULL. SQL tidak menggunakan “=” atau “<>” untuk membandingkan nilai atribut dengan nilai NULL, karena SQL menganggap setiap nilai NULL berbeda dengan nilai NULL lainnya. Hal ini menyebabkan apabila terdapat kondisi join, tuple dengan nilai NULL tidak akan dimasukkan ke dalam hasil (kecuali outer join).

Query 1 : Menampilkan Nama Depan, Nama Belakang, Nama Perusahaan, dan Kota dari Customers dengan kondisi Nama Perusahaan bernilai NULL

SELECT FirstName, LastName, Company, City
FROM customers
WHERE Company IS NULL

2. Nested Queries

Beberapa query mengharuskan nilai yang ada dalam database diambil untuk kemudian dimasukkan dalam kondisi perbandingan. Nested query merupakan query SELECT-FROM-WHERE yang berada di dalam WHERE query sebelumnya (outer query). Operator perbandingan yang digunakan adalah IN

Query 2 : Menampilkan id dan judul-judul lagu yang dibeli di negara Denmark

SELECT TrackId, Name
FROM tracks
WHERE TrackId IN (SELECT TrackId 
                  FROM invoice_items
                  WHERE InvoiceId IN (SELECT InvoiceId 
                                      FROM invoices
                                      WHERE BillingCountry='Denmark'))
ORDER BY (TrackId)

Selain itu juga dapat digunakan operator lain selain operator IN dalam nested query, seperti =, >, >=, <, <=, <>

Query 2a : Menampilkan negara-negara dan total pembelian dimana customers melakukan pembelian lebih dari invoiceId ke 5 yang memiliki total pembelian 13.86

SELECT BillingCountry, Total
FROM invoices
WHERE Total > (SELECT Total 
               FROM invoices
               WHERE InvoiceId = 5)

3. Correlated Nested Queries

Secara umum, query yang ditulis dalam nested query menggunakan operator = atau IN dapat dinyatakan juga dengan kueri tunggal. Sebagai contoh, query 2 yang telah dilakukan sebelumya dengan nested query akan dinyatakan sebagai query tunggal sebagai berikut

Query 3 : Menampilkan id dan judul-judul lagu yang dibeli di negara Denmark

SELECT T.TrackId, T.Name
FROM tracks AS T, invoices AS I, invoice_items AS II
WHERE T.TrackId = II.TrackId AND I.InvoiceId = II.InvoiceId 
      AND I.BillingCountry='Denmark'
ORDER BY (T.TrackId)

4. EXISTS and NOT EXISTS Functions

EXISTS adalah suatu fungsi Boolean yang mengembalikan nilai TRUE atau FALSE. EXISTS digunakan untuk mengecek apakah hasil query yang dihasilkan oleh nested query kosong atau tidak, sehingga EXIST dan NOT EXISTS biasanya digunakan bersamaan dengan nested query. Sebagai contoh akan ditampilkan kembali query 2 dengan opsi lain menggunakan EXISTS

Query 4a : Menampilkan id dan judul-judul lagu yang dibeli di negara Denmark

SELECT TrackId, Name
FROM tracks as T
WHERE EXISTS (SELECT * 
              FROM invoice_items AS II, invoices AS I
              WHERE T.TrackId = II.TrackId AND I.InvoiceId = II.InvoiceId 
                    AND I.BillingCountry='Denmark')
ORDER BY (TrackId)

Contoh penggunaan NOT EXIST

Query 4b : Menampilkan nama depan dan nama belakang pegawai yang tidak menangani customers

SELECT FirstName, LastName
FROM employees as E
WHERE NOT EXISTS (SELECT * 
                  FROM customers as C
                  WHERE E.EmployeeId = C.SupportRepId)

5. EXPLICIT SETS

Opsi lain untuk menggantikan nested value adalah dengan menerapkan suatu set nilai eksplisit di dalam WHERE. Sebagai contoh dan perbandingan, akan dilakukan lagi Query 2 yang sebelumnya menggunakan nested value digantikan dengan explisit set

Query 5 : Menampilkan id dan judul-judul lagu yang dibeli di negara Denmark

SELECT T.TrackId, T.Name
FROM tracks AS T, invoice_items AS II, invoices AS I
WHERE T.TrackId = II.TrackId AND I.InvoiceId = II.InvoiceId
      AND BillingCountry IN('Denmark')
ORDER BY (T.TrackId)

6. RENAMING Attribute

Dalam SQL, dimungkinkan untuk mengganti nama attribute hasil query dengan menggunakan perintah AS.

Akan dicontohkan dengan menggunakan query 4b dimana attribute FirstName akan diubah menjadi Nama_Depan dan LastName akan diubah menjadi Nama_Belakang

Query 6 : Menampilkan nama depan dan nama belakang pegawai yang tidak menangani customers

SELECT FirstName AS Nama_Depan, LastName AS Nama_Belakang
FROM employees as E
WHERE NOT EXISTS (SELECT * 
                  FROM customers as C
                  WHERE E.EmployeeId = C.SupportRepId)

7. Joined Relations

Joined relations dimasukkan ke dalam klausa FROM. Dapat menggunakan JOIN, CROSS JOIN, atau NATURAL JOIN.

Query 7 : Menampilkan album yang penyanyinya adalah Aerosmith

SELECT Title
FROM albums AS AL, artists AS AR
WHERE Name='Aerosmith' AND AL.ArtistId=AR.ArtistId

atau dapat juga dengan menggunakan query seperti di bawah ini

SELECT Title
FROM (albums as AL JOIN artists AS AR ON AR.ArtistId=AL.ArtistId)
WHERE Name='Aerosmith'

SELECT Title
FROM (albums NATURAL JOIN artists)
WHERE Name='Aerosmith'

Dapat pula dilakukan nest join, dimana salah satu tabel yang di join merupakan tabel hasil join. Hal ini memungkinkan 3 atau lebih tabel menjadi satu tabel gabungan yang disebut multiway join.

Contoh lainnya adalah dengan melakukan join terlebih dahulu antara tabel album dengan tabel artist, kemudian hasil join tersebut dijoin lagi dengan tabel tracks

Query 7c : Menampilkan judul lagu, composer, dan harga satuan dari band Queen.

SELECT T.Name, T.Composer, T.UnitPrice
FROM ((albums AS AL JOIN artists AS AR ON AR.ArtistId=AL.ArtistId)                    JOIN tracks AS T ON T.AlbumId=AL.AlbumId)
WHERE AR.Name='Queen'

8. Aggregate Function

Aggregate function adalah fungsi-fungsi yang dapat digunakan saat query yang mencakup operasi COUNT, SUM, MAX, MIN, dan AVG

Query 8 : Menampilkan jumlah pembelian, Pembelian tertinggi, Pembelian terendah, dan rata-rata pembelian yang dilakukan oleh semua customers

SELECT SUM(Total) AS Jml_Pembelian, MAX(Total) AS Pembelian_Tertinggi,
      MIN(Total) AS Pembelian_Terendah, AVG(Total) AS Rata_Pembelian
FROM invoices

Dapat juga ditambahkan suatu kondisi tertentu sebagai berikut

Query 8a : Menampilkan jumlah pembelian, Pembelian tertinggi, Pembelian terendah, dan rata-rata pembelian yang dilakukan oleh customers di negara USA

SELECT SUM(Total) AS Jml_Pembelian, MAX(Total) AS Pembelian_Tertinggi,
       MIN(Total) AS Pembelian_Terendah, AVG(Total) AS Rata_Pembelian
FROM invoices
WHERE BillingCountry='USA'

Selain itu dapat pula digabungkan dengan query JOIN sebagai berikut

Query 8b : Menampilkan jumlah pembelian, Pembelian tertinggi, Pembelian terendah, dan rata-rata pembelian yang dilakukan oleh customers bernama depan Dominique

SELECT SUM(Total) AS Jml_Pembelian, MAX(Total) AS Pembelian_Tertinggi,
      MIN(Total) AS Pembelian_Terendah, AVG(Total) AS Rata_Pembelian
FROM (invoices AS I JOIN customers AS C ON I.CustomerId=C.CustomerId)
WHERE C.FirstName='Dominique'

Contoh penggunaan fungsi agregate COUNT

Query 8c : Menghitung banyaknya lagu

SELECT COUNT(*) AS JumlahLagu
FROM tracks

Query 8d : Menghitung banyaknya lagu yang memiliki genre metal

SELECT COUNT(*) AS JumlahLaguMetal
FROM tracks AS T, genres AS G
WHERE T.GenreId=G.GenreId AND G.Name='Metal'

Queri 8e : Menampilkan nama perusahaan dan negaranya yang melakukan transaksi lebih dari 5 kali (penggabungan dengan nested query)

SELECT C.Company, C.Country
FROM customers AS C
WHERE (SELECT COUNT(*)
       FROM invoices AS I
       WHERE I.CustomerId=C.CustomerId)>5

9. Grouping

Jika kita ingin menerapkan fungsi agregat untuk tiap subkelompok tupples, dapat digunakan perintah GROUP BY. Perintah GROUP BY digunakan untuk untuk menentukan atribute mana yang dijadikan dasar pengelompokkan.

Query 9a : Untuk setiap genre, akan ditampilkan banyaknya lagu dan rata-rata harganya

SELECT GenreId, COUNT(*) AS JumlahLagu, AVG(UnitPrice) AS RataRataHarga
FROM tracks
GROUP BY GenreId

Query 9b : Untuk setiap Tipe Media, akan ditampilkan MediaTypeId, Nama Media, dan jumlah lagu yang menggunakan media tersebut (penggabungan dengan operasi join)

SELECT M.MediaTypeId, M.Name AS Media, COUNT(*) AS JumlahLagu
FROM media_types AS M, tracks AS T
WHERE M.MediaTypeId=T.MediaTypeId
GROUP BY M.MediaTypeId, M.Name

10. HAVING clause

Jika kita ingin mengambil nilai dari suatu fungsi agregat hanya untuk grup yang memenuhi kondisi tertentu, maka dapat digunakan perintah HAVING. HAVING digunakan untuk menentukan kondisi seleksi pada grup (bukan tupples secara individu)

Query 10a : Pada Query 9b sebelumnya, akan diseleksi lagi hanya menampilkan jumlah lagu berdasarkan medianya yang memiliki jumlah lagu lebih dari 100 lagu

SELECT M.MediaTypeId, M.Name AS Media, COUNT(*) AS JumlahLagu
FROM media_types AS M, tracks AS T
WHERE M.MediaTypeId=T.MediaTypeId
GROUP BY M.MediaTypeId, M.Name
HAVING COUNT(*)>100

Query 10b : Untuk setiap tipe media, akan ditampilkan mediatypeId, nama media, dan jumlah lagu yang menggunakan media tersebut, hanya untuk lagu dengan genre Alternative (menambahkan tabel baru yaitu genres)

SELECT M.MediaTypeId, M.Name AS Media, COUNT(*) AS JumlahLagu
FROM media_types AS M, tracks AS T, genres AS G
WHERE M.MediaTypeId=T.MediaTypeId AND G.GenreId=T.GenreId 
      AND G.Name='Alternative'
GROUP BY M.MediaTypeId, M.Name