DB 연습하기 - 09

오늘·2021년 4월 28일
오라클
0

DB

목록 보기
9/14

조인 연습하기

테이블 삭제 및 재생성

-- 부서 테이블 제거하기
DROP TABLE EMP;
-- 사원 테이블 제거하기
DROP TABLE DEPT;
-- 급여 테이블 제거하기
DROP TABLE SALGRADE;
-- 부서 테이블 제거하기
DROP TABLE EMPLOYEE;
select * from employee;
-- 사원 테이블 제거하기
DROP TABLE DEPARTMENT;
-- 급여 테이블 제거하기
DROP TABLE SALGRADE;

-- 부서 테이블 생성하기
CREATE TABLE DEPT(
DEPTNO NUMBER(2) PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13) ) ;

-- 사원 테이블 생성하기
CREATE TABLE EMP( 
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

-- 급여 테이블 생성하기
CREATE TABLE SALGRADE(
GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );

-- 사원 테이블에 샘플 데이터 추가하기
INSERT INTO DEPT VALUES(10, '경리부', '서울');
INSERT INTO DEPT VALUES(20, '인사부', '인천');
INSERT INTO DEPT VALUES(30, '영업부', '용인'); 
INSERT INTO DEPT VALUES(40, '전산부', '수원');

-- 부서 테이블에 샘플 데이터 추가하기
INSERT INTO EMP VALUES(1001, '김사랑', '사원', 1013, to_date('2007-03-01','yyyy-mm-dd'), 300, NULL, 20);
INSERT INTO EMP VALUES(1002, '한예슬', '대리', 1005, to_date('2007-04-02','yyyy-mm-dd'), 250, 80, 30);
INSERT INTO EMP VALUES(1003, '오지호', '과장', 1005, to_date('2005-02-10','yyyy-mm-dd'), 500, 100, 30);
INSERT INTO EMP VALUES(1004, '이병헌', '부장', 1008, to_date('2003-09-02','yyyy-mm-dd'), 600, NULL, 20);
INSERT INTO EMP VALUES(1005, '신동협', '과장', 1005, to_date('2005-04-07','yyyy-mm-dd'), 450, 200, 30);
INSERT INTO EMP VALUES(1006, '장동건', '부장', 1008, to_date('2003-10-09','yyyy-mm-dd'), 480, NULL, 30);
INSERT INTO EMP VALUES(1007, '이문세', '부장', 1008, to_date('2004-01-08','yyyy-mm-dd'), 520, NULL, 10);
INSERT INTO EMP VALUES(1008, '감우성', '차장', 1003, to_date('2004-03-08','yyyy-mm-dd'), 500, 0, 30);
INSERT INTO EMP VALUES(1009, '안성기', '사장', NULL, to_date('1996-10-04','yyyy-mm-dd'),1000, NULL, 20);
INSERT INTO EMP VALUES(1010, '이병헌', '과장', 1003, to_date('2005-04-07','yyyy-mm-dd'), 500, NULL, 10);
INSERT INTO EMP VALUES(1011, '조향기', '사원', 1007, to_date('2007-03-01','yyyy-mm-dd'), 280, NULL, 30);
INSERT INTO EMP VALUES(1012, '강혜정', '사원', 1006, to_date('2007-08-09','yyyy-mm-dd'), 300, NULL, 20);
INSERT INTO EMP VALUES(1013, '박중훈', '부장', 1003, to_date('2002-10-09','yyyy-mm-dd'), 560, NULL, 20);
INSERT INTO EMP VALUES(1014, '조인성', '사원', 1006, to_date('2007-11-09','yyyy-mm-dd'), 250, NULL, 10);

-- 급여 테이블에 샘플 데이터 추가하기
INSERT INTO SALGRADE VALUES (1, 700,1200);
INSERT INTO SALGRADE VALUES (2, 1201,1400);
INSERT INTO SALGRADE VALUES (3, 1401,2000);
INSERT INTO SALGRADE VALUES (4, 2001,3000);
INSERT INTO SALGRADE VALUES (5, 3001,9999);

COMMIT;

조건에 맞게 출력하시오

-- 1번 경리부에서 근무하는 사원의 이름과 입사일, 부서명 출력하기
select ename, dname, HIREDATE from emp e, dept d
	where d.deptno = e.deptno and e.deptno=10;
select ename, dname, HIREDATE from emp e, dept d
	where d.deptno = e.deptno and d.dname='경리부';

-- 2번 ansi join 을 사용하여 인천에서 근무하는 사원 이름과 급여 출력하기
select ename, sal from emp e inner join dept d
	on e.deptno=d.deptno where loc='인천';

-- 3번 사원 테이블과 부서 테이블을 조인하여 사원이름과 부서번호, 부서명을 출력
select e.ename, e.deptno, d.dname from emp e, dept d
	where d.deptno=e.deptno;

-- 4번 직급이 과장인 사원이 이름, 부서명을 출력
select e.ename, d.dname from emp e inner join dept d
	on e.deptno=d.deptno where e.job='과장';
select e.ename, d.dname from emp e, dept d
	where e.deptno = d.deptno and e.job='과장';

-- 5번 직속상관이 감우성인 사원들의 이름과 직급을 출력(오늘 정리할 서브쿼리의 모습)
select ename as 이름, job as 직급 from emp where mgr=
	(select empno from emp where ename='감우성');
select e1.ename 이름, e1.job 직급 from emp e1, emp e2
	where e1.mgr = e2.empno and e2.ename = '감우성';

-- 6번 감우성과 동일한 근무지에서 근무하는 사원의 이름을 출력
select e2.ename from emp e1, emp e2
    where e2.ename != '감우성' and e1.ename = '감우성'
    	  and e1.deptno = e2.deptno;
select e2.ename, e1.ename from emp e1, emp e2
	where e1.deptno = e2.deptno and e1.ename='감우성'
    	      and e2.ename <> '감우성';

서브쿼리

하나의 select 문장의 절 안에 포함된 또 하나의 select 문장

서브 쿼리를 포함하고 있는 쿼리문을 메인쿼리, 포함된 쿼리를 서브 쿼리라 부른다

규칙

  • 서브쿼리 안에 서브 쿼리가 또 들어갈 수도 있다. 이것을 네스팅 이라 하며, 메모리가 허용하는 한 무제한으로 중첩이 가능하다.

  • 메인 쿼리에서 서브 쿼리의 결과값을 조건으로 사용할 때, (some, any 또는 all과 같은 연산자를 사용하지 않는 경우) 서브 쿼리에서는 '하나'의 레코드 값만 리턴해야 한다. 그러므로 대부분의 경우 서브 쿼리에는 group by, having 문을 사용할 수가 없다.

  • 서브 쿼리에서 order by 문은 top 연산자와 함께 있을 때만 사용이 가능하다.

단일 행 서브 쿼리

수행 결과가 오직 하나의 로우(행, row)만을 반환하는 서브 쿼리를 갖는 것을 말한다.

-- '이문세'의 부서명 알아내기
select * from emp where ename = '이문세';
select * from dept where deptno='10';
-- 서브쿼리 사용해 알아내기
select dname from dept where deptno =
	(select deptno from emp where ename='이문세');


-- 김사랑의 근무지 알아보기
select * from emp where ename = '김사랑';
select * from dept where deptno='20';
-- 서브쿼리를 사용해 알아내기
select loc from dept where deptno =
	(select deptno from emp where ename='김사랑');


-- 사원들의 평균 급여보다 더 많은 급여를 받는 사원명과 급여를 출력하세요
select avg(sal) from emp;   -- 평균 급여구하는 쿼리
-- 서브쿼리로 조건에 맞게 출력하기
select ename, sal from emp where sal > (select avg(sal) from emp);


-- 부서번호 30에 소속된 사원들 중 최고급여 사원을 알고싶다면
select max(sal) from emp where deptno='30';
-- 전체 사원들 중 위 조건보다 더 많이 받는 사람들을 구하고 싶다면
select ename, sal, deptno from emp
	where sal > (select max(sal) from emp where deptno='30');

다중 행 서브 쿼리

서브 쿼리에서 반환되는 결과가 하나 이상의 행일 때 사용하는 것. 반드시 다중 행 연산자와 함께 사용해야 한다

다중 행 연산자의 종류
1. in = 하나라도 일치하면 참
2. any, some = 하나 이상이 일치하면 참
3. all = 모든 값이 일치하면 참
4. exist = 만족하는 값이 하나라도 존재하면 참

in

-- 급여가 500을 초과하는 사원과 같은 부서에 근무하는 사원 정보를 출력하시오
select ename, sal, deptno from emp where sal > 500;
-- distinct : 데이터 중복을 제거
select ename, sal, deptno
	from emp where deptno
    	in (select deptno from emp where sal > 500 ) ;

any, some

-- 20번 부서의 최소 급여보다 많은 급여를 받는 사원 출력하기
select ename, sal, deptno from emp
	where sal > (select min(sal) from emp where deptno=20);
    
-- any : 하나라도 일치하면 되기 때문에
-- 	 30번 부서의 사람들 중 한명이라도 본인보다 급여가 작다면 참이 된다.
select ename, sal, deptno from emp
	where sal > any (select sal from emp where deptno=20);
select ename, sal, deptno from emp
	where sal > some (select sal from emp where deptno=20);

all

-- 30번 부서의 최대 급여보다 많이 받는 사람들만 출력
-- all : 값 모두가 일치해야 하기 때문에
--	 30번 부서의 모든 사람보다 급여가 많아야만 참
select ename, sal, deptno from emp
	where sal > all (select sal from emp where deptno='30');

exists

exists : 서브 쿼리의 결과 값이 참이 나오기만 하면 바로 메인 쿼리의 결과 값을 리턴, in과 비슷하나 속도가 훨씬 빠르고 좀 다르다

-- in의 경우 dept와 emp 가 deptno로 겹치는 부분만 출력
select * from dept where deptno in (select deptno from emp); 
-- exists의 경우 서브쿼리가 '참'이기만 하면 dept가 모두 출력된다
select * from dept where exists (select * from emp where deptno=10);
-- 만약 서브쿼리가 '거짓'이라면 출력되는 값은 없다
select * from dept where exists (select * from emp where deptno=90);

서브쿼리로 테이블 복사하기

-- 서브쿼리로 테이블 생성하기
create table emp02 as select * from emp;
select * from emp02;
-- emp03 테이블은 empno, ename, sal, deptno, dname만 넣고 싶다면?
create table emp03 as
    select empno, ename, sal, e.deptno, dname from emp e, dept d
    where e.deptno = d.deptno;
-- 확인하기
select * from emp03;



-- deptno가 10인 부서만 테이블로 만들기
create table emp04 as
    select * from emp where deptno=10;
select * from emp04;



-- 테이블 구조만 복사하기
-- 조건이 만족하지 않으면 값 없이 비워져서 구조만 복사된다.
create table emp05 as
    select * from emp where deptno=100;
    
-- 값 없는지 확인
select * from emp05;
-- 구조 확인
desc emp05;

서브쿼리로 데이터 삽입하기

형식(구문)
구문 : insert into 테이블명 ( 컬럼명 ) (서브쿼리)

-- 지역이 서울인 emp의 자료 출력
select * from emp e, dept d where e.deptno=d.deptno and d.loc='서울';
-- emp05 테이블에 테이터 삽입하기
-- insert into 만 한다 values는 명시 안해야 함
insert into emp05 (empno, ename, job, mgr, sal, comm, deptno)
    (select e.empno, e.ename, e.job, e.mgr, e.sal, e.comm, e.deptno
     from emp e, dept d where e.deptno=d.deptno and d.loc='서울');
-- 값이 제대로 들어갔나 확인
select * from emp05;

-- 조건없이 (delete 테이블명) 하면 내용만 모두 삭제된다.
delete emp05;
-- 급여를 평균 급여보다 많이 받는 사람만 emp05 테이블로 삽입하기
insert into emp05 (empno, ename, job, mgr, sal, comm, deptno)
    (select e.empno, e.ename, e.job, e.mgr, e.sal, e.comm, e.deptno
     from emp e where e.sal > (select avg(sal) from emp ));
-- 값이 제대로 들어갔는지 확인
select * from emp05;

서브쿼리로 Update하기

형식(구문)
update 테이블명 set 컬럼명 = (서브쿼리문) where 조건절

-- 20번 부서의 지역명(인천)을 수원으로 변경하고 싶다
create table dept01 as select * from dept; -- dept01 테이블 생성
update dept01 set loc =
	(select loc from dept where deptno=40)where deptno = 20;
    
-- 확인하기
select * from dept01;

서브쿼리로 delete하기

-- 영업부인 사람 삭제하기
select * from dept01 where dname = '영업부';
delete from dept01 where deptno =
	(select deptno from dept01 where dname = '영업부') ;
    
-- 확인하기
select * from dept01;
profile
오늘
이전 포스트

DB 연습하기 - 08

다음 포스트

DB 연습하기 - 10

0개의 댓글