OraQA

 The following is an interesting problem posted by mathforum.org:

What two numbers - neither of them containing zeros - can be multiplied together to make 5,000,000,000?

The following SQL puzzle solution is built on top of a neat sql trick that I have learned from the SQL_RU Forum.

variable input number
exec :input := 5000000000 ;
———————————SQL Solution —————————–

SELECT str_eq || ' = ' || XMLQuery( str_eq RETURNING CONTENT).getnumberval()  as output
  FROM (SELECT RTRIM
                  (LTRIM
                      (REGEXP_REPLACE
                            (XMLAGG (XMLELEMENT (x, CAST (num AS INTEGER)) ORDER BY num
                                    ),
                             '||',
                             ' * '
                            ),
                       ' * '
                      ),
                   ' * '
                  ) AS str_eq
          FROM (SELECT DISTINCT EXP (SUM (LN (num)) OVER (PARTITION BY num))
                                                                          num
                           FROM (WITH data1 AS
                                      (SELECT     LEVEL l
                                             FROM DUAL
                                       CONNECT BY LEVEL <= SQRT (:input)),
                                      data2 AS
                                      (SELECT l num1,
                                              :input / l num2
                                         FROM data1
                                        WHERE MOD (:input, l) = 0),
                                      data3 AS
                                      (SELECT num1
                                         FROM data2
                                       UNION
                                       SELECT num2 AS num1
                                         FROM data2),
                                      data4 AS
                                      (SELECT num1,
                                              (SELECT MIN (num1)
                                                 FROM data3 data3_1
                                                WHERE data3_1.num1
                                                      / data3.num1 =
                                                         TRUNC (  data3_1.num1
                                                                / data3.num1
                                                               )
                                                  AND data3_1.num1 >
                                                                    data3.num1)
                                                                     data_num
                                         FROM data3)
                                 SELECT     data_num / num1 AS num
                                       FROM data4
                                      WHERE data_num IS NOT NULL
                                 START WITH num1 = 1
                                 CONNECT BY num1 = PRIOR data_num)));

 
OUTPUT
--------------------------------------------------------------------------------
512 * 9765625 = 5000000000

Elapsed: 00:00:01.32

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