1. SQL> select empno, ename from emp where deptno=(select deptno from dept where dname='RESEARCH');
2. SQL> select empno, ename from emp where deptno in (select deptno from dept where loc in ('NEW YORK','CHICAGO'));
3. SQL> select dname from dept where deptno in ( select deptno from emp where job ='ANALYST');
4. SQL> select empno, ename, mgr from emp where mgr = (select empno from emp where ename='JONES');
5. SQL> select empno, ename, mgr from emp where mgr = (select mgr from emp where ename='JONES')
6. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','ACCOUNTING'))
7. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','RESEARCH')) and empno in (select mgr from emp)
8. SQL> select empno, ename from emp where empno not in ( select mgr from emp where mgr is not null)
9. select empno, ename from emp where empno in (select mgr from emp group by mgr
having count(*) >= 2)
10. SQL> select dname from dept where deptno in (select deptno from emp group by deptno having count(*) >=5)
11. SQL> select deptno, job, count(*) from emp where job = 'SALESMAN' group by deptno, job having count(*) >=3
12. SQL> select empno, ename, deptno from emp where empno in (select mgr from emp group by mgr
having count(*) >= 2) and deptno in (select deptno from dept where dname='RESEARCH' or dname='ACCOUNTING')
13. SQL>select max(sal) from emp where sal < (select max(sal) from emp);
14. SQL> select max(sal) from emp where sal < (select max(sal) from emp where sal < (select max(sal) from emp where sal < (select max(sal) from emp)))
2. SQL> select empno, ename from emp where deptno in (select deptno from dept where loc in ('NEW YORK','CHICAGO'));
3. SQL> select dname from dept where deptno in ( select deptno from emp where job ='ANALYST');
4. SQL> select empno, ename, mgr from emp where mgr = (select empno from emp where ename='JONES');
5. SQL> select empno, ename, mgr from emp where mgr = (select mgr from emp where ename='JONES')
6. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','ACCOUNTING'))
7. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','RESEARCH')) and empno in (select mgr from emp)
8. SQL> select empno, ename from emp where empno not in ( select mgr from emp where mgr is not null)
9. select empno, ename from emp where empno in (select mgr from emp group by mgr
having count(*) >= 2)
10. SQL> select dname from dept where deptno in (select deptno from emp group by deptno having count(*) >=5)
11. SQL> select deptno, job, count(*) from emp where job = 'SALESMAN' group by deptno, job having count(*) >=3
12. SQL> select empno, ename, deptno from emp where empno in (select mgr from emp group by mgr
having count(*) >= 2) and deptno in (select deptno from dept where dname='RESEARCH' or dname='ACCOUNTING')
13. SQL>select max(sal) from emp where sal < (select max(sal) from emp);
14. SQL> select max(sal) from emp where sal < (select max(sal) from emp where sal < (select max(sal) from emp where sal < (select max(sal) from emp)))
Comments
select dname from deptwhere deptno in (select deptno from emp where job='SALESMAN' group by deptno,job having count(*)>=3);
select * from emp where deptno in ( select deptno from dept where dname in
('SALES','ACCOUNTING') AND JOB = 'MANAGER');
I HAV DONE MY GRADUATION, I HAV LEARNT ORACLE SQL AND PL/SQL .I NEED SOME QUESTIONS AND ANWERS .I HOPE SO WITH UR HELP I CAN CRACK THE INTERVIEW.
ANS:
#####
select * from emp e where sal>(select avg(sal) from emp d where d.deptno=e.deptno);
OR
SELECT empno, ename, deptno
FROM emp e
WHERE sal > (SELECT AVG(sal)
FROM emp e1
WHERE e1.deptno = e.deptno
GROUP BY deptno)
......could tell me sir easy short cut query.....