R SQL

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

getwd()

[1] "C:/Users/raulr/Downloads"

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")

WARNING: Rtools is required to build R packages but is not currently installed. Please download and install the appropriate version of Rtools before proceeding:

https://cran.rstudio.com/bin/windows/Rtools/
Installing package into ‘C:/Users/raulr/AppData/Local/R/win-library/4.2’
(as ‘lib’ is unspecified)
trying URL 'https://cran.rstudio.com/bin/windows/contrib/4.2/sqldf_0.4-11.zip'
Content type 'application/zip' length 78523 bytes (76 KB)
downloaded 76 KB

package ‘sqldf’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
    C:\Users\raulr\AppData\Local\Temp\RtmpIlbpCL\downloaded_packages

install.packages("sqldf")

WARNING: Rtools is required to build R packages but is not currently installed. Please download and install the appropriate version of Rtools before proceeding:

https://cran.rstudio.com/bin/windows/Rtools/
Installing package into ‘C:/Users/raulr/AppData/Local/R/win-library/4.2’
(as ‘lib’ is unspecified)
trying URL 'https://cran.rstudio.com/bin/windows/contrib/4.2/sqldf_0.4-11.zip'
Content type 'application/zip' length 78523 bytes (76 KB)
downloaded 76 KB

package ‘sqldf’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
    C:\Users\raulr\AppData\Local\Temp\RtmpIlbpCL\downloaded_packages

library(sqldf)

Warning: package ‘sqldf’ was built under R version 4.2.2Loading required package: gsubfn
Warning: package ‘gsubfn’ was built under R version 4.2.2Loading required package: proto
Warning: package ‘proto’ was built under R version 4.2.2Loading required package: RSQLite
Warning: package ‘RSQLite’ was built under R version 4.2.2

Upload data frame quiz2, since it has header to have a proper visualization would be necessary to write header=TRUE and since values are separated by commas sep=‘,’

Let us run a query that returns every single record from the table data1. Function sqldf allows you to select what do you want to show, in this exapmle it is plottted the whole data frmae but in the next one will be selected some parts.

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

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

In this example sqldf function returns the first 10 rows of the dataframe which in this case is the whole dataframe.

In this example sqldf function returns the first 5 rows ordered by the number of infections, from the one with highest number to the oone with the lowest, of the dataframe

Interpret the output of the query ran above and run a similar query (limit 7) including the attributes ipadddr , ufo2010 ,and infections.

This example returns region and infection variables with more than 1000 infections, and it is ordered from the city with more infections to the one with less.

Now I would like you to inspect (create a query) income,region and population when the condition is income>=50,000. Make comments There is not location with 50000 or more infections.

Returns the rows with more than 50000 income and more than 2000 infections or rows with a bigger population than 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).

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

Interpret the query below and list a hypothetical case scenario in which this query would be useful. Query below would be useful since we would like to know if could be a relationship between subregions which name starts with “a” and number of infections. Reason to search this relationship could be that have more tourists because their names are appears first in the lists.

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

Let us inspect the new table defined below data2.

Let us apply summary to data2 and examine the output. This summary is saying that the number of regions examined is 29, that its populations goes from 2311 to 1554720 people, income goes from 26784 to 70821, in some of the subregions have zero unadentified flying objects visualizations since maximum number of visualization (169) is so much higher than the value of the 3rd quarter (9), and since value of the mean is not very big aswell, most of the subregions do not have so much visualizations and that there are a small number of subregions that have a lot of ufo visualizations, this values works as outliers. From the data could be seen too that the average of infections between all the regions is 1117.

summary(data2)

    region               pop              income          ipaddr           ufo2010         infections  
 Length:29          Min.   :   2311   Min.   :26784   Min.   :    637   Min.   :  0.00   Min.   :  39  
 Class :character   1st Qu.:  19005   1st Qu.:37970   1st Qu.:  12294   1st Qu.:  0.00   1st Qu.: 123  
 Mode  :character   Median :  32122   Median :41595   Median :  30418   Median :  2.00   Median : 245  
                    Mean   : 135940   Mean   :43858   Mean   : 440130   Mean   : 16.66   Mean   :1117  
                    3rd Qu.: 101482   3rd Qu.:47469   3rd Qu.: 102104   3rd Qu.:  9.00   3rd Qu.: 672  
                    Max.   :1554720   Max.   :70821   Max.   :5394949   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. Since west is the only word in regions that starts with w.

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

3-Run a few queries that can potentially help us to make the point that: a.Region affects the number of infections Could be said that region affects the number of infections because the average of each location is very different.

b.Population affects the number of infections I would say that since regions with more population has higher value of average infections, yes population affects infections.

c.Income affects the number of infections The maximun number of infections is for the max income. However, I do not see a clear relation between these 2 variables.

sqldf('SELECT income,infections FROM data2 order by income desc')

NA

d.ufo2010 affects the number of infections Since biggest number of infections happened in regions where more unidentified flying objects are founded, I think could exits a relationship between variable ufo2010 and infections.

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

The average of infections of each region depends on the number on income the region has.

with the data that could be seen inthe table, I think the income does not depend on the population number but xist a realtionship between income and infections.