OraQA

The following is an interesting puzzle posted by Vector and Jsoftware:

VIER and NEUN represent 4-digit squares, each letter denoting a distinct digit. You are asked to find the value of each, given the further requirement that each uniquely determines the other. The “further requirement” means that of the numerous pairs of answers, choose the one in which each number only appears once in all of the pairs.

——————-SQL solution——————–

SELECT to_number(str) AS VIERNEUN
FROM
(SELECT str,count(*) OVER (PARTITION BY CASE WHEN n = 1 THEN vier ELSE neun END) as cnt
   FROM
   (SELECT DISTINCT str, vier, neun
      FROM
      (SELECT str,vier,neun,
	      count(distinct substr(str,n,1)) OVER (PARTITION BY str) as cnt_char
         FROM
         (
	     WITH DATA AS
             ( SELECT power(LEVEL,2) num FROM dual
               WHERE LEVEL >= ceil(sqrt(1000))
               CONNECT BY LEVEL <= trunc(sqrt(9999))
             )
          SELECT to_char(a.num||b.num) as str,a.num as vier, b.num as neun
          FROM DATA a, DATA b
          WHERE substr(a.num,3,1) = substr(b.num,2,1)
          AND substr(b.num,1,1) = substr(b.num,4,1)
         ) A, (SELECT LEVEL n FROM dual CONNECT BY LEVEL <= length('vierneun')) B
      )
      WHERE  cnt_char = (SELECT count(distinct substr('vierneun',LEVEL,1))
                          FROM dual CONNECT BY LEVEL <= length('vierneun'))
   ) A, (SELECT LEVEL n FROM dual CONNECT BY LEVEL <= 2)
)
GROUP BY str  HAVING sum(cnt) = 2;

  VIERNEUN
----------
  62419409

Elapsed: 00:00:00.31

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.