select RPAD(RPAD(ename||' '||sal,15,' '),(sal/100)+15,'*') from emp;
select ename, job, dname, loc from emp e join dept d on e.deptno=d.deptno and ename like '%L%';
select ename, job, dname, loc from emp e natural join dept d where ename like '%L%';
select e.ename, sal, s.grade from emp e join salgrade s on sal between losal and hisal;
s1 (union all) s2 = a+b+b+c //중복해서 표시
s1 union s2 = a+b+c
s1 minus s2 = a
s1 intersect s2 = b
select ename, sal job from emp where ename like '%S%' //A
select ename sal, null(개수맞추기) from emp where ename like '%S%' //B
A minus B
20번 부서 이름 급여 부서 업무
2000이상 이름 급여 부서 업무
select e.empno, e.ename, e.mgr, m.ename from emp e join emp m on e.mgr=m.empno;
select e.empno, e.ename, e.mgr, m.ename from emp e left outer join emp m on e.mgr=m.empno;
select e.empno, e.ename, e.mgr, m.ename from emp e right outer join emp m on e.mgr=m.empno;
select e.empno, e.ename, e.mgr, m.ename from emp e full outer join emp m on e.mgr=m.empno;
select e.empno, e.ename, d.dname, d.loc, sal, grade from emp e join dept d on e.deptno=d.deptno join salgrade on sal between losal and hisal;
//세개 테이블 조인!!!!
select e.empno, e.ename, e.mgr, m.ename, d.dname from emp e full outer join emp m on e.mgr=m.empno join dept d on e.deptno=d.deptno;
select m.empno, m.ename, e.empno, e.ename from emp m join emp e on m.empno=e.mgr join dept d on d.deptno;
//
select e.empno, e.ename, m.empno, m.ename, d.dname from emp e full outer join emp m on e.mgr=m.empno join dept d on m.deptno=d.deptno;
//조건기술이 중요!!!!!
emp e right outer join emp m on e.mgr=m.empno left outer join dept d on e.deptno=d.deptno;
from emp e join dept d on e. deptno= d.deptno right outer join emp m on e. mgr=m.empno;
//조인 순서..가 다름!!!!!
select empno, ename, sal, job from emp where sal>3000;
select sal from emp where ename='SCOTT'; //3000
//합치면
select empno, ename, sal, job from emp where sal>(select sal from emp where ename='SCOTT');
//서브쿼리
select a.empno, a.ename, a.sal, a.job, b.ename, b.sal from emp a join emp b on a.sal > b.sal and lower(b.ename)=lower('scott');
//급여가 스캇의 급여보다 많이 받는 사번, 이름, 급여, 업무
select a.empno, a.ename, a.sal, a.job from emp a join emp b on a.job = b.job and lower(b.ename)=lower('scott');
select empno, ename, sal, job from emp where job=(select job from emp where ename='SCOTT');
//스캇의 업무와 같은 사람표시
select ename, job, sal from emp where job=(select job from emp where ename='SMITH') or sal>(select sal from emp where empno=7499);
select a.empno, a.ename, a.sal, a.job from emp a join emp b on (a.job = b.job and lower(b.ename)=lower('SMITH')) or (a.sal>b.sal and b.empno=7499);
//스미스와 업무가 같거나 7499의 급여보다 많이 받는 사람
select count(*), count(comm), avg(comm), count(distinct deptno) from emp;
select count(*), avg(sal), min(sal), max(sal), sum(sal) from emp group by deptno order by sum(sal) desc;
select deptno, avg(sal), max(sal), min(sal) from emp where sal>=2900 group by deptno;
select empno, ename, sal, job, deptno from emp where deptno=(select distinct deptno from emp where job='CLERK');
//두줄이상이 리턴되어 에러
select empno, ename, sal, job, deptno from emp where deptno in (select distinct deptno from emp where job='CLERK');
//IN을 사용해서 해결
단일행 연산자 비교시 >,=,<
복수행 연산자 비교시 in, any, all, exist
select empno, ename, sal, dname from emp natural join dept where sal>(select min(sal) from emp where deptno=10);
//10번 부서의 최소급여보다 많이 받는 사번 이름 급여 부서
//any (or)
//all (and) 연산자포함
//효율적이지 못함
select empno, ename, sal, dname from emp natural join dept where sal//10번 부서의 최대급여보다 적게 받는 사번 이름 급여 부서
select empno, ename, sal, dname from emp natural join dept where sal//10번 부서의 최소급여보다 적게 받는 사번 이름 급여 부서
select empno, ename, sal, dname from emp natural join dept where sal>any(select sal from emp where deptno=10);
//보다 효율적!!!!
select ename, job from emp where empno=any(select mgr from emp);
//관리자만
select ename, job from emp where empno=any(select distinct mgr from emp);
select ename, job, mgr from emp where empno not in(select distinct NVL(mgr,empno) from emp);
//말단사원만
//mgr중 NULL이 들어있으므로 not in 연산시 true와 NULL의 AND연산시 NULL이 되므로 값이 반환되지 않는다.
//표 참조
//NVL함수를 사용해서 실수값을 할당해야만 한다.!!!!
//
select ename, job, mgr from emp where empno e exists (select mgr from emp where e. empno=mgr);
//비효율적임
SELECT ename, sal, job, deptno from emp where job=(select job from emp where ename='SMITH') and deptno=(select deptno from emp where ename='SMITH');
//SMITH 와 급여가 같으면서 부서가 동일 사람
SELECT ename, sal, job, deptno from emp where job||deptno=(select job||deptno from emp where ename='SMITH');
//꽁수
select ename, job, deptno from emp where (job, deptno) in (select job, deptno from emp where ename='SMITH');
select empno, ename, deptno, sal, comm from emp where (sal,NVL(comm,-1)) in (select sal, NVL(comm,-1) from emp where deptno=30);
//()로 묶는것이 포인트.. N:N비교
01
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);
02
select empno, ename, job, sal, deptno from emp where sal<(select avg(sal) from emp);
03
select deptno, min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=20);
04
select e.job, e.asal from (select job, avg(sal) as asal from emp group by job) e, (select min(avg(sal)) a_sal from emp group by job) a where e.asal=a.a_sal;
select job, avg(sal) from emp group by job having avg(sal)=(select min(avg(sal)) from emp group by job);
05
select empno, ename, job, hiredate, sal, deptno from emp where sal in (select min(sal) from emp group by job);
06
select empno, ename, job, hiredate, sal, deptno from emp where sal > any (select sal from emp where deptno=30) and deptno != 30;
//***
07
select empno, ename, job, hiredate, sal deptno from emp where sal>all(select sal from emp where deptno=30) and deptno!=30;
//***
08
select empno, ename, job, hiredate, sal, deptno from emp where empno in (select NVL(mgr,0) from emp);
select empno, ename, job, hiredate, sal, deptno from emp e where exists (select * from emp where e.empno=mgr);
//****
09
select ename, deptno, sal, comm from emp where (sal,comm) in (select sal, comm from emp where deptno=30);
//***
10
select empno, ename, job, sal, deptno from emp where sal in (select min(sal) from emp group by job) order by job;
11
select ename, deptno, sal, comm from emp where (sal, NVL(comm,0)) in (select sal, NVL(comm,0) from emp where deptno=30);
12
select empno, ename, job, sal, deptno from emp where (job, sal) in (select job, min(sal) from emp group by job ) order by job;
13
select e.ename, e.job, d.dname, d.loc from emp e, dept d where e.deptno=d.deptno and e.job='MANAGER';
14
select ename, job, dname, loc from (select deptno, ename, job from emp where job='MANAGER') e , dept d where e.deptno=d.deptno;
select ename, job, dname, loc from (select ename, job, deptno from emp where job='MANAGER') e join dept d on e.deptno=d.deptno;
01
select ename, hiredate from emp where deptno=(select deptno from emp where ename='BLAKE');
02
select empno, ename from emp where sal>(select avg(sal) from emp) order by sal desc;
03
select empno, ename, sal, deptno from emp where deptno in (select deptno from emp where ename like '%T%');
04
select ename, job, sal from emp where deptno=(select deptno from dept where lower(loc)=lower('dallas'));
05
select ename, sal, mgr from emp where mgr=(select empno from emp where ename='KING');
06
select ename, job from emp where deptno=(select deptno from dept where dname='SALES');
07
select ename, deptno, sal from emp where sal>(select min(sal) from emp where deptno=30);
08
select * from emp where deptno=10 and job in (select job from emp where deptno=30);
09
select * from emp where (job, sal) in (select job, sal from emp where ename='FORD');
10
select * from emp where job=(select job from emp where ename='JONES') or sal>=(select sal from emp where ename='FORD') order by job, sal desc;
11
select ename, job, sal from emp where sal in (select sal from emp where ename in ('SCOTT','WARD'));
12
select ename, job from emp where job in (select job from emp where deptno=(select deptno from dept where loc='CHICAGO'));
13. EMP 테이블에서 부서별로 월급이 평균 월급보다 높은 사원을 부서번, 이름, 급여를 출력하는 SELECT 문을 작성하시오.
select e.deptno, e.ename, e.sal, e.job, a.a_sal "jobsalavg" from emp e, (select deptno, avg(sal) as a_sal from emp group by deptno) a where a.deptno=e.deptno and e.sal>a.a_sal;
14. EMP 테이블에서 업무별로 월급이 평균월급보다 낮은 사원을 부서번호, 이름, 급여를 출력하는 SELECT 문을 작성하시오.
select e.deptno, e.ename, e.sal, e.job, a.a_sal "jobsalavg" from emp e, (select job, avg(sal) as a_sal from emp group by job) a where a.job=e.job and e.sal>a.a_sal;
//핵심은 알리아스!!!!
15
select job, ename, empno, deptno from emp where empno in (select distinct NVL(mgr,0) from emp);
16
select empno, ename, job, deptno from emp where empno not in (select distinct NVL(mgr,0) from emp);
select ename, job, dname, loc from emp e join dept d on e.deptno=d.deptno and ename like '%L%';
select ename, job, dname, loc from emp e natural join dept d where ename like '%L%';
select e.ename, sal, s.grade from emp e join salgrade s on sal between losal and hisal;
s1 (union all) s2 = a+b+b+c //중복해서 표시
s1 union s2 = a+b+c
s1 minus s2 = a
s1 intersect s2 = b
select ename, sal job from emp where ename like '%S%' //A
select ename sal, null(개수맞추기) from emp where ename like '%S%' //B
A minus B
20번 부서 이름 급여 부서 업무
2000이상 이름 급여 부서 업무
select e.empno, e.ename, e.mgr, m.ename from emp e join emp m on e.mgr=m.empno;
select e.empno, e.ename, e.mgr, m.ename from emp e left outer join emp m on e.mgr=m.empno;
select e.empno, e.ename, e.mgr, m.ename from emp e right outer join emp m on e.mgr=m.empno;
select e.empno, e.ename, e.mgr, m.ename from emp e full outer join emp m on e.mgr=m.empno;
select e.empno, e.ename, d.dname, d.loc, sal, grade from emp e join dept d on e.deptno=d.deptno join salgrade on sal between losal and hisal;
//세개 테이블 조인!!!!
select e.empno, e.ename, e.mgr, m.ename, d.dname from emp e full outer join emp m on e.mgr=m.empno join dept d on e.deptno=d.deptno;
select m.empno, m.ename, e.empno, e.ename from emp m join emp e on m.empno=e.mgr join dept d on d.deptno;
//
select e.empno, e.ename, m.empno, m.ename, d.dname from emp e full outer join emp m on e.mgr=m.empno join dept d on m.deptno=d.deptno;
//조건기술이 중요!!!!!
emp e right outer join emp m on e.mgr=m.empno left outer join dept d on e.deptno=d.deptno;
from emp e join dept d on e. deptno= d.deptno right outer join emp m on e. mgr=m.empno;
//조인 순서..가 다름!!!!!
select empno, ename, sal, job from emp where sal>3000;
select sal from emp where ename='SCOTT'; //3000
//합치면
select empno, ename, sal, job from emp where sal>(select sal from emp where ename='SCOTT');
//서브쿼리
select a.empno, a.ename, a.sal, a.job, b.ename, b.sal from emp a join emp b on a.sal > b.sal and lower(b.ename)=lower('scott');
//급여가 스캇의 급여보다 많이 받는 사번, 이름, 급여, 업무
select a.empno, a.ename, a.sal, a.job from emp a join emp b on a.job = b.job and lower(b.ename)=lower('scott');
select empno, ename, sal, job from emp where job=(select job from emp where ename='SCOTT');
//스캇의 업무와 같은 사람표시
select ename, job, sal from emp where job=(select job from emp where ename='SMITH') or sal>(select sal from emp where empno=7499);
select a.empno, a.ename, a.sal, a.job from emp a join emp b on (a.job = b.job and lower(b.ename)=lower('SMITH')) or (a.sal>b.sal and b.empno=7499);
//스미스와 업무가 같거나 7499의 급여보다 많이 받는 사람
select count(*), count(comm), avg(comm), count(distinct deptno) from emp;
select count(*), avg(sal), min(sal), max(sal), sum(sal) from emp group by deptno order by sum(sal) desc;
select deptno, avg(sal), max(sal), min(sal) from emp where sal>=2900 group by deptno;
select empno, ename, sal, job, deptno from emp where deptno=(select distinct deptno from emp where job='CLERK');
//두줄이상이 리턴되어 에러
select empno, ename, sal, job, deptno from emp where deptno in (select distinct deptno from emp where job='CLERK');
//IN을 사용해서 해결
단일행 연산자 비교시 >,=,<
복수행 연산자 비교시 in, any, all, exist
select empno, ename, sal, dname from emp natural join dept where sal>(select min(sal) from emp where deptno=10);
//10번 부서의 최소급여보다 많이 받는 사번 이름 급여 부서
//any (or)
//all (and) 연산자포함
//효율적이지 못함
select empno, ename, sal, dname from emp natural join dept where sal//10번 부서의 최대급여보다 적게 받는 사번 이름 급여 부서
select empno, ename, sal, dname from emp natural join dept where sal//10번 부서의 최소급여보다 적게 받는 사번 이름 급여 부서
select empno, ename, sal, dname from emp natural join dept where sal>any(select sal from emp where deptno=10);
//보다 효율적!!!!
select ename, job from emp where empno=any(select mgr from emp);
//관리자만
select ename, job from emp where empno=any(select distinct mgr from emp);
select ename, job, mgr from emp where empno not in(select distinct NVL(mgr,empno) from emp);
//말단사원만
//mgr중 NULL이 들어있으므로 not in 연산시 true와 NULL의 AND연산시 NULL이 되므로 값이 반환되지 않는다.
//표 참조
//NVL함수를 사용해서 실수값을 할당해야만 한다.!!!!
//
select ename, job, mgr from emp where empno e exists (select mgr from emp where e. empno=mgr);
//비효율적임
SELECT ename, sal, job, deptno from emp where job=(select job from emp where ename='SMITH') and deptno=(select deptno from emp where ename='SMITH');
//SMITH 와 급여가 같으면서 부서가 동일 사람
SELECT ename, sal, job, deptno from emp where job||deptno=(select job||deptno from emp where ename='SMITH');
//꽁수
select ename, job, deptno from emp where (job, deptno) in (select job, deptno from emp where ename='SMITH');
select empno, ename, deptno, sal, comm from emp where (sal,NVL(comm,-1)) in (select sal, NVL(comm,-1) from emp where deptno=30);
//()로 묶는것이 포인트.. N:N비교
01
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);
02
select empno, ename, job, sal, deptno from emp where sal<(select avg(sal) from emp);
03
select deptno, min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=20);
04
select e.job, e.asal from (select job, avg(sal) as asal from emp group by job) e, (select min(avg(sal)) a_sal from emp group by job) a where e.asal=a.a_sal;
select job, avg(sal) from emp group by job having avg(sal)=(select min(avg(sal)) from emp group by job);
05
select empno, ename, job, hiredate, sal, deptno from emp where sal in (select min(sal) from emp group by job);
06
select empno, ename, job, hiredate, sal, deptno from emp where sal > any (select sal from emp where deptno=30) and deptno != 30;
//***
07
select empno, ename, job, hiredate, sal deptno from emp where sal>all(select sal from emp where deptno=30) and deptno!=30;
//***
08
select empno, ename, job, hiredate, sal, deptno from emp where empno in (select NVL(mgr,0) from emp);
select empno, ename, job, hiredate, sal, deptno from emp e where exists (select * from emp where e.empno=mgr);
//****
09
select ename, deptno, sal, comm from emp where (sal,comm) in (select sal, comm from emp where deptno=30);
//***
10
select empno, ename, job, sal, deptno from emp where sal in (select min(sal) from emp group by job) order by job;
11
select ename, deptno, sal, comm from emp where (sal, NVL(comm,0)) in (select sal, NVL(comm,0) from emp where deptno=30);
12
select empno, ename, job, sal, deptno from emp where (job, sal) in (select job, min(sal) from emp group by job ) order by job;
13
select e.ename, e.job, d.dname, d.loc from emp e, dept d where e.deptno=d.deptno and e.job='MANAGER';
14
select ename, job, dname, loc from (select deptno, ename, job from emp where job='MANAGER') e , dept d where e.deptno=d.deptno;
select ename, job, dname, loc from (select ename, job, deptno from emp where job='MANAGER') e join dept d on e.deptno=d.deptno;
01
select ename, hiredate from emp where deptno=(select deptno from emp where ename='BLAKE');
02
select empno, ename from emp where sal>(select avg(sal) from emp) order by sal desc;
03
select empno, ename, sal, deptno from emp where deptno in (select deptno from emp where ename like '%T%');
04
select ename, job, sal from emp where deptno=(select deptno from dept where lower(loc)=lower('dallas'));
05
select ename, sal, mgr from emp where mgr=(select empno from emp where ename='KING');
06
select ename, job from emp where deptno=(select deptno from dept where dname='SALES');
07
select ename, deptno, sal from emp where sal>(select min(sal) from emp where deptno=30);
08
select * from emp where deptno=10 and job in (select job from emp where deptno=30);
09
select * from emp where (job, sal) in (select job, sal from emp where ename='FORD');
10
select * from emp where job=(select job from emp where ename='JONES') or sal>=(select sal from emp where ename='FORD') order by job, sal desc;
11
select ename, job, sal from emp where sal in (select sal from emp where ename in ('SCOTT','WARD'));
12
select ename, job from emp where job in (select job from emp where deptno=(select deptno from dept where loc='CHICAGO'));
13. EMP 테이블에서 부서별로 월급이 평균 월급보다 높은 사원을 부서번, 이름, 급여를 출력하는 SELECT 문을 작성하시오.
select e.deptno, e.ename, e.sal, e.job, a.a_sal "jobsalavg" from emp e, (select deptno, avg(sal) as a_sal from emp group by deptno) a where a.deptno=e.deptno and e.sal>a.a_sal;
14. EMP 테이블에서 업무별로 월급이 평균월급보다 낮은 사원을 부서번호, 이름, 급여를 출력하는 SELECT 문을 작성하시오.
select e.deptno, e.ename, e.sal, e.job, a.a_sal "jobsalavg" from emp e, (select job, avg(sal) as a_sal from emp group by job) a where a.job=e.job and e.sal>a.a_sal;
//핵심은 알리아스!!!!
15
select job, ename, empno, deptno from emp where empno in (select distinct NVL(mgr,0) from emp);
16
select empno, ename, job, deptno from emp where empno not in (select distinct NVL(mgr,0) from emp);
