Oracle 4

 

 

drop table 테이블명;    //삭제하면 쓰레기통으로 감. 따라서 purge를 사용해서 쓰레기통의 내용까지 비워줌

drop table 테이블명 purge;

select name, id ,
trunc(months_between(sysdate,to_date(substr(id,1,6),'rrmmdd'))/12)
from te;

 

 

Q.deptno별로 10,20,30  (count,decode나case사용)

totalcount dept10  dept20   dept30    

select count(decode(deptno,10,deptno)) "dept10",  //deptno라는 임의의값을 넣어서 카운드시켜준다. (10이아니면 값을 넣어주지 않으므로 나머지는 자동null값이 됨.(카운드 안함)
count(decode(deptno,20,deptno)) "dept20",
count(decode(deptno,30,deptno)) "dept30"
 from emp

select 학년,count(학년) from 학교 group by 학년// 개별적인 자료는 그룹함수와 사용할 수 없지만 학년의 자료는 group by가 되어서 정상적으로 작동함.(가능가능~)

select 학년,반,count(학년) from 학교 group by 학년,반  //학년,반별로 그룹지어줌

 

부서별 평균 급여

select deptno,avg(sal) from emp
group by deptno;

select deptno,avg(sal),min(sal),max(sal) from emp
group by deptno;   //부서별 평균급여,최소,최대급여 추출

select deptno,avg(sal)     5 

from emp                        1

where sal>=2000              2

group by deptno             3

having                          4

order by                        6     /이름지정역시 이름을 지정한 부분이후부터 인식가능함.

 

(where와 having의 차이)

where절은 개별 자료자체에서 조건을 주고 걸러줌(따라서 group by된 결과를 where절에서 다시 계산x->avg(sal>=2000이 안됨)

Having절//(group by로)그룹된 함수의 연산결과에 다시 조건을 걸어서 결과를 도출

select deptno,avg(sal),min(sal),max(sal)

from emp
group by deptno
having avg(sal)>=2000     

회사에 급여가 2000이하인 사람들의 부서별 평균연봉을 보여주오

select deptno, avg(sal) from emp where sal<2000 group by deptno

 회사에서 부서별 평균연봉이 3000이상인 부서를 보여주세요

select deptno, avg(sal) from emp group by deptno having avg(sal)>=2000

Q.job별로 인원을 구하세요.

select job, count(ename) from emp group by job

 

select count(decode(lower(job),'salesman',1)), count(decode(lower(job),'clerk',1))
from emp;

Q.입사일 기준으로 인원을 구하세요.(hiredate에서 뽑음

total   1980   1981  1982  1987   (select decode(to_char(hiredate,'yy'),'80',1) from emp )

select count(*) total , count(decode(to_char(hiredate,'yy'),'80',1)) as "1980"
,count(decode(to_char(hiredate,'yy'),'81',1)) as "1981"
,count(decode(to_char(hiredate,'yy'),'82',1)) as "1982"
,count(decode(to_char(hiredate,'yy'),'87',1)) as "1987" from emp

Q.job에 따라 평균 salary(그룹바이해서)

           dept10    dept20     dept30    total   (얘네는 디코드별로)

job        (얘네는 그룹별로)

salesman  

clerk 

manager 

analyst

A.select job,avg(sal),avg(decode(deptno,10,sal)) dept10,avg(decode(deptno,20,sal)) dept20
,avg(decode(deptno,30,sal)) dept30, avg(sal) total from emp group by job

Q.최고 급여가 $10,000 이 넘는 부서에 대해 부서번호와 최고급여를 표시하라.

A.select dept.dname,max(sal) from emp, dept where emp.deptno=dept.deptno group by dept.dname having max(sal)>=1000

 

Join(조인)이란 테이블과 테이블의 자료를 연결하는것을 의미하는데 데이터베이스는 중복을 최소화하고자 하는 목적으로 테이블을 쪼개는데 만일 자료와 자료의 연결이 필요한 경우 조인을 이용해서 테이블간의 자료를 연결하면 된다.!

 

cross join 이란 From절에 테이블을 콤마를 찍어서 연결해서 보여주는데 의미없는 테이블의 연결로 테이블로우X테이블로우의 결과값이다. 

 예) select * from emp, dept;  (emp*dept개수)

Equi Join이란 조인대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼값이 일치되는 행을 연결하여 결과를 생성하는 방법이다.

예)select ename, emp.deptno empno, dept.deptno deptno from emp, dept where emp.deptno=dept.deptno; (조건으로 두테이블의 deptno가 동일한 값으로 설정해줌)

tip.조인을 사용할때는 테이블 컬럼명을 쓸때 혼동이 될 수 있으니 컬럼앞에 테이블명을 써주는 것이 좋다.

Q.이름,직책,부서(deptno가 아닌 accounting으로)를 출력하는 쿼리

select ename,job,dname from emp,dept where emp.deptno=dept.deptno and dept.dname='ACCOUNTING';  

Q.SALESMAN부서의 부서원 이름과 직책 그리고 근무지역, 급여를 출력하세요.

select emp.ename,emp.job,emp.sal,dept.loc from emp, dept
where emp.deptno=dept.deptno
and dept.dname=upper('sales')

Q.근무지역이 시카고있는 사람들의 부서와 이름 직책

