P3b. Combining Multiple Tables with SQL

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

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

Berikut ini adalah proses membentuk suatu object db yang merupakan class SQLiteConnection

```{r, eval=TRUE}
db<-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/db/chinook.db")
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.

```{r, connection=db, engine="sql"}
SELECT
  *
FROM
  tracks;
Displaying records 1 - 10
TrackId Name AlbumId MediaTypeId GenreId Composer Milliseconds Bytes UnitPrice
1 For Those About To Rock (We Salute You) 1 1 1 Angus Young, Malcolm Young, Brian Johnson 343719 11170334 0.99
2 Balls to the Wall 2 2 1 NA 342562 5510424 0.99
3 Fast As a Shark 3 2 1 F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman 230619 3990994 0.99
4 Restless and Wild 3 2 1 F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman 252051 4331779 0.99
5 Princess of the Dawn 3 2 1 Deaffy & R.A. Smith-Diesel 375418 6290521 0.99
6 Put The Finger On You 1 1 1 Angus Young, Malcolm Young, Brian Johnson 205662 6713451 0.99
7 Let’s Get It Up 1 1 1 Angus Young, Malcolm Young, Brian Johnson 233926 7636561 0.99
8 Inject The Venom 1 1 1 Angus Young, Malcolm Young, Brian Johnson 210834 6852860 0.99
9 Snowballed 1 1 1 Angus Young, Malcolm Young, Brian Johnson 203102 6599424 0.99
10 Evil Walks 1 1 1 Angus Young, Malcolm Young, Brian Johnson 263497 8611245 0.99 
```

SQLite Join

Untuk demonstrasi digunakan tabel artist dan tabel album dari database chinook.db

Seorang astist mungkin tidak memiliki Album atau lebih dari satu Album. Sementara seorang Artis bisa memiliki lebih dari satu Album.

SQLite Inner Join

Syntax berikut mengembalikan Title dari tabel albums dan name dari tabel artists

SELECT
  Title,
  Name
FROM
  albums
INNER JOIN
  artists 
ON artists.ArtistId = albums.ArtistId;
Displaying records 1 - 10
Title Name
For Those About To Rock We Salute You AC/DC
Balls to the Wall Accept
Restless and Wild Accept
Let There Be Rock AC/DC
Big Ones Aerosmith
Jagged Little Pill Alanis Morissette
Facelift Alice In Chains
Warner 25 Anos Antônio Carlos Jobim
Plays Metallica By Four Cellos Apocalyptica
Audioslave Audioslave

Karena nama kolom ArtistID sama antara tabel artists dan albums maka Anda bisa menggunakan USING

SELECT
  Title,
  Name
FROM
  albums
INNER JOIN
  artists
USING(ArtistId);
Displaying records 1 - 10
Title Name
For Those About To Rock We Salute You AC/DC
Balls to the Wall Accept
Restless and Wild Accept
Let There Be Rock AC/DC
Big Ones Aerosmith
Jagged Little Pill Alanis Morissette
Facelift Alice In Chains
Warner 25 Anos Antônio Carlos Jobim
Plays Metallica By Four Cellos Apocalyptica
Audioslave Audioslave

SQLite LEFT JOIN

SELECT Name,Title
FROM artists LEFT JOIN albums
USING (ArtistId)
ORDER BY Name;
Displaying records 1 - 10
Name Title
A Cor Do Som NA
AC/DC For Those About To Rock We Salute You
AC/DC Let There Be Rock
Aaron Copland & London Symphony Orchestra A Copland Celebration, Vol. I
Aaron Goldberg Worlds
Academy of St. Martin in the Fields & Sir Neville Marriner The World of Classical Favourites
Academy of St. Martin in the Fields Chamber Ensemble & Sir Neville Marriner Sir Neville Marriner: A Celebration
Academy of St. Martin in the Fields, John Birch, Sir Neville Marriner & Sylvia McNair Fauré: Requiem, Ravel: Pavane & Others
Academy of St. Martin in the Fields, Sir Neville Marriner & Thurston Dart Bach: Orchestral Suites Nos. 1 - 4
Academy of St. Martin in the Fields, Sir Neville Marriner & William Bennett NA

SQLite Inner Join - Contoh 3 table

Sebuah tracks masuk dalam satu albums. Sementara sebuah albums memiliki banyak track. Kedua tabel ini dihubungkan melalui Kolom AlbumID

Sebuah albums dimiliki orang artists. Sementara seorang artists punya satu atau lebih albums. Kedua tabel ini terhubung melalui ArtistsID

Berikut adalah contoh syntax nya :

SELECT
    trackid,
    tracks.name AS track,
    albums.title AS album,
    artists.name AS artist
FROM
    tracks
    INNER JOIN albums ON albums.albumid = tracks.albumid
    INNER JOIN artists ON artists.artistid = albums.artistid;
Displaying records 1 - 10
TrackId track album artist
1 For Those About To Rock (We Salute You) For Those About To Rock We Salute You AC/DC
6 Put The Finger On You For Those About To Rock We Salute You AC/DC
7 Let’s Get It Up For Those About To Rock We Salute You AC/DC
8 Inject The Venom For Those About To Rock We Salute You AC/DC
9 Snowballed For Those About To Rock We Salute You AC/DC
10 Evil Walks For Those About To Rock We Salute You AC/DC
11 C.O.D. For Those About To Rock We Salute You AC/DC
12 Breaking The Rules For Those About To Rock We Salute You AC/DC
13 Night Of The Long Knives For Those About To Rock We Salute You AC/DC
14 Spellbound For Those About To Rock We Salute You AC/DC

Referensi

-.-. SQLite Tutorial - An Easy Way to Master SQLite Fast. (22 Februari 2021). https://www.sqlitetutorial.net/

Xie, Yihui, Dervieux Christophe, and Riederer. 2020. R Markdown Cookbook. Boca Raton, Florida: Chapman; Hall/CRC. https://bookdown.org/yihui/rmarkdown-cookbook/

  1. Badan Informasi Geospasial, ↩︎