SQL Query Challenges

This one-day hands-on seminar lets you test and challenge your query skills while learning tips, tricks and best practices for quality and performant SQL Server queries.

Note: This is an interactive and hands-on class. The number of puzzles and topics will vary.

• Knowledge of the SQL language, particularly the Microsoft T-SQL dialect, at a basic to intermediate level. (All of our puzzles will use Microsoft SQL Server or Azure SQL Database, or both.)
• Ability to use various tools for running queries against a Microsoft SQL database, either on-premises or cloud-based. These could include SQL Server Management Studio, Visual Studio Code with the SQL extensions or Azure Data Explorer.
• Basic understanding of SQL Server indexes.
• Basic relational database concepts.

Module 1 – Inventorying and Documenting your Database

Rule #1! Know your data! How do you know if you have the right answer? Here we will look at steps to study a database.

• Introduction
• What you can do based on your role: administrator, data reader, cloud user.
• Is there a data dictionary anywhere to be found?
• T-SQL Scripts and tools to document databases, tables, indexes and columns.
• Database statistics and distribution discovery using SQL and other tools like Excel and Power BI.
• Hands on: Puzzle 1, puzzle 2, puzzle 3, …
• Summary

Module 2 – Dealing with problem data

Rule #1! Know your data! Is your data “clean”? Is “KE” really a state? Are “AB-100” and “AB100” the same product? Should nulls be treated as zeros or just ignored?

• Introduction
• Checking for data quality. Missing data, nulls, “outliers”, and other things that mess up your reports!
• Reporting/Custom lookup tables.
• Functions to deal with irregular data.
• Hands on: Puzzle 1, puzzle 2, puzzle 3, …
• Summary

Module 3 – Working with Complex Queries, One Bite at a Time

Should you use temp tables? How about subqueries and Common Table Expressions? Here we will look at taking complex queries and breaking them in to smaller more manageable, and often independently testable, components. And of course… Rule #1! Know your data!

• Introduction
• Are you smarter than the Query Optimizer?
• When to use temp tables or table variables. And what about tempdb?
• When to use subqueries.
• Hands on: Puzzle 1, puzzle 2, puzzle 3, …
• Summary

Module 4 – Dealing with Data Types

Does mixing integers with money impact your query results? How about mixing varchar, varchar(max) and char? And don’t forget Rule #1!

• Introduction
• A brief review of numeric, text, and date data types.
• Dates are evil! What does same date next month mean for January 31^st?
• Hands on: Puzzle 1, puzzle 2, puzzle 3, …
• Summary