O'Reilly logo
live online training icon Live Online training

Bridging the Gap from Excel to SQL

Foundational SQL Tables, Formulas, and Functions

Benn Stancil

Excel is one of the most popular and useful business applications ever conceived. But in the era of the cloud-based data warehouse, Excel's limitations have become painfully clear. Excel is notorious for its struggles with large datasets and heavy-duty analysis. But more importantly, it's hard to trust that the data in a static Excel spreadsheet is up-to-date and accurate. For these reasons and more, organizations that used to run everything out of Excel are increasingly turning to SQL to manage and analyze their data.

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

  • Using the free functionality of Mode Studio, learn how relational databases can make life easier and eliminate many of the pains of Excel
  • Learn how SQL can deliver the same functionality of Excel, without many of the headaches
  • Use SQL to take your analysis beyond what Excel can do

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

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

Accessing data (30 minutes)

  • Instructor will explain basics of databases and SQL, including differences between Excel sheets and database tables.
  • Participants will learn how to access data in SQL and identify characteristics of a table.

Working with data (30 minutes)

  • Instructor will explain the fundamentals of working with data in SQL, including syntax and contrast with Excel formulas.
  • Participants will begin to pull data in SQL and work with queries.
  • Break: 10 mins

SELECT, FROM, Math (30 minutes)

  • Instructor will guide an exercise introducing how to access data and perform basic calculations in SQL
  • Participants will begin accessing and working with data in SQL

IF, WHERE, Filtering (30 minutes)

  • Instructor will guide an exercise illustrating overlap of filtering functions in SQL and Excel
  • Participants will learn how to access only the data they want
  • Break: 10 mins

Aggregation (SUM/COUNT/AVG), Pivot Tables (30 minutes) - Instructor will guide an exercise on how to use SQL functionality similar and beyond - Participants will begin to explore how SQL can take their analysis to the next level.

Subqueries (30 minutes) - Instructor will introduce concept of subqueries and guide an exercise in their use. - Participants will learn how subqueries can enable them to perform multi-step operations

Wrap-up, final Q&A (10 minutes)