Skip to Content
View all events

SQL-powered Excel for business analytics

Published by O'Reilly Media, Inc.

Intermediate to advanced content levelIntermediate to advanced

Retrieve and analyze information from databases

Structured Query Language (SQL) is the primary language used for accessing and managing data in a relational database system. Analysts, project managers, and other professionals want to make informed decisions using large datasets, but without a working knowledge of SQL, they often have to request that IT staff perform these data retrievals.

Expert George Mount walks you through using SQL to query a database to extract the information you need for your analyses. No prior experience in programming or databases is required. As an Excel user, you already know more about these topics than you realize. Over three informative hours, you’ll discover how the foundations of SQL relate to everyday Excel tasks, empowering you to better explore and analyze your own databases.

What you’ll learn and how you can apply it

By the end of this live, hands-on, online course, you’ll understand:

  • The basics of relational databases and SQL’s role
  • The foundational commands used for reading data from one or more tables in a database
  • Concepts such as schemas, primary keys, and foreign keys (i.e., what makes relational databases “relational”)

And you’ll be able to:

  • Use SQL to query a database to extract useful information
  • Explore basic SQL commands and functions
  • Extract data and work with queries to pull the data you need for your analysis

This live event is for you because...

  • You’re an analyst, project manager, or other professional working with data.
  • You work primarily with Excel and see its limitations for data storage and retrieval.
  • You want to work with the larger datasets that are often housed in databases.

Prerequisites

  • A machine with SQLite and SQLiteStudio installed
  • Basic to intermediate Excel knowledge (sorting and filtering, IF() statements, SUMIF(), COUNTIF(), PivotTables, etc.)

Recommended preparation:

Recommended follow-up:

Schedule

The time frames are only estimates and may vary according to how the class is progressing.

Excel and SQL for data analytics (25 minutes)

  • Lecture: What’s a database, and when would you use it?; using the pgAdmin interface to visually inspect databases and tables and generate simple SQL syntax via the GUI
  • Hands-on exercise: Practice visually exploring a second database
  • Q&A

From Excel tables to SQL SELECT (25 minutes)

  • Lecture: SQL’s rules of the road—comparing object types and table names vis-à-vis Excel tables; querying fields—your first SQL SELECT FROM; using functions and aliasing on SELECT fields
  • Hands-on exercise: Use SQL SELECT FROM to complete practice exercises
  • Q&A

Break (5 minutes)

From Excel Sort and Filter to SQL WHERE and ORDER BY (30 minutes)

  • Lecture: Sorting your query’s results—ORDER BY, COUNT(*), aliasing, and NULLs; filtering your query’s results—WHERE and LIMIT
  • Hands-on exercise: Use WHERE and ORDER BY to complete practice exercises
  • Q&A

From Excel PivotTables to SQL aggregation (30 minutes)

  • Lecture: Counting, grouping, and listing distinct records; aggregate functions and GROUP BY; HAVING and clause ordering
  • Hands-on exercise: Use aggregation to complete practice exercises; combine all commands

Break (5 minutes)

From VLOOKUP() to SQL JOINs (30 minutes)

  • Lecture: Introduction to working with multiple tables; RIGHT and OUTER JOINs; JOINs, aggregation and UNION
  • Hands-on exercise: Use JOINs to complete practice exercises
  • Q&A

From “That’s hard in Excel” to “That’s easy in SQL” (30 minutes)

  • Lecture: CASE and subqueries; common data tasks that are more difficult or time-consuming to do in Excel than SQL; What next?
  • Q&A

Your Instructor

  • George Mount

    George Mount is the founder and CEO of Stringfest Analytics, a consulting firm specializing in analytics education and upskilling. He has worked with leading bootcamps, learning platforms and practice organizations to help individuals excel at analytics.

    George regularly blogs and speaks on data analysis, data education and workforce development and is the author of Advancing into Analytics: From Excel to Python and R (O'Reilly Media, 2021) and _Modern Data Analytics in Excel: Using Power Query, Power Pivot and More for Enhanced Data Analytics _(O'Reilly Media, 2024). He is a recipient of the Microsoft Most Valuable Professional (MVP) award for exceptional technical expertise and community advocacy in the field of Excel.

    George holds a bachelor’s degree in economics from Hillsdale College and master’s degrees in finance and information systems from Case Western Reserve University. He resides in Cleveland, Ohio.

    linkedinXlinksearch

Skill covered

SQL