Baseball Database Queries with SQL and dplyr

Explaining the relationship between SQL and dplyr with baseball examples.

Created byCharles Redmond

Last updated 6/2015

English

What you'll learn

download the Lahman baseball database for Access
install dplyr and the Lahman baseball database package in R
select, filter, and order with SQL and dplyr
group and aggregate with SQL and dplyr
perform inner joins with SQL and dplyr

Course content

5 sections • 30 lectures • 3h 2m total length

Introduction1:05
This is our course intro.
Access Set-up5:55
After viewing this lecture, you will know how to download the Access version of the Lahman Baseball Database and how to set it up for querying.
RStudio Set-up7:10
After viewing this lecture, you will be able to download R and RStudio and install R packages. You will also gain a basic familiarity with the RStudio interface.
The Rcpp Package1:00
After viewing this lecture, you will be able to install the Rcpp package.

SELECT (SQL) and select (dplyr)9:04
After viewing this lecture, you will be able to use SQL in Access and dplyr in R to select columns of interest from a table or data frame.
ORDER BY (SQL) and arrange (dplyr)4:58
After viewing this lecture, you will be able to use SQL in Access and dplyr in R to order records by a particular column.
WHERE (SQL) and filter (dplyr)7:00
After viewing this lecture, you will be able to use SQL in Access and dplyr in R to filter records according to certain criteria.
AND and OR6:11
After viewing this lecture, you will be able to employ more complicated filters with both SQL and dplyr.

Grouping and Sum in Access SQL3:49
After viewing this lecture, you will be able to group records and sum a particular field for a grouping with SQL in Access.
Grouping, Summarize, and Sum in dplyr5:33
After viewing this lecture, you will be able to group records and sum a particular field for a group with

dplyr in R.
Averaging in Access SQL and dplyr6:31
After viewing this lecture, you will be able to use the avg (SQL) and mean (dplyr) aggregate functions.
max and min5:33
After viewing this lecture, you will be able to calculate the max and min values of a field for a group, with either SQL or dplyr.
count (SQL) and n (dplyr)3:40
After viewing this lecture, you will be able to count the number of records in a group, with either SQL or dplyr.
WHERE vs. HAVING9:50
After viewing this lecture, you will be able to filter before and after grouping, in either SQL or dplyr.
Batting Average and Mutate (dplyr)7:10
After viewing this lecture, you will be able to calculate batting averages for individual records with SQL or with dplyr. You will also learn how to use the mutate verb in dplyr.
Career Batting Average10:54
In this lecture, we calculate career batting averages with both SQL and dplyr.

Inner Joins with Access SQL7:18
After viewing this lecture, you will be able to perform INNER JOINS with SQL in Access.
Inner Joins with dplyr6:27
After viewing this lecture, you will be able to perform INNER JOINS with dplyr in R.
A Query with an INNER JOIN8:36
In this video, we practice with INNER JOINS.
Joining on more than one field with SQL in Access6:22
After viewing this lecture, you will be able to perform an INNER JOIN with SQL in Access on more than one field.
Joining on more than one field with dplyr in R7:00
After viewing this lecture, you will be able to perform an INNER JOIN with dplyr in R on more than one field.
Joining three tables with SQL in Access5:07
After viewing this lecture, you will be able to join three tables with SQL in Access.
Joining three tables with dplyr in R4:31
After viewing this lecture, you will be able to join three tables with dplyr in R.
Grouping and joining with SQL in Access4:07
After viewing this lecture, you will be able to write queries where you are both grouping records and joining tables with SQL in Access.
Grouping and joining with dplyr in R6:09
After viewing this lecture, you will be able to write queries where you are both grouping records and joining tables with dplyr in R.

Problem #1 with SQL6:50
This is our first practice problem, solved with SQL.
Problem #1 with dplyr7:23
This is our first practice problem, solved with dplyr.
Problem #2 with Access5:38
This is our second practice problem, solved with SQL.
Problem #2 with dplyr4:10
This is our second practice problem, solved with dplyr.
Reading Other Data into R7:21
After viewing this lecture, you will be able to read csv files into R and query them with dplyr.

Requirements

Students will need to have R and RStudio installed on their own computers. (I will cover how to do this.)
It will be best if students also have Microsoft Access, but this is not absolutely necessary.

Description

In this course, we explain the relationship between SQL and the R package dplyr. I will show you how to query a baseball database with SQL in Microsoft Access and then show you how to do exactly the same thing with dplyr in R. We will begin with simple queries, progress to aggregation and grouping, and finish with queries involving joins. By the end of the course, you should be able to use dplyr to explore your own data sets.

At a relaxed pace, it should take about three weeks to complete the course. The course is for beginners in SQL, R, and dplyr. You also do not have to understand very much about baseball. We will be using the Lahman Baseball Database, R, dplyr, and Microsoft Access. I will show you how to install everything.

Who this course is for:

This course is for beginners who would like to learn about SQL and/or dplyr.
This course is for beginners interested in baseball analytics.
This course is NOT for those with extensive knowledge of both SQL and dplyr.

Baseball Database Queries with SQL and dplyr

What you'll learn

Explore related topics

Course content

Getting Set Up4 lectures • 15min

Basic Queries4 lectures • 27min

Grouping and Aggregation8 lectures • 53min

Inner Joins9 lectures • 56min

Practice Problems5 lectures • 31min

Requirements

Description

Who this course is for: