Oracle - join 문제

SELECT * FROM EMP
SELECT * FROM DEPT
SELECT * FROM salgrade

 

>사원들의 이름, 부서번호, 부서이름을 출력하라?
==========================================================================
SELECT e.ename, e.deptno, d.dname
FROM EMP e, DEPT d
WHERE e.deptno = d.deptno

 

SELECT e.ename,DEPTNO, d.dname
FROM EMP e NATURAL join DEPT d
==========================================================================

 

>30번 부서의 사원들의 이름,직업,부서위치를 출력하라?
==========================================================================
SELECT e.ename, e.job, d.dname
FROM EMP e, DEPT d
WHERE e.deptno = d.deptno AND e.deptno=30

 

SELECT e.ename, e.job, d.dname
FROM EMP e NATURAL JOIN DEPT d
where deptno=30

==========================================================================

 

>커미션을 받는 사원의 이름, 직업, 부서번호,부서위치을 출력하라?
==========================================================================
SELECT e.ename, e.deptno, d.dname,d.loc
FROM EMP e, DEPT d
WHERE e.deptno = d.deptno AND e.comm IS NOT null

 

SELECT e.ename,deptno, d.dname, d.loc
FROM EMP e NATURAL JOIN  DEPT d
WHERE e.comm IS NOT null
==========================================================================

 

>dallas에서 근무하는 사원의 이름, 직업,부서번호,부서이름을 출력하라?
==========================================================================
SELECT e.ename, e.job, e.deptno, d.dname
FROM EMP e, DEPT d
WHERE e.deptno = d.deptno AND d.loc='DALLAS'

 

SELECT e.ename, e.job, deptno, d.dname
FROM EMP e NATURAL JOIN DEPT d
WHERE d.loc='DALLAS'
=========================================================================

 

 

> 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라?
=========================================================================
SELECT e.ename, d.dname
FROM EMP e, DEPT d
WHERE e.deptno=d.deptno AND e.ename LIKE '%A%'

 

SELECT e.ename, d.dname
FROM EMP e NATURAL JOIN  DEPT d
where e.ename LIKE '%A%'
=========================================================================

 

>  사원이름과 그 사원의 관리자 이름을 출력하라
=========================================================================
SELECT e.ename employee , c.ename manager
FROM EMP e, EMP c
WHERE  e.mgr = c.empno(+)

 

SELECT e.ename employee, c.ename manager
FROM EMP e left JOIN EMP c
on (e.mgr = c.empno)

=========================================================================

 

> 사원이름과 직업, 급여, 급여등급을 출력하라?
=========================================================================
SELECT e.ename, e.job, s.grade
FROM EMP e, salgrade s
WHERE e.sal BETWEEN S.LOSAL AND S.HISAL

 

SELECT e.ename, e.job, s.grade
FROM EMP e NATURAL JOIN  salgrade s
where e.sal BETWEEN S.LOSAL AND S.HISAL

=========================================================================

 

> 사원이름과 부서명과 월급을 출력하는데 월급이 3000 이상인 사원을 출력하라?
=========================================================================
 SELECT e.ename, d.dname, e.sal
 FROM EMP e, DEPT d
 WHERE e.deptno = d.deptno AND e.sal>=3000

 

SELECT e.ename, d.dname, e.sal
FROM EMP e JOIN DEPT d
USING(deptno)
WHERE e.sal>=3000
=========================================================================

 

>사원이름과 부서번호와 같은 부서에 근무하는 동료 사원들을 출력하라
ex> scott 20 smith  scott 20 jones smith 20 scott
=========================================================================
SELECT e.ename, e.deptno, c.ename
FROM EMP e , EMP c
WHERE e.deptno = c.deptno AND e.ename != c.ename
ORDER BY e.ename,deptno
-- != , <>  둘다 같지 않다라는 표현임

 

SELECT e.ename, e.deptno, c.ename
FROM EMP e  JOIN EMP c
on (e.deptno =c.deptno)
WHERE e.ename !=c.ename
ORDER BY e.ename,deptno

 

 

> BLAKE 이란 사원보다 늦게 입사한 사원의 이름과 입사일을 출력하라?
=========================================================================
SELECT ename, hiredate
FROM EMP
WHERE e.hiredate >(SELECT hiredate FROM EMP WHERE ename='BLAKE')

SELECT e.ename, c.hiredate
FROM EMP e  JOIN EMP c
ON (e.ename=c.ename)

WHERE  e.hiredate >(SELECT hiredate FROM EMP WHERE ename='BLAKE')

select c.ename,c.hiredate
from emp e, emp c
where e.ename = 'BLAKE' AND E.HIREDATE < C.HIREDATE
ORDER BY 1