R-SQL Exercises

December 7, 2016
By

(This article was first published on R-exercises, and kindly contributed to R-bloggers)

How to write Structured Query Language (SQL) code in R. Well there are many packages on CRAN that relate to databases.

01_sqldf

In the exercises below we cover some of the important data manipulation operations using SQL in R. We will use a ‘sqldf’ package, an R package for running SQL statements on data frames.

Answers to the exercises are available here

If you obtained a different (correct) answer than those listed on the solutions page, please feel free to post your answer as a comment on that page.

Exercise 1
Install the ‘sqldf’ and ‘PASWR’ package. Load the packages. Also load the ‘titanic3’ data from ‘PASWR’ package.

Exercise 2
Count the number of rows in the ‘titanic3’ data using sqldf function. Below is the R equivalent code to do the same.

nrow(titanic3)

Exercise 3
Select all the columns and rows from ‘titanic3’ data and put it into a variable ‘TitanicData’. Below is the R equivalent code to the same.

TitanicData <- titanic3[ , ]

Exercise 4
Select the first two columns of the ‘titanic3’ data and put it into a variable ‘TitanicSubset2Cols’. Below is the R equivalent code to the same. Note: you need to specify the column names in sqldf function.

TitanicSubset2Cols2 <- titanic3[,c(1,2)]

Exercise 5
Print the first 6 rows of the ‘titanic3’ dataset using sqldf function. Below is the R equivalent code to do the same.

head(titanic3)

Exercise 6
Count the number of people in the ‘titanic3’ dataset where the sex is female. Below is the R equivalent code to do the same.

nrow(titanic3[titanic3$sex=="female",])

Exercise 7
Count the number of people in the ‘titanic3’ dataset where the sex is female and the port of embarkment is southampton. Below is the R equivalent code to do the same.

nrow(titanic3[(titanic3$sex=="female" & titanic3$embarked=="southampton"),])

Exercise 8
Calculate the total amount paid by female (where sex is female). Below is the R equivalent code to do the same.

sum(titanic3$fare[titanic3$sex=="female"])

Exercise 9
Count the number of cabins in the ship. Below is the R equivalent code to do the same.

length(unique(titanic3$cabin))

Exercise 10
Count the number of people in the ship whose name start with ‘A’. Below is the R equivalent to do the same.

nrow(Data[grep("^A", Data$name),])

To leave a comment for the author, please follow the link and comment on their blog: R-exercises.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.

Sponsors

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)