Connecting With DBI

DBI Overview

R Package
Specifies a generic database interface

DBI functions

dbConnect
dbListTable
dbGetQuery

Implementation

RSQLite Package
RPostgreSQL Package

DBI Overview

R File -> DBI -> SQLite -> SQLite
R File -> DBI -> SQLite -> SQLite
R File -> DBI -> RPostgreSQL -> PostgreSQL
R File -> DBI -> RMariaDB -> MariaDB

SQLite Database Connection

Create Connection, Write Data, Read Data, Close Connection

# install.packages(c('DBI', 'RSQLite'))
library(DBI)
library(RSQLite)
# create connection
con <- dbConnect(RSQLite::SQLite(), 'demo.db')
# list tables in the database
dbListTables(con)

## [1] "mtcars"

# read first 6 rows of mtcars dataset
head(mtcars)

##                    mpg cyl disp  hp drat    wt  qsec vs am gear carb
## Mazda RX4         21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
## Mazda RX4 Wag     21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
## Datsun 710        22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
## Hornet 4 Drive    21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
## Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
## Valiant           18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

# check the type of dataset
class(mtcars)

## [1] "data.frame"

# drop table mtcars if exists
dbExecute(con, "drop table if exists mtcars")

## [1] 0

df <- mtcars
df$row_names <- rownames(mtcars)
# write the dataframe to mtcars table
dbWriteTable(con, 'mtcars', df)
# list table of the database
dbListTables(con)

## [1] "mtcars"

# query the database
dbGetQuery(con, "SELECT * FROM mtcars LIMIT 5")

##    mpg cyl disp  hp drat    wt  qsec vs am gear carb         row_names
## 1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4         Mazda RX4
## 2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4     Mazda RX4 Wag
## 3 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1        Datsun 710
## 4 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1    Hornet 4 Drive
## 5 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2 Hornet Sportabout

# close the connection
dbDisconnect(con)

Try To Read Data If Connection is Closed

# try to query the database again
# dbGetQuery(con, "SELECT * FROM mtcars")

PostgreSQL Database Connection

Create Connection, Read Data, Close Connection

# install.packages(c('DBI', 'RPostgreSQL', 'RPostgres'))
library(DBI)
library(RPostgres)

con = dbConnect(RPostgres::Postgres(), 
                dbname = 'northwind',
                host = 'localhost',
                user = 'postgres',
                password = '1947')
dbListTables(con)

##  [1] "us_states"              "customers"              "orders"                
##  [4] "employees"              "shippers"               "products"              
##  [7] "order_details"          "categories"             "suppliers"             
## [10] "region"                 "territories"            "employee_territories"  
## [13] "customer_demographics"  "customer_customer_demo"

dbGetQuery(con, "select count(*) from products")

##   count
## 1    77

dbDisconnect(con)

MySQL Database Connection

Create Connection, Read Data, Close Connection

# install.packages(c('DBI', 'RMySQL'))
library(DBI)
library(RMySQL)

## 
## Attaching package: 'RMySQL'

## The following object is masked from 'package:RSQLite':
## 
##     isIdCurrent

con = dbConnect(RMySQL::MySQL(), 
                dbname = 'sakila',
                host = 'localhost',
                user = 'root',
                password = '1947')
dbListTables(con)

##  [1] "actor"                      "actor_info"                
##  [3] "address"                    "category"                  
##  [5] "city"                       "country"                   
##  [7] "customer"                   "customer_list"             
##  [9] "film"                       "film_actor"                
## [11] "film_category"              "film_list"                 
## [13] "film_text"                  "inventory"                 
## [15] "language"                   "nicer_but_slower_film_list"
## [17] "payment"                    "rental"                    
## [19] "sales_by_film_category"     "sales_by_store"            
## [21] "staff"                      "staff_list"                
## [23] "store"

dbGetQuery(con, "select count(*) from film")

##   count(*)
## 1     1000

dbDisconnect(con)

## [1] TRUE

ODBC Postgres Database Connection

Create Connection, Read Data, Close Connection

library(RODBC)
con <- odbcConnect('DemoEnv')
sqlTables(con)

##    TABLE_CAT TABLE_SCHEM             TABLE_NAME TABLE_TYPE REMARKS
## 1  northwind      public             categories      TABLE        
## 2  northwind      public customer_customer_demo      TABLE        
## 3  northwind      public  customer_demographics      TABLE        
## 4  northwind      public              customers      TABLE        
## 5  northwind      public   employee_territories      TABLE        
## 6  northwind      public              employees      TABLE        
## 7  northwind      public          order_details      TABLE        
## 8  northwind      public                 orders      TABLE        
## 9  northwind      public               products      TABLE        
## 10 northwind      public                 region      TABLE        
## 11 northwind      public               shippers      TABLE        
## 12 northwind      public              suppliers      TABLE        
## 13 northwind      public            territories      TABLE        
## 14 northwind      public              us_states      TABLE

sqlQuery(con, "select * from region")

##   region_id region_description
## 1         1            Eastern
## 2         2            Western
## 3         3           Northern
## 4         4           Southern

odbcClose(con)

Mixing DBI and ODBC

R -> DBI -> RPostgreSQL -> PostgreSQL
R -> RODBC -> DemoEnv -> PostgreSQL
R -> DBI -> ODBC -> DemoEnv -> PostgreSQL

library(odbc)
con <- dbConnect(odbc(), dsn = 'DemoEnv')
dbListTables(con)

##  [1] "categories"             "customer_customer_demo" "customer_demographics" 
##  [4] "customers"              "employee_territories"   "employees"             
##  [7] "order_details"          "orders"                 "products"              
## [10] "region"                 "shippers"               "suppliers"             
## [13] "territories"            "us_states"

dbGetQuery(con, "select count(*) from customers")

##   count
## 1    91

dbDisconnect(con)

Querying With SQL

Selecting

# install.packages(c('DBI', 'RSQLite'))
library(RSQLite)

con <- dbConnect(SQLite(), 'demo.db')

dbListTables(con)

## [1] "mtcars"

dbGetQuery(con, 'SELECT * FROM mtcars')

##     mpg cyl  disp  hp drat    wt  qsec vs am gear carb           row_names
## 1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4           Mazda RX4
## 2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4       Mazda RX4 Wag
## 3  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1          Datsun 710
## 4  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1      Hornet 4 Drive
## 5  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2   Hornet Sportabout
## 6  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1             Valiant
## 7  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4          Duster 360
## 8  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2           Merc 240D
## 9  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2            Merc 230
## 10 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4            Merc 280
## 11 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4           Merc 280C
## 12 16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3          Merc 450SE
## 13 17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3          Merc 450SL
## 14 15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3         Merc 450SLC
## 15 10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4  Cadillac Fleetwood
## 16 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4 Lincoln Continental
## 17 14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4   Chrysler Imperial
## 18 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1            Fiat 128
## 19 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2         Honda Civic
## 20 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1      Toyota Corolla
## 21 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1       Toyota Corona
## 22 15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2    Dodge Challenger
## 23 15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2         AMC Javelin
## 24 13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4          Camaro Z28
## 25 19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2    Pontiac Firebird
## 26 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1           Fiat X1-9
## 27 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2       Porsche 914-2
## 28 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2        Lotus Europa
## 29 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4      Ford Pantera L
## 30 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6        Ferrari Dino
## 31 15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8       Maserati Bora
## 32 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2          Volvo 142E

