Using SQL Server to solve Einstein’s puzzles

A particular category of logic puzzles have to do with the satisfaction of constraints. Among those are the so called “Einstein’s puzzles”, also called “zebra puzzles“. The first time I saw such a puzzle was about 10 years ago. I immediately thought that such puzzles could be solved using SQL Server.

Indeed, I thought that it would be easy to use a SELECT statement without a WHERE clause to get the Cartesian product from tables that would list all the entities involved. This SELECT statement would populate a “main table” that would keep all possibilities. Then, we would translate each constraint to DELETE statements that would eliminate from the main table the rows that do not fit the particular constraint. When all constraints would have been accounted for, in the main table there would remain only the rows that correspond to the solution of the puzzle.

The problem I faced early on was that most Einstein puzzles have some constraints that are difficult to “translate” to straightforward DELETE statements. Instead, these constraints can be handled by writing very specific programs that examine the rows of the main table and perform deletions based on the particular rules of the constraint statement.

Again, in order to make Einstein puzzles more challenging, their creators devise most constraints in a way that makes it impossible to “translate” them to simple and straightforward DELETE statements from a main table that contains all possibilities. Even the original (i.e. the first) Einstein puzzle exhibits this practice.

In order to provide a proof-of-concept solution to such a puzzle, I needed a puzzle that provided straightforward constraints. The existence of complex constraints that cannot be represented by DELETE statements (and instead need specific programming) would complicate my analysis and would obscure its main points.

Thus, for this analysis, I chose to create my own puzzle (based on a “story” and on “entities” from my own imagination). Of course, the story and the entities are not what is important here; what is important are the constraints that I will choose to introduce. All the constraints that I will introduce in this puzzle lack complexity and “translate” to straightforward DELETE statements. I believe that this practice will help showcase the concepts that are needed in order to understand how a relational database like SQL Server can be used to solve these kind of puzzles.

The puzzle I devised is the following:

In an airport, five different couples showed up. Each couple was heading to a different destination. Each couple also had a suitcase with them. Each suitcase had a different color and a different dangerous item within. Your mission, should you decide to accept it, is to find the exact details: For each couple, you have to find the husband, the wife, their destination, the color of their suitcase and the dangerous item within the suitcase.

The names of the husbands in alphabetical order are: Adam, Bill, Chad, Dave, Eric. The names of the wives in alphabetical order are: Annabell, Beatrice, Christin, Dorothea, Elleonor. The destinations in alphabetical order are: China, France, Greece, Italy, Japan. The colors of the suitcases in alphabetical order are: Blue, Green, Red, White, Yellow. The dangerous items in alphabetical order are: Cutter, Hammer, Knife, Pistol, Wrench.

I will go ahead and give you the solution that I prearranged. The constraints that I will provide will come straight from this solution.

Adam Dorothea Greece Green  Cutter
Bill Elleonor Japan  Red    Wrench
Chad Christin Italy  Yellow Hammer
Dave Annabell China  White  Pistol
Eric Beatrice France Blue   Knife

After I arranged the solution, I wrote a SQL script that creates a database. In that database, it creates one table that lists the husbands, one that lists the wives and so on. It then uses a SELECT statement FROM those tables without a WHERE clause. In effect, this produces the Cartesian product of the available combinations/possibilities. The rows from the SELECT statement populate the main table. Then, I devise constraints. I am careful when I create the constraints, so that each constraint is not redundant. I check that each constraint leads to the elimination of rows from  the main table. Here are the constraints I came up with:

01. The couple that headed to Italy had a hammer in their suitcase.
02. Elleonor and her husband were going to Japan.
03. There was a pistol in the white suitcase.
04. Eric and Beatrice are married to each other.
05. Adam and his wife carried a green suitcase.
06. Bill and his wife had a wrench in their suitcase.
07. The couple that was going to Greece had a green suitcase.
08. Annabell and her husband were heading to China.
09. The red suitcase contained a wrench.
10. Chad and Christin are married.
11. Beatrice and her husband were carrying a knife in their suitcase.
12. Chad and his wife carried a yellow suitcase.
13. Dave and Annabell are married.
14. The cutter was in the green suitcase.
15. Dorothea and her husband were not going to France.

