OraQA

How to solve the Optimal Ghost Puzzle in SQL

Frank Zhou — Thu, 14 Jan 2010 06:47:04 +0000

The following is an interesting puzzle posted by Itasofware on the web.

In the game of Ghost , two players take turns building up an English word from left to right.

Each player adds one letter per turn. The goal is to not complete the spelling of a word: if you add a letter that completes a word (of 4+ letters), or if you add a letter that produces a string that cannot be extended into a word, you lose.

Write a program that allows a user to play Ghost against the computer. The computer should play optimally given the following dictionary: WORD.LST.

Allow the human to play first. If the computer thinks it will win, it should play randomly among all its winning moves; if the computer thinks it will lose, it should play so as to extend the game as long as possible (choosing randomly among choices that force the maximal game length).

—————————————————————————————————————————————————-
Here is the strategy for solving this problem: Try to add a letter to the current input string, so that there is only “one word” left in the whole dictionary for your opponent. Once your opponent adds the next letter to the input string, that will complete the spelling of the word.

Solution:
Copy the word.lst from the web to a word.txt file on the database server ( \tmp directory).


create or replace directory tmp as 'C:\oracle\tmp';

CREATE  TABLE words (str VARCHAR2(38))
     ORGANIZATION EXTERNAL
     (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY tmp
       ACCESS PARAMETERS
       (
         records delimited by newline
         fields terminated by whitespace
         missing field values are null
         ( str
         )
       )
       LOCATION ('word.txt')
     )
     REJECT LIMIT UNLIMITED;

COLUMN input FORMAT A18
COLUMN destination FORMAT A28
COLUMN next_char   FORMAT A12

variable input varchar2(38)
SQL> exec :input :='tar'

PL/SQL procedure successfully completed.

------------------------------SQL Solution----------------- 

WITH data AS
(SELECT str, length(str) as len, :input as input, length(:input) as in_len,
        case when str like :input||'_'
             then  str
             end as flag_str
 FROM words
 WHERE str LIKE :input||'%' and length(str) >=4
),
flag_data as
(
SELECT flag_str FROM data WHERE flag_str is not null
),
Filter_data as
(
SELECT distinct a.str FROM data a , flag_data b
WHERE substr(a.str, 1, length(b.flag_str)) = b.flag_str
),
Base_data as
(SELECT str, len , in_len, input FROM data
 WHERE str not in ( SELECT str FROM Filter_data)
)
SELECT input, destination, next_char
FROM
(SELECT input, str as destination, substr(str, in_len+1, 1) as next_char
  FROM
 (SELECT a.str, a.len, input, a.in_len,
         case when mod(a.len, 2) = 0 then 1
              else case when  REGEXP_LIKE (a.str, '^'||input||'[[:alpha:]]{2}$')
                        then case when
                                 (SELECT count(*) FROM data
                                  WHERE REGEXP_LIKE (str, '^'||substr(a.str,0,length(a.input)+1)||'[[:alpha:]]{2,}$')
                                  and  not REGEXP_LIKE (str, '^'||a.str||'[[:alpha:]]+$')
                                  ) = 0
                                  then 4
                                  else 3
                             end
                        else  2
                    end
         end as flag
  FROM Base_data a
 ) ORDER BY flag desc , case when flag = 1 then len else 0 end desc
)
WHERE ROWNUM = 1;

INPUT              DESTINATION                  NEXT_CHAR
------------------ ---------------------------- ------------
tar                targe                        g

SQL> exec :input := 'bot'

SQL> /

INPUT              DESTINATION                  NEXT_CHAR
------------------ ---------------------------- ------------
bot                botch                        c

SQL> exec :input := 'aba'

SQL> /

INPUT              DESTINATION                  NEXT_CHAR
------------------ ---------------------------- ------------
aba                abamp                        m

How to determine the table creation order in SQL

Frank Zhou — Wed, 29 Jul 2009 05:55:52 +0000

