Tag Archives: quiz

Funny friday Oracle SQL quiz: query running N seconds

Posted on by Posted in curious, Funny, oracle, quiz, SQL 1,757 Page views Leave a comment

Write a pure SQL query with PL/SQL that stop after :N seconds, where :N is a bind variable.

My solution

[sourcecode language=”sql”]
with v(start_hsecs, delta, flag) as (
select
hsecs as start_hsecs, 0 as delta, 1 as flag
from v$timer
union all
select
v.start_hsecs,
(t.hsecs-v.start_hsecs)/100 as delta,
case when (t.hsecs-v.start_hsecs)/100 > :N /* seconds */ then v.flag*-1 else v.flag+1 end as flag
from v, v$timer t
where v.flag>0 and t.hsecs>=v.start_hsecs
)
select delta
from v
where 0>flag
/
–end
[/sourcecode]

[collapse]
SQL> var N number
SQL> exec :N := 3 /* seconds */;

PL/SQL procedure successfully completed.

SQL> select...

     DELTA
----------
      3.01

1 row selected.

Elapsed: 00:00:03.01

Another my solution using sys.standard.current_timestamp, so some internal pl/sql…:

select count(*) from dual 
connect by sys.standard.current_timestamp - current_timestamp <= interval'3'second;

8 queens chess problem: solution in Oracle SQL

Posted on by Posted in curious, oracle, SQL 1,866 Page views 1 Comment

This is just another solution of this problem for a chessboard, but you can choose any size of the checkerboard:

with 
 t as (select level i, cast(level as varchar2(1)) c from dual connect by level<=&d)
,x(l,s,n) as (
       select 1 l, c s, chr(97)||c||' ' from t
       union all
       select l+1, x.s||t.c, n||chr(97+l)||i||' '
       from x
            join t
                 on instr(s,c)=0
                    and not exists(select 0 from dual 
                                   where L+1 - t.i = level - substr(s,level,1)
                                      or L+1 + t.i = level + substr(s,level,1)
                                   connect by level<=length(s))
       where L<&d
 )
select n
from x
where l=&d

8x8:

[sourcecode language=”sql”]
SQL> @tests/f
Size[8]: 8

N
——————————————————————————–
a1 b5 c8 d6 e3 f7 g2 h4
a1 b6 c8 d3 e7 f4 g2 h5
a1 b7 c4 d6 e8 f2 g5 h3
a1 b7 c5 d8 e2 f4 g6 h3
a2 b4 c6 d8 e3 f1 g7 h5
a2 b5 c7 d1 e3 f8 g6 h4
a2 b5 c7 d4 e1 f8 g6 h3
a2 b6 c1 d7 e4 f8 g3 h5
a2 b6 c8 d3 e1 f4 g7 h5
a2 b7 c3 d6 e8 f5 g1 h4
a2 b7 c5 d8 e1 f4 g6 h3
a2 b8 c6 d1 e3 f5 g7 h4
a3 b1 c7 d5 e8 f2 g4 h6
a3 b5 c2 d8 e1 f7 g4 h6
a3 b5 c2 d8 e6 f4 g7 h1
a3 b5 c7 d1 e4 f2 g8 h6
a3 b5 c8 d4 e1 f7 g2 h6
a3 b6 c2 d5 e8 f1 g7 h4
a3 b6 c2 d7 e1 f4 g8 h5
a3 b6 c2 d7 e5 f1 g8 h4
a3 b6 c4 d1 e8 f5 g7 h2
a3 b6 c4 d2 e8 f5 g7 h1
a3 b6 c8 d1 e4 f7 g5 h2
a3 b6 c8 d1 e5 f7 g2 h4
a3 b6 c8 d2 e4 f1 g7 h5
a3 b7 c2 d8 e5 f1 g4 h6
a3 b7 c2 d8 e6 f4 g1 h5
a3 b8 c4 d7 e1 f6 g2 h5
a4 b1 c5 d8 e2 f7 g3 h6
a4 b1 c5 d8 e6 f3 g7 h2
a4 b2 c5 d8 e6 f1 g3 h7
a4 b2 c7 d3 e6 f8 g1 h5
a4 b2 c7 d3 e6 f8 g5 h1
a4 b2 c7 d5 e1 f8 g6 h3
a4 b2 c8 d5 e7 f1 g3 h6
a4 b2 c8 d6 e1 f3 g5 h7
a4 b6 c1 d5 e2 f8 g3 h7
a4 b6 c8 d2 e7 f1 g3 h5
a4 b6 c8 d3 e1 f7 g5 h2
a4 b7 c1 d8 e5 f2 g6 h3
a4 b7 c3 d8 e2 f5 g1 h6
a4 b7 c5 d2 e6 f1 g3 h8
a4 b7 c5 d3 e1 f6 g8 h2
a4 b8 c1 d3 e6 f2 g7 h5
a4 b8 c1 d5 e7 f2 g6 h3
a4 b8 c5 d3 e1 f7 g2 h6
a5 b1 c4 d6 e8 f2 g7 h3
a5 b1 c8 d4 e2 f7 g3 h6
a5 b1 c8 d6 e3 f7 g2 h4
a5 b2 c4 d6 e8 f3 g1 h7
a5 b2 c4 d7 e3 f8 g6 h1
a5 b2 c6 d1 e7 f4 g8 h3
a5 b2 c8 d1 e4 f7 g3 h6
a5 b3 c1 d6 e8 f2 g4 h7
a5 b3 c1 d7 e2 f8 g6 h4
a5 b3 c8 d4 e7 f1 g6 h2
a5 b7 c1 d3 e8 f6 g4 h2
a5 b7 c1 d4 e2 f8 g6 h3
a5 b7 c2 d4 e8 f1 g3 h6
a5 b7 c2 d6 e3 f1 g4 h8
a5 b7 c2 d6 e3 f1 g8 h4
a5 b7 c4 d1 e3 f8 g6 h2
a5 b8 c4 d1 e3 f6 g2 h7
a5 b8 c4 d1 e7 f2 g6 h3
a6 b1 c5 d2 e8 f3 g7 h4
a6 b2 c7 d1 e3 f5 g8 h4
a6 b2 c7 d1 e4 f8 g5 h3
a6 b3 c1 d7 e5 f8 g2 h4
a6 b3 c1 d8 e4 f2 g7 h5
a6 b3 c1 d8 e5 f2 g4 h7
a6 b3 c5 d7 e1 f4 g2 h8
a6 b3 c5 d8 e1 f4 g2 h7
a6 b3 c7 d2 e4 f8 g1 h5
a6 b3 c7 d2 e8 f5 g1 h4
a6 b3 c7 d4 e1 f8 g2 h5
a6 b4 c1 d5 e8 f2 g7 h3
a6 b4 c2 d8 e5 f7 g1 h3
a6 b4 c7 d1 e3 f5 g2 h8
a6 b4 c7 d1 e8 f2 g5 h3
a6 b8 c2 d4 e1 f7 g5 h3
a7 b1 c3 d8 e6 f4 g2 h5
a7 b2 c4 d1 e8 f5 g3 h6
a7 b2 c6 d3 e1 f4 g8 h5
a7 b3 c1 d6 e8 f5 g2 h4
a7 b3 c8 d2 e5 f1 g6 h4
a7 b4 c2 d5 e8 f1 g3 h6
a7 b4 c2 d8 e6 f1 g3 h5
a7 b5 c3 d1 e6 f8 g2 h4
a8 b2 c4 d1 e7 f5 g3 h6
a8 b2 c5 d3 e1 f7 g4 h6
a8 b3 c1 d6 e2 f5 g7 h4
a8 b4 c1 d3 e6 f2 g7 h5

