SQL Puzzle – Retrieve the Unique Evens Under 10

As developers and DBAs, we know that continually honing our SQL skills is critical for unlocking all our database systems offer. Sometimes, working through some fun puzzles can be a great way to stretch our SQL muscles. In that spirit, let me walk through a bite-sized SQL puzzle that helps reinforce some key concepts. Let us see Retrieve the Unique Evens Under 10 puzzle.

I was chatting recently with a friend who is diving deeper into SQL Server at work. They asked if I could provide some sample puzzles to solve using T-SQL. Here’s one I shared that helps combine a few basic but everyday tasks:

The Puzzle – Unique Evens

Imagine you have the following table called ‘numbers’ –

CREATE TABLE numbers (
num INT
);

And let’s say we populate it with a few integers, both even and odd, and allow some duplicates as well:

INSERT INTO numbers (num)
VALUES (2), (4), (6), (8), (10), (5), (3), (9);

Now, using a single SQL statement, can you query this table and just return only the unique, even numbers less than 10?

So for example, if you queried the populated numbers table above, we would want the result set:

2 4 6 8

The key requirements are: even numbers only, no duplicates (unique), and less than 10. How can we write that using standard ANSI SQL?

Walking Through the Solution

The first tool we have is DISTINCT – this will make sure we only get one copy of each value, eliminating duplicates:

SELECT DISTINCT num FROM numbers;

Next, we can use a WHERE clause to filter – first for only even numbers by checking the modulus of 2:

SELECT DISTINCT num
FROM numbers
WHERE num % 2 = 0;

And finally, we specify getting values less than 10 by adding an additional condition:

SELECT DISTINCT num
FROM numbers
WHERE num % 2 = 0 AND num < 10;

And there we have it! The puzzle uses DISTINCT, WHERE, modulo, and compound conditions to query the subset of rows we want. Feel free to try adding more data to the numbers table or tweaking the conditions. I hope you found this enjoyable – let me know other puzzle ideas for honing SQL!

You can watch my YouTube videos over here.

Reference: Pinal Dave (https://blog.sqlauthority.com)