R-SQL Exercises

[This article was first published on R-exercises, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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 about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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)