Advanced SQL Queries in 90 Minutes—with Interactivity
Published by O'Reilly Media, Inc.
Beyond the basics
Course outcomes
- Identify when an advanced querying function can be used
- Read and understand queries that use advanced functions
- Understand the following advanced querying concepts and how to use them: window functions, subqueries and common table expressions, case statements, string functions and date/time functions
Course description
Join expert Alice Zhao to take the next step beyond simple queries and explore commonly used advanced SQL querying concepts. You'll learn how to utilize window functions, subqueries and common table expressions, case statements, string functions, and date/time functions for data querying and management needs beyond the basics.
Hands-on interactive learning
All exercises are provided as O'Reilly interactive labs or sandboxes—complete development environments that are preconfigured with everything you need. So you can explore, experiment, and tinker without fear of breaking anything. There's nothing to install or set up; just click a link and get started! And you can revisit them anytime after class ends to practice and refine your skills.
What you’ll learn and how you can apply it
- Understand the different parts of a window function
- Identify when to use subqueries and common table expressions
- Utilize CASE statements along with the SUM function
- Incorporate string functions or date/time functions into a query
This live event is for you because...
- You’re a data analyst, programmer, or data scientist.
- You can read or write simple SQL queries but want to move on to more advanced SQL queries.
Prerequisites
- Familiarity with SQL queries (SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY)
- Familiarity with SQL JOINs (LEFT, INNER, and OUTER)
Recommended preparation:
- Read “SQL Crash Course” and “Querying Basics” (chapters 1 and 4 in SQL Pocket Guide, fourth edition)
- Take Writing Better SQL in 90 Minutes (live online course with Alice Zhao)
- Take SQL Fundamentals for Data (live online course with Thomas Nield)
Recommended follow-up:
- Read SQL Cookbook, fourth edition (book)
- Read SQL for Data Analysis (book)
Schedule
The time frames are only estimates and may vary according to how the class is progressing.
Introduction (5 minutes)
- Presentation: Refresher on SELECT statements and JOINs
- Group discussion: Advanced querying concepts you’re most curious about
Window functions (30 minutes)
- Presentation: Breaking down a window function and its various applications
- Interactive lab: Calculate the moving average using a window function
- Q&A
Subqueries and common table expressions (25 minutes)
- Presentation: How subqueries and CTEs work
- Demo: Using a subquery and a CTE
- Q&A
- Break
Case statements (10 minutes)
- Presentation: How a case statement works
- Interactive lab: Perform a SUMIF calculation using CASE and SUM together
- Q&A
String and date/time functions (10 minutes)
- Presentation: String functions including concatenate and regular expressions; date/time functions including extract and interval calculations
- Q&A
Wrap-up and Q&A (10 minutes)
- Presentation: Other advanced querying concepts
Your Instructor
Alice Zhao
Alice Zhao is a data scientist who’s passionate about teaching and making complex things easy to understand. She’s the author of the SQL Pocket Guide, fourth edition (O’Reilly) and has taught numerous courses in SQL, Python, and R as a senior data scientist at Metis and as a cofounder of Best Fit Analytics. She writes about analytics and pop culture on her blog, A Dash of Data. Her work has been featured in Huffington Post, Thrillist, and Working Mother. She has her MS in analytics and BS in electrical engineering, both from Northwestern University.