select emp.*, dept.dname, dept.loc
from emp, dept
where emp.deptno=dept.deptno
order by empno;
select e.*, d.dname, d.loc
from emp e , dept d
where e.deptno=d.deptno
order by e.empno;
select e.*, d.dname, d.loc
from emp e , dept d
where e.deptno=d.deptno -- join 조건
and e.sal>=3000
order by e.empno;
-- ANSI SQL
select e.*, d.dname, d.loc
from emp e join dept d
on e.deptno=d.deptno -- join 조건
and e.sal>=3000
order by e.empno;
select * from salgrade;
-- non equi join
select e.*,s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;
-- self join
select e1.empno, e1.ename, e2.ename as manager
from emp e1, emp e2
where e1.mgr=e2.empno;
-- left outer join
select e1.empno, e1.ename, e2.ename as manager
from emp e1, emp e2
where e1.mgr=e2.empno(+);
--ansi sql
select e1.empno, e1.ename, e2.ename as manager
from emp e1 left outer join emp e2
on e1.mgr=e2.empno;
-- right outer join
select e2.empno, e2.ename, e1.ename as manager
from emp e1, emp e2
where e1.empno(+)=e2.mgr;
-- ansi sql
select e2.empno, e2.ename, e1.ename as manager
from emp e1 right outer join emp e2
on e1.empno=e2.mgr;
-- full outer join
select e2.empno, e2.ename, e1.ename as manager
from emp e1 full outer join emp e2
on e1.empno=e2.mgr;
select * from emp;
select * from salgrade;
create table category(
cno number primary key,
cname varchar2(50) not null,
parent_cno number not null
)
insert into category values(1,'패션의류/잡화',0);
insert into category values(10,'여성패션',1);
insert into category values(100,'의류',10);
commit;
select c1.cno, c1.cname, c2.cname as parent_category
from category c1 left outer join category c2
on c1.parent_cno=c2.cno;
create table study(
sno number primary key,
sname varchar2(50) not null
);
insert into study values(1,'HTML스터디');
insert into study values(2,'CSS스터디');
insert into study values(3,'SQL스터디');
commit;
create table study_join(
sjno number primary key,
empno number references emp(empno),
sno number references study(sno)
)
insert into study_join values(1, 7369, 1);
insert into study_join values(2, 7839, 3);
insert into study_join values(3, 7369, 3);
commit;
select * from study_join;
-- Table 3개 사용해서 조인
select study_join.empno, emp.ename, study.sname
from study_join, emp, study
where study_join.empno=emp.empno
and study_join.sno=study.sno;
-- ansi sql
select study_join.empno, emp.ename, study.sname
from study_join inner join emp
on study_join.empno=emp.empno
inner join study
on study_join.sno=study.sno;
-- study에 가입하지 않은 직원도 모두 출력하면서 join
select study_join.empno, emp.ename, study.sname
from study_join right outer join emp
on study_join.empno=emp.empno
left outer join study
on study_join.sno=study.sno
where emp.sal<2000;
select sal from emp where ename='JONES';
select * from emp where sal>2975;
select * from emp where sal > (select sal from emp where ename='JONES');
select * from emp
where deptno=(
select deptno from dept where dname='ACCOUNTING'
);
select emp.*, dept.dname
from emp, dept
where emp.deptno=dept.deptno
and dept.dname='ACCOUNTING';
