How to find second highest or maximum salary of Employee in SQL

In this section we will write SQL query to get second highest salary of Employee. Before writing query its good to be familiar with schema as well as data in table. Here is the Employee table we will be using this SQL example:


mysql>SELECT *FROM Employee;

+--------+----------+---------+--------+
| emp_id |emp_name | dept_id |salary |
+--------+----------+---------+--------+
| 1     | James    | 10      |   2000 |
| 2     | Jack     | 10      |   4000 |
| 3     | Henry    | 11      |   6000 |
| 4     | Tom      | 11     |   8000 |
+--------+----------+---------+--------+
 
4 rows INSET (0.00 sec)

If you look data, you will find that second maximum salary in this case is 6000 and employee name is Henry. Now let’s see some SQL example to find out this second maximum salary.
Sub queries in SQL are great tool for this kind of scenario, here we first select maximum salary and then another maximum excluding result of subquery. To learn more about Subquery see correlate and non-correlate subquery in SQL

mysql>SELECT max(salary) FROMEmployee WHERE salary NOT IN(SELECTmax(salary)FROM Employee);

+-------------+
| max(salary) |
+-------------+
|        6000 |
+-------------+
1 row INSET (0.00 sec)

If you look data, you will find that second maximum salary in this case is 6000 and employee name is Henry. Now let’s see some SQL example to find out this second maximum salary.
Sub queries in SQL are great tool for this kind of scenario, here we first select maximum salary and then another maximum excluding result of subquery. To learn more about Subquery see correlate and non-correlate subquery in SQL

 mysql>SELECT max(salary) FROMEmployee WHERE salary NOT IN(SELECTmax(salary)FROM Employee);

+-------------+
| max(salary) |
+-------------+
|        6000 |
+-------------+
 
1 row INSET (0.00 sec)

Here is another SQL query to find second highest salary using subquery and < operator instead of IN clause:

mysql>SELECT max(salary) FROMEmployee WHERE salary < (SELECT max(salary) FROMEmployee);

+-------------+
| max(salary) |
+-------------+
|        6000 |
+-------------+
 
1 row INSET (0.00 sec)

TOP keyword of Sybase and SQL Server database is used to select top record or row of any result set, by carefully using TOP keyword you can find out second maximum or Nth maximum salary as shown below.

SELECT TOP 1salary FROM (SELECT TOP 2salary FROM employees ORDER BYsalary DESC)AS emp ORDERBY salary ASC

Here is what this SQL query is doing : First find out top 2 salary from Employee table and list them in descending order, Now second highest salary of employee is at top so just take that value. Though you need to keep in mind of using distinct keyword if there are more than one employee with top salary, because in that case same salary will be repeated and TOP 2 may list same salary twice.

Second maximum salary using LIMIT keyword of MYSQL database

LIMIT keyword of MySQL database is little bit similar with TOP keyword of SQL Server database and allows to take only certain rows from result set. If you look at below SQL example, its very much similar to SQL Server TOP keyword example.

mysql>SELECT salary  FROM (SELECT salary FROMEmployee ORDER BY salary DESCLIMIT 2) AS emp ORDER BYsalary LIMIT 1;

+--------+
| salary |
+--------+
|   6000|
+--------+
 
1 row INSET (0.00 sec)

How to find second highest or maximum salary of Employee in SQL

In this section we will write SQL query to get second highest salary of Employee. Before writing query its good to be familiar with schema as well as data in table. Here is the Employee table we will be using this SQL example:

Using Mysql

mysql> SELECT * FROM Employee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
| 1      | James    | 10      |   2000 |
| 2      | Jack     | 10      |   4000 |
| 3      | Henry    | 11      |   6000 |
| 4      | Tom      | 11      |   8000 |
+--------+----------+---------+--------+
4 rows IN SET (0.00 sec)

If you look data, you will find that second maximum salary in this case is 6000 and employee name is Henry. Now let’s see some SQL example to find out this second maximum salary.

Sub queries in SQL are great tool for this kind of scenario, here we first select maximum salary and then another maximum excluding result of subquery. To learn more about Subquery see correlate and non-correlate subquery in SQL

mysql> SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee);
+-------------+
| max(salary) |
+-------------+
|        6000 |
+-------------+
1 row IN SET (0.00 sec)

