db<-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/chinook.db")Menampilkan table invoices dari database chinook.db dan memilih data dengan BillingCountry “USA”
SELECT
*
FROM
invoices
WHERE
BillingCountry = 'USA';
| InvoiceId | CustomerId | InvoiceDate | BillingAddress | BillingCity | BillingState | BillingCountry | BillingPostalCode | Total |
|---|---|---|---|---|---|---|---|---|
| 5 | 23 | 2009-01-11 00:00:00 | 69 Salem Street | Boston | MA | USA | 2113 | 13.86 |
| 13 | 16 | 2009-02-19 00:00:00 | 1600 Amphitheatre Parkway | Mountain View | CA | USA | 94043-1351 | 0.99 |
| 14 | 17 | 2009-03-04 00:00:00 | 1 Microsoft Way | Redmond | WA | USA | 98052-8300 | 1.98 |
| 15 | 19 | 2009-03-04 00:00:00 | 1 Infinite Loop | Cupertino | CA | USA | 95014 | 1.98 |
| 16 | 21 | 2009-03-05 00:00:00 | 801 W 4th Street | Reno | NV | USA | 89503 | 3.96 |
| 17 | 25 | 2009-03-06 00:00:00 | 319 N. Frances Street | Madison | WI | USA | 53703 | 5.94 |
| 26 | 19 | 2009-04-14 00:00:00 | 1 Infinite Loop | Cupertino | CA | USA | 95014 | 13.86 |
| 37 | 17 | 2009-06-06 00:00:00 | 1 Microsoft Way | Redmond | WA | USA | 98052-8300 | 3.96 |
| 38 | 21 | 2009-06-07 00:00:00 | 801 W 4th Street | Reno | NV | USA | 89503 | 5.94 |
| 39 | 27 | 2009-06-10 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 8.91 |
Menampilkan table employees dari database chinook.db
SELECT
*
FROM
employees;| EmployeeId | LastName | FirstName | Title | ReportsTo | BirthDate | HireDate | Address | City | State | Country | PostalCode | Phone | Fax | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Adams | Andrew | General Manager | NA | 1962-02-18 00:00:00 | 2002-08-14 00:00:00 | 11120 Jasper Ave NW | Edmonton | AB | Canada | T5K 2N1 | +1 (780) 428-9482 | +1 (780) 428-3457 | andrew@chinookcorp.com |
| 2 | Edwards | Nancy | Sales Manager | 1 | 1958-12-08 00:00:00 | 2002-05-01 00:00:00 | 825 8 Ave SW | Calgary | AB | Canada | T2P 2T3 | +1 (403) 262-3443 | +1 (403) 262-3322 | nancy@chinookcorp.com |
| 3 | Peacock | Jane | Sales Support Agent | 2 | 1973-08-29 00:00:00 | 2002-04-01 00:00:00 | 1111 6 Ave SW | Calgary | AB | Canada | T2P 5M5 | +1 (403) 262-3443 | +1 (403) 262-6712 | jane@chinookcorp.com |
| 4 | Park | Margaret | Sales Support Agent | 2 | 1947-09-19 00:00:00 | 2003-05-03 00:00:00 | 683 10 Street SW | Calgary | AB | Canada | T2P 5G3 | +1 (403) 263-4423 | +1 (403) 263-4289 | margaret@chinookcorp.com |
| 5 | Johnson | Steve | Sales Support Agent | 2 | 1965-03-03 00:00:00 | 2003-10-17 00:00:00 | 7727B 41 Ave | Calgary | AB | Canada | T3B 1Y7 | 1 (780) 836-9987 | 1 (780) 836-9543 | steve@chinookcorp.com |
| 6 | Mitchell | Michael | IT Manager | 1 | 1973-07-01 00:00:00 | 2003-10-17 00:00:00 | 5827 Bowness Road NW | Calgary | AB | Canada | T3B 0C5 | +1 (403) 246-9887 | +1 (403) 246-9899 | michael@chinookcorp.com |
| 7 | King | Robert | IT Staff | 6 | 1970-05-29 00:00:00 | 2004-01-02 00:00:00 | 590 Columbia Boulevard West | Lethbridge | AB | Canada | T1K 5N8 | +1 (403) 456-9986 | +1 (403) 456-8485 | robert@chinookcorp.com |
| 8 | Callahan | Laura | IT Staff | 6 | 1968-01-09 00:00:00 | 2004-03-04 00:00:00 | 923 7 ST NW | Lethbridge | AB | Canada | T1H 1Y8 | +1 (403) 467-3351 | +1 (403) 467-8772 | laura@chinookcorp.com |
company<-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/company.db")class(company)## [1] "SQLiteConnection"
## attr(,"package")
## [1] "RSQLite"RSQLite::dbListTables(company)## [1] "DEPARTMENT" "DEPENDENT" "DEPT_LOCATIONS" "EMPLOYEE"
## [5] "PROJECT" "WORKS_ON"EMPLOYEE<-dplyr::tbl(company,"EMPLOYEE")
class(EMPLOYEE)## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"Menampilkan data EMPLOYEE dari database company.db
EMPLOYEE## # Source: table<EMPLOYEE> [?? x 10]
## # Database: sqlite 3.37.0 [C:\sqlite\company.db]
## Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <int> <chr> <int>
## 1 John B Smith 123456789 1965-~ 731 Fond~ M 30000 333445555 5
## 2 Franklin T Wong 333445555 1965-~ 638 Voss~ M 40000 888665555 5
## 3 Alicia J Zelaya 999887777 1968-~ 3321 Cas~ F 25000 987654321 4
## 4 Jennifer S Wallace 987654321 1941-~ 291 Berr~ F 43000 888665555 4
## 5 Ramesh K Narayan 666884444 1962-~ 975 Fire~ M 38000 333445555 5
## 6 Joyce A English 453453453 1972-~ 5631 Ric~ F 25000 333445555 5
## 7 Ahmad V Jabbar 987987987 1969-~ 980 Dall~ M 25000 987654321 4
## 8 James E Borg 888665555 1937-~ 450 Ston~ M 55000 <NA> 1library(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, unionMenampilkan Nama depan, Ssn, dan Gaji pegawai dengan rentang gaji pegawai antara 30.000-50.000.
q <-EMPLOYEE %>%
select(Fname, Ssn, Salary) %>%
filter(Salary>30000 && Salary<50000)
q## # Source: lazy query [?? x 3]
## # Database: sqlite 3.37.0 [C:\sqlite\company.db]
## Fname Ssn Salary
## <chr> <chr> <int>
## 1 Franklin 333445555 40000
## 2 Jennifer 987654321 43000
## 3 Ramesh 666884444 38000dplyr::show_query(q)## <SQL>
## SELECT *
## FROM (SELECT `Fname`, `Ssn`, `Salary`
## FROM `EMPLOYEE`)
## WHERE (`Salary` > 30000.0 AND `Salary` < 50000.0)library(tidyverse)## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --## v ggplot2 3.3.5 v purrr 0.3.4
## v tibble 3.1.6 v stringr 1.4.0
## v tidyr 1.1.4 v forcats 0.5.1
## v readr 2.1.1## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()Memanggil data yang akan digunakan:
library(datasets)
data(OrchardSprays)
OrchardSprays <-tibble::as_tibble(OrchardSprays)Menghitung rata-rata decrease setiap treatment.
OrchardSprays %>% group_by(treatment) %>% summarise(mean_decrease=mean(decrease), .groups='drop')## # A tibble: 8 x 2
## treatment mean_decrease
## <fct> <dbl>
## 1 A 4.62
## 2 B 7.62
## 3 C 25.2
## 4 D 35
## 5 E 63.1
## 6 F 69
## 7 G 68.5
## 8 H 90.2Mengurutkan data berdasarkan peubah decrease dari nilai yang terkecil.
OrchardSprays %>% arrange(decrease)## # A tibble: 64 x 4
## decrease rowpos colpos treatment
## <dbl> <dbl> <dbl> <fct>
## 1 2 8 1 A
## 2 2 6 2 A
## 3 3 7 8 A
## 4 4 8 3 B
## 5 4 2 4 A
## 6 4 5 6 B
## 7 4 5 7 A
## 8 5 3 3 A
## 9 5 4 5 A
## 10 6 2 2 B
## # ... with 54 more rowsMemilih data A dari peubah treatment.
OrchardSprays %>% filter(treatment =="A")## # A tibble: 8 x 4
## decrease rowpos colpos treatment
## <dbl> <dbl> <dbl> <fct>
## 1 2 8 1 A
## 2 2 6 2 A
## 3 5 3 3 A
## 4 4 2 4 A
## 5 5 4 5 A
## 6 12 1 6 A
## 7 4 5 7 A
## 8 3 7 8 AMemilih subset data berdasarkan variabel treatment dan decrease.
OrchardSprays %>% select(treatment,decrease)## # A tibble: 64 x 2
## treatment decrease
## <fct> <dbl>
## 1 D 57
## 2 E 95
## 3 B 8
## 4 H 69
## 5 G 92
## 6 F 90
## 7 C 15
## 8 A 2
## 9 C 84
## 10 B 6
## # ... with 54 more rowsMenambahkan peubah baru pada data yaitu unit.
OrchardSprays %>% mutate(unit=rowpos * colpos)## # A tibble: 64 x 5
## decrease rowpos colpos treatment unit
## <dbl> <dbl> <dbl> <fct> <dbl>
## 1 57 1 1 D 1
## 2 95 2 1 E 2
## 3 8 3 1 B 3
## 4 69 4 1 H 4
## 5 92 5 1 G 5
## 6 90 6 1 F 6
## 7 15 7 1 C 7
## 8 2 8 1 A 8
## 9 84 1 2 C 2
## 10 6 2 2 B 4
## # ... with 54 more rowsMembuat dataset baru dengan nama OrchardSpraysbaru yang berasal dari:
Memilih data A dari peubah treatment, selanjutnya menambahkan peubah baru yaitu unit yang berasal dari perkalian colpos dan rowpos dari treatment A, Selanjutnya mengurutkan data berdasarkan peubah unit dari nilai yang terkecil, dan yang terakhir memilih subset data berdasarkan peubah treatment, unit, dan decrease.
OrchardSpraysbaru <- OrchardSprays %>% filter(treatment =="A") %>% mutate(unit=rowpos * colpos) %>% arrange(unit) %>% select(treatment, unit, decrease)
OrchardSpraysbaru## # A tibble: 8 x 3
## treatment unit decrease
## <fct> <dbl> <dbl>
## 1 A 6 12
## 2 A 8 2
## 3 A 8 4
## 4 A 9 5
## 5 A 12 2
## 6 A 20 5
## 7 A 35 4
## 8 A 56 3