Good afternoon all! First let us find out what our working directory
is.
getwd()
[1] "C:/Users/f0874999/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/f0874999/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 78514 bytes (76 KB)
downloaded 76 KB
package ‘sqldf’ successfully unpacked and MD5 sums checked
The downloaded binary packages are in
C:\Users\f0874999\AppData\Local\Temp\Rtmp6LHE3m\downloaded_packages
library(sqldf)
Loading required package: gsubfn
Loading required package: proto
Loading required package: RSQLite
data1<-read.csv("quiz2.csv",header = TRUE,sep = ",")
summary(data1)
subregion region pop income ipaddr ufo2010
Length:29 Length:29 Min. : 2311 Min. :26784 Min. : 637 Min. : 0.00
Class :character Class :character 1st Qu.: 19005 1st Qu.:37970 1st Qu.: 12294 1st Qu.: 0.00
Mode :character Mode :character Median : 32122 Median :41595 Median : 30418 Median : 2.00
Mean : 135940 Mean :43858 Mean : 440130 Mean : 16.66
3rd Qu.: 101482 3rd Qu.:47469 3rd Qu.: 102104 3rd Qu.: 9.00
Max. :1554720 Max. :70821 Max. :5394949 Max. :169.00
infections
Min. : 39
1st Qu.: 123
Median : 245
Mean :1117
3rd Qu.: 672
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 now create a query that returns the first 10 records of the
data1 table.
sqldf('SELECT * FROM data1 LIMIT 10')
sqldf("SELECT * FROM data1 ORDER BY infections DESC LIMIT 5")
Interpret the output of the query ran above and run a similar query
(limit 7) including the attributes ipadddr ,
ufo2010 ,and infections.
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.
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).
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.
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 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.
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
I filter region and infections, ordering infections by
Decreasing and asking for the first 10 lines. As we can see
there are 7 regions with a lot of infections compared to the rest. 4 of
them are west and 3 east, not any central shows. So the region could
affect the infection number. West more, then East and last Central being
not that significal
b.Population affects the number of infections
I filter population and infections, ordering Population by
Decreasing and asking for the first 10 lines I do not believe
that the amount of population affect the number of infection since we
can see that the 4th, 5th, 6th, 7th, 9th, 10th most populated region
does not has the biggest infections.
c.Income affects the number of infections
I filter income and infections, ordering income by Decreasing
and asking for the first 10 lines I do not believe that the
amount of population affect the number of infection since we can see
that from the 10 variables with the highest income 5 does not has a
significant amount of infections.
d.ufo2010 affects the number of infections
I filter ufo2010 and infections, ordering infections by
Decreasing and asking for the first 10 lines I do not believe
that the amount of ufo2010 affect the number of infection since we can
see that from the 10 variables with the highest infections 6 does not
has a significant amount of ufo2010
e. The different interactions of region and income, and income and
population affects the number of infections.
I select region, income, pop, and infections. I asked for
income higher than 42000, population higher than 32000, or infections
higher than 1500, and ordering infections by Decreasing I do
not believe that the interactions of region and income affect the number
of infection but as we said it before just the region does affect the
infections. Also the relation of population and income it does affect
the number of infections.
Explain every single query you include in this part of the
handout.
LS0tDQp0aXRsZTogIkFjdGl2aXR5IDUiDQpvdXRwdXQ6IGh0bWxfbm90ZWJvb2sNCi0tLQ0KDQpHb29kIGFmdGVybm9vbiBhbGwhIEZpcnN0IGxldCB1cyBmaW5kIG91dCB3aGF0IG91ciB3b3JraW5nIGRpcmVjdG9yeSBpcy4NCg0KYGBge3J9DQpnZXR3ZCgpDQpgYGANCg0KVG9kYXkgd2UgYXJlIGdvaW5nIHRvIHF1ZXJ5IHRoZSB0YWJsZSBxdWl6MiBieSB1c2luZyBUcmFuc2FjdC1TUUwgaW5zaWRlIHRoZSBSIGNodW5rcy4gRmlyc3QsIHdlIGFyZSBnb2luZyB0byBuZWVkIHRvIGluc3RhbGwgdGhlIHBhY2thZ2UgKipzcWxkZioqLg0KDQpgYGB7cn0NCmluc3RhbGwucGFja2FnZXMoInNxbGRmIikNCmBgYA0KDQoNCmBgYHtyfQ0KbGlicmFyeShzcWxkZikNCmBgYA0KDQoNCmBgYHtyfQ0KZGF0YTE8LXJlYWQuY3N2KCJxdWl6Mi5jc3YiLGhlYWRlciA9IFRSVUUsc2VwID0gIiwiKQ0KYGBgDQoNCg0KYGBge3J9DQpzdW1tYXJ5KGRhdGExKQ0KYGBgDQoNCkxldCB1cyBydW4gYSBxdWVyeSB0aGF0IHJldHVybnMgZXZlcnkgc2luZ2xlIHJlY29yZCBmcm9tIHRoZSB0YWJsZSBkYXRhMS4NCg0KYGBge3J9DQpzcWxkZigiU0VMRUNUICogRlJPTSBkYXRhMSIpDQpgYGANCg0KTm93IEkgd291bGQgbGlrZSB5b3UgdG8gY3JlYXRlIGEgcXVlcnkgdGhhdCByZXR1cm5zIGV2ZXJ5IHNpbmdsZSByZWNvcmQgKHJvdykgZm9yIHRoZSBmaWVsZHMgcmVnaW9uIGFuZCBwb3B1bGF0aW9uLg0KDQpMZXQgdXMgbm93IGNyZWF0ZSBhIHF1ZXJ5IHRoYXQgcmV0dXJucyB0aGUgZmlyc3QgMTAgcmVjb3JkcyBvZiB0aGUgZGF0YTEgdGFibGUuDQoNCmBgYHtyfQ0Kc3FsZGYoJ1NFTEVDVCAqIEZST00gZGF0YTEgTElNSVQgMTAnKQ0KYGBgDQoNCg0KYGBge3J9DQpzcWxkZigiU0VMRUNUICogRlJPTSBkYXRhMSBPUkRFUiBCWSBpbmZlY3Rpb25zIERFU0MgIExJTUlUIDUiKQ0KYGBgDQoNCkludGVycHJldCB0aGUgb3V0cHV0IG9mIHRoZSBxdWVyeSByYW4gYWJvdmUgYW5kIHJ1biBhIHNpbWlsYXIgcXVlcnkgKGxpbWl0IDcpIGluY2x1ZGluZyB0aGUgYXR0cmlidXRlcyAqKmlwYWRkZHIqKiAsICoqdWZvMjAxMCoqICxhbmQgKippbmZlY3Rpb25zKiouDQoNCmBgYHtyfQ0Kc3FsZGYoJ1NFTEVDVCByZWdpb24saW5mZWN0aW9ucyBGUk9NIGRhdGExIFdIRVJFIGluZmVjdGlvbnMgPiAxMDAwIG9yZGVyIGJ5IDIgZGVzYycpDQpgYGANCg0KTm93IEkgd291bGQgbGlrZSB5b3UgdG8gaW5zcGVjdCAoY3JlYXRlIGEgcXVlcnkpIGluY29tZSxyZWdpb24gYW5kIHBvcHVsYXRpb24gd2hlbiB0aGUgY29uZGl0aW9uIGlzIGluY29tZT49NTAsMDAwLiBNYWtlIGNvbW1lbnRzLg0KDQpgYGB7cn0NCnNxbGRmKCdTRUxFQ1QgKiBGUk9NIGRhdGExIFdIRVJFIChpbmNvbWUgPiA1MDAwMCBBTkQgaW5mZWN0aW9ucyA+IDIwMDApIE9SIHBvcCA+IDEwMDAwMCcpDQpgYGANCg0KSW50ZXJwcmV0IHRoZSBxdWVyeSByYW4gYWJvdmUgYW5kIGNyZWF0ZSBhIHF1ZXJ5IHRoYXQgcmVjcmVhdGVzIGEgc2ltaWxhciBjYXNlIHNjZW5hcmlvIChvbmUgdGhhdCBpcyBjcml0aWNhbCBmb3IgdGhlIHN1Y2Nlc3Mgb2YgdGhpcyBtaW5pLXByb2plY3QpLg0KDQpMZXQgdXMgcnVuIGEgcXVlcnkgdGhhdCByZXR1cm5zIGV2ZXJ5IHJlZ2lvbiBhbmQgcG9wdWxhdGlvbiB3aXRoIGEgbnVtYmVyIG9mIGluZmVjdGlvbnMgYmV0d2VlbiAxMDAwIGFuZCA3MDAwLiANCg0KYGBge3J9DQpzcWxkZignU0VMRUNUIHJlZ2lvbixwb3AgRlJPTSBkYXRhMSBXSEVSRSBpbmZlY3Rpb25zIGJldHdlZW4gIDEwMDAgYW5kIDcwMDAnKQ0KYGBgDQoNCkludGVycHJldCB0aGUgcXVlcnkgYmVsb3cgYW5kIGxpc3QgYSBoeXBvdGhldGljYWwgY2FzZSBzY2VuYXJpbyBpbiB3aGljaCB0aGlzIHF1ZXJ5IHdvdWxkIGJlIHVzZWZ1bC4NCg0KYGBge3J9DQpzcWxkZignU0VMRUNUICogRlJPTSBkYXRhMSBXSEVSRSBzdWJyZWdpb24gTElLRSAiQSUiJykNCmBgYA0KDQpMYXN0LGxldCB1cyBmaW5kIHRoZSBhdmVyYWdlIG51bWJlciBvZiBpbmZlY3Rpb25zIGluIG91ciB0YWJsZS4NCg0KYGBge3J9DQpzcWxkZigiU0VMRUNUIEFWRyhpbmZlY3Rpb25zKSBGUk9NIGRhdGExIikNCmBgYA0KDQpMZXQgdXMgaW5zcGVjdCB0aGUgbmV3IHRhYmxlIGRlZmluZWQgYmVsb3cgZGF0YTIuDQoNCmBgYHtyfQ0KZGF0YTI8LXJlYWQuY3N2KCJmaW5hbGV4YW0uY3N2IixoZWFkZXIgPSBUUlVFLHNlcCA9ICIsIikNCmBgYA0KDQpMZXQgdXMgYXBwbHkgc3VtbWFyeSB0byBkYXRhMiBhbmQgZXhhbWluZSB0aGUgb3V0cHV0Lg0KYGBge3J9DQpzdW1tYXJ5KGRhdGEyKQ0KYGBgDQoNCk5leHQsIEkgd291bGQgbGlrZSB5b3UgdG8gY29tcGxldGUgdGhlIGZvbGxvd2luZyBzdGVwczoNCg0KIyMjIDEtUnVuIGEgcXVlcnkgdGhhdCByZXR1cm5zIGZ1bGwgZGV0YWlscyBmb3IgZXZlcnkgcmVjb3JkIGZyb20gdGhlIHdlc3QgcmVnaW9uLg0KYGBge3J9DQpzcWxkZignU0VMRUNUICogRlJPTSBkYXRhMiBXSEVSRSByZWdpb24gTElLRSAid2VzdCInKQ0KYGBgDQoNCg0KDQojIyMgMi1SdW4gYSBxdWVyeSB0aGF0IHJldHVybnMgZnVsbCBkZXRhaWxzIGZvciBldmVyeSByZWNvcmQgZnJvbSB0aGUgZWFzdCByZWdpb24gd2hlcmUgdGhlIG51bWJlciBvZiBpbmZlY3Rpb25zIGlzIGdyZWF0ZXIgdGhhbiAxNTAwLg0KYGBge3J9DQpzcWxkZignU0VMRUNUICogRlJPTSBkYXRhMiBXSEVSRSByZWdpb24gTElLRSAiZWFzdCIgQU5EIGluZmVjdGlvbnMgPiAxNTAwJykNCmBgYA0KDQoNCg0KIyMjIDMtUnVuIGEgZmV3IHF1ZXJpZXMgdGhhdCBjYW4gcG90ZW50aWFsbHkgaGVscCB1cyB0byBtYWtlIHRoZSBwb2ludCB0aGF0Og0KDQojIyMjICAgYS5SZWdpb24gYWZmZWN0cyB0aGUgbnVtYmVyIG9mIGluZmVjdGlvbnMNCmBgYHtyfQ0Kc3FsZGYoIlNFTEVDVCByZWdpb24saW5mZWN0aW9ucyBGUk9NIGRhdGEyIE9SREVSIEJZIGluZmVjdGlvbnMgREVTQyAgTElNSVQgMTAiKQ0KYGBgDQogICAqKkkgZmlsdGVyIHJlZ2lvbiBhbmQgaW5mZWN0aW9ucywgb3JkZXJpbmcgaW5mZWN0aW9ucyBieSBEZWNyZWFzaW5nIGFuZCBhc2tpbmcgZm9yIHRoZSBmaXJzdCAxMCBsaW5lcyoqLiBBcyB3ZSBjYW4gc2VlIHRoZXJlIGFyZSA3IHJlZ2lvbnMgd2l0aCBhIGxvdCBvZiBpbmZlY3Rpb25zIGNvbXBhcmVkIHRvIHRoZSByZXN0LiA0IG9mIHRoZW0gYXJlIHdlc3QgYW5kIDMgZWFzdCwgbm90IGFueSBjZW50cmFsIHNob3dzLiBTbyB0aGUgcmVnaW9uIGNvdWxkIGFmZmVjdCB0aGUgaW5mZWN0aW9uIG51bWJlci4gV2VzdCBtb3JlLCB0aGVuIEVhc3QgYW5kIGxhc3QgQ2VudHJhbCBiZWluZyBub3QgdGhhdCBzaWduaWZpY2FsDQogICANCiMjIyMgIGIuUG9wdWxhdGlvbiBhZmZlY3RzIHRoZSBudW1iZXIgb2YgaW5mZWN0aW9ucw0KYGBge3J9DQpzcWxkZigiU0VMRUNUIHBvcCxpbmZlY3Rpb25zIEZST00gZGF0YTIgT1JERVIgQlkgcG9wIERFU0MgIExJTUlUIDEwIikNCmBgYA0KKipJIGZpbHRlciBwb3B1bGF0aW9uIGFuZCBpbmZlY3Rpb25zLCBvcmRlcmluZyBQb3B1bGF0aW9uIGJ5IERlY3JlYXNpbmcgYW5kIGFza2luZyBmb3IgdGhlIGZpcnN0IDEwIGxpbmVzKiogSSBkbyBub3QgYmVsaWV2ZSB0aGF0IHRoZSBhbW91bnQgb2YgcG9wdWxhdGlvbiBhZmZlY3QgdGhlIG51bWJlciBvZiBpbmZlY3Rpb24gc2luY2Ugd2UgY2FuIHNlZSB0aGF0IHRoZSA0dGgsIDV0aCwgNnRoLCA3dGgsIDl0aCwgMTB0aCBtb3N0IHBvcHVsYXRlZCByZWdpb24gZG9lcyBub3QgaGFzIHRoZSBiaWdnZXN0IGluZmVjdGlvbnMuDQogICANCiMjIyMgICBjLkluY29tZSBhZmZlY3RzIHRoZSBudW1iZXIgb2YgaW5mZWN0aW9ucw0KYGBge3J9DQpzcWxkZigiU0VMRUNUIGluY29tZSxpbmZlY3Rpb25zIEZST00gZGF0YTIgT1JERVIgQlkgaW5jb21lIERFU0MgIExJTUlUIDEwIikNCmBgYA0KICAgKipJIGZpbHRlciBpbmNvbWUgYW5kIGluZmVjdGlvbnMsIG9yZGVyaW5nIGluY29tZSBieSBEZWNyZWFzaW5nIGFuZCBhc2tpbmcgZm9yIHRoZSBmaXJzdCAxMCBsaW5lcyoqIEkgZG8gbm90IGJlbGlldmUgdGhhdCB0aGUgYW1vdW50IG9mIHBvcHVsYXRpb24gYWZmZWN0IHRoZSBudW1iZXIgb2YgaW5mZWN0aW9uIHNpbmNlIHdlIGNhbiBzZWUgdGhhdCBmcm9tIHRoZSAxMCB2YXJpYWJsZXMgd2l0aCB0aGUgaGlnaGVzdCBpbmNvbWUgNSBkb2VzIG5vdCBoYXMgYSBzaWduaWZpY2FudCBhbW91bnQgb2YgaW5mZWN0aW9ucy4NCiAgIA0KIyMjIyAgIGQudWZvMjAxMCBhZmZlY3RzIHRoZSBudW1iZXIgb2YgaW5mZWN0aW9ucw0KYGBge3J9DQpzcWxkZigiU0VMRUNUIHVmbzIwMTAsaW5mZWN0aW9ucyBGUk9NIGRhdGEyIE9SREVSIEJZIGluZmVjdGlvbnMgREVTQyAgTElNSVQgMTAiKQ0KDQpgYGANCiAgKipJIGZpbHRlciB1Zm8yMDEwIGFuZCBpbmZlY3Rpb25zLCBvcmRlcmluZyBpbmZlY3Rpb25zIGJ5IERlY3JlYXNpbmcgYW5kIGFza2luZyBmb3IgdGhlIGZpcnN0IDEwIGxpbmVzKiogSSBkbyBub3QgYmVsaWV2ZSB0aGF0IHRoZSBhbW91bnQgb2YgdWZvMjAxMCBhZmZlY3QgdGhlIG51bWJlciBvZiBpbmZlY3Rpb24gc2luY2Ugd2UgY2FuIHNlZSB0aGF0IGZyb20gdGhlIDEwIHZhcmlhYmxlcyB3aXRoIHRoZSBoaWdoZXN0IGluZmVjdGlvbnMgNiBkb2VzIG5vdCBoYXMgYSBzaWduaWZpY2FudCBhbW91bnQgb2YgdWZvMjAxMA0KICAgDQojIyMjICAgZS4gVGhlIGRpZmZlcmVudCBpbnRlcmFjdGlvbnMgb2YgcmVnaW9uIGFuZCBpbmNvbWUsIGFuZCBpbmNvbWUgYW5kIHBvcHVsYXRpb24gYWZmZWN0cyB0aGUgbnVtYmVyIG9mIGluZmVjdGlvbnMuDQpgYGB7cn0NCnNxbGRmKCdTRUxFQ1QgcmVnaW9uLGluY29tZSxwb3AsaW5mZWN0aW9ucyBGUk9NIGRhdGEyIFdIRVJFIChpbmNvbWUgPiA0MjAwMCBBTkQgcG9wID4gMzIwMDApIE9SIGluZmVjdGlvbnMgPiAxNTAwIE9SREVSIEJZIGluZmVjdGlvbnMgREVTQycpDQpgYGANCioqSSBzZWxlY3QgcmVnaW9uLCAgaW5jb21lLCBwb3AsIGFuZCBpbmZlY3Rpb25zLiBJIGFza2VkIGZvciBpbmNvbWUgaGlnaGVyIHRoYW4gNDIwMDAsIHBvcHVsYXRpb24gaGlnaGVyIHRoYW4gMzIwMDAsIG9yIGluZmVjdGlvbnMgaGlnaGVyIHRoYW4gMTUwMCwgYW5kIG9yZGVyaW5nIGluZmVjdGlvbnMgYnkgRGVjcmVhc2luZyAqKiBJIGRvIG5vdCBiZWxpZXZlIHRoYXQgdGhlIGludGVyYWN0aW9ucyBvZiByZWdpb24gYW5kIGluY29tZSBhZmZlY3QgdGhlIG51bWJlciBvZiBpbmZlY3Rpb24gYnV0IGFzIHdlIHNhaWQgaXQgYmVmb3JlIGp1c3QgdGhlIHJlZ2lvbiBkb2VzIGFmZmVjdCB0aGUgaW5mZWN0aW9ucy4gQWxzbyB0aGUgcmVsYXRpb24gb2YgcG9wdWxhdGlvbiBhbmQgaW5jb21lIGl0IGRvZXMgYWZmZWN0IHRoZSBudW1iZXIgb2YgaW5mZWN0aW9ucy4NCiAgIA0KRXhwbGFpbiBldmVyeSBzaW5nbGUgcXVlcnkgeW91IGluY2x1ZGUgaW4gdGhpcyBwYXJ0IG9mIHRoZSBoYW5kb3V0Lg0KDQoNCg0K