Coding Summary
2023. 11. 20. 00:43ㆍDaily Codig Reminder
서브쿼리와 오라클조인
오라클 조인
- left outer 조인은 left 로 지정된 테이블1의 데이터를
테이블2의 조인조건 일치여부와 상관없이 모두 출력한다는 의미 - right outer 조인은 right 로 지정된 테이블1의 데이터를
테이블2의 조인조건 일치여부와 상관없이 모두 출력한다는 의미 - full outer 조인은 right outer 조인 결과와 left outer 조인 결과를 합친 결과로서
양쪽 테이블1 과 테이블2 의 데이터를 조인조건 일치여부와 상관없이 모두 출력한다는 의미
select *from emp left join on emp.deptno = dept.deptno;
select count(8) 총인원수,
sum (decode( to_char(hiredate, 'YYYY'), 1980, 1, 0)) "1980",
sum (decode( to_char(hiredate, 'YYYY'), 1981, 1, 0)) "1981",
sum (decode( to_char(hiredate, 'YYYY'), 1982, 1, 0)) "1982"
from emp;
일치하면 1 아니면 0 을 반환받아 스미스부터 계산을 함
따라서 사원의 각자 해당연도에 따라 1씩 부여받고 계산을 더함.
select empno, ename, sal, dname
from emp e join d on e.deptno = d.deptno
where sal >= 2000 order by 3 desc;
실습문제 4번
select empno, ename, salm grade from
rmp e inner join dept d on e.deptno = d.deptno
inner join salgrade g
on e.sal betwwen g.losal and g.hisal
order by 4 desc;
사원테이블에서 사원명과 해당 사원의 관리자
select e.ename , m.ename, p.ename from
emp e left outer join emp m on e.mgr = m. empno
left join emp p on m.mggr = p.empno;
13. select e.ename , e.sal from emp e join emp m on e.mgr = m.empno
where e.sal > m.sal;
select d.dname, count(*) 전체인원
from emp e join dept d on e.deptno = d.deptno
where TO_CHAR(HIREDATE, 'YYYY') < 1982 group by d.dname;
=
select dname, count(*) 전체인원
from emp join dept using(deptno) where to_char(hiredate, 'yyyy') < '1982'
group by dname;
서브쿼리 중요!!!!!!!!
서브쿼리는 여러개의 select 문을 수행해야 얻을 수 있는 결과를 하나의 slect 문으로 해결
select ename, sal from emp where sal(select sal from emp where ename ='ALLEN');
- 단일행 서브쿼리
서브쿼리의 실행결과가 한개의 행을 반환
select ename , sal
from emp where sal >= (select avg(sal)from emp);
-> 평균보다 높은 월금.. 하나의 결과에 대응
select ename , hiredate
from emp where hiredate >=
(select hiredate from emp where ename = 'CLARK');
->클락씨보다 늦게 입사한 사람
select empno 사원번호, ename 이름, sal 월급
from emp where sal < (select avg(sal) from emp
join salgrade on sal between losal and hisal where grade = 2);
→→ 세일즈부서의 인원수보다 사원인원수가 적은 부서으 이름과 인원
select dname , count() from emp e join dept d
on e.deptno = d.deptno group by dname having
count()< (select count(*) from emp e
join dept d on e.deptno = d.deptno where dname = 'SALES');
→ 세일즈부서의 인원수보다 사원인원수가 적은 부서으 이름과 인원
select dname loc from emp e join dept d on e.deptno = d.deptno
where ename = 'SMITH';
-스미스가 속한 부서명 지역 / 서브쿼리X - 복수행 서브쿼리
동등비교 = 안쓰고in
하나라도 일치라면 참 ANY
15> 10, 20, 30 이라면 10 하나라도 15보다 작으므로 참
즉, 최소값
ALL 은 모든값이 일치
15> 10, 20, 30 이라면 10 하나만 돼서 거짓.
즉. 최대값
1) IN 연산자
서브쿼리 반환값이 복수.
메인쿼리와 동등연산자로 비교할 때 사용하는 연산자
select ename , deptno
from emp where deptno in(select deptno from emp where sal>= 3000);
2) ALL
select ename sal from emp where sal <all
(select sal from emp where deptno ='30');
-> 30번 부서 모든 월급보다 작은 사원의 이름 월급
select ename sal from emp where sal > all
(select sal from emp where deptno ='30');
-> 30번 부서 모든 월급보다 월급이 큰 사원의 이름 월급
3) ANY
select * from emp where sal < any
(select sal from emp where deptno = '30');
-> 30번 부서의 월급이 2850 이 최대인데 이보다 작은 값
4) EXIST
select ename , deptno , sal from emp where exists
(select empno from emp where comm is not null);
→만일 사원들 중에서 커미션을 받는 사원이한명이라도 있으면 모든 사원 정보를 출력 사원번호가 7369이면서 이름이 스미스인사람 셀렉 - 다중컬럼 서브쿼리
서브ㅝ리에서 여러개의 컬럼값을 검색하여 메인쿼리의 조건절과 비교
메인쿼리의 조건절은 서브쿼리의 컬럼과 일대일 매칭이 되어야
select * from emp where job = 'SALESMAN' and sal = 1250;
=
select * from emp where (job, sal) in (('SALESMAN' , 1250'));
select ename, deptno, sal from emp
where (deptno, sal ) in
(select deptno, max(sal) from emp group by deptno);
-> 10번부서 월급, 20번... 30번..
select empno, ename, job , hiredate, sal from emp
where job = ( select job from emp where empno = 7521)
and sal >(select sal from emp where empno = 7934);
-> EMP 테이블에서 산원번호가 7521 의 업무와 같고
급여가 사원번호 7934보다 많은 사원의 사원번호 이름 담당업무와 입사일자 급여를 출력. - 인라인 뷰
select select_list
from (서브쿼리)alis
where 조건식;
select e.deptno , 합계 , 평균, 인원수
from (select deptno , sum(sal) 합계 , avg(sal) 평균, count(*) 인원수
from emp group by deptno) e, dept d
where e.deptno = d.deptno order by 1;
-> 그룹핑으로 부서 3개를 3개와 조인
먼저 서브쿼리에서 부서번호를 사용하여 그룹핑을 하고 월급의 합계 평균 부서별 인원수를 구한다
사원테이블과 부서테이블에서 부서별 월급총합과 평균 그리고 부서별 인원수 출력
select ename, deptno , sal from emp
where sal in (select max (sal) from emp group by deptno);
=> 다중컬럼사용
select ename, deptno, sal from emp where (deptno ,sal)
in ((select deptno,max(sal) from emp group by deptno ));
=> and사용
select ename, deptno, sal from emp where deptno
in (select deptno from emp group by deptno) and
sal in (select Max(sal) from emp group by deptno);
→ 부서번호로 모으고 sal max 를 구하기
select ename 사원명, deptno 부서번호, dname 부서명, sal 월급
from emp join dept using(deptno) where (deptno, sal)
in (select deptno, max(sal) from emp group by deptno) order by 2;
서브쿼리에서 검색된 department_id와 MAX(salary)값과
메인쿼리의 department_id와 salary 컬럼을 pairwise 방식으로 비교하여
동시에 만족하는 경우에만 출력하는 방법이다
select dname, loc from dept where deptno
= (select deptno from emp where ename = 'SMITH');
→smith 가 속한 부서 명, 지역 /서브쿼리 O
'Daily Codig Reminder' 카테고리의 다른 글
DDL , 뷰 (0) | 2023.11.22 |
---|---|
DML DDL summary (1) | 2023.11.20 |
Database Summary (1) | 2023.11.19 |
Daily Reminder (0) | 2023.11.16 |
Daily Coding Summary (1) | 2023.11.14 |