Notes

**EXERCISE 7

SELECT DEPTNO, DNAME, LOC
FROM DEPT;


**EXERCISE 8

SELECT * FROM EMP;


**EXERCISE 9

SELECT JOB FROM EMP;


**EXERCISE 10

SELECT * FROM EMP WHERE EMPNO = 7369;



**EXERCISE 11

SELECT ENAME, JOB FROM EMP WHERE DEPTNO = 20;


**EXERCISE 12

SELECT ENAME ||' EMPLOYEE JOB TITLE IS '||JOB FROM EMP WHERE DEPTNO = 20;

**EXERCISE 13


**EXERCISE 14

SELECT ENAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND LOC='DALLAS';


**EXERCISE 15

SELECT ENAME ||' EMPLOYEE JOB TITLE IS '||JOB FROM EMP WHERE DEPTNO = 20 AND MGR=7839;


**EXERCISE 16

SELECT COUNT(*) FROM EMP WHERE DEPTNO=20;


**EXERCISE 17

SELECT DISTINCT(JOB) FROM EMP;



**EXERCISE 18

SELECT ENAME, JOB FROM EMP WHERE COMM IS NULL;



**EXERCISE 19

SELECT ENAME ||' EMPLOYEE JOB TITLE IS '||JOB, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;


**EXERCISE 20

SELECT ENAME ||' EMPLOYEE JOB TITLE IS '||JOB, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME='SALES';


**EXERCISE 21

SELECT ENAME ,DEPTNO, SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000;


**EXERCISE 22

SELECT * FROM EMP WHERE DEPTNO IN(10,20) ORDER BY ENAME;


**EXERCISE 23

SELECT ENAME FROM EMP WHERE ENAME LIKE '%TH%' OR ENAME LIKE '%LL%';

**EXERCISE 24

SELECT ENAME, SAL*12 ANNUAL_SAL, COMM FROM EMP WHERE SAL>COMM
ORDER BY SAL DESC, ENAME;


**EXERCISE 25

SELECT ENAME, JOB, SAL, GRADE, DNAME
FROM EMP, SALGRADE, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
AND SAL BETWEEN LOSAL AND HISAL
AND JOB !='CLERK'
ORDER BY SAL DESC;


**EXERCISE 26

SELECT MAX(SAL)-MIN(SAL) FROM EMP;


**EXERCISE 27

SELECT DNAME, DEPTNO
FROM DEPT
WHERE DEPTNO NOT IN
(SELECT DEPTNO FROM EMP);


**EXERCISE 28

SELECT DEPTNO, ENAME, HIREDATE FROM EMP WHERE(HIREDATE, DEPTNO)
IN (SELECT MAX(HIREDATE), DEPTNO
FROM EMP
GROUP BY DEPTNO);


**EXERCISE 29

select ENAME,DEPTNO, SAL
from EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP
WHERE DEPTNO=EMP.DEPTNO);


**EXERCISE 30

SELECT JOB, ENAME, SAL FROM EMP
WHERE SAL IN (SELECT MAX(SAL) FROM EMP
GROUP BY JOB);


**EXERCISE 31

SELECT JOB, SAL FROM EMP WHERE SAL IN(SELECT MIN(SAL) FROM EMP
GROUP BY JOB);


**EXERCISE 32

SELECT ENAME, JOB FROM EMP
WHERE JOB!='SALESMAN'
AND DEPTNO =(SELECT DEPTNO FROM DEPT
WHERE DNAME='SALES');


**EXERCISE 33

SELECT JOB, MAX(SAL) MAXIMUM, MIN(SAL) MINIMUM FROM EMP
GROUP BY JOB;

**EXERCISE 34

SELECT DEPTNO, COUNT(*) FROM EMP
GROUP BY DEPTNO HAVING COUNT(*)>3;


**EXERCISE 35

SELECT MGR, MIN(SAL) FROM EMP
GROUP BY MGR
HAVING MIN(SAL)>1000
ORDER BY MIN(SAL);


**EXERCISE 36
SELECT EMPS, EMPNO, EMPS.ENAME, MGRS.EMPNO MGRNO, MGRS.ENAME MGR_NAME
FROM EMP EMPS, EMP MGRS
WHERE EMPS.MGR=MGRS.EMPNO;


**EXERCISE 37
SELECT EMPS, EMPNO, EMPS.ENAME, MGRS.EMPNO MGRNO, MGRS.ENAME MGR_NAME
FROM EMP EMPS, EMP MGRS
WHERE EMPS.MGR=MGRS.EMPNO(+);



**EXERCISE 38
SELECT D.DEPTNO, DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
AND E.EMPNO IS NULL;


**EXERCISE 39
SELECT


**EXERCISE 40


**EXERCISE 41



**EXERCISE 42


**EXERCISE 43


Having clause is used in combination with the group by clasue to restrict the groups of returned rows to only those whose condition is ture

SYNTAX:-
SELECT EXP1, EXP2,...
AGGREGATE FUNCTION(AGGREGATE_EXP)
FROM TABLE
[WHERE CONDITION]
GROUP BY EXP1, EXP2....
HAVING HAVING_CONDITION;