Course Description

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


Self Paced Online Courses


Our online self paced courses are designed to allow participants to learn at their convenience. Learning outcomes are clearly defined and enhanced by the use of byte sized videos, PPTs, assignments, quizzes, suggested reading and case studies. Participants can take advantage of the discussion forum to discuss with their peers and the instructors to enhance their learning experience.


Course Features




Course Requirements

  • Access to a computer with an internet connection

  • Ability and permissions to download files & install software

  • Basic knowledge of English (Course is delivered in English language)


Who is this course for?


We expect the students to have some prior experience with R and RStudio. If you have never used R before, our Introduction to R course will help you prepare for this course.


If I do not meet the requirements to enroll, what should I do?

We have a number of free courses that can help you prepare including:

  • Introduction to R

  • Import Data into R

  • Data Wrangling with dplyr


How will I execute the practical's?


You will use RStudio for the practical's. All the R scripts used in the examples, assignments and case study are available for download from the Learning Management System. For those of you who face issues installing RStudio on your system, we have created a RStudio cloud project where you can practice without installing any software or downloading files.

Do I need to apply? What are the admission criteria?

No. This course is open to enrolment for all and accepts students regardless of experience and background.



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