SQL

Resize the browser window to see the effect.




SQL and R - Introduction to Database Queries

taught by Ben Baumer

Aim of Course:

Most data are stored in relational database management systems, which are organized as a number of related tables.  To perform common statistical analyses, the data must typically be merged into a single dataframe.  

The SQL (structured query language) programming language is often used to pull data from the various tables in a database and to assemble the data in a format amenable to statistical analysis or review.  SQL can also be used for basic calculations, but it’s not meant for heavy-duty statistical programming. 

The purpose of this online course, "SQL and R Introduction to Database Queries" is to teach you how to extract data from a relational database using SQL, and then merge the data into a single file in R, so that you can perform statistical operations.   The focus is on structuring queries to extract structured data (not on building databases or methods of handling big data).

An example you might face at work is: you’d like to see the effect of different variations of an online ad on click-through rates (CTR's).  All the various ad names are stored in one table, while daily CTR's for each ad are stored another table.  You'll need to total CTR's by ad and merge that data with the ads table.   your final goal may be to correlate ad characteristics with total CTR's.

This is an introductory course that will help you get started on dealing with problems like the above. You will learn how to think “like” a relational database, so that you can manipulate matrixes and vectors of data using SQL queries.   Then you will learn how to bring data from your database and organize it into a flatfile in R. This course will teach you how to prepare data so that you can perform anything from basic statistical calculations (e.g. averages, tabulations, linear regressions, test of two means) to machine learning algorithms on your data.  By the end of the 4 weeks, you should be able to visualize how you need to manipulate a dataset in order to perform a desired calculation or answer a particular question. 

This course may be taken individually (one-off) or as part of a certificate program.

Course Program:

WEEK 1: Thinking Ahead, How to Organize your Data?

  • Discussion and visuals of data models how data are stored in Postgres(SQL), i.e. entity relationship schemes, vs. R, flat files or data frames.
  • Unique identifiers, and primary vs. foreign keys
  • Questions we’ll ask: Will I need all the data (panel), or should I select certain segments of the data?
  • Restoring a database in Postgres

WEEK 2:  Basic SQL Procedures and Functions

  • Using commands: Select and Where, Like, Order By, And, Or
  • Using functions: Count, Avg, Sum
  • Using: group-by and having
  • Subqueries: In
  • Copying tables to csv from SQL

WEEK 3:  Bringing SQL into R

  • Joins (Inner, Outer, and multiple joins) in SQL
  • Using the ODBC driver to run SQL queries in R.
  • Reading in csv files

WEEK 4:  Working in R

Plyr function in R for grouping data

Performing a few basic statistical calculations in R

  • Joins vs merges?
  • Subsetting data and merging it back in
  • Data summary statistics
  • Basic Graph with Ggplot

And if there’s time, we’ll compare some basic calculations (counts, sums, averages) in SQL and R, so you can check your work in R on the raw data in Postgres.


HOMEWORK:

Homework in this course consists of short answer questions to test concepts and guided data wrangling projects using SQL and R.

In addition to assigned readings, this course also has practice exercises, and supplemental readings available online.


SQL and R - Introduction to Database Queries

Who Should Take This Course:

This course is meant for beginners in data science who need to learn how to extract data from their relational databases.  It can also be a good kickstarter course for individuals who have fluency in SPSS, SAS, or STATA. Such data scientists might not have had to work with relational databases directly before, or they may not be familiar with the syntax and logic of a more object oriented language like R.

Level:

Introductory / Intermediate

Prerequisite:

Familiarity with Excel; basic familiarity with R

Basic knowledge of vectors and matrices

Note:  Statistics.com's introductory courses for R do not need to be taken prior to this course, but if you are planning to take them anyway, you may gain more from this course by taking the Introduction to R courses first.


Organization of the Course:

This course takes place online at the Institute for 4 weeks. During each course week, you participate at times of your own choosing - there are no set times when you must be online. Course participants will be given access to a private discussion board. In class discussions led by the instructor, you can post questions, seek clarification, and interact with your fellow students and the instructor.

At the beginning of each week, you receive the relevant material, in addition to answers to exercises from the previous session. During the week, you are expected to go over the course materials, work through exercises, and submit answers. Discussion among participants is encouraged. The instructor will provide answers and comments, and at the end of the week, you will receive individual feedback on your homework answers.

Time Requirement:
About 15 hours per week, at times of  your choosing.

Credit:
Students come to the Institute for a variety of reasons. As you begin the course, you will be asked to specify your category:

  1. You may be interested only in learning the material presented, and not be concerned with grades or a record of completion.
  2. You may be enrolled in PASS (Programs in Analytics and Statistical Studies) that requires demonstration of proficiency in the subject, in which case your work will be assessed for a grade.
  3. You may require a "Record of Course Completion," along with professional development credit in the form of Continuing Education Units (CEU's).  For those successfully completing the course,  CEU's and a record of course completion will be issued by The Institute, upon request.
SQL and R - Introduction to Database Queries has been evaluated by the American Council on Education (ACE) and is recommended for the upper-division baccalaureate degree category, 3 semester hours in computer science or programming. Note: The decision to accept specific credit recommendations is up to each institution. More info here.
This course is also recognized by the Institute for Operations Research and the Management Sciences (INFORMS) as helpful preparation for the Certified Analytics Professional (CAP®) exam, and can help CAP®analysts accrue Professional Development Units to maintain their certification .


Course Text:

Step by step SQL and R instructions will be supplied in the course.  The required text for this course is  SQL in a Nutshell . If you’d like to have a reference book for R, take a look at  R in a Nutshell .

If you already have some statistical programming experience and are looking for a quick entry into R based on that experience, you may want to try R for STATA users or R for SAS and SPSS users.

Software:

We will use

1.  Postgres SQL:  http://www.postgresql.org/download/

2.  R:

Mac (tar.gz file): http://cran.r-project.org/bin/macosx/

Windows:  http://cran.r-project.org/bin/windows/base/

3.  R Studio (an editing and development environment for R): http://www.rstudio.com/ide/download/

Please start the process of getting these software components in place at least one week before the class start date.


SQL and R - Introduction to Database Queries

Instructor(s):
Dates:
March 17, 2017 to April 14, 2017August 04, 2017 to September 01, 2017November 10, 2017 to December 08, 2017March 16, 2018 to April 13, 2018August 03, 2018 to August 31, 2018November 09, 2018 to December 07, 2018

Course Fee: $549