[MySQL] #17 서브 쿼리 실습을 해보자~

-- ### equi join ###

-- 1.  emp와 dept Table을 JOIN하여 부서번호, 부서명, 이름, 급여를  출력하라.

SELECT d.deptno, d.dname, e.ename, e.sal
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- 2.  이름이 ‘SMITH’인 사원의 부서명을 출력하라.

SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename = 'SMITH';

 
-- ### outer join ###
-- 3.  dept Table에 있는 모든 부서를 출력하고, emp Table에 있는 DATA와 JOIN하여 
-- 모든 사원의 이름, 부서번호, 부서명, 급여를 출력 하라.

SELECT e.ename, d.deptno, d.dname, e.sal
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;

-- ###self join###
-- 4.  emp Table에 있는 empno와 mgr을 이용하여 서로의 관계를 다음과 같이 출력하라. ‘SMTH의 매니저는 FORD이다’

SELECT concat(e.ename, '의 매니저는 ', m.ename, '이다') Info
FROM emp e, emp m
WHERE e.mgr = m.empno;

-- ### join 실습 ###

-- 5. 관리자가 7698인 사원의 이름, 사원번호, 관리자번호, 관리자명을 출력하라.

SELECT e.ename, e.empno, e.mgr, m.ename
FROM emp e, emp m
WHERE e.mgr = m.empno
AND m.empno = '7698';

-- 6. 업무가 MANAGER이거나 CLERK인 사원의 사원번호, 이름, 급여, 업무, 부서명

SELECT e.empno, e.ename, e.sal, e.job, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND job IN ('MANAGER', 'CLERK');

7, 8번 문제 풀기전에 실행해야 되는 코드

-- dept와 비슷한 테이블(dept테이블의 10, 20부서데이터를 입력)을 하나 생성한다.

create table newdept 
as 
select * from dept where deptno in (10, 20);

-- 추가 데이터를 입력한다.
insert into newdept values(50, 'MARKETING', 'SEOUL');
select * from newdept;
select * from dept;

-- 7. dept테이블과 newdept 테이블의 모든 행을 출력하라.(단, 중복되는 행은 한번만 출력한다)

SELECT * FROM dept d
UNION
SELECT * FROM  newdept n;

-- 8. dept테이블과 newdept 테이블의 모든 행을 출력하라.

SELECT * FROM dept d
UNION ALL
SELECT * FROM  newdept n;

-- 9.  ‘SMITH'의 직무와 같은 사람의 이름, 부서명, 급여, 직무를  출력하라.

SELECT e.ename, d.dname, e.sal, e.job
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND job = (
		SELECT job
                FROM emp
                WHERE ename = 'SMITH'
          );

-- 10. 10번 부서와 같은 일을 하는 사원의 사원번호, 이름, 부서명,지역, 급여를 급여가 많은 순으로 출력하라.

SELECT e.empno, e.ename, d.dname, d.loc, e.sal
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND job IN (
		SELECT job
                FROM dept
                WHERE deptno = 10
           )
ORDER BY sal DESC;