<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/r-sql-exercises/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://r-exercises.com/2016/12/07/r-sql-exercises/"> R-exercises</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>How to write Structured Query Language (SQL) code in R. Well there are many packages on CRAN that relate to databases.</p>
<p><a target="_blank" rel="nofollow" href="http://r-exercises.com/wp-content/uploads/2016/12/01_sqldf.jpg?x61433"><img sizes="(max-width: 300px) 100vw, 300px" alt="01_sqldf" class="wp-image-3137" src="https://i2.wp.com/r-exercises.com/wp-content/uploads/2016/12/01_sqldf-300x79.jpg?resize=300%2C79" data-recalc-dims="1"></a></p>
<p>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.</p>
<p>Answers to the exercises are available <a target="_blank" rel="nofollow" href="http://r-exercises.com/2016/12/07/r-sql-exercises-2/">here</a></p>
<p>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.</p>
<p><strong>Exercise 1</strong><br>
Install the ‘sqldf’ and ‘PASWR’ package. Load the packages. Also load the ‘titanic3’ data from ‘PASWR’ package.</p>
<p><strong>Exercise 2</strong><br>
Count the number of rows in the ‘titanic3’ data using sqldf function. Below is the R equivalent code to do the same.<br>
<code><br>
nrow(titanic3)<br>
</code></p>
<p><strong>Exercise 3</strong><br>
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.<br>
<code><br>
TitanicData &lt;- titanic3[ , ]<br>
</code></p>
<p><strong>Exercise 4</strong><br>
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.<br>
<code><br>
TitanicSubset2Cols2 &lt;- titanic3[,c(1,2)]<br>
</code></p>
<p><strong>Exercise 5</strong><br>
Print the first 6 rows of the ‘titanic3’ dataset using sqldf function. Below is the R equivalent code to do the same.<br>
<code><br>
head(titanic3)<br>
</code></p>
<p><strong>Exercise 6</strong><br>
Count the number of people in the ‘titanic3’ dataset where the sex is female. Below is the R equivalent code to do the same.<br>
<code><br>
nrow(titanic3[titanic3$sex==&quot;female&quot;,])<br>
</code></p>
<p><strong>Exercise 7</strong><br>
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.<br>
<code><br>
nrow(titanic3[(titanic3$sex==&quot;female&quot; &amp; titanic3$embarked==&quot;southampton&quot;),])<br>
</code></p>
<p><strong>Exercise 8</strong><br>
Calculate the total amount paid by female (where sex is female). Below is the R equivalent code to do the same.<br>
<code><br>
sum(titanic3$fare[titanic3$sex==&quot;female&quot;])<br>
</code></p>
<p><strong>Exercise 9</strong><br>
Count the number of cabins in the ship. Below is the R equivalent code to do the same.<br>
<code><br>
length(unique(titanic3$cabin))<br>
</code></p>
<p><strong>Exercise 10</strong><br>
Count the number of people in the ship whose name start with ‘A’. Below is the R equivalent to do the same.<br>
<code><br>
nrow(Data[grep(&quot;^A&quot;, Data$name),])<br>
</code></p>
<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://r-exercises.com/2016/12/07/r-sql-exercises/"> R-exercises</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/search/data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/search/map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/search/animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/search/sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/search/SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/search/git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/search/hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/search/PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/search/time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/search/trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/pzlpadGcYEs"></a></div>