오라클[sql] 연습문제 / 조인문제
연습문제
▶ * SELECT 통해서 WHERE 조건으로 1차적인 데이터를 추출
▶ 1. WHERE 통해서 데이터 추출
▶ 2. GROUP BY 통해서 그룹화
▶ 3. HAVING 통해서 그룹화된 데이터를 추출
▶ 4. ORDER BY 통해서 정렬
▶ ROWNUM / ORDER BY
▶ ROWNUM 출력될 대상의 순서를 작성, ORDER BY가 후작업, ORDER BY 랑은 무관
▶ SELECT 단순조회
각자 시스템이 단순 조회를 해온것, ORDER BY 하지 않는 이상 데이터 순서는 항상 조금씪 다르게 보일수 있음
▶ 업무별, 부서별 급여합계, 인원수 (10번 부서를 제외, 업무 SALESMAN MANAGER만 출력)
SELECT job, deptno, sum(sal), count(*)
FROM emp
WHERE deptno <> 10
AND job IN('SALESMAN', 'MANAGER')
GROUP BY job, deptno;
▶ 업무별 평균급여, 최대급여를 출력하되, 평균급여가 2000이상인것만 출력, 평균급여가 높은순으로 정렬
SELECT job, ROUND(avg(sal), 2) avg, max(sal) max
FROM emp
GROUP BY job
HAVING avg(sal) >= 2000
ORDER BY avg(sal) desc;
▶ 5개씩 급여합계와 인원수를 출력
>> ROWNUM : 행의 번호를 가지고 있는 가상 컬럼
SELECT ROWNUM, e. * FROM emp e;
SELECT ROWNUM, e. * FROM emp e ORDER BY sal desc;
SELECT CEIL(ROWNUM/5) as grp1, sum(sal) as sum, count(*) as cnt
FROM emp e
GROUP BY CEIL(ROWNUM/5);
▶ 입사년도를 기준으로 그룹, 입사년도에 따른 입사자인원
SELECT TO_CHAR(hiredate, 'YYYY'), count(*), SUBSTR(hiredate, 1, 2)
FROM emp
GROUP BY TO_CHAR(hiredate, 'YYYY'), SUBSTR(hiredate, 1, 2);
조인 연습문제
▶ SALESMAN 사원만 사원번호, 이름, 급여, 부서명, 근무지를 출력
SELECT e.empno, e.ename, e.sal, e.deptno, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;
▶ 보너스(comm)를 받는 사원에 대해 이름, 부서명, 위치를 출력
SELECT e.ename, d.dname, d.loc, e.comm
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno
WHERE comm IS NOT NULL
AND comm <> 0;
▶ 부서별 부서명과 급여 합계를 출력 >> 부서명 dept 테이블 dname / 급여 emp 테이블 sal
-- emp : 10 20 30 부서데이터+월급만 존재
-- dept : 10 20 30 40 부서이름 데이터가 존재
-- 40 부서는 월급데이터가 없어 > 어떻게 처리 할까요?
풀이1)
SELECT d.deptno, d.dname, sum(e.sal)
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno
GROUP BY d.deptno, d.dname
ORDER BY d.deptno;
풀이2)
SELECT d.deptno, d.dname, sum(e.sal)
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno
GROUP BY d.deptno, d.dname
ORDER BY d.deptno;
▶ SMITH와 같은 부서에서 근무하는 사람들 출력
-- JOIN을 이용해서 emp를 이용해서 SMITH 부서 = emp 같은 부서사람들 선택
-- self 조인 : 자기자신을 조인
SELECT e1.ename, e1.deptno
FROM emp e1, emp e2
WHERE e1.deptno = e2.deptno
AND e2.ename = 'SMITH';
SELECT ename
FROM emp
WHERE deptno = (SELECT deptno FROM emp WHERE ename='SMITH');
▶ JONES보다 급여를 많이 받는 사람들 출력
SELECT *
FROM emp e1, emp e2
WHERE e2.ename = 'JONES'
AND e1.sal > e2.sal;
SELECT ename
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');