To find all names who have same salry of eid=10

create table emp(eid int,ename varchar(20),esal money)

insert into  emp values(10,'Raju',200)

insert into  emp values(20,'ramu',200)

insert into  emp values(30,'muttu',300)

insert into  emp values(40,'vikarm',400)

insert into  emp values(50,'vina',500)

insert into  emp values(60,'guru',200)

select * from emp

--To find all names who have same salry of eid=10

select e2.eid,e2.esal,e2.ename  from emp e1,emp e2

where e1.eid=10 and e2.esal=e1.esal

Single updation change gender type

Create table empGender(eid int,ename varchar(20),eGender varchar(1))

insert into empGender values(1,'Raju','M')

insert into empGender values(2,'mura','M')

insert into empGender values(3,'kiran','M')

insert into empGender values(4,'rani','M')

insert into empGender values(5,'vani','F')

insert into empGender values(6,'ramya','F')

select * from empgender

update empgender set eGender=case eGender when 'M' Then 'F'   ELSE 'M' END            

select * from empgender

EID vs ManagerName

I have a table  empsalary with columns as eid,ename,emanager I need result table as

Create table empSalary(eid int,ename varchar(20),esal money,eManager int null)

insert into empsalary values(1,'rghu',20000,null)

insert into empsalary values(2,'en',15000,1)

insert into empsalary values(3,'ven',10000,2)

insert into empsalary values(4,’murthys’,15000,1)

insert into empsalary values(5,'karim',10000,2)

insert into empsalary values(6,'sir',18000,2)

insert into empsalary values(7,'siri',20000,null)

select *from empsalary order by esal desc

EId,Ename.Manager

select e.eid,e.ename,isnull(s.ename,'No Manager') as manager from empsalary e

left outer join (select * from empsalary )as  s on s.eid=e.emanager

Nth higest salary

Create table empSalary(eid int,ename varchar(20),esal money,eManager int null)

insert into empsalary values(1,'rghu',20000,null)

insert into empsalary values(2,'en',15000,1)

insert into empsalary values(3,'ven',10000,2)

insert into empsalary values(4,’murthys’,15000,1)

insert into empsalary values(5,'karim',10000,2)

insert into empsalary values(6,'sir',18000,2)

insert into empsalary values(7,'siri',20000,null)

select *from empsalary order by esal desc

--This example 2nd highest salary

--for nth highest salry replace 1 with n-1

select * from empsalary as a where 1=(select count(distinct (esal)) from empsalary as b where b.esal>a.esal)

Max year of every employee salary

Create table employee(eid int,esal money, ename varchar(20))

insert into employee values(1,'raju')

insert into employee values(2,'ramu')

Create table salary(eid int,esal money,dateyear int)

insert into salary values(1,3000,2009)

insert into salary values(1,6000,2010)

insert into salary values(1,3000,2011)

insert into salary values(1,300,2020)

insert into salary values(2,5000,2009)

insert into salary values(2,9000,2010)

Select eid,esal,dateyear from salary

/*           eid          esal                        dateyear

1              3000.00                2009

1              6000.00                2010

1              3000.00                2011

2              5000.00                2009

2              9000.00                2010*/

select t.eid,ename,esal,dateyear from employee as e inner join (

select s.eid,s.esal,s.dateyear from salary as s inner join

(select eid,max(dateyear) as dateyear from salary group by eid) as a

on a.eid=s.eid and s.dateyear=a.dateyear) t on  e.eid=t.eid

/*--Result:

2              ramu     9000.00 2010

1              raju        300.00                  2020*/