Tips and Tricks for SQL Queries

Tips and Tricks for SQL Queries
( SQL Server 2000/later )

Table Design
--------------
EmployeeName Varchar(50)
JobGrade Char(1)
NetSalary Money

Insert rows
-----------------
CrissBron A 7000.00
MikeMiles A 5600.00
John A 5000.00
Armados B 3000.00
LeeBron C 2100.00
ThoiChin C 2000.00


1) Find all employees salary starts from Hign to Low
Select * from Employee Order bY NetSalary Desc

2) Find Nth Maximum Salary of employee
a) finding 2nd maximum salary employee
Select Min(netSalary) From
(Select Top 2 NetSalary From Employee Order By NetSalary desc)
As A
b) Finding 5th maximum salary employee
Select Min(netSalary) From
(Select Top 5 NetSalary From Employee Order By NetSalary desc)
As A

3) Add Sequence Number/Row Number and display all rows

Select * from
(
Select ROW_NUMBER() OVER(ORDER BY EmployeeName DESC) As
RowNo,
EmployeeName,
JobGrade,
NetSalary
From Employee
) as A


4)Create New table and record Cloning
Select * Into NewEmployee From Employee
: NewEmployee is new table name