OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

How to solve the Multiple Dwellings Puzzle in SQL

June 8th, 2010 By Frank Zhou

The following is an interesting puzzle posted on the programming praxis website:

Multiple Dwellings
Baker, Cooper, Fletcher, Miller and Smith live on different floors of an apartment house that contains only five floors.
Baker does not live on the top floor. Cooper does not live on the bottom floor.
Fletcher does not live on either the top or the bottom floor. Miller lives on a higher floor than does Cooper.
Smith does not live on a floor adjacent to Fletcher’s.
Fletcher does not live on a floor adjacent to Cooper’s. Where does everyone live?

—————————————————————–SQL Solution——————————————————————————————



WITH DATA ( num ) AS
(select 1 from dual
 union all
 select d.num+1 from DATA d
 where d.num <5
)
select Baker.num as Baker, Cooper.num as Cooper, Fletcher.num AS Fletcher ,
Miller.num AS Miller, Smith.num AS Smith 
FROM DATA Baker, DATA Cooper, DATA Fletcher, DATA Miller, DATA Smith 
WHERE Baker.num  NOT IN (Cooper.num, Fletcher.num, Miller.num,  Smith.num )
AND Cooper.num   NOT IN (Baker.num,  Fletcher.num, Miller.num , Smith.num )
AND Fletcher.num NOT IN (Baker.num,  Cooper.num,  Miller.num , Smith.num )
AND Miller.num   NOT IN (Baker.num,  Cooper.num, Fletcher.num,  Smith.num )
AND Smith.num    NOT IN (Baker.num,  Cooper.num, Fletcher.num,  Miller.num ) 
AND Baker.num != 5
AND Cooper.num NOT IN (1,5)
AND Fletcher.num NOT IN (1,5)
AND Miller.num > Cooper.num  AND Miller.num NOT IN (1,2)
AND ABS(Smith.num -  Fletcher.num) > 1
AND ABS (Fletcher.num -  Cooper.num) >1;




     BAKER     COOPER   FLETCHER     MILLER      SMITH
---------- ---------- ---------- ---------- ----------
         3          2          4          5          1

SQL>

2 Responses to “How to solve the Multiple Dwellings Puzzle in SQL”

  1. newkid Says:

    WITH comb as (
    SELECT INSTR(p,’1′) AS Baker
    ,INSTR(p,’2′) AS Cooper
    ,INSTR(p,’3′) AS Fletcher
    ,INSTR(p,’4′) AS Miller
    ,INSTR(p,’5′) AS Smith
    FROM ( SELECT REPLACE(SYS_CONNECT_BY_PATH(rn,’\’),’\’) AS p
    FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=5)
    WHERE LEVEL=5
    CONNECT BY NOCYCLE LEVEL<=5 AND rnPRIOR rn
    )
    )
    SELECT * FROM comb
    WHERE Baker5
    AND Cooper1
    AND Fletcher NOT IN (1,5)
    AND Miller > Cooper
    AND Smith-Fletcher NOT IN (1,-1)
    AND Cooper-Fletcher NOT IN (1,-1);

    BAKER COOPER FLETCHER MILLER SMITH
    ———- ———- ———- ———- ———-
    3 2 4 5 1

  2. newkid Says:

    It’s not easy to post my code correctly:

    WITH comb as (  
           SELECT INSTR(p,'1') AS Baker
                 ,INSTR(p,'2') AS Cooper      
                 ,INSTR(p,'3') AS Fletcher       
                 ,INSTR(p,'4') AS Miller  
                 ,INSTR(p,'5') AS Smith     
             FROM ( SELECT REPLACE(SYS_CONNECT_BY_PATH(rn,'\'),'\') AS p
                      FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM <=5)
                     WHERE LEVEL=5
                     CONNECT BY NOCYCLE LEVEL<=5 AND rn != PRIOR rn
                     )
              )
    SELECT * FROM comb
    WHERE Baker != 5 
          AND Cooper != 1 
          AND Fletcher NOT IN (1,5) 
          AND Miller > Cooper
          AND Smith-Fletcher NOT IN (1,-1)
          AND Cooper-Fletcher NOT IN (1,-1);
    
         BAKER     COOPER   FLETCHER     MILLER      SMITH
    ---------- ---------- ---------- ---------- ----------
             3          2          4          5          1
    
    

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question