• subscribe
Search
Browse By: Author | Issue
September 20, 2005 12:00 AM

The Logical Puzzle

Itzik Ben-Gan
SQL Server Pro
InstantDoc ID #47503

Solution to September's Puzzle: The Missing Buck
In last month's puzzle, three people arrived at a hotel and asked to share a room. The charge was $30, so each person chipped in $10. Later on, the hotel receptionist discovered that he overcharged the guests by $5. Realizing that he couldn't evenly split $5 between the three guests, he pocketed $2 and gave each guest $1 back. However, the receptionist found it hard to sleep that night because the numbers didn't seem to add up. Each guest eventually paid $9, and 9 x 3 + $2 = $29. Where's the missing buck?

The guests ultimately were supposed to pay $25. After getting the change back, each of them paid $9, for a total of $27. The mistake that the receptionist made was that he added $2 to $27, assuming he'd get $30 instead of subtracting $2 from $27 and expecting $25. To get the original incorrect room charge of $30, you should sum the correct room charge ($25) plus the change that the guests got ($3) plus the amount that the receptionist pocketed ($2).

Similar errors frequently occur in T-SQL code when you apply the incorrect operator (a plus operator instead of a minus operator and vise versa). For example, suppose table T1 has an integer column c1 and you need to return all rows before a gap. In other words, you need to return all c1 values for which a value greater by 1 doesn't exist. A common mistake is to write

SELECT c1 FROM T1 AS A
  WHERE NOT EXISTS(SELECT *
  FROM T1 AS B WHERE A.c1 = 
  B.c1 + 1)

Instead of getting all the values before a gap, you'll get all the values after a gap. The correct query uses a minus operator, not a plus operator:

SELECT c1 FROM T1 AS A
  WHERE NOT EXISTS(SELECT *
  FROM T1 AS B WHERE A.c1 = 
  B.c1 — 1)

The correct way to phrase a relationship where a c1 value in B is greater than a c1 value in A by 1 is

A.c1 = B.c1 — 1

If B1.c1 – 1 is equal to A.c1, then B.c1 is greater than A.c1 by 1 and not the other way around.

October's Puzzle: Alternating Lamp States
One hundred lamps are arranged in a row (call them lamp 1, lamp 2, lamp 3, and so on). All lamps are originally turned off. Each lamp has a switch that alternates its state (on/off). One hundred people (call them person 1, person 2, person 3, and so on) get the following mission: Go to each nth lamp and alternate its state, where n is the person's number. So person 1 alternates the state of lamps 1, 2, 3, and so on. Person 2 alternates the states of lamps 2, 4, 6, and so on. What's the state of the lamps after all 100 people finished their missions?



ARTICLE TOOLS

Comments
Add A Comment
  • LOU
    7 years ago
    Nov 18, 2005

    Interestingly, I got this right, though I didn't get around to it until today. The answer according to Ben-Gan confirms daniel.newman's answer, but I went about it using code. Here is the code I used to get the answer:

    DECLARE @Lamps TABLE( hLamp SMALLINT IDENTITY(1,1),
    bState SMALLINT
    );
    DECLARE @iCount TINYINT;

    SET @iCount = 1;

    WHILE @iCount <= 100
    BEGIN
    INSERT INTO
    @Lamps(bState)
    VALUES
    (-1);

    SET @iCount = @iCount + 1;
    END

    SET @iCount = 1;

    WHILE @iCount <= 100
    BEGIN
    UPDATE
    @Lamps
    SET
    bState = (bState * -1)
    WHERE
    (hLamp % @iCount) = 0;

    SET @iCount = @iCount + 1;
    END

    SELECT
    [Lamp Number] AS 'Lamp Number'
    FROM
    (
    SELECT
    hLamp AS 'Lamp Number',
    CASE WHEN bState = 1 THEN
    'On'
    ELSE
    'Off'
    END AS 'Final Lamp State'
    FROM
    @Lamps
    ) x
    WHERE
    [Final Lamp State] = 'On'
    ORDER BY
    [Lamp Number];

  • Salomon
    7 years ago
    Oct 25, 2005

    I agree with MikeBoy; all the odd lamps will be on. Here's some code to verify it.

    CREATE TABLE dbo.#BlackBelt(
    ID int identity(1,1),
    Switch int)

    DECLARE @ID int,
    @Switch bit

    SET @ID = 1
    SET @Switch = 1

    WHILE @ID <= 100
    BEGIN
    INSERT dbo.#BlackBelt(
    Switch)
    VALUES (0)

    SELECT @ID = @ID + 1
    END


    SELECT @ID = MIN(ID)
    FROM dbo.#BlackBelt

    WHILE @ID IS NOT NULL
    BEGIN
    UPDATE dbo.#BlackBelt
    SET Switch = @Switch
    WHERE ID % @ID = 0 AND
    ID >= @ID

    SELECT @ID = MIN(ID)
    FROM dbo.#BlackBelt
    WHERE ID > @ID

    SELECT @Switch = CASE WHEN @Switch = 1 THEN 0
    ELSE 1 END
    END


    SELECT *
    FROM dbo.#BlackBelt

  • TOBY
    7 years ago
    Oct 13, 2005

    And I meant to say "The factor 40 is paired with the factor 1." Duh!

  • TOBY
    7 years ago
    Oct 13, 2005

    Ooops. s/it's/its/;

  • TOBY
    7 years ago
    Oct 13, 2005

    Yes. Here's why. The lights are on if there are an odd number of factors for the number. For all numbers that aren't squares, for every factor, there is a partner factor that isn't the same as the factor. For instance, for 40, the factor 4 is paired with the factor 10. The factor 40 is paired with the factor 40. The factor 5 is paired with the factor 8. If you think about it, you will realize that every factor has a partner factor. The exception is for a square, for which the square root is it's own partner. This means that the only numbers with an odd number of factors are squares. Which is why those lights are on.

    --Toby Ovod-Everett

You must log on before posting a comment.

Are you a new visitor? Register Here

© 2012 Penton Media, Inc.

Windows is a trademark of the Microsoft group of companies. Windows IT Pro is used by Penton Media Inc. under license from owner.