OraQA
How to solve the Multiply Two Numbers (No Zeros) to make 5 Billion Puzzle in SQL
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
How to solve The Remainder Puzzle in SQL
The following is an interesting problem posted by mathforum.org:
Find the smallest number, M, such that:
M/10 leaves a remainder of 9;
M/9 leaves a remainder of 8;
M/8 leaves a remainder of 7;
M/7 leaves a remainder of 6;
M/6 leaves a remainder of 5;
M/5 leaves a remainder of 4;
M/4 leaves a remainder of 3;
M/3 leaves a remainder of 2;
M/2 leaves a remainder of 1.
variable input number
exec :input := 10
——————-SQL Solution —————–
SELECT MIN (num)
FROM (SELECT num
FROM (SELECT LEVEL num
FROM DUAL
CONNECT BY LEVEL <= (SELECT EXP (SUM (LN (l)))
FROM (SELECT LEVEL l
FROM DUAL
CONNECT BY LEVEL <= :input))) a,
(SELECT LEVEL div
FROM DUAL
CONNECT BY LEVEL <= :input) b
WHERE MOD (a.num, b.div) = b.div - 1
GROUP BY num
HAVING COUNT (*) = :input)
MIN(NUM)
----------
2519
Create Alphanumeric Sequence in SQL?
How can I create an alphanumeric sequence in SQL (without using the decode function and all)? I’m using SQL*Plus. Thanks.
How Do I Verify that the Redo File is Being Used?
I have a table on which when I run a procedure for computation through sql*plus it runs fine. But the same when I run it from my application server the redo log file gets full. I don’t understand the reason behind this. So I changed the logging property of the table to no so that it doesn’t write anything in redo log. How do i verify that the redo file is being used by the table or not apart from the processing result?
How to Scan and Save Images into Oracle Database?
I have a requirement to scan and save a photograph through a program without user interaction in Oracle 10g. How can I do this job?
I m using TWAIN program but it throws a window error, and I am not able to save it in specific directory.
Plz reply.
Thanks & Regards
Sunita
Regarding Sequence Numbers
Suppose I have inserted, say 10 rows into a table using sequence numbers (1-10). If I delete the 5th record from the table. Will the sequence number from 6th record get affected or will it be the same?
If it is same then please suggest some way so that the remaining records get updated, ie it should be (1-9) in sequence not (1-4 & 6-10).
Blocking IP Address from Logging on to Database Server?
Can I block a particular IP address to prevent it from logging into an Oracle 9i server?
How to solve the Ways To List 1, 2, … 10 Out of Order problem in SQL
The following is an interesting problem posted by mathforum.org:
How many ways are there to list the numbers one through ten so that
no number appears in its own position (i.e. 1 is not first in the
list, 2 is not second, three is not third, etc.)?
——————-SQL Solution ——————
SELECT count(*)
FROM
(SELECT LEVEL num FROM dual CONNECT BY LEVEL<=10)
WHERE LEVEL= 10
CONNECT BY NOCYCLE num != PRIOR num
AND num != LEVEL
AND CONNECT_BY_ROOT(num) != 1
AND LEVEL <=10;
COUNT(*)
———-
1334961
How to generate all strings of balanced parentheses in SQL
The following SQL pattern can be used to generate all strings of balanced parentheses.
variable input number
—————————-SQL Solution ————————–
SELECT str
FROM
(SELECT CASE WHEN instr(str,')(') = 0
THEN 'F'
ELSE 'T' END flag, str FROM (SELECT rpad('()', &input * 2,'()') str from dual )
)
MODEL
DIMENSION BY (0 dim)
MEASURES(str, flag)
RULES ITERATE (10000) UNTIL (instr(str[ITERATION_NUMBER+1],')(') = 0 OR flag[0] = 'F')
(
str[ITERATION_NUMBER+1] =
CASE WHEN instr(str[cv()-1],')(') >0
THEN CASE WHEN instr(substr(str[cv()-1],1,instr(str[cv()-1],')(')-1),'()')>0
THEN rpad('()',least(length(replace(substr(str[cv()-1],1,instr(str[cv()-1],')(')-1),')')),
length(replace(substr(str[cv()-1],1,instr(str[cv()-1],')(')-1),'(')))*2,'()')
||CASE WHEN length(replace(substr(str[cv()-1],1,instr(str[cv()-1],')(')-1),')')) =
least(length(replace(substr(str[cv()-1],1,instr(str[cv()-1],')(')-1),')')),
length(replace(substr(str[cv()-1],1,instr(str[cv()-1],')(')-1),'(')))
THEN rpad(')', abs(length(replace(substr(str[cv()-1],1,instr(str[cv()-1],')(')-1),')'))
- length(replace(substr(str[cv()-1],1,instr(str[cv()-1],')(')-1),'(')) ),')')
ELSE rpad('(', abs(length(replace(substr(str[cv()-1],1,instr(str[cv()-1],')(')-1),')'))
- length(replace(substr(str[cv()-1],1,instr(str[cv()-1],')(')-1),'('))),'(')
END||'()'||substr(str[cv()-1], instr(str[cv()-1],')(')+2 )
ELSE substr(str[cv()-1],1,instr(str[cv()-1],')(')-1)||'()'||
substr(str[cv()-1],instr(str[cv()-1],')(')+2)
END
END
);
Enter value for input: 5
old 5: ELSE 'T' END flag, str FROM (SELECT rpad('()', &input * 2,'()') str from dual )
new 5: ELSE 'T' END flag, str FROM (SELECT rpad('()', 5 * 2,'()') str from dual )
STR
----------
()()()()()
(())()()()
()(())()()
(()())()()
((()))()()
()()(())()
(())(())()
()(()())()
(()()())()
((())())()
()((()))()
STR
----------
(()(()))()
((()()))()
(((())))()
()()()(())
(())()(())
()(())(())
(()())(())
((()))(())
()()(()())
(())(()())
()(()()())
STR
----------
(()()()())
((())()())
()((())())
(()(())())
((()())())
(((()))())
()()((()))
(())((()))
()(()(()))
(()()(()))
((())(()))
STR
----------
()((()()))
(()(()()))
((()()()))
(((())()))
()(((())))
(()((())))
((()(())))
(((()())))
((((()))))
42 rows selected.
How to determine if the formula string contains balanced pairs of parentheses in SQL
The following SQL pattern can be used to determine whether the formula/expression string contains balanced pairs of parentheses.
create table test as select '( (1+2)*8 + ( (3+4)+(5+6) ) /9 ) * 9- (7+8)' str_num from dual union all select '( (1+2)+ (3+4) * 5 ) +6 ) ' str_num from dual ;
——————-SQL Solution —————-
SELECT old_str_num, flag as balanced_parentheses
FROM
(
SELECT str_num as old_str_num, rownum rn,
regexp_replace(str_num,'[^()]') str_num
FROM test
)
MODEL
PARTITION BY (rn)
DIMENSION BY (0 dim)
MEASURES(old_str_num, str_num, CAST(NULL AS VARCHAR2(1)) flag )
RULES ITERATE (10000) UNTIL (str_num[0] IS NULL OR flag[0] = 'F')
(
flag[0] = CASE WHEN regexp_like(str_num[cv()],'^\(.*\)$')AND
(length(regexp_replace(str_num[cv()], '\(')) =
length(regexp_replace(str_num[cv()], '\)'))
)
THEN 'T'
ELSE 'F' END,
str_num[0] = CASE WHEN flag[0] = 'T'
THEN regexp_replace(str_num[cv()], '\(\)')
END
);
OLD_STR_NUM BALANCED_PARENTHESES
-------------------------------------------- -------------------------
( (1+2)*8 + ( (3+4)+(5+6) ) /9 ) * 9- (7+8) T
( (1+2)+ (3+4) * 5 ) +6 ) F