For each constraint I devised, I created corresponding DELETE statements that enforced those constraints, by eliminating the rows that did not apply. I kept adding constraints until only 5 rows remained. The 5 rows that remained are the solution rows that I just presented above. It took me 15 simple constraint statements to eliminate all invalid rows.

The SQL script that I wrote is presented below. I run it using a Microsoft SQL Server 2005 Express instance that I happened to have in my PC for another reason. After each constraint is applied, I provide the row count of the main table. This way, we can see the row count diminishing after each constraint is acknowledged.

PRINT 'In an airport, five different couples showed up.'
PRINT 'Each couple was heading to a different destination.'
PRINT 'Each couple also had a suitcase with them.'
PRINT 'Each suitcase had a different color and a different dangerous item within.'
PRINT 'Your mission, should you decide to accept it, is to find the exact details:'
PRINT 'For each couple, you have to find the husband, the wife, their destination,'
PRINT 'the color of their suitcase and the dangerous item within the suitcase.'

USE master
GO

CREATE DATABASE PuzzleDatabase
GO

USE PuzzleDatabase
GO

CREATE TABLE PuzzleTable
(
   Husband     VARCHAR(10),
   Wife        VARCHAR(10),
   Destination VARCHAR(10),
   Color       VARCHAR(10),
   Item        VARCHAR(10)
)
GO

CREATE TABLE HusbandTable     (HusbandColumn     VARCHAR(10))
GO
CREATE TABLE WifeTable        (WifeColumn        VARCHAR(10))
GO
CREATE TABLE DestinationTable (DestinationColumn VARCHAR(10))
GO
CREATE TABLE ColorTable       (ColorColumn       VARCHAR(10))
GO
CREATE TABLE ItemTable        (ItemColumn        VARCHAR(10))
GO

PRINT ''
PRINT 'The names of the husbands in alphabetical order are:'
PRINT 'Adam, Bill, Chad, Dave, Eric.'

INSERT INTO HusbandTable (HusbandColumn) VALUES ('Adam')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Bill')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Chad')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Dave')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Eric')
GO

PRINT ''
PRINT 'The names of the wives in alphabetical order are:'
PRINT 'Annabell, Beatrice, Christin, Dorothea, Elleonor.'

INSERT INTO WifeTable (WifeColumn) VALUES ('Annabell')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Beatrice')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Christin')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Dorothea')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Elleonor')
GO

PRINT ''
PRINT 'The destinations in alphabetical order are:'
PRINT 'China, France, Greece, Italy, Japan.'

INSERT INTO DestinationTable (DestinationColumn) VALUES ('China')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('France')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('Greece')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('Italy')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('Japan')
GO

PRINT ''
PRINT 'The colors of the suitcases in alphabetical order are:'
PRINT 'Blue, Green, Red, White, Yellow.'

INSERT INTO ColorTable (ColorColumn) VALUES ('Blue')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('Green')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('Red')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('White')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('Yellow')
GO

PRINT ''
PRINT 'The dangerous items in alphabetical order are:'
PRINT 'Cutter, Hammer, Knife, Pistol, Wrench.'

INSERT INTO ItemTable (ItemColumn) VALUES ('Cutter')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Hammer')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Knife')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Pistol')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Wrench')
GO

PRINT ''
PRINT 'The following step is the most important one.'
PRINT 'We calculate all posibilities (i.e. the Cartesian product).'
PRINT '(5 husbands) x (5 wives) x (5 destinations) x (5 suitcase colors) x (5 dangerours items) ='
PRINT '= 5^5 possibilities = 3125 possibilities = 3125 rows.'
PRINT 'The SELECT statement does this calculation automatically for us,'
PRINT 'if we do not specify a WHERE clause.'