The following SQL pattern can be used to determine the table creation order for a set of tables with parent-child relationship.
This SQL solution is built on top of a sql from the AskTom website, it assumes no circular dependency between the tables

create table p    ( x int primary key );
create table c1   ( x primary key references p );
create table c2   ( x primary key references c1 );
create table c3   ( x primary key references c2 );
create table c4   ( x primary key references c2 );
create table p_t ( x int primary key );
create table c_t ( x primary key references p_t );
create table c_t2 ( x primary key references c_t );
create table c_t3 ( x primary key references c_t2 );
create table c_t4 ( x primary key references c_t3 );
create table c_t5 ( x primary key references c_t4 );
create table m3   (x int, y int, z int );
ALTER TABLE m3 ADD (CONSTRAINT x unique (x) );
ALTER TABLE m3 ADD (CONSTRAINT y unique (y) );
ALTER TABLE m3 ADD (CONSTRAINT z unique (z) );
ALTER TABLE m3 ADD (CONSTRAINT cx FOREIGN KEY (x) REFERENCES c_t5 (x));
ALTER TABLE m3 ADD (CONSTRAINT cy FOREIGN KEY (y) REFERENCES c3 (x));
ALTER TABLE m3 ADD (CONSTRAINT cz FOREIGN KEY (z) REFERENCES c4(x));

variable input varchar2(1028) exec :input := 'P,C1,C2,C3,C4,M3,P_T,C_T,C_T2,C_T3,C_T4,C_T5'

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

With table_list AS
(
select doc.extract('/X/text( )').getStringVal() as t_name
from
TABLE(xmlSequence(extract(XMLType(''||
      REGEXP_REPLACE(:input, ',', '')||''),'/DOC/X'))) doc
)
select table_name, row_number( ) over (order by le) table_creation_order
from
(select distinct table_name, le
 from
 (select le, max(le) over (partition by table_name) max_level, table_name
  from
  (select table_name, level le
   from
   (select table_name, constraint_name pkey,
           to_char(null) fkey, to_char(null) r_con
    from   user_constraints
    where  constraint_type in ('U', 'P')
    and    owner = USER
    and table_name in (select  t_name from table_list)
    union all
    select a.table_name,
           a.constraint_name pkey,
           b.constraint_name fkey,
           b.r_constraint_name r_con
    from user_constraints a, user_constraints b
    where a.table_name = b.table_name
      and a.constraint_type in ('U', 'P')
      and b.constraint_type = 'R'
      and a.owner = USER
      and b.owner = a.owner
      and a.table_name in (select  t_name from table_list)
  )
  start with fkey is null
  connect by nocycle prior pkey = r_con
  order SIBLINGS by table_name DESC
  )
  )
  where le = max_level
);

TABLE_NAME TABLE_CREATION_ORDER

—————————— ——————–

P 1

P_T 2

C1 3

C_T 4

C2 5

C_T2 6

C_T3 7

C3 8

C4 9

C_T4 10

C_T5 11

M3 12

12 rows selected.

How to solve the Largest 7-Digit Number Puzzle in SQL

Frank Zhou — Mon, 19 Jan 2009 00:07:31 +0000

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

Work out the largest 7-digit number you can applying 2 rules only:

1) every digit in the number must be able to be divided into the number;
2) no digit can be repeated.

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

