1) SELECT *
FROM DEPT
WHERE DNAME LIKE '%S';
2) SELECT DNAME
FROM DEPT
WHERE DNAME IN('OPERATIONS','ACCOUNTING');
3) SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL, E.COMM, D.LOC
FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE D.LOC = 'CHICAGO'
AND E.COMM IS NULL;
4) SELECT E.EMPNO, E.ENAME, E.JOB, D.DNAME
FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE D.DNAME = 'RESEARCH'
AND E.JOB = 'MANAGER';
5) SELECT D.DNAME, E.ENAME, E.COMM
FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE COMM IS NOT NULL;
6) SELECT E.DEPTNO,D.DNAME,E.JOB
FROM EMP E
JOIN DEPT D ON E.DEPTNO =D.DEPTNO
WHERE DNAME = 'SALES'
AND JOB = 'MANAGER';
7) SELECT E.HIREDATE, E.JOB, D.DNAME
FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE D.DNAME = 'SALES';
8) SELECT D.LOC, D.DNAME, E.ENAME, E.JOB
FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE JOB = 'PRESIDENT';
9) SELECT DISTINCT D.DNAME, E.ENAME
FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE E.JOB = 'CLERK';
10) SELECT D.DNAME, E.ENAME
FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE DNAME LIKE '%L%L%';
11) SELECT ENAME
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO FROM DEPT WHERE LOC = 'CHICAGO' );
12) SELECT D.DNAME
FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE E.JOB = 'SALESMAN';
13) SELECT D.LOC, D.DEPTNO, E.HIREDATE
FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE HIREDATE BETWEEN '1-JAN-1981' AND '31-DEC-1981';
14) SELECT LOC
FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE HIREDATE BETWEEN '1-JAN-1981' AND '31-
DEC-1981');
15) SELECT COUNT(*)
FROM EMP
WHERE JOB = 'SALESMAN'
AND DEPTNO IN( SELECT DEPTNO FROM DEPT WHERE LOC IN('NEW YORK','CHICAGO'));
16) SELECT DNAME
FROM DEPT
WHERE DEPTNO IN(
SELECT DEPTNO FROM EMP
WHERE HIREDATE BETWEEN '1-JAN-1981' AND '31-DEC-1982'
AND SAL > 1800 );
17)SELECT LOC
FROM DEPT
WHERE DEPTNO IN(
SELECT DEPTNO FROM EMP
WHERE COMM IS NOT NULL
AND COMM != 0);
18) SELECT LOC
FROM DEPT
WHERE DEPTNO IN(
SELECT DEPTNO FROM EMP
WHERE COMM IS NOT NULL);
19)SELECT *
FROM EMP
WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'SMITH')
AND DEPTNO IN(SELECT DEPTNO FROM EMP WHERE ENAME = 'JONES')
AND SAL > (SELECT SAL FROM EMP WHERE ENAME = 'TURNER');
20) SELECT *
FROM EMP
WHERE DEPTNO IN( SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS');
21) SELECT *
FROM EMP
WHERE DEPTNO IN( SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS');
22)SELECT *
FROM DEPT
WHERE LOC = 'NEW YORK';
23) SELECT JOB,ENAME
FROM EMP
WHERE JOB IN('CLERK','ANALYST')
AND DEPTNO IN( SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS');
24) SELECT *
FROM EMP
WHERE HIREDATE > '31-JAN-1956'
AND DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'RESEARCH');
25) 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
WHERE SAL < ( SELECT MAX(SAL) FROM EMP))));
26) SELECT MIN(SAL)
FROM EMP
WHERE SAL > ( SELECT MIN(SAL) FROM EMP
WHERE SAL > ( SELECT MIN(SAL) FROM EMP
WHERE SAL > ( SELECT MIN(SAL) FROM EMP
WHERE SAL > ( SELECT MIN(SAL) FROM EMP))));
27)
