Fun with SQL - Part 59

I got an email today titled, "Your Favorite Movie."

It claims to successfully calculate your favorite movie by picking a number, from 1 to 9, then doing some calculations and applying the result to a list of movies.

Years ago...my first boss, got a similar email and began working through it via SQL. I was very impressed, both by the idea of using SQL and that he could do it in SQL. He was (is) a smart, creative guy.

I'm sure you've seen this one before:

Your Favorite Movie

Try this test. Scroll down and do the quiz as it instructs and find out what movie is your favorite. This amazing math quiz can likely predict which of 18 films you would enjoy the most. Don't ask me how but it really works!

Pick a number from 1-9.

Multiply by 3.

Add 3.

Multiply by 3 again.

Now add the two digits together to find your predicted favorite movie in the list of 18 movies below.

Mine was "Gone with the Wind" - exactly right! So be honest, and do it before you scroll down to see the list below. It's easy and it works.

How do you do this in SQL? It's not that hard really. First, get 1-9. SELECT from DUAL will accomplish that.

SELECT rownum
FROM dual
  CONNECT BY LEVEL <= 9

Then you just need to apply the calculations. First go, I put the calcs in the outer query:

SELECT rn, ( ( ( rn * 3 ) + 3 ) * 3 ) calc_#
FROM
  (
    SELECT rownum rn
    FROM dual
      CONNECT BY LEVEL <= 9
  )
/

    RN     CALC_#
------ ----------
     1         18
     2         27
     3         36
     4         45
     5         54
     6         63
     7         72
     8         81
     9         90

A pattern jumps out at me immediately...

So I move it to the inner query and use SUBSTR to get each number and then add them together. I am sure there are better ways...this was quick and dirty.

SELECT
  rn,
  SUBSTR( t, 1, 1 ) + SUBSTR( t, 2, 1 ) total
FROM
  (
    SELECT
      rownum rn,
      ( ( ( rownum * 3 ) + 3 ) * 3 ) t
    FROM dual
      CONNECT BY LEVEL <= 9
  )
/

The results reveal the pattern rather quickly.

    RN      TOTAL
------ ----------
     1          9
     2          9
     3          9
     4          9
     5          9
     6          9
     7          9
     8          9
     9          9

Of course #9 is the movie that you would never pick. It's funny though:

1. Gone With The Wind
2. E.T.
3. Beverly Hills Cop
4. Star Wars
5. Forrest Gump
6. The Good, The Bad, and the Ugly
7. Jaws
8. Grease
9. The Joy of Anal Sex With A Goat
10. Casablanca
11. Jurassic Park
12. Shrek
13. Pirates of the Caribbean
14. Titanic
15. Raiders Of The Lost Ark
16. Home Alone
17. Mrs. Doubtfire
18. Toy Story