92 rows selected.
[/sourcecode]

[collapse]

Solution for N between 10 and 100

[sourcecode language=”sql”]
with
t as (select level i, to_char(level,’fm00′) c from dual connect by level<=&d)
,x(l,s,n) as (
select 1 l, c s, chr(97)||c||’ ‘ from t
union all
select l+1, x.s||t.c, n||chr(97+l)||to_char(i,’fm00′)||’ ‘
from x
join t
on instr(s,c)=0
and not exists(select 0 from dual
where L+1 – t.i = level – substr(s,length(c)*level-1,length(c))
or L+1 + t.i = level + substr(s,length(c)*level-1,length(c))
connect by level<=length(s))
where L<&d
)
select n
from x
where l=&d
[/sourcecode]

[collapse]

It works quite fast:
8*8 ~ 0.1s
9*9 ~ 0.6s
10*10 ~4s

script for sqlplus

[sourcecode language=”sql”]
set arrays 1000;
col n for a80;
accept d prompt "Size[8]: " default 8;
with
t as (select/*+inline*/ level i, cast(level as varchar2(2)) c from dual connect by level<=&d)
,x(l,s,n) as (
select 1 l, c s, chr(97)||c||’ ‘ from t
union all
select l+1, x.s||t.c, n||chr(97+l)||i||’ ‘
from x
join t
on instr(s,c)=0
and not exists(select 0 from dual
where L+1 – t.i = level – substr(s,level,1)
or L+1 + t.i = level + substr(s,level,1)
connect by level<=length(s))
where L<&d
)
select n
from x
where l=&d
/
col n clear;
[/sourcecode]

[collapse]

Update: Fixed the typo, thanks to Brian Fitzgerald (@ExaGridDba)

Little quiz: Ordering/Grouping – Guess the output

Posted on by Posted in curious, oracle, SQL 1,753 Page views Leave a comment

How many times have you guessed the right answer? 🙂

1

[sourcecode language=”SQL”]
select * from dual order by -1;
select * from dual order by 0;
[/sourcecode]

[collapse]
2

[sourcecode language=”SQL”]
select * from dual order by -(0.1+0/1) desc;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by -(0.1+0/1) desc;
[/sourcecode]

[collapse]
3

[sourcecode language=”SQL”]
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 0;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 0+0;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 3+7 desc;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by -(3.1+0f) desc;
[/sourcecode]

[collapse]
4

[sourcecode language=”SQL”]
select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by 1.9;
select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by 2.5;
select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by 2.7 desc;
select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by -2.7 desc;
[/sourcecode]

[collapse]