SELECT  str_num
FROM
(SELECT str_num
  FROM
  (SELECT str_num, to_number(substr(str_num,LEVEL,1)) n1
   FROM
   (SELECT to_number(utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(
           replace(sys_connect_by_path(n,','),',')))))  str_num
    FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL <10)
    WHERE LEVEL = 7
    CONNECT BY NOCYCLE PRIOR n != n
    AND LEVEL < 8
    AND CASE LEVEL
        WHEN 2
	THEN CASE WHEN n in (2, 6, 8)
	          THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n in (4)
		  THEN CASE WHEN MOD(to_number(PRIOR N||N), n) = 0 THEN 1 END
		  WHEN n = 5
		  THEN CASE WHEN PRIOR N in (5) THEN 1 END
		  ELSE 1 END
	WHEN 3
	THEN CASE WHEN n in (2, 6)
		  THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n in (4)
		  THEN CASE WHEN MOD(to_number(PRIOR N||N), n) = 0 THEN 1 END
		  WHEN n in (8)
		  THEN CASE WHEN MOD(to_number(CONNECT_BY_ROOT(n)||PRIOR N||N),n)= 0
			    THEN 1 END
	          WHEN n = 5
		  THEN CASE WHEN CONNECT_BY_ROOT(n) in (5) THEN 1 END
		  ELSE 1 END
        WHEN 4
        THEN CASE WHEN n in (2, 4, 6 ,8)
		  THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n = 5
                  THEN CASE WHEN CONNECT_BY_ROOT(n) in (5) THEN 1 END
		  ELSE 1 END
	WHEN 5
        THEN CASE WHEN n in (2, 4, 6 ,8)
		  THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n = 5
	          THEN CASE WHEN CONNECT_BY_ROOT(n) in (5 ) THEN 1 END
		  ELSE 1 END
	WHEN 6
        THEN CASE WHEN n in (2, 4, 6 ,8)
		  THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n = 5
                  THEN CASE WHEN CONNECT_BY_ROOT(n) in (5) THEN 1 END
		  ELSE 1 END
        WHEN 7
        THEN CASE WHEN n in (2, 4, 6 ,8)
		  THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n = 5
		  THEN CASE WHEN CONNECT_BY_ROOT(n) in (5) THEN 1 END
		  ELSE 1 END
        ELSE 1 END = 1
      )
      CONNECT BY PRIOR str_num = str_num
      AND LEVEL <= length(str_num)
      AND PRIOR DBMS_RANDOM.STRING ('P',20) IS NOT NULL
   )
   GROUP BY str_num
   HAVING count(CASE WHEN mod(str_num, n1) = 0
                     THEN 1 END ) = length(str_num)
   ORDER BY str_num DESC
)
WHERE ROWNUM  = 1;

STR_NUM
———-
9867312

How to solve the Find Maximum Possible Product Puzzle in SQL

Frank Zhou — Fri, 19 Dec 2008 05:40:16 +0000

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

Use all the digits from 1 to 9 without repeating, to form two numbers such that their product is maximum. A digit used should be unique across both the numbers. For example, the numbers formed could be 1234 and 56789.

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

SELECT n1, n2, product as MAX_PRODUCT
FROM
(SELECT max(n1 * n2 ) over ( ) max_product, n1, n2, n1 * n2 product
 FROM
 (
  WITH DATA AS
  (SELECT replace(sys_connect_by_path(num, ','), ',') str_num
   FROM (SELECT LEVEL num FROM dual CONNECT BY LEVEL <10)
   WHERE LEVEL = 9
   CONNECT BY NOCYCLE PRIOR num != num
   AND LEVEL <= 9
   AND CONNECT_BY_ROOT(num) = 9
  )
  SELECT to_number(substr(str_num,0,LEVEL)) n1, to_number(substr(str_num,LEVEL+1)) n2
  FROM DATA
  CONNECT BY PRIOR str_num = str_num
  AND LEVEL <= length(str_num)-1
  AND PRIOR DBMS_RANDOM.STRING ('P',20) IS NOT NULL
 )
)
WHERE max_product = product;

        N1                  N2                   MAX_PRODUCT
----------               ----------                -----------
      9642                87531                      843973902

How to solve the Find all 3 digit numbers have the digital sum of nine Puzzle in SQL

Frank Zhou — Fri, 19 Dec 2008 05:37:54 +0000

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

Find out how many 3 digit numbers have the digital sum of nine?

————————SQL Solution————————-

SELECT to_number(str_num) as str_num,
       CASE WHEN ROWNUM = 1
	    THEN count(*) over ( )
       END AS CNT
