Install Packages
library(tidyverse)
## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --
## v ggplot2 3.3.5 v purrr 0.3.4
## v tibble 3.1.3 v dplyr 1.0.7
## v tidyr 1.1.3 v stringr 1.4.0
## v readr 2.0.1 v forcats 0.5.1
## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()
library(RSQLite)
library(DBI)
Membentuk objek db yang merupakan class SQLiteConnection
Chinook<-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/db/chinook.db")
class(Chinook)
## [1] "SQLiteConnection"
## attr(,"package")
## [1] "RSQLite"
Melihat tabel di Chinook
RSQLite::dbListTables(Chinook)
## [1] "albums" "artists" "customers" "employees"
## [5] "genres" "invoice_items" "invoices" "media_types"
## [9] "playlist_track" "playlists" "sqlite_sequence" "sqlite_stat1"
## [13] "tracks"
Melihat Tabel Tracks di Chinook
tracks<-dplyr::tbl(Chinook,"tracks")
class(tracks)
## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"
tracks
## # Source: table<tracks> [?? x 9]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## TrackId Name AlbumId MediaTypeId GenreId Composer Milliseconds Bytes
## <int> <chr> <int> <int> <int> <chr> <int> <int>
## 1 1 For Tho~ 1 1 1 Angus Young~ 343719 1.12e7
## 2 2 Balls t~ 2 2 1 <NA> 342562 5.51e6
## 3 3 Fast As~ 3 2 1 F. Baltes, ~ 230619 3.99e6
## 4 4 Restles~ 3 2 1 F. Baltes, ~ 252051 4.33e6
## 5 5 Princes~ 3 2 1 Deaffy & R.~ 375418 6.29e6
## 6 6 Put The~ 1 1 1 Angus Young~ 205662 6.71e6
## 7 7 Let's G~ 1 1 1 Angus Young~ 233926 7.64e6
## 8 8 Inject ~ 1 1 1 Angus Young~ 210834 6.85e6
## 9 9 Snowbal~ 1 1 1 Angus Young~ 203102 6.60e6
## 10 10 Evil Wa~ 1 1 1 Angus Young~ 263497 8.61e6
## # ... with more rows, and 1 more variable: UnitPrice <dbl>
Melihat Tabel Playlist_track di Chinook
playlist_track<-dplyr::tbl(Chinook,"playlist_track")
class(playlist_track)
## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"
playlist_track
## # Source: table<playlist_track> [?? x 2]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## PlaylistId TrackId
## <int> <int>
## 1 1 3402
## 2 1 3389
## 3 1 3390
## 4 1 3391
## 5 1 3392
## 6 1 3393
## 7 1 3394
## 8 1 3395
## 9 1 3396
## 10 1 3397
## # ... with more rows
inner_join()
inner_join(tracks, playlist_track)
## Joining, by = "TrackId"
## # Source: lazy query [?? x 10]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## TrackId Name AlbumId MediaTypeId GenreId Composer Milliseconds Bytes
## <int> <chr> <int> <int> <int> <chr> <int> <int>
## 1 1 For Thos~ 1 1 1 Angus Youn~ 343719 1.12e7
## 2 1 For Thos~ 1 1 1 Angus Youn~ 343719 1.12e7
## 3 1 For Thos~ 1 1 1 Angus Youn~ 343719 1.12e7
## 4 2 Balls to~ 2 2 1 <NA> 342562 5.51e6
## 5 2 Balls to~ 2 2 1 <NA> 342562 5.51e6
## 6 2 Balls to~ 2 2 1 <NA> 342562 5.51e6
## 7 3 Fast As ~ 3 2 1 F. Baltes,~ 230619 3.99e6
## 8 3 Fast As ~ 3 2 1 F. Baltes,~ 230619 3.99e6
## 9 3 Fast As ~ 3 2 1 F. Baltes,~ 230619 3.99e6
## 10 3 Fast As ~ 3 2 1 F. Baltes,~ 230619 3.99e6
## # ... with more rows, and 2 more variables: UnitPrice <dbl>, PlaylistId <int>
left_join()
left_join(tracks, playlist_track)
## Joining, by = "TrackId"
## # Source: lazy query [?? x 10]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## TrackId Name AlbumId MediaTypeId GenreId Composer Milliseconds Bytes
## <int> <chr> <int> <int> <int> <chr> <int> <int>
## 1 1 For Thos~ 1 1 1 Angus Youn~ 343719 1.12e7
## 2 1 For Thos~ 1 1 1 Angus Youn~ 343719 1.12e7
## 3 1 For Thos~ 1 1 1 Angus Youn~ 343719 1.12e7
## 4 2 Balls to~ 2 2 1 <NA> 342562 5.51e6
## 5 2 Balls to~ 2 2 1 <NA> 342562 5.51e6
## 6 2 Balls to~ 2 2 1 <NA> 342562 5.51e6
## 7 3 Fast As ~ 3 2 1 F. Baltes,~ 230619 3.99e6
## 8 3 Fast As ~ 3 2 1 F. Baltes,~ 230619 3.99e6
## 9 3 Fast As ~ 3 2 1 F. Baltes,~ 230619 3.99e6
## 10 3 Fast As ~ 3 2 1 F. Baltes,~ 230619 3.99e6
## # ... with more rows, and 2 more variables: UnitPrice <dbl>, PlaylistId <int>
right_join()
right_join(tracks, playlist_track)
## Joining, by = "TrackId"
## # Source: lazy query [?? x 10]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## TrackId Name AlbumId MediaTypeId GenreId Composer Milliseconds Bytes
## <int> <chr> <int> <int> <int> <chr> <int> <int>
## 1 3402 "Band Membe~ 271 3 23 <NA> 294294 6.11e7
## 2 3389 "Revelation~ 271 2 23 <NA> 252376 4.11e6
## 3 3390 "One and th~ 271 2 23 <NA> 217732 3.56e6
## 4 3391 "Sound of a~ 271 2 23 <NA> 260154 4.23e6
## 5 3392 "Until We F~ 271 2 23 <NA> 230758 3.77e6
## 6 3393 "Original F~ 271 2 23 <NA> 218916 3.58e6
## 7 3394 "Broken Cit~ 271 2 23 <NA> 228366 3.73e6
## 8 3395 "Somedays" 271 2 23 <NA> 213831 3.50e6
## 9 3396 "Shape of T~ 271 2 23 <NA> 274597 4.47e6
## 10 3397 "Jewel of t~ 271 2 23 <NA> 233242 3.81e6
## # ... with more rows, and 2 more variables: UnitPrice <dbl>, PlaylistId <int>
full_join()
full_join(tracks, playlist_track)
## Joining, by = "TrackId"
## # Source: lazy query [?? x 10]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## TrackId Name AlbumId MediaTypeId GenreId Composer Milliseconds Bytes
## <int> <chr> <int> <int> <int> <chr> <int> <int>
## 1 1 For Thos~ 1 1 1 Angus Youn~ 343719 1.12e7
## 2 1 For Thos~ 1 1 1 Angus Youn~ 343719 1.12e7
## 3 1 For Thos~ 1 1 1 Angus Youn~ 343719 1.12e7
## 4 2 Balls to~ 2 2 1 <NA> 342562 5.51e6
## 5 2 Balls to~ 2 2 1 <NA> 342562 5.51e6
## 6 2 Balls to~ 2 2 1 <NA> 342562 5.51e6
## 7 3 Fast As ~ 3 2 1 F. Baltes,~ 230619 3.99e6
## 8 3 Fast As ~ 3 2 1 F. Baltes,~ 230619 3.99e6
## 9 3 Fast As ~ 3 2 1 F. Baltes,~ 230619 3.99e6
## 10 3 Fast As ~ 3 2 1 F. Baltes,~ 230619 3.99e6
## # ... with more rows, and 2 more variables: UnitPrice <dbl>, PlaylistId <int>
semi_join()
semi_join(tracks, playlist_track)
## Joining, by = "TrackId"
## # Source: lazy query [?? x 9]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## TrackId Name AlbumId MediaTypeId GenreId Composer Milliseconds Bytes
## <int> <chr> <int> <int> <int> <chr> <int> <int>
## 1 1 For Tho~ 1 1 1 Angus Young~ 343719 1.12e7
## 2 2 Balls t~ 2 2 1 <NA> 342562 5.51e6
## 3 3 Fast As~ 3 2 1 F. Baltes, ~ 230619 3.99e6
## 4 4 Restles~ 3 2 1 F. Baltes, ~ 252051 4.33e6
## 5 5 Princes~ 3 2 1 Deaffy & R.~ 375418 6.29e6
## 6 6 Put The~ 1 1 1 Angus Young~ 205662 6.71e6
## 7 7 Let's G~ 1 1 1 Angus Young~ 233926 7.64e6
## 8 8 Inject ~ 1 1 1 Angus Young~ 210834 6.85e6
## 9 9 Snowbal~ 1 1 1 Angus Young~ 203102 6.60e6
## 10 10 Evil Wa~ 1 1 1 Angus Young~ 263497 8.61e6
## # ... with more rows, and 1 more variable: UnitPrice <dbl>
anti_join()
anti_join(tracks, playlist_track)
## Joining, by = "TrackId"
## # Source: lazy query [?? x 9]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## # ... with 9 variables: TrackId <int>, Name <chr>, AlbumId <int>,
## # MediaTypeId <int>, GenreId <int>, Composer <chr>, Milliseconds <int>,
## # Bytes <int>, UnitPrice <dbl>