Welcome

Welcome to my SQL puzzle website. I hope you enjoy solving these puzzles as much as I have enjoyed creating them.


Link to my GitHub repository below:

https://github.com/smpetersgithub/AdvancedSQLPuzzles


All solutions to the puzzles are written in Microsoft SQL Server’s T-SQL, but you can easily adapt the solutions to your flavor of SQL. I’m constantly adding new material, so scroll through to find all sorts of database tips, tricks, and insights.

I also welcome any corrections, new tricks and techniques, dead links, misspellings, bugs, and any new puzzles you think would be great for this blog.

Check out my blog for updates

Let’s get on to the puzzles….


Advanced SQL Puzzles

Here are 58 of the most difficult set-based SQL puzzles that I could write, nicely condensed into a PDF file. This is a great starting point to determine your level of expertise with SQL. I highly recommend developers of all skill levels work through this puzzle set; you may be surprised! Link to my GitHub repository below.

Advanced SQL Puzzles


Permutations, Combinations, Sequences, and Random Numbers

This next set of 21 puzzles will test your skills trying to solve for Pascal’s Triangle, the Josephus Problem or performing simulations on the Monty Hall Problem. The below links to my GitHub repository where you will find a PDF of the puzzles, along with the solutions. This also works great as a recipe book for understanding recursion.

Permutations, Combinations, Sequences, and Random Numbers


Python Puzzles

I also started a WordPress page of my Python puzzles. These are probability puzzles where you perform hacker statistics using the law of large numbers.

Python Puzzles


Tips and Tricks

Here are some additional resources for finding new tips, tricks and insights that will help you in your journey. The documentation for the following can be found in the parent folder here.

Creating a Calendar Table

My code for creating a table-valued function for generating a calendar table. Once understood, I hope you find the table-valued function to be more beneficial than the usual static calendar table.

Pivoting Data

If you find the pivot table syntax to be a little frustrating, here you can implement a stored procedure to parameterize the pivot syntax. Very useful if you find yourself having to write a lot of pivot statements.

Data Profiling

A robust SQL script to perform data profiling on a table using dynamic sql. The script can be used to quickly find minimum and maximum values, if NULLs or empty strings appear in the data, etc… I wouldn’t recommend this script on large tables, but its simple to us; just change the table name variable in the script.

Subtracting Two Dates

Anyone who has used SQL Server’s date difference functions know they are severely limiting. Here I have a couple of functions (one table valued and one scalar) that can accurately subtract two dates.

Full Outer Join

Here I have a script that compares two identical tables using dynamic SQL and a FULL OUTER JOIN. The SQL is auto generated dynamically and executed, where the user simply needs to provide the table names and join criteria. Work great when auditing data migrations!


Database Writings

In this repository you will find my musing and writings on various database topics.

Advanced SQL Joins

A great document that covers advanced join theory topics. It’s a great resource to learn the inner working of SQL joins.

Behavior of Nulls

NULL markers are a great source of frustration, so I decided to write a whole document about how the various SQL constructs treat the NULL marker.

Database Concepts and Components

Here I have collected all the different database concepts, components, and aspects into one document; formatted into a list of questions with a link to an online resource (which is mostly the Wikipedia article on the subject) covering the subject.


Data Analytics

Here is where I keep the various data analytic projects that I have done. This mostly includes descriptive and inferential statistical projects I’ve created, plus some machine learning models that I have built.

Data Analytics


Blog

I also keep a small blog here. Right now, it’s pretty sparse, but I plan to keep adding to it, hopefully.

Blog


Happy coding!!