SQL 쿼리문 연습(해답)

(조인)

1. 직업이 ANALYST인 사원의 이름, 근무지, 직업 출력.

SELECT e.ename, d.loc, e.job
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND job = 'ANALYST';

* 마지막에 AND와 함께 조건문 작성 잊지 말아야 한다.

 

(비동등 조인)

2. 사원 테이블과 급여 등급 테이블을 조인하여 이름, 월급, 급여 등급 출력.

SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

* 조건문에 등급의 최소 범위인 losal과 최대 범위인 hisal안에 들어있는가에 따라 등급이 달라지므로 BETWEEN을 사용하며 losal과 hisal을 AND로 묶어줘야 한다.

 

(외부 조인)

3. 사원 테이블과 부서 테이블을 조인하여 이름과 부서 위치를 출력. 단, 근무지에 근무하는 사원이 없어도 부서의 모든 위치를 함께 출력.

SELECT e.ename, d.loc
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;

* 외부 조인을 할 때 오라클 조인 같은 경우 왼쪽 외부 조인, 오른쪽 외부 조인은 가능하나 완전 외부 조인은 할 수 없다.

* 오라클에서 사용하는 외부 조인의 기호는 (+)이다.

 

(자기 자신과 조인)

4. 사원 테이블을 자기 자신의 테이블과 조인하여 이름, 직업, 해당 사원의 관리자 이름, 관리자 직업을 출력.

SELECT e.ename, e.job, m.ename, m.job
FROM emp e, emp m
WHERE e.mgr = m.empno;

* 조인을 시키는 조건이 자신의 empno가 mgr에 있다면 관리자 가 되기 때문에 조건을 e.mgr = m.empno로 한다.

 

(INTERSECT)

5. 사원 테이블에서 이름과 부서 번호 10번, 20번을 출력하는 쿼리의 결과에서 부서 번호 20번, 30번을 출력하는 쿼리의 결과의 교집합을 출력.

SELECT ename, deptno
FROM emp
WHERE deptno in (10,20)
INTERSECT
SELECT ename, deptno
FROM emp
WHERE deptno in (20,30);

* INTERSECT는 '교차'라는 뜻을 가지고 있다 단어만 알고 있다면 충분히 사용할 수 있다.

 

(MINUS)

6. 사원 테이블에서 이름과 부서 번호 10번, 20번을 출력하는 쿼리의 결과에서 부서 번호 20번, 30번을 출력하는 쿼리의 결과의 차이를 출력.

SELECT ename, deptno
FROM emp
WHERE deptno in (10,20)
MINUS
SELECT ename, deptno
FROM emp
WHERE deptno in (20,30);

* MINUS는 항상 위에 있는 쿼리문을 기준으로 하기 때문에 위치에 신경 써야 한다.

 

(WHERE절 서브쿼리문)

7. 'JONES'보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력.

SELECT ename, sal
FROM emp
WHERE sal > 
(SELECT sal FROM emp WHERE ename = 'JONES');

* 서브쿼리문은 ( ) 를 이용하여 그 안에 쿼리문을 작성하는 것이다.

* 값과 비교할 값을 구하기 위한 쿼리문을 작성하면 된다.

 

8. 'SCOTT'의 월급과 같은 월급을 받는 사원의 이름과 월급 출력.

SELECT ename, sal
FROM emp
WHERE sal = 
(SELECT sal FROM emp WHERE ename = 'SCOTT')
AND ename != 'SCOTT';

* 7번 문제와 같은 방식으로 ( ) 안에 쿼리문을 작성하면 되고 'SCOTT'의 월급과 같은 월급을 받는 사원을 찾고 싶은 것이기에 AND를 사용해 'SCOTT'을 제외한 사람들만 출력되도록 작성한다.

 

9. 직업이 'SALESMAN'인 사원들과 같은 월급을 받는 사원들의 이름과 월급을 출력.

SELECT ename, sal
FROM emp
WHERE sal IN (SELECT sal FROM emp WHERE job = 'SALESMAN');

* 조건에서 비교하는 값이 1개 라면 부등호를 사용하여 나타내고 값이 여러 개인 경우 IN을 사용하여 비교한다.

 

10. 관리자가 아닌 사원들의 이름, 월급, 직업을 출력.

SELECT ename, sal, job
FROM emp
WHERE empno NOT IN (SELECT mgr FROM emp WHERE mgr IS NOT NULL);

* 먼저 관리자가 아니라면 empno에 mgr이 들어가선 안된다. 그렇기 때문에 empno NOT IN을 이용해 empno에 들어가선 안된다 라는 조건을 달고 ( ) 를 사용해 mgr을 찾아낸다. mgr에는 값이 없는 사람도 있기 때문에 mgr IS NOT NULL로 mgr이 비어있지 않은 값들을 가져와 비교한다.

 

11. 부서 테이블에 있는 부서 번호 중에서 사원 테이블에도 존재하는 부서 번호의 부서 번호, 부서명, 부서 위치를 출력.

SELECT deptno, dname, loc
FROM dept
WHERE deptno IN (SELECT deptno FROM emp);

* 부서 번호와 부서명, 부서 위치가 들어있는 dept를 테이블로 사용하고 비교하는 사원 테이블을 조건으로 사용한다.

* dept테이블에 있는 deptno를 emp테이블에 있는 deptno와 비교하여 같은 게 존재한다면 출력한다.

 

12. 직업과 직업별 토탈 월급을 출력하는데, 직업이 'SALESMAN'인 사원들의 토탈 월급보다 더 큰 값들만 출력.

SELECT job, SUM(sal)
FROM emp
GROUP BY job
HAVING SUM(sal) > (SELECT SUM(sal) FROM emp WHERE job = 'SALESMAN');

* 직업별 토탈 월급을 구하기 위해 SUM(sal)을 이용하고 이를 정리하기 위해 GROUP BY job을 해준다.

* 그 후에 직업이 'SALESMAN'인 사람들의 토탈 월급보다 더 큰 값이라는 조건을 적용하기 위해 HAVING절에 조건을 넣어준다.

 

(FROM절 서브쿼리문)

13. 이름과 월급의 순위를 출력하는데 순위가 1위인 사원만 출력.

SELECT v.ename, v.sal, v.rank
FROM (SELECT ename, sal, rank() over (ORDER BY sal DESC) rank FROM emp) v
WHERE v.rank = 1;

* 위 쿼리문은 한번 검색한 결과를 테이블로 만들어 다시 검색하는 쿼리문이다.

* 때문에 FROM절에서 rank() 함수를 사용하여 월급이 높은 순서대로 rank를 달아 테이블로 만들고 그중에서 1위인 값을 출력한다.

조건에 따라 재배치 된 후 생성된 테이블의 데이터

 

(SELECT절 서브쿼리문)

14. 직업이 'SALESMAN'인 사원들의 이름과 월급을 출력하는데, 최대 월급과 최소 월급도 같이 출력.

SELECT ename, sal,
(SELECT max(sal) FROM emp WHERE job = 'SALESMAN') AS "최대 월급",
(SELECT min(sal) FROM emp WHERE job = 'SALESMAN') AS "최소 월급"
FROM emp
WHERE job = 'SALESMAN';

* 최대 월급과 최소 월급을 각각 쿼리문으로 작성하여 AS로 이름을 작성하면 아래에 있는 FROM, WHERE절과 상관없이 내가 검색한 값이 출력된다.

* 이름을 지정할 때 " " 를 사용하지 않으면 오류가 발생하니 주의해야 한다.