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>
June 24th, 2010 at 8:48 am
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
June 24th, 2010 at 12:54 pm
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