IMPORTANT QUERIES?

--1)Duplicate records

with cte as
(

select *,ROW_NUMBER() over(partition by id order by id) as
 duplicateids from duptable
)
delete from cte where duplicateids>1

select * from employee

--2)highest salary
with cte as
(
select salary,ROW_NUMBER() over ( order by salary desc) as Highsalaries
 from employee

)
select salary from cte  where  Highsalaries=3

--3)highest salary without using subquery,cte,functions?

select * from employee a where 1=(select COUNT(distinct salary) from employee B where a.salary>B.salary)



select ISNULL(t1.id,t2.id) from tab1 t1
right join
tab2 t2
on
t1.id=t2.id
where t2.id =null


--4)inner join result
SELECT isnull(t1.id,t2.ID)
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID


--5)left join result

select isnull(t1.id,t2.id )
from
tab1 t1 left join  tab2 t2
on t1.id=t2.id
where t2.id is null

--6)self join example

select * from Employeeselfjoin

select e2.name as   emp ,e1.name as mgr
from
Employeeselfjoin e1
inner join
Employeeselfjoin e2
on
e1.EmployeeID=e2.ManagerID

--7)how to seperate the domain name from a given name

select SUBSTRING('yjreddy@yahoo.com',
                   charindex('@','yjreddy@yahoo.com')+1,LEN('yjreddy@yahoo.com'))

select CHARINDEX('@','yjreddy@yahoo.com')+1





create table Nametablesubstring(Name varchar(max))

insert into Nametablesubstring values('yjredy'),('vsrikar'),('Mershad'),('yanil')


--8)Substring Example
select SUBSTRING([name],1,1) as intital from Nametablesubstring

--9)Replace and stuff

select REPLACE('yjreddy ?','?','cricketer and activist')


select STUFF('janardhanareddy',2,10,'india')


--10)update a table using case

update table set salary=(
case
 when salary=20000 then salary+5000
 when salary=30000 the salary+2000
 else
 salary+5000
 end