–update table with swap between two columns
update mmm_1
set id=phone,phone=id;
–delete duplicate value from data table and select not duplicate also
with x as( select empid,lname,deptno,rn=ROW_NUMBER() over (partition by deptno order by deptno),rn1=dense_rank() over (partition by deptno order by deptno),
rn2=Rank() over ( order by deptno) from employees)
delete x where x.rn>1;
–delete all duplicate values
delete employees where deptno in ( select (deptno)from employees group by deptno having COUNT(deptno)>1);
select * from employees;
–select total salary departmentvise and employee details
with z as( select y.*,e2.empid,(e2.fname+’ ‘+e2.lname) as fullname,rn=ROW_NUMBER() over(partition by e2.deptno order by e2.deptno asc) from employees e2,( select e1.deptno,SUM(e1.sal) as sal from employees e1 group by deptno)y where y.deptno=e2.deptno )
select * from z where rn=1;
–select manager name in self join
select
(e1.fname+’ ‘+e1.lname)empname,e1.empid,e1.man_id,(e2.fname+’ ‘+e2.lname)as managname
from employees e1
left join employees e2
on e1.man_id=e2.empid
select * from employees e1;