R-SQL Exercises

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

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),])