FROM
(SELECT replace(sys_connect_by_path(num, ','), ',') str_num
 FROM (SELECT LEVEL - 1 num FROM dual CONNECT BY LEVEL <=10)
 WHERE LEVEL = 3
 CONNECT BY LEVEL < 4
 AND CASE LEVEL
          WHEN 2
          THEN CASE WHEN (PRIOR num != 0) AND (PRIOR num + num <= 9)
		    THEN 1 END
	  WHEN 3
	  THEN CASE WHEN CONNECT_BY_ROOT(num) + PRIOR num + num = 9
		    THEN 1 END
	  ELSE 1  END = 1
 AND PRIOR dbms_random.string('p', 20) IS NOT NULL
);

STR_NUM CNT
———- ———-
108 45
117
126
135
144
153
162
171
180
207
216
225
234
243
252
261
270
306
315
324
333
342
351
360
405
414
423
432
441
450
504
513
522
531
540
603
612
621
630
702
711
720
801
810
900

45 rows selected.

SQL>

Is it best to use SQL or PL/SQL?

Laurent Schneider — Mon, 10 Nov 2008 06:11:40 +0000

The issue is very often related to context switching. A context switch is when you write plsql code that contains sql statement, or sql that contains plsql functions.

select emp,dbms_random.value from emp;

implies a context switch

begin update emp set sal=0; end;

implies a context switch too.

However, it is sometimes better practice to have a few context switches rather than a way too much complex SQL query, that nobody will ever be able to maintain but you.

Ok, let’s try to print the equation abc+def=ghi where abcdefghi are digits from 1 to 9 with no duplicate.

SQL> create or replace function f return sys.odcivarchar2list pipelined deterministic is
  2    a7 number;
  3    a8 number;
  4    a9 number;
  5  begin
  6    -- How to solve the 3 Digits Plus 3 Digits Puzzle
  7    -- where a1a2a3 + a4a5a6 = a7a8a9 and each a is an unique digit from 1 to 9
  8    for a1 in 1..9 loop
  9      for a2 in 1..9 loop
 10        if a2 not in (a1) then
 11          for a3 in 1..9 loop
 12            if a3 not in (a1,a2)
 13            then
 14              for a4 in 1..9 loop
 15                if a4 not in (a1,a2,a3) and a1+a4<10
 16                then
 17                  for a5 in 1..9 loop
 18                    if a5 not in (a1,a2,a3,a4) and a1*10+a2+a4*10+a5<100
 19                    then
 20                      for a6 in 1..9 loop
 21                        if a6 not in (a1,a2,a3,a4,a5) and a1*100+a2*10+a3+a4*100+a5*10+a6<1000
 22                        then
 23                          a9:=a3+a6;
 24                          a8:=trunc(a9/10);
 25                          a9:=a9-a8*10;
 26                          if a9 not in (a1,a2,a3,a4,a5,a6,0)
 27                          then
 28                            a8:=a8+a2+a5;
 29                            a7:=trunc(a8/10);
 30                            a8:=a8-a7*10;
 31                            if a8 not in (a1,a2,a3,a4,a5,a6,a9,0)
 32                            then
 33                              a7:=a7+a1+a4;
 34                              if a7 not in (a1,a2,a3,a4,a5,a6,a8,a9,0)
 35                              then
 36                                pipe row(a1||a2||a3||'+'||a4||a5||a6||'='||a7||a8||a9);
 37                              end if;
 38                            end if;
 39                          end if;
 40                        end if;
 41                      end loop;
 42                    end if;
 43                  end loop;
 44                end if;
 45              end loop;
 46            end if;
 47          end loop;
 48        end if;
 49      end loop;
 50    end loop;
 51  end;
 52  /

Function created.

SQL>
SQL> set timi on
SQL> select column_value as Equation_str, decode(rownum,1,count(*) over()) cnt from table(f);
124+659=783                 336
125+739=864
...
784+152=936

336 rows selected.

