Latihan Praktikum Mandarel 14

A. Contoh Akses Database

1. Contoh Engine SQL

db<-DBI::dbConnect(RSQLite::SQLite(), "C:/Sqlite/chinook.db")
SELECT
  *
FROM
  customers;
Displaying records 1 - 10
CustomerId FirstName LastName Company Address City State Country PostalCode Phone Fax Email SupportRepId
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 3
2 Leonie Köhler NA Theodor-Heuss-Straße 34 Stuttgart NA Germany 70174 +49 0711 2842222 NA 5
3 François Tremblay NA 1498 rue Bélanger Montréal QC Canada H2G 1A7 +1 (514) 721-4711 NA 3
4 Bjørn Hansen NA Ullevålsveien 14 Oslo NA Norway 0171 +47 22 44 22 22 NA 4
5 František Wichterlová JetBrains s.r.o. Klanova 9/506 Prague NA Czech Republic 14700 +420 2 4172 5555 +420 2 4172 5555 4
6 Helena Holý NA Rilská 3174/6 Prague NA Czech Republic 14300 +420 2 4177 0449 NA 5
7 Astrid Gruber NA Rotenturmstraße 4, 1010 Innere Stadt Vienne NA Austria 1010 +43 01 5134505 NA 5
8 Daan Peeters NA Grétrystraat 63 Brussels NA Belgium 1000 +32 02 219 03 03 NA 4
9 Kara Nielsen NA Sønder Boulevard 51 Copenhagen NA Denmark 1720 +453 3331 9991 NA 4
10 Eduardo Martins Woodstock Discos Rua Dr. Falcão Filho, 155 São Paulo SP Brazil 01007-010 +55 (11) 3033-5446 +55 (11) 3033-4564 4

2. Menggunakan DPLYR

chinook<-DBI::dbConnect(RSQLite::SQLite(), "C:/Sqlite/chinook.db")
class(chinook)
## [1] "SQLiteConnection"
## attr(,"package")
## [1] "RSQLite"
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"
dplyr::tbl(chinook, "invoices")
## # Source:   table<invoices> [?? x 9]
## # Database: sqlite 3.37.0 [C:\Sqlite\chinook.db]
##    InvoiceId CustomerId InvoiceDate    BillingAddress   BillingCity BillingState
##        <int>      <int> <chr>          <chr>            <chr>       <chr>       
##  1         1          2 2009-01-01 00~ Theodor-Heuss-S~ Stuttgart   <NA>        
##  2         2          4 2009-01-02 00~ Ullevålsveien 14 Oslo        <NA>        
##  3         3          8 2009-01-03 00~ Grétrystraat 63  Brussels    <NA>        
##  4         4         14 2009-01-06 00~ 8210 111 ST NW   Edmonton    AB          
##  5         5         23 2009-01-11 00~ 69 Salem Street  Boston      MA          
##  6         6         37 2009-01-19 00~ Berger Straße 10 Frankfurt   <NA>        
##  7         7         38 2009-02-01 00~ Barbarossastraß~ Berlin      <NA>        
##  8         8         40 2009-02-01 00~ 8, Rue Hanovre   Paris       <NA>        
##  9         9         42 2009-02-02 00~ 9, Place Louis ~ Bordeaux    <NA>        
## 10        10         46 2009-02-03 00~ 3 Chatham Street Dublin      Dublin      
## # ... with more rows, and 3 more variables: BillingCountry <chr>,
## #   BillingPostalCode <chr>, Total <dbl>
invoices <- dplyr::tbl(chinook, "invoices")
class(invoices)
## [1] "tbl_SQLiteConnection" "tbl_dbi"              "tbl_sql"             
## [4] "tbl_lazy"             "tbl"
invoices
## # Source:   table<invoices> [?? x 9]
## # Database: sqlite 3.37.0 [C:\Sqlite\chinook.db]
##    InvoiceId CustomerId InvoiceDate    BillingAddress   BillingCity BillingState
##        <int>      <int> <chr>          <chr>            <chr>       <chr>       
##  1         1          2 2009-01-01 00~ Theodor-Heuss-S~ Stuttgart   <NA>        
##  2         2          4 2009-01-02 00~ Ullevålsveien 14 Oslo        <NA>        
##  3         3          8 2009-01-03 00~ Grétrystraat 63  Brussels    <NA>        
##  4         4         14 2009-01-06 00~ 8210 111 ST NW   Edmonton    AB          
##  5         5         23 2009-01-11 00~ 69 Salem Street  Boston      MA          
##  6         6         37 2009-01-19 00~ Berger Straße 10 Frankfurt   <NA>        
##  7         7         38 2009-02-01 00~ Barbarossastraß~ Berlin      <NA>        
##  8         8         40 2009-02-01 00~ 8, Rue Hanovre   Paris       <NA>        
##  9         9         42 2009-02-02 00~ 9, Place Louis ~ Bordeaux    <NA>        
## 10        10         46 2009-02-03 00~ 3 Chatham Street Dublin      Dublin      
## # ... with more rows, and 3 more variables: BillingCountry <chr>,
## #   BillingPostalCode <chr>, Total <dbl>
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
q<-invoices %>%
  select(InvoiceId, CustomerId, Total) %>%
  filter(Total>10)
q
## # Source:   lazy query [?? x 3]
## # Database: sqlite 3.37.0 [C:\Sqlite\chinook.db]
##    InvoiceId CustomerId Total
##        <int>      <int> <dbl>
##  1         5         23  13.9
##  2        12          2  13.9
##  3        19         40  13.9
##  4        26         19  13.9
##  5        33         57  13.9
##  6        40         36  13.9
##  7        47         15  13.9
##  8        54         53  13.9
##  9        61         32  13.9
## 10        68         11  13.9
## # ... with more rows
dplyr::show_query(q)
## <SQL>
## SELECT *
## FROM (SELECT `InvoiceId`, `CustomerId`, `Total`
## FROM `invoices`)
## WHERE (`Total` > 10.0)