Here is another SQL query to find second highest salary using subquery and < operator instead of IN clause:

mysql> SELECT max(salary) FROM Employee WHERE salary < (SELECT max(salary) FROM Employee);
+-------------+
| max(salary) |
+-------------+
|        6000 |
+-------------+
1 row IN SET (0.00 sec)

Using Sql -Server

 

TOP keyword of Sybase and SQL Server database is used to select top record or row of any result set, by carefully using TOP keyword you can find out second maximum or Nth maximum salary as shown below.

SELECT TOP 1 salary FROM ( SELECT TOP 2 salary FROM employees ORDER BY salary DESC) AS emp ORDER BY salary ASC

Here is what this SQL query is doing : First find out top 2 salary from Employee  table and list them in descending order, Now second highest salary of employee is at top so just take that value. Though you need to keep in mind of using distinct keyword if there are more than one employee with top salary, because in that case same salary will be repeated and TOP 2 may list same salary twice.

Second maximum salary using LIMIT keyword of MYSQL database

LIMIT keyword of MySQL database is little bit similar with TOP keyword of SQL Server database and allows to take only certain rows from result set. If you look at below SQL example, its very much similar to SQL Server TOP keyword example.

mysql> SELECT salary  FROM (SELECT salary FROM Employee ORDER BY salary DESC LIMIT 2) AS emp ORDER BY salary LIMIT 1;
+--------+
| salary |
+--------+
|   6000 |
+--------+
1 row IN SET (0.00 sec)

Insert multiple selected items of ListBox into SQL Server database

Introduction : -

In this tutorial, we will learn how to create a ListBox control at design-time as well as at run-time. We will also see how to create a multiple-column ListBox control with single and multiple selections. This article also covers most of the properties and methods of the ListBox control.

Description :-
  In my previous article i have used to save list box selected data in multiple column.
This example shows the basics on how to save multiple selected items from the ASP.Net ListBox control to the database in ASP.Net. Please note that this example requires a basic knowledge of ADO.NET.

STEP1: Setting up the User Interface (GUI)

For the simplicity of this demo, I just set up the web form like below:

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        Employee Names: <br />

        <asp:ListBox ID="ListBox1" runat="server" Height="149px" SelectionMode="Multiple" Width="113px">

        <asp:ListItem>chitranjna</asp:ListItem>

        <asp:ListItem>ranjan</asp:ListItem>

        <asp:ListItem>Rohan</asp:ListItem>

        <asp:ListItem>Sohan</asp:ListItem>

        <asp:ListItem>Ronak</asp:ListItem>

        <asp:ListItem>Ranu</asp:ListItem>

        <asp:ListItem>Raja</asp:ListItem>

        <asp:ListItem>papu</asp:ListItem>

        </asp:ListBox>    

    </div>

    <br />

    <asp:Button ID="Button1" runat="server" Text="Save" onclick="Button1_Click" />

    </form>

</body>

</html>

Notes: 

* Since the ListBox is intended for multiple item selections then we need to set the SelectionMode attribute of the ListBox to Multiple

* To do multiple Selections in the ListBox then just hold Ctrl key and select the items you want.
STEP 2: Creating a Simple Database Table

In this demo, we are going to store the selected employee names that is selected from the ListBox to the database. So let's now create a simple table that contains the following Column Names:
Note [-id  is auto increment]

Note: I set the Id to auto increment so that the id will be automatically generated for every new added row. To do this select the Column name “Id” and in the column properties set the “Identity Specification” to yes.

STEP 3: Declaring the necessary name spaces:

Be sure to add the following namespaces below:

using System.Data.SqlClient;

using System.Collections.Specialized;

using System.Text;

We need to declare the namespaces above so that we can use the SqlClient, StrngCollections and StringBuilder built-in methods in our codes later.

STEP4: Creating the Method for Multiple Inserts.

Here are the code blocks below:

