Tuesday, April 3, 2012


Generally you don't inspired with every puzzle that you solve. Every body would have seen handful of puzzles that made them elevated fore few moment. All of them will introduces something new to us. It is not the feeling of solving the puzzle makes a puzzle solver excited instead the wisdom it brought in indeed make it more thrilling experience. Here is the one such puzzle...

There are 1000 lockers in a high school with 1000 students. The problem begins with the first student opening all 1000 lockers; next the second student closes lockers 2,4,6,8,10 and so on to locker 1000; the third student changes the state (opens lockers closed, closes lockers open) on lockers 3,6,9,12,15 and so on; the fourth student changes the state of lockers 4,8,12,16 and so on. This goes on until every student has had a turn. How many lockers will be open at the end?

Locker 1 will remain open for sure
Locker 2 will be closed (opened by 1 and closed by 2)
Locker 3 will also be closed
Locker 4 will be open (opened by 1, closed by 2 and opened by 4)
Locker 5 - closed (1-5)
Locker 6 - closed ( 1-2-3-6)
Locker 7 - closed (1-7)
Locker 8 - closed (1-2-4-8)
Locker 9 - open (1-3-9)

One can easily come to a below conclusion by looking at the above iterations,

 numbers have even number of factors will be closed at the end and numbers having odd number of factors will remain open.

 Now, the question is to get the list of numbers having odd number of factors. The below points are obvious when it comes to factors of a number

1. every number has at least two factors (1 and the number itself)
2. every factor can be paired with another factor in such a way that multiplying both the factor will be the number itself
    For instance, number 6 has four factors, 1,2,3 and 6
              here 1 can be paired with 6, 2 can be paired with 3.

3. Total number of factors will be even if the paired factors are not same.

In other words the number of factors are not even if any of the pairs have the same numbers ( for instance 2,2 for 4).

if a factor pair contains the same number then the number must be a perfect square :).
Also a number can not have two such factor pairs with identical number (which is obvious).

Hence number of lockers those remain open will be perfect squares (perfect is not it :) ).
So total number of lockers remain open is 31 (31^2 =961, 32^2= 1024 ).

Friday, March 16, 2012

A simple For..Loop Puzzle

What is the output of below pl/sql block?

        var1 number(2) :=1;
        var2 number(2) :=5;
      for i in var1..var2 loop
           dbms_output.put_line( 'i,var2: ' || i || ','||var2);
           if i= 3 then
                var2 :=7;
           end if;
      end loop;

The output of the above pl/sql block is,
i,var2: 1,5
i,var2: 2,5
i,var2: 3,5
i,var2: 4,7
i,var2: 5,7

Surprised?? One can argue that the loop should have been continued until i reaches the new value of var2, which is 7. The below extract from oracle documentation will silent the argument,

Numeric FOR_LOOP loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP.

The range is evaluated when the FOR loop is first entered and is never re-evaluated. The loop body is executed once for each integer in the range defined by lower_bound..upper_bound. After each iteration, the loop index is incremented.

Thursday, March 15, 2012

I lost my Boarding pass

I used to hear people saying that the big challenge in problem solving lies in understanding the problem.The solution just follows that... This puzzle is a classical example.


On a sold out flight, 100 people line up to board the plane. The first passenger in the line has lost his boarding pass, but was allowed in, regardless. He takes a random seat. Each subsequent passenger takes his or her assigned seat if available, or a random unoccupied seat, otherwise. What is the probability that the last passenger to board the plane finds his seat unoccupied?


One can make this very complex by starting from Passenger 1 to 100 and working out all possibilities.But this question is as simple as below,

What is the probability for a passenger to get a specific seat among 100 seats? It is a simple probability question is not it?

answer is : 99!/100! = 1/100 = 1%

n(A) = other 99 seats can be occupied by other passengers = 99! ways.

n(s) =  number of ways 100 passengers can occupy 100 seats =100!

Thursday, March 8, 2012

Puzzle of 100 Bulbs...

