6월16일 수업내용

select * from emp group by job having (select avg(sal) from emp group by job);

select job, avg(sal) as asal from emp e, (select job, avg(sal) as asal from emp group by job) a where e.asal
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);
//직업별 평균급여중 최소값

select ename, job, dname, loc from (select ename, job, deptno, empno from emp where job='MANAGER') e join dept d on e.deptno=d.deptno;
//직업이 MANAGER인 사람의 이름 직업 부서명 지역명

select ename, e.job, sal from (select job, min(sal) msal from emp group by job) m, emp e where e.job=m.job and sal=msal;

select ename, e.job, sal from (select job, min(sal) msal from emp group by job) m join emp e on e.job=m.job and sal=msal;
//업무별 최소급여를 받는 사람

select ename, sal from emp e where sal>(select avg(sal) from emp where e.deptno=deptno)
//효율이 떨어짐.. 10000x100

select ename, hiredate from emp where deptno=(select deptno from emp where ename='BLAKE');

select ename, job, sal from emp where deptno=(select deptno from dept where loc='DALLAS');
//달라스에 근무하는 사람들

select ename, job, sal from emp e join dept d on e.deptno=d.deptno and loc='DALLAS';
//구지 조인할필요가 없음
//효율이 떨어짐

select ename, job, sal, mgr, empno from emp
where mgr in (select ename, job, sal, mgr, empno from emp
where mgr in (select empno from emp where ename='KING'));

select * from emp where (job, sal) IN(select job, sal from emp where ename='FORD') and ename !='FORD';
//포드와 월급과 직업이 같은 사람을 출력
//본인 제외
select ename, job, sal 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;
//존과 직업이 같거나 포드보다 월급이 많은사람

select ename, job, sal from emp where sal in (select sal from emp where ename in ('SCOTT','WARD')) and ename not in ('SCOTT','WARD');
//스캇과 워드와 같은 월급 받는 사람

select ename, job, deptno from emp where job in (select job from emp natural join dept where loc='CHICAGO');
//하나로 처리
//주의!!

select * from emp where mgr=(select mgr from emp where ename='BLAKE') and ename!='BLAKE';
//BLAKE와 같은 상사를 가진 사람

select * from emp e where 5>(select count(*) from emp where sal>e.sal) order by sal desc;
//급여가 많은 순서로 5명 출력


select rownum, empno, ename, sal from emp;
//rownum 숨겨진 컬럼명
//
select rownum, empno, ename, sal from emp order by sal;

select rownum, empno, ename, sal from emp where rownum<=5 order by sal desc;

//rownum은 PK를 따라다님

select * from(select rownum, empno, ename, sal from emp order by sal desc) where rownum<=5;
//따라서 월급에 따라 정렬한 뷰를 만들어서 rownum을 호출하면 정렬된 순서대로 가져올 수 있다.!!!!!!!



**PLSQL**


ed
RUN(/)

>ed a
select * from emp; //<--작정 a라는 SQL 문

>/ //이전에 실행했던 SQL문 실행
>@a //a라는 SQL문 실행

>ed b
select * from emp; //<--작성 b라는 SQL 문

>/ //이전에 실행했던 SQL문 실행
>@b //b라는 SQL문 실행

>ed //버퍼영역 재실행
select ename from emp; //버퍼영역수정
>/
>@b //b는 수정되지 않은 dept가 실행

>ed b //<--기본확장자 SQL
select ename, sal from emp; //b를 수정
>@b //b를 실행

>get a //a를 버퍼로 불러옴
>/ //버퍼에 있는 a내용 실행
//위의 두개는 @a

>select empno, ename, sal, sal*12 from emp;
>save c:\c //버퍼의 내용을 c:\c.sql로 저장

>ed w
set linesize 120
set pagesize 50
/
>@W /저장해서 쓰면 편함
//



>select empno, ename
2 from emp
3 where deptno=10;
>L(ist)
>3
>L

>C/10/20 //바꾸기 10을 20으로(deptno)
>L
>1
>L
>A ,sal,job //추가 ,sal,job을 추가
>L

>del 3 //3행을 없앰
>L
>3 where sal>2000
4 //3수정후 엔터치면 4로..
>L

>2
>A join dept //추가 join dept
>L

>2
>i //input
>3i on emp.deptno=dept.deptno //세번째줄로 추가됨(2다음)