1. Cara Akses Database

a. Engine SQL

db<-DBI::dbConnect (RSQLite::SQLite(), "D:/BERLIANA/Kuliah/Materi Kuliah/Semester 3/Manajemen Data Relasional/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	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

b. dplyr

chinook <-DBI::dbConnect (RSQLite::SQLite(), "D:/BERLIANA/Kuliah/Materi Kuliah/Semester 3/Manajemen Data Relasional/chinook.db")
chinook <-DBI::dbConnect (RSQLite::SQLite(), "D:/BERLIANA/Kuliah/Materi Kuliah/Semester 3/Manajemen Data Relasional/chinook.db")
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, "customers")

## # Source:   table<customers> [?? x 13]
## # Database: sqlite 3.37.0 [D:\BERLIANA\Kuliah\Materi Kuliah\Semester
## #   3\Manajemen Data Relasional\chinook.db]
##    CustomerId FirstName LastName  Company Address City  State Country PostalCode
##         <int> <chr>     <chr>     <chr>   <chr>   <chr> <chr> <chr>   <chr>     
##  1          1 Luís      Gonçalves Embrae~ Av. Br~ São ~ SP    Brazil  12227-000 
##  2          2 Leonie    Köhler    <NA>    Theodo~ Stut~ <NA>  Germany 70174     
##  3          3 François  Tremblay  <NA>    1498 r~ Mont~ QC    Canada  H2G 1A7   
##  4          4 Bjørn     Hansen    <NA>    Ullevå~ Oslo  <NA>  Norway  0171      
##  5          5 František Wichterl~ JetBra~ Klanov~ Prag~ <NA>  Czech ~ 14700     
##  6          6 Helena    Holý      <NA>    Rilská~ Prag~ <NA>  Czech ~ 14300     
##  7          7 Astrid    Gruber    <NA>    Rotent~ Vien~ <NA>  Austria 1010      
##  8          8 Daan      Peeters   <NA>    Grétry~ Brus~ <NA>  Belgium 1000      
##  9          9 Kara      Nielsen   <NA>    Sønder~ Cope~ <NA>  Denmark 1720      
## 10         10 Eduardo   Martins   Woodst~ Rua Dr~ São ~ SP    Brazil  01007-010 
## # ... with more rows, and 4 more variables: Phone <chr>, Fax <chr>,
## #   Email <chr>, SupportRepId <int>

customers <- dplyr::tbl(chinook, "customers")
class("customers")

## [1] "character"

customers

## # Source:   table<customers> [?? x 13]
## # Database: sqlite 3.37.0 [D:\BERLIANA\Kuliah\Materi Kuliah\Semester
## #   3\Manajemen Data Relasional\chinook.db]
##    CustomerId FirstName LastName  Company Address City  State Country PostalCode
##         <int> <chr>     <chr>     <chr>   <chr>   <chr> <chr> <chr>   <chr>     
##  1          1 Luís      Gonçalves Embrae~ Av. Br~ São ~ SP    Brazil  12227-000 
##  2          2 Leonie    Köhler    <NA>    Theodo~ Stut~ <NA>  Germany 70174     
##  3          3 François  Tremblay  <NA>    1498 r~ Mont~ QC    Canada  H2G 1A7   
##  4          4 Bjørn     Hansen    <NA>    Ullevå~ Oslo  <NA>  Norway  0171      
##  5          5 František Wichterl~ JetBra~ Klanov~ Prag~ <NA>  Czech ~ 14700     
##  6          6 Helena    Holý      <NA>    Rilská~ Prag~ <NA>  Czech ~ 14300     
##  7          7 Astrid    Gruber    <NA>    Rotent~ Vien~ <NA>  Austria 1010      
##  8          8 Daan      Peeters   <NA>    Grétry~ Brus~ <NA>  Belgium 1000      
##  9          9 Kara      Nielsen   <NA>    Sønder~ Cope~ <NA>  Denmark 1720      
## 10         10 Eduardo   Martins   Woodst~ Rua Dr~ São ~ SP    Brazil  01007-010 
## # ... with more rows, and 4 more variables: Phone <chr>, Fax <chr>,
## #   Email <chr>, SupportRepId <int>

library(dplyr)

## Warning: package 'dplyr' was built under R version 4.1.2

## 
## 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

PRAGUE.customers<- customers %>%
  select(CustomerId, City)%>%
  filter(City=="Prague")
PRAGUE.customers

## # Source:   lazy query [?? x 2]
## # Database: sqlite 3.37.0 [D:\BERLIANA\Kuliah\Materi Kuliah\Semester
## #   3\Manajemen Data Relasional\chinook.db]
##   CustomerId City  
##        <int> <chr> 
## 1          5 Prague
## 2          6 Prague

dplyr::show_query(PRAGUE.customers)

## <SQL>
## SELECT *
## FROM (SELECT `CustomerId`, `City`
## FROM `customers`)
## WHERE (`City` = 'Prague')

2. Data Wrangling

a. Penggunaan Fungsi Secara Terpisah

data(cars)
cars <-tibble ::as_tibble(cars)

b. Mencari Rata-rata speed cars

cars %>% summarise(mean=mean(speed), .groups="drop")

## # A tibble: 1 x 1
##    mean
##   <dbl>
## 1  15.4

c. Mengurutkan data dist cars terbesar ke terkecil

cars %>% arrange(desc(dist))

## # A tibble: 50 x 2
##    speed  dist
##    <dbl> <dbl>
##  1    24   120
##  2    24    93
##  3    24    92
##  4    25    85
##  5    18    84
##  6    14    80
##  7    18    76
##  8    24    70
##  9    19    68
## 10    22    66
## # ... with 40 more rows

d. Memfilter Speed 11

cars %>% filter(speed=="11")

## # A tibble: 2 x 2
##   speed  dist
##   <dbl> <dbl>
## 1    11    17
## 2    11    28

e. Membuat Kolom Baru

cars %>% mutate(Y=speed + dist)

## # A tibble: 50 x 3
##    speed  dist     Y
##    <dbl> <dbl> <dbl>
##  1     4     2     6
##  2     4    10    14
##  3     7     4    11
##  4     7    22    29
##  5     8    16    24
##  6     9    10    19
##  7    10    18    28
##  8    10    26    36
##  9    10    34    44
## 10    11    17    28
## # ... with 40 more rows

f. Menampilkan kolom yang dipilih

cars %>% select(speed)

## # A tibble: 50 x 1
##    speed
##    <dbl>
##  1     4
##  2     4
##  3     7
##  4     7
##  5     8
##  6     9
##  7    10
##  8    10
##  9    10
## 10    11
## # ... with 40 more rows

g. Penggunaan Fungsi secara bersamaan

cars %>% mutate(x=speed/dist) %>%
    filter(speed==12) %>%
    group_by(speed,dist) %>%
    summarise(mean=mean(speed), .groups="drop") %>%
    arrange(desc(dist))

## # A tibble: 4 x 3
##   speed  dist  mean
##   <dbl> <dbl> <dbl>
## 1    12    28    12
## 2    12    24    12
## 3    12    20    12
## 4    12    14    12

Tugas Akhir Mandarel

Berliana Apriyanti

1. Cara Akses Database

a. Engine SQL

b. dplyr

2. Data Wrangling

a. Penggunaan Fungsi Secara Terpisah

b. Mencari Rata-rata speed cars

c. Mengurutkan data dist cars terbesar ke terkecil

d. Memfilter Speed 11

e. Membuat Kolom Baru

f. Menampilkan kolom yang dipilih

g. Penggunaan Fungsi secara bersamaan