select emp.ename,emp.job,emp.sal,dept.loc from emp, dept
where emp.deptno=dept.deptno
and dept.loc=upper('chicago')

Rename

select e.ename,e.job,e.sal,d.loc from emp e, dept d 
where e.deptno=d.deptno
and d.loc=upper('chicago')

 

Q.뉴욕에 근무하는 사원이름과 급여

답)select e.ename,e.sal from emp e, dept d where e.deptno=d.deptno and d.loc='NEW YORK'

Q.직급이 MANAGER인 사원의 이름, 부서명을 출력

답)select ename,dname from emp,dept where emp.deptno=dept.deptno and emp.job='MANAGER'

INNER JOIN(ANSI표준) //=오라클의 Equi Join

select empno,ename,job,sal,dname,loc from emp inner join dept on emp.deptno=dept.deptno;

cross join(ansi표준) //=오라클의 Cross Join

select  empno,ename  from emp cross join dept d ;

 

Non-Equi Join이란 값이 똑같은 조건이 아닌 범위조건 안에 드는 값을 조사하기 위해 where절의 조인조건으로 =연산자와 비교연산자를 사용한다.

Q.급여 등급을 5개로 나누어 놓은 salgrade에서 정보를 얻어 각 사원의 급여 등급을 지정해보라.

A1.select ename,sal,grade from emp, salgrade where sal>=losal and sal<=hisal

A2.select ename,sal,grade from emp, salgrade where sal between losal and hisal

Q.사원테이블과 부서테이블, 급여테이블을 이용하여 사원이름,부서이름,급여등급을 보여주세요.

A.select ename, dname,grade from emp,dept,salgrade where emp.deptno=dept.deptno and sal between losal and hisal;

Q.부서번호별 평균등급

A.select deptno, avg(grade) from emp, salgrade where sal between losal and hisal group by deptno 

Q.성적 등급별 인원체크

A.select grade, count(grade) from emp, salgrade where sal between losal and hisal group by grade

self join이란 하나의 테이블 내에서 조인을 해야만 자료를 얻을 수 있는 경우 말그대로 자기 자신과 조인을 맺는것

예를들어, EMP테이블 안에 아래와 같은 내용이 있다면 홍길동은 매니저의 이름을 찾기 위해 emp테이블의 매니저 사원번호를 이용해 emp테이블안에 사원번호와 비교하여 매니저의 이름을 찾아낸다. 이럴때 쓰는게 바로 Self Join이다.

사원번호  이름    매니저사원번호

123         홍길동      124

124         장발장      125

125         박문수       135

Q.SMITH의 매니저의 이름은?

A.select e1.mgr, e1.ename||'의 매니저 이름은'||e2.ename||'입니다' from emp e1, emp e2 where e1.mgr=e2.empno and e1.ename='SMITH'

Q.매니저가 KING인 사원들의 이름과 직급

A.select e.ename, e.job from emp e, emp m where e.mgr=m.empno and m.ename='KING'

Q.scott의 상사
select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr=e2.empno and e1.ename='SCOTT'

Q.SCOTT과 동일한 근무지에서 근무하는 사원의 이름을 출력하시오

A.select e1.ename,e2.ename from emp e1, emp e2 where e1.deptno=e2.deptno and e1.ename='SCOTT'

Q. king의  부하직원의 부서와 연봉을 출력하세요.

select e1.employee_id, e1.first_name
from employees e1, employees e2
where e1.manager_id = e2.employee_id and
e2.last_name='King'  ????ㅡ.ㅡ;

 

Q.william smith의 매니저는 누구? 그사람의 이름과 부서, 연봉, 직책을 구하세요.

(과제)

select e1.first_name "나는 윌리암" , e2.last_name "나는 윌리암의 매니저" 
from employees e1, employees e2
where e1.manager_id=e2.employee_id and e1.first_name='William' and e1.last_name='Smith'
/

empno, ename, manager_name

7369 smith ford

select e1.empno, e1.ename, e2.ename from emp e1, emp e2 where e1.mgr=e2.empno(+)

(e1.mgr에 manager가 없을 수 있으므로 e2.empno에 (+)를 준다.)

select e1.empno,e1.ename,e2.ename from emp e1 left outer join emp e2 on e1.mgr = e2.empno;

 

 

 

Outer join 2개 이상의 테이블이 조인될 때 어느 한쪽의 테이블에는 해당하는 데이터가 존재하는데 다른 쪽 테이블에는 데이터가 존재하지 않는 경우 그 데이터는 출력되지 않는 문제를 해결하기 위해서 사용되는 조인기법

select e.ename||'의 매니저는'||m.ename||'입니다' from emp e, emp m where e.mgr=m.empno

//emp e의 매니저 번호와 emp의 m의 사원리스트(동일 리스트)를 참고하여 매니저번호를 이용해 동일테이블의

사원리스트를 뽑아서 보여주는데 위와같은 경우는 조건에 일치하는 값만을 검색해 주기때문에 비록null값이라도 보여주고자 하는 값을 다보여주려면! e에서 m을 참고하는 것이니 e에는 없는 리스트를 m(+)해주어

조인해준다(Rigt Join)->

select e.ename||'의 매니저는'||m.ename||'입니다' from emp e, emp m where e.mgr=m.empno(+);