A simple For..Loop Puzzle

What is the output of below pl/sql block?

declare

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

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.

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.

Question:

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?

Solution:

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!

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.

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

EMPLOYEE_ID	LAST_NAME	SALARY	DEPT
100	Employee100	1000	10
101	Employee101	2000	10
104	Employee104	2000	10
105	Employee105	4000	10
110	Employee110	4000	40
111	Employee111	8000	40
114	Employee114	8000	40
115	Employee115	16000	40

The above query will return, 

 

DEPT    DENSE_RNK 10      2 40      4

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,

SELECT ADD_MONTHS('31-JAN-2012',1),

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

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

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

                ADD_MONTHS('29-FEB-2012',1)

   FROM  DUAL;

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.

 SELECT DATE '30-NOV-2012' + INTERVAL '1' MONTH

    FROM DUAL;

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.

SELECT DATE '30-NOV-2012' + INTERVAL '3' MONTH     

   FROM DUAL;

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.