SQL – Introduction to Database Queries

SQL – Introduction to Database Queries

This course will teach you how to extract data from a relational database using SQL and merge data into a single file in R so that you can perform statistical operations.

This course will teach you how to extract data from a relational database using SQL and merge data into a single file in R so that you can perform statistical operations.

549
Alert me to upcoming courses
Menu

Overview

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. 

The purpose of this course is to teach you how to extract data from a relational database using SQL so 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). This is an introductory course that will help you think “like” a relational database in order to manipulate matrices and vectors of data using SQL queries.

Learning Outcomes

After completing this course, 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. You should be able to understand relational database files, load MySQL and read in .csv data. You will learn how to write and filter queries, incorporate functions, perform subqueries and how to join tables.

  • 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

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.  

Instructors

ben-baumer

Dr. Ben Baumer

Dr. Ben Baumer is the Director of the Statistical & Data Sciences Program at Smith College, and a Assistant Professor in the Program in Statistical & Data Program. He is a data scientist -- his research and teaching is focused on extracting meaning from data. This interest is informed by both his graduate work, which focused on discrete mathematics and theoretical computer science, and his professional experience, where he served as the Statistical Analyst for the New York Mets from 2004 to 2012. Having extensive experience and training in mathematics, computer science, and statistics, Ben enjoys the interplay between these three fields, particularly in data science and network science. Ben ...
See Instructor Bio

Course Syllabus

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

Class Dates

2019

Jul 12, 2019 to Aug 9, 2019

Nov 15, 2019 to Dec 12, 2019

2020

Mar 13, 2020 to Apr 10, 2020

Jul 31, 2020 to Aug 28, 2020

2021

Mar 12, 2021 to Apr 9, 2021

Jul 30, 2021 to Aug 27, 2021

Send me reminder for next class

Prerequisites

Familiarity with Excel, basic knowledge of vectors and matrices.

What Our Students Say​

This course gave me a solid background on structure of databases and how to use SQL statements to query a database

Evelina Gershfeld
Clarus Marketing Group

I learned a lot of short cuts that I will be grateful to use! I think the instructor and the assistant instructor were great!

Caroline Rives
Mass Mutual Financial Group

Frequently Asked Questions

Can I transfer or withdraw from a course?

We have a flexible transfer and withdrawal policy that recognizes circumstances may arise to prevent you from taking a course as planned. You may transfer or withdraw from a course under certain conditions.

  • Students are entitled to a full refund if a course they are registered for is canceled.
  • You can transfer your tuition to another course at any time prior to the course start date or the drop date, however a transfer is not permitted after the drop date.
  • Withdrawals on or after the first day of class are entitled to a percentage refund of tuition.

Please see this page for more information.

Who are the instructors at the Institute?

The Institute has more than 60 instructors who are recruited based on their expertise in various areas in statistics. Our faculty members are:

  • Authors of well-regarded texts in their area;
  • Advisory board members;
  • Senior faculty; and
  • Educators who have made important contributions to the field of statistics or online education in statistics.

The majority of our instructors have more than five years of teaching experience online at the Institute.

Please visit our faculty page for more information on each instructor at The Institute for Statistics Education.

Please see our knowledge center for more information.

What type of courses does the Institute offer?

The Institute offers approximately 80 courses each year. Topics include basic survey courses for novices, a full sequence of introductory statistics courses, bridge courses to more advanced topics. Our courses cover a range of topics including biostatistics, research statistics, data mining, business analytics, survey statistics, and environmental statistics.

Please see our course search or knowledge center for more information.

Do your courses have for-credit options?

Our courses have several for-credit options:

  • Continuing education units (CEU)
  • College credit through The American Council on Education (ACE CREDIT)
  • Course credits that are transferable to the INFORMS Certified Analytics Professional (CAP®)

Please see our knowledge center for more information.

Is the Institute for Statistics Education certified?

The Institute for Statistics Education is certified to operate by the State Council of Higher Education for Virginia (SCHEV). For more information visit: http://www.schev.edu

Please see our knowledge center for more information.

Visit our knowledge base and learn more.

FAQs + Knowledge Base

Related Courses

Introduction to Python Programming

This course will introduce you to the basics of programming in Python on either Windows or Mac platform.
Topic: Data Science, Using Python | Skill: Introductory | Credit Options: CEU
Class Start Dates: Jan 10, 2020, May 15, 2020, Sep 11, 2020, Jan 15, 2021

Python for Analytics

This course will teach you the basic Python skills and data structures – how to load data from different sources and aggregate it, and how to analyze and visualize it to create high-quality products.
Topic: Analytics, Using Python | Skill: Introductory | Credit Options: ACE, CAP, CEU
Class Start Dates: Mar 13, 2020, Jul 10, 2020, Nov 13, 2020, Mar 12, 2021

Additional Course Information

Organization of 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 Requirements

This is a 4-week course requiring 10-15 hours per week of review and study, at times of your choosing.

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.

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.

Please order a copy of your course textbook prior to course start date.

Software

We will use MySQL programming language.

Software Uses and Descriptions | Available Free Versions
To learn more about the software used in this course, or how to obtain free versions of software used in our courses, please read our knowledge base article “What software is used in courses?” 

Course Fee & Information

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

Transfers and Withdrawals
We have flexible policies to transfer to another course or withdraw if necessary.

Group Rates
Contact us to get information on group rates.

Discounts
Academic affiliation?  In most courses you are eligible for a discount at checkout.

New to Statistics.com?  Click here for a special introductory discount code.  

Invoice or Purchase Order
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.

Options for Credit and Recognition

This course is eligible for the following credit and recognition options:

No Credit
You may take this course without pursuing credit or a record of completion.

Mastery or Certificate Program Credit
If you are enrolled in mastery or certificate program that requires demonstration of proficiency in this subject, your course work may be assessed for a grade.

CEUs and Proof of Completion
If you require a “Record of Course Completion” along with professional development credit in the form of Continuing Education Units (CEU’s), upon successfully completing the course, CEU’s and a record of course completion will be issued by The Institute upon your request.

ACE CREDIT | College Credit
This course has been evaluated by the American Council on Education (ACE) and is recommended for the the upper division baccalaureate degree, 3 semester hours in data mining, statistics, or computer science. Please note that the decision to accept specific credit recommendations is up to the academic institution accepting the credit.

ACE Digital Badge
Courses evaluated by the American Council on Education (ACE) have a digital badge available for successful completion of the course.

INFORMS-CAP
This course is 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.

Supplemental Information

There is no supplemental content for this course.

Miscellaneous

There is no additional information for this course.

Register for This Course​

SQL – Introduction to Database Queries
549
Get Notified

Have a Question About This Course?

Janet Dobbins

Sales and Business Development

Phone

(571) 281-8817

Send Us A Note

We like to hear from you.

Name*

Email*

Phone

Company

Message*

 

Send
Close Menu