INSERT INTO PuzzleTable (Husband, Wife, Destination, Color, Item)
SELECT HusbandColumn, WifeColumn, DestinationColumn, ColorColumn, ItemColumn
FROM HusbandTable, WifeTable, DestinationTable, ColorTable, ItemTable
GO

PRINT ''
PRINT 'By monitoring the row count, we will be able to see'
PRINT 'how each restriction diminishes the number of rows/possibilities.'
PRINT 'When all of the restrictions have been acknowledged,'
PRINT 'only 5 rows should remain on the table.'
PRINT 'These 5 rows should correspond to the solution.'

SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '01. The couple that headed to Italy had a hammer in their suitcase.'

DELETE FROM PuzzleTable
WHERE Destination = 'Italy' AND Item != 'Hammer'
GO
DELETE FROM PuzzleTable
WHERE Destination != 'Italy' AND Item = 'Hammer'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '02. Elleonor and her husband were going to Japan.'

DELETE FROM PuzzleTable
WHERE Wife = 'Elleonor' AND Destination != 'Japan'
GO
DELETE FROM PuzzleTable
WHERE Wife != 'Elleonor' AND Destination = 'Japan'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '03. There was a pistol in the white suitcase.'

DELETE FROM PuzzleTable
WHERE Color = 'White' AND Item != 'Pistol'
GO
DELETE FROM PuzzleTable
WHERE Color != 'White' AND Item = 'Pistol'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '04. Eric and Beatrice are married to each other.'

DELETE FROM PuzzleTable
WHERE Husband = 'Eric' AND Wife != 'Beatrice'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Eric' AND Wife = 'Beatrice'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '05. Adam and his wife carried a green suitcase.'

DELETE FROM PuzzleTable
WHERE Husband = 'Adam' AND Color != 'Green'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Adam' AND Color = 'Green'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '06. Bill and his wife had a wrench in their suitcase.'

DELETE FROM PuzzleTable
WHERE Husband = 'Bill' AND Item != 'Wrench'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Bill' AND Item = 'Wrench'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '07. The couple that was going to Greece had a green suitcase.'

DELETE FROM PuzzleTable
WHERE Destination = 'Greece' AND Color != 'Green'
GO
DELETE FROM PuzzleTable
WHERE Destination != 'Greece' AND Color = 'Green'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '08. Annabell and her husband were heading to China.'

DELETE FROM PuzzleTable
WHERE Wife = 'Annabell' AND Destination != 'China'
GO
DELETE FROM PuzzleTable
WHERE Wife != 'Annabell' AND Destination = 'China'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '09. The red suitcase contained a wrench.'

DELETE FROM PuzzleTable
WHERE Color = 'Red' AND Item != 'Wrench'
GO
DELETE FROM PuzzleTable
WHERE Color != 'Red' AND Item = 'Wrench'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '10. Chad and Christin are married.'

DELETE FROM PuzzleTable
WHERE Husband = 'Chad' AND Wife != 'Christin'  
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Chad' AND Wife = 'Christin'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '11. Beatrice and her husband were carrying a knife in their suitcase.'

DELETE FROM PuzzleTable
WHERE Wife = 'Beatrice' AND Item != 'Knife'
GO
DELETE FROM PuzzleTable
WHERE Wife != 'Beatrice' AND Item = 'Knife'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '12. Chad and his wife carried a yellow suitcase.'

DELETE FROM PuzzleTable
WHERE Husband = 'Chad' and Color != 'Yellow'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Chad' and Color = 'Yellow'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '13. Dave and Annabell are married.'

DELETE FROM PuzzleTable
WHERE Husband = 'Dave' and Wife != 'Annabell'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Dave' and Wife = 'Annabell'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '14. The cutter was in the green suitcase.'

