Baseball Database Queries with SQL and dplyr
4.6 (181 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
4,670 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Baseball Database Queries with SQL and dplyr to your Wishlist.

Add to Wishlist

Baseball Database Queries with SQL and dplyr

Explaining the relationship between SQL and dplyr with baseball examples.
4.6 (181 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
4,670 students enrolled
Created by Charles Redmond
Last updated 6/2015
English
Price: Free
Includes:
  • 3 hours on-demand video
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I 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
View Curriculum
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 is the target audience?
  • 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.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
30 Lectures
03:02:22
+
Getting Set Up
4 Lectures 15:10

This is our course intro.

Introduction
01:05

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.

Access Set-up
05:55

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.

RStudio Set-up
07:10

After viewing this lecture, you will be able to install the Rcpp package.

The Rcpp Package
01:00
+
Basic Queries
4 Lectures 27:13

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.

SELECT (SQL) and select (dplyr)
09:04

After viewing this lecture, you will be able to use SQL in Access and dplyr in R to order records by a particular column.

ORDER BY (SQL) and arrange (dplyr)
04:58

After viewing this lecture, you will be able to use SQL in Access and dplyr in R to filter records according to certain criteria.

WHERE (SQL) and filter (dplyr)
07:00

After viewing this lecture, you will be able to employ more complicated filters with both SQL and dplyr.

AND and OR
06:11
+
Grouping and Aggregation
8 Lectures 53:00

After viewing this lecture, you will be able to group records and sum a particular field for a grouping with SQL in Access.

Grouping and Sum in Access SQL
03:49

After viewing this lecture, you will be able to group records and sum a particular field for a group with

dplyr in R.

Grouping, Summarize, and Sum in dplyr
05:33

After viewing this lecture, you will be able to use the avg (SQL) and mean (dplyr) aggregate functions.

Averaging in Access SQL and dplyr
06:31

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.

max and min
05:33

After viewing this lecture, you will be able to count the number of records in a group, with either SQL or dplyr.

count (SQL) and n (dplyr)
03:40

After viewing this lecture, you will be able to filter before and after grouping, in either SQL or dplyr.

WHERE vs. HAVING
09:50

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.
Batting Average and Mutate (dplyr)
07:10

In this lecture, we calculate career batting averages with both SQL and dplyr.

Career Batting Average
10:54
+
Inner Joins
9 Lectures 55:37

After viewing this lecture, you will be able to perform INNER JOINS with SQL in Access.

Inner Joins with Access SQL
07:18

After viewing this lecture, you will be able to perform INNER JOINS with dplyr in R.

Inner Joins with dplyr
06:27

In this video, we practice with INNER JOINS.

A Query with an INNER JOIN
08:36

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 SQL in Access
06:22

After viewing this lecture, you will be able to perform an INNER JOIN with dplyr in R on more than one field.

Joining on more than one field with dplyr in R
07:00

After viewing this lecture, you will be able to join three tables with SQL in Access.

Joining three tables with SQL in Access
05:07

After viewing this lecture, you will be able to join three tables with dplyr in R.

Joining three tables with dplyr in R
04:31

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 SQL in Access
04:07

After viewing this lecture, you will be able to write queries where you are both grouping records and joining tables with dplyr in R.

Grouping and joining with dplyr in R
06:09
+
Practice Problems
5 Lectures 31:22

This is our first practice problem, solved with SQL.

Problem #1 with SQL
06:50

This is our first practice problem, solved with dplyr.

Problem #1 with dplyr
07:23

This is our second practice problem, solved with SQL.

Problem #2 with Access
05:38

This is our second practice problem, solved with dplyr.

Problem #2 with dplyr
04:10

After viewing this lecture, you will be able to read csv files into R and query them with dplyr.

Reading Other Data into R
07:21
About the Instructor
Charles Redmond
4.5 Average rating
1,690 Reviews
22,070 Students
7 Courses
Professor at Mercyhurst University

Dr. Charles Redmond is a professor in the Tom Ridge School of Intelligence Studies and Information Science at Mercyhurst University. He has been a member of the Department of Mathematics and Computer Systems at Mercyhurst for 21 years and has recently completed a term as chair of the department. Dr. Redmond received his PhD in mathematics from Lehigh University in 1993 and has published in the Annals of Applied Probability, the Journal of Stochastic Processes and Their Applications, Mathematics Magazine, the College Mathematics Journal, and Mathematics Teacher. In his spare time he enjoys making music and computer generated art, reading, and owning a Clumber Spaniel.