Nth Highest Salary Query in Oracle 9i/10g/11g/11i

Problem: Show the list of all employees from the employee table having second highest Salary or Nth highest salary in the organization.

Solution: Well we can achieve the result by so many ways but in this post I would like to do it by using co-related queries.
Query to get the list of employees getting 2nd highest salary (Set the value of @SalaryPosition to get Nth highest salary):




SELECT * FROM Employee E1  WHERE @SalaryPosition = ( SELECT COUNT(DISTINCT E2.Salary) FROM Employee E2 WHERE E2.Salary >= E1.Salary )



Co-Related Query:  In short, it’s a type of nested sub query where the inner query is referenced to some value of the outer query. And the inner query is executed for each record in the outer query making it slowL.
     In our query, to get the 2nd highest salary, works in the same way too. Let’s dive a little deeper.

Sample data used in employee table:


slNo           empname               salary


101            Ram                      5000
102            Abhishek              7500
101            kumar                   5000
104            prasad                  6570
102            Jumla                    7500
101           Harkesh                12000
101           John                      4000

So we have taken two aliases for the employee table i.e. E1 and E2.(Here N=2)

Corollary: Let’s take the first record in the table E1 where the salary is 5000 and assume it as the second highest salary. But how will you know if it is actually the second highest salary or not. If 5000 is the second highest salary, then the distinct count of all the salaries from table E2 which are greater than or equal to 5000 must be 2.

The first record from E1 (salary = 5000) will be compared to all the records in E2 (salary).


Table E1                                         Table E2                                                  Distinct count where
(considering the first record only)   (distinct salary)                                           salary is >= 5000

5000                                               4000                                                                   4
                                                       5000
                                                       6570
                                                       7500
                                                      12000

Hence 5000 is not the highest salary as we have 4 more salaries greater than equal to 5000. But it is concluded that 5000 is at the fourth position.

     Now let’s take the second salary from table E1 and do the same comparison as in the earlier case.

The second salary is 7500 and as per rule the count of distinct salaries greater than equal to 7500 must be 2.

   Table E1                                         Table E2                                                Distinct count where
(considering the second record only)  (distinct salary)                                       salary is >= 5000


7500                                                  4000                                                                 2
                                                          5000
                                                         6570
                                                         7500
                                                         12000


Here in this case our condition in the where clause of outer query is satisfying.

Note:-Please comment and reply me.

Read More

Anwer Of Question : Difference between PDA and Tuning Machine

1. PDA has a capability to move in a on direction (right) while tuning machine has a capability to move either left or right direction.
2. PDA contain a stack which stating symbol is represented by # where TU contain a unlimited buffer.

Note:-Please comment and reply me.

Read More

Answer of Question : How to call a default constructor inside a default constructor

in below example , we make an object of A using the Constructor of B Class.
First default constructor of class A called then CLR call default constructor of class B ,then make the object of class A .

public class A


{ public A()

{  Console.WriteLine("A"); }

}

public class B:A

{ public B()

{ Console.WriteLine("B"); }

}

class Program

{ static void Main(string[] args)

{

 A ob = new B();

}

}

output
A
B


Note:-Please comment and reply me.

Read More

Answer of Question : deadlock, starvation and synchronization problems in multithreaded scenario

Thread is a sub part of a process . Multiple Thread Model involve the existence of multiple threads. Three are main problem in multiple thread model.

1. Deadlock :- Suppose there are two thread T1 & T2 of a process P and R1 & R2 are two resouces . T1 has R1 and T2 has R2 at intance of time . suppose T1 need a resource R2 that are acquired by the T2 and T2 need a resource R1 that are acquired by the T1 then these type of situation goes in unlimited waiting i.e. these situation called Deadlock Problem.
2. Stravation :- Suppose thread are processed on the base of Priority (More Priority Processed First),T1 & T2 are two thread .T1 has more priority than T2 than T1 first processed and suppose in mean time another thread T3 come with high priority than T2 , T3 processed before than T2. and so on then t2 has wait for limited of a time while thread has a capability to perform work.This type of situation is called stravation.
3. Synchronization :-The biggest problem of allowing multiple threads sharing the same data set is that one operation in one thread could collide with another operation in another threads on the same data. When this happens, the result is un-desirable.

          Let's use a bank application program as an example. Assuming that the program has multiple threads   running, with each thread connecting one ATM system, and you have a saving account in the bank with $100.00, now you and your friend are going to two different ATMs at about the same time, and trying to withdraw $50.00 from your account, what do you think it will happen?
If the threads are running independently, the following could happen:

Time     01:01      02:01     03:01     04:01
         +----------+---------+---------+-------
Thread 1            Get       Set
Action   You        Account   Account   You
         Withdraw   Balance   Balance   Receive
         $50.00     $100.00   $50.00    $50.00

Time      01:02      02:02     03:02     04:02
         -+----------+---------+---------+------
Thread 2             Get       Set
Action    Friend     Account   Account   Friend
          Withdraw   Balance   Balance   Receive
          $50.00     $100.00   $50.00    $50.00

Time     01:01      02:01     03:01     04:01
         -----------++--------++--------++------ 
Account  $100.00    $100.00   $50.00    $50.00

Both you and your friend will receive $50.00 each, and your account will still have $50.00. The bank could lose $50.00. The solution to this problem is synchronization.


 Note:-Please comment and reply me.

Read More

Algorithmic Trading Technology

Yesterday, I contact to Mr.RK Dhanvada (Business & Recruitment Consultant, D & HR Consultants ,Hyderabad ) and talked him about 10 min and told him that I am B.Tech(C.S) final year Student of Shobhit University, Meerut .I passed OCA and MCTS exam . I looked for an Job. He send me his email id and asked for sending the email with resume and certification.


I send an email to him.In response, I got 10 question of Algorithmic  Trading Technology ,which i want to share you.





1. Explain mechanism and pointers related to virtual functions in C++.

2. How to call a default constructor inside a default constructor?

3. What is Object-Factory design pattern?

4. What is the runtime complexity of STL Set and Map?

5. Implement a red-black tree in C++.

6. Simulate Travelling-Salesman problem in C++.

7. What happens when we cast a huge pointer into a far pointer in C?

8. Explain deadlock, starvation and synchronization problems in multithreaded scenario.

9. Explain complete booting process in a Microsoft® Windows based PC.

10. Differences in PDA and Turing machine.


the answer of these question , are posted now.



Note:-Please comment and reply me.

Read More