R SQL

Julius Schmid

In-class activity 5

Good afternoon all! First let us find out what our working directory is.

getwd()

[1] "/cloud/project"

Today we are going to query the table quiz2 by using Transact-SQL inside the R chunks. First, we are going to need to install the package sqldf.

install.packages("sqldf")

Error in install.packages : Updating loaded packages

We import our new downloaded library sqldf.

library(sqldf)

Loading required package: gsubfn
Loading required package: proto
Warning: couldn't connect to display ":0"Loading required package: RSQLite

We upload the csv file quiz2.csv and import it with the read.csv function. Since in the data file we have headers, we set header = TRUE. The separator in this file is a comma.

data1<-read.csv("quiz2.csv",header = TRUE,sep = ",")

We let us give out a summary of the data frame:

summary(data1)

  subregion            region               pop              income          ipaddr       
 Length:29          Length:29          Min.   :   2311   Min.   :26784   Min.   :    637  
 Class :character   Class :character   1st Qu.:  19005   1st Qu.:37970   1st Qu.:  12294  
 Mode  :character   Mode  :character   Median :  32122   Median :41595   Median :  30418  
                                       Mean   : 135940   Mean   :43858   Mean   : 440130  
                                       3rd Qu.: 101482   3rd Qu.:47469   3rd Qu.: 102104  
                                       Max.   :1554720   Max.   :70821   Max.   :5394949  
    ufo2010         infections  
 Min.   :  0.00   Min.   :  39  
 1st Qu.:  0.00   1st Qu.: 123  
 Median :  2.00   Median : 245  
 Mean   : 16.66   Mean   :1117  
 3rd Qu.:  9.00   3rd Qu.: 672  
 Max.   :169.00   Max.   :6781  

Let us run a query that returns every single record from the table data1.

sqldf("SELECT * FROM data1")

Now I would like you to create a query that returns every single record (row) for the fields region and population.

Let us create a query that returns the first 10 records of the data1 table.

sqldf('SELECT * FROM data1 LIMIT 10')

Next, select all columns from the data1 table, order it by the parameter infections (from big to small) and return the first 5 columns.

sqldf("SELECT * FROM data1 ORDER BY infections DESC  LIMIT 5")

Now run a similar query (limit 7) including the attributes ipadddr , ufo2010 ,and infections.

sqldf("SELECT ipaddr, ufo2010, infections FROM data1 ORDER BY infections DESC  LIMIT 7")

In the next step, we return the columns region and infections, filter all the rows where the number of infections is greater than 1000 and sort the rows by infections from highn to low.

sqldf('SELECT region,infections FROM data1 WHERE infections > 1000 order by 2 desc')

Now I would like you to inspect (create a query) income,region and population when the condition is income>=50,000. Make comments.

To do this, we select the columns income, region, and population (pop) from data1 where the income is greater than or equal to 50,000.

sqldf('SELECT income, region, pop FROM data1 WHERE income >= 50000')

These are only six rows, representing Idaho, Colorado, Pennsylvania, Vermont, California, and New York.

Next, we create a query to return the whole rows of data1 where either, income is greater than 50000 and infections greater than 2000 (case 1), or the population is greater than 100000 (case 2). Since we have two conditions for case 1, we use the AND operator and parentheses.

sqldf('SELECT * FROM data1 WHERE (income > 50000 AND infections > 2000) OR pop > 100000')

Interpret the query ran above and create a query that recreates a similar case scenario (one that is critical for the success of this mini-project).

Similarly, let us return all the rows where either, income > 50000 and number of ufos > 40 (case 1), or number of infections > 1000.

sqldf('SELECT * FROM data1 WHERE (income > 50000 AND ufo2010 > 40) OR infections > 1000')

Let us run a query that returns every region and population with a number of infections between 1000 and 7000.

sqldf('SELECT region,pop FROM data1 WHERE infections between  1000 and 7000')

Interpret the query below and list a hypothetical case scenario in which this query would be useful.

In this query, we return all data of subregions that start with the letter A. This could be useful if we have a loss of data and we are looking for values of a specific subregion which we lost (e.g., NA). But all we remember is that the subregion started with the letter A. We do this with the LIKE command and state LIKE “A%” to filter all the rows where subregion starts with an A.

sqldf('SELECT * FROM data1 WHERE subregion LIKE "A%"')

Last, let us find the average number of infections in our table.

sqldf("SELECT AVG(infections) FROM data1")

Let us inspect the new table defined below data2.

data2<-read.csv("finalexam.csv",header = TRUE,sep = ",")

Let us apply summary to data2 and examine the output.

summary(data2)

    region               pop              income          ipaddr       
 Length:29          Min.   :   2311   Min.   :26784   Min.   :    637  
 Class :character   1st Qu.:  19005   1st Qu.:37970   1st Qu.:  12294  
 Mode  :character   Median :  32122   Median :41595   Median :  30418  
                    Mean   : 135940   Mean   :43858   Mean   : 440130  
                    3rd Qu.: 101482   3rd Qu.:47469   3rd Qu.: 102104  
                    Max.   :1554720   Max.   :70821   Max.   :5394949  
    ufo2010         infections  
 Min.   :  0.00   Min.   :  39  
 1st Qu.:  0.00   1st Qu.: 123  
 Median :  2.00   Median : 245  
 Mean   : 16.66   Mean   :1117  
 3rd Qu.:  9.00   3rd Qu.: 672  
 Max.   :169.00   Max.   :6781  

Next, I would like you to complete the following steps:

1 - Run a query that returns full details for every record from the west region.

We select all columns (*) of the data2 table and filter the region to “West”.

sqldf('SELECT * FROM data2 WHERE region == "West"')

2 - Run a query that returns full details for every record from the east region where the number of infections is greater than 1500.

We select all columns (*) of the data2 table and add a filter where the region is set to “East” and the infections to greater than 1500 at the same time (AND).

sqldf('SELECT * FROM data2 WHERE region == "East" AND infections > 1500')

3 - Run a few queries that can potentially help us to make the point that:

1. Region affects the number of infections

Select the columns region and infections, and sort by infections (high to low):

sqldf('SELECT region, infections FROM data2 ORDER BY infections DESC')

The most infections seem to appear in the West region.

2. Population affects the number of infections

Select the pop and infections columns, order by infections (high to low).

sqldf('SELECT pop, infections FROM data2 ORDER BY infections DESC')

3. Income affects the number of infections

Select the income and infections columns, order by infections (high to low).

sqldf('SELECT income, infections FROM data2 ORDER BY infections DESC')

4. ufo2010 affects the number of infections

Select the ufo2010 and infections columns, order by infections (high to low).

sqldf('SELECT ufo2010, infections FROM data2 ORDER BY infections DESC')

5. The different interactions of region and income, and income and population affects the number of infections.

Select the columns region, income, population, and infections, and order by infections (high to low).

sqldf('SELECT region, income, pop, infections FROM data2 ORDER BY infections DESC')