O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Understanding SQL and R

Video Description

This course is recommended for anyone (business analyst, researcher, social scientist, programmer, etc.) interested in analyzing and visualizing data. It shows you how to combine two of the most popular technologies used in data analytics: SQL and R. SQL is essential for communicating with relational databases, the type of database where most of the world's data is stored.

R is a statistical analysis tool: It uses SQL to interact with databases for the purpose of creating charts, plots, reports, visualizations, and even web applications that incorporate data into a final product. Complete this course and you'll learn the basics of a skill set highly valued by employers around the globe. The course will go fastest for those with basic command line interface experience and some previous exposure to both SQL and R.

  • Gain experience combining SQL and R for data analytics and visualization purposes
  • Learn to write SQL queries that can retrieve and summarize data stored in databases
  • Explore the statistical analysis and visualization capabilities of R
  • See how R uses SQL to interact with databases
  • Discover how to create graphics using R, based on data in databases
  • Learn to use features of RStudio to streamline the analysis process
Casimir Saternos has worked in IT since 1999 as a software architect, software engineer, systems engineer, developer, and database administrator. He's taught courses on Linux, R, and Oracle SQL; he holds multiple certifications (Statistical Inference, R Programming, etc.); he's written about SQL, R, and Java for Java Magazine, InfoQ.com, and others; and he's the author of the O'Reilly title "Client-Server Web Apps with JavaScript and Java".

Table of Contents

  1. Introduction
    1. Welcome To The Course 00:02:36
    2. About The Author 00:02:07
    3. Course Overview 00:05:46
    4. Current Context 00:08:14
    5. Introduction To SQL 00:07:20
    6. Introduction To R 00:08:30
    7. Software Installation 00:06:42
    8. Rstudio Overview 00:05:39
    9. R Packages 00:07:42
    10. The Relationship Between R And SQL 00:10:43
    11. Demo Application And Database Schema Overview 00:04:16
  2. SQL With Single Table Results Sets
    1. Relational Theory Review 00:08:35
    2. Results Sets 00:07:17
    3. Processing Results Sets With R 00:08:33
    4. Filtering And Ordering With SQL 00:06:59
    5. Grouping And Summarizing SQL 00:06:03
    6. Modify Results Using SQL Functions 00:08:30
  3. SQL With Multiple Tables
    1. Common Database Joins 00:10:15
    2. Less-Common Joins 00:04:52
    3. Subqueries 00:08:13
    4. Set Operations 00:04:51
    5. DBA Considerations 00:09:01
    6. Table-Like Objects 00:06:25
    7. Indexes 00:06:08
  4. R Packages And SQL-Like Results Set Processing
    1. SQL Results Set And Tidy Data 00:06:53
    2. Processing Results Sets With R Vs SQL 00:08:55
    3. Filtering And Ordering With Dplyr Vs. SQL 00:08:20
    4. Grouping And Summarizing With Dplyr Vs. SQL 00:06:03
    5. Modify Results Using Dplyr And R Functions 00:07:10
    6. Joins Using Dplyr 00:06:22
    7. Set Operations Using Dplyr 00:04:01
    8. Reshape Package 00:05:49
    9. RTidy Package 00:04:31
  5. Data Artifacts Using SQL And R
    1. Plotting Database Results With R 00:09:14
    2. Plots Using Base R Plots 00:08:22
    3. Plots Using Lattice 00:06:53
    4. Plots Using ggplot2 00:09:36
    5. Plotting Time Series Data 00:08:24
    6. Creating Maps With R 00:07:33
    7. Creating Reports With R 00:09:00
    8. Web Applications With R 00:06:54
  6. Data Sources And Connections
    1. Sample Data Sets 00:06:31
    2. Database File Exports/Imports 00:06:47
    3. Local Relational Databases (RSQLite) 00:07:00
    4. Non-Relational Data Sources 00:08:06
    5. Remote Connections 00:04:33
    6. Troubleshooting Remote Connections 00:04:39
    7. JDBC Client Software 00:05:32
  7. Additional Topics
    1. Derived Tables 00:07:23
    2. Vendor Specific SQL 00:08:51
    3. Non-SQL Inspired/SQL-Like Languages 00:05:56
    4. R Inside The Database 00:04:52
    5. Schema Design Considerations 00:08:06
  8. Conclusion
    1. Wrap Up And Thank You 00:02:10