Just came across this puzzle in geekinterview.com,
College has two rooms, one is having 100 bulbs and other room has its switches. Switches doesn't have number/name written on it. New warden doesn't know the switches for the bulbs. How many trips he has to make to identify all the switches for the bulbs?

And my answer to this puzzle is that he can do it in 20 trips...

Here is my solution,
1. Group 100 switches in to 10groups of 10 switches each.
2. On all the switches in group 1, make a trip and note down the bulbs belong to that group
3. Repeat step 2 for all the groups . That will make 10 trips and at the end of 10 trips bulbs also grouped along with switches.
4. Now ON one switch in each group - 10 switches are on now
5. make a visit and mark down the bulbs - now each bulb can be matched with its switch
6. Repeat step 4 and 5 - for remaining 90 bulbs

so by end of this 20 trips he will be able to match all 100 switches with the bulbs.

I am not sure whether it is possible to solve this problem with less than 20 trips.

Tuesday, March 6, 2012

Is DENSE_RANK an Analytic function or an Aggregate Function?

Is DENSE_RANK an Analytic function or an Aggregate Function?

'Analytic Function' was an instant answer whenever this question had been thrown at me. But the answer is DENSE_RANK is both an Analytic and an aggregate function since Oracle 10G. 

DENSE_RANK as an aggregate function,

    •   returns the dense rank of the hypothetical row identified by the arguments of the function in an order group of rows

    •   The arguments of the function must all evaluate to constant expressions within each aggregate group, because they identify a single row within each group

    •   the number of arguments must be the same as number of expressions in the order_by_clause and types of the arguments must also be compatible.

The following query returns dense rank of salary 2000 in each department,

  SELECT dept,
                DENSE_RANK (2000) within group (order by salary DESC) DENSE_RNK

    FROM employees

  GROUP BY dept;


Notably, the dense_rank will be identified and returned even if such salary does not exist in the department.

For instance when the above query runs against employees table with below set of records dense_rank of salary 2000 in dept 4 will be returned as 4 even though there are no employees earning the salary of 2000


The above query will return, 


Saturday, March 3, 2012

Understanding ADD_MONTHS function

Do you know everything about ADD_FUNCTION in oracle? Let us have a reality check. Have a look at the below query and predict the output,

                ADD_MONTHS('30-APR-2012',-1) ,

Execute the above query and match your predictions with actual results. If you have got everything correct then I suggest not to waste your time by reading further ;). If you have not got everything right then its fair to say that you need to understand ADD_MONTHS function better.

My suggestion to those who think there must be something wrong with ADD_MONTHS function is that read the below extract from oracle manual about ADD_MONTHS function,

"ADD_MONTHS returns the date d plus n months. The argument n can be any integer. If d is the last day of the month or if the resulting month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d."

The above statement clearly defines two boundary conditions. They are

1. If input date d is the last day of the month then ADD_MONTHS function will always return last day of the resulting month.
For instance, ADD_MONTHS('29-FEB-2012',1) will return '31-Mar-2012' rather than '29-Mar-2012'

2. If the resultant month has fewer days than input day then the result will be last day of the month
For instance ADD_MONTHS('30-JAN-2012',1) will return '29-Feb-2012'

The above two are very fair boundary conditions in order to ensure that ADD_MONTHS works fine for all dates with out any issue.

Every one will be happy with this behavior until you have a requirement to shift months (add or subtract) with out changing the date like below,

         fn('30-Nov-2012',1) = '30-Dec-2012'

Unfortunately ADD_MONTHS does not support this. But there is a work around. This can be achieved by using Interval arithmetic. Unlike ADD_MONTHS function INTERVAL '1' MONTH just shifts the month from November to December but keep the date as 30th.


But this solution comes in with a pitfall. The interval arithmetic will fail with invalid date exception when the resultant months has fewer days than you input date. For instance the below query will fail since the month of February does not have 30 days.


Better late than never::TNSTC online booking is enabled!!

A good news for frequent travelers. Tamil Nadu Government has enabled Online Booking for Government buses. Here is the link for online booking.