#select 형식 순서
select 컬럼(들) 5
from 테이블(들) 1
[where 조건식] 2
[group by 컬럼(들)] 3
[having 조건식] 4
[order by 컬럼(들)] 6
#join
조회하고자하는 컬럼이 두 개 이상의 테이블에 있을때 조인 이용
#self join
실제로는 하나의 테이블 / 컬럼 값이 그 테이블의 다른 컬럼을 참조할때 사용하는 조인
emp 테이블의 mgr은 emp 테이블의 eno 참조하고 있음
직원이름과 관리자 이름 출력하고자하면 emp 테이블 하나는 "직원" 애칭 주고 하나는 "관리자" 애칭 줘서 조인 할 수 있음
emp 테이블의 mgr은 emp 테이블의 eno 참조
만약 모든 직원 이름과 관리자이름 출력해야 한다면 self 조인 가능
emp e 직원
emp m 관리자
select e.ename, m.ename
from emp e, emp m
where e.mgr = m.eno;
emp > eno , ename, ename, dno >> eno, e.dno, e.ename/m.ename
dept > dno, dname >> d.dno, dname
ex) 개발 팀에 근무하는 모든 직원들의 사원번호, 사원명, 관리자명, 부서번호, 부서명 출력
select e.eno, e.ename, m.ename, d.dno, d.dname
from emp e, emp m, dept d
where e.mgr = m.eno and
e.dno= d.dno and
dname like '개발%';
ex) '개발' 팀에 근무하는 직원들 중에 관리자보다 입사일이 빠른 직원의 사원번호, 사원명, 관리자명, 입사일, 관리자의 입사일 출력
select e.eno, e.ename, m.ename, e.hiredate, m.hiredate
from emp e, emp m, dept d
where e.mgr = m.eno and e.dno =d.dno
and dname like '%개발%' and e.hiredate < m.hiredate;
ex) '판교' 나 '종각' 근무, 직책이 '사원'이거나 '대리'인 직원들 중
입사일이 관리자보다 빠르거나 급여가 관리자보다 많은 직원들의
사원번호, 사원명, 관리자명, 입사일, 관리자의 입사일, 급여, 관리자의 급여 출력
(입사일 순으로 출력, 동일 시 급여 높은순)
select e.eno, e.ename, m.ename, e.hiredate, m.hiredate, e.salary, m.salary
from emp e, emp m, dept d
where e.mgr = m.eno and d.dno = e.dno
and dloc in ('판교', '종각') and e.job in ('사원','대리')
and (e.hiredate < m.hiredate or e.salary > m.salary)
order by e.hiredate asc, e.salary desc;
ex) '홍길동'의 부하 직원들의 사원번호, 사원명, 입사일, 급여 출력
(입사일 기준 출력)
select e.eno, e.ename, e.hiredate, e.salary
from emp e, emp m
where e.mgr= m.eno
and m.ename = '홍길동'
order by hiredate;
ex) 고객 번호 별로 주문한 건수 출력
select custid, count(*)
from orders
group by custid;
ex) 고객 이름별로 주문한 건수 출력
select c.name, count(*)
from customer c, orders o
where c.custid = o.custid
group by name;
select c.name, count(*) from customer c, orders o where c.custid = o.custid group by name;
#일반적인 조인 (inner join)
두개의 테이블에 모두 조건식을 만족하는 레코드 검색
#외부 조인 (outer join)
두개의 테이블에 왼쪽이나 오른쪽에 레코드는 조건을 만족하지 않아도 모두 검색
select 컬럼이름(들) from 테이블 1 right | left outer join 테이블2 on 조건식
#left outer join
조건 만족하지 않더라도 왼쪽의 테이블 무조건 검색
#right outer join
조건 만족하지 않더라도 오른쪽의 테이블 무조건 검색
ex) 고객 이름별로 주문건수 출력 (주문 없는 고객 이름도 출력)
select c.name, count(*)
from customer c left outer join orders o
on c.custid = o.custid
group by name;
>>이렇게 하면 주문 안한 사람도 주문건수 1로 count 됨
select c.name, count(orderid)
from customer c left outer join orders o
on c.custid = o.custid
group by name;
>> 정상적으로 작동됨
ex) 관리자 이름 별로 부하직원 수 출력
(부하직원이 없는 직원 이름도 출력)
select m.ename , count(e.ename)
from emp e right outer join emp m
on e.mgr = m.eno
group by m.ename;
select m.ename , count(e.ename) from emp e right outer join emp m on e.mgr = m.eno group by m.ename;
ex) 부서명 별로 총직원수 출력
(소속된 직원 없는 부서명도 출력)
select d.dname, count(e.ename)
from emp e right outer join dept d
on d.dno = e.dno
group by dname;
ex) 출판사별 총주문건수, 총주문금액 출력 (주문내역 없는 출판사도 출력)
select publisher, count(saleprice), sum(saleprice)
from book b left outer join orders o
on b.bookid = o.bookid
group by publisher;
count 함수는 조건을 만족하는 레코드가 한개도 없으면 0
count 외의 집계함수는 조건을 만족하는 레코드 없으면 null
>> null인 값을 다른 값으로 채우고 싶으면 > nvl 함수 사용
select publisher, count(saleprice) cnt, nvl (sum(saleprice),0) sum
from book b left outer join orders o
on b.bookid = o.bookid
group by publisher;
