Hi!
Do you remember when CUBRID hosted the SQL challenge last year...? It was such great fun!
Unfortunately, this year there was no more such a challenge... :( Hey, CUBRID team, if you listen, please do it again - is was such a great initiative!
In the mean time, what better way to celebrate one year ending - 2012 - and one new year coming, than having our own SQL challenge...? ha-ha... :)
So today, let’s take a look at some (hopefully!) funny SQL challenges...
So today, let’s take a look at some (hopefully!) funny SQL challenges...
Q: Let’s
start with an easy one: Given a date, what day of the week it falls on?
A: We will use
the TO_CHAR
function:
SELECT
TO_CHAR(DATETIME'01/01/2013', 'Day')
Result:
Tuesday
Tip: The TO_CHAR function accepts a new parameter
in 9.0 – the locale. Try it for your
own language… :)
Q:
Given an integer (69 for
example), how many bits are set to 0 in its binary representation?
SELECT
LENGTH(BIN(69)) - BIT_COUNT(69)
Result:
4
(69 = 10001012)
Q: Wanna
have some great time? If YES, then read about the “Answerto the Ultimate Question of Life, the Universe, and Everything (42)”!
It’s nothing else but pure magic! …Do NOT come back here
until you have read it!! :)
Now, one of the cool things which are mentioned in
there is the GIGO principle of
“Garbage in,garbage out”…
And the challenge for you is: Illustrate the GIGO
principle the best you can, in relation to 42, using
SQL statements…!
A: Here are some
of my own answers… …can you do BETTER? :)
SELECT
LENGTH(‘42’) * TO_NUMBER(LENGTH('GARBAGE IN') + LENGTH('GARBAGE OUT'))
SELECT
42 * BIT_COUNT(4242424242424242 * BIN(LENGTH('GARBAGE IN GARBAGE OUT')))
SELECT
TO_NUMBER(CONV(LENGTH('Ultimate Question of Life, The Universe, and
Everything.'), 10, 13)) - LENGTH('42')
Result is always (obviously - ha-ha):
42!
- The angle at which light reflects off of water to create a rainbow is 42 degrees.
- Two physical constants in the universe are the speed of light and the diameter of a proton. It takes light 10 to the minus 42nd power seconds to cross the diameter of a proton.
- A barrel holds 42 gallons.
Q: What
is number 10, expressed in base -10,
when converted to base 10?
A: Let’s see this, by
using the CUBRID CONV
function:
SELECT
CONV(10, -10, 10)
Result:
1!
(…can you figure out why…?)
Q: How
do you test in SQL if a number is part of the Fibonacci sequence?
A: Gessel solved
this in 1972 with a simple test:
“N is a Fibonacci
number if and only if 5 N^2 + 4 or 5 N^2 – 4 is a square number!”
Let’s do this test in plain SQL (let’s use 33 and 34 for example):
SET @x := 34;
SELECT @x,
CASE WHEN
(POWER(ROUND(SQRT((5 * POWER(@x, 2)) +
4), 0), 2) = (5 * POWER(@x, 2)) + 4 )
OR
(POWER(ROUND(SQRT((5 * POWER(@x, 2)) -
4), 0), 2) = (5 * POWER(@x, 2)) - 4 )
THEN 'Yes'
ELSE 'No'
END
Result for 33:
No!
Result for 34:
Yes!
Q: What
is the connection between the 5thelement - WATER and the so-called “end of days (21 December2012)”…?! LOL…
A: Let’s see…
SELECT
CHR(TO_DAYS('2012-12-21'));
Result:
Thank you, I hope you
learned some new stuff today (and had fun as well!) - See you next time –
Bye-bye!
/Dee
P.S. Hey - one last funny CUBRID SQL query… :)