Elapsed: 00:00:00.96
SQL>
SQL> SELECT num1||' + '||num2||' = '|| num3 as Equation_str,
  2         CASE WHEN lag(cnt) over (order by num1)  cnt
  3                OR lag(cnt) over (order by num1) IS NULL
  4       THEN  cnt END AS Counter
  5  FROM
  6  (SELECT num1, num2, num3, count(*) over ( ) as cnt
  7   FROM
  8   (SELECT substr(num,1,3) num1, substr(num,4,3) num2, substr(num,7,3) num3
  9    FROM
 10    (SELECT replace(sys_connect_by_path(n,','), ',') num
 11     FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL <=9)
 12     WHERE LEVEL = 9
 13     CONNECT BY NOCYCLE PRIOR n != n
 14     AND LEVEL <=9
 15     AND CASE LEVEL
 16              WHEN 1
 17              THEN CASE WHEN n < 9 THEN 1 END
 18              WHEN 4
 19              THEN CASE WHEN n  + CONNECT_BY_ROOT(n) = 3 THEN 1 END
 23                        ELSE CASE WHEN n >= CONNECT_BY_ROOT(n) + 1 THEN 1 END
 24                   END
 25             ELSE 1 END = 1
 26     )
 27    )
 28   WHERE to_number(num1)     + to_number(num2)       =  to_number(num3)
 29   );
124 + 659 = 783             336
125 + 739 = 864
...
784 + 152 = 936

336 rows selected.

Elapsed: 00:00:22.91

hmm, do not you just love select * from table(f); ? Just a sinister scheme to seduce you.

How to solve the 3 Digits Plus 3 Digits Puzzle in SQL

Frank Zhou — Mon, 03 Nov 2008 04:06:32 +0000

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

Use the digits 1 to 9 only once in a sum that must be a three-digit number plus another three-digit number to equal another three-digit number. Each digit can only be used once, but all must be used.

———————————-SQL Solution——————————-

SELECT num1||' + '||num2||' = '|| num3 as Equation_str,
       CASE WHEN lag(cnt) over (order by num1) <> cnt
              OR lag(cnt) over (order by num1) IS NULL
     THEN  cnt END AS Counter
FROM
(SELECT num1, num2, num3, count(*) over ( ) as cnt
 FROM
 (SELECT substr(num,1,3) num1, substr(num,4,3) num2, substr(num,7,3) num3
  FROM
  (SELECT replace(sys_connect_by_path(n,','), ',') num
   FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL <=9)
   WHERE LEVEL = 9
   CONNECT BY NOCYCLE PRIOR n != n
   AND LEVEL <=9
   AND CASE LEVEL
            WHEN 1
            THEN CASE WHEN n < 9 THEN 1 END
            WHEN 4
            THEN CASE WHEN n  + CONNECT_BY_ROOT(n) < 10 THEN 1 END
            WHEN 7
            THEN CASE WHEN CONNECT_BY_ROOT(n) =1
                      THEN CASE WHEN n >= 3 THEN 1 END
                      ELSE CASE WHEN n >= CONNECT_BY_ROOT(n) + 1 THEN 1 END
                 END
           ELSE 1 END = 1
   )
  )
 WHERE to_number(num1)  + to_number(num2)  =  to_number(num3)
 );

EQUATION_STR                                               COUNTER
------------------------------------------                  ----------
124 + 659 = 783                                              336
125 + 739 = 864
127 + 359 = 486
127 + 368 = 495
128 + 367 = 495
128 + 439 = 567
129 + 357 = 486
129 + 735 = 864
129 + 654 = 783
129 + 438 = 567
134 + 658 = 792
.....................................................
.....................................................
.....................................................
.....................................................
.....................................................

762 + 183 = 945
763 + 182 = 945
782 + 154 = 936
782 + 163 = 945
783 + 162 = 945
784 + 152 = 936

336 rows selected.

How to solve the Closest Palindromic Dates Puzzle in SQL

Frank Zhou — Mon, 03 Nov 2008 04:01:48 +0000

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

Using the abbreviation date.month.year (the last two digits of the year), what are the 2 palindromic dates (of any number of digits) closest together in the 1900s?

