07월 23일 화 | SQL활용 04 - SQL View와 Index Ⅰ

 

SQL View와 Index

Group 함수

group by는 집계함수와 많이 사용한다.

집계함수는 :  sum(), avg(), max(), min(), count()

 

문제 1) 부서코드별 급여정보 조회하기

select ename, sal, deptno from emp group by deptno; select deptno from emp group by deptno order by deptno; select ename from emp group by deptno order by deptno; --에러 group지은 값만 가능

 

예시 1) 

select deptno, sum(sal), avg(sal), min(sal), max(sal), count(sal) from emp --1) group by deptno --2) order by deptno; --3)

 

쿼리문을 접할 때는 문제, 요구분석을 잘해야한다.

 

select deptno, sum(sal), round(avg(sal),1), min(sal), max(sal), count(sal) from emp group by deptno order by deptno;

 

 

문제 2) 부서코드별 급여합계를 구하되 그 합계값이 1500이상만 조회하시오.

select deptno, sum(sal) from emp group by deptno having sum(sal) >= 1500;

 

having

view

indext

 

문제 3) 급여가 300이상 데이터 중에서 부서코드별 급여평균을 구해서 급여순 조회.

select deptno, avg(sal) from emp where sal>=300 group by deptno order by avg(sal);

 

 

문제 4) 급여가 300이상 데이터 중에서 부서코드별 급여평균이 400이상인 것을 급여순 조회.

select deptno, avg(sal) from emp where sal>=300 group by deptno having avg(sal)>=400 order by avg(sal);

select deptno, avg(sal) as 급여평균 from emp where sal>=300 group by deptno having avg(sal)>=400 order by 급여평균;

 

 

문제 5) 부서별 급여총액이 가장 높은 총액과 가장 낮은 급여총액을 반올림해서 소수점 1자리 까지만 출력

select deptno,  round(max(sal+nvl(comm,0)),1) as 최고총액 , round(min(sal+nvl(comm,0)),1) as 최저총액 , round(avg(sal+nvl(comm,0)),1) as 평균금액 from emp group by deptno;

 

문제 6) hiredate 칼럼을 사용하여 월별로 입사한 인원수를 구하시오

select to_char(hiredate, 'mm') --문자열변환  from emp;  select to_char(hiredate, 'mm') || '월'  from emp;  select to_char(hiredate, 'mm'), count(*) from emp  group by to_char(hiredate, 'mm')  order by to_char(hiredate,'mm');

 

 

서브쿼리

문제 1) emp 테이블에서 월급을 가장 많이 받는 사원정보를 조회하시오.

select ename, sal from emp where sal = (select max(sal) from emp);

 

문제 2) 평균급여보다 많은 급여를 받는 직원의 이름, 부서명, 급여를 조회하시오.

select ename, job, sal from emp where sal > (select avg(sal) from emp);

 

문제 3) 부서코드 10의 최고급여보다 더 많은 급여를 받는 직원목록을 조회하시오.

select ename, sal from emp where sal > (select max(sal) from emp where deptno = 10);

 

 

문제 4) 손흥민과 같은 입사일에 입사한 사람들 중에서 박지성보다 급여를 적게 받는 사람의 이름, 급여, 입사일을 조회하시오.

select ename, sal, hiredate from emp where hiredate = (select hiredate from emp where ename ='손흥민') and sal < (select sal from emp where ename='박지성');

입사일이 같은 사람이 없기 때문에 결과 데이터가 없게 되었다.

 

 

 

 

View

뷰는 논리 테이블로서 사용자에게(생성 관점 아닌 사용 관점에서) 테이블과 동일하다. 아래 그림에서 `테이블A'와 `테이블B'는 물리 테이블을 의미하고, `뷰C'는 두 개의 테이블을 이용하여 생성한 뷰를 의미한다.

 

 

 

뷰는 `테이블A'와 같은 하나의 물리 테이블로부터 생성 가능하며, 다수의 테이블 또는 다른 뷰를 이용해 만들 수 있다. 위 그림의 뷰와 같은 결과를 만들기 위해 다음 장에서 배울 조인(Join) 기능을 활용할 수 있으나, 뷰가 만들어져 있다면 사용자는 조인 없이 하나의 테이블을 대상으로 하는 단순한 질의어를 사용할 수 있다.

 

1) 정의 

뷰는 테이블에 대한 가상의 테이블로써 테이블처럼 직접 데이터를 소유하지 않고 검색 시에 이용할 수 있도록 정보를 담고 있는 객체 테이블 정보의 부분집합

 

2) 사용목적

- 테이블에 대한 보안기능을 설정해야 하는 경우

- 복잡하며 자주 사용하는 질의 SQL문을 보다 쉽고 간단하게 사용해야하는 경우

 

 

 

물리적 테이블

- 사용자가 create에 의해 생성된 실제 존재하는 테이블

 

논리적 테이블

- 사용자가 SQL문에 의해 가공된 테이블

 

 

3) 뷰 생성 권한부여

뷰도 생성할 수 있는 권한이 정해져 있다.

뷰를 생성할 수 있는 권한을 부여하려면

cmd > sqlplus / as sysdba

SQL>grant create view to java0514;

 

4) 뷰 생성 형식

create or replace view 뷰 이름 

as [sql문]

-> replace: 이미 존재하는 뷰의 내용을 수정함.

 

--테이블 목록 확인

select * from tab;

뷰가 생성 된 것을 알 수 있다.

 

 

5) 뷰 생성하기

create view 뷰이름

 

6) 뷰 삭제하기

drop view 뷰이름

 

 

Join

--보여줄때 부서번호가 아닌 부서명으로 보여줘야한다.
--그러므로 조인이 필요한 것이다.

 

select emp.ename, emp.deptno, dept.* from emp join dept on emp.deptno = dept.deptno;

 

이렇게 했더니 emp, dept 두개의 테이블이 하나인 것처럼 나왔다.

 

-- 테이블 별칭을 주었을 때  select AA.ename, AA.deptno, BB.* from emp AA join dept BB on AA.deptno = BB.deptno;

 

create or replace view test2_view as      select AA.ename, AA.deptno, BB.dname, BB.loc     from emp AA join dept BB      on AA.deptno = BB.deptno;

이렇게 뷰를 생성하고 조회하면

 

 

select emp.deptno, emp.ename, emp.sal, dept.dname from emp join dept on emp.deptno = dept.deptno;

 

 

select ename, sal, deptno, dname from (select emp.ename, emp.sal, emp.deptno, dept.dname         from emp join dept         on emp.deptno = dept.deptno         ) AA where AA.deptno =10;