How to solve the Find the Seven Numbers Puzzle in SQL
October 12th, 2008 By Frank Zhou
The following is an interesting problem posted by mathforum.org:
There are seven numbers, A, B, C, D, E, F, and G.
When all these numbers except A are added together, 116 is obtained.
When all these numbers except B are added together, 122 is obtained
When all these numbers except C are added together, 123 is obtained
When all these numbers except D are added together, 108 is obtained
When all these numbers except E are added together, 110 is obtained
When all these numbers except F are added together, 119 is obtained
When all these numbers except G are added together, 100 is obtained
Find each of the seven numbers.
COLUMN letter FORMAT A6
—————————–SQL Solution———————————-
SELECT TRANSLATE (new_str, CHR (0) || str, CHR (0)) letter,
AVG - num AS num
FROM (WITH DATA AS
(SELECT TRANSLATE (old_str, CHR (0) || mis_char, CHR (0))
AS str,
num
FROM (SELECT 'A' AS mis_char,
116 AS num
FROM DUAL
UNION ALL
SELECT 'B' AS mis_char,
122 AS num
FROM DUAL
UNION ALL
SELECT 'C' AS mis_char,
123 AS num
FROM DUAL
UNION ALL
SELECT 'D' AS mis_char,
108 AS num
FROM DUAL
UNION ALL
SELECT 'E' AS mis_char,
110 AS num
FROM DUAL
UNION ALL
SELECT 'F' AS mis_char,
119 AS num
FROM DUAL
UNION ALL
SELECT 'G' AS mis_char,
100 AS num
FROM DUAL),
(SELECT 'ABCDEFG' old_str
FROM DUAL))
SELECT str,
num,
new_str,
AVG
FROM (SELECT str,
num
FROM DATA),
(SELECT XMLAGG (XMLELEMENT (x, str) ORDER BY NULL).EXTRACT
('/X/text()').getstringval
()
AS new_str,
MAX (AVG) AVG
FROM (SELECT str,
MAX (num) / COUNT (*) AS AVG
FROM (SELECT SUBSTR (str, LEVEL, 1) str,
num
FROM (SELECT XMLAGG
(XMLELEMENT (x, str) ORDER BY NULL
).EXTRACT
('/X/text()').getstringval
()
str,
SUM (num) AS num
FROM (SELECT str,
num
FROM DATA))
CONNECT BY PRIOR str = str
AND LEVEL <= LENGTH (str)
AND PRIOR DBMS_RANDOM.STRING ('P',
20
) IS NOT NULL)
GROUP BY str)))
ORDER BY letter;
LETTER NUM ------ ---------- A 17 B 11 C 10 D 25 E 23 F 14 G 33
7 rows selected.
Elapsed: 00:00:00.31