SQL-Powered Excel for Business Analytics
Data retrieval and analysis from spreadsheet to database
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
- Basic to intermediate Excel knowledge: sorting and filtering, IF() statements, SUMIF(), COUNTIF(), possibly PivotTables.
- Read Chapters 1 “Why Learn SQL?” and 2 “Databases” in Getting Started with SQL (book)
- Watch the “Excel Tables” and “Pivot Tables” sections of Big Data Analytics with Excel (video course)
- Download SQLite and SQLite Studio
- No prior experience in programming or databases needed. As an Excel user, you already know more about these topics than you realize!
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.
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
- Presentation: Exploring SQLiteStudio
- Brief tour of using the interface to visually inspect databases, tables.
- Exercise: Practice visually exploring a second database.
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
- 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
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
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