Interesting T - SQL Queries (T - SQL Interview Questions)

Question 1
Write a single DML statement to replace 0 with 1 & 1 with 0?

Answer
create table #temp

(nm varchar(10),

id int)



insert into #temp values

('one', 1), ('zero', 0), ('one', 1), ('zero', 0), ('one', 1),

('zero', 0), ('one', 1), ('zero', 0), ('one', 1), ('zero', 0)



select * from #temp



update #temp

set id = case when id = 1 then 0

when id = 0 then 1

end



select * from #temp


Question 2
Write a Query to get maximum 3rd salaried employee
create table #emp

(empid int,

empsal money)

insert into #emp values(1, 25000), (2, 30000), (3, 15000), (4, 40000), (5, 22000) ,(6, 18000)

select * from #emp order by empsal desc



--using top keyword
 select top 1 * from



(
  select top 3 * from #emp

order by empsal desc

)a

order by empsal asc



--using ranking function
 select empid, empsal



from
 (
  select empid, empsal, RANK() over (order by empsal desc) rnk

from #emp

)a

where a.rnk = 3



--using corelated subquery
 select *

from #emp e1

where (3-1) = (select COUNT(distinct (e2.empsal)) from #emp e2 where e2.empsal > e1.empsal)