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)

alter table add multiple column sql server 2008

The SQL ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.
To rename a table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  RENAME TO new_table_name;

For example:

ALTER TABLE suppliers
  RENAME TO vendors;

This will rename the suppliers table to vendors.

SQL ALTER TABLE - Adding column(s) to a table

Syntax #1
To add a column to an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD column_name column-definition;

For example:

ALTER TABLE supplier
  ADD supplier_name varchar2(50);

This will add a column called supplier_name to the supplier table.
Syntax #2
To add multiple columns to an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD (column_1 column-definition,
       column_2 column-definition,
       ...
       column_n column_definition);

For example:

ALTER TABLE supplier
  ADD (supplier_name varchar2(50),
       city varchar2(45));

This will add two columns (supplier_name and city) to the supplier table.

SQL ALTER TABLE - Modifying column(s) in a table

Syntax #1
To modify a column in an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  MODIFY column_name column_type;

For example:

ALTER TABLE supplier
  MODIFY supplier_name varchar2(100) not null;

This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.
Syntax #2
To modify multiple columns in an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  MODIFY (column_1 column_type,
          column_2 column_type,
          ...
          column_n column_type);

For example:

ALTER TABLE supplier
  MODIFY (supplier_name varchar2(100) not null,
          city varchar2(75));

This will modify both the supplier_name and city columns.

SQL ALTER TABLE - Drop column(s) in a table

Syntax #1
To drop a column in an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  DROP COLUMN column_name;

For example:

ALTER TABLE supplier
  DROP COLUMN supplier_name;

This will drop the column called supplier_name from the table called supplier.

SQL ALTER TABLE - Rename column(s) in a table
(NEW in Oracle 9i Release 2)

Syntax #1
Starting in Oracle 9i Release 2, you can now rename a column.
To rename a column in an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  RENAME COLUMN old_name to new_name;

For example:

ALTER TABLE supplier
  RENAME COLUMN supplier_name to sname;

This will rename the column called supplier_name to sname.

Practice Exercise #1:

Based on the departments table below, rename the departments table to depts.

CREATE TABLE departments
( department_id number(10) not null,
  department_name varchar2(50) not null,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution:

The following SQL ALTER TABLE statement would rename the departments table to depts:

ALTER TABLE departments
  RENAME TO depts;

Practice Exercise #2:

Based on the employees table below, add a column called salary that is a number(6) datatype.

CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  department_id number(10),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution:

The following SQL ALTER TABLE statement would add a salary column to the employees table:

ALTER TABLE employees
  ADD salary number(6);

Practice Exercise #3:

Based on the customers table below, add two columns - one column called contact_name that is a varchar2(50) datatype and one column called last_contacted that is a date datatype.

CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution:

The following SQL ALTER TABLE statement would add the contact_name and last_contacted columns to the customers table:

ALTER TABLE customers
  ADD (contact_name varchar2(50),
       last_contacted date);

Practice Exercise #4:

Based on the employees table below, change the employee_name column to a varchar2(75) datatype.

CREATE TABLE employees
( employee_number number(10) not null,
  employee_name >varchar2(50) not null,
  department_id number(10),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution:

The following SQL ALTER TABLE statement would change the datatype for the employee_name column to varchar2(75):

ALTER TABLE employees
  MODIFY employee_name varchar2(75);

Practice Exercise #5:

Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a varchar2(2) datatype.

CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50),
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution:

The following SQL ALTER TABLE statement would modify the customer_name and state columns accordingly in the customers table:

ALTER TABLE customers
  MODIFY (customer_name varchar2(50) not null,
          state varchar2(2));

Practice Exercise #6:

Based on the employees table below, drop the salary column.

CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  department_id number(10),
  salary number(6),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution:

The following SQL ALTER TABLE statement would drop the salary column from the employees table:

ALTER TABLE employees
  DROP COLUMN salary;

Practice Exercise #7:

Based on the departments table below, rename the department_name column to dept_name.

CREATE TABLE departments
( department_id number(10) not null,
  department_name varchar2(50) not null,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution:

The following SQL ALTER TABLE statement would rename the department_name column to dept_name in the departments table:

ALTER TABLE departments
  RENAME COLUMN department_name to dept_name;

----------------------------------------------------------------------------------------------------\
Example For Alter Multiple Coloumn in sql server
dfdsfd