Database Package

#install.packages("sqldf")
library(sqldf)
## Loading required package: gsubfn
## Loading required package: proto
## Loading required package: RSQLite

Create Database

We’ll create a SQLite database for the purpose of this project.

db <- dbConnect(SQLite(), dbname = "project_3.sqlite")

Create Tables

Create Cyber Coders Table

dbSendQuery(conn = db,
             "CREATE TABLE IF NOT EXISTS cybercoders
             (titles TEXT,
             date DATE,
             location TEXT,
             wage TEXT,
             skills TEXT,
             query TEXT)")
## <SQLiteResult>
##   SQL  CREATE TABLE IF NOT EXISTS cybercoders
##              (titles TEXT,
##              date DATE,
##              location TEXT,
##              wage TEXT,
##              skills TEXT,
##              query TEXT)
##   ROWS Fetched: 0 [complete]
##        Changed: 0
# Uncomment should you have to drop this table
#dbSendQuery(conn = db,
#             "DROP TABLE cybercoders")

Create Career Builder Table

dbSendQuery(conn = db,
             "CREATE TABLE IF NOT EXISTS careerbuilder
             (ws_order TEXT,
             ws_st_url TEXT,
             link TEXT,
             link_href TEXT,
             load_more TEXT,
             load_more_href TEXT,
             name TEXT,
             location TEXT,
             salary TEXT,
             skills TEXT,
             query TEXT)")
## Warning: Closing open result set, pending rows
## <SQLiteResult>
##   SQL  CREATE TABLE IF NOT EXISTS careerbuilder
##              (ws_order TEXT,
##              ws_st_url TEXT,
##              link TEXT,
##              link_href TEXT,
##              load_more TEXT,
##              load_more_href TEXT,
##              name TEXT,
##              location TEXT,
##              salary TEXT,
##              skills TEXT,
##              query TEXT)
##   ROWS Fetched: 0 [complete]
##        Changed: 0
# Uncomment should you have to drop this table
#dbSendQuery(conn = db,
#             "DROP TABLE careerbuilder")

Create Blended View

We’ll create a blended view for analysis in which we’re lining up the Job Title, Location, Salary, and Skill from each of the ‘cybercoders’ and ‘careerbuilder’ tables.

dbSendQuery(conn = db,
             "CREATE VIEW IF NOT EXISTS job_postings AS
             SELECT 'Cyber Coders' AS website, query, titles, location, wage, skills FROM cybercoders
             UNION
             SELECT 'Career Builder' AS website, query, name, location, salary, skills FROM careerbuilder")
## Warning: Closing open result set, pending rows
## <SQLiteResult>
##   SQL  CREATE VIEW IF NOT EXISTS job_postings AS
##              SELECT 'Cyber Coders' AS website, query, titles, location, wage, skills FROM cybercoders
##              UNION
##              SELECT 'Career Builder' AS website, query, name, location, salary, skills FROM careerbuilder
##   ROWS Fetched: 0 [complete]
##        Changed: 0
# Uncomment should you have to drop this view
#dbSendQuery(conn = db,
#             "DROP VIEW job_postings")

Close Connection

dbDisconnect(db)
## Warning in connection_release(conn@ptr): There are 1 result in use. The
## connection will be released when they are closed