231128복습(오라클 7,8교시)

--1128복습(오라클 7,8교시 예제 복습)

SELECT SUM(SAL) FROM EMP;

SELECT ENAME, SUM(SAL) FROM EMP GROUP BY ENAME;

SELECT SUM(COMM) FROM EMP;

SELECT SUM(DISTINCT SAL), SUM(ALL SAL), SUM(SAL) FROM EMP;

SELECT COUNT(*) FROM EMP;

SELECT COUNT(*) FROM EMP;

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

SELECT COUNT(DISTINCT SAL),COUNT(ALL SAL), COUNT(SAL) FROM EMP;

SELECT COUNT(COMM) FROM EMP;

SELECT COUNT(COMM) FROM EMP WHERE COMM IS NOT NULL;

SELECT MAX(SAL) FROM EMP WHERE DEPTNO=10;

SELECT MIN(SAL) FROM EMP WHERE DEPTNO=10;

SELECT MAX(HIREDATE) FROM EMP WHERE DEPTNO=20;
--입사 연도가 제일 큰 사원이 최근 

SELECT MIN(HIREDATE) FROM EMP WHERE DEPTNO=20;
--입사 연도가 제일 작은 사원이 입사일이 제일 오래된 사람

SELECT AVG(SAL) FROM EMP WHERE DEPTNO=30;

SELECT AVG(DISTINCT SAL) FROM EMP WHERE DEPTNO=30;

SELECT AVG(COMM) FROM EMP WHERE DEPTNO=30;

SELECT AVG(SAL), DEPTNO FROM EMP GROUP BY DEPTNO;

SELECT AVG(SAL), DEPTNO, JOB FROM EMP 
GROUP BY DEPTNO, JOB;

SELECT AVG(COMM), DEPTNO FROM EMP GROUP BY DEPTNO;

SELECT DEPTNO, JOB, SAL FROM EMP WHERE SAL<=3000
ORDER BY DEPTNO, JOB;

SELECT DEPTNO,JOB,SAL,AVG(SAL) FROM EMP
GROUP BY DEPTNO,JOB,SAL HAVING AVG(SAL)>=500;

SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP GROUP BY DEPTNO, JOB;

SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);

SELECT DEPTNO, JOB, COUNT(*),MAX(SAL),SUM(SAL),AVG(SAL) FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

SELECT DEPTNO,JOB,COUNT(*) FROM EMP
GROUP BY DEPTNO, ROLLUP(JOB);

SELECT DEPTNO, TRUNC(AVG(SAL),0),MAX(SAL),MIN(SAL),COUNT(*)
FROM EMP GROUP BY DEPTNO;

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

SELECT TO_CHAR(HIREDATE,'YYYY') AS HIRE_YEAR, DEPTNO, COUNT(*)
FROM EMP GROUP BY TO_CHAR(HIREDATE,'YYYY'), DEPTNO;

SELECT NVL2(COMM,'O','X') AS EXIST_COMM ,COUNT(*) 
FROM EMP
GROUP BY NVL2(COMM,'O','X') ;

SELECT  DEPTNO , TO_CHAR(HIREDATE,'YYYY') AS HIRE_YEAR, COUNT(*),MAX(SAL),SUM(SAL),AVG(SAL)
FROM EMP GROUP BY CUBE(DEPTNO, TO_CHAR(HIREDATE,'YYYY')) ORDER BY DEPTNO;

SELECT * FROM EMP, DEPT ORDER BY EMPNO;

SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO ORDER BY EMPNO;

SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO ORDER BY EMPNO;

SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO;

SELECT E.EMPNO, E.ENAME, DEPTNO, D.DNAME, D.LOC FROM EMP E JOIN DEPT D USING(DEPTNO);

SELECT E. EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND SAL>=3000;

SELECT * FROM EMP E, DEPT D 
WHERE E.DEPTNO=D.DEPTNO
AND E.SAL <=2500 AND E.DEPTNO<=9999;

SELECT * FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL; 

SELECT SAL FROM EMP WHERE ENAME='JONES';

SELECT * FROM EMP WHERE SAL>2975;

SELECT * FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='JONES');

SELECT COMM FROM EMP WHERE ENAME='ALLEN';

SELECT * FROM EMP WHERE COMM>(SELECT COMM FROM EMP WHERE ENAME='ALLEN'); 

SELECT * FROM EMP WHERE HIREDATE < (SELECT HIREDATE FROM EMP WHERE ENAME='SCOTT');

SELECT TRUNC(AVG(SAL),0) FROM EMP ;

SELECT * FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO=D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL
AND E.SAL<=(SELECT AVG(SAL) FROM EMP)AND E.DEPTNO=20;

SELECT*FROM EMP WHERE DEPTNO IN(20,30);

SELECT DEPTNO, MAX(SAL)FROM EMP GROUP BY DEPTNO;

SELECT * FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
--GROUP BY DEPTNO와 MAX(SAL)를 함께 사용하는 부분에 주의가 필요,
--만약 같은 부서에서 여러 사원이 최대 급여를 받는 경우가 있을 수 있음 등호대신 IN을 쓰자

SELECT * FROM EMP WHERE (DEPTNO,SAL) IN(SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO);

SELECT * FROM EMP WHERE SAL< ANY(SELECT SAL FROM EMP WHERE DEPTNO=30)
ORDER BY SAL, EMPNO;

SELECT * FROM EMP WHERE SAL>ANY (SELECT SAL FROM EMP WHERE DEPTNO=30);

SELECT * FROM EMP WHERE SAL<ALL (SELECT SAL FROM EMP WHERE DEPTNO=30);

SELECT * FROM EMP WHERE SAL>ALL (SELECT SAL FROM EMP WHERE DEPTNO=30);

SELECT * FROM EMP WHERE EXISTS(SELECT DNAME FROM DEPT WHERE=10);

SELECT * FROM EMP 
WHERE HIREDATE <ALL(SELECT HIREDATE FROM EMP WHERE DEPTNO=10);

SELECT * FROM EMP 
WHERE(DEPTNO, SAL) 
IN (SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO);

SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
FROM (SELECT * FROM EMP WHERE DEPTNO = 10) E10,
(SELECT * FROM DEPT) D
WHERE E10.DEPTNO=D.DEPTNO;

SELECT JOB FROM EMP WHERE ENAME='ALLEN';

SELECT * FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO 
AND E.JOB IN(SELECT JOB FROM EMP WHERE JOB LIKE 'SALESMAN');

SELECT AVG(SAL) FROM EMP;

SELECT *FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO=D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL
AND SAL>(SELECT AVG(SAL)FROM EMP);