-- 9) emp 테이블에서 사원 수가 5명이 넘는 부서의 부서 번호와 사원 수를 구하시오.
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*)>5;
-- 10) emp 테이블에서 부서별 급여 평균을 구할 때 소수점 이하 셋째 자리에서 반올림해서 둘째 자리까지 구하고 부서 번호, 급여 평균을 출력하시오.
SELECT deptno, ROUND(AVG(sal), 2) FROM emp GROUP BY deptno;
-- 11) emp 테이블에서 분기별로 입사한 사원 수를 구하는데 2분기에 입사한 사원 수만 구하시오.
SELECT TO_CHAR(hiredate, 'Q') AS 분기, COUNT(*) AS "사원 수" FROM emp
WHERE TO_CHAR(hiredate, 'Q')=2 GROUP BY TO_CHAR(hiredate, 'Q'); -- GROUP BY절에는 별칭 사용 불가
-- 인자를 오른쪽에서 왼쪽순으로 묶어 집계를 산출하는 함수
-- ROLLUP(a, b, c) : (a, b, c) 소계, (a, b) 소계, a 소계, 총계
-- 동일 부서에 대해 직무별 평균 급여 및 사원 수
-- 부서별 평균 급여와 사원 수
-- 전체 사원의 평균 급여와 사원 수
SELECT deptno, job, ROUND(AVG(sal), 1) avg_sal, COUNT(*)
FROM emp GROUP BY ROLLUP(deptno, job);
-- 인자를 묶을 수 있는 모든 경우의 수에 대해 집계를 산출하는 함수
-- CUBE(a, b, c) : (a, b, c) 소계, (a, b) 소계, (a, c) 소계, (b, c) 소계, a 소계, b 소계, c 소계, 총계
-- 동일 부서에 대해 직무별 평균 급여
-- 부서별 평균 급여
-- 직무별 평균 급여(=ROLLUP에서 구하지 않는 소계)
-- 전체 사원의 평균 급여
SELECT deptno, job, ROUND(AVG(sal), 1) AS avg_sal, COUNT(*) AS cnt_emp
FROM emp GROUP BY CUBE(deptno, job);
-- RANK(조건값) WITHIN GROUP (ORDER BY 조건값이 포함된 컬럼명 [ASC|DESC])
-- 특정 데이터의 순위 보기
SELECT RANK('SMITH') WITHIN GROUP (ORDER BY ename) AS rank FROM emp;
SELECT ename FROM emp ORDER BY ename; -- SMITH는 10번째로 조회됨
-- RANK() OVER(ORDER BY 기준 컬럼명 [ASC|DESC])
-- 전체 순위 보기
SELECT empno, ename, sal,
RANK() OVER(ORDER BY sal) AS rank_asc,
RANK() OVER(ORDER BY sal DESC) AS rank_desc
FROM emp;
SELECT * FROM emp, dept;
-- 두 테이블에 공통적으로 존재하는 컬럼의 값이 일치하는 행끼리 연결하는 기법
-- WHERE절에서 Equality Condition(=)을 사용
SELECT emp.ename, dept.deptno, dept.dname FROM emp, dept
WHERE emp.deptno=dept.deptno;
-- 테이블에 별칭 부여하기
SELECT e.ename, d.deptno, d.dname FROM emp e, dept d -- 가능한 짧은 별칭 사용
WHERE e.deptno=d.deptno;
-- 추가 조건 명시하기
SELECT e.ename, d.dname FROM emp e, dept d
WHERE e.deptno=d.deptno -- JOIN 조건
AND e.ename='ALLEN'; -- 추가 조건
SELECT e.ename, e.sal, d.dname FROM emp e, dept d
WHERE e.deptno=d.deptno -- JOIN 조건
AND e.sal BETWEEN 3000 AND 4000; -- 추가 조건
-- 한 테이블의 어떤 컬럼도 다른 테이블의 컬럼에 직접적으로 일치하지 않을 때, 값의 범위를 비교하여 연결하는 기법
-- WHERE절에서 동등(=) 이외의 연산자(BETWEEN AND, IS NULL, IN 등) 사용
-- 사원 이름, 급여, 급여 등급 구하기
SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;
-- 1) 모든 사원의 이름, 부서 번호, 부서 이름을 표시하시오.
SELECT e.ename, d.deptno, d.dname FROM emp e, dept d
WHERE e.deptno=d.deptno;
-- 2) 업무가 MANAGER인 사원의 정보를 이름, 업무, 부서명, 근무지 순으로 출력하시오.
SELECT e.ename, e.job, d.dname, d.loc FROM emp e, dept d
WHERE e.deptno=d.deptno AND e.job='MANAGER';
-- 3) 커미션을 받고 급여가 1,600 이상인 사원의 이름, 급여, 부서명, 근무지를 출력하시오.
SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, dept d
WHERE e.deptno=d.deptno AND e.comm IS NOT NULL AND e.sal>=1600;
-- 4) 근무지가 CHICAGO인 모든 사원의 이름, 업무, 부서 번호 및 부서 이름을 표시하시오.
SELECT e.ename, e.job, d.deptno, d.dname FROM emp e, dept d
WHERE e.deptno=d.deptno AND d.loc='CHICAGO';
-- 5) 근무지별로 근무하는 사원의 수가 5명 이하인 경우, 인원이 적은 도시 순으로 정렬하시오.
SELECT d.loc "근무지", COUNT(e.empno) AS "사원 수" FROM emp e, dept d
WHERE e.deptno=d.deptno GROUP BY d.loc HAVING COUNT(e.empno)<=5 ORDER BY "사원 수";
-- 자기 자신과 연결하는 기법
-- 같은 테이블에 두 개의 별칭을 사용
-- 사원 이름과 해당 사원의 관리자 이름 구하기(관리자가 없는 사원 제외)
SELECT a.ename "사원 이름", m.ename "관리자 이름"
FROM emp a, emp m
WHERE a.mgr=m.empno;
-- 두 테이블을 연결할 때, 두 테이블 중 한 테이블에만 있는 값(=JOIN 조건을 만족하지 못하는 행)까지 출력하는 기법
-- 누락된 행이 없는 테이블의 공통 컬럼에 (+) 기호 명시
SELECT DISTINCT(e.deptno), d.deptno FROM emp e, dept d
WHERE e.deptno(+)=d.deptno; -- dept 테이블의 deptno=40이 누락됨
-- 근무지별로 근무하는 사원의 수가 5명 이하인 경우, 인원이 적은 도시 순으로 정렬(근무 인원이 0명인 곳도 표시)
SELECT d.loc "근무지", COUNT(e.empno) AS "사원 수" FROM emp e, dept d -- COUNT(*)은 NULL까지 포함하므로 1을 반환
WHERE e.deptno(+)=d.deptno GROUP BY d.loc HAVING COUNT(e.empno)<=5 ORDER BY "사원 수"; -- dept 테이블에서 loc=BOSTON인 행이 누락됨
-- 사원 이름과 해당 사원의 관리자 이름 구하기(관리자가 없는 사원도 표시)
SELECT a.ename 사원, m.ename 관리자 FROM emp a, emp m
WHERE a.mgr=m.empno(+); -- a 테이블에서 mgr이 NULL인 행이 누락됨
-- 1) 관리자보다 먼저 입사한 모든 사원의 이름 및 입사일을 관리자의 이름 및 입사일과 함께 표시하시오.
-- 열 별칭은 employee, emp_hired, manager, mgr_hired로 지정하시오. (관리자가 없는 사원 미출력)
SELECT e.ename AS employee, e.hiredate AS emp_hired, m.ename AS manager, m.hiredate AS mgr_hired
FROM emp e, emp m WHERE e.mgr=m.empno AND e.hiredate<m.hiredate;
-- 2) 10번 부서에서 근무하는 사원들의 부서 번호, 부서 이름, 사원 이름, 월급, 급여 등급을 출력하시오.
SELECT d.deptno, d.dname, e.ename, e.sal, s.grade FROM emp e, dept d, salgrade s
WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal AND d.deptno=10;
-- INNER JOIN 다음에 연결할 테이블명을 명시하고, ON절에 JOIN 조건을 명시
-- INNER 생략 가능
SELECT e.ename, d.deptno FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
-- 추가 조건 명시하기
SELECT e.ename, d.dname FROM emp e JOIN dept d
ON e.deptno=d.deptno -- JOIN 조건
WHERE e.ename='KING'; -- 추가 조건을 WHERE절에 명시
SELECT e.ename, d.dname FROM emp e JOIN dept d
ON e.deptno=d.deptno -- JOIN 조건
AND e.ename='KING'; -- 추가 조건을 ON절에 AND로 명시
-- JOIN 조건에 사용된 컬럼명이 같으면 USING절 사용 가능
SELECT e.ename, deptno, d.dname FROM emp e JOIN dept d USING(deptno); -- USING절에 지정된 컬럼은 테이블명 생략한 단순 컬럼명만 명시해야 함
SELECT ename, deptno, dname FROM emp JOIN dept USING(deptno); -- ename과 dname은 두 테이블 중 한 테이블에만 있고, deptno는 USING절로 합쳤기 때문에 테이블명 명시할 필요 없음
-- Self Join의 경우 JOIN 조건에 이름이 다른 컬럼을 사용하기 때문에 USING절 사용 불가
SELECT e.ename "사원 이름", m.ename "관리자 이름" FROM emp e JOIN emp m ON e.mgr=m.empno;
-- 1) 모든 사원의 이름, 부서 번호, 부서 이름을 표시하시오.
SELECT ename, deptno, dname FROM emp JOIN dept USING(deptno);
-- 2) 업무가 SALESMAN인 사원의 정보를 이름, 업무, 부서명, 근무지 순으로 출력하시오.
SELECT ename, job, dname, loc FROM emp JOIN dept USING(deptno) WHERE job='SALESMAN';
-- 3) 커미션을 받고 급여가 1,600 이상인 사원의 이름, 급여, 부서명, 근무지를 출력하시오.
SELECT ename, sal, dname, loc FROM emp JOIN dept ON emp.deptno=dept.deptno
WHERE comm IS NOT NULL AND sal>=1600;
-- OUTER JOIN 다음에 연결할 테이블명을 명시하고, ON절에 JOIN 조건을 명시
-- OUTER 생략 가능
-- 누락된 행이 JOIN 키워드 기준 왼쪽 테이블에 있으면 LEFT, 오른쪽 테이블에 있으면 RIGHT, 양쪽 테이블 모두 있으면 FULL 명시
SELECT DISTINCT(e.deptno), d.deptno FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno; -- dept 테이블에서 deptno=40인 행이 누락됨
-- OUTER JOIN에서는 추가 조건 명시 위치에 따라 출력 결과 상이
-- 추가 조건을 WHERE절에 명시하는 경우, 누락된 행이 추가 조건을 만족하지 않으면 출력되지 않음
SELECT e.ename, d.dname FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno -- JOIN 조건
WHERE e.ename='KING'; -- 추가 조건을 WHERE절에 명시하면, ename=KING이고 dname=ACCOUNTING인 행만 출력됨
-- 추가 조건을 ON절에 AND로 명시하는 경우, 누락된 행이 추가 조건을 만족하지 않아도 출력됨
SELECT e.ename, d.dname FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno -- JOIN 조건
AND e.ename='KING'; -- 추가 조건을 ON절에 AND로 명시하면, WHERE절 결과에 추가로 ename=NULL이고 dname=RESEARCH, SALES, OPERATIONS인 행들까지 출력됨
-- 사원 이름과 해당 사원의 관리자 이름 구하기(관리자가 없는 사원도 표시)
SELECT e.ename "사원 이름", m.ename "관리자 이름" FROM emp e LEFT JOIN emp m ON e.mgr=m.empno;
-- 1) 근무지가 DALLAS인 모든 사원의 이름, 업무, 부서 번호 및 부서 이름을 표시하시오.
SELECT ename, job, deptno, dname
FROM emp JOIN dept USING(deptno) WHERE loc='DALLAS';
-- 2) 근무지별로 근무하는 사원의 수가 5명 이하인 경우, 인원이 적은 도시순으로 정렬하시오.
-- (근무 인원이 0명인 곳도 표시)
SELECT COUNT(empno), loc FROM emp RIGHT OUTER JOIN dept USING(deptno)
GROUP BY loc HAVING COUNT(empno)<=5 ORDER BY COUNT(empno);
-- 3) 관리자보다 먼저 입사한 모든 사원의 이름 및 입사일을 관리자의 이름 및 입사일과 함께 표시하시오.
-- 열 별칭은 사원 이름, 사원 입사일, 관리자 이름, 관리자 입사일로 지정하시오.
SELECT e.ename "사원 이름", e.hiredate "사원 입사일", m.ename "관리자 이름", m.hiredate "관리자 입사일"
FROM emp e JOIN emp m ON e.mgr=m.empno WHERE e.hiredate<m.hiredate;
-- 4) 10번 부서에서 근무하는 사원들의 부서 번호, 부서 이름, 사원 이름, 월급, 급여 등급을 출력하시오.
SELECT deptno, dname, ename, sal, grade
FROM emp JOIN dept USING(deptno)
JOIN salgrade ON sal BETWEEN losal AND hisal WHERE deptno=10;