Course Description

About the Course

In this course, you will learn to interact with databases from R. Databases are everywhere and as a data scientist you will interact with regularly irrespective of whether you like it or not. From connecting to different databases from R, using SQL script in RStudio & the SQL engine in knitr while working with RMarkdown documents to querying, visualizing and modeling data while securely handling database credential, this course covers all the important topics you should be aware of while working with databases.

What will I learn?

In this course, students will learn to 

  • connect to & explore database
  • read & write data
  • use RStudio SQL script & knitr SQL engine
  • query data using dplyr
  • visualize data with dbplot
  • model data with modeldb & tidypredict
  • explore RStudio connections pane
  • securely handle credentials


What are the prerequisites for this course?

  • Introduction to R
  • Data Wrangling with dplyr (Part 1)
  • Readable Code with Pipes


Course curriculum

  • 1

    Welcome

    • Course Resources

    • Agenda

    • Slides

    • R Script

    • Database

    • RStudio Cloud
  • 2

    Query Data

    • Read Entire Table

    • Read Sample Rows

    • Read Data in Batches

    • Query Details - I

    • Query Details - I

    • Query Details - II

    • Query Details - III

    • Your Turn

    • Practice
  • 3

    Tables

    • Check if Table Exists in Database

    • Create New Table in Database

    • Overwrite Table in Database

    • Append Data to Table in Database

    • Insert Data using SQL Statements

    • Remove/Delete Table from Database

    • Your Turn

    • Practice
  • 4

    Data Types & SQL Generation

    • Data Types

    • Generate SQL Statements
  • 5

    SQL Script & Engine

    • RStudio SQL Script

    • knitr SQL Engine

    • Your Turn

    • Practice

    • SQL Script & RMarkdown Document
  • 6

    Query Data using dplyr

    • Reference Table in Database

    • Query Data using dplyr Verbs

    • Collect Data from the Database

    • dplyr to SQL Translation

    • Simulate SQL Statements

    • Your Turn

    • Practice
  • 7

    Data Visualization

    • Introduction & Bar Plot

    • Line Chart

    • Your Turn

    • Practice
  • 8

    Data Modeling

    • Introduction

    • Simple Regression

    • Multiple Regression

    • Regression with Categorical Variables

    • Putting it All Together

    • Your Turn

    • Practice

    • Introduction to tidypredict

    • R Code for Fitted Values

    • SQL Statement for Fitted Values
  • 9

    Disconnect

    • Disconnect from the Database

    • Disconnect from the Database
  • 10

    RStudio Connections Tab

    • Introduction

    • Add New Connection

    • Add New Data Source

    • References
  • 11

    Handling Credentials

    • Introduction

    • Use rstudioapi

    • Use Environment Variables

    • Use Global Options

    • Use config

    • Use keyring

    • Scripts
  • 12

    Course Summary

    • Summary

    • Practice Solutions

    • References & Further Readings

Recommended Courses