DELETE FROM PuzzleTable
WHERE Color = 'Green' and Item != 'Cutter'
GO
DELETE FROM PuzzleTable
WHERE Color != 'Green' and Item = 'Cutter'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '15. Dorothea and her husband were not going to France.'

DELETE FROM PuzzleTable
WHERE Wife = 'Dorothea' and Destination = 'France'
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT 'The 5 rows that should remain on the table,'
PRINT 'i.e. the solution, should be:'
PRINT '----------------------------------'
PRINT 'Adam Dorothea Greece Green  Cutter'
PRINT 'Bill Elleonor Japan  Red    Wrench'  
PRINT 'Chad Christin Italy  Yellow Hammer'
PRINT 'Dave Annabell China  White  Pistol'
PRINT 'Eric Beatrice France Blue   Knife'
PRINT '----------------------------------'

SELECT * FROM PuzzleTable
GO

USE master
GO

DROP DATABASE PuzzleDatabase
GO

Here is the results tab after running the previous script:

SQL1

Although we eliminated all invalid rows, leaving only the 5 correct ones, it took us as much as 15 simple constraint statements to do that. Now, that may not be a problem in and of itself. What is a problem is that some of these constraints are redundant. We could have reached the solution using less constraints. To understand why, we have to realize that relying only on the DELETE statements is not enough. We have to be more thorough.

Translating a constraint statement to DELETE statements is not enough. After a deletion, we also have to check for the formation of unique relationships and use them to further eliminate invalid rows. Let me give you an example.

Suppose that we delete some rows. Then, checking the remaining rows, we observe that Adam only has Dorothea as his wife. There are no rows with Adam and another of the wives. Thus, we understand that Adam and Dorothea are a couple. This is great. And we owe it to the DELETE statements that we have been able to reach this conclusion. But as we continue to observe other rows, we find that other husbands have wives with Dorothea being one of them. For example, there are rows that list Bill with Dorothea, as well as with other wives as well. The same may happen, say, for Eric.

But, since we know that Adam and Dorothea are a couple, we can DELETE the rows that corresponds to Dorothea with other husbands.

The same goes for any combination of two different entitles. If, for example, we find that the rows containing the Destination Italy only have the Item Hammer and no other Items, then we immediately know that we should eliminate all rows that correspond to other destinations and hammers.

Thus, to extract the maximum information and to do the maximum elimination possible from the constraints, after the straightforward deletion statements, we should search for unique relationships and use them to further eliminate invalid rows.

In order to do that, I created a lengthy procedure that I call after each constraint has been acknowledged and handled. This procedure checks each and every pair of different entities, in order to find a unique correspondence. If it finds such a correspondence, it “propagates” this information, meaning that deletes the rows that are invalid due to this unique correspondence.

The procedure that checks each and every pair of different entities for a unique correspondence, also keeps track of whether it made any deletions or not. If it indeed made any deletions, it runs again, because there might be new unique correspondences that came up because of the deletions. And it continues to run again, as long as it makes any deletions in its previous run.

This procedure is lengthy (code-wise). It is a shame that I created it using such a verbose technique, especially when SQL Server scripts can be very versatile and dynamic. Programming a SQL Server script allows you to use dynamic SQL statements, thus forming your code on the fly. Still, I concentrated on the main concepts. Creating an elaborate, dynamic statement generation would have obscure the main points of this analysis.

Calling this procedure after each constraint has been handled, helps in the reduction of the constraints needed. Indeed, when we run the SQL script, we see that we reach the solution with no need for the last two constraints.

The SQL script, that contains the procedure that accounts for unique correspondences, and thus, “converges” faster (“faster” meaning “using less constraints”), is listed below:

