Find Nth Highest Salary of Employee

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM  Tb_EmpDetails
ORDER BY salary DESC) a
ORDER BY salary

method1:
2nd highest salary from table

SELECT TOP 1 Salary FROM
(
      SELECT DISTINCT TOP 2 Salary FROM Tb_EmpDetails ORDER BY Salary DESC
) AS T ORDER BY Salary ASC


get 3rd highest salary from table

SELECT TOP 1 Salary FROM
(
      SELECT DISTINCT TOP 3 Salary FROM Tb_EmpDetails ORDER BY Salary DESC
     ) AS T ORDER BY Salary ASC

et 4th, 5th, 6th...nth salary using the following query structure

SELECT TOP 1 Salary FROM
(
      SELECT DISTINCT TOP N Salary FROM Tb_EmpDetails ORDER BY Salary DESC
) AS T ORDER BY Salary ASC

Method2:
get 2nd highest salary from table

SELECT MAX(Salary) AS 'Salary' FROM Tb_EmpDetails
WHERE Salary NOT IN
(
 SELECT DISTINCT  TOP 1 (SALARY) FROM Tb_EmpDetails ORDER BY Salary DESC
)

get 3rd highest salary from table

SELECT MAX(Salary) AS 'Salary' FROM Tb_EmpDetails
WHERE Salary NOT IN
(
 SELECT DISTINCT  TOP 2 (SALARY) FROM Tb_EmpDetails ORDER BY Salary DESC
)

get 4th, 5th, 6th...nth salary using the following query structure

SELECT MAX(Salary) AS 'Salary' FROM Tb_EmpDetails
WHERE Salary NOT IN
(
 SELECT DISTINCT TOP N-1(SALARY) FROM Tb_EmpDetails ORDER BY Salary DESC
)

Method 3:

get 2nd highest salary from table

SELECT MIN(Salary) AS 'Salary' FROM Tb_EmpDetails
WHERE Salary IN
(
 SELECT DISTINCT  TOP 2 Salary FROM Tb_EmpDetails ORDER BY Salary DESC
)

get 4th, 5th, 6th...nth salary using the following query structure

SELECT MIN(Salary) AS 'Salary' FROM Tb_EmpDetails
WHERE Salary IN
(
 SELECT DISTINCT  TOP N Salary FROM Tb_EmpDetails ORDER BY Salary DESC
)

Method 4:

SELECT MAX(salary)AS 'Salary' FROM Tb_EmpDetails WHERE salary NOT IN (SELECT MAX(salary) FROM Tb_EmpDetails)

Method 5:

SELECT MAX(salary) AS 'Salary' FROM Tb_EmpDetails WHERE salary < (SELECT MAX(salary) FROM Tb_EmpDetails)

Difference between Method Overriding and Method Hiding

Difference between Method Overriding and Method Hiding

Method Overriding :

public class BaseClass
{
   public virtual void print()
   {
    console.WriteLine("Base Class Print Method");
   }
}
Public class DerivedClass : BaseClass
{
 public Override void print()
 {
  console.WriteLine("Derived Class Print Method");
 }

}

Public Class program
{
 public static void Main()
 {
  BaseClass b=new DerivedClass();
  b.print();
 }

}

output: Derived Class Print Method

Note: In method overriding  a base class reference variable pointing to the child class object,
will invoke overridden method in the child class.

Method Hiding:

public class BaseClass
{
   public void print()
   {
    console.WriteLine("Base Class Print Method");
   }
}
Public class DerivedClass : BaseClass
{
 public new void print()
 {
  console.WriteLine("Derived Class Print Method");
 }

}

Public Class program
{
 public static void Main()
 {
  BaseClass b=new DerivedClass();
  b.print();
 }

}

output: Base Class Print Method

Note:
In method hiding a base class reference variable pointing to the child class object,
will invoke the hidden method in the Base class.

-- To Hide the base class members from derived class using new keyword

how to invoke hidden base class member from derived class?

there are three ways to invoke hidden base class member from derived class

1.using base keyword

public class BaseClass
{
   public void print()
   {
    console.WriteLine("Base Class Print Method");
   }
}
Public class DerivedClass : BaseClass
{
 public new void print()
 {
   base.print();
 }

}

Public Class program
{
 public static void Main()
 {
  DerivedClass d=new DerivedClass();
  d.print();
 }

}

output: Base Class Print Method

