Reader Challenge #3: Find the Yak!

By Rob Volk on 22 March 2002 | Tags: Reader Challenges

Tired of all those standard SQL problems? Begging for a real challenge?? Think you're some kind of SQL Jedi Master??? Here's a puzzle that I found on the Internet. It has nothing to do with databases, but can you solve it using SQL?

Can you....FIND THE YAK????

THE READER CHALLENGE IS CLOSED! Thank you everyone for your submissions!

Some of you have probably already seen this one and know the answer, or can find it pretty quickly. However, the answer is not the challenge...although you do have to provide the correct answer...the REALLY interesting part is how you use SQL to solve it! Here it is:

You are visiting a neighborhood where 5 next-door neighbors have the following qualities:

They all have different nationalities.
They all live in different houses.
They all have different pets.
They all drink different drinks.
They all smoke different cigarettes.
The English man lives in the red house.
The Swede has a dog.
The Dane drinks tea.
The green house is on the left side of the white house.
They drink coffee in the green house.
The man who smokes Pall Mall has birds.
In the yellow house they smoke Dunhill.
In the middle house they drink milk.
The Norwegian lives in the first house.
The man who smokes Blend lives in the house next to the house with cats.
In the house next to the house where they have a horse, they smoke Dunhill.
The man who smokes Blue Master drinks beer.
The German smokes Prince.
The Norwegian lives next to the blue house.
They drink water in the house next to the house where they smoke Blend.

Question: Who owns the YAK?

I really like this puzzle for a number of reasons. For one thing it very much resembles the type of requirements you get for a real-world database application. As lacking as they seem (ARE!) I've worked on a lot of projects where I YEARNED to have even HALF this much information...and I'm not alone! The key feature is that these points neatly describe domain requirements--which is a fancy term for data integrity, relationships, constraints, rules, etc.--an essential feature of good database design. At the same time, there are some issues that won't come up until you dig into the problem a little more...another major feature of real-world database/application design! :)

Second, not only can SQL solve this puzzle, it is probably the BEST and EASIEST programming solution! If you are relatively new to SQL and set-based programming, or gripe about how SQL lacks "procedural" features, you will still admit that this is very difficult to solve using Visual Basic, C++, Java, etc. (I'd LOVE to see a non-SQL solution, but this is SQL Team after all!)

Consider this puzzle a terrific SQL "exercise", in every sense of the word! It will test both your knowledge of SQL, AND your ability with it. There are many approaches, and even more solutions, and reviewing everyone's submissions will be a tremendous learning experience for all of us on SQL Team. And yes, there IS enough information here to find the answer!

The only restriction is that the solution MUST USE AT LEAST ONE TABLE for the data...you can definitely use more. Views are also perfectly legal. You can use procedural code (loops, IF...THEN statements, multiple stored procedures, functions, even cursors if you must) as long as there's a table and a SELECT statement in there somewhere. The following are additional challenges but not required:

Can it be done without cursors?
Can this be done with only one SQL statement?
Can this be solved using only one table?
What were the factors that led to the design you chose?
What led you to reject a solution or approach?

Points are given for:

Table & database design
Speed and efficiency of code
Originality & innovation
Clarity
Completeness (can your solution answer any question, not just the YAK?)
Least amount of code

If you find inspiration in an article, book, or documentation somewhere, make sure you cite it in your solution, even if you modify it beyond all recognition. Most of all, think openly: working the problem backwards might be easier than forwards! I'm really intrigued in seeing how many different approaches everyone can come up with!

This challenge is open to any registered member of SQL Team; if you are not already a member you can register here. All solutions MUST be emailed to robvolk@sqlteam.com no later than Friday, March 29, 11:59 PM SQL Team time. DO NOT POST your solution; this article will be locked to prevent posts. Questions should also be emailed, however, and any clarifications will be posted in the comments for this article. Submissions will be reviewed and presented in a follow-up article.

Remember to include all of your code, table structures, and comments describing your approach. YOU MUST SUBMIT EVERYTHING, including DDL for the tables and other objects...an overview is not enough. Your solution MUST work with SQL Server 7.0 or higher; if it ALSO works in an earlier version or another database product, that will get some extra credit. If two people submit very similar or identical solutions, the one who submitted earliest gets credit. BONUS POINTS if you find any logical "wrinkles" that affect the answer!

The winner will receive a custom SQL Team title, either their own choice or a special one created by the SQL Teamers! Any suggestions for a custom title can also be emailed.

Good luck and happy coding!