<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-1/"></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/10/r-with-remote-databases-exercises-part-1/"> 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 first 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/10/r-with-remote-databases-solutions-part-1/" 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 (the database will be created on-the-fly so you do not have to take care of this).</p>
<p><strong>Exercise 2</strong><br>
Upload data sets <code>nycflights13::flights</code> and <code>nycflights13::planes</code> to the database as non temporal tables.</p>
<p><strong>Exercise 3</strong><br>
List names of columns in <code>flights</code> table (hint: There is a function in <code>DBI</code> package for this).</p>
<p><strong>Exercise 4</strong><br>
Use SQL query to count number of flights per carrier and pull it to a local tibble.</p>
<p><strong>Exercise 5</strong><br>
Do the same thing using <code>dplyr</code> verbs instead of SQL.</p>
<p><strong>Exercise 6</strong><br>
Calculate number of flights, mean and total distance per plane, discard records with <code>NA</code> at <code>tailnum</code> column and save it to temporal table.</p>
<div>
<aside><img src="http://www.r-exercises.com/2017/09/10/r-with-remote-databases-exercises-part-1/"></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 7</strong><br>
List all the tables in the database (hint: There is a function in <code>DBI</code> package for this).</p>
<p><strong>Exercise 8</strong><br>
Use <code>head()</code>, <code>tail()</code> and <code>nrow()</code> to investigate table <code>planes</code>. Do you understand why the latter two do not work?</p>
<p><strong>Exercise 9</strong><br>
Join the table from exercise 6 with <code>planes</code> table and without pulling the data to local frame find manufacturers and models of 10 planes with the highest total flown distance.</p>
<p><strong>Exercise 10</strong><br>
Check what is the actual SQL query generated by the code you have created in exercise 9.</p>
<div>
<h3>Related exercise sets:</h3>
<ol>
<li><a rel="nofollow" href="http://www.r-exercises.com/2016/12/26/descriptive-analytics-part-5-data-visualisation-spatial/" title="Descriptive Analytics-Part 5: Data Visualisation (Spatial data)" target="_blank">Descriptive Analytics-Part 5: Data Visualisation (Spatial data) </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/02/descriptive-analytics-part-2-data-imputation/" title="Descriptive Analytics-Part 2 : Data Imputation" target="_blank">Descriptive Analytics-Part 2 : Data Imputation </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/10/r-with-remote-databases-exercises-part-1/"> 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/5iaJE4U59fA" height="1"></a></div>