2.cast child type to parent Type and invoke the hidden member

public class BaseClass
{
   public void print()
   {
    console.WriteLine("Base Class Print Method");
   }
}
Public class DerivedClass : BaseClass
{
 public new void print()
 {
   base.print();
 }

}

Public Class program
{
 public static void Main()
 {
  DerivedClass d=new DerivedClass();
  var b=(BaseClass)d;
  b.print();
 }

}

output: Base Class Print Method

3.  BaseClass b=new DerivedClass();
    b.print();
 see this example in acove

cte in sql

CTE are commonly used for storing data temporarily in SQL Server.

delete duplicate records in table using cte :

WITH EmployeesCTE AS
(
   SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) AS RowNumber
   FROM Employees
)
DELETE FROM EmployeesCTE WHERE RowNumber > 1



N th highest salary

using Max() function:

Select Max(Salary) from Employees

use a sub query along with Max() function :

Select Max(Salary) from Employees where Salary < (Select Max(Salary) from Employees)

To find nth highest salary using Sub-Query :

SELECT TOP 1 SALARY
FROM (
      SELECT DISTINCT TOP N SALARY
      FROM EMPLOYEES
      ORDER BY SALARY DESC
      ) RESULT
ORDER BY SALARY

To find nth highest salary using CTE :

