Puzzles Archives

Solving puzzles is a great way to learn SQL. Nothing beats practicing what you’ve learned.

Once you have figured out the puzzle, post your answer in the comments so we all can learn from one another.

We also discuss puzzle and more in Essential SQL Learning Group on Facebook. Be sure to find us there!

Here are two of my favorite puzzles you should try to solve:

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters.

You can get started using these free tools using my guide Getting Started Using SQL Server

SQL Server Hierarchical Query using the hierarchyid type

In this SQL minute we’ll look at how to create a SQL server hierarchical query using the hierarchyid data type. This is a two-part article. In part one you were introduced to the hierarchyid type. In this article we dig deeper into the hierarchyid functions. If you’re studying for the 70-761 exam, you’ll find there aren’t […]

Use hierarchyid to query Hierarchical data

In this SQL minute we’ll look at how you can use hierarchyid to query hierarchy data. This is a two-part article. In this article we look at how to do one query to get portion of the hierarchy. Next week, we will look to see how to query the entire tree. If you’re studying for […]

How to use SQL Union in Set Operations

In this puzzle, we’re going to learn how to write a SQL UNION without using Set Operations. Set operations allow us to compare rows from two or more tables to arrive at a result. For several classes of problems, it is much easier to use a set operation, than join data. But can we […]

SQL Server and Set Operations

In this puzzle, we’re going to about SQL set operations. Set operations allow us to compare rows from two or more tables to arrive at a result. For several classes of problems, is is much easier to use a set operation, than join data. Solving puzzles is a great way to learn SQL. Nothing beats […]

Data Modeling Principles in Action

In this puzzle, we’re going to learn how to do some basic data modeling. Many of you have expressed an interest in learning more about data modeling and database design. I figure we could start with a simple case study and let it evolve from there. Solving puzzles is a great way to learn SQL. […]

Joins versus Subqueries SQL Puzzle

In this puzzle, we’re going to learn about joins versus subqueries. In many queries you can substitute joins and subqueries. Yet, since each has their strengths, it isn’t wise to do so. Once you have worked through this puzzle you see some of the benefits and disadvantages of joins versus subqueries. Solving puzzles is a […]

What is the difference between a subquery and inner join?

In this puzzle, we’re going to learn how to rewrite a subquery using inner joins. Knowing about a subquery versus inner join can help you with interview questions and performance issues. Though subqueries have unique abilities, there are times when it is better to use other SQL constructs such as joins. By reading this article […]

Use SQL to Calculate Median Values.

In this puzzle we’re going to learn how to find the person whose birthday, among others, is in the middle. Knowing how to calculate the median value is a good skill to have. As you start to explore business intelligence you’ll come across similar problems to solve. Also, be sure to check out the bonus […]

Learn to use the Data Dictionary in SQL Server

In this puzzle, we’re going to learn how to query the data dictionary using SQL Server. Knowing how to query the data dictionary is good to know. There are many questions you can answer about your database through the data dictionary. For instance, do you know how many of your table don’t have a primary […]

What is a Dynamic Pivot Table?

In this puzzle, we’re going to learn how to create a dynamic pivot table using SQL Server. A dynamic pivot table is a great way to summarize data. And given that Business Intelligence is a hot topic, knowing how to create one is key. By reading this article you’ll learn to create a dynamic pivot […]

Use SQL Server to Create a Cross Tab Query

In this puzzle, we’re going to learn how to create a cross tab query using SQL Server. Cross tabs are a great way to summarize data. And given that Business Intelligence is a hot topic, knowing how to create one is key. We’ll learn to create a cross tab query. Before you read the entire […]

Learn how to Work Joins and Messy Data

In this puzzle, we’re going to learn how to join dirty data using SQL Server. Many times you’re forced to use someone else’s data. Since it is their system and data, not yours, you can’t always fix the inconsistently formatted data before it is in the database. So how can you use it? Do this […]

Use Subquery with Bill of Materials

In this puzzle, we’re going to learn how to work with the AdventureWorks Bill of Materials table. Companies use a BOM (Bill of Materials) to itemize the products that are used or contained in another product. Solving puzzles is a great way to learn SQL. Nothing beats practicing what you’ve learned. Once you have figured […]

How to Create A Bar Chart Using SQL Server

In this puzzle, we’re going to learn how to create a bar chart using SQL Server. Sometimes it’s fun to see what you can do with the humble SELECT statement. Today I figured it would be fun to see if I could create an “old fashioned” bar chart, much like we used to do with […]

How to Find a Gap in a Sequence using SQL SERVER

In this puzzle, we’re going to learn how to find a gap in a sequence using SQL Server. I was recently reviewing my bank statement and noticed they flagged the start of a missing check number with ***’s. I figured with would be a great example for a SQL puzzle as there are many ways […]

SQL Puzzle: How to Calculate Moving Averages

In this puzzle, we’re going to learn how to calculate moving averages by working through a hypothetical stock market example. Solving puzzles is a great way to learn SQL. Nothing beats practicing what you’ve learned. Once you have figured out the puzzle, post you answer in the comments so we all can learn from one […]

Rank Data with SQL

In this puzzle, we’re going to work through a problem to identify the top and bottom 25 percent to rank student scores. Solving puzzles is a great way to learn SQL. Nothing beats practicing what you’ve learned. SQL Puzzle Question In today’s puzzle assume you’ve been asked by the head of the Mathematics department to […]