Top 3 Salaries:
1) select * from emp e1 where 3>(select count(distinct sal) from emp e2 where e1.sal<e2.sal) order by sal desc;
Top “N” Salaries:
2) select * from emp e1 where &n>(select count(distinct sal) from emp e2 where e1.sal<e2.sal) order by sal desc;
least “3” Salaries:
3) select * from emp e1 where &n>(select count(distinct sal) from emp e2 where e1.sal>e2.sal) order by sal desc
least “N” Salaries:
4) select * from emp e1 where &n>(select count(distinct sal) from emp e2 where e1.sal>e2.sal) order by sal desc
Top salary: (OR) 1st salary
5) select * from emp e1 where 1-1=(select count (distinct sal) from emp e2 where e1.sal<e2.sal);
To eliminate the duplicates Records from table:
6)select * from emp where rowid in (select min(rowid) from emp group by sal);
To Retrieve Only duplicates Records from table:
7) select * from emp where rowid not in (select min(rowid) from emp group by sal);
To Change The Table name
8) Rename OldTableName to NewTableName;
To Change the column name:
9) Alter table TableName Rename column OldColumnName to NewColumnName
All Clauses included in this Query
10) select deptno,job,sum(sal) from emp where deptno in(10,20) group by deptno,job having sum(sal)>1000 order by sum(sal) desc
Dept whose not having employees.
Select dname,deptno,loc from dept d where not exists(select * from emp e where e.deptno=d.deptno);
Selecting the employees whose belongs to pirtculer manager name=’BLAKE’:
1)select empno from emp where ename=’BLAKE’;
2) select * from emp where mgr=7698;
OR
3) select * from emp where mgr=(select empno from emp where ename='BLAKE');
To select the last record of the table;
select * from emp where rowid=(select max(rowid) from emp);
11) we have 200 records from theat records I want 50 th record name,sal,job, rownum
Select * from(select ename,job,sal,rownum r from emp where rownum<14) where r=5;
12) TO GET THE FIRST AND LAST RECORD.
Select * from emp where rowid=(select min(rowid) from emp)
Select * from emp where rowid=(select max(rowid) from emp);
13) TO GET WITH OUT FIRST AND LAST RECORD;
select * from emp
minus
select * from emp where rowid=(select min(rowid) from emp)
minus
select * from emp where rowid=(select max(rowid) from emp);
14) TO GET 7 RECORDS WITH OUT DUPLICATES.
Select * from emp e1 where 7>(select count(distinct sal) from emp e2 where e1.sal<e2.sal)
Minus
Select * from emp where rowid not in(select min(rowid) from emp group by sal);
MATERIALIZED VIEW
15) CREATE MATERIALIZED VIEW NAGESH1 AS SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB;
16) CREATE VIEW NAGESH12 AS SELECT * FROM EMP;
17) QUARY THAT WILL DISPLAY THE TOTAL NO OF EMPLOYEES, AND OF THE TOTAL NUMBER WHO WERE HIRED IN 1980,1981,82,83 ;
17) select Count(*),count(decode(to_char(hiredate,'yyyy'),'1980',empno))"1980" from emp;
18) Query that retrieves “2” highest paid employees from each department
Select deptno,empno,sal from emp e where 2>(select count(e1.sal) from emp e1 where e.deptno=e1.deptno and e.sal<e1.sal)order by 1,3 desc
18) The deptno which is having Maximum avg sal
select deptno,avg(sal) from emp having avg(sal)=(select max(avg(sal)) rom emp group by deptno) group by deptno
19) Department name which is having maximum average sal
select dname,avg(sal) from emp,dept where emp.deptno=dept.deptno having
avg(sal)=(select max(avg(sal)) from emp,dept where emp.deptno=dept.deptno group by dname ) group by dname;
20) self join
select e.ename employee,e1.ename manager from emp e,emp e1 where e.mgr=e1.empno;
