<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/r-with-remote-databases-exercises-part-2/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.r-exercises.com/2017/09/17/r-with-remote-databases-exercises-part-2/"> R-exercises</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p><a rel="nofollow" href="https://i1.wp.com/www.r-exercises.com/wp-content/uploads/2017/09/database-149760_1280.png" target="_blank"><img sizes="(max-width: 124px) 100vw, 124px" src="https://i1.wp.com/www.r-exercises.com/wp-content/uploads/2017/09/database-149760_1280-248x300.png?resize=124%2C150" alt="" class="wp-image-7810" data-recalc-dims="1"></a><br>
This is common case when working with data that your source is a remote database. Usual ways to cope this when using R is either to load all the data into R or to perform the heaviest joins and aggregations with SQL before loading the data. Both of them have cons: the former one is limited by the memory capacity and may be very slow and the later forces you to use two technologies thus is more complicated and prone to errors. Solution to these problems is to use <code>dplyr</code> with <code>dbplyr</code> to communicate with database backend. This allows user to write <code>dplyr</code> code that is translated to SQL and executed at database server. One can say that this combines advantages of the two standard solutions and gets rid of their disadvantages.</p>
<p>This is the second part of R with remote databases series. For other parts follow the tag <a rel="nofollow" href="http://r-exercises.com/tags/databases" target="_blank">databases</a>.</p>
<p>The reader is assumed to know basics of <code>dplyr</code> and SQL. If you want to practice <code>dplyr</code> first there is great series of exercises <a rel="nofollow" href="http://www.r-exercises.com/tag/data-wrangling/" target="_blank">Data wrangling: Transforming</a> available. For quick introduction to <code>dplyr</code> with database backend I recommend this <a rel="nofollow" href="https://cran.r-project.org/web/packages/dbplyr/vignettes/dbplyr.html" target="_blank">vignette</a>.</p>
<p>Answers to the exercises are available <a rel="nofollow" href="http://r-exercises.com/2017/09/17/r-with-remote-databases-solutions-part-2/" target="_blank">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>
Load libraries: <code>dplyr</code>, <code>dbplyr</code>, <code>DBI</code>, <code>RSQLite</code>, <code>nycflights13</code>. Create a connection to temporal in-memory SQLite database and load to it two copies of data set <code>nycflights13::flights</code> – one with no indexes and another with index at <code>carrier</code>.</p>
<p><strong>Exercise 2</strong><br>
Compare execution time for counting a number of flights per carrier for indexed and not indexed table. (HINT: You can use <code>st &lt;- Sys.time(); **code**; Sys.time - st</code> or <code>microbenchmark</code> package.)</p>
<p><strong>Exercise 3</strong><br>
Compare query plans for queries from Exercise 2.</p>
<p><strong>Exercise 4</strong><br>
Check how dbplyr translates <code>as.character</code> to SQL.</p>
<p><strong>Exercise 5</strong><br>
Check how dbplyr translates <code>substr</code> to SQL depending on values of parameters. What is the relation between R and SQL version?</p>
<p><strong>Exercise 6</strong><br>
Check how dbplyr translates <code>^</code> to SQL. Check how does it work with SQLite.</p>
<p><strong>Exercise 7</strong><br>
Check how dbplyr translates <code>mean</code> to SQL. What happens when you specify <code>na.rm</code> or <code>trim</code> parameter?</p>
<div>
<aside><img src="http://www.r-exercises.com/2017/09/17/r-with-remote-databases-exercises-part-2/"></aside>
<div><strong>Learn more</strong> about Data Pre-Processing in the online course <a rel="nofollow" href="http://www.r-exercises.com/product/r-data-pre-processing-data-management-shape-your-data/" target="_blank">R Data Pre-Processing &amp; Data Management – Shape your Data!</a>. In this course you will learn how to:
<ul>
<li>import data into R in several ways while also beeing able to identify a suitable import tool</li>
<li>use SQL code within R</li>
<li>And much more</li>
</ul>
</div>
</div>
<p><strong>Exercise 8</strong><br>
Calculate mean value of <code>dep_delay</code> per carrier. Compare results for the table in the database and for local one.</p>
<p><strong>Exercise 9</strong><br>
In database calculate number of destination airports with airport code starting with ‘A’. Use wildcard matching.</p>
<p><strong>Exercise 10</strong><br>
In database concatenate <code>origin</code> and <code>dest</code> to one column separated by ‘-‘.</p>
<div>
<h3>Related exercise sets:</h3>
<ol>
<li><a rel="nofollow" href="http://www.r-exercises.com/2017/09/10/r-with-remote-databases-exercises-part-1/" title="R with remote databases Exercises (Part-1)" target="_blank">R with remote databases Exercises (Part-1) </a></li>
<li><a rel="nofollow" href="http://www.r-exercises.com/2016/12/11/descriptive-analytics-part-5-data-visualisation-categorical-variables/" title="Descriptive Analytics-Part 5: Data Visualisation (Categorical variables)" target="_blank">Descriptive Analytics-Part 5: Data Visualisation (Categorical variables) </a></li>
<li><a rel="nofollow" href="http://www.r-exercises.com/2016/11/21/descriptive-analytics-part-4-data-manipulation/" title="Descriptive Analytics-Part 4 : Data Manipulation" target="_blank">Descriptive Analytics-Part 4 : Data Manipulation </a></li>
<li><a rel="nofollow" href="http://www.r-exercises.com/start-here-to-learn-r/" target="_blank">Explore all our (>1000) R exercises</a></li>
<li><a rel="nofollow" href="http://www.r-exercises.com/r-courses/" target="_blank">Find an R course using our R Course Finder</a> directory</li>
</ol>
</div>
	<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://www.r-exercises.com/2017/09/17/r-with-remote-databases-exercises-part-2/"> 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 rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/zkxkk_cD6gU" height="1"></a></div>