Total Pageviews

Monday, 7 November 2016

1.Display deptno, sumsal along with dname?

select e.deptno,d.dname,sum(e.sal) from emp e,dept d where e.deptno=d.deptno
group by e.deptno,d.dname

2.Display deptno wise average salaries?


select deptno,avg(sal) from emp group by deptno;


3.Displaay Highest salary  from emp?


Select max(sal) from emp;


4.Display rows from  5 to 7?


select * from emp where rownum<=7 minus select * from emp where rownum<5;


5.Display 5thn row from table?


select * from emp where rownum<=5 minus select * from emp where rownum<5


6.Display duplicate records from the table?


select empno from emp group by empno having count(*)>1;


7.Display unique records from table?


select empno from emp group by empno;


8.Display highest salary from the table?or Top 4 salaries?


select * from (select * from emp order by sal desc) where rownum<2; or where rownum<=4;


9.Display lowest salary from table:


select * from (select * from emp order by sal) where rownum<2;


10.Display dept wise highest salary from table?


select deptno,max(sal) from emp group by deptno;


11.Remove duplicate rows from table permanently?


delete from emp_dat where rowid not in(select min(rowid) from emp_dat group by empno);


12.Retrieve all records except last record?


select empno,ename,sal,deptno from emp where rowid not in(select max(rowid) from emp);


13.Update all rows except first record?


update emp set sal=sal+1000 where rowid not in(select min(rowid) from emp);


14.Delete the duplicate rows in a table permanently


--Delete from emp where rowid not in(select max(rowid) from emp group by empno)

--select distinct deptno from emp;

15. Display 2nd highest salary from employee table


--Select sal from (select rownum r ,sal from (select rownum, sal from emp order by sal desc)) where r=2;


If we want total record


Select * from (select rownum r, sal,empno,ename,deptno from (select rownum,sal from emp order bysal desc)) where r=2;


16.Write a query to select top 5 salaries?


Select * from (select * from emp order by sal desc) where rownum<=5;


17.Display max sal for each dept?


Select deptno,max(sal) from emp group by deptno;


18.Display last row of the table?


Select * from emp where rowid=(select max(rowid) from emp);


19.select even rows from table?


Select * from  (select rownum r from emp)where mod(r,2)=0;


20.Display ‘mth’ to nth rows from table?


Select * from emp where rownum<=&m Minus Select * from emp where rownum<=&n;


21.Top 3 salaries in each dept?


select * from emp a where 3>= (select count(b.sal) from emp b

where a.deptno=b.deptno and b.sal>=a.sal) order by deptno;

22.First highest salary from each dept?                              


select * from emp a where 1>=(select count(b.sal) from emp b

where a.deptno=b.deptno and a.sal<=b.sal) order by deptno;
select ename,deptno,sal from emp where sal in(select max(sal) from emp group by deptno);

23.Find out Cumulative salary?


Select ename,daptno,sum(sal) from OVER(PARTITION BY deptno order by sal desc,ename)”Commulative” from emp;


24.Display max sal from emp table?

Select  * from emp where &N=(select count(*) from emp);
select * from emp where SAL=(select max(sal) from emp);

OR


select * from emp a where 1=(select count(b.sal) from emp b where b.sal>=a.sal);


OR 


select * from emp a where 2=(select count(b.sal) from emp b where b.sal>=a.sal);


25.Display min sal from emp table?


select * from emp a where 1=(select count(b.sal) from emp b where b.sal<=a.sal);


26.Display 4th ,8th ,12th  in the table (if the table has rows 4,8,12……)?


select * from emp where (rowid,0) in(select rowid,mod(rownum,4)from emp);


27.Duplicates display based on salary?


select * from emp a where rowid not in(select min(rowid) from emp b where b.sal=a.sal);


28.Display the rows from M to N ex: Display 5 to 7?


select * from emp where rowid in(select rowid from emp where rownum<=7

minus select rowid from emp where rownum<5);

29.Get the 1st highest paid salary in each department?


OR 


Dept wise 1st highest getting salary?


Select * from (select ename,deptno,sal,RANK() OVER(PARTITION BY deptno order by sal desc)”Topsal” from emp) where “topsal” IN(1);


30.Getting alternate rows from a table


select a.* from (select rownum as row_num,T.* from T) a where mod (a.row_num,2) = 1;


OR 

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


select count(*), rownum from emps group by rownum having mod(rowmum,2) = 1;


OR


SELECT * FROM emp WHERE (rowid 1) IN (SELECT rowid mod(rownum 2) FROM emp);



 OR

SELECT * FROM ( SELECT rownum rn empno ename

FROM emp) temp WHERE MOD(temp.rn 2) 1

31.even number query.


SELECT * FROM ( SELECT rownum rn, empno, ename FROM emp) temp

WHERE MOD(temp.rn 3) 0;

Or

To Display even row numbers.
select * from emp where (rowid 0) in (select rowid mod(rownum 2) from emp ) 

To Display odd row numbers.

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


Or


SELECT * FROM (SELECT ROWNUM RN FIRST_NAME FROM EMPLOYEES) TEMP WHERE MOD (TEMP.RN 2) 1;


CHANGE LAST NUMBER 1 TO 0 FOR EVEN NUMBERED ROWS.

Basically in this I used a subquary and selected all rows where RN was holding ROWNUM values then outer SELECT was used to check with MOD function.


32.cumulative salary from emp based on job?


select job, ename, sal, sum(sal) over (partition by job order by sal,ename) CumDeptTot

from emp;

33.Display Max (sal) for each dept in emp table?


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


OR


select * from emp where empno in (select empno from emp where sal in

(select max(sal) from emp group by deptno));

OR


select e.* from emp e where e.sal in (select max(e.sal) from emp e group by e.deptno);


34.How to delete duplicate records.


Delete form emp where rowid not in(select min(rowid) from emp group by column name);


OR


delete from customer where rowid in (select rowid from(select rowid,row_number() over (partition by custnbr order by custnbr) dup from customer) where dup > 1);


35.In which dept employees are not working?


select dept_name from dept where dept_id not in ( select dept_id from emp)


36.How to find first record and last record.


select * from emp where rownum<=1 UNION (select * from emp where rownum<=14

minus select * from emp where rownum<=13);

OR


Select ename from (select rownum RN, ename from emp) Where rn in(1,14);


37. How to get last some records ?


select * from (select rownum a, column1,column2 ,...,columnn from my_table)

where a > ( select (max(rownum)-10) from my_table);