PRINT 'In an airport, five different couples showed up.'
PRINT 'Each couple was heading to a different destination.'
PRINT 'Each couple also had a suitcase with them.'
PRINT 'Each suitcase had a different color and a different dangerous item within.'
PRINT 'Your mission, should you decide to accept it, is to find the exact details:'
PRINT 'For each couple, you have to find the husband, the wife, their destination,'
PRINT 'the color of their suitcase and the dangerous item within the suitcase.'

USE master
GO

CREATE DATABASE PuzzleDatabase
GO

USE PuzzleDatabase
GO

CREATE PROCEDURE PropagateUniqueInformation
AS
BEGIN
   DECLARE @myExecuteAgainFlag BIT

   DECLARE @myDistinctCount INT
   DECLARE @myDistinctValue VARCHAR(10)

   SET @myExecuteAgainFlag = 0

   -- Husbands and Wives

   SELECT @myDistinctCount = COUNT (DISTINCT Wife)
   FROM PuzzleTable
   WHERE Husband = 'Adam'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Wife
      FROM PuzzleTable
      WHERE Husband = 'Adam'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Adam' AND Wife = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Wife)
   FROM PuzzleTable
   WHERE Husband = 'Bill'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Wife
      FROM PuzzleTable
      WHERE Husband = 'Bill'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Bill' AND Wife = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Wife)
   FROM PuzzleTable
   WHERE Husband = 'Chad'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Wife
      FROM PuzzleTable
      WHERE Husband = 'Chad'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Chad' AND Wife = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Wife)
   FROM PuzzleTable
   WHERE Husband = 'Dave'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Wife
      FROM PuzzleTable
      WHERE Husband = 'Dave'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Dave' AND Wife = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Wife)
   FROM PuzzleTable
   WHERE Husband = 'Eric'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Wife
      FROM PuzzleTable
      WHERE Husband = 'Eric'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Eric' AND Wife = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Husbands and Destinations

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Husband = 'Adam'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Husband = 'Adam'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Adam' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Husband = 'Bill'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Husband = 'Bill'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Bill' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Husband = 'Chad'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Husband = 'Chad'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Chad' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Husband = 'Dave'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Husband = 'Dave'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Dave' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Husband = 'Eric'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Husband = 'Eric'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Eric' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Husbands and Colors

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Husband = 'Adam'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Husband = 'Adam'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Adam' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Husband = 'Bill'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Husband = 'Bill'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Bill' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Husband = 'Chad'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Husband = 'Chad'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Chad' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Husband = 'Dave'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Husband = 'Dave'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Dave' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Husband = 'Eric'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Husband = 'Eric'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Eric' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Husbands and Items

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Husband = 'Adam'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Husband = 'Adam'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Adam' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Husband = 'Bill'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Husband = 'Bill'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Bill' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Husband = 'Chad'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Husband = 'Chad'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Chad' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Husband = 'Dave'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Husband = 'Dave'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Dave' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Husband = 'Eric'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Husband = 'Eric'
      DELETE FROM PuzzleTable
      WHERE Husband != 'Eric' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Wives and Destinations

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Wife = 'Annabell'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Wife = 'Annabell'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Annabell' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Wife = 'Beatrice'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Wife = 'Beatrice'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Beatrice' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Wife = 'Christin'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Wife = 'Christin'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Christin' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Wife = 'Dorothea'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Wife = 'Dorothea'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Dorothea' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Destination)
   FROM PuzzleTable
   WHERE Wife = 'Elleonor'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Destination
      FROM PuzzleTable
      WHERE Wife = 'Elleonor'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Elleonor' AND Destination = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Wives and Colors

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Wife = 'Annabell'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Wife = 'Annabell'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Annabell' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Wife = 'Beatrice'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Wife = 'Beatrice'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Beatrice' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Wife = 'Christin'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Wife = 'Christin'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Christin' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Wife = 'Dorothea'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Wife = 'Dorothea'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Dorothea' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Wife = 'Elleonor'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Wife = 'Elleonor'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Elleonor' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Wives and Items

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Wife = 'Annabell'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Wife = 'Annabell'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Annabell' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Wife = 'Beatrice'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Wife = 'Beatrice'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Beatrice' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Wife = 'Christin'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Wife = 'Christin'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Christin' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Wife = 'Dorothea'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Wife = 'Dorothea'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Dorothea' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Wife = 'Elleonor'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Wife = 'Elleonor'
      DELETE FROM PuzzleTable
      WHERE Wife != 'Elleonor' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Destinations and Colors

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Destination = 'China'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Destination = 'China'
      DELETE FROM PuzzleTable
      WHERE Destination != 'China' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Destination = 'France'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Destination = 'France'
      DELETE FROM PuzzleTable
      WHERE Destination != 'France' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Destination = 'Greece'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Destination = 'Greece'
      DELETE FROM PuzzleTable
      WHERE Destination != 'Greece' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Destination = 'Italy'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Destination = 'Italy'
      DELETE FROM PuzzleTable
      WHERE Destination != 'Italy' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Color)
   FROM PuzzleTable
   WHERE Destination = 'Japan'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Color
      FROM PuzzleTable
      WHERE Destination = 'Japan'
      DELETE FROM PuzzleTable
      WHERE Destination != 'Japan' AND Color = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Destinations and Items

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Destination = 'China'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Destination = 'China'
      DELETE FROM PuzzleTable
      WHERE Destination != 'China' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Destination = 'France'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Destination = 'France'
      DELETE FROM PuzzleTable
      WHERE Destination != 'France' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Destination = 'Greece'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Destination = 'Greece'
      DELETE FROM PuzzleTable
      WHERE Destination != 'Greece' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Destination = 'Italy'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Destination = 'Italy'
      DELETE FROM PuzzleTable
      WHERE Destination != 'Italy' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Destination = 'Japan'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Destination = 'Japan'
      DELETE FROM PuzzleTable
      WHERE Destination != 'Japan' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Colors and Items

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Color = 'Blue'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Color = 'Blue'
      DELETE FROM PuzzleTable
      WHERE Color != 'Blue' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Color = 'Green'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Color = 'Green'
      DELETE FROM PuzzleTable
      WHERE Color != 'Green' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END
 
   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Color = 'Red'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Color = 'Red'
      DELETE FROM PuzzleTable
      WHERE Color != 'Red' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Color = 'White'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Color = 'White'
      DELETE FROM PuzzleTable
      WHERE Color != 'White' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   SELECT @myDistinctCount = COUNT (DISTINCT Item)
   FROM PuzzleTable
   WHERE Color = 'Yellow'
   IF @myDistinctCount = 1
   BEGIN
      SELECT @myDistinctValue = Item
      FROM PuzzleTable
      WHERE Color = 'Yellow'
      DELETE FROM PuzzleTable
      WHERE Color != 'Yellow' AND Item = @myDistinctValue
      IF @@rowcount > 0
      BEGIN
         SET @myExecuteAgainFlag = 1
      END
   END

   -- Epilogue

   IF @myExecuteAgainFlag = 0
   BEGIN
      RETURN
   END

   EXEC PropagateUniqueInformation
