Some funny SQL challenges in CUBRID

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...

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?

A: To solve this one, we will use the BIT_COUNT and the BIN functions:

SELECT LENGTH(BIN(69)) - BIT_COUNT(69)

Result:

4

(69 = 1000101₂)

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!

Btw, did you know that? (http://searchcio-midmarket.techtarget.com/definition/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… :)