db<-DBI::dbConnect(RSQLite::SQLite(), "C:/Users/LENOVO/Documents/Departemen Statistika/SEM 3/Manajemen Data Relasional/Praktikum 12/chinook.db") SELECT
*
FROM
customers;| CustomerId | FirstName | LastName | Company | Address | City | State | Country | PostalCode | Phone | Fax | 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 | luisg@embraer.com.br | 3 |
| 2 | Leonie | Köhler | NA | Theodor-Heuss-Straße 34 | Stuttgart | NA | Germany | 70174 | +49 0711 2842222 | NA | leonekohler@surfeu.de | 5 |
| 3 | François | Tremblay | NA | 1498 rue Bélanger | Montréal | QC | Canada | H2G 1A7 | +1 (514) 721-4711 | NA | ftremblay@gmail.com | 3 |
| 4 | Bjørn | Hansen | NA | Ullevålsveien 14 | Oslo | NA | Norway | 0171 | +47 22 44 22 22 | NA | bjorn.hansen@yahoo.no | 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 | frantisekw@jetbrains.com | 4 |
| 6 | Helena | Holý | NA | Rilská 3174/6 | Prague | NA | Czech Republic | 14300 | +420 2 4177 0449 | NA | hholy@gmail.com | 5 |
| 7 | Astrid | Gruber | NA | Rotenturmstraße 4, 1010 Innere Stadt | Vienne | NA | Austria | 1010 | +43 01 5134505 | NA | astrid.gruber@apple.at | 5 |
| 8 | Daan | Peeters | NA | Grétrystraat 63 | Brussels | NA | Belgium | 1000 | +32 02 219 03 03 | NA | daan_peeters@apple.be | 4 |
| 9 | Kara | Nielsen | NA | Sønder Boulevard 51 | Copenhagen | NA | Denmark | 1720 | +453 3331 9991 | NA | kara.nielsen@jubii.dk | 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 | eduardo@woodstock.com.br | 4 |
chinook<-DBI::dbConnect(RSQLite::SQLite(), "C:/Users/LENOVO/Documents/Departemen Statistika/SEM 3/Manajemen Data Relasional/Praktikum 12/chinook.db")
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, unionq<-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 rowsdplyr::show_query(q)## <SQL>
## SELECT *
## FROM (SELECT `InvoiceId`, `CustomerId`, `Total`
## FROM `invoices`)
## WHERE (`Total` > 10.0)data("rivers")
rivers<-tibble::as_tibble(rivers)rivers %>% summarise(mean=mean(value), .groups='drop')## # A tibble: 1 x 1
## mean
## <dbl>
## 1 591.rivers %>% arrange(desc(value))## # A tibble: 141 x 1
## value
## <dbl>
## 1 3710
## 2 2533
## 3 2348
## 4 2315
## 5 1885
## 6 1770
## 7 1459
## 8 1450
## 9 1306
## 10 1270
## # ... with 131 more rowsrivers %>% filter(value<=500)## # A tibble: 84 x 1
## value
## <dbl>
## 1 320
## 2 325
## 3 392
## 4 450
## 5 135
## 6 465
## 7 330
## 8 336
## 9 280
## 10 315
## # ... with 74 more rowsrivers %>% select(value)## # A tibble: 141 x 1
## value
## <dbl>
## 1 735
## 2 320
## 3 325
## 4 392
## 5 524
## 6 450
## 7 1459
## 8 135
## 9 465
## 10 600
## # ... with 131 more rowsrivers %>% mutate(X=sum(value))## # A tibble: 141 x 2
## value X
## <dbl> <dbl>
## 1 735 83357
## 2 320 83357
## 3 325 83357
## 4 392 83357
## 5 524 83357
## 6 450 83357
## 7 1459 83357
## 8 135 83357
## 9 465 83357
## 10 600 83357
## # ... with 131 more rowsrivers %>% arrange(desc(value)) %>% filter(value<=400) %>% summarise(mean=mean(value), .groups='drop')## # A tibble: 1 x 1
## mean
## <dbl>
## 1 300.