———————————-SQL Solution——————————-

SELECT date_str, next_date, min_diff_days
FROM
(SELECT min(diff_days) over ( ) min_diff_days, date_str, next_date, diff_days
 FROM
 (SELECT lead(date_str) over (order by pal_date) next_date, date_str,
         lead(pal_date) over (order by pal_date) - pal_date  as diff_days
  FROM
  (SELECT to_char(to_date('01/01/1900','DD/MM/YYYY')-1+ LEVEL,'fmDD.MM.fmYY') date_str,
          to_date('01/01/1900','DD/MM/YYYY')-1  + LEVEL pal_date
   FROM dual
   CONNECT BY to_date('01/01/1900','MM/DD/YYYY')+LEVEL <= to_date('01/01/2000','MM/DD/YYYY')
  )
  WHERE replace(date_str,'.') =
       utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw((replace(date_str,'.')))))
 )
)
WHERE diff_days = min_diff_days;

DATE_STR     NEXT_DAT     MIN_DIFF_DAYS
——–                    ——–        ————-
29.8.92         2.9.92                 4

Elapsed: 00:00:00.41

How to solve the 1000 $1 Bills in 10 Envelopes Puzzle in SQL

Frank Zhou — Mon, 13 Oct 2008 01:44:30 +0000

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

You are given 1000 one dollar bills and 10 envelopes. Put the bills
into the envelopes in such a way that someone can ask you for any
amount of money from $1 to $1000 (examples $532, $619, $88, etc.)
and you can give it to them through a combination of the envelopes.

The following SQL pattern can be used to solve this puzzle, it also proves
that the result is correct.

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

SELECT   ROW_NUMBER () OVER (ORDER BY num) envelope_num,
         num dollars,
         MIN from_min,
         MAX to_max,
         cnt combo_cnt
    FROM (WITH DATA AS
               (SELECT num
                  FROM (SELECT CASE
                                  WHEN SUM (num) OVER (ORDER BY num) > 1000
                                     THEN   num
                                          - (  SUM (num) OVER (ORDER BY num)
                                             - 1000
                                            )
                                  ELSE num
                               END AS num
                          FROM (SELECT     POWER (2, LEVEL - 1) AS num
                                      FROM DUAL
                                CONNECT BY POWER (2, LEVEL - 1) <= 1000))),
               data_check AS
               (SELECT MAX (num) MAX,
                       MIN (num) MIN,
                       COUNT (*) cnt
                  FROM (SELECT DISTINCT SUM
                                           (TO_NUMBER (REGEXP_SUBSTR (str,
                                                                      '[^,]+',
                                                                      1,
                                                                      LEVEL
                                                                     )
                                                      )
                                           ) num
                                   FROM (SELECT     SYS_CONNECT_BY_PATH (num,
                                                                         ','
                                                                        ) str
                                               FROM (SELECT num
                                                       FROM DATA)
                                         CONNECT BY PRIOR num < num)
                             CONNECT BY PRIOR str = str
                                    AND REGEXP_SUBSTR (str, '[^,]+', 1, LEVEL) IS NOT NULL
                                    AND PRIOR DBMS_RANDOM.STRING ('p', 20) IS NOT NULL
                               GROUP BY str))
          SELECT MAX,
                 MIN,
                 cnt,
                 num
            FROM data_check FULL OUTER JOIN DATA ON (data_check.MIN = DATA.num
                                                    )
                 )
ORDER BY num NULLS FIRST;

SQL> /

ENVELOPE_NUM DOLLARS FROM_MIN TO_MAX COMBO_CNT ------------ ---------- ---------- ---------- ---------- 1 1 1 1000 1000 2 2 3 4 4 8 5 16 6 32 7 64 8 128 9 256 10 489

10 rows selected.

Elapsed: 00:00:00.43

How to solve the Find the Seven Numbers Puzzle in SQL

Frank Zhou — Mon, 13 Oct 2008 01:43:08 +0000

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