O'Reilly logo
live online training icon Live Online training

SQL-Powered Excel for Business Analytics

Data retrieval and analysis from spreadsheet to database

George Mount

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 often have to request IT staff to perform these data retrievals. By relating the foundations of SQL to everyday Excel tasks, professionals will be empowered to perform their own data analysis and exploration on 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
  • Schemas, primary keys and foreign keys: what makes relational databases “relational.”

And you’ll be able to:

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

This training course is for you because...

  • You’re an analyst, project manager or other professional working frequently 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

  • Basic to intermediate Excel knowledge: sorting and filtering, IF() statements, SUMIF(), COUNTIF(), possibly PivotTables.

Recommended preparation:

Recommended follow-up:

About your instructor

  • George works as an independent analyst and data analytics educator with the goal to help clients manage their data so they think more creatively. He serves as a technical expert and lead curriculum developer for Thinkful’s data analytics program and is the instructor of the DataCamp course “Survey and Measure Development in R.” George blogs about data, innovation, and career development at georgejmount.com. He holds a master’s degree in information systems with a certificate of achievement in quantitative methods from Case Western Reserve University.

Schedule

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

Excel and SQL for data analytics (25 minutes)

  • Presentation: What is a database and when would you use it?
    • Provide landscape of SQL and relational databases
  • Poll:
  • Presentation: Exploring SQLiteStudio
    • Brief tour of using the interface to visually inspect databases, tables.
  • Exercise: Practice visually exploring a second database.
  • Q&A

From Excel tables to SQL SELECT (25 minutes)

  • Presentation: SQL’s “rules of the road”
    • Compare object types and table names vis a vis Excel tables.
  • Presentation: Querying fields: your first SQL SELECT FROM
    • Practice selecting all or certain fields from various tables of a database
    • Using functions and aliasing on SELECT fields
  • Exercise: Drills on SQL SELECT FROM
  • Q&A
  • Break (5 minutes)

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

  • Presentation: Sorting your query’s results
    • ORDER BY, COUNT(*), aliasing, NULLs
  • Presentation: Filtering your query’s results
    • WHERE, LIMIT
  • Exercise: Drills on WHERE and ORDER BY
  • Q&A

From Excel PivotTables to SQL aggregation (30 minutes)

  • Presentation: Counting, grouping and listing distinct records
  • Presentation: Aggregate functions and GROUP BY
  • Exercise: Practice aggregation
  • Presentation: HAVING and clause ordering
  • Exercise (brief): Have students combine all commands for the first time.
  • Break (5 minutes)

From VLOOKUP() to SQL joins (30 minutes)

  • Presentation: Introduction to working with multiple tables
  • Presentation: INNER and LEFT OUTER JOINs
  • Presentation: JOINs, aggregation and UNION
  • Exercise: Drills on JOINs
  • Q&A

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

  • Presentation: CASE, subqueries and CTEs
    • Preview on common data tasks that are more difficult or time-consuming to do in Excel than SQL.
  • Presentation: SQL, the Prequel
    • Conclusion and recommendations for extended learning
  • Q&A