연습하기
-- 1. "영업부"에서 근무하는 모든 사원의 이름과 급여를 출력하세요
-- 서브쿼리 사용
select ename, sal from emp where deptno=(select deptno from dept where dname='영업부');
-- 이퀄 조인 사용
select ename,sal from emp e,dept d where e.deptno=d.deptno and d.dname='영업부';
-- 2. 서브쿼리를 사용하여 emp06에 emp테이블의 모든 데이터를 추가합니다.
-- 테이블 생성과 동시에 값 넣어주기
create table emp06 as select * from emp;
select * from emp06; -- 확인
drop table emp06; -- 테이블 삭제
-- 빈 테이블을 먼저 생성해준 후 insert into 로 값 넣어주기
create table emp06 as select * from emp where empno=100;
insert into emp06 select * from emp;
-- 3. emp06테이블에 저장된 사원 정보 중
-- 과장들의 최소 급여보다 급여를 많이 받는 사원의 이름과 급여와 직급을 출력해라
-- 단, 과장은 출력하지 않는다
select ename, sal, job from emp06
where sal > ( select min(sal) from emp where job='과장') and job != '과장';
-- any 사용해보기
select ename, sal, job from emp06
where sal > any( select sal from emp where job='과장') and job != '과장';
-- 4. emp06 테이블에 저장된 사원 정보 중
-- 인천에 위치한 부서 소속 사원들의 급여만 100 인상하는 SQL문을 작성하시오
update emp06 set sal = sal+100
where deptno=(select deptno from dept where loc='인천');
-- 5. emp06테이블에서 경리부에 소속된 사원들만 삭제하는 SQL문을 작성하시오
select * from emp06;
select * from dept;
delete from emp06 where deptno=(select deptno from dept where dname='경리부');
-- 6. '이문세'와 같은 부서에 근무하는 사원의 이름과 부서번호를 출력하시오
select ename, deptno from emp where deptno=(select deptno from emp where ename='이문세');
-- 7. '이문세'와 동일한 직급을 가진 사원을 출력하는 SQL문을 완성하시오.
select * from emp where job=(select job from emp where ename='이문세');
-- 8. '이문세'의 급여와 동일하거나 더 많이 받는 사원명과 급여을 출력하시오
-- 서브쿼리
select ename, sal from emp where sal >= (select sal from emp where ename='이문세');
-- self 조인 사용
select e2.ename, e2.sal from emp e1, emp e2 where e1.ename='이문세' and e1.sal <= e2.sal;
-- 9. '인천'에서 근무하는 사원의 이름, 부서번호를 출력하시오.
select ename, deptno from emp where deptno=(select deptno from dept where loc='인천');
-- 10. 직속상관이 감우성인 사원의 이름과 급여를 출력하는 SQL문을 완성하시오
select * from emp;
select ename, sal from emp where mgr = (select empno from emp where ename='감우성');
-- 11. 부서별로 가장 급여를 많이 받는 사원의 정보(사원번호, 사원이름, 급여, 부서번호)를 출력하는 SQL문을 완성하시오.
select empno, ename, sal, deptno from emp e1
where sal in (select max(sal) from emp e2 where e1.deptno=e2.deptno);
-- 12. 직급(JOB)이 과장인 사원이 속한 부서의 부서번호와 부서명, 지역을 출력하시오
select deptno, dname, loc from dept
where deptno in (select deptno from emp where job='과장');
-- 13. 과장보다 급여를 많이 받은 사원들의 이름과 급여와 직급을 출력하되 과장은 출력하지 않는 SQL문을 완성하시오
select ename, job from emp
where sal > all (select sal from emp where job = '과장') and job != '과장'; 뷰(View)
- 실제로 데이터를 저장하고 있지는 않지만 DML 작업이 가능한, 물리적인 테이블에 근거한 '논리적인 가상' 테이블이다
- 기본 테이블에서 파생된 객체로서 기본 테이블에 대한 하나의 쿼리문
- 뷰를 통해 실제 테이블에 저장된 데이터를 볼 수 있도록 한다
-- 뷰 생성하기
create view v_emp as select * from emp;
-- 뷰 삭제하기
drop view v_emp;굳이 기능으로 분류해보자면 아래와 같다 한다
-
단순뷰
: 단일 테이블에 필요한 컬럼을 나열한 것이다.
: 조인, 함수, GROUP BY, UNION 등을 사용하지 않고 만든 것.
: 이러한 단순 뷰는 SELECT, INSERT, UPDATE, DELETE 를 자유롭게 사용가능하다 -
복합 뷰
: 조인, 함수, GROUP BY, UNION 등을 사용하여 뷰를 생성한 것.
: 함수 등을 사용할 경우 컬럼 별칭은 꼭 부여해야 한다. (ex, AS hiredate)
: 복합 뷰는 SELECT는 가능하지만 INSERT, UPDATE, DELETE는 상황에 따라서 안될 수도
필요성
- 빈번히 join 하거나 서브쿼리로 두 개 이상의 테이블을 참조하여 검색할 때, 테이블을 가상으로 합쳐두고 찾으면 구문이 훨씬 간단해진다.
- DB 사용자가 원본 테이블을 직접 사용하게 되면 보안의 문제나 데이터 손상이 있을 수 있기 때문에 그것을 예방한다. (접근 권한을 제한할 수 있기 때문에 가능)
- 물리적 DB는 컬럼을 최소화하고, 뷰를 이용해 필요한 컬럼을 논리적 테이블로 만든다.
-> 프로그램에서 DB를 사용할 때 뷰테이블명을 이용해서 값을 입력하고(insert into), 검색하고(select) 등등..
뷰 생성
-- 테이블이 섞어서 뷰 생성하기
create view v_emp_dept as
select empno, ename, job, mgr, sal, e.deptno, d.dname, d.loc
from emp e, dept d where e.deptno = d.deptno;
-- 생성한 뷰 확인하기
select * from v_emp_dept;
-- 영업부인 사람의 이름을 구하고 싶다
select ename from v_emp_dept where dname='영업부';
-- 인천에 사는 사람들의 이름과 급여보기
select ename, sal from v_emp_dept where loc='인천';
insert 해보기
뷰를 통해 값을 삽입하면 뷰는 물론이고 물리적 테이블 안에도 값이 들어간다.
(다른 제한을 걸어놓지 않았다면 말이다)
-- deptno 30 인 사람들의 enpno, ename, deptno 만 view로 만드세요
create view emp_v_30 as
select empno, ename, deptno from emp where deptno=30;
-- 확인하기
select * from emp_v_30;
-- 값 삽입해보기
insert into emp_v_30 values (1111, '뷰입력', 30);
select * from emp_v_30;
select * from emp;update 해보기
뷰를 통해 값을 변경시, 뷰는 물론이고 물리적 테이블 안에도 값이 들어간다.
(다른 제한을 걸어놓지 않았다면 말이다)
update emp_v_30 set ename = '뷰수정' where empno=1111;
select * from emp_v_30;
select * from emp;delete 해보기
뷰를 통해 값을 변경시, 뷰는 물론이고 물리적 테이블 안에서도 값이 삭제된다.
(다른 제한을 걸어놓지 않았다면 말이다)
delete from emp_v_30 where empno=1111;
select * from emp_v_30;
select * from emp;뷰 수정(변경) 하기
create or replace
없으면 만들고, 이미 같은 이름의 view가 생성되어 있었다면 덮어쓰기해서 새로 만들도록 해준다.
-- 뷰 생성
create view emp_view_30 as
select * from emp where deptno=30;
-- 뷰 구조 보기
desc emp_view_30;
-- view를 만드는데 같은 이름으로 view가 이미 있다면 새로 만들어라
create or replace view emp_view_30 as
select * from emp where deptno=30;내용없이 뷰 생성하기
일반적으로 기본 테이블이 존재한다는 가정 하에서 뷰를 만들지만, 기본 테이블이 존재하지 않는 경우에도 뷰를 생성해야 할 경우가 있다. 이럴 경우에 사용하는 것은 FORCE 옵션이다.
생성은 된다. 이외의 것들이 안됨.
-- 없는 테이블이다
select * from employees;
-- 실행결과
-- 경고: 컴파일 오류와 함께 뷰가 생성되었습니다
create or replace force view employees_view as
select empno, ename, deptno from employees
where deptno=30;
-- 모습 확인
select view_name, text from user_views;컬럼 값 변경을 못하게
with check option
뷰를 정의하는 서브 쿼리문에 where절을 추가해 기본 테이블에서 정보가 추출되는 조건을 제시하게 되는데, 여기에 with check option을 기술하면 조건 제시를 위해 사용한 컬럼값이 뷰를 통해 변경되지 못한다.
-- emp 테이블을 복사해 테이블 생성하기
create table emp_copy as select * from emp;
-- 뷰 생성하기
create or replace view emp_view_30 as
select * from emp_copy where deptno=30;
-- 급여가 500 이상인 것들의 deptno를 20으로 update하세요
select * from emp_view_30;
update emp_view_30 set deptno=30 where sal <= 500;
-- 이휴 뷰를 확인해보면
-- update하면서 sal <= 500 인 것들의 deptno를 20으로 바꿨기 때문에
-- 원래 뷰의 조건인 deptno=30 에 해당하지 않아
-- 원래의 뷰와 달라진 것을 확인할 수 있다.
select * from emp_view_30;
drop table emp_copy;
create table emp_copy as select * from emp;
-- 위와 같이 값이 막 변경되는 것을 막기 위하여 with check option을 붙여준다
-- where 에 조건을 서술한 뒤 with check option을 붙여주면 된다.
create or replace view emp_view_30 as
select * from emp_copy where deptno=30 with check option;
-- 이 뷰에 값이 변경될 수도 있는 update를 하면
-- 오류가 생겼다는 말과 함께 실행되지 않는다.
update emp_view_30 set deptno=20 where mgr = 1003;