END
GO

CREATE TABLE PuzzleTable
(
   Husband     VARCHAR(10),
   Wife        VARCHAR(10),
   Destination VARCHAR(10),
   Color       VARCHAR(10),
   Item        VARCHAR(10)
)
GO

CREATE TABLE HusbandTable     (HusbandColumn     VARCHAR(10))
GO
CREATE TABLE WifeTable        (WifeColumn        VARCHAR(10))
GO
CREATE TABLE DestinationTable (DestinationColumn VARCHAR(10))
GO
CREATE TABLE ColorTable       (ColorColumn       VARCHAR(10))
GO
CREATE TABLE ItemTable        (ItemColumn        VARCHAR(10))
GO

PRINT ''
PRINT 'The names of the husbands in alphabetical order are:'
PRINT 'Adam, Bill, Chad, Dave, Eric.'

INSERT INTO HusbandTable (HusbandColumn) VALUES ('Adam')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Bill')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Chad')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Dave')
GO
INSERT INTO HusbandTable (HusbandColumn) VALUES ('Eric')
GO

PRINT ''
PRINT 'The names of the wives in alphabetical order are:'
PRINT 'Annabell, Beatrice, Christin, Dorothea, Elleonor.'

INSERT INTO WifeTable (WifeColumn) VALUES ('Annabell')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Beatrice')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Christin')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Dorothea')
GO
INSERT INTO WifeTable (WifeColumn) VALUES ('Elleonor')
GO