dbGetQuery(con, 'SELECT * FROM mtcars LIMIT 5')

##    mpg cyl disp  hp drat    wt  qsec vs am gear carb         row_names
## 1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4         Mazda RX4
## 2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4     Mazda RX4 Wag
## 3 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1        Datsun 710
## 4 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1    Hornet 4 Drive
## 5 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2 Hornet Sportabout

dbGetQuery(con, 'SELECT COUNT(*) FROM mtcars')

##   COUNT(*)
## 1       32

dbGetQuery(con, 'SELECT mpg, cyl FROM mtcars LIMIT 5')

##    mpg cyl
## 1 21.0   6
## 2 21.0   6
## 3 22.8   4
## 4 21.4   6
## 5 18.7   8

dbGetQuery(con, 'SELECT mpg, cyl AS cylinders FROM mtcars LIMIT 5')

##    mpg cylinders
## 1 21.0         6
## 2 21.0         6
## 3 22.8         4
## 4 21.4         6
## 5 18.7         8

dbGetQuery(con, 'SELECT cyl, mpg FROM mtcars LIMIT 5;')

##   cyl  mpg
## 1   6 21.0
## 2   6 21.0
## 3   4 22.8
## 4   6 21.4
## 5   8 18.7

dbGetQuery(con, 'SELECT cyl, mpg, 235/mpg AS "l/100km" FROM mtcars LIMIT 5;')

##   cyl  mpg  l/100km
## 1   6 21.0 11.19048
## 2   6 21.0 11.19048
## 3   4 22.8 10.30702
## 4   6 21.4 10.98131
## 5   8 18.7 12.56684

Ordering

dbGetQuery(con, 'SELECT * FROM mtcars')

##     mpg cyl  disp  hp drat    wt  qsec vs am gear carb           row_names
## 1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4           Mazda RX4
## 2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4       Mazda RX4 Wag
## 3  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1          Datsun 710
## 4  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1      Hornet 4 Drive
## 5  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2   Hornet Sportabout
## 6  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1             Valiant
## 7  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4          Duster 360
## 8  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2           Merc 240D
## 9  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2            Merc 230
## 10 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4            Merc 280
## 11 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4           Merc 280C
## 12 16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3          Merc 450SE
## 13 17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3          Merc 450SL
## 14 15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3         Merc 450SLC
## 15 10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4  Cadillac Fleetwood
## 16 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4 Lincoln Continental
## 17 14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4   Chrysler Imperial
## 18 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1            Fiat 128
## 19 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2         Honda Civic
## 20 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1      Toyota Corolla
## 21 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1       Toyota Corona
## 22 15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2    Dodge Challenger
## 23 15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2         AMC Javelin
## 24 13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4          Camaro Z28
## 25 19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2    Pontiac Firebird
## 26 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1           Fiat X1-9
## 27 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2       Porsche 914-2
## 28 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2        Lotus Europa
## 29 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4      Ford Pantera L
## 30 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6        Ferrari Dino
## 31 15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8       Maserati Bora
## 32 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2          Volvo 142E

dbGetQuery(con, 'SELECT * FROM mtcars ORDER BY mpg')

##     mpg cyl  disp  hp drat    wt  qsec vs am gear carb           row_names
## 1  10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4  Cadillac Fleetwood
## 2  10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4 Lincoln Continental
## 3  13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4          Camaro Z28
## 4  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4          Duster 360
## 5  14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4   Chrysler Imperial
## 6  15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8       Maserati Bora
## 7  15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3         Merc 450SLC
## 8  15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2         AMC Javelin
## 9  15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2    Dodge Challenger
## 10 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4      Ford Pantera L
## 11 16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3          Merc 450SE
## 12 17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3          Merc 450SL
## 13 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4           Merc 280C
## 14 18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1             Valiant
## 15 18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2   Hornet Sportabout
## 16 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4            Merc 280
## 17 19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2    Pontiac Firebird
## 18 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6        Ferrari Dino
## 19 21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4           Mazda RX4
## 20 21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4       Mazda RX4 Wag
## 21 21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1      Hornet 4 Drive
## 22 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2          Volvo 142E
## 23 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1       Toyota Corona
## 24 22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1          Datsun 710
## 25 22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2            Merc 230
## 26 24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2           Merc 240D
## 27 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2       Porsche 914-2
## 28 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1           Fiat X1-9
## 29 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2         Honda Civic
## 30 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2        Lotus Europa
## 31 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1            Fiat 128
## 32 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1      Toyota Corolla

dbGetQuery(con, 'SELECT * FROM mtcars ORDER BY mpg LIMIT 5')

##    mpg cyl disp  hp drat    wt  qsec vs am gear carb           row_names
## 1 10.4   8  472 205 2.93 5.250 17.98  0  0    3    4  Cadillac Fleetwood
## 2 10.4   8  460 215 3.00 5.424 17.82  0  0    3    4 Lincoln Continental
## 3 13.3   8  350 245 3.73 3.840 15.41  0  0    3    4          Camaro Z28
## 4 14.3   8  360 245 3.21 3.570 15.84  0  0    3    4          Duster 360
## 5 14.7   8  440 230 3.23 5.345 17.42  0  0    3    4   Chrysler Imperial

dbGetQuery(con, 'SELECT * FROM mtcars ORDER BY mpg DESC LIMIT 5')

##    mpg cyl disp  hp drat    wt  qsec vs am gear carb      row_names
## 1 33.9   4 71.1  65 4.22 1.835 19.90  1  1    4    1 Toyota Corolla
## 2 32.4   4 78.7  66 4.08 2.200 19.47  1  1    4    1       Fiat 128
## 3 30.4   4 75.7  52 4.93 1.615 18.52  1  1    4    2    Honda Civic
## 4 30.4   4 95.1 113 3.77 1.513 16.90  1  1    5    2   Lotus Europa
## 5 27.3   4 79.0  66 4.08 1.935 18.90  1  1    4    1      Fiat X1-9

dbGetQuery(con, 'SELECT * FROM mtcars ORDER BY mpg DESC, hp DESC LIMIT 5')

##    mpg cyl disp  hp drat    wt  qsec vs am gear carb      row_names
## 1 33.9   4 71.1  65 4.22 1.835 19.90  1  1    4    1 Toyota Corolla
## 2 32.4   4 78.7  66 4.08 2.200 19.47  1  1    4    1       Fiat 128
## 3 30.4   4 95.1 113 3.77 1.513 16.90  1  1    5    2   Lotus Europa
## 4 30.4   4 75.7  52 4.93 1.615 18.52  1  1    4    2    Honda Civic
## 5 27.3   4 79.0  66 4.08 1.935 18.90  1  1    4    1      Fiat X1-9

Filtering

dbGetQuery(con, 'SELECT * FROM mtcars LIMIT 5')

##    mpg cyl disp  hp drat    wt  qsec vs am gear carb         row_names
## 1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4         Mazda RX4
## 2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4     Mazda RX4 Wag
## 3 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1        Datsun 710
## 4 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1    Hornet 4 Drive
## 5 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2 Hornet Sportabout

dbGetQuery(con, 'SELECT * FROM mtcars WHERE hp>100 LIMIT 5')

