• Do first left join

--Get the second highest salary of HR department

select * from #employee1
select * from #department1

;with cte(empname,sal,row)
as
(
select empname,empsal,ROW_NUMBER() OVER (Order by empsal desc) AS RowNumber from #employee1 e
join #department1 d on e.depid=d.depid
where d.depname='HR'
)
select * from cte where row=2

These are all basics but when asked in interview we make mistake.


select * from #temp












select AVG(id) from #temp  [5/3 or 5/5]







select COUNT(*) from #temp [5 or 3]






select distinct(id) from #temp [1,2 or 1,2,null]

select * from EmployeeDetail

Id EmployeeName
1 Anurag Nayak
2  Ritika mohanty
3     Siba Dalai
4 Biswa Panda

Trick 1 ;with cte(id,name,finalIndex,startIndex)as
(
select id,
EmployeeName,
LEN(EmployeeName) as LengthName,
CHARINDEX(' ',EmployeeName) as StartIndex from dbo.EmployeeDetail
)
select id,name,SUBSTRING(name,startIndex+1,finalIndex) as final from cte 
order by final


id       name                               final
3 Siba Dalai                       Dalai
2 Ritika mohanty
1 Anurag Nayak                Nayak
4 Biswa Panda                   Panda



Trick 2

select EmployeeName
from employeedetail
order by REVERSE(substring(reverse(EmployeeName),1,CHARINDEX(' ',reverse(EmployeeName))))

select * from EmployeeSalary order by id














--o/p [Need employee id and the count that crossed more than 5000]
--employeeid and count(> 5000)
--1 1
--2 1
--3 2

select id,COUNT(EmployeeSalary) from EmployeeSalary
where EmployeeSalary > 5000
group by id





--Now i want those employees id who has more than 1 count [> 5000] salary.

select id,COUNT(EmployeeSalary) from EmployeeSalary
where EmployeeSalary > 5000
group by id
having COUNT(EmployeeSalary) >1








--> After Group by No Where clause we know that.