Learning guide: SQL for Excel users, half-day workshop

I like to call SQL the cool old uncle of analytics because, no matter what you are trying to do with your data, SQL has probably done it before.

For as impressive as the new Microsoft stack is, it’s important to remember that Microsoft didn’t invent things like a left outer join or one-to-one relationship. These concepts are old relational data modeling, and SQL has classically been the language to interact with relational database management systems.

From this in Excel to that same thing in SQL

We tend to do the same common data tasks regardless of program used: it could be sorting, filtering, or creating a calculated field. You may perform these all the time in Excel, and you will similarly do them all the time in SQL.

That’s why in this workshop I “pivot” from common Excel tasks into their corresponding SQL tasks. One of the best ways to learn something new is to relate it to what you already know, and this approach really accelerates what can be learned in a short amount of time. This workshop covers the major read operations in SQL. By the end of the workshop, users are able to query information from multiple tables in a database.

SQL is essential knowledge for anyone looking to improve their data chops. This workshop accelerates that knowledge acqusition by relating SQL skills right back to Excel skills.

Lesson 1: SQL and Excel for data analytics

Objective: Student can visually inspect a database with SQLiteStudio

Description:

• What is a database and when would you use it?
• Exploring the SQLiteStudio interface

Exercises: Practice exploring a second database

Assets needed: Baseball database

Time: 30 minutes

Lesson 2: From Excel tables to SQL SELECT

Objective: Student select some or all fields from various tables of a database

Description:

• The grammar of SQL
• Querying fields
• Functions
• Aliases

Exercises: Drills

Assets needed: Baseball database

Time: 35 minutes

Lesson 3: From Excel sort & filter to SQL WHERE and ORDER BY

Objective: Student can sort and filter a query’s results

Description:

• Sorting and filtering
• How NULLs work
• Limiting a query’s results

Exercises: Drills

Assets needed: Baseball database

Time: 30 minutes

Lesson 4: From Excel PivotTable to SQL aggregation

Objective: Student can aggregate and summarize query results

Description:

• Counting records
• Counting and listing distinct records
• Grouping and aggregating records
• Filtering aggregated records
• Using all read clauses

Exercises: Drills

Assets needed: Baseball database

Time: 35 minutes

Lesson 5: From VLOOKUP() to JOIN

Objective: Student can join multiple tables from the same database

Description:

• Relational database modeling
• Left outer joins
• Inner joins
• Set operators

Exercises: Drills

Assets needed: Baseball database

Time: 45 minutes

Lesson 6: From “That’s hard in Excel” to “That’s easy in SQL!”

Objective: Student can conduct intermediate data analysis

Description:

• Subqueries
• Case operators
• Common table expressions

Exercises: Drills

Assets needed: Baseball database

Time: 45 minutes

This learning guide is a part of my resource library. For exclusive free access, subscribe below.