private string GetConnectionString()

    {

        //Where DBConnection is the connetion string that was set up in the web config file

        return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"]

.ConnectionString;

    }

    private void InsertRecords(StringCollection sc)

    {

        SqlConnection conn = new SqlConnection(GetConnectionString());

        StringBuilder sb = new StringBuilder(string.Empty);

        foreach (string item in sc)

        {

            const string sqlStatement = "INSERT INTO Table1 (Employees) VALUES";

            sb.AppendFormat("{0}('{1}'); ", sqlStatement, item);

        }

        try

        {

            conn.Open();

            SqlCommand cmd = new SqlCommand(sb.ToString(), conn);

            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();

            Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved!');", true);

        }

        catch (System.Data.SqlClient.SqlException ex)

        {

            string msg = "Insert Error:";

            msg += ex.Message;

            throw new Exception(msg);

        }

        finally

        {

            conn.Close();

        }

    }

    protected void Page_Load(object sender, EventArgs e)

    {

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

        StringCollection sc = new StringCollection();

        foreach (ListItem item in ListBox1.Items)

        {

            if (item.Selected)

            {

                sc.Add(item.Text);

            }

        }

        InsertRecords(sc);

    }

STEP5: Compile and Run the Application.

The page output would look something like below:

On Run Time

 On Selection The Name From the ListBox And Press Save Button

Genrate the Pupop After Insert The Record In the database image as shown below

listbox multiple selection asp.net

Introduction : -

A ListBox control provides an interface to display a list of items. Users can select one or more items from the list. A ListBox may be used to display multiple columns and these columns may have images and other controls.
In this tutorial, we will learn how to create a ListBox control at design-time as well as at run-time. We will also see how to create a multiple-column ListBox control with single and multiple selections. This article also covers most of the properties and methods of the ListBox control.

Description :-
 
This example shows the basics on how to save multiple selected items from the ASP.Net ListBox control to the database in ASP.Net. Please note that this example requires a basic knowledge of ADO.NET.

STEP1: Setting up the User Interface (GUI)

For the simplicity of this demo, I just set up the web form like below:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        #form1
        {
            text-align: center;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        Employee names: <br />

        <asp:ListBox ID="ListBox1" runat="server" Height="149px" SelectionMode="Multiple" Width="113px">

        <asp:ListItem>chitranjan</asp:ListItem>

        <asp:ListItem>ranjan</asp:ListItem>

        <asp:ListItem>Rohan</asp:ListItem>

        <asp:ListItem>Shohan</asp:ListItem>

        <asp:ListItem>Ronak</asp:ListItem>

        <asp:ListItem>Ranu</asp:ListItem>

        <asp:ListItem>Raja</asp:ListItem>

        <asp:ListItem>Papu</asp:ListItem>

        </asp:ListBox>  

    </div>

    <br />

    <asp:Button ID="Button1" runat="server" Text="Save" onclick="Button1_Click" />
    </form>
</body>
</html>

Notes: 

* Since the ListBox is intended for multiple item selections then we need to set the SelectionMode attribute of the ListBox to Multiple

* To do multiple Selections in the ListBox then just hold Ctrl key and select the items you want.
STEP 2: Creating a Simple Database Table

In this demo, we are going to store the selected employee names that is selected from the ListBox to the database. So let's now create a simple table that contains the following Column Names:
Note [-id  is auto increment]

Note: I set the Id to auto increment so that the id will be automatically generated for every new added row. To do this select the Column name “Id” and in the column properties set the “Identity Specification” to yes.

STEP 3: Declaring the necessary name spaces:

Be sure to add the following namespaces below:

using System.Data.SqlClient;

using System.Collections.Specialized;

using System.Text;

We need to declare the namespaces above so that we can use the SqlClient, StrngCollections and StringBuilder built-in methods in our codes later.

STEP4: Creating the Method for Multiple Inserts.

Here are the code blocks below:

protected void Button1_Click(object sender, EventArgs e)
    {
        StringCollection sc = new StringCollection();
        foreach (ListItem item in ListBox1.Items)
        {

            if (item.Selected)
            {

                sc.Add(item.Text);

            }

        }

        InsertRecords(sc);
    }


    private void InsertRecords(StringCollection sc)
    {
        SqlConnection conn = VoterConnectionDB.GetConnection();
        StringBuilder sb = new StringBuilder(string.Empty);
        foreach (string item in sc)
        {

            //For pass the more than one column then use this format
            const string sqlStatement = "INSERT INTO Table1 (city,contactno,Employees) VALUES";
            sb.AppendFormat("{0}('{1}','{2}','{3}');",sqlStatement,"indore","0000000000", item);
           // sb.AppendFormat("{0}('{1}','{2}','{3}');", sqlStatement, sqlStatement[0], sqlStatement[1], item);
           // sb.AppendFormat("{0}('{1}','{2}','{3}');", sqlStatement, txtcity_txt, txt_contact.Text, item);
        }

        try
        {
         SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved!');", true);
        }

        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            conn.Close();
        }

    }

