JOIN : 연습 문제 1

스콧 사원의 이름, 부서 번호, 부서 이름 출력 (내부 조인)

select e.ename, e.dno, d.dname
from employee e, department d
where e.dno=d.dno and e.ename = '스콧';

select e.ename, e.dno, d.dname
from employee e
inner join department d
on e.dno= d.dno
where e.ename = '스콧';

 

사원명 부서명 지역명 출력
내부 조인 표준, JOIN~ON, JOIN~USING, EQUI 조인 4가지 방법

- 내부 조인

select e.ename,d.dname,d.loc
from employee e inner join department d
on e.dno = d.dno;

 

- JOIN ~ ON

select e.ename, d.dname,d.loc
from employee e join department d
on e.dno = d.dno;

- JOIN ~ USING

select e.ename, d.dname,d.loc
from employee e join department d
using(dno);

 

- EQUI

select e.ename, d.dname,d.loc
from employee e ,department d
where e.dno = d.dno;

 

이름에 스 가 들어간 모든 사원의 이름 부서명 출력 EQUI, NATURAL

 

- EQUI

select e.ename, d.dname
from employee e,department d
where e.dno = d.dno and e.ename like '%스%';

- NATURAL JOIN

select e.ename, d.dname
from employee e natural join department d
where e.ename like '%스%';

- INNER JOIN

select e.ename, d.dname
from employee e inner join department d
on e.dno = d.dno
where e.ename like '%스%';

커미션을 받는 사원명 부서명 근무지 EQUI, NATURAL
- EQUI

select e.ename, d.dname ,d.loc
from employee e,department d
where e.dno = d.dno and e.commission is not null;

- NATURAL JOIN

select e.ename, d.dname,d.loc
from employee e natural join department d
where e.commission is not null;

- INNER JOIN

select e.ename,d.dname,d.loc
from employee e inner join department d
on e.dno = d.dno
where e.commission is not null and commission not in(0);

 

뉴욕에 근무하는 모든 사원의 이름 업무 부서 번호 부서명 EQUI, NATURAL
- EQUI

select e.ename,e.job,e.dno,d.dname
from employee e,department d
where e.dno = d.dno and d.loc = '뉴욕';

- NATURAL JOIN

select e.ename,e.job,dno,d.dname
from employee e natural join department d
where d.loc = '뉴욕';

- INNER JOIN

select e.ename,e.job,e.dno,d.dname
from employee e inner join department d
on e.dno = d.dno
where d.loc = '뉴욕';