Oracle SQL
  • LICENSE

Tag Archives: prank

Little quiz: Ordering/Grouping – Guess the output

Posted on April 28, 2015 by Sayan Malakshinov Posted in curious, oracle, SQL Leave a comment

How many times have you guessed the right answer? πŸ™‚

1

select * from dual order by -1;
select * from dual order by 0;

[collapse]
2

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;

[collapse]
3

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;

[collapse]
4

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;

[collapse]

oracle oracle undocumented behaviour prank quiz undocumented oracle

Friday prank: select from join join join

Posted on February 21, 2014 by Sayan Malakshinov Posted in curious, oracle 1 Comment

Valid funny queries πŸ™‚

select the join from join join join using(the,some) 
/
select some join from left join join right using(some,the) 
/
select 1 join from join join join join join using(the) on 1=1
/
select the some from join 
where the=some( the(select some from join) 
               ,the(select the  from join)
               ) 
/ 
tables
create table join as select 1 some,1 the from dual;
create table left as select 1 some,1 the from dual;
create table right as select 1 some,1 the from dual;

[collapse]
πŸ™‚

Little addition πŸ™‚

update two tables set join=2;
select join from two tables;
select first, second, random from two tables join three tables on 1=1;

Another one πŸ™‚

select first
     , case when the=some(some,some) then join end true
from two tables join three tables using(random);

….
and…

select random columns from two tables join four tables on the=some(some,some);
friday prank
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 μ‹œν‚¬ λ•Œ λ°œμƒ μž₯μ•  원인 – DBA의 정석 on Example of controlling β€œdirect path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events β€” Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
Β©Sayan Malakshinov. Oracle SQL