STEP5: Compile and Run the Application.

The page output would look something like below:

On Run Time

 On Selection The Name From the ListBox And Press Save Button

Genrate the Pupop After Insert The Record In the database image as shown below

Download sample code attached

WCF Service Example Step By Step For Beginners

AppendFormat

Protected void btnSaveIterations_Click(object sender, EventArgs e)
{


    int rowIndex = 0;

    StringCollection sc = new StringCollection();

    if (ViewState["CurrentTable"] != null)
    {

        DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];


        if (dtCurrentTable.Rows.Count > 0)
        {

            for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
            {

                //extract the TextBox values
                TextBox box1 = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("start_iteration");
                TextBox box2 = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("end_iteration");

                start_date = box1.Text;
                end_date = box2.Text;

                //get the values from the TextBoxes
                //then add it to the collections with a comma "," as the delimited values

                sc.Add(proj_id + "," + start_date + "," + end_date);

                rowIndex++;

            }

            //Call the method for executing inserts

            InsertRecords(sc);
            Response.Redirect(Request.Url.ToString());

            //r.Close();
            //conn.Close();

        }
    }
}

private void InsertRecords(StringCollection sc)
{

    SqlConnection conn = new SqlConnection(GetConnectionString());
    StringBuilder sb = new StringBuilder(string.Empty);

    string[] splitItems = null;

    foreach (string item in sc)
    {
        const string sqlStatement = "INSERT INTO Iterations (ProjectID, StartDate, EndDate) VALUES (@ProjectID, @StartDate, @EndDate)";

        if (item.Contains(","))
        {

            splitItems = item.Split(",".ToCharArray());
            sb.AppendFormat("{0}('{1}','{2}','{3}'); ", sqlStatement, splitItems[0], splitItems[1], splitItems[2]);

        }
    }

    try
    {

        conn.Open();
        SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
        SqlCommand cmd2 = new SqlCommand(sql, conn);

        cmd.Parameters.Add("@ProjectID", SqlDbType.Int);
        cmd.Parameters.Add("@StartDate", SqlDbType.DateTime);
        cmd.Parameters.Add("@EndDate", SqlDbType.DateTime);

        System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-GB");

        cmd.Parameters["@ProjectID"].Value = proj_id;
        cmd.Parameters["@StartDate"].Value = Convert.ToDateTime(start_date, ci);
        cmd.Parameters["@EndDate"].Value = Convert.ToDateTime(end_date, ci);

        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();

        ...
}

Data Contracts and Service Contracts (WCF)

Introduction : -

A WCF Service is comprised of the following major components. The diagram below shows how the components are related to each other:

• Service Contract
• Operation Contract
• Data Contract
• Data Member

ServiceContract

A ServiceContract is a method that is exposed in a WCF Service, which a client calls to execute the service. The ServiceContract almost always has a return value exposed to the client to check if it was successful, or to see if there are any results. A ServiceContract uses Data Contracts to transfer data to and from the client. It can also use primitive data types to transfer data.

// Student ServiceContract
[ServiceContract]
public interface IStudentService
{
    // Define the OperationContact here….
}

Operation Contract

An operation contract defines the methods of the service that are accessible by external systems. The OperationContract attribute needs to be applied for all these methods, these are also like web methods in a web service. Operation contracts are defined as follows:

// Student ServiceContract
[ServiceContract]
public interface IStudentService
{
 //Define the GetStudentFullName OperationContact here
    [OperationContract]
    String GetStudentFullName (int studentId); 

//Define the GetStudentInfo OperationContact here….
    [OperationContract]
    StudentInformation GetStudentInfo (int studentId);

}

Data Contract

A DataContract is a formal agreement between a service and a client on how data will be exchanged. It also describes how data is to be serialized. A DataContract is used when complex data types are needed to be exchanged between the server and client. A class has to be marked with the DataContract attribute for it to be serialized and exposed in the WCF Service. Any class that does not have the DataContract attribute is not accessible in the WCF Service.
Data Contracts are used by Service Contracts like primitive data types. DataContract objects can be passed as parameters for Service Contracts or returned as values for the Service Contracts.
For more Example you can refer to the article: Using Data Contracts

[DataContract]
public class StudentInformation
{
    // Define the Datamembers here….
}

Data  Member

A data member specifies the type which is part of a data contract used as a composite type member of the contract. To define a data member, apply the DataMember attribute to the fields that must be serialized. The DataMember attribute can be applied to private properties, but they will be serialized and deserialized, and will be accessible to the user or process. The code below shows how to define a data member in a data contract:

[DataContract]
public class StudentInformation
{
      _studentId = studId;

    [DataMember]
    public int StudentId
    {
        get { return _studentId; }
        set { _studentId = value; }
    }

}

Creating WCF Applications with Visual Studio 2010

WCF Web Services

Categories : - Introduction to WCF  , Differences Between WCF and ASP.NET Web Services,
Basic Step by Step WCF WebService ,

 
Introduction : -Here I will explain what WCF (Windows communication foundation) is, uses of windows communication foundation and how to create and use windows communication foundation in c#.


Description : - In my previous article i have explained Basic Step by Step WCF WebService   in Asp.net. Know i am create a wcf webservice which return the JSON Data.

CREATE A WCF WEB  SERVICE


1. Start Visual Studio 2010

2. Create a new project 

Click on File \ New \ Project.

Make sure WCF is selected in the left part of the window, then select the project type WCF Service Application.

In the Name textbox, type in JSONWebService.

We're going to create a .Net Framework 4.0 web service, so make sure this is selected at the top of the dialog.

Then click on OK.

To do this, we need to make some changes to the sample code. First, we need to change GetData's parameter type from "int" to "string"

Open up the Service1.svc.cs file, and change the "value" parameter to be a string:

Next.....

Javascript validation for Gridview in edit mode

Categories :- Javascript validation for Gridview footer textbox , Advantages of wcf
 
Introdouction : -

In my Article I am explain how  in can use java script for  validate text box in footer row and check text box in now then generate  the alert please insert Record using  ASp.Net 

Description : -

In My previous Post I have Explained Javascript validation for Gridview footer textbox  in ASp.Net  .There is good source code of  WCF . Now  Java script Validation for Gridview for GridView in Edit Mode.

Here gridview have dropdownlist and two textbox.This javascript validate these fields in edit mode.

function ValidateGridEditMode()
{
var Gid = document.getElementById('ctl00_ContentPlaceHolder1_TabContaner_tabCustomer
_gvCustomer').rows.length;
var i;
for(i=2; i <=Gid; i++)
{
if(i<10)
{
ddlCustLocation = document.getElementById('ctl00_ContentPlaceHolder1_TabContaner_tabCustomer
_gvCustomer_ctl0'+i+'_ddlCustLocation');
txtCustCode = document.getElementById('ctl00_ContentPlaceHolder1_TabContaner_tabCustomer
_gvCustomer_ctl0'+i+'_txtCustCode');
txtCustName = document.getElementById('ctl00_ContentPlaceHolder1_TabContaner_tabCustomer
_gvCustomer_ctl0'+i+'_txtCustName');
}

else
{
ddlCustLocation = document.getElementById('ctl00_ContentPlaceHolder1_TabContaner_tabCustomer
_gvCustomer_ctl'+i+'_ddlCustLocation');
txtCustCode = document.getElementById('ctl00_ContentPlaceHolder1_TabContaner_tabCustomer
_gvCustomer_ctl'+i+'_txtCustCode');
txtCustName = document.getElementById('ctl00_ContentPlaceHolder1_TabContaner_tabCustome
r_gvCustomer_ctl'+i+'_txtCustName');
}


ddlCustLocation != null || txtCustCode != null || txtCustName != null)
{
ddlCustLocation == 0)
{
alert("Please Select Location..");
ddlCustLocation.focus();
return false;
}
else
{
if(txtCustCode.value == 0)
{
alert("Please enter Customer Code..");
txtCustCode.focus();
return false;
}
else
{
if(txtCustName.value == "")
{
alert("Please enter Customer Name ..");
txtCustName.focus();
return false;
}
}
}
}
}
return true;
}