Calculate Total Possible Score for Wii Bowling, Power Throws Needless to say I have to much time on my hands. I've become obsessed with two Wii games, Bowling and Tanks. I play over and over. I had a perfect game in bowling the other day which wasn't quite as exciting as I thought it would be.

I've also been playing the "Training" game of Power Throws (bowling). Your given 10 shots with another row added each frame. You start out with 10, then 15, then 21, etc. If you get all the pins, you get a bonue (total pins * 2). So far, my highest score has been 651.

I've never bothered to figure out what the total possible score is though.

SQL to the rescue.

Using analytics and the CONNECT BY LEVEL option (10g and higher), it's fairly easy:

SELECT
  pin_row,
  pins,
  running_total,
  rt_with_bonus
FROM
  (
    SELECT
      rownum pin_row,
      rownum pins,
      SUM( rownum ) OVER ( ORDER BY rownum ) running_total,
      ( SUM( rownum ) OVER ( ORDER BY rownum ) * 2 ) rt_with_bonus
    FROM dual
      CONNECT BY LEVEL <= 13
  )
WHERE pins >= 4

I had to set the filter on the outer query because you start with 4 rows (10 pins). And the results:

CJUSTICE@XE>BREAK ON REPORT
CJUSTICE@XE>COMPUTE SUM OF RUNNING_TOTAL ON REPOR
CJUSTICE@XE>COMPUTE SUM OF RT_WITH_BONUS ON REPOR
CJUSTICE@XE>/

   PIN_ROW       PINS RUNNING_TOTAL RT_WITH_BONUS
---------- ---------- ------------- -------------
         4          4            10            20
         5          5            15            30
         6          6            21            42
         7          7            28            56
         8          8            36            72
         9          9            45            90
        10         10            55           110
        11         11            66           132
        12         12            78           156
        13         13            91           182
                      ------------- -------------
sum                             445           890

Voila! 890 is the best possible score if I strike every frame. I did manage to pass my high score by 9 pins tonight as well. It's a great day!

So how come no one wants to hire a guy that can do this kind of fun stuff with SQL? ;)

Labels: funny, puzzle, sql

  ¶ 8/20/2008 10:04:00 PM 3 Comments Links to this post

  Count The K's I have this really annoying co-worker who happens to be the DBA. Everytime he walks by my desk he pounces on my keyboard. I've learned to Windows Key + L to lock the computer when I hear him approaching, but occasionally I forget.

As I'm standing near my unlocked computer he starts typing furiously in my sql*plus session (shouldn't he know better?). I should know better...

Walking away he asks, "How many K's are in there?"

I ignored him, but then wondered myself...what's the best solution to this problem?

So, here it goes:

DECLARE
  l_count_k NUMBER := 0;
  l_string VARCHAR2(300);
  l_string_length INTEGER;
BEGIN
  l_string := 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;
LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;
EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L
TKELTKER';

  l_string_length := LENGTH( l_string );

  FOR i IN 1..l_string_length LOOP
    IF SUBSTR( l_string, i, 1 ) IN ( 'K', 'k' ) THEN
      l_count_k := l_count_k + 1;
    END IF;
  END LOOP;
  
  dbms_output.put_line( 'Kk Count:  ' || l_count_k );
END;
/

Easy enough, 45.

Then I started thinkinhg...can I do this in pure SQL? Of course!

SELECT 
  SUM( CASE
         WHEN SUBSTR( UPPER( mystring ), rownum, 1 ) = 'K' THEN
           1
       END ) k
FROM
  dual,
  (
    SELECT 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;
    LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;
    EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L
    TKELTKER' mystring
    FROM dual
  ) c
CONNECT BY LEVEL <= LENGTH( mystring )

COUNT_OF_K
----------
        45

1 row selected.

I'm sure many of you can do better than that. So let's see 'em. Prodlife, this isn't a complicators test either. ;)

Labels: funny, puzzle, sql

  ¶ 7/08/2008 09:00:00 PM 9 Comments Links to this post

  Funny License Plate

H20UUP2

First to figure out wins absolutely nothing but praise!

Labels: funny, puzzle

  ¶ 7/02/2008 08:33:00 PM 2 Comments Links to this post

  Fun with SQL My group held it's first Peer Code Review today. It was my code (the code I've screwed up a couple of times). One of the requirements (I stuck to them this time, yeah for me!) was to limit the data we were pulling from another system to only the last 48 months. There were three different procedures that needed this date but I didn't want to break the existing automated job so I defaulted everything to:

p_date_from DATE DEFAULT ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), -48 )

I wanted the last full 48 months and this worked just fine.

This is a fairly long process which takes anywhere from 8 to 12 hours to run, depending on system resources. One of my colleagues asked what would happen in the event of a crash (on the last day of the month) and the required re-start (on the first day of the month). One set of data would have everything going back 49 months and the other 48 months which would create a lot of errors. A great catch.

Since we're in maintenance mode now (re-architecting the whole thing), we decided not to stop the deployment, we looked at the calendar to see when the first occurence was in which the 1st fell on a Sunday (the job is scheduled weekly on Sunday)...which now that I think about it, was probably wrong. We should have been looking for a Sunday in which was also the last day of the month. Anyway...

I like trying to answer questions with SQL. So I wanted to find the occurences on which Sunday was also the first day of the month (despite while writing this realizing that we should have been looking for the last day, again, digression). Here's my resultant query:

SELECT
  ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), myrownum )first_day_on_sunday
FROM
  ( 
    SELECT rownum myrownum
    FROM dual
    CONNECT BY LEVEL < 100
  )
WHERE TO_CHAR( ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), myrownum ), 'D' ) = 1
  AND TO_CHAR( ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), myrownum ), 'DD' ) = 1
/

FIRST_DAY_
----------
06/01/2008
02/01/2009
03/01/2009
11/01/2009
08/01/2010
05/01/2011
01/01/2012
04/01/2012
07/01/2012
09/01/2013
12/01/2013
06/01/2014
02/01/2015
03/01/2015
11/01/2015

I'm not so sure it impressed my colleagues, but it certainly reinforced the notion that I'm a nerd.

Labels: puzzle, sql, work

  ¶ 8/29/2007 10:04:00 PM 2 Comments Links to this post

  SQL Puzzle, Sort of... My group was once asked, "What's the first number that contains an 'A' in it?"

This has become a once a week exercise for fun.

I of course started counting from one to see where it might happen. Then I decided to use the magic of SQL to help me go through all the numbers.

On 10g:

SELECT num
FROM
  (
    SELECT
      TO_CHAR(TO_DATE('01/01/4712 BC', 'MM/DD/YYYY BC') + (rownum - 1), 'JSP' ) num
    FROM DUAL
      CONNECT BY LEVEL < 1000
  )
WHERE INSTR( num, 'A' ) > 0;

NUM
-------------------
ONE THOUSAND

I found the format model in the documention and moved on from there. I think my original query was a bit more elegant, but this works.


Pretty cool stuff.

Labels: database, puzzle, sql

  ¶ 8/21/2007 02:11:00 AM 0 Comments Links to this post