SQL - Introduction to Database Queries
Taught by Dr. Ben Baumer

SQL - Introduction to Database Queries

taught by Ben Baumer

 

 
Close Popup

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 - Introduction to Database Queries" is to teach you how to extract data from a relational database using SQL, 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 matrices and vectors of data using SQL queries.   Then you will learn how to bring data from your database and organize it into a flatfile. 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.

After completing this course you should be able to:

  • Understand the relational database file model (as contrasted with the flat file)
  • Load MySQL and read in .csv data
  • Query databases to obtained desired data
  • Incorporate functions in queries
  • Add filtering to queries
  • Join tables
  • Perform subqueries
  • Understand the principles of efficient SQL databases
This course may be taken individually (one-off) or as part of a certificate program.
Course Program:

WEEK 1: Getting Started

  • Flat files (spreadsheet model)
  • Relational databases (RDBMS) [note: compare to dataframe in R]
  • MySQL
  • Read in .csv data
  • The airline data https://github.com/beanumber/airlines
  • Preview of SQL commands 
  • Simple queries with the airline data - SELECT ... FROM ... LIMIT…

WEEK 2:  Basic SQL Procedures and Functions

  • Functions in queries
  • Alias for column header
  • Operations on tables vs. operations on results
  • WHERE
  • GROUP BY and ORDER BY
  • HAVING
  • Wrangling dates - MySQL documentation str_to_date

WEEK 3:  Joining Tables and Subqueries 

  • JOIN (left, right, cross)
  • Table Aliases
  • Subqueries
 

WEEK 4:  Efficient SQL Databases

  • Indices
  • EXPLAIN
  • KEYS (primary, unique, foreign) 
  • UPDATE
  • INSERT
  • CREATE TABLE
  • ALTER TABLE

HOMEWORK:

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

In addition to assigned readings, this course also has practice exercises.

SQL - 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.  

Level:
Introductory / Intermediate
Prerequisite:

Familiarity with Excel, basic knowledge of vectors and matrices

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.

Options for Credit and Recognition:
Students come to the Institute for a variety of reasons. As you begin the course, you will be asked to specify your category:
  1. No credit - You may be interested only in learning the material presented, and not be concerned with grades or a record of completion.
  2. Certificate - 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. CEUs and/or proof of completion - 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.
  4. Other options - Statistics.com Specializations, INFORMS CAP recognition, and academic (college) credit are available for some Statistics.com courses
College credit:
SQL - 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.

INFORMS CAP:
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 instructions will be supplied in the course.  The required text for this course is Modern Data Science with R.   Note:  The sections of the book we will use deal almost exclusively with SQL, although they will have occasional references to R.  Lack of knowledge of R will not impede you in this course.

Software:

We will use MySQL

 

Instructor(s):

Dates:

March 15, 2019 to April 12, 2019 July 12, 2019 to August 09, 2019 November 15, 2019 to December 13, 2019 March 13, 2020 to April 10, 2020 July 31, 2020 to August 28, 2020 March 12, 2021 to April 09, 2021 July 30, 2021 to August 27, 2021

SQL - Introduction to Database Queries

Instructor(s):

Dates:
March 15, 2019 to April 12, 2019 July 12, 2019 to August 09, 2019 November 15, 2019 to December 13, 2019 March 13, 2020 to April 10, 2020 July 31, 2020 to August 28, 2020 March 12, 2021 to April 09, 2021 July 30, 2021 to August 27, 2021

Course Fee: $549

Do you meet course prerequisites? What about book & software? (Click here to learn more)

We have flexible policies to transfer to another course, or withdraw if necessary (modest fee applies)

Group rates: Click here to get information on group rates. 

First time student or academic? Click here for an introductory offer on select courses. Academic affiliation?  You may be eligible for a discount at checkout.

Register Now

Add $50 service fee if you require a prior invoice, or if you need to submit a purchase order or voucher, pay by wire transfer or EFT, or refund and reprocess a prior payment. Please use this printed registration form, for these and other special orders.

Courses may fill up at any time and registrations are processed in the order in which they are received. Your registration will be confirmed for the first available course date, unless you specify otherwise.

The Institute for Statistics Education is certified to operate by the State Council of Higher Education in Virginia (SCHEV).

Contact Us
Have a question about a course before you register? Call us. We're here for you. (571) 281-8817 or ourcourses (at) statistics.com

Want to be notified of future courses?

Yes
Student comments