ORACLE SQL Queries

Running Total ……….

select empno,sal,deptno, sum(sal)over(partition by deptno order by sal ) Running_Total
from emp;

Any Highest Salary…..

select a.*, row_number() over(order by sal desc) Order_Sal from emp a;

Max Sal in Every department. :

select empno,deptno,sal from emp where (sal,deptno) in(select max(sal),deptno from
emp group by deptno)

Retrieving Duplicate Employeesss.......

select empno,deptno from emp where (empno, deptno) in (select empno,deptno from emp group by
deptno,empno having count(deptno)>1)

Deleting Duplicate Records………..

Delete from emp a where rowid not in (select min (rowid) from emp b where a.empno = b.empno);


Dispaly employee records who gets more salary than the average salary in their department?

select a.* from emp a,
(select deptno,avg ( sal) avg_sal from emp group by deptno) b
where a.deptno= b.deptno and a.sal > b.avg_sal

Write a query to display alternate records from the employee table?

Select * from emp where (rowid,1) in (select rowid,mod(rownum,2) from emp);

Write a query to display employee records having same salary?

Select a.empno,a.ename,a.sal from scott.emp a where a.sal in

(select sal from scott.emp group by sal having count(*)>1)




What is output of this SQL where current value of sequence is 3?

Select seq.curval,seq.Nextval,seq.Nextval from dual ? Give reason?

U r output wll be...

CURRVAL NEXTVAL NEXTVAL
----------- ----------- -----------
3 3 3

Coz , sequence are stored objects in oracle and Incremented only when they are use with in executable code, let Us C.....


How to compare the total salary of all employee with a individual employee salary for a particular department.

Select empno, sal/( select sum ( a.sal ) from emp a ) total_sal
from emp;

Or

Select empno, sal, sal/(sum(sal) over()) sum_sal from emp;


Reverse Pivoting in Oracle

select id, sum(a1_amt) a1_amt,sum(a2_amt) a2_amt,sum(a2_amt) a3_amt from (
select a.id,
decode(a.name,'a1',sum(a.amount))a1_amt ,
decode(a.name,'a2',sum(a.amount)) a2_amt,
decode(a.name,'a3',sum(a.amount)) a3_amt
from
reverse_pivot a group by a.id,a.name) group by id
/

Or…

select a.id,sum(decode(a.name,'a1',amount)) A1,
sum(decode(a.name,'a2',amount)) A2,
sum(decode(a.name,'a3',amount)) A3
from reverse_pivot a group by a.id




I want to first display the employees of deptid=30, then all the employees of all the departments in asceding order within the same query

select * from emp order by decode(deptno,30,0,deptno);

To Generate the series of characters from 'A' to 'Z' and 'a' to 'z'.

Select chr(l+64), chr(l+96) from (select level l from dual connect by rownum between 1 and 26)


Max Salary on basis of location

Select f1.* from ( select a11.*, row_number() over (partition by loc order by sal desc) r
from (
select distinct a.*,c.loc from emp a, (select deptno, max(sal) sal from emp group by deptno) b,
dup_dept c
where a.deptno = b.deptno
and
a.deptno = c.deptno) a11 ) f1
where f1.r = 1


Or

Select d.* from (select c.*, row_number() over(partition by loc order by sal desc) rn from
(select a.*,loc from (select max(sal) sal,deptno from emp group by deptno)a,dept b where a.deptno=b.deptno)c) d
Where d.rn=1
/