PRINT ''
PRINT 'The destinations in alphabetical order are:'
PRINT 'China, France, Greece, Italy, Japan.'

INSERT INTO DestinationTable (DestinationColumn) VALUES ('China')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('France')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('Greece')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('Italy')
GO
INSERT INTO DestinationTable (DestinationColumn) VALUES ('Japan')
GO

PRINT ''
PRINT 'The colors of the suitcases in alphabetical order are:'
PRINT 'Blue, Green, Red, White, Yellow.'

INSERT INTO ColorTable (ColorColumn) VALUES ('Blue')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('Green')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('Red')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('White')
GO
INSERT INTO ColorTable (ColorColumn) VALUES ('Yellow')
GO

PRINT ''
PRINT 'The dangerous items in alphabetical order are:'
PRINT 'Cutter, Hammer, Knife, Pistol, Wrench.'

INSERT INTO ItemTable (ItemColumn) VALUES ('Cutter')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Hammer')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Knife')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Pistol')
GO
INSERT INTO ItemTable (ItemColumn) VALUES ('Wrench')
GO

PRINT ''
PRINT 'The following step is the most important one.'
PRINT 'We calculate all posibilities (i.e. the Cartesian product).'
PRINT '(5 husbands) x (5 wives) x (5 destinations) x (5 suitcase colors) x (5 dangerours items) ='
PRINT '= 5^5 possibilities = 3125 possibilities = 3125 rows.'
PRINT 'The SELECT statement does this calculation automatically for us,'
PRINT 'if we do not specify a WHERE clause.'

INSERT INTO PuzzleTable (Husband, Wife, Destination, Color, Item)
SELECT HusbandColumn, WifeColumn, DestinationColumn, ColorColumn, ItemColumn
FROM HusbandTable, WifeTable, DestinationTable, ColorTable, ItemTable
GO

PRINT ''
PRINT 'By monitoring the row count, we will be able to see'
PRINT 'how each restriction diminishes the number of rows/possibilities.'
PRINT 'When all of the restrictions have been acknowledged,'
PRINT 'only 5 rows should remain on the table.'
PRINT 'These 5 rows should correspond to the solution.'

SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '01. The couple that headed to Italy had a hammer in their suitcase.'

DELETE FROM PuzzleTable
WHERE Destination = 'Italy' AND Item != 'Hammer'
GO
DELETE FROM PuzzleTable
WHERE Destination != 'Italy' AND Item = 'Hammer'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '02. Elleonor and her husband were going to Japan.'

DELETE FROM PuzzleTable
WHERE Wife = 'Elleonor' AND Destination != 'Japan'
GO
DELETE FROM PuzzleTable
WHERE Wife != 'Elleonor' AND Destination = 'Japan'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '03. There was a pistol in the white suitcase.'

DELETE FROM PuzzleTable
WHERE Color = 'White' AND Item != 'Pistol'
GO
DELETE FROM PuzzleTable
WHERE Color != 'White' AND Item = 'Pistol'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '04. Eric and Beatrice are married to each other.'

DELETE FROM PuzzleTable
WHERE Husband = 'Eric' AND Wife != 'Beatrice'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Eric' AND Wife = 'Beatrice'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '05. Adam and his wife carried a green suitcase.'

