O'Reilly logo
live online training icon Live Online training

SQL Data Wrangling for Excel Users

Working with multiple tables, joins, and data wrangling and cleaning

Benn Stancil

Attendees in this course will learn how to connect data between SQL tables. Then, they will be introduced to the concept of a Join and they will learn how SQL lets you work with multiple tables at once. They will explore each type of join and how they work. Finally, participants will put these concepts to work as they learn to programmatically clean and fix data to make it easier to work with in the future.

This course assumes working knowledge of the following: - SQL Tables & Formulas - SELECT/FROM/Math - IF/WHERE, Filtering - Aggregation - Subqueries

We recommend taking the first course in this series, “Bridging the Gap from Excel to SQL”, within the O'Reilly Online Learning platform, if you require that training.

What you'll learn-and how you can apply it

  • Using the free functionality of Mode Studio, you will learn how to connect data between SQL tables
  • Learn how SQL lets you work with multiple tables at once
  • Explore each type of join and how they work
  • Learn how to work with messy data in SQL
  • Learn to programmatically clean and fix data to make it easier to work with in the future
  • Learn how to apply these concepts in daily work

This training course is for you because...

  • You typically have to ask someone else to pull data for your analysis, and you want to be able to do so yourself.
  • You're frustrated by the inefficiency or reliability of Excel, or want to work with larger data sets than Excel allows.
  • You want to conduct more sophisticated analysis or visualization than Excel allows.
  • You want to advance your career.

Prerequisites

  • Proficiency in Excel and familiarity with the following Excel concepts:
    • Columns, Rows
    • Formulas
    • Filtering
    • Pivot Tables
    • VLOOKUP
  • Access to a web browser and a high-speed internet connection during the presentation

Course Set-up:

In order to use the same datasets that we will use in our examples, we recommend creating an account in Mode Studio. Everything covered in this lesson is included in the free functionality of Mode Studio. If you have another SQL editor, the concepts still apply and you can follow along.

Recommended Preparation:

In addition to the set-up instructions and required preparation resources, include any additional materials or downloads that attendees might want prior to the course. Consider these optional but relevant resources, ideally books and videos within the Safari platform. Include Safari links.

About your instructor

  • Benn Stancil is a co­founder and Chief Analyst at Mode, a company building collaborative tools for data scientists and analysts. Benn is responsible for overseeing Mode's internal analytics efforts, and is also an active contributor to the data science community. In addition, Benn provides strategic oversight and guidance to Mode's product direction as a member of the product leadership team.

Schedule

The timeframes are only estimates and may vary according to how the class is progressing

Review of first SLT, Bridging the Gap from Excel to SQL (20 minutes)

  • Instructor will provide a brief review of the following concepts:
  • SQL Tables & Formulas
  • SELECT/FROM/Math
  • IF/WHERE, Filtering
  • Aggregation
  • Subqueries
  • Participants will catch up on concepts explained in the first course in this series, “Bridging the Gap from Excel to SQL”
  • 5 minutes Q&A/break

Multiple Tables (10 minutes)

  • Instructor will introduce concepts required to understand how SQL tables relate to each other.
  • Participants will begin working on connecting data between SQL tables.
  • Joins

Introduction to Joins (30 minutes)

  • Instructor will introduce the concept of a Join and provide a guided exploration of their functionality.
  • Participants will learn how SQL lets you work with multiple tables at once.
  • 5 minutes Q&A/break

Types of Joins (30 minutes)

  • Instructor will explain the different types of joins and why each might be used.
  • Participants will explore each type of join and how they work.

Break, unstructured exploration/Q&A (10 minutes)

Putting the concepts to work: Data Wrangling (30 minutes)

  • Instructor will explain how to use SQL to wrangle disparate data sets and fit them together for analysis.
  • Participants will learn how to work with messy data in SQL.
  • 5 minutes Q&A/break

Data Cleaning with String Functions (30 minutes)

  • Instructor will explain what string functions are and how they work.
  • Participants will learn to programmatically clean and fix data to make it easier to work with in the future
  • 5 minutes Q&A/break

What's next? (10 minutes)

  • Instructor will suggest examples of how participants can apply these concepts in their daily work
  • Participants will learn where they can go from here for further learning

Final Q&A (10 minutes)