[DB] SQL 문제3

***Subquery 문제

1. Blake와 같은 부서에 있는 모든 직원의 사번, 이름, 입사일자 조회

SELECT deptno, empno, ename, hiredate

FROM emp

WHERE deptno=(select deptno from emp where ename='BLAKE');

2. SELECT empno, ename, deptno, sal, comm FROM emp

WHERE (sal, comm) IN(SELECT sal, comm FROM emp WHERE deptno=30);

이 쿼리에서 보너스가 null인 사람도 출력될 수 있도록 수정하시오.

3. 평균 급여 이상을 받는 직원들의  사번, 이름을 조회.

단, 급여가 많은 순으로 정렬

SELECT empno, ename, sal

FROM emp

WHERE sal>=(select avg(sal) from emp)

ORDER BY sal desc;

4. 이름에 T자가 들어가는 직원이 근무하는 부서에서 근무하는 직원의 사번,

이름, 급여 조회

SELECT empno, ename, sal, deptno 

FROM emp

WHERE deptno in(select deptno from emp where ename like '%T%');

5. 부서의 위치가 dallas인 모든 직원에 대해 사번, 이름, 급여, 업무조회

SELECT empno, ename, sal, job

FROM emp

WHERE deptno in(select deptno from dept where loc like 'DALLAS');

6. King에게 보고하는 모든 직원의 이름과 부서, 업무, 급여를 조회

SELECT ename, deptno, job, sal

FROM emp

WHERE mgr in(select empno from emp where ename='KING');

7. 급여가 30번 부서의 최저급여보다 높은 직원의 사번, 이름, 급여 조회

SELECT empno, ename, sal

FROM emp

WHERE sal>(select min(sal) from emp where deptno='30');

8. 10번부서에서 30번 부서의 직원과 같은 업무를 하는 직원의 이름과 업무를 조회

SELECT ename, job

FROM emp

WHERE deptno='10'

AND job in(select job from emp where deptno='30');

9. 가장 최근에 입사한 직원의 이름과 부서, 업무, 급여를 조회

SELECT ename, deptno, sal, hiredate

FROM emp

WHERE hiredate like '87%';

*** JOIN 문제

1. Newyork에서 근무하는 직원의 사번, 이름, 업무, 부서명을 조회

SELECT empno, ename, job, dname, loc

FROM emp e INNER JOIN dept d

ON e.deptno = d.deptno

WHERE d.loc='NEW YORK';

2. 보너스를 받는 직원에 대해 이름, 부서명, 근무지를 조회

SELECT  ename, dname, loc, comm

FROM emp e INNER JOIN dept d

ON e.deptno = d.deptno

AND e.comm is not null;

3. 이름 중간에 L자가 있는 직원의 이름, 업무, 부서명, 근무지를 조회

SELECT ename, job, dname, loc

FROM emp e INNER JOIN dept d

ON e.deptno = d.deptno

WHERE e.ename like '%L%';

4. 각 직원들에 대해 그들의 관리자보다 먼저 입사한 직원의 이름, 입사일, 

관리자 이름, 관리자 입사일을 조회

SELECT e.ename, e.hiredate, m.ename, m.hiredate

FROM emp e, emp m

WHERE e.mgr = m.empno and e.hiredate<m.hiredate;

5. 말단사원의 사번, 이름, 업무, 부서번호, 근무지를 조회

SELECT empno, ename, job, e.deptno, loc

FROM emp e RIGHT OUTER JOIN dept d

ON e.deptno = d.deptno

WHERE empno not in(select mgr from emp where mgr is not null);

*. 테이블을 생성한다.

CREATE TABLE tblBook(

author varchar2(20),

title varchar2(20)

);

INSERT INTO tblBook VALUES('최주현', '하늘과 땅');

INSERT INTO tblBook VALUES('최주현', '바다');

INSERT INTO tblBook VALUES('유은정', '바다');

INSERT INTO tblBook VALUES('박성우', '문');

INSERT INTO tblBook VALUES('최주현', '문');

INSERT INTO tblBook VALUES('박성우', '천국');

INSERT INTO tblBook VALUES('최지은', '천국');

INSERT INTO tblBook VALUES('최주현', '천국');

INSERT INTO tblBook VALUES('박성우', '고슴도치');

INSERT INTO tblBook VALUES('서금동', '나');

6. 한권의 책에 대해 두명 이상의 작가가 쓴 책을 검색

책이름 작가명 작가명

SELECT a.title, a.author, b.author

FROM tblBook a , tblBook b

WHERE a.title = b.title

AND a.author<b.author;

7. 한권의 책에 대해 세명의 작가가 쓴 책을 검색

책이름 작가명 작가명 작가명

SELECT a.title, a.author,  

FROM tblBook a, tblBook b, tblBook c

WHERE a.title = b.title 

AND b.title = c.title

AND a.author < b.author

AND b.author < c.author;