WITH RESULT AS
(
    SELECT SALARY,
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
    FROM EMPLOYEES
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = N


To find 2nd highest salary we can use any of the above queries. Simple replace N with 2.

Similarly, to find 3rd highest salary, simple replace N with 3.

WITH RESULT AS
(
    SELECT SALARY,
           ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER
    FROM EMPLOYEES
)
SELECT SALARY
FROM RESULT
WHERE ROWNUMBER = 3

splitting table column value in sqlserver

Declare @delimiter VARCHAR(50)
Set @delimiter=','
;WITH Cte AS
(
SELECT
[Student ID],
[Student Name],
-- Replace the delimiter to the opeing and closing tag
--to make it an xml document
CAST('<M>' + REPLACE([Code], @delimiter , '</M><M>') + '</M>' AS XML) AS [Code]
FROM [Student]
)
Select
[Student ID],
[Student Name],
--Query this xml document via xquery to split rows
Split.a.value('.', 'VARCHAR(MAX)') AS [Code]
FROM Cte
CROSS APPLY [Code].nodes('/M')Split(a)


--------------------------------------------------------------------------
 ;with cte as
(
select row_number()over(order by (select 0)) row,* from [ufn_Split] (@Typeid,',')
),
cte1 as
(
select row_number()over(order by (select 0)) row,* from [ufn_Split] (@Amount,',')
)

insert into dbo.SA_FeeStructure(Typeid,Amount, courseid,remarks,Status,Date,Duration)
select convert(int,a.[value]),b.[value],@courseid,@remarks,@Status,getdate(),@Duration from cte a,cte1 b where a.row=b.row

jquery event differences

document.ready event :

document.ready event will fire whenever DOm is loaded

window.load event :

window.load event will fire after loading all assets(images,Iframes etc) of webpage.


ex1:
 <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"></script>
        <script>
        //first DOM will load
            $('document').ready(function() {
                alert('DOM loaded');
            });
        //in this program, after image will load this event will fire.
            $(window).load(function() {
                alert("window loaded");
            });
        </script>
</head>
<body>
     <img src="http://www.all-wallpapers.net/wallpapers/2012/11/Dimensional-Images-1050x1680.jpg" />
</body>


ex2:

$(document).ready(function () {
    $(document.body).css('background-color', 'silver');
});

$(window).load(function () {
    alert('this will alert after all the window resources completly loaded');
    $(document.body).css("background-color", "green");
});

Maximum Length Control of multiline Textbox in asp.net using jquery

 <script type="text/javascript" language="javascript">
        $(document).ready(function () {
            $('#txtComment').keypress(function (evt) {
                var maxLength = 50;
                // Allow Delete & BackSpace keys
                if (evt.keyCode == 8 || evt.keyCode == 46) return true;
                // Allow Shift, Ctrl & Tab Key
                if (evt.keyCode == 16 || evt.keyCode == 17 || evt.keyCode == 9) return true;
                // Allow Arrow Keys
                if (evt.keyCode == 37 || evt.keyCode == 38 || evt.keyCode == 39 || evt.keyCode == 40) return true;
                // Check and restrict other Keys
                if ($(this).val().length > maxLength) {
                    return false;
                }
            });
            //
            $('#txtComment').blur(function () {
                var maxLength = 50;
                var text = $(this).val();
                if ($(this).val().length > maxLength) {
                    $(this).val(text.substr(0, maxLength));
                }
            });
        });
    </script>

WCF Interview Questions

1. What is WCF also known as?

  WCF (Windows Communication Foundation) is also know an  Indigo by its code name.

2. Difference between WCF and Web Services?

Below are the main differences between the WCF and Web Service:
Web Service:
1.    Can be hosted in IIS only
2.    Only two types of operations affects- One-Way, Request-Response
3.     To serialize the data use System.Xml.Serialization
4.    To encode the data use- XML 1.0, MTOM, DIME, Custom

WCF service:
1.    Can be hosted in IIS, Self Hosting, WAS, Windows Services etc
2.    Three types of operations affects- One-Way, Request-Response and Duplex
3.     To serialize the data use System.Runtimel.Serialization
4.    To encode the data use- XML 1.0, MTOM,Binary, Custom
5.    WCF Service can be accessed through HTTP, TCP, Named pipes, MSMQ,P2P etc.

3. What are Endpoints?

 The collection of Address, Binding and Contract is called as End Point. In Sort,
EndPoint = A+B+C
Address (Where)-  it means where the service is hosted. URL of the service shows the address.
Binding (How)- How to connect to the service, is defined by the Binding. It basically has the definition of the communication channel to communicate to the WCF service
Contract (what)- It means what the service contains for the client. What all the methods are implemented in the WCF service is implemented in the Contract.

4. What are Behavior and Bindings?

Binding mainly describes about the communication of the client and service. For this, there are protocols corresponding to the binding behavior which will take care of the communication channel. There are different protocols which we use for the different types of bindings. E.g. HTTP, TCP, MSMQ, Named Pipes etc.
Behavior is used for the common configurations that could be for endpoints. When we use the common behavior, they affect to all the end points. Adding the service behavior affect the service related stuff while the endpoint related behavior affects the end points. Also operations level behavior affects the operations. 

5. What are different types of Contracts supported?

 There are mainly 5 type of contracts used in WCF service:
1. Service Contract
2. Operation Contract
3. Data Contract
4. Message Contract
5. Fault Contract

6. What is the difference between Transport and Message Security mode?

 WCF supports 2 types of security- Transport Level Security and Message Level Security
Transport Level Security- In this type of security, we make the transport channel as secure so that the data flows in that channel will be automatically secured. For HTTP channel, we use the client certificate for the security of the web address. SSL is used for the HTTP channel security. As we don’t need to secure each of the messages which are floating between the client and the service, the speed is faster as direct message is going to the client from the service.
Message level security- This type of security in WCF is used where we don’t have the fixed transport medium and we need to secure each message which is floating between the server and the client. In this type of security we use certain algorithms for making the message as secure message. We use some extra bits and send with the message. We also use some encryption techniques like SHA1 or MD5 which make the proper security for our message. As each message needs to be secured, this type of security makes some delay in the process of sending and receiving the messages.

7. How to configure WCF security to support Windows authentication?

To support the WCF security in Windows Authentication, we need to add the ClientCredetialType attribute to “Windows” under the security tab element:
transport clientCredentialType="Windows"

8. How to use Fault Contract?

 Fault Contract is mainly used for viewing and displaying the errors which occurred in the service. 
So it basically documents the error and the error message can be shown to the user in the understandable way.
We can’t use here the try….catch block for the error handling because the try…catch is the technology specific (.Net Technology). 
So we use the Fault contract for the error handling.

e.g. To use the Fault contract, we can simply write like the below:

public  int Add(int number1,int number2)
{
  // write some implementation
 throw new FaultException (“Error while adding data..”);
}

Here the fault Exception method is the inbuilt method which will throw the exception and display the message . We can use the custom class so that the message can be customized and the customized message can be sent to the client.

So we can creeat  a clss like:

Public Class CustomException()
{
public int ID{get;set;}
public string Message{get;set;}

public string Type{get;set;}
}

Now this custom type we ca use with the Operation Contract as:

[ServiceContract] 
Public interface IMyInterface
{
[OperationContract]
[FaultContract(typeOf(CustomException))]
Int Add(int num1,int num2);
}

Now while implementation of the Add method, we can assign the class properties.