FirstName LastName JoinDate Salary Department

Rakesh Kalluri 2012-07-01 10:00:00.000 20000 Software

Shabari Vempati 2011-05-01 10:00:00.000 25000 Software
Venkatesh Bodupaly 2013-04-01 10:00:00.000 15000 Bpo
Surjan Peddineni 2011-07-01 10:00:00.000 25000 Software
Nani Ch 2010-07-01 10:00:00.000 50000 Software
Raju Chinna 2012-07-01 10:00:00.000 25000 admin
Kiran Kumar 2011-07-01 10:00:00.000 20000 Software
Raki Kumar 2012-07-01 10:00:00.000 17000 Bpo
Sri Vidya 2011-07-01 10:00:00.000 30000 admin
Fehad MD 2013-07-01 10:00:00.000 20000 Bpo
Anusha Kumari 2011-07-01 10:00:00.000 35000 Software
Venky Naidu 2013-07-01 10:00:00.000 20000 Bpo
Radha Kumari 2012-07-01 10:00:00.000 10000 Bpo
--create Employee table
create table Employee
(
EmpId int identity(1,1) primary key,
FirstName varchar(100),
LastName varchar(100),
JoinDate datetime ,
Salary int ,
Department varchar(20)
)
--Insert data to Employee table
insert into
Employee(FirstName,LastName,JoinDate,Salary,Department)values('Rakesh','Kalluri','2012
-07-01 10:00:00.000',20000,'Software')
insert into
Employee(FirstName,LastName,JoinDate,Salary,Department)values('Shabari','Vempati','201
1-05-01 10:00:00.000',25000,'Software')
insert into
Employee(FirstName,LastName,JoinDate,Salary,Department)values('Venkatesh','Bodupaly','
2013-04-01 10:00:00.000',15000,'Bpo')
insert into
Employee(FirstName,LastName,JoinDate,Salary,Department)values('Surjan','Peddineni','20
11-07-01 10:00:00.000',25000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)
values('Nani','Ch','2010-07-01 10:00:00.000',50000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)
values('Raju','Chinna','2012-07-01 10:00:00.000',25000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)
values('Kiran','Kumar','2011-07-01 10:00:00.000',20000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)
values('Raki','Kumar','2012-07-01 10:00:00.000',17000,'Bpo')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)
values('Sri','Vidya','2011-07-01 10:00:00.000',30000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)
values('Fehad','MD','2013-07-01 10:00:00.000',20000,'Bpo')
insert into
Employee(FirstName,LastName,JoinDate,Salary,Department)values('Anusha','Kumari','2011-
07-01 10:00:00.000',35000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)
values('Venky','Naidu','2013-07-01 10:00:00.000',20000,'Bpo')
insert into
Employee(FirstName,LastName,JoinDate,Salary,Department)values('Radha','Kumari','2012-
07-01 10:00:00.000',10000,'Bpo')

//Write a query to top 1 experienced employee in a table along with full name

Like Below
 FullName JoinDate Top1
NaniCh 2010-07-01 10:00:00.000 1
//Write a query to top 1 less experienced employee in a table along with full name

Like Below

FullName JoinDate Top1

FehadMD 2013-07-01 10:00:00.000 1
//Write a query to give ranks based on high salary employees in a table along with
full name like below

FullName Salary Top1

NaniCh 50000 1
AnushaKumari 35000 2
SriVidya 30000 3
RajuChinna 25000 4
ShabariVempati 25000 5
SurjanPeddineni 25000 6
RakeshKalluri 20000 7
KiranKumar 20000 8
FehadMD 20000 9
VenkyNaidu 20000 10
RakiKumar 17000 11
VenkateshBodupaly 15000 12
RadhaKumari 10000 13

//Write a query to give ranks based on less salary employees in a table along with
full name

FullName Salary Top1

RadhaKumari 10000 1
VenkateshBodupaly 15000 2
RakiKumar 17000 3
KiranKumar 20000 4
RakeshKalluri 20000 5
VenkyNaidu 20000 6
FehadMD 20000 7
ShabariVempati 25000 8
SurjanPeddineni 25000 9
RajuChinna 25000 10
SriVidya 30000 11
AnushaKumari 35000 12
NaniCh 50000 13

//Write a query to give ranks Department wise based on salary in a table

FullName Department Salary Rank_

RajuChinna admin 25000 1
SriVidya admin 30000 2
RadhaKumari Bpo 10000 1
VenkateshBodupaly Bpo 15000 2
RakiKumar Bpo 17000 3
VenkyNaidu Bpo 20000 4
FehadMD Bpo 20000 5
KiranKumar Software 20000 1
 RakeshKalluri Software 20000 2
ShabariVempati Software 25000 3
SurjanPeddineni Software 25000 4
AnushaKumari Software 35000 5
NaniCh Software 50000 6

select FirstName,LastName,(JoinDate),Salary,Department from employee

--top 1 experienced employee
select top 1 concat (FirstName,LastName) as FullName,JoinDate, ROW_NUMBER()over(order
by joindate) AS Top1 from employee
--top 1 less experienced employee
select top 1 concat (FirstName,LastName) as FullName,JoinDate, ROW_NUMBER()over(order
by joindate desc) AS Top1 from employee
--Department wise based on salary
select concat (FirstName,LastName) as FullName,Department,Salary,
ROW_NUMBER()over(partition by department order by Salary asc) AS Top1 from employee
--Department wise based on salary
select concat (FirstName,LastName) as FullName,Department,Salary,
ROW_NUMBER()over(partition by department order by Salary asc) Rank_ from employee