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:
Options for Credit and Recognition:
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:

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:
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