2).
SQL> SELECT HIREDATE,JOB
2 FROM EMP
3 WHERE DEPTNO IN (SELECT DEPTNO
4 FROM DEPT
5 WHERE DNAME='SALES');
HIREDATE JOB
--------- ---------
20-FEB-81 SALESMAN
22-FEB-81 SALESMAN
28-SEP-81 SALESMAN
01-MAY-81 MANAGER
08-SEP-81 SALESMAN
03-DEC-81 CLERK
6 rows selected.
3).SQL> SELECT LOC,DNAME
2 FROM DEPT
3 WHERE DEPTNO=(SELECT DEPTNO
4 FROM EMP
5 WHERE JOB='PRESIDENT' AND SAL=2000);
no rows selected
4).SQL> SELECT DNAME
2 FROM DEPT
3 WHERE DEPTNO=(SELECT DEPTNO
4 FROM EMP
5 WHERE SAL IN (SELECT MAX(SAL)
6 FROM EMP
7 WHERE SAL<3000));
DNAME
--------------
RESEARCH
5). 1 SELECT DNAME
2 FROM DEPT
3 WHERE DEPTNO IN (SELECT DEPTNO
4 FROM EMP
5 WHERE MGR = (SELECT EMPNO
6 FROM EMP
7* WHERE ENAME='ADAMS'))
SQL> /
o rows selected
6). SQL> SELECT *
2 FROM EMP
3 WHERE EMPNO=(SELECT MAX(EMPNO)
4 FROM EMP);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7). 1 SELECT ENAME
2 FROM EMP
3 WHERE SAL>(SELECT AVG(SAL)
4 FROM EMP
5* WHERE DEPTNO=30)
SQL> /
ENAME
----------
ALLEN
JONES
BLAKE
CLARK
SCOTT
KING
FORD
7 rows selected.
8).
1 SELECT COUNT(*)
2 FROM DEPT
3 WHERE DNAME='RESEARCH' AND DEPTNO IN (SELECT DEPTNO
4 FROM EMP
5 WHERE SAL<ANY(SELECT SAL
6 FROM EMP
7* WHERE DEPTNO=30))
SQL> /
COUNT(*)
----------
1
9). SQL> SELECT DNAME
2 FROM DEPT
3 WHERE DEPTNO IN (SELECT DEPTNO
4 FROM EMP
5 WHERE JOB='CLERK');
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
10). SQL> SELECT DNAME
2 FROM DEPT
3 WHERE DEPTNO IN (SELECT DEPTNO
4 FROM EMP
5 GROUP BY DEPTNO
6 HAVING COUNT(*)>=2);
DNAME
--------------
SALES
RESEARCH
ACCOUNTING
11).SQL> SELECT ENAME
2 FROM EMP
3 WHERE HIREDATE> (SELECT HIREDATE
4 FROM EMP
5 WHERE ENAME='BLAKE');
ENAME
----------
MARTIN
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
9 rows selected.
12). SQL> SELECT
2
SQL>
SQL> SELECT DNAME
2 FROM DEPT
3 WHERE DEPTNO IN (SELECT DEPTNO
4 FROM EMP
5 GROUP BY DEPTNO
6 HAVING COUNT(*)>=3 AND COUNT(*)<=5);
DNAME
--------------
RESEARCH
ACCOUNTING
13). SQL> SELECT LOC
2 FROM DEPT
3 WHERE DEPTNO IN (SELECT DEPTNO
4 FROM EMP
5 WHERE EMPNO IN (SELECT MGR
6 FROM EMP
7 WHERE SAL>2000));
LOC
-------------
DALLAS
NEW YORK
14). SQL> SELECT ENAME,SAL
2 FROM EMP
3 WHERE DEPTNO IN (SELECT DEPTNO
4 FROM DEPT
5 WHERE DNAME LIKE '%O%');
ENAME SAL
---------- ----------
CLARK 2450
KING 5000
MILLER 1300
15). SQL> SELECT ENAME,SAL
2 FROM EMP
3 WHERE SAL> ANY(SELECT SAL
4 FROM EMP
5 WHERE JOB='ANALYST');
ENAME SAL
---------- ----------
KING 5000