DELETE FROM PuzzleTable
WHERE Husband = 'Adam' AND Color != 'Green'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Adam' AND Color = 'Green'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '06. Bill and his wife had a wrench in their suitcase.'

DELETE FROM PuzzleTable
WHERE Husband = 'Bill' AND Item != 'Wrench'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Bill' AND Item = 'Wrench'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '07. The couple that was going to Greece had a green suitcase.'

DELETE FROM PuzzleTable
WHERE Destination = 'Greece' AND Color != 'Green'
GO
DELETE FROM PuzzleTable
WHERE Destination != 'Greece' AND Color = 'Green'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '08. Annabell and her husband were heading to China.'

DELETE FROM PuzzleTable
WHERE Wife = 'Annabell' AND Destination != 'China'
GO
DELETE FROM PuzzleTable
WHERE Wife != 'Annabell' AND Destination = 'China'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '09. The red suitcase contained a wrench.'

DELETE FROM PuzzleTable
WHERE Color = 'Red' AND Item != 'Wrench'
GO
DELETE FROM PuzzleTable
WHERE Color != 'Red' AND Item = 'Wrench'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '10. Chad and Christin are married.'

DELETE FROM PuzzleTable
WHERE Husband = 'Chad' AND Wife != 'Christin'  
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Chad' AND Wife = 'Christin'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '11. Beatrice and her husband were carrying a knife in their suitcase.'

DELETE FROM PuzzleTable
WHERE Wife = 'Beatrice' AND Item != 'Knife'
GO
DELETE FROM PuzzleTable
WHERE Wife != 'Beatrice' AND Item = 'Knife'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '12. Chad and his wife carried a yellow suitcase.'

DELETE FROM PuzzleTable
WHERE Husband = 'Chad' and Color != 'Yellow'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Chad' and Color = 'Yellow'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '13. Dave and Annabell are married.'

DELETE FROM PuzzleTable
WHERE Husband = 'Dave' and Wife != 'Annabell'
GO
DELETE FROM PuzzleTable
WHERE Husband != 'Dave' and Wife = 'Annabell'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '14. The cutter was in the green suitcase.'

DELETE FROM PuzzleTable
WHERE Color = 'Green' and Item != 'Cutter'
GO
DELETE FROM PuzzleTable
WHERE Color != 'Green' and Item = 'Cutter'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT '15. Dorothea and her husband were not going to France.'

DELETE FROM PuzzleTable
WHERE Wife = 'Dorothea' and Destination = 'France'
GO
EXEC PropagateUniqueInformation
GO
SELECT COUNT(*) FROM PuzzleTable
GO

PRINT ''
PRINT 'The 5 rows that should remain on the table,'
PRINT 'i.e. the solution, should be:'
PRINT '----------------------------------'
PRINT 'Adam Dorothea Greece Green  Cutter'
PRINT 'Bill Elleonor Japan  Red    Wrench'  
PRINT 'Chad Christin Italy  Yellow Hammer'
PRINT 'Dave Annabell China  White  Pistol'
PRINT 'Eric Beatrice France Blue   Knife'
PRINT '----------------------------------'

SELECT * FROM PuzzleTable
GO

USE master
GO

DROP DATABASE PuzzleDatabase
GO

Here is the results tab after running the previous script:

SQL2

Advertisements

About Dimitrios Kalemis

I am a systems engineer specializing in Microsoft products and technologies. I am also an author. Please visit my blog to see the blog posts I have written, the books I have written and the applications I have created. I definitely recommend my blog posts under the category "Management", all my books and all my applications. I believe that you will find them interesting and useful. I am in the process of writing more blog posts and books, so please visit my blog from time to time to see what I come up with next. I am also active on other sites; links to those you can find in the "About me" page of my blog.
This entry was posted in SQL Server. Bookmark the permalink.