##    mpg cyl disp  hp drat    wt  qsec vs am gear carb         row_names
## 1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4         Mazda RX4
## 2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4     Mazda RX4 Wag
## 3 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1    Hornet 4 Drive
## 4 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2 Hornet Sportabout
## 5 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1           Valiant

dbGetQuery(con, 'SELECT * FROM mtcars WHERE hp>100 OR am=1 LIMIT 5')

##    mpg cyl disp  hp drat    wt  qsec vs am gear carb         row_names
## 1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4         Mazda RX4
## 2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4     Mazda RX4 Wag
## 3 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1        Datsun 710
## 4 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1    Hornet 4 Drive
## 5 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2 Hornet Sportabout

dbGetQuery(con, 'SELECT row_names AS car, mpg, hp 
                 FROM mtcars 
                 WHERE hp>100 AND hp<150 AND mpg>21
                 ORDER BY mpg DESC, hp DESC')

##              car  mpg  hp
## 1   Lotus Europa 30.4 113
## 2 Hornet 4 Drive 21.4 110
## 3     Volvo 142E 21.4 109

Grouping

dbGetQuery(con, 'SELECT * FROM mtcars LIMIT 5')

##    mpg cyl disp  hp drat    wt  qsec vs am gear carb         row_names
## 1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4         Mazda RX4
## 2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4     Mazda RX4 Wag
## 3 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1        Datsun 710
## 4 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1    Hornet 4 Drive
## 5 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2 Hornet Sportabout

dbGetQuery(con, 'SELECT am, COUNT(*) FROM mtcars GROUP BY am')

##   am COUNT(*)
## 1  0       19
## 2  1       13

dbGetQuery(con, 'SELECT am, cyl, COUNT(*) FROM mtcars GROUP BY am, cyl')

##   am cyl COUNT(*)
## 1  0   4        3
## 2  0   6        4
## 3  0   8       12
## 4  1   4        8
## 5  1   6        3
## 6  1   8        2

dbGetQuery(con, 'SELECT AVG(hp), cyl FROM mtcars GROUP BY cyl')

##     AVG(hp) cyl
## 1  82.63636   4
## 2 122.28571   6
## 3 209.21429   8

dbGetQuery(con, 'SELECT AVG(hp), COUNT(*), cyl FROM mtcars GROUP BY cyl')

##     AVG(hp) COUNT(*) cyl
## 1  82.63636       11   4
## 2 122.28571        7   6
## 3 209.21429       14   8

dbGetQuery(con, 'SELECT AVG(hp), MAX(mpg), cyl 
                 FROM mtcars 
                 GROUP BY cyl
                 ORDER BY MAX(mpg) DESC')

##     AVG(hp) MAX(mpg) cyl
## 1  82.63636     33.9   4
## 2 122.28571     21.4   6
## 3 209.21429     19.2   8

dbGetQuery(con, 'SELECT COUNT(*) AS how_many, cyl 
                 FROM mtcars 
                 GROUP BY cyl 
                 HAVING how_many>10')

##   how_many cyl
## 1       11   4
## 2       14   8

dbGetQuery(con, 'SELECT COUNT(*) AS how_many, cyl 
                 FROM mtcars 
                 WHERE hp>100
                 GROUP BY cyl 
                 HAVING how_many>5')

##   how_many cyl
## 1        7   6
## 2       14   8

dbGetQuery(con, 'SELECT COUNT(*) AS how_many, cyl 
                 FROM mtcars 
                 WHERE hp>100
                 GROUP BY cyl 
                 HAVING how_many>5
                 ORDER BY how_many DESC')

##   how_many cyl
## 1       14   8
## 2        7   6

Joining

con <- dbConnect(SQLite(), 'joining.db')

dbGetQuery(con, 'SELECT * FROM Invoices')

##   InvoiceId CustomerName         InvoiceDate BillingCity BillingCountry Total
## 1         1      John A. 2019-10-01 10:30:00        Oslo         Norway   300
## 2         2      Mary B. 2018-10-21 00:00:00      Boston            USA   600
## 3         3      Jane C. 2019-07-12 00:00:00    New York            USA  1500

dbGetQuery(con, 'SELECT * FROM InvoiceLines')

## [1] InvoiceLineId InvoiceId     Description   Price        
## <0 rows> (or 0-length row.names)

dbGetQuery(con, 'SELECT * 
                 FROM Invoices I
                 JOIN InvoiceLines IL ON I.InvoiceId=IL.InvoiceId')

##  [1] InvoiceId      CustomerName   InvoiceDate    BillingCity    BillingCountry
##  [6] Total          InvoiceLineId  InvoiceId      Description    Price         
## <0 rows> (or 0-length row.names)

dbGetQuery(con, 'SELECT I.InvoiceId, I.CustomerName, 
                        IL.Description, IL.Price 
                 FROM Invoices I
                 JOIN InvoiceLines IL ON I.InvoiceId=IL.InvoiceId
                 WHERE IL.Price>200 
                 ORDER BY I.BillingCity')

## [1] InvoiceId    CustomerName Description  Price       
## <0 rows> (or 0-length row.names)

dbGetQuery(con, 'SELECT I.InvoiceId, IL.InvoiceId, 
                        I.CustomerName, IL.Description, IL.Price
                 FROM Invoices I
                 LEFT JOIN InvoiceLines IL ON I.InvoiceId=IL.InvoiceId
                 ')

##   InvoiceId InvoiceId CustomerName Description Price
## 1         1        NA      John A.        <NA>    NA
## 2         2        NA      Mary B.        <NA>    NA
## 3         3        NA      Jane C.        <NA>    NA

dbGetQuery(con, 'SELECT I.CustomerName, COUNT(*)
                 FROM Invoices I
                 LEFT JOIN InvoiceLines IL ON I.InvoiceId=IL.InvoiceId
                 GROUP BY CustomerName')

##   CustomerName COUNT(*)
## 1      Jane C.        1
## 2      John A.        1
## 3      Mary B.        1

dbDisconnect(con)

Updating

con <- dbConnect(SQLite(), 'joining.db')

dbGetQuery(con, 'SELECT * FROM Invoices')

##   InvoiceId CustomerName         InvoiceDate BillingCity BillingCountry Total
## 1         1      John A. 2019-10-01 10:30:00        Oslo         Norway   300
## 2         2      Mary B. 2018-10-21 00:00:00      Boston            USA   600
## 3         3      Jane C. 2019-07-12 00:00:00    New York            USA  1500

dbExecute(con, 'UPDATE Invoices SET Total=300 WHERE InvoiceId = 1')

## [1] 1

dbGetQuery(con, 'SELECT * FROM Invoices')

##   InvoiceId CustomerName         InvoiceDate BillingCity BillingCountry Total
## 1         1      John A. 2019-10-01 10:30:00        Oslo         Norway   300
## 2         2      Mary B. 2018-10-21 00:00:00      Boston            USA   600
## 3         3      Jane C. 2019-07-12 00:00:00    New York            USA  1500

dbExecute(con, 'UPDATE Invoices SET Total=0 WHERE InvoiceId = 1')

## [1] 1

dbGetQuery(con, 'SELECT * FROM Invoices')

##   InvoiceId CustomerName         InvoiceDate BillingCity BillingCountry Total
## 1         1      John A. 2019-10-01 10:30:00        Oslo         Norway     0
## 2         2      Mary B. 2018-10-21 00:00:00      Boston            USA   600
## 3         3      Jane C. 2019-07-12 00:00:00    New York            USA  1500

dbExecute(con, 'UPDATE Invoices
                SET Total=300, InvoiceDate="2019-10-01 10:30:00"
                WHERE InvoiceId = 1')

## [1] 1

dbGetQuery(con, 'SELECT * FROM Invoices')

##   InvoiceId CustomerName         InvoiceDate BillingCity BillingCountry Total
## 1         1      John A. 2019-10-01 10:30:00        Oslo         Norway   300
## 2         2      Mary B. 2018-10-21 00:00:00      Boston            USA   600
## 3         3      Jane C. 2019-07-12 00:00:00    New York            USA  1500

dbDisconnect(con)

Inserting

con <- dbConnect(SQLite(), 'joining.db')

dbGetQuery(con, 'SELECT * FROM InvoiceLines')

## [1] InvoiceLineId InvoiceId     Description   Price        
## <0 rows> (or 0-length row.names)

dbExecute(con, 'INSERT INTO InvoiceLines
                VALUES(104, 1, \'New service\', 200)')

## [1] 1

dbGetQuery(con, 'SELECT * FROM InvoiceLines')

##   InvoiceLineId InvoiceId Description Price
## 1           104         1 New service   200

dbExecute(con, 'INSERT INTO InvoiceLines
                VALUES(105, 1, "New toy", 50),
                      (106, 1, "New book", 50)')

## [1] 2

dbGetQuery(con, 'SELECT * FROM InvoiceLines')

##   InvoiceLineId InvoiceId Description Price
## 1           104         1 New service   200
## 2           105         1     New toy    50
## 3           106         1    New book    50

dbDisconnect(con)

Deleting

con <- dbConnect(SQLite(), 'joining.db')

dbGetQuery(con, 'SELECT * FROM InvoiceLines')

##   InvoiceLineId InvoiceId Description Price
## 1           104         1 New service   200
## 2           105         1     New toy    50
## 3           106         1    New book    50

dbExecute(con, 'DELETE FROM InvoiceLines WHERE Price<=100')

## [1] 2

dbGetQuery(con, 'SELECT * FROM InvoiceLines')

##   InvoiceLineId InvoiceId Description Price
## 1           104         1 New service   200

dbExecute(con, 'DELETE FROM InvoiceLines')

## [1] 1

dbGetQuery(con, 'SELECT * FROM InvoiceLines')

## [1] InvoiceLineId InvoiceId     Description   Price        
## <0 rows> (or 0-length row.names)

dbDisconnect(con)

Querying With DPLYR

Selecting

library(tidyverse)

## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.2     ✔ readr     2.1.4
## ✔ forcats   1.0.0     ✔ stringr   1.5.0
## ✔ ggplot2   3.4.3     ✔ tibble    3.2.1
## ✔ lubridate 1.9.2     ✔ tidyr     1.3.0
## ✔ purrr     1.0.2     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

con <- dbConnect(SQLite(), 'demo.db')
dbListTables(con)

## [1] "mtcars"

(input <- tbl(con, 'mtcars'))

## # Source:   table<mtcars> [?? x 12]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names  
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>      
##  1  21       6  160    110  3.9   2.62  16.5     0     1     4     4 Mazda RX4  
##  2  21       6  160    110  3.9   2.88  17.0     0     1     4     4 Mazda RX4 …
##  3  22.8     4  108     93  3.85  2.32  18.6     1     1     4     1 Datsun 710 
##  4  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1 Hornet 4 D…
##  5  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2 Hornet Spo…
##  6  18.1     6  225    105  2.76  3.46  20.2     1     0     3     1 Valiant    
##  7  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4 Duster 360 
##  8  24.4     4  147.    62  3.69  3.19  20       1     0     4     2 Merc 240D  
##  9  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2 Merc 230   
## 10  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4 Merc 280   
## # ℹ more rows

select(input, mpg, hp)

## # Source:   SQL [?? x 2]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg    hp
##    <dbl> <dbl>
##  1  21     110
##  2  21     110
##  3  22.8    93
##  4  21.4   110
##  5  18.7   175
##  6  18.1   105
##  7  14.3   245
##  8  24.4    62
##  9  22.8    95
## 10  19.2   123
## # ℹ more rows

select(input, 1)

## # Source:   SQL [?? x 1]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg
##    <dbl>
##  1  21  
##  2  21  
##  3  22.8
##  4  21.4
##  5  18.7
##  6  18.1
##  7  14.3
##  8  24.4
##  9  22.8
## 10  19.2
## # ℹ more rows

select(input, 4, 1)

## # Source:   SQL [?? x 2]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##       hp   mpg
##    <dbl> <dbl>
##  1   110  21  
##  2   110  21  
##  3    93  22.8
##  4   110  21.4
##  5   175  18.7
##  6   105  18.1
##  7   245  14.3
##  8    62  24.4
##  9    95  22.8
## 10   123  19.2
## # ℹ more rows

select(input, car=row_names, mpg, 4)

## # Source:   SQL [?? x 3]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##    car                 mpg    hp
##    <chr>             <dbl> <dbl>
##  1 Mazda RX4          21     110
##  2 Mazda RX4 Wag      21     110
##  3 Datsun 710         22.8    93
##  4 Hornet 4 Drive     21.4   110
##  5 Hornet Sportabout  18.7   175
##  6 Valiant            18.1   105
##  7 Duster 360         14.3   245
##  8 Merc 240D          24.4    62
##  9 Merc 230           22.8    95
## 10 Merc 280           19.2   123
## # ℹ more rows

select(input, contains('c'))

## # Source:   SQL [?? x 3]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      cyl  qsec  carb
##    <dbl> <dbl> <dbl>
##  1     6  16.5     4
##  2     6  17.0     4
##  3     4  18.6     1
##  4     6  19.4     1
##  5     8  17.0     2
##  6     6  20.2     1
##  7     8  15.8     4
##  8     4  20       2
##  9     4  22.9     2
## 10     6  18.3     4
## # ℹ more rows

(start_c_columns <- select(input, starts_with('c')))

## # Source:   SQL [?? x 2]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      cyl  carb
##    <dbl> <dbl>
##  1     6     4
##  2     6     4
##  3     4     1
##  4     6     1
##  5     8     2
##  6     6     1
##  7     8     4
##  8     4     2
##  9     4     2
## 10     6     4
## # ℹ more rows

# many columns
select(input, -mpg)

## # Source:   SQL [?? x 11]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names        
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>            
##  1     6  160    110  3.9   2.62  16.5     0     1     4     4 Mazda RX4        
##  2     6  160    110  3.9   2.88  17.0     0     1     4     4 Mazda RX4 Wag    
##  3     4  108     93  3.85  2.32  18.6     1     1     4     1 Datsun 710       
##  4     6  258    110  3.08  3.22  19.4     1     0     3     1 Hornet 4 Drive   
##  5     8  360    175  3.15  3.44  17.0     0     0     3     2 Hornet Sportabout
##  6     6  225    105  2.76  3.46  20.2     1     0     3     1 Valiant          
##  7     8  360    245  3.21  3.57  15.8     0     0     3     4 Duster 360       
##  8     4  147.    62  3.69  3.19  20       1     0     4     2 Merc 240D        
##  9     4  141.    95  3.92  3.15  22.9     1     0     4     2 Merc 230         
## 10     6  168.   123  3.92  3.44  18.3     1     0     4     4 Merc 280         
## # ℹ more rows

select(input, -1)

## # Source:   SQL [?? x 11]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names        
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>            
##  1     6  160    110  3.9   2.62  16.5     0     1     4     4 Mazda RX4        
##  2     6  160    110  3.9   2.88  17.0     0     1     4     4 Mazda RX4 Wag    
##  3     4  108     93  3.85  2.32  18.6     1     1     4     1 Datsun 710       
##  4     6  258    110  3.08  3.22  19.4     1     0     3     1 Hornet 4 Drive   
##  5     8  360    175  3.15  3.44  17.0     0     0     3     2 Hornet Sportabout
##  6     6  225    105  2.76  3.46  20.2     1     0     3     1 Valiant          
##  7     8  360    245  3.21  3.57  15.8     0     0     3     4 Duster 360       
##  8     4  147.    62  3.69  3.19  20       1     0     4     2 Merc 240D        
##  9     4  141.    95  3.92  3.15  22.9     1     0     4     2 Merc 230         
## 10     6  168.   123  3.92  3.44  18.3     1     0     4     4 Merc 280         
## # ℹ more rows

(no_c_column <- select(input, -contains('c')))

## # Source:   SQL [?? x 9]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg  disp    hp  drat    wt    vs    am  gear row_names        
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>            
##  1  21    160    110  3.9   2.62     0     1     4 Mazda RX4        
##  2  21    160    110  3.9   2.88     0     1     4 Mazda RX4 Wag    
##  3  22.8  108     93  3.85  2.32     1     1     4 Datsun 710       
##  4  21.4  258    110  3.08  3.22     1     0     3 Hornet 4 Drive   
##  5  18.7  360    175  3.15  3.44     0     0     3 Hornet Sportabout
##  6  18.1  225    105  2.76  3.46     1     0     3 Valiant          
##  7  14.3  360    245  3.21  3.57     0     0     3 Duster 360       
##  8  24.4  147.    62  3.69  3.19     1     0     4 Merc 240D        
##  9  22.8  141.    95  3.92  3.15     1     0     4 Merc 230         
## 10  19.2  168.   123  3.92  3.44     1     0     4 Merc 280         
## # ℹ more rows

# SQL queries
show_query(no_c_column)

## <SQL>
## SELECT `mpg`, `disp`, `hp`, `drat`, `wt`, `vs`, `am`, `gear`, `row_names`
## FROM `mtcars`

show_query(start_c_columns)

## <SQL>
## SELECT `cyl`, `carb`
## FROM `mtcars`

dbDisconnect(con)

Mutating

con <- dbConnect(SQLite(), 'demo.db')
(input <- tbl(con, 'mtcars'))

## # Source:   table<mtcars> [?? x 12]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names  
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>      
##  1  21       6  160    110  3.9   2.62  16.5     0     1     4     4 Mazda RX4  
##  2  21       6  160    110  3.9   2.88  17.0     0     1     4     4 Mazda RX4 …
##  3  22.8     4  108     93  3.85  2.32  18.6     1     1     4     1 Datsun 710 
##  4  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1 Hornet 4 D…
##  5  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2 Hornet Spo…
##  6  18.1     6  225    105  2.76  3.46  20.2     1     0     3     1 Valiant    
##  7  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4 Duster 360 
##  8  24.4     4  147.    62  3.69  3.19  20       1     0     4     2 Merc 240D  
##  9  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2 Merc 230   
## 10  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4 Merc 280   
## # ℹ more rows

(cars_details <- mutate(input, liters_100km = 235/mpg))

## # Source:   SQL [?? x 13]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names  
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>      
##  1  21       6  160    110  3.9   2.62  16.5     0     1     4     4 Mazda RX4  
##  2  21       6  160    110  3.9   2.88  17.0     0     1     4     4 Mazda RX4 …
##  3  22.8     4  108     93  3.85  2.32  18.6     1     1     4     1 Datsun 710 
##  4  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1 Hornet 4 D…
##  5  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2 Hornet Spo…
##  6  18.1     6  225    105  2.76  3.46  20.2     1     0     3     1 Valiant    
##  7  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4 Duster 360 
##  8  24.4     4  147.    62  3.69  3.19  20       1     0     4     2 Merc 240D  
##  9  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2 Merc 230   
## 10  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4 Merc 280   
## # ℹ more rows
## # ℹ 1 more variable: liters_100km <dbl>

show_query(cars_details)

## <SQL>
## SELECT *, 235.0 / `mpg` AS `liters_100km`
## FROM `mtcars`

# keep only new columns
transmute(input, liters_100km = 235/mpg)

## # Source:   SQL [?? x 1]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##    liters_100km
##           <dbl>
##  1        11.2 
##  2        11.2 
##  3        10.3 
##  4        11.0 
##  5        12.6 
##  6        13.0 
##  7        16.4 
##  8         9.63
##  9        10.3 
## 10        12.2 
## # ℹ more rows

transmute(input, liters_100km = 235/mpg, 
                 liters_km = liters_100km/100)

## # Source:   SQL [?? x 2]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##    liters_100km liters_km
##           <dbl>     <dbl>
##  1        11.2     0.112 
##  2        11.2     0.112 
##  3        10.3     0.103 
##  4        11.0     0.110 
##  5        12.6     0.126 
##  6        13.0     0.130 
##  7        16.4     0.164 
##  8         9.63    0.0963
##  9        10.3     0.103 
## 10        12.2     0.122 
## # ℹ more rows

head(transmute(input, liters_100km = 235/mpg, 
                      liters_km = liters_100km/100), 2)

## # Source:   SQL [2 x 2]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##   liters_100km liters_km
##          <dbl>     <dbl>
## 1         11.2     0.112
## 2         11.2     0.112

show_query(head(transmute(input, liters_100km = 235/mpg, 
                                 liters_km = liters_100km/100), 2))

## <SQL>
## SELECT `liters_100km`, `liters_100km` / 100.0 AS `liters_km`
## FROM (
##   SELECT *, 235.0 / `mpg` AS `liters_100km`
##   FROM `mtcars`
## )
## LIMIT 2

dbDisconnect(con)

Piping

con <- dbConnect(SQLite(), 'demo.db')
input <- tbl(con, 'mtcars')

# before
select(input, mpg)

## # Source:   SQL [?? x 1]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg
##    <dbl>
##  1  21  
##  2  21  
##  3  22.8
##  4  21.4
##  5  18.7
##  6  18.1
##  7  14.3
##  8  24.4
##  9  22.8
## 10  19.2
## # ℹ more rows

# after
input %>% select(mpg)

## # Source:   SQL [?? x 1]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg
##    <dbl>
##  1  21  
##  2  21  
##  3  22.8
##  4  21.4
##  5  18.7
##  6  18.1
##  7  14.3
##  8  24.4
##  9  22.8
## 10  19.2
## # ℹ more rows

# before
select(mutate(input, liters_100km = 235/mpg), mpg, liters_100km)

## # Source:   SQL [?? x 2]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg liters_100km
##    <dbl>        <dbl>
##  1  21          11.2 
##  2  21          11.2 
##  3  22.8        10.3 
##  4  21.4        11.0 
##  5  18.7        12.6 
##  6  18.1        13.0 
##  7  14.3        16.4 
##  8  24.4         9.63
##  9  22.8        10.3 
## 10  19.2        12.2 
## # ℹ more rows

# after
input %>% 
  mutate(liters_100km = 235/mpg) %>% 
  select(mpg, liters_100km)

## # Source:   SQL [?? x 2]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg liters_100km
##    <dbl>        <dbl>
##  1  21          11.2 
##  2  21          11.2 
##  3  22.8        10.3 
##  4  21.4        11.0 
##  5  18.7        12.6 
##  6  18.1        13.0 
##  7  14.3        16.4 
##  8  24.4         9.63
##  9  22.8        10.3 
## 10  19.2        12.2 
## # ℹ more rows

dbDisconnect(con)

Arranging

con <- dbConnect(SQLite(), 'demo.db')
input <- tbl(con, 'mtcars')

input %>% arrange(mpg)

## # Source:     SQL [?? x 12]
## # Database:   sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
## # Ordered by: mpg
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names  
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>      
##  1  10.4     8  472    205  2.93  5.25  18.0     0     0     3     4 Cadillac F…
##  2  10.4     8  460    215  3     5.42  17.8     0     0     3     4 Lincoln Co…
##  3  13.3     8  350    245  3.73  3.84  15.4     0     0     3     4 Camaro Z28 
##  4  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4 Duster 360 
##  5  14.7     8  440    230  3.23  5.34  17.4     0     0     3     4 Chrysler I…
##  6  15       8  301    335  3.54  3.57  14.6     0     1     5     8 Maserati B…
##  7  15.2     8  276.   180  3.07  3.78  18       0     0     3     3 Merc 450SLC
##  8  15.2     8  304    150  3.15  3.44  17.3     0     0     3     2 AMC Javelin
##  9  15.5     8  318    150  2.76  3.52  16.9     0     0     3     2 Dodge Chal…
## 10  15.8     8  351    264  4.22  3.17  14.5     0     1     5     4 Ford Pante…
## # ℹ more rows

arrange(input, mpg)

## # Source:     SQL [?? x 12]
## # Database:   sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
## # Ordered by: mpg
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names  
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>      
##  1  10.4     8  472    205  2.93  5.25  18.0     0     0     3     4 Cadillac F…
##  2  10.4     8  460    215  3     5.42  17.8     0     0     3     4 Lincoln Co…
##  3  13.3     8  350    245  3.73  3.84  15.4     0     0     3     4 Camaro Z28 
##  4  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4 Duster 360 
##  5  14.7     8  440    230  3.23  5.34  17.4     0     0     3     4 Chrysler I…
##  6  15       8  301    335  3.54  3.57  14.6     0     1     5     8 Maserati B…
##  7  15.2     8  276.   180  3.07  3.78  18       0     0     3     3 Merc 450SLC
##  8  15.2     8  304    150  3.15  3.44  17.3     0     0     3     2 AMC Javelin
##  9  15.5     8  318    150  2.76  3.52  16.9     0     0     3     2 Dodge Chal…
## 10  15.8     8  351    264  4.22  3.17  14.5     0     1     5     4 Ford Pante…
## # ℹ more rows

#input %>% arrange(desc(mpg)) %>% select(car=row_names, mpg) 

#input %>% 
#  arrange(desc(mpg), desc(hp)) %>% 
#  select(car=row_names, mpg, hp) 

# does order of functions matter?
input %>% 
  select(car=row_names, mpg) %>% 
  arrange(mpg) %>% 
  head(3)

## # Source:     SQL [3 x 2]
## # Database:   sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
## # Ordered by: mpg
##   car                   mpg
##   <chr>               <dbl>
## 1 Cadillac Fleetwood   10.4
## 2 Lincoln Continental  10.4
## 3 Camaro Z28           13.3

Filtering

con <- dbConnect(SQLite(), 'demo.db')
input <- tbl(con, 'mtcars')

filter(input, am==0)

## # Source:   SQL [?? x 12]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names  
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>      
##  1  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1 Hornet 4 D…
##  2  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2 Hornet Spo…
##  3  18.1     6  225    105  2.76  3.46  20.2     1     0     3     1 Valiant    
##  4  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4 Duster 360 
##  5  24.4     4  147.    62  3.69  3.19  20       1     0     4     2 Merc 240D  
##  6  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2 Merc 230   
##  7  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4 Merc 280   
##  8  17.8     6  168.   123  3.92  3.44  18.9     1     0     4     4 Merc 280C  
##  9  16.4     8  276.   180  3.07  4.07  17.4     0     0     3     3 Merc 450SE 
## 10  17.3     8  276.   180  3.07  3.73  17.6     0     0     3     3 Merc 450SL 
## # ℹ more rows

input %>% filter(am==0)

## # Source:   SQL [?? x 12]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names  
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>      
##  1  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1 Hornet 4 D…
##  2  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2 Hornet Spo…
##  3  18.1     6  225    105  2.76  3.46  20.2     1     0     3     1 Valiant    
##  4  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4 Duster 360 
##  5  24.4     4  147.    62  3.69  3.19  20       1     0     4     2 Merc 240D  
##  6  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2 Merc 230   
##  7  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4 Merc 280   
##  8  17.8     6  168.   123  3.92  3.44  18.9     1     0     4     4 Merc 280C  
##  9  16.4     8  276.   180  3.07  4.07  17.4     0     0     3     3 Merc 450SE 
## 10  17.3     8  276.   180  3.07  3.73  17.6     0     0     3     3 Merc 450SL 
## # ℹ more rows

auto <- 0
input %>% filter(am==auto)

## # Source:   SQL [?? x 12]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names  
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>      
##  1  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1 Hornet 4 D…
##  2  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2 Hornet Spo…
##  3  18.1     6  225    105  2.76  3.46  20.2     1     0     3     1 Valiant    
##  4  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4 Duster 360 
##  5  24.4     4  147.    62  3.69  3.19  20       1     0     4     2 Merc 240D  
##  6  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2 Merc 230   
##  7  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4 Merc 280   
##  8  17.8     6  168.   123  3.92  3.44  18.9     1     0     4     4 Merc 280C  
##  9  16.4     8  276.   180  3.07  4.07  17.4     0     0     3     3 Merc 450SE 
## 10  17.3     8  276.   180  3.07  3.73  17.6     0     0     3     3 Merc 450SL 
## # ℹ more rows

#input %>% filter(am==auto) %>% show_query()

input %>% filter(am==auto & mpg>20)

## # Source:   SQL [4 x 12]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names   
##   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>       
## 1  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1 Hornet 4 Dr…
## 2  24.4     4  147.    62  3.69  3.19  20       1     0     4     2 Merc 240D   
## 3  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2 Merc 230    
## 4  21.5     4  120.    97  3.7   2.46  20.0     1     0     3     1 Toyota Coro…

input %>% filter(am==auto | mpg>20)

## # Source:   SQL [?? x 12]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names  
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>      
##  1  21       6  160    110  3.9   2.62  16.5     0     1     4     4 Mazda RX4  
##  2  21       6  160    110  3.9   2.88  17.0     0     1     4     4 Mazda RX4 …
##  3  22.8     4  108     93  3.85  2.32  18.6     1     1     4     1 Datsun 710 
##  4  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1 Hornet 4 D…
##  5  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2 Hornet Spo…
##  6  18.1     6  225    105  2.76  3.46  20.2     1     0     3     1 Valiant    
##  7  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4 Duster 360 
##  8  24.4     4  147.    62  3.69  3.19  20       1     0     4     2 Merc 240D  
##  9  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2 Merc 230   
## 10  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4 Merc 280   
## # ℹ more rows

input %>% filter(cyl==4 | cyl==6)

## # Source:   SQL [?? x 12]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names  
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>      
##  1  21       6 160     110  3.9   2.62  16.5     0     1     4     4 Mazda RX4  
##  2  21       6 160     110  3.9   2.88  17.0     0     1     4     4 Mazda RX4 …
##  3  22.8     4 108      93  3.85  2.32  18.6     1     1     4     1 Datsun 710 
##  4  21.4     6 258     110  3.08  3.22  19.4     1     0     3     1 Hornet 4 D…
##  5  18.1     6 225     105  2.76  3.46  20.2     1     0     3     1 Valiant    
##  6  24.4     4 147.     62  3.69  3.19  20       1     0     4     2 Merc 240D  
##  7  22.8     4 141.     95  3.92  3.15  22.9     1     0     4     2 Merc 230   
##  8  19.2     6 168.    123  3.92  3.44  18.3     1     0     4     4 Merc 280   
##  9  17.8     6 168.    123  3.92  3.44  18.9     1     0     4     4 Merc 280C  
## 10  32.4     4  78.7    66  4.08  2.2   19.5     1     1     4     1 Fiat 128   
## # ℹ more rows

input %>% filter(cyl %in% c(4, 6))

## # Source:   SQL [?? x 12]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names  
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>      
##  1  21       6 160     110  3.9   2.62  16.5     0     1     4     4 Mazda RX4  
##  2  21       6 160     110  3.9   2.88  17.0     0     1     4     4 Mazda RX4 …
##  3  22.8     4 108      93  3.85  2.32  18.6     1     1     4     1 Datsun 710 
##  4  21.4     6 258     110  3.08  3.22  19.4     1     0     3     1 Hornet 4 D…
##  5  18.1     6 225     105  2.76  3.46  20.2     1     0     3     1 Valiant    
##  6  24.4     4 147.     62  3.69  3.19  20       1     0     4     2 Merc 240D  
##  7  22.8     4 141.     95  3.92  3.15  22.9     1     0     4     2 Merc 230   
##  8  19.2     6 168.    123  3.92  3.44  18.3     1     0     4     4 Merc 280   
##  9  17.8     6 168.    123  3.92  3.44  18.9     1     0     4     4 Merc 280C  
## 10  32.4     4  78.7    66  4.08  2.2   19.5     1     1     4     1 Fiat 128   
## # ℹ more rows

# lazy vs. eager results
(lazy_result <- input %>% filter(mpg>20))

## # Source:   SQL [?? x 12]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names  
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>      
##  1  21       6 160     110  3.9   2.62  16.5     0     1     4     4 Mazda RX4  
##  2  21       6 160     110  3.9   2.88  17.0     0     1     4     4 Mazda RX4 …
##  3  22.8     4 108      93  3.85  2.32  18.6     1     1     4     1 Datsun 710 
##  4  21.4     6 258     110  3.08  3.22  19.4     1     0     3     1 Hornet 4 D…
##  5  24.4     4 147.     62  3.69  3.19  20       1     0     4     2 Merc 240D  
##  6  22.8     4 141.     95  3.92  3.15  22.9     1     0     4     2 Merc 230   
##  7  32.4     4  78.7    66  4.08  2.2   19.5     1     1     4     1 Fiat 128   
##  8  30.4     4  75.7    52  4.93  1.62  18.5     1     1     4     2 Honda Civic
##  9  33.9     4  71.1    65  4.22  1.84  19.9     1     1     4     1 Toyota Cor…
## 10  21.5     4 120.     97  3.7   2.46  20.0     1     0     3     1 Toyota Cor…
## # ℹ more rows

lazy_result %>% nrow()

## [1] NA

(eager_result <- input %>% filter(mpg>20) %>% collect())

## # A tibble: 14 × 12
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names  
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>      
##  1  21       6 160     110  3.9   2.62  16.5     0     1     4     4 Mazda RX4  
##  2  21       6 160     110  3.9   2.88  17.0     0     1     4     4 Mazda RX4 …
##  3  22.8     4 108      93  3.85  2.32  18.6     1     1     4     1 Datsun 710 
##  4  21.4     6 258     110  3.08  3.22  19.4     1     0     3     1 Hornet 4 D…
##  5  24.4     4 147.     62  3.69  3.19  20       1     0     4     2 Merc 240D  
##  6  22.8     4 141.     95  3.92  3.15  22.9     1     0     4     2 Merc 230   
##  7  32.4     4  78.7    66  4.08  2.2   19.5     1     1     4     1 Fiat 128   
##  8  30.4     4  75.7    52  4.93  1.62  18.5     1     1     4     2 Honda Civic
##  9  33.9     4  71.1    65  4.22  1.84  19.9     1     1     4     1 Toyota Cor…
## 10  21.5     4 120.     97  3.7   2.46  20.0     1     0     3     1 Toyota Cor…
## 11  27.3     4  79      66  4.08  1.94  18.9     1     1     4     1 Fiat X1-9  
## 12  26       4 120.     91  4.43  2.14  16.7     0     1     5     2 Porsche 91…
## 13  30.4     4  95.1   113  3.77  1.51  16.9     1     1     5     2 Lotus Euro…
## 14  21.4     4 121     109  4.11  2.78  18.6     1     1     4     2 Volvo 142E

eager_result %>% nrow()

## [1] 14

# empty values
#input %>% filter(is.na(cyl)) %>% show_query()
input %>% filter(is.na(cyl)) %>% collect() %>% nrow()

## [1] 0

dbDisconnect(con)

Grouping

con <- dbConnect(SQLite(), 'demo.db')
input <- tbl(con, 'mtcars')

# one group
input %>% summarize(n())

## # Source:   SQL [1 x 1]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##   `n()`
##   <int>
## 1    32

#input %>% summarize(n()) %>% show_query()

input %>% summarize(mean(hp, na.rm=TRUE))

## # Source:   SQL [1 x 1]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##   `mean(hp, na.rm = TRUE)`
##                      <dbl>
## 1                     147.

input %>% summarize(highest_hp=max(hp, na.rm=TRUE), 
                    min_hp=min(hp, na.rm=TRUE))

## # Source:   SQL [1 x 2]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##   highest_hp min_hp
##        <dbl>  <dbl>
## 1        335     52

# two groups
input %>% head(5) %>% group_by(am)

## # Source:   SQL [5 x 12]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
## # Groups:   am
##     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names   
##   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>       
## 1  21       6   160   110  3.9   2.62  16.5     0     1     4     4 Mazda RX4   
## 2  21       6   160   110  3.9   2.88  17.0     0     1     4     4 Mazda RX4 W…
## 3  22.8     4   108    93  3.85  2.32  18.6     1     1     4     1 Datsun 710  
## 4  21.4     6   258   110  3.08  3.22  19.4     1     0     3     1 Hornet 4 Dr…
## 5  18.7     8   360   175  3.15  3.44  17.0     0     0     3     2 Hornet Spor…

input %>% head(5) %>% 
  group_by(am) %>% 
  summarize(n())

## # Source:   SQL [2 x 2]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      am `n()`
##   <dbl> <int>
## 1     0     2
## 2     1     3

input %>% group_by(am) %>% summarize(n(), mean(hp))

## Warning: Missing values are always removed in SQL aggregation functions.
## Use `na.rm = TRUE` to silence this warning
## This warning is displayed once every 8 hours.

## # Source:   SQL [2 x 3]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      am `n()` `mean(hp)`
##   <dbl> <int>      <dbl>
## 1     0    19       160.
## 2     1    13       127.

input

## # Source:   table<mtcars> [?? x 12]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
##      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb row_names  
##    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>      
##  1  21       6  160    110  3.9   2.62  16.5     0     1     4     4 Mazda RX4  
##  2  21       6  160    110  3.9   2.88  17.0     0     1     4     4 Mazda RX4 …
##  3  22.8     4  108     93  3.85  2.32  18.6     1     1     4     1 Datsun 710 
##  4  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1 Hornet 4 D…
##  5  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2 Hornet Spo…
##  6  18.1     6  225    105  2.76  3.46  20.2     1     0     3     1 Valiant    
##  7  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4 Duster 360 
##  8  24.4     4  147.    62  3.69  3.19  20       1     0     4     2 Merc 240D  
##  9  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2 Merc 230   
## 10  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4 Merc 280   
## # ℹ more rows

# more groups
input %>% group_by(am, gear) %>% summarize()

## `summarise()` has grouped output by "am". You can override using the `.groups`
## argument.

## # Source:   SQL [4 x 2]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
## # Groups:   am
##      am  gear
##   <dbl> <dbl>
## 1     0     3
## 2     0     4
## 3     1     4
## 4     1     5

input %>% 
  group_by(am, gear) %>% 
  summarize(n=n(), avg_hp=mean(hp, na.rm=TRUE))

## `summarise()` has grouped output by "am". You can override using the `.groups`
## argument.

## # Source:   SQL [4 x 4]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
## # Groups:   am
##      am  gear     n avg_hp
##   <dbl> <dbl> <int>  <dbl>
## 1     0     3    15  176. 
## 2     0     4     4  101. 
## 3     1     4     8   83.9
## 4     1     5     5  196.

input %>% 
  group_by(am, gear) %>% 
  summarize(n=n(), avg_hp=mean(hp, na.rm=TRUE)) %>% 
  filter(avg_hp>150) %>% 
  arrange(n)

## `summarise()` has grouped output by "am". You can override using the `.groups`
## argument.

## # Source:     SQL [2 x 4]
## # Database:   sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\demo.db]
## # Groups:     am
## # Ordered by: n
##      am  gear     n avg_hp
##   <dbl> <dbl> <int>  <dbl>
## 1     1     5     5   196.
## 2     0     3    15   176.

dbDisconnect(con)

Joining

con <- dbConnect(SQLite(), 'joining.db')
dbListTables(con)

## [1] "InvoiceLines" "Invoices"

(invoices <- tbl(con, 'Invoices'))

## # Source:   table<Invoices> [3 x 6]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\joining.db]
##   InvoiceId CustomerName InvoiceDate         BillingCity BillingCountry Total
##       <int> <chr>        <chr>               <chr>       <chr>          <int>
## 1         1 John A.      2019-10-01 10:30:00 Oslo        Norway           300
## 2         2 Mary B.      2018-10-21 00:00:00 Boston      USA              600
## 3         3 Jane C.      2019-07-12 00:00:00 New York    USA             1500

(invoice_lines <- tbl(con, 'InvoiceLines'))

## # Source:   table<InvoiceLines> [0 x 4]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\joining.db]
## # ℹ 4 variables: InvoiceLineId <int>, InvoiceId <int>, Description <chr>,
## #   Price <dbl>

inner_join(invoices, invoice_lines, by='InvoiceId')

## # Source:   SQL [0 x 9]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\joining.db]
## # ℹ 9 variables: InvoiceId <int>, CustomerName <chr>, InvoiceDate <dbl>,
## #   BillingCity <chr>, BillingCountry <chr>, Total <dbl>, InvoiceLineId <int>,
## #   Description <chr>, Price <dbl>

#inner_join(invoices, invoice_lines, by.x='InvoiceId', by.y='InvoiceId')
#inner_join(invoices, invoice_lines, by='InvoiceId') %>% show_query()

inner_join(invoices, invoice_lines, by='InvoiceId') %>% 
  select(InvoiceId, CustomerName, BillingCountry, Description)

## # Source:   SQL [0 x 4]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\joining.db]
## # ℹ 4 variables: InvoiceId <int>, CustomerName <chr>, BillingCountry <chr>,
## #   Description <chr>

left_join(invoices, invoice_lines, by='InvoiceId')

## # Source:   SQL [3 x 9]
## # Database: sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\joining.db]
##   InvoiceId CustomerName InvoiceDate         BillingCity BillingCountry Total
##       <int> <chr>        <chr>               <chr>       <chr>          <int>
## 1         1 John A.      2019-10-01 10:30:00 Oslo        Norway           300
## 2         2 Mary B.      2018-10-21 00:00:00 Boston      USA              600
## 3         3 Jane C.      2019-07-12 00:00:00 New York    USA             1500
## # ℹ 3 more variables: InvoiceLineId <int>, Description <chr>, Price <dbl>

#left_join(invoices, invoice_lines, by='InvoiceId') %>% show_query()


left_join(invoices, invoice_lines, by='InvoiceId') %>% 
  select(InvoiceId, CustomerName, Description, Price) %>%
  filter(Price > 200) %>% 
  arrange(desc(Price))

## # Source:     SQL [0 x 4]
## # Database:   sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\joining.db]
## # Ordered by: desc(Price)
## # ℹ 4 variables: InvoiceId <int>, CustomerName <chr>, Description <chr>,
## #   Price <dbl>

inner_join(invoices, invoice_lines, by='InvoiceId') %>% 
  select(InvoiceId, CustomerName, Description, Price) %>%
  group_by(CustomerName) %>% 
  summarize(n=n(), avg_price=mean(Price, na.rm=TRUE)) %>% 
  arrange(avg_price)

## # Source:     SQL [0 x 3]
## # Database:   sqlite 3.41.2 [C:\Users\aimlr\Documents\GitHub\My-Projects\R\PluralSight\Importing Data from Relational Databases in R 3\importing-data-relational-databases-r\joining.db]
## # Ordered by: avg_price
## # ℹ 3 variables: CustomerName <chr>, n <lgl>, avg_price <lgl>

dbDisconnect(con)

Importing Data from Relational Databases in R 3

Syed Hamed Raza

2023-08-30

Connecting With DBI

DBI Overview

DBI functions

Implementation

DBI Overview

SQLite Database Connection

Create Connection, Write Data, Read Data, Close Connection

Try To Read Data If Connection is Closed

PostgreSQL Database Connection

Create Connection, Read Data, Close Connection

MySQL Database Connection

Create Connection, Read Data, Close Connection

ODBC Postgres Database Connection

Create Connection, Read Data, Close Connection

Mixing DBI and ODBC

Querying With SQL

Selecting

Ordering

Filtering

Grouping

Joining

Updating

Inserting

Deleting

Querying With DPLYR

Selecting

Mutating

Piping

Arranging

Filtering

Grouping

Joining