[SQL-3] SQL중급 1

Mar 31, 2024

Contents

PART 3「중급」 SQL 실력 다지기 ■ 출력되는 행 제한하기 조인문법의 종류 2가지 ■ 여러 테이블 데이터 조인하기 ■ 집합 연산자 복습 ■ 서브쿼리(Sub-query) 1 ■ DML / DCL 실습하기 ■ 서브쿼리(Sub-query) 2

PART 3「중급」 SQL 실력 다지기

실습 전 필요한 데이터

telecom_table 생성.txt

0.4KB

create table bonus.txt

복습문제

Q1.

부서번호, 부서번호별 토탈월급을 출력하시오.(세로출력)


A. select deptno, sum(sal)
    from emp
    group by deptno;

Q2.

부서위치, 부서위치별 토탈월급을 출력하는데 맨 아래에 전체 토탈월급을 다음과 같이 출력하시오.


A. select d.loc, sum(e.sal)
    from emp e, dept d
    where e.deptno = d.deptno
    group by rollup(d.loc);

#보스턴이 출력되지 않고 있음.. => 목차 060번 outer 조인에서 공부할 것

Q3.

이름에 S자를 포함하고 있는 사원들의 이름과 월급과 부서위치를 출력하시오.


A. select e.ename, e.sal, d.loc
    from emp e, dept d
    where e.deptno = d.deptno
    AND e.ename like '%S%';

Q4.

emp와 dept와 salgrade를 조인해서 결과를 출력하는데, CHICAGO에서 근무하는 사원들의 이름과 월급과 부서위치와 부서명(dname)과 grade(부서등급)을 출력하시오.


A. select e.ename, e.sal, d.loc, d.dname, s.grade
    from emp e, dept d, salgrade s
    where e.deptno = d.deptno
    and e.sal between s.losal and s.hisal
    and d.loc = 'CHICAGO';

Q5.

부서위치, 이름, 월급, 월급의 순위를 출력하는데, 부서위치별로 각각 월급의 순위가 출력되게 하시오.


A. select d.loc, e.ename, e.sal, 
    dense_rank() over (partition by d.loc order by e.sal desc) 순위
    from emp e, dept d
    where e.deptno = d.deptno;

■ 출력되는 행 제한하기

056 출력되는 행 제한하기 1(ROWNUM)

📖

출력되는 행 제한하기 1(ROWNUM)

주로 사용하는 경우

어떤 큰 테이블의 일부만 보고싶을 때 사용


select * 
	from emp 
	where rownum < 4;  #맨 위에 3줄만 보겠다는 것

# 데이터의 양이 많을 때!
## where절 없이 select 하게 되면, db에 부하를 주게 됨

특징(주의)

앞에 있는 번호들을 전부 봐야 특정 번호의 rownum 데이터를 볼 수 있음


select ename, sal
    from emp
    where rownum = 1; #출력됨
    
select ename, sal
    from emp
    where rownum = 2; #출력되지 않음 

즉 순서대로 출력되게 해야 볼 수 있음 

select ename, sal
    from emp
    where rownum <= 2; #이렇게 수정해야함

특정 번호의 rownum데이터만 출력하고 싶으면 별칭 사용!


select 번호, ename, sal    
    from (select rownum 번호, ename, sal
            from emp
             where job = 'SALESMAN')
    where 번호 = 2;

#만약

select rouwnum, ename, sal    
    from (select rownum, ename, sal
            from emp
             where job = 'SALESMAN')
    where rownum = 2;

#으로 출력하면 from 절 밖의 rownum으로 인식 되기 때문에
##from절 안의 rownum을 가져오기 위해 확실하게 별칭 사용

문제풀며 익히기

Q1.

통신사가 kt인 학생중에서 나이가 2번째로 많은 학생을 출력하시오.


ERROR. select row_number() over (order by age desc) 번호, ename, age, telecom
		from emp18
		where lower(telecom)='kt' and 번호 = 2;

	!!ERROR!!
	실행 순서에 의해 WHERE절에 번호 = 2는 부적합 식별자 에러 발생

A. select 번호, ename, age, telecom
		from ( select row_number() over (order by age desc) 번호,
					ename, age, telecom
						from emp18
						where lower(telecom)='kt' )
		where 번호 = 2;

#서브쿼리의 sql결과를 memory에 올린 후 실행되게 해야하기 때문에
##in line view사용

Q2.

사원테이블에서 월급을 5번째로 많이 받는 사원의 이름과 월급을 출력하시오.


select 번호, ename, sal        
from (select row_number() over(order by sal desc) 번호, ename, sal
    from emp)
    where 번호 = 5;

Q3.

직업이 SALESMAN 인 사원들의 이름과 월급을 출력하는데, 출력되는 결과에서 2번째 데이터만 출력하시오.


A. select 번호, ename, sal    
    from (select rownum 번호, ename, sal
            from emp
             where job = 'SALESMAN')
    where 번호 = 2;

Q4.

rownum을 이용해서 sql을 작성하시오

우리반 테이블에서 2번째 행의 학생의 이름과 나이를 출력하시오.


A. select ename, age
    from (select rownum 번호, ename, age
        from emp18)
    where 번호 = 2;

057 출력되는 행 제한하기 2(Simple TOP-n Queries)

📖

Simple TOP-n Queries 정렬된 결과에서 상위 몇 개의 데이터를 가져오는 방법


order by [컬럼명] asc/desc fetch first 숫자 rows only

예제)

사원 테이블에서 이름과 월급을 출력하는데, 월급이 높은 사원부터 출력하고. 출력된 결과에서 2번째 행까지 출력하시오.


select ename, sal
	from emp
	order by sal desc fetch first 2 rows only;

문제풀며 익히기

Q1.

부서번호가 20번인 사원들의 이름과 입사일을 출력하는데, 최근에 입사한 사원부터 출력하고 위의 3줄의 행만 출력하시오.


A. select ename, hiredate
    from emp
    where deptno = 20
    order by hiredate desc fetch first 3 rows only;

조인문법의 종류 2가지

오라클 조인의 종류 4가지

equi join (대표적)

: 조인하려는 두 테이블 사이에 공통된 컬럼으로 이퀄(=) 조인하는 조인문법

즉, 연결고리 조건이 이퀄(=) 조건인 경우 사용

non equi join

: 조인하려는 두 테이블 사이에 공통된 컬럼이 없어서 이퀄(=)이 아닌 조건으로 조인하는 조인문법 ⇒ 어느 DBMS에서도 다 되는 문법

outer join (대표적)

: equi join 만으로는 조인된 결과를 볼 수 없을 때 , 조인 안되는 결과도 볼때 사용하는 조인 문법. 아우터 조인 사인으로 (+) 를 사용함

⇒ outer join sign(+)은 결과가 모자라게 나오는 컬럼 쪽에 붙여야함


where e.detpno (+) = d.detpno

#enmae을 가져온 emp as e 테이블이 모자라게 출력되기 때문에 (+)를 e.deptno에 붙임

self join

: 자기 자신의 테이블과 조인을 해서 조인하는 문법

1999 ANSI (America national Standard Institute) 조인 문법

오라클 조인법으로는 볼 수 없는 결과를 볼 때 사용

오라클의 아우터 조인은 양쪽에 아우터 사인을 사용할 수 없음

따라서, 아우터 조인 사인을 양쪽에 붙이고 싶을 때 1999 ANSI문법을 사용함

판교에 있는 게임회사들 같은 경우 MSSQL을 많이 사용함

MS사에서 만든 DB 소프트웨어인 MSSQL은 기본적으로 1999 ANSI 문법을 지원하고 있음. 그런데, 오라클 문법도 지원됨.

종류

ON절을 사용한 JOIN 문법
USING절을 사용한 JOIN 문법
NATURAL JOIN을 사용한 JOIN문법
LEFT/RIGHT/FULL OUTER JOIN 문법

■ 여러 테이블 데이터 조인하기

058 여러 테이블의 데이터를 조인해서 출력하기 1(EQUI JOIN)

📖

JOIN 이란? 여러개의 테이블의 컬럼들의 결과를 하나로 모아서 출력해주는 SQL문법


1.
select [출력할 컬럼명]
	from [테이블명1], [테이블명2]
	where [테이블명1].[연결되는 컬럼명] = [테이블명2].[연결되는 컬럼명];

2. 테이블 별칭을 쓰게 되면 간결하게 조인 문장을 작성할 수 있음. 
	테이블명이 길게 되면 코딩이 길어지게 되기 때문

		select [출력할 컬럼명]
			from [테이블명1] as [테이블 별칭1], [테이블명2] as [테이블 별칭2]
			where [테이블 별칭1].[연결되는 컬럼명] = [테이블 별칭2].[연결되는 컬럼명];

💡

오라클 조인의 종류 4가지

equi join

: 조인하려는 두 테이블 사이에 공통된 컬럼으로 이퀄(=) 조인하는 조인문법

non equi join

: 조인하려는 두 테이블 사이에 공통된 컬럼이 없어서 이퀄(=)이 아닌 조건으로 조인하는 조인문법 ⇒ 어느 DBMS에서도 다 되는 문법

outer join

: 조인하는 테이블에 값이 없으면 출력 되지 않는 경우 null값도 출력되게 하기 위해 사용하는 join

self join

: 자기 자신의 테이블과 조인하는 조인 문법

EQUI JOIN

조인하려는 두 테이블 사이에 공통된 컬럼으로 이퀄(=) 조인하는 조인문법

*주의*

출력할 컬럼명이 두 테이블에 같은 이름으로 있다면, 어느 테이블에서 가져올지 명시해줘야함(같은 이름이 없다면 명시하지 않아도 출력됨. 하지만 써줘야 성능 좋음)


select [테이블명].[출력할 컬럼명]
	from [테이블명1], [테이블명2]
	where [테이블명1].[연결되는 컬럼명] = [테이블명2].[연결되는 컬럼명];

예제1)KING은 어느 부서에서 근무하는가? ← EMP테이블만 가지고는 알 수 없음

그럼 부서 테이블을 우선 출력해보시오.


select * from dept;

예제2)

emp와 dept를 조인해서 이름(ename)과 부서위치(loc)를 출력하시오.


select ename, loc
	from emp, dept
	where emp.depnto = dept.deptno;

#emp와 dept 테이블을 조인하려면, 반드시 where절에 조인 연결고리를 적어줘야함.
#연결고리를 적지 않으면 다 조인해서(카티션 조인) 14*4=56개의 행이 출력됨

문제풀며 익히기

Q1.

사원번호, 이름, 월급, 부서위치, 입사일을 출력하시오.

(emp와 dept 테이블을 조인하여 출력하시오.)


A. select empno, ename, loc, hiredate
    from emp, dept
    where emp.deptno = dept.deptno;

Q2.

사원번호, 이름, 월급, 부서위치, 부서번호를 출력하시오.


A. select e.empno, e.ename, d.loc, e.hiredate, d.deptno
    from emp e, dept d
    where e.deptno = d.deptno;

Q3.

위의 결과를 다시 출력하는데, 부서위치가 DALLS인 사원들만 출력하시오.


A. select e.empno, e.ename, d.loc, e.hiredate, d.deptno
    from emp e, dept d
    where e.deptno = d.deptno 
    AND d.loc = 'DALLAS';

Q4.

월급이 1000에서 3000 사이인 사원들의 이름과 월급과 부서위치를 출력하시오.


A. select e.ename, e.sal, d.loc
    from emp e, dept d
    where e.deptno = d.deptno
    and e.sal between 1000 and 3000;

Q5.

telecom_table 생성 후 진행


drop  table  telecom_table;

 create   table   telecom_table  
  (   telecom         varchar2(10),
      t_price         number(10),
     etc_service     number(10) );

insert   into   telecom_table   values( 'sk',  17000,  7 );
insert   into   telecom_table   values( 'lg',  16000,  6 );
insert   into   telecom_table   values( 'kt',  18000,  9 );
insert   into   telecom_table   values( '알뜰kt',  15000,  10 );
commit;

select * from telecom_table;

우리반 테이블과 telecom_table을 조인해서 이름, 나이, 통신사, 통신비(t_price) 를 출력하시오.


A. select e.ename, e.age, t.telecom, t.t_price
    from emp18 e, telecom_table t
    where lower(e.telecom) = t.telecom;

Q6.

위의 결과를 다시 출력하는데, 통신사가 lg인 학생들만 출력하시오.


A1. select e.ename, e.age, t.telecom, t.t_price
	    from emp18 e, telecom_table t
	    where lower(e.telecom) = t.telecom
	    and lower(e.telecom) = 'lg';

A2. select e.ename, e.age, t.telecom, t.t_price
	    from emp18 e, telecom_table t
	    where lower(e.telecom) = t.telecom
	    and lower(t.telecom) = 'lg';

Q7.

부서위치, 부서위치별 토탈월급을 출력하시오.


A. select d.loc, sum(e.sal)
    from emp e, dept d
    where e.deptno = d.deptno
    group by d.loc;

Q8.

아래의 bonus table을 생성하시오


create table bonus
	(empno number(10), b_bonus number(10));

insert into bonus
select empno, sal*1.2
	from emp;

commit;

select * from bonus;

Q9.

emp 테이블과 bonus 테이블을 조인해서 이름, 월급, 보너스(b_bonus)를 출력하시오.


A. select e.ename, e.sal, b.b_bonus
    from emp e, bonus b
    where e.empno = b.empno;

Q10. (3개의 테이블 조인)

emp와 dept와 bonus를 조인해서 이름, 월급, 보너스, 부서위치를 출력하시오.


A. select e.ename, e.sal, b.b_bonus, d.loc
    from emp e, bonus b, dept d
    where e.empno = b.empno #연결 조인 조건
    and e.deptno = d.deptno; #연결 조인 조건

Q14.

위의 결과를 다시 출력하는데, 부서위치가 DALLAS인 사원들만 출력하시오.


A. select e.ename, e.sal, b.b_bonus, d.loc
    from emp e, bonus b, dept d
    where e.empno = b.empno #연결 조인 조건
    and e.deptno = d.deptno #연결 조인 조건
    and d.loc = 'DALLAS';   #검색 조건

059 여러 테이블의 데이터를 조인해서 출력하기 2(NON EQUI JOIN)

📖

NON EQUI JOIN 각 테이블 간에 공통 컬럼이 없을 때 사용하는 조인

* 급여등급 테이블 생성 후 진행


drop  table  salgrade;
 
create table salgrade
( grade   number(10),
  losal   number(10),
  hisal   number(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;

예제)

emp테이블과 salgrade 테이블을 서로 조인해서 이름, 월급, 급여등급(grade)을 출력하시오.


select e.ename, e.sal, s.grade
	from emp e, salgrade s
	where e.sal between s.losal and s.hisal;

#emp와 salgrade 테이블은 서로 공통된 column이 없어서 equal join은 어려움
##따라서 oracle 조인 문법을 사용함

emp 테이블의 월급이 salgrade 테이블의 losal과 hisal사이에 있다.

문제풀며 익히기

Q1.

emp테이블과 salgrade 테이블을 서로 조인해서 이름, 월급, 급여등급(grade)을 출력 (단. GRADE(급여등급)이 2등급인것만 출력하시오.)


A. select e.ename, e.sal, s.grade
		from emp e, salgrade s
		where e.sal between s.losal and s.hisal
    and s.grade = 2;

Q2. (3개 테이블 조인)

emp와 dept와 salgrade를 조인해서 DALLAS에서 근무하는 사원들의 이름과 부서위치, 월급, 급여등급(grade)을 출력하시오.


A. select e.ename, d.loc, e.sal, s.grade
    from emp e, dept d, salgrade s
    where e.deptno = d.deptno     #연결 고리 조건
    and e.sal between s.losal and s.hisal  #연결 고리 조건
    and d.loc = 'DALLAS';  #일반 조건

Q3. (Oracle 정규 교재 문제)

emp와 dept를 조인해서 부서위치를 출력하고, 부서위치별로 속한 사원들의 이름을 가로로 출력하시오.


A. select d.loc, listagg(e.ename,',') within group(order by e.ename asc)
    from emp e, dept d
    where e.deptno = d.deptno
    group by d.loc; #listagg는 group by 써줘야함

060 여러 테이블의 데이터를 조인해서 출력하기 3(OUTER JOIN)

📖

OUTER JOIN 조인하는 테이블에 값이 없으면 출력되지 않는 경우, null값도 출력되게 하기 위해 사용하는 join


(+) => outer join sign

where [테이블명1].[컬럼명] (+) = [테이블명1].[컬럼명]

##부족한 곳에 (+) 아우터 조인 사인 넣기
##outer join sign은 한쪽에만 쓸 수 있음. 양쪽에는 쓸 수 없음(오류 발생)

예제1)

부서테이블 전체를 출력하시오.


select * from dept;

예제2)

emp와 dept를 조인해서 이름과 부서위치를 출력하시오.


select e.ename, d.loc
    from emp e, dept d
    where e.deptno = d.deptno;

	!BOSTON!
	이 출력되지 않은 것을 볼 수 있음.
	EMP 테이블에 부서번호 40번이 없기 때문
	40번 부서번호가 입력되지 않으면 위의 SQL로는 영원히 BOSTON을 볼 수 없음

	만약 BOSTON이 출력되게 하고싶다면? => 예제3

예제3)

위 결과를 BOSTON이 출력되게 출력되시오.


select e.ename, d.loc
    from emp e, dept d
    where e.deptno(+) = d.deptno;

#모자란 쪽에 (+) 아우터 조인 사인을 넣음

예제4)

다음의 데이터를 입력하고 아우터 조인을 해서 EMP테이블에는 존재하는데, DEPT 테이블에는 존재하지 않은 부서번호에 대한 사원이름과 부서위치가 출력되게 하시오.


<입력값>
insert into emp(empno, ename, deptno)
	values(1235, 'jack', 70);

commit;

<출력sql>
select e.ename, d.loc
    from emp e, dept d
    where e.deptno = d.deptno (+);

📖

FULL OUTER JOIN

문제풀며 익히기

Q1.

아우터 조인을 해서 EMP테이블에는 존재하는데, DEPT 테이블에는 존재하지 않은 부서번호에 대한 사원이름과 부서위치가 출력되게 하시오.(단,jack만 출력)


A. select e.ename, d.loc
    from emp e, dept d
    where e.deptno = d.deptno (+)
    and ename = 'jack';

061 여러 테이블의 데이터를 조인해서 출력하기 4(SELF JOIN)

📖

SELF JOIN 자기 자신의 테이블과 조인하는 조인 문법


select [테이블1의 별칭1].[컬럼명1],[테이블1의 별칭2].[컬럼명1]
	from [테이블1] as [테이블1의 별칭1], [테이블1] as [테이블1의 별칭2]
	where [테이블1의 별칭1].[연결할 컬럼명]= [테이블1의 별칭2].[연결할 컬럼명]

예제1) MGR컬럼 이해하기


select empno, ename, mgr
	from emp;

#BLACK의 관리자가 KING임을 알 수 있음
#즉, ENMAE의 MGR은 관리자의 사원번호

예제2)

사원이름, 관리자의 이름을 출력하시오.


select 사원.ename as 사원이름, 관리자.ename as 관리자이름
	from emp 사원, emp 관리자
	where 사원.mgr = 관리자.empno;

문제풀며 익히기

Q1.(전 세계인들이 공통으로 보는 오라클 정규교재)

사원이름, 사원월급, 관리자이름, 관리자의 월급을 출력하시오.


A. select 사원.ename as 사원이름, 사원.sal as 사원월급,
    관리자.ename as 관리자이름, 관리자.sal as 관리자월급
        from emp 사원, emp 관리자
        where 사원.mgr = 관리자.empno;

Q2. (전 세계인들이 공통으로 보는 오라클 정규교재 난이도 상)

위의 결과를 다시 출력하는데, 관리자 보다 더 많은 월급을 받는 사원들만 출력하시오.


A. select 사원.ename as 사원이름, 사원.sal as 사원월급,
    관리자.ename as 관리자이름, 관리자.sal as 관리자월급
        from emp 사원, emp 관리자
        where 사원.mgr = 관리자.empno
        and 사원.sal > 관리자.sal;

Q3.(전 세계인들이 공통으로 보는 오라클 정규교재 난이도 상)

사원이름, 사원의 입사일, 관리자이름, 관리자의 입사일을 출력하는데, 관리자 보다 먼저 입사한 사원들만 출력하시오.


A. select 사원.ename as 사원이름, 사원.hiredate as 사원입사일,
    관리자.ename as 관리자이름, 관리자.hiredate as 관리자입사일
        from emp 사원, emp 관리자
        where 사원.mgr = 관리자.empno
        and 사원.hiredate < 관리자.hiredate;

#hiredate가 더 작은게 먼저 입사한 것

062 여러 테이블의 데이터를 조인해서 출력하기 5(ON절)

📖

* 오라클 조인과 1999 ANSI는 성능상 차이가 없음 *

1999 ANSI (America national Standard Institute) 조인 문법

오라클 조인법으로는 볼 수 없는 결과를 볼 때 사용

오라클의 아우터 조인은 양쪽에 아우터 사인을 사용할 수 없음

따라서, 아우터 조인 사인을 양쪽에 붙이고 싶을 때 1999 ANSI문법을 사용함

판교에 있는 게임회사들 같은 경우 MSSQL을 많이 사용함

MS사에서 만든 DB 소프트웨어인 MSSQL은 기본적으로 1999 ANSI 문법을 지원하고 있음. 그런데, 오라클 문법도 지원됨.

종류

ON절을 사용한 JOIN 문법
USING절을 사용한 JOIN 문법
NATURAL JOIN을 사용한 JOIN문법
LEFT/RIGHT/FULL OUTER JOIN 문법

ON절을 사용한 Join 문법


select [테이블명1/테이블명 별칭1].[컬럼명]
			,[테이블명2/테이블명 별칭2].[컬럼명]

	from [테이블명1]as[테이블명 별칭1] JOIN [테이블명2]as[테이블명 별칭2]
			ON ( [테이블명1/테이블명 별칭1].[공통 컬럼명]
					= [테이블명2/테이블명 별칭2].[공통 컬럼명])
		JOIN [테이블명3]as[테이블명 별칭3]
			ON ( [테이블명1/테이블명 별칭1].[비슷한 컬럼명]
					between [테이블명3/테이블명 별칭3].[비숫한 컬럼명]
					and [테이블명3/테이블명 별칭3].[비숫한 컬럼명])

	where [조건];

예제)

emp와 dept를 조인해서 이름과 부서위치를 출력하시오.


<1999 ANSI 조인문법>
select e.ename, d.loc
	from emp e join dept d
	on (e.deptno = d. deptno);

<Oracle 조인문법>
select e.ename, d.loc
	from emp e, dept d
	where e.deptno = d.deptno

문제풀며 익히기

Q1.

ON절을 사용한 조인문법으로 직업이 SALESMAN인 사원들의 이름과 월급과 직업과 부서위치를 출력하시오.


A. select e.ename, e.sal, e.job, d.loc
    from emp e join dept d
    on (e.deptno = d.deptno)
    where e.job = 'SALESMAN';

Q2.

ON절을 사용한 조인문법으로 emp와 salgrade를 조인해서 이름,월급,급여등급(grade)을 출력하는데, 급여등급이 2등급인 사원만 출력하시오.


A. select e.ename, e.sal, s.grade
    from emp e join salgrade s
    on (e.sal between s.losal and s.hisal)
    where s.grade = 2;

Q3,

emp와 dept와 salgrade 테이블을 조인해서 이름과 부서위치와 급여등급(grade)를 출력하시오.


A. select e.ename, d.loc, s.grade
    from emp e join dept d 
                    on(e.deptno = d.deptno)
                join salgrade s
                    on(e.sal between s.losal and s.hisal);

Q4.

위의 결과를 다시 출력하는데, CHICAGO에서 근무하는 사원들만 출력하시오.


A. select e.ename, d.loc, s.grade
    from emp e join dept d 
                    on(e.deptno = d.deptno)
                join salgrade s
                    on(e.sal between s.losal and s.hisal)
    where d.loc = 'CHICAGO';

063 여러 테이블의 데이터를 조인해서 출력하기 5(USING절)

📖

1999 ANSI 조인문법의 USING절 on절 대신에 using에 [공통컬럼명]만 쓰면 알아서 결과가 나옴


select [테이블명1/테이블명 별칭1].[컬럼명]
			,[테이블명2/테이블명 별칭2].[컬럼명]
		from [테이블명1]as[테이블명 별칭1] JOIN [테이블명2]as[테이블명 별칭2]
		using ([공통 컬럼명]) #괄호 반드시 써야함. 쓰지않으면 ERROR
		where 조건;

#USING절에 무조건 공통 컬럼명만! 테이블명과 함께 쓰지 않음
##USING절에 쓴 공통컬럼을 WHERE사용할 때에도 테이블명은 사용하지 않고
###공통컬럼명만 사용
=> (오라클 정규교재 SQL_sg.pdf p6-14)

예제)

이름, 부서위치를 출력하는데 using절을 사용한 조인으로 수행하시오.


A. select e.ename, d.loc
		from emp e join dept d
		using (deptno);

예제2) OCP시험문제

아래의 SQL은 실행되겠는가?


select e.ename, d.loc
	from emp e join dept d
	using(e.deptno);

	!!ERROR!!
	USING절에 공통된 연결고리 컬럼을 써야하는데, 
	컬럼을 쓸 때는 테이블 별칭을 앞에 붙일 수 없음

문제풀며 익히기

Q1.

USING절을 사용한 조인문법으로 직업이 SALESMAN인 사원들의 이름,월급,부서위치를 출력하시오.


A. select e.ename, e.sal, d.loc
    from emp e join dept d
    using (deptno)
    where job = 'SALESMAN';

Q2. (OCP 시험문제)

emp와 salgrade를 조인해서 이름,월급, 급여등급(grade)을 출력하는데 using절을 사용한 조인문법으로 가능한지 확인하시오.


A. select e.ename, e.sal, s.grade
    from emp e join salgrade s
    using ( ? );

	 즉, 가능하지 않다.

	#using 절을 사용한 조인은 조인조건이 이퀄(=) 조건인 equi조인만 가능하고
	##between..and로 주었던 non equi 조인은 구현이 안됨

Q3.(OCP 시험문제)

USING절을 사용한 조인문법에서 USING절에 괄호를 안쓰면 실행되는지 확인하시오.


select e.ename, d.loc
	from emp e join dept d
	using deptno;

	!!ERROR!!
	ORA-00906: 누락된 좌괄호

	USING절에 괄호를 쓰지 않으면 ERROR발생

064 여러 테이블의 데이터를 조인해서 출력하기 6(NATURAL JOIN)

📖

1999 ANSI 조인문법의 NATURAL JOIN (오라클 정규교재 p.6-9) WHERE절이나 ON절을 쓰지 않아도 오라클이 알아서 조인조건을 찾아서 조인해줌

두 테이블에서 데이터 유형과 이름이 일치하는 열을 기반으로 자동으로 테이블을 조인 할 수 있습니다. 이 작업은 NATURAL JOIN 키워드를 사용하여 수행할 수 있습니다.

조인은 두 테이블의 이름과 데이터 유형이 동일한 열에서만 발생합니다. 열 이름은 같지만 데이터 유형이 다를 경우 NATURAL JOIN 구문에서 오류가 발생합니다.

*NATURAL 조인이 가능하려면, 두 테이블에 서로 조인하려는 공통된 컬럼이 있어야하고 두 컬럼이 데이터 유형이 서로 동일해야함.( ✅ 테스트해보기)

예제)

이름, 부서위치를 출력하는데 NATURAL JOIN으로 수행하시오.


select e.ename, d.loc
	from emp e natural join dept d;

문제풀며 익히기

Q1.

065 여러 테이블의 데이터를 조인해서 출력하기 7(LEFT/RIGHT OUTER JOIN)

📖

RIGHT OUTER JOIN


<오라클 조인 문법>
	select e.ename, d.loc
		from emp e, dept d
		where e.deptno (+) = d.deptno;

<1999 ANSI 조인문법>
	select e.ename, d.loc
		from emp e RIGHT OUTER JOIN dept d
		on (e.deptno = d.deptno);

LEFT OUTER JOIN


<오라클 조인 문법>
	select e.ename, d.loc
		from emp e, dept d
		where e.deptno = d.deptno (+);

<1999 ANSI 조인문법>
	select e.ename, d.loc
		from emp e LEFT OUTER JOIN dept d
		on (e.deptno = d.deptno);

문제풀며 익히기

Q1.

RIGHTER OUTER JOIN을 이용해서 이름, 부서명(dname)을 출력하시오.


A. select e.ename, d.dname
    from emp e right outer join dept d
    on (e.deptno = d.deptno);

Q2. (SQLD 출제 문제)

위의 결과를 다시 출력하는데, ename의 null값이 맨 위에 출력되게 하시오.


A. select e.ename, d.dname
    from emp e right outer join dept d
    on (e.deptno = d.deptno)
    order by e.ename asc nulls first;

Q3.

어제 배운 self join을 1999 ANSI 조인문법으로 수행해서 사원이름,관리자의 사원이름을 출력하시오.


A. select 사원.ename as 사원이름, 관리자.ename as 관리자이름
    from emp 사원 join emp 관리자
    on (사원.mgr = 관리자.empno);

#KING,JACK의 관리자가 없으므로 15명이 아니라 13명이 출력됨

Q4. (OCP 문제)

self join을 1999 ANSI 조인문법으로 수행해서 사원이름,관리자의 사원이름을 출력하는데, 1999 ANSI 문법으로 사원이름쪽에 KING,JACK도 나오게 해서 15개의 행이 출력되게 하시오.


<오라클 조인문법>
A. select 사원.ename as 사원이름, 관리자.ename as 관리자이름
    from emp 사원, emp 관리자
    where 사원.mgr = 관리자.empno (+);

<1999 ANSI 조인문법>
A. select 사원.ename as 사원이름, 관리자.ename as 관리자이름
    from emp 사원 left outer join emp 관리자
    on (사원.mgr = 관리자.empno);

066 여러 테이블의 데이터를 조인해서 출력하기 8(FULL OUTER JOIN)

📖

1999 ANSI 조인문법 FULL OUTER JOIN (오라클 정규교재 한글판 p6-30) Oracle join 문법으로는 지원하지 않고, 오로지 1999 ANSI조인문법으로만 구현할 수 있는 문법

테이블에 서로 일치하는 행이 없어도 모든 행을 검색함

예제1)

아래의 오라클 조인문법이 수행되는지 확인하시오.


select e.ename, d.loc
	from emp e, dept d
	where e.deptno (+) = d.deptno (+);

	!!ERROR!!
	ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다

예제2)

위의 결과를 볼 수 있는 문법은 아래와 같다


select e.ename, d.loc
	from emp e full outer join dept d
	on (e.deptno = d.deptno);

문제풀며 익히기

Q1.

어제 만들었던 employees와 departments 테이블을 서로 조인해서 오라클 정규교재 한글판 p6-30에 나온것 처럼 last_name, department_id, department_name을 출력하시오.(FULL OUTER JOIN 사용)


A. select e.last_name, d.department_id, d.department_name
    from employees e full outer join departments d
    on (e.department_id = d.department_id);

■ 집합 연산자

067 집합 연산자로 데이터를 위아래로 연결하기 1(UNION ALL)

📖

집합연산자 4가지

UNION ALL : 합집합

UNION : 합집합 + 중복 데이터 제거

MINUS : 차집합

INTERSECT : 교집합

UNION ALL

!!집합연산자 사용시 참고사항!!

위의 SQL과 아래의 SQL의 컬럼의 개수가 동일해야함
위의 SQL과 아래의 SQL의 컬럼의 데이터 유형이 동일해야함
위의 SQL의 컬럼명으로 결과가 출력됨
ORDER BY 절은 맨 아래에 있는 쿼리문에만 사용할 수 있음

조인(join) vs 집합 연산자 1. 조인 : 두 테이블의 결과를 양옆으로 연결해서 보여주는 문법 2. 집합연산자 : 두 테이블의 결과를 위아래로 연결해서 보여주는 문법

예제1)

부서번호, 부서번호별 토탈월급을 출력하시오.


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

예제2)

사원 테이블의 전체 토탈월급을 출력하시오.


select sum(sal)
	from emp;

예제3)

rollup을 이용해서 부서번호, 부서번호별 토탈월급을 출력하는데, 맨 아래에 전체 토탈월급을 출력되게하시오.


select deptno, sum(sal)
	from emp
	group by rollup(deptno);

예제4)

위의 결과를 rollup 대신 union all로 구현하시오


A1. select deptno as 부서번호, sum(sal) as 토탈값
		from emp
		group by deptno
	UNION ALL
	select null as deptno, sum(sal) 
		from emp
		order by 부서번호 asc;

A2. select deptno as 부서번호, sum(sal) as 토탈값
		from emp
		group by deptno
	UNION ALL
	select to_number(null) as deptno, sum(sal)  #데이터 유형 일치시킴
		from emp
		order by 부서번호 asc;

예제5)

점심시간 문제의 합계 위쪽의 결과를 출력하시오


select  job, 
      sum( decode(to_char(hiredate,'RRRR'), '1980', sal, 0) ) as "1980",
	    sum( decode(to_char(hiredate,'RRRR'), '1981', sal, 0) ) as "1981",
	    sum( decode(to_char(hiredate,'RRRR'), '1982', sal, 0) ) as "1982",
	    sum( decode(to_char(hiredate,'RRRR'), '1983', sal, 0) ) as "1983",
	    sum(sal)
 from emp
 group by  job;

예제6)

점심시간 문제 결과의 맨아래에 합계의 행만 출력하시오.


select  nvl(null,'합계:'), 
        sum( decode(to_char(hiredate,'RRRR'),'1980',sal,0)) as "1980",
        sum( decode(to_char(hiredate,'RRRR'),'1981',sal,0)) as "1981",
        sum( decode(to_char(hiredate,'RRRR'),'1982',sal,0)) as "1982",
        sum( decode(to_char(hiredate,'RRRR'),'1983',sal,0)) as "1983",
        sum(sal)
   from  emp;

문제풀며 익히기

Q1.

예제 5번의 결과와 예제 6번의 결과를 union all로 합집합해서 출력하시오


A. select  job, 
       sum( decode(to_char(hiredate,'RRRR'), '1980', sal, 0) ) as "1980",
    sum( decode(to_char(hiredate,'RRRR'), '1981', sal, 0) ) as "1981",
    sum( decode(to_char(hiredate,'RRRR'), '1982', sal, 0) ) as "1982",
    sum( decode(to_char(hiredate,'RRRR'), '1983', sal, 0) ) as "1983",
    sum(sal)
 from emp
 group by  job
 union all
 select    nvl(null,'합계:'), 
               sum( decode(to_char(hiredate,'RRRR'),'1980',sal,0)) as "1980",
               sum( decode(to_char(hiredate,'RRRR'),'1981',sal,0)) as "1981",
               sum( decode(to_char(hiredate,'RRRR'),'1982',sal,0)) as "1982",
               sum( decode(to_char(hiredate,'RRRR'),'1983',sal,0)) as "1983",
          sum(sal)
   from  emp;

Q2.

위의 결과를 union all을 써서 쿼리문을 2개 쓰지말고 rollup을 써서 한번에 출력하시오.


A. select  nvl(job,'합계:') job, 
    sum( decode(to_char(hiredate,'RRRR'), '1980', sal, 0) ) as "1980",
    sum( decode(to_char(hiredate,'RRRR'), '1981', sal, 0) ) as "1981",
    sum( decode(to_char(hiredate,'RRRR'), '1982', sal, 0) ) as "1982",
    sum( decode(to_char(hiredate,'RRRR'), '1983', sal, 0) ) as "1983",
    sum(sal) 합
 from emp
 where job is not null
 group by  rollup(job);

068 집합 연산자로 데이터를 위아래로 연결하기 2(UNION)

📖

집합연산자 4가지

UNION ALL : 합집합

UNION : 합집합 + 중복 데이터 제거

MINUS : 차집합

INTERSECT : 교집합

UNION ALL vs UNION

UNION

중복행 제거(NULL값도 중복이면 제거됨)
첫번째 컬럼을 기준으로 결과 데이터가 정렬됨.(버전마다 차이 존재)

예제1)

부서번호가 10번, 20번인 사원들의 부서번호, 부서번호별 토탈월급을 출력하시오.


select deptno, sum(sal)
 from emp
	where deptno in (10, 20)
	group by deptno;

예제2)

부서번호가 20번, 30번인 사원들의 부서번호, 부서번호별 토탈월급을 출력하시오.


select deptno, sum(sal)
	from emp
	where deptno in (20, 30)
	group by deptno;

예제3)

예제1의 결과와 예제2의 결과의 쿼리의 결과를 하나로 합쳐서 출력하시오.


select deptno, sum(sal)
	from emp
	where deptno in (10, 20)
	group by deptno
UNION ALL
select deptno, sum(sal)
	from emp
	where deptno in (20, 30)
	group by deptno;

#쿼리1의 결과집합과 쿼리2의 결과집합이 하나로 합쳐져서 나옴
##정렬 안됨

예제 4)

위의 SQL을 UNION으로 수행하시오


select deptno, sum(sal)
	from emp
	where deptno in (10, 20)
	group by deptno
UNION
select deptno, sum(sal)
	from emp
	where deptno in (20, 30)
	group by deptno;

#정렬 됨
##중복행 제거됨

문제풀며 익히기

Q1.(OCP 문제)

UNION을 사용하면 NULL값도 중복 제거가 되는지 확인하시오.


<UNION ALL>
A1. select comm
    from emp
    where empno in (7566, 7654, 7499)
	union all
	select comm
    from emp
    where empno in (7499, 7844, 7900);

#NULL값이 두 개 출력됨

<UNION>
A2. select comm
    from emp
    where empno in (7566, 7654, 7499)
union
select comm
    from emp
    where empno in (7499, 7844, 7900);

#NULL값이 하나 출력됨
##즉, NUUL값도 중복 제거 됨

Q2.

아래의 SQL의 결과를 UNION ALL 또는 UNION으로 해서 구현하시오.

(단, grouping sets 를 사용하지 않고 구현하라)


select job, deptno, sum(sal)
	from emp
	group by grouping sets(job, deptno);


A. select to_char(null) as job, deptno, sum(sal)
    from emp
    group by deptno
	union all
	select job, to_number(null) as deptno, sum(sal)
    from emp
    group by job;

#null인 컬럼을 만들어 별칭을 같은 이름으로 줘버림

Q3.

아래의 SQL의 결과를 UNION 또는 UNION ALL로 구현하시오


select empno, ename, sum(sal)
	from emp
	group by grouping sets((empno, ename),());


A. select empno, ename, sum(sal)
    from emp
    group by  empno,ename
union all
select to_number(null) as empno, to_char(null) as ename, sum(sal)
    from emp;

069 집합 연산자로 데이터의 교집합을 출력하기(INTERSECT)

📖

집합연산자 4가지

UNION ALL : 합집합

UNION : 합집합 + 중복 데이터 제거

MINUS : 차집합

INTERSECT : 교집합

INTERSECT

테이블 간의 교집합 출력

null을 무시하지 않고 처리한다.


racdb1 SCOTT > select deptno, loc
  2     from dept
  3     where deptno = 20
  4  intersect
  5  select deptno, loc
  6     from dept;

    DEPTNO LOC
---------- -------------
        20 DALLAS
        20

예제)


select deptno, sum(sal)
	from emp
	where deptno in(10,20)
	group by deptno
intersect
select deptno, sum(sal)
	from emp
	where deptno in(20,30)
	group by deptno;

문제풀며 익히기

Q1.

MARKET_2017에서 상호명에 카페베네가 포함된 상호명의 상가업소번호와 상호명을 출력하시오


A. select 상가업소번호, 상호명
    from market_2017
    where 상호명 like '%카페베네%';

Q2.

MARKET_2017과 MARKET_2022의 교집합을 구해서 2017년도에도 존재했는데, 2022년도에도 있는 카페베네의 상호명과 상가업소번호를 출력하시오.


A. select 상호명, 상가업소번호
    from market_2017
    where 상호명 like '%카페베네%'
INTERSECT
select 상호명, 상가업소번호
    from market_2022
    where 상호명 like '%카페베네%';

070 집합 연산자로 데이터의 차이를 출력하기(MINUS)

📖

집합연산자 4가지

UNION ALL : 합집합

UNION : 합집합 + 중복 데이터 제거

MINUS : 차집합

INTERSECT : 교집합

MINUS


쿼리 1 
MINUS
쿼리 2

DBA나 DBE들이 차집합을 구할 일들이 많음

예를들면 다음과 같음


     운영서버 --------—---------------- 테스트 서버
			 ↑                                     ↑
	운영데이터                  운영서버와 비슷한 테스트 데이터

#운영서버의 데이터를 테스트 서버로 데이터 이행(ETL.데이터를 옮김)

해보기

test 서버의 IPv 주소를 확인(명령프롬프트(cmd)에서 ipconfig로 확인

운영서버가 cmd창에서 ping 날려보기 (ping [테스트서버의 ipv번호])

ping이 안보내지면 운영서버의 방화벽 해제 필요

제어판 > 시스템 및 보안 > 방화벽 > 방화벽설정 해제

> 방화벽사용안함으로 모두 바꿈

4. 테스트 서버에서 리스너의 상태를 확인하기 => cmd창에 lsnrctl status

운영 서버에서


create public database link dblink7
connect to c##scott
    identified by tiger
    using '192.168.19.26:1521/xe';

select * from emp@dblink7;

신용검색 에러 나는 경우 아래와 같이 해결(운영서버, 테스트서버 둘 다)

1) 경로를 따라서 sqlnet.ora 파일을 찾음

2) 파일 위에서 마우스 우클릭 > 연결프로그램 > 메모장

3) SQLNET.AUTHENTICATION_SERVICES = (NTS)이라는 sql 앞에 #붙이기

다른 sql있어도 위의 sql 앞에만 #붙이기

4) db drop > 다시 시


drop public database link dblink7;

create public database link dblink7
connect to c##scott
    identified by tiger
    using '192.168.19.26:1521/xe';
    
select * from emp@dblink7;


create public database link db_test
connect to c##scott
    identified by tiger
    using '192.168.19.26:1521/xe';
    
select * from emp@db_test;

운영서버와 테스터서버의 데이터의 차이를 확인해보기


    운영서버 --------—---------------- 테스트 서버
			 ↑                                    ↑
	DB링크 생성                 운영서버와 비슷한 테스트 데이터

문제풀며 익히기

Q1.

테스트 서버에서 EMP테이블의 데이터를 아래와 같이 INSERT하시오


insert  into  emp(empno, ename, sal, deptno)
  valueS(2345,'AAA',4000, 30);

commit;

Q2.

운영 서버에서 emp 테이블의 테이터를 같이 insert 하시오.


insert  into  emp(empno, ename,sal, deptno)
   values(3454,'BBB', 5000, 20);

commit;

Q3.

운영서버의 emp와 테스트 서버의 emp minus 연산자로 비교해서 운영서버에는 존재하는데, 테스트 서버에는 없는 데이터를 출력하시오.


운영서버:  select  *
                from  emp
                minus
          select *
           from  emp@db_test;

테스트 서버:  select  *
                     from  emp@db_real
                    minus
                    select *
                     from  emp;

#아래는 운영서버 출력결과

Q4.

market_2022 테이블에서 상호명이 스타벅스를 포함하고 있는 상가업소번호와 상호명을 출력하시오.


A. select 상가업소번호, 상호명
    from market_2022
    where 상호명 like '%스타벅스%';

Q5.

2017년도에 존재했는데, 2022년도에 사라진 스타벅스 매장이 총 몇 개인지 출력하시오.


select count(*)
	from (
		select 상가업소번호, 상호명
			from market_2017
			where 상호명 like '%스타벅스%'
	MINUS
select 상가업소번호, 상호명
	from market_2022
	where 상호명 like '%스타벅스%'
);

복습

📖

오라클 설치(SQL을 배우기 위한 간단한 설치) - 신입 때 많이 함

SELECT 문의 6가지 절

함수

단일행 함수
복수행 함수

데이터 분석 함수

조인(JOIN)

오라클 조인문법
1999 ANSI 조인문법

집합 연산자

UNION ALL
UNION
INTERSECT
MINUS

서브쿼리(SUBQUERY)

SINGLE ROW SUBQUERY : 서브쿼리의 결과가 하나의 행
MULTIPLE ROW SUBQUERY
MULTIPLE COLUMN SUBQUERY

서브쿼리에서 사용할 수 있는 연산자

단일행 서브쿼리의 연산자: =, >, <, >=, <=, !=, ^=
다중행 서브쿼리의 연산자: in, not in, >all, <all, >any, <any

복습문제

Q1.

ALLEN과 같은 부서번호에서 근무하는 사원들의 이름, 월급을 출력하는데, ALLEN은 제외하고 출력하시오.


A. select ename, sal
    from emp
    where deptno = (select deptno
                        from emp
                        where ename = 'ALLEN')
    AND ename != 'ALLEN';

#단일행 서브쿼리(SINGLE ROW SUBQUERY)를 사용한 것
#서브쿼리의 결과가 하나의 행을 출력하기 때문

Q2.

부서번호가 20번인 사원들과 같은 월급을 받는 사원들의 이름, 월급을 출력하시오.


A. select ename, sal
    from emp
    where sal in (select sal
                    from emp
                    where deptno = 20);

#부서번호가 20번인 사원들이 여러명이므로, 월급이 여러개가 리턴됨
##이 경우에는 이퀄(=)을 사용하면 안되고 in을 사용해야함
###이런 서브쿼리를 다중행 서브쿼리(MULTIPLE ROW SUBQUERY)라고

Q3.

사원테이블에서 토탈 월급을 출력하시오.


select sum(sal)
	from emp;

■ 서브쿼리(Sub-query) 1

071 서브 쿼리 사용하기 1(단일행 서브쿼리)

📖

단일행 서브쿼리

예제)

JONES의 월급보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하시오.


1. JONES의 월급을 알기
select sal
    from emp
    where ename = 'JONES';

#JONES의 월급 = 2975

2. JONES의 월급보다 큰거 추출    
select ename, sal
    from emp
    where sal > 2975;

3. 한 번에 해결해보기
	select ename, sal
		from emp
		where sal > ( select sal
										from emp
										where ename = 'JONES');

*설명*
괄호 안이 서브쿼리(Subquery) 괄호 밖의 쿼리가 메인쿼리(Main query)
실행순서 서브쿼리 -> 메인쿼리

문제풀며 익히기

Q1.

SCOTT과 같은 월급을 받는 사원들의 이름과 월급을 출력하시오.


A. select ename, sal
from emp
where sal = (
select sal
    from emp
    where ename = 'SCOTT');

Q2.

위의 결과를 다시 출력하는데, SCOTT을 제외하고 출력하시오.


A. select ename, sal
			from emp
			where sal = 
(
		select sal
	    from emp
	    where ename = 'SCOTT')

	AND ename != 'SCOTT';

Q3.

ALLEN 보다 늦게 입사한 사원들의 이름과 입사일을 출력하시오.


A. select ename, hiredate
    from emp
    where hiredate > 
    ( select hiredate
        from emp
        where ename ='ALLEN');

#hiredate가 클수록 늦게 입사한 것

Q4.

서울시 물가 데이터 테이블을 생성하시오.

price2.txt

1366.4KB

Q5.

price 테이블에서 a_price 의 최대값을 출력하시오 !


A. select  max(a_price)
		from  price;

Q6.

서울시 물가 데이터중에 가장 가격(a_price) 이 비싼 생필품(a_name) 과 그 가격(a_price) 을 출력하시오 !


select a_name, a_price
   from  price
   where  a_price =  ( select  max(a_price)
                         from  price );

Q7.

직업이 SALESMAN인 사원들 중에 최대월급을 받는 사원의 이름과 월급을 출력하시오.


A. select ename, sal
		from emp
		where sal =(select max(sal)
						    from emp
						    where job = 'SALESMAN');

Q8.

위의 결과를 서브쿼리를 이용하지 말고 order by .. fetch row를 이용해서 출력하시오.


A. select ename, sal
    from emp
    where job = 'SALESMAN'
    order by sal desc fetch first 1 rows only;

Q9.

dba는 sql을 볼 때 성능을 생각해서 봐야하므로 위의 2개의 sql중 어느 sql이 더 좋은 성능이 좋은지 확인해야함


<7번 서브쿼리> 성능 확인
select /*+ gather_plan_statistics */ ename, sal
		from emp
		where sal =(select max(sal)
						    from emp
						    where job = 'SALESMAN');

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

#위의 SQL의 실행계획과 BUFFER의 개수를 볼 수 있음
## 이 BUFFER의 수가 작을수록 성능이 좋은것(맨 위의 버퍼의 수 확인)


<8번 ORDER BY> 성능 확인
select /*+ gather_plan_statistics */ ename, sal
    from emp
    where job = 'SALESMAN'
    order by sal desc fetch first 1 rows only;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

#ORDER BY의 성능이 더 좋은것을 알 수 있음

Q10.

DALLAS의 부서번호가 몇번인지 출력하시오.


A. select deptno
		from dept
		where loc = 'DALLAS';

Q11.

DALLAS에 있는 부서번호에서 근무하는 사람들의 이름과 월급을 출력하시오


A. select ename, sal
		from emp
		where deptno = ( select deptno
											from dept
											where loc = 'DALLAS');

Q12.

위의 결과를 조인으로 출력하시오.


A. select e.ename, e.sal
		from emp e, dept d
		where e.deptno = d.deptno 
		and d.loc = 'DALLAS';

Q13.

문제 11번과 12번 중 어던게 더 BUFFER의 개수가 적은가?


<문제 11. 서브쿼리>
 
select /*+ gather_plan_statistics */ ename, sal
	from emp
	where deptno = ( select deptno
											from dept
											where loc = 'DALLAS');

	select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

<문제 12. 조인>

select /*+ gather_plan_statistics */ e.ename, e.sal
		from emp e, dept d
		where e.deptno = d.deptno 
		and d.loc = 'DALLAS';
        
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

서브쿼리 BUFFER 14

JOIN BUFFER 15

Q14.

우리반 테이블에서 통신사가 KT인 학생중에서 나이가 가장 많은 학생의 이름과 나이를 출력하시오.


A. select ename, age
    from emp18
    where age = ( select max(age)
                    from emp18
                    where lower(telecom) = 'kt')
    and lower(telecom) != '알뜰kt';

Q15.

통신사가 kt인 학생의 최대나이보다 더 나이가 많은 학생들의 이름과 나이를 출력하는데, 나이가 높은 학생부터 출력하라


A. select ename, age
    from emp18
    where age > ( select max(age)
                    from emp18
                    where lower(telecom) = 'kt')
    order by age desc;

072 서브 쿼리 사용하기 2(다중 행 서브쿼리)

📖

서브쿼리의 종류 3가지

단일행 서브쿼리 : 서브쿼리에서 메인쿼리로 하나의 값이 리턴되는 경우

다중행 서브쿼리 : 서브쿼리에서 메인쿼리로 여러개의 값이 리턴되는 경우

다중 컬럼 서브쿼리 : 서브쿼리에서 메인쿼리로 여러개의 컬럼값이 리턴되는 경우

다중행 서브쿼리 (정규교재 p7-8)

단일행 서브쿼리의 연산자: =, >, <, >=, <=, !=, ^= 다중행 서브쿼리의 연산자: in, not in, >all, <all, >any, <any

예제)

직업이 SALSEMAN 인 사원들과 월급이 같은 사원들의 이름과 월급을 출력하시오 ! (다중행 서브쿼리)


select  ename, sal
   from  emp
   where  sal in ( select  sal
                    from  emp
                    where  job='SALESMAN' );

문제풀며 익히기

Q1.

직업이 SALESMAN 인 사원들과 월급이 같지 않은 사원들의 이름과 월급과 직업을 출력하시오 !


select  ename, sal, job
  from emp
  where  sal not in ( select  sal
                       from  emp
                       where  job='SALESMAN' );

Q2.

우리반 테이블에서 성씨가 김씨인 학생들과 나이가 같은 학생들의 이름과 나이를 출력하는데 성씨가 김씨인 학생들은 제외하고 출력하시오


select  ename, age
 from  emp18
 where   age  in  ( select  age
                      from  emp18
                      where  ename like '김%')
 and  ename  not  like  '김%';

072 서브 쿼리 사용하기 2(다중 행 서브쿼리)

📖

서브쿼리의 종류 3가지

단일행 서브쿼리 : 서브쿼리에서 메인쿼리로 하나의 값이 리턴되는 경우

다중행 서브쿼리 : 서브쿼리에서 메인쿼리로 여러개의 값이 리턴되는 경우

다중 컬럼 서브쿼리 : 서브쿼리에서 메인쿼리로 여러개의 컬럼값이 리턴되는 경우

주의

서브쿼리에서 결과를 RETURN할때 NULL값이 리턴되지 않게 주의하시오.

다중행 서브쿼리 (정규교재 p7-8)

서브쿼리에서 사용할 수 있는 연산자

단일행 서브쿼리의 연산자: =, >, <, >=, <=, !=, ^=
다중행 서브쿼리의 연산자: in, not in, >all, <all, >any, <any

예제)

직업이 SALSEMAN 인 사원들과 월급이 같은 사원들의 이름과 월급을 출력하시오 ! (다중행 서브쿼리)


select  ename, sal
   from  emp
   where  sal in ( select  sal
                    from  emp
                    where  job='SALESMAN' );

문제풀며 익히기

Q1.

직업이 SALESMAN 인 사원들과 월급이 같지 않은 사원들의 이름과 월급과 직업을 출력하시오 !


select  ename, sal, job
  from emp
  where  sal not in ( select  sal
                       from  emp
                       where  job='SALESMAN' );

Q2.

우리반 테이블에서 성씨가 김씨인 학생들과 나이가 같은 학생들의 이름과 나이를 출력하는데 성씨가 김씨인 학생들은 제외하고 출력하시오


select  ename, age
 from  emp18
 where   age  in  ( select  age
                      from  emp18
                      where  ename like '김%')
 and  ename  not  like  '김%';

073 서브 쿼리 사용하기 3(NOT IN)

📖

서브쿼리의 종류 3가지

단일행 서브쿼리 : 서브쿼리에서 메인쿼리로 하나의 값이 리턴되는 경우

다중행 서브쿼리 : 서브쿼리에서 메인쿼리로 여러개의 값이 리턴되는 경우

다중 컬럼 서브쿼리 : 서브쿼리에서 메인쿼리로 여러개의 컬럼값이 리턴되는 경우

서브쿼리에서 사용할 수 있는 연산자

단일행 서브쿼리의 연산자: =, >, <, >=, <=, !=, ^=
다중행 서브쿼리의 연산자: in, not in, >all, <all, >any, <any

예제1)

부서번호가 20번인 사원들과 월급이 같지 않은 사원들의 이름과 월급을 출력하시오.


A. select ename, sal
    from emp
    where sal not in (select sal
                        from emp
                        where deptno = 20);

예제2) (단일행)

KING에게 보고하는 사원들의 이름을 출력하시오.

(KING의 사원번호를 관리자번호로 받고 있는 사원들을 의미함)


select empno, ename, mgr
    from emp;


select ename
    from emp
    where mgr = (select empno
                    from emp
                    where ename = 'KING');

예제3) (오라클 정규 교재의 난이도 중)

관리자인 사원들의 이름을 출력하시오.


select ename
    from emp
    where empno in (select mgr
                        from emp);

#관리자번호 값에 번호가 들어가 있는 사람의 관리자번호를 추출하여
##그들의 empno인 사원번호와 대응해 이름을 추출해야하기 때

예제4) (오라클 정규 교재의 난이도 상)

관리자가 아닌 사원들의 이름을 출력하시오.


!!ERROR!!
select ename
    from emp
    where empno not in (select mgr
                          from emp);

!!ERROR!!
선택된 레코드가 없다고 나옴
왜? NULL값 때문. MGR 중에 NULL값이 하나 있음
=> 서브쿼리에서 NOT IN을 사용했을 때 NULL값이 리턴되면 선택된 레코드가
		없다고 출력됨.

		TRUE AND FALSE = FALSE
		TRUE AND NULL = NULL ->  NULL값은 TRUE와 FALSE를 알 수 없기 때문

		따라서 NULL하나때문에 리턴되지 않은 것

-비교-
select *
    from emp
    where empno not in (select mgr
                          from emp);
	
	#선택된 레코드가 없다고 출력됨
                          
                          
select COUNT(*)
    from emp
    where empno not in (select mgr
                          from emp);

	#0을 출력함. 
	##왜? 그룹함수는 항상 결과를 리턴해주기 때문



A1.	select ename
	    from emp
	    where empno not in (select mgr
                            from emp
                            where mgr is not null);
                            
A2. select ename
	    from emp
	    where empno not in (select nvl(mgr,-1)
                            from emp);
	
#서브쿼리에서 mgr이 null값이 아닌 mgr번호 출력(관리자인 사원)
##출력된 mgr번호(관리자인 사원)와 empno에 해당하지 않는(관리자가 아닌자) 비교

*데이터 전처리*
select ename
	    from emp
	    where empno not in (select mgr
                            from emp
                            where mgr is not null);
		AND mgr is not null;

#서브쿼리에서 mgr이 null값이 아닌 mgr번호 출력(관리자인 사원)
##출력된 mgr번호(관리자인 사원)와 empno에 해당하지 않는(관리자가 아닌자) 비교
###관리자가 아닌 사원 중 데이터가 들어있지 않은 사원 전처리 하기위해 and 사용

문제풀며 익히기

Q1. (이수자 평가 객관식 문제) (SQLD 기출문제 74번) (✅ SQLD,SQLP 자주 출제)

아래의 테이블들에 대해서 SQL문을 수행하였을 때의 결과 값은?


[SQL]
SELECT COUNT(*)
FROM TEST74_1 A
WHERE A.COL NOT IN (SELECT COL FROM TEST74_2);

정답 = 0

(추가) 서브 쿼리 사용하기4(다중 컬럼 서브쿼리)

📖

서브쿼리의 종류 3가지

단일행 서브쿼리 : 서브쿼리에서 메인쿼리로 하나의 값이 리턴되는 경우

다중행 서브쿼리 : 서브쿼리에서 메인쿼리로 여러개의 값이 리턴되는 경우

다중 컬럼 서브쿼리 : 서브쿼리에서 메인쿼리로 여러개의 컬럼값이 리턴되는 경우(서브쿼리에서 컬럼을 여러개 뽑음)

다중 컬럼 서브쿼리(MULTIPLE COLUMN SUBQUERY)

NON PAIR WISE 방식

PAIR WISE 방

⇒ 서로 다른 결과가 출력됨.

NON PAIR WISE방식이 더 많은 데이터를 출력

더 많은 경우의 수를 가지고 있음

(단, Oracle과 MySQL에서만 두 개 다 지원. MSSQL에서는 NON PAIR WISE가 지원되지 않음)

예제1) (다중행 서브쿼리)

직업이 SALEMAN인 사원들과 월급이 같은 사원들의 이름과 월급과 커미션을 출력하시오.


select ename, sal, comm
	from emp
	where sal in (select sal
									from emp
									where job = 'SALESMAN');

#다중행 서브쿼리 (MULTIPLE ROW SUBQUERY)

예제2)

직업이 SALESMAN인 사원들과 월급도 같고 커미션도 같은 사원들의 이름과 월급과 커미션과 직업을 출력하시오.


<NON PAIR WISE방식>
A1. select emane, sal, comm, job
			from emp
			where sal in (select sal
											from emp
											where job = 'SALESMAN')
			AND comm in (select comm
											from emp
											where job = 'SALESMAN');

<PAIR WISE 방식>
A2.	select ename, sal, comm, job
			from emp
		    where (sal, comm) in ( select sal, comm
		                            from emp
		                            where job = 'SALESMAN');

#데이터가 적어서 명확하게 비교되지 않는 것

예제3)

명확하게 비교하기 위해 데이터 업데이트


update emp
    set sal = 1500
    where ename = 'KING';

update emp
    set comm = 1400
    where ename = 'KING';
    
commit;

NON PAIR WISE 방식의 답

PAIR WISE방식의 답


    	SAL       COMM
      1250       1400
      1600        300
      1500          0
      1250        500

#NON PAIR WISE방식은 위의 월급 중에 하나만 있거나
#위의 커미션 중에 하나만 있으면 다른 직업도 다 검색됨
#ex. sal이 1250 이면서 comm이 1400, sal이 1250이면서 comm이 300 ~

#PAIR WISE방식은
#딱 위의 4가지 경우에만 검색되는 것.
#ex. sal이 1250이면서 comm이 1400, sal이 1600이면서 comm이 300 ~

문제풀며 익히기

Q1.

우리반에서 통신사가 KT인 학생들과 나이가 같고 성별이 같은 학생들의 이름, 나이, 성별, 통신사를 출력하시오. (NON PAIR WISE 방식으로 수행)


A. select ename, age, gender, lower(telecom)
    from emp18
    where age in (select age
                    from emp18
                    where lower(telecom) = 'kt')
    and gender in (select gender
                    from emp18
                    where lower(telecom) = 'kt');

Q2.

위의 결과를 PAIR WISE 방식으로 출력하시오.


A. select ename,rownum, age, gender, lower(telecom)
    from emp18
    where (age, gender) in (select age, gender
                                from emp18
                                where lower(telecom) = 'kt');

#더 조금 출력되는것을 알 수 있음

074 서브 쿼리 사용하기 4(EXISTS와 NOT EXISTS)

📖

EXISTS와 NOT EXISTS 짝꿍의 emp테이블에는 존재하는데, 나의 emp테이블에는 존재하지 않는 데이터가 무엇인지 확인하고 싶다면?

MINUS

EXISTS와 NOT EXISTS


EXISTS문
- 메인쿼리의 데이터를 먼저 읽고 서브쿼리에 그 데이터가 존재하는지 찾아보는 구문
- 서브쿼리 내 SELECT절에 싱글 쿼테이션 마크(')로 감싸는 것은 NULL값을 제외하고
	아무거나 넣어도 됨. 뭔가를 출력하게 하기만 하면 됨

언제 유리한가?
=> 메인쿼리의 테이블의 개수가 작을 때 유리함

NOT EXISTS
- A에는 존재하는데 B에는 존재하지 않는 [컬럼명]을 출력하라고 하면
	큰거에서 작은거 빼면 되기 때문에 MAIN 테이블은 A가

예제1)

부서테이블의 부서번호를 출력하시오.


select deptno
	from dept;

예제2)

MINUS를 이용해서 부서 테이블에는 존재하는데, 사원 테이블에는 존재하지 않는 부서번호를 출력하시오.

DEPT - EMP = 40

10 10

20 20

30 30


select deptno
	from dept

MINUS

select deptno
	from emp;

예제3) 원리설명 포함

EXISTS문을 이용해서 DEPT테이블의 부서번호를 출력하는데,

EMP 테이블에 존재하는 부서번호들만 출력하시오.


select deptno
	from dept d
	where EXISTS (select 'X' #X자리에는 아무거나 넣어도 됨(NULL제외)
									from emp e
									where e.deptno = d.deptno);

#존재하는지 찾으러 가려면 내가 가지고있는게 무엇인지 알아야함
##그래서 쿼리문이 수행될때 MAIN QUERY가 먼저 수행됨.

#나 10번 가지고 있는데 너도 가지고 있니? 하고 10을 서브쿼리에 넘김
##하나 찾기만 하면 그 아래는 읽지 않고 STOP함. 다 읽을 필요 없어서 빠름
###존재하면 X가 출력되면서 존재한다고 MAIN QUERY로 넘김 -> 10번 출력됨
####위를 반복

문제풀며 익히기

Q1.


#데이터 입력 후 시작

insert into telecom_table values('알뜰lg', 15000, 11);

insert into telecom_table values('알뜰sk', 16000, 9);

commit;

telecom_table에서 통신사를 출력하는데, 우리반 테이블 emp18테이블에 존재하는 통신사만 출력하시오.


A. select telecom
    from telecom_table t
    where EXISTS (select 'd'
                    from emp18 e
                    where lower(e.telecom) = t.telecom);

Q2.

telecom_table에는 존재하는 통신사인데, emp18에는 존재하지 않는 통신사만 출력하시오.


A. select telecom
    from telecom_table t
    where NOT EXISTS (select 'd'
                         from emp18 e
                         where lower(e.telecom) = t.telecom);

Q3. (OCP 시험용 테이블)

departments 테이블에는 존재하는 departments_id인데 employees테이블에는 존재하지 않는 department_id를 출력하시오.


A. select department_id
    from departments d
    where NOT EXISTS (select *
                        from employees e
                        where e.department_id = d.department_id);

075 서브 쿼리 사용하기 5(HAVING절의 서브 쿼리)

📖

HAVIG절의 서브쿼리

Q. SELECT문의 6가지 절에서 서브쿼리로 사용할 수 있는 것은?(SQLD,SQLP,OCP)


select       O (SACLAR SUBQUERY 확장됐다는 의미)
	from       O (IN LINE VIEW)
	where      O (SUBQUERY)
	group by   X
	having     O (SCALAR SUBQUERY)
	order by   O (SCALAR SUBQUERY)

예제1)

직업, 직업별 토탈월급을 출력하시오.


#지우고 시작
delete from emp
    where job is null;
    
commit;

#정답
A. select job, sum(sal)
		from emp
		group by job;

예제2)

위의 결과를 다시 출력하는데, 직업별 토탈 월급이 5000이상인 것만 출력하시오.


select job, sum(sal)
    from emp
    group by job
    having sum(sal) >= 5000;

예제3)

직업, 직업별 토탈월급을 출력하는데, 직업별 토탈 월급의 직업이 SALESMAN인 사원들의 토탈월급 보다 더 큰것만 출력하시오.


select job, sum(sal)
    from emp
    group by job
    having sum(sal) > (select sum(sal)
                        from emp
                        where job ='SALESMAN');
	
#스칼라 서브쿼리 사용

문제풀며 익히기

Q1.

통신사, 통신사별 인원수를 출력하는데, 통신사별 인원수가 lg의 인원수보다 더 큰것만 출력하시오.


A. select lower(telecom), count(*)
    from emp18
    group by lower(telecom)
    having count(*) > (select count(*)
                        from emp18
                        where lower(telecom) = 'lg');

Q2. (복습문제)

우리반 테이블에서 나이가 30대인 학생들의 통신사를 출력하는데,

중복을 제거하여 출력하시오.


A. select distinct lower(telecom)
    from emp18
    where age between 30 and 39
    and telecom is not null;

076 서브 쿼리 사용하기 6(FROM절의 서브 쿼리)

📖

서브 쿼리 사용하기

Q. SELECT문의 6가지 절에서 서브쿼리로 사용할 수 있는 것은?(SQLD,SQLP,OCP)


!SELECT문의 6가지 절에서 서브쿼리를 사용할 수 있는 절!

select       O (SACLAR SUBQUERY 확장됐다는 의미)
	from       O (IN LINE VIEW)
	where      O (SUBQUERY)
	group by   X
	having     O (SCALAR SUBQUERY)
	order by   O (SCALAR SUBQUERY)

예제1) (복습문제)

이름, 월급, 월급에 대한 순위를 출력하시오.


select ename, sal, dense_rank() over (order by sal desc) 순위
	from emp;

예제2)

위의 결과에서 3등과 5등만 출력하시오.


!ERROR!
select ename, sal, dense_rank() over (order by sal desc) 순위
	from emp
    where 순위 in (3,5);

	!!ERROR!!
	ORA-00904: "순위": 부적합한 식별자
	=> 실행순서로 인해 발생하는 에러

#해결#
A. SELECT *    
    FROM (select ename, sal, dense_rank() over (order by sal desc) 순위
						from emp)
		WHERE 순위 IN (3,5);

#IN LINE VIEW라는 서브쿼리의 결과가 메모리에 올라간 후 메인쿼리로 보냄
##RANK함수는 무조건 순위가 순서대로 출력되기 때문에
###원하는 것만 뽑을 수 없기 때문임

문제풀며 익히기

Q1.(복습문제)

직업, 이름, 월급, 순위를 출력하는데, 순위가 직업별로 각각 월급이 높은 순서대로 순위를 부여하시오.


select job, ename, sal,
				dense_rank() over (partition by job
                            order by sal desc) 순위
    from emp;

Q2.

위의 결과를 다시 출력하는데, 각 직업별로 순위가 1등인 사원들만 출력하시오.


A. select *
    from (select job, ename, sal, dense_rank() over (partition by job
                            order by sal desc) 순위
            from emp)
    where 순위 = 1;

Q3.(복습문제)

부서번호, 부서번호별 평균월급을 출력하시오.


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

Q4. (복습문제)

부서번호, 월급, 이름, 자기가 속한 부서번호의 평균월급을 출력하시오.


A. select deptno, sal, ename, avg(sal) over (partition by deptno) 부서평균
    from emp;

Q5.

위의 결과를 출력하는데, 자기의 월급이 자기가 속한 부서번호의 평균월급보다 더 큰 사원들만 출력하시오.


A. SELECT *
    FROM (select deptno, sal, ename, avg(sal) over (partition by deptno) 부서평균
            from emp )
    WHERE sal > 부서평균;

077 서브 쿼리 사용하기 7(SELECT절의 서브 쿼리)

📖

서브 쿼리 사용하기

Q. SELECT문의 6가지 절에서 서브쿼리로 사용할 수 있는 것은?(SQLD,SQLP,OCP)


!SELECT문의 6가지 절에서 서브쿼리를 사용할 수 있는 절!

select       O (SACLAR SUBQUERY 확장됐다는 의미)
	from       O (IN LINE VIEW)
	where      O (SUBQUERY)
	group by   X
	having     O (SCALAR SUBQUERY)
	order by   O (SCALAR SUBQUERY)

스칼라 서브쿼리의 특징

select 절에 서브쿼리를 사용함으로 인해 로직 구현이 편해짐
스칼라 서브쿼리는 딱 1개의 값만 리턴 할 수 있

예제1)

이름, 월급, 사원테이블의 토탈 월급을 출력하시오.


!ERROR!
select ename, sal, sum(sal)
    from emp;

	!!ERROR!!
	ORA-00937: 단일 그룹의 그룹 함수가 아닙니다

select ename, sal, sum(sal) over () 토탈
   from emp;

#sum(sal) 뒤에 OVER() [별칭] 붙여주기
##하지만 현업에서는 주로 스칼라 서브쿼리 사용하여 문제 해결

예제2)

위의 결과를 SELECT절의 서브쿼리인 서브쿼리로 수행하시오.


select ename, sal, (select sum(sal)
											from emp) 토탈
	from emp;

예제3)

이름, 월급, 직업이 SALESMAN인 사원들의 토탈 월급을 출력하시오.


select ename, sal, (select sum(sal)
                        from emp
                        where job = 'SALESMAN') 토탈
    from emp;

문제풀며 익히기

Q1.

이름, 성별, 나이, 우리반 테이블의 평균나이를 출력하시오.


A. select ename, gender, age, (select round(avg(age))
                                from emp18) 평균나이
    from emp18;

#집계함수가 있어도 GROUP BY 없이 사용 가능

Q2.(SQLP 주관식 문제)

이름, 월급, 직업이 SALESMAN인 사원들의 토탈월급, 직업이 SALESMAN인 사원들의 최대월급, 직업이 SALESMAN인 사원들의 최소월급을 출력하시오.


<악성 SQL 답>
select ename, sal, (select sum(sal) from emp where job = 'SALESMAN') 토탈월급,
                (select MAX(sal) from emp where job = 'SALESMAN') 최대월급,
                (select MIN(sal) from emp where job = 'SALESMAN') 최소월급
    from emp;

	!ERROR!
	select ename, sal, (select sum(sal) 토탈월급, max(sal) 최대월급, min(sal)최소월급
                        from emp
                        where job = 'SALESMAN') 전체
    from emp;

!!ERROR!!
ORA-00913: 값의 수가 너무 많습니다
=> 스칼라 서브쿼리는 딱 1개의 값만 리턴할 수 있기 때문

<완벽한 답은 아니지만 해결 방법 중 하나>
select ename, sal, (select sum(sal) || max(sal) || min(sal)최소월급
                        from emp
                        where job = 'SALESMAN') 전체
    from emp;

Q3. (위의 문제 튜닝 힌트 문제)

직업이 SALESMAN인 사원들의 토탈월급, 최대월급, 최소월급을 출력하시오.


A. select sum(sal), max(sal), min(sal)
    from emp
    where job = 'SALESMAN';

Q4.

아래와 같이 문제 2번의 코드를 수정해서 작성하시오.

■ DML / DCL 실습하기

078 데이터 입력하기(INSERT)

📖

INSERT문 INSERT문을 이용하면 테이블에 데이터를 입력할 수 있음

예제1)

사원 테이블에 아래의 데이터를 입력하시오.

입력 데이터

사원번호 : 9321 사원이름 : JANE 월급 : 4000 입사일 : 오늘날짜


INSERT INTO emp(empno, ename, sal, hiredate)
	VALUES(9321, 'JANE', 4000, to_date('2023/12/07', 'rrrr/mm/dd'));

commit;

INSERT INTO emp(empno, ename, sal, hiredate)
	VALUES(9321, 'JANE', 4000, sysdate);  => 이방법은 좋지 않음

문제풀며 익히기

Q1.

오늘 입사한 사원의 이름과 입사일을 출력하시오


ERROR. 
	select ename, hiredate
    from emp
    where hiredate = sysdate;

#컬럼명만 출력되고 레코드가 출력되지 않음
##이렇게 해도 조회되는 SQL을 짤 수 있는데 악성 SQL이 될 확률이 높음

delete from emp
    where job is null;

commit;

Q2.

부서(dept) 테이블에 아래의 데이터를 입력하시오.

입력 데이터

부서번호: 50 부서명 : dba team 부서위치: seoul


A. INSERT INTO dept(deptno, dname, loc)
    VALUES(50, 'dba team', 'seoul');
    
commit;

Q3.

우리반 테이블에 아래의 데이터를 입력하시오.

입력 데이터

학생번호: 31 학생이름: 김인호 나이 : 33 생일 : 1991/11/04 주소 : 서울시 강남구 통신사 : sk 성별 : 남


INSERT INTO emp18(empno, ename, age, birth, address, telecom, gender )
    VALUES(31, '김인호', 33, to_date('1991/11/04','rrrr/mm/dd'), '서울시 강남구', 'sk', '남');
    
commit;

(추가) NULL 값 입력하기

NULL값을 입력하는 2가지 방법

명시적 입력


INSERT INTO emp(empno, ename, sal)
	VALUES(7234, NULL, 4000);

INSERT INTO emp(empno, ename, sal)
	VALUES(7456, '', 5000);

select ename, sal
	from emp;

암시적 입력


INSERT INTO emp(empno, ename, sal)
	VALUES(3933, 'aaa', 5000);

입력하는 컬럼외에 나머지 컬럼들은 다 null이 자동으로 입력됨

문제

Q1.

이름이 NULL인 사원들의 이름과 월급을 출력하시오.


A. select ename, sal
    from emp
    where ename is null;

Q2.

아래와 같이 사원테이블의 이름에 공백을 입력하시오.


INSERT INTO emp(empno, ename, sal)
	VALUES(4956, '   ', 6000); #공백 적당히

Q3.

이름이 NULL이 아닌 사원들의 이름과 월급을 출력하시오


A. select ename, sal
    from emp
    where ename is not null;

Q4. (현업에서 많이 사용하는 SQL)

위의 결과를 다시 출력하는데 이름이 공백인 사원도 제외하고 출력되게 하시오. (단, 공백이 몇 개가 들어있어도 상관없이 제외시켜야함)


A1. select ename, sal
	    from emp
	    where trim(ename) is not null; 

#이게 현업에서 많이 사용


A2. select ename, sal
	    from emp
	    where ename is not null
	    AND ename not like '% %';

#둘 다 악성 SQL

079 데이터 수정하기(UPDATE)-flashback

📖

데이터 수정하기 UPDATE


UPDATE [테이블명]
	SET [수정할 값]
	WHERE [조건]

주의사항

update문을 작성할때에는 where절을 작성했는지 주의깊게 봐야함

잘못하면 전체를 다 갱신할 수 있음

ROLLBACK

혹시라도 잘못 UPDATE 했으면 ROLLBACK

(commit 하기 전에만 가능→ 15분이 골든타임)


rollback;

오라클의 타임머신 기능 사용하기

COMMIT해버린 테이블을 FALSHBACK이 가능한 상태로 구성


ALTER TABLE emp ENABLE ROW MOVEMENT;

SELECT table_name, row_movement
	from user_tables
	where table_name = 'EMP'; #테이블명 무조건 대문자로 작성

-ENABLED라고 나오면 상태 변경이 잘 된 것

현재 시간에서 19분 전으로 EMP 테이블을 되돌리기


FLASHBACK TABLE emp TO TIMESTAMP
	(SYSTIMESTAMP - INTERVAL '19' MINUTE);

commit;

#systimestamp = 현재시간
##현재시간에서 19분을 뺀 시간으로 돌아가는 것

flashback이 가능한 시간을 확인하시오


select name, value
	from v$parameter
	where name='undo_retention';

⇒ 900초면 15분임. 15분 안에는 복구할 수 있는데, 15분이 넘어갔어도 지원진 데이터가 남아있으면 복구 할 수 있음. 다른 사람들과 함께 테이블을 사용했으면 안됐을 수도 있음

⇒ 기본값이 15분이고 DBA가 회사 사정에 맞춰서 설정을 바꿀 수 있음

예제1)

이름이 SCOTT인 사원의 월급을 5600으로 수정하시오


UPDATE emp
	SET sal = 5600
	where ename = 'SCOTT';

문제풀며 익히기

Q1.

직업이 SALESMAN 인 사원들의 커미션을 9000으로 수정하시오


A. update emp
    set comm = 9000
    where job = 'SALESMAN';
    
ROLLBACK;

Q2.

월급이 2000이상인 사원


update emp
    set deptno = 10
    where sal >= 2000;
    
rollback

Q3.

사원 테이블의 월급을 모두 0으로 변경하시오.


update emp
    set sal = 0;
    
select sal
    from emp;

rollback;

080 데이터 삭제하기(DELETE, TRUNCATE, DROP)

📖

DELETE vs TRUNCATE vs DROP의 차이

ㅤ	DELETE	TRUNCATE	DROP
데이터	삭제	삭제	삭제
데이터 저장공간	유지	삭제	삭제
테이블 구조	유지	유지	삭제
롤백 여부	가능	불가능	불가능
FLASHBACK 여부	가능	불가능	가능

주의: MySQL은 INSERT, UPDATE, DELETE를 하면 자동으로 COMMIT되는게 기본값!!

DELETE문

-대용량 데이터를 delete하면 db성능 느려짐. 그래서 대용량은 truncate사용

예제1)

직업이 SALESMAN인 사원들을 사원 테이블에서 지우시오.


DELETE from emp
	where job = 'SALESMAN';

select * from emp;

rollback;

DROP문

예제1)

EMP18테이블 백업 후 EMP18테이블을 DROP 해보기


create table emp18_backup
as
select * from emp18;


DROP table emp18;

예제2)

flashback으로 테이블을 복구하시오


1. 휴지통 속에 있는지 확인하기
	SHOW RECYCLEBIN;


2. 휴지통에서 복구하기
	FLASHBACK table emp18 TO BEFORE DROP;

	# 시간으로 하는게 아니라 이렇게 작성해야함

3. 확인
	select * from emp18;

TRUNCATE

-FLASHBACK이 안됨.. 조심히 수행해야함!

-테이블의 구조를 남겨놓는 삭제 방법

-대용량 데이터를 delete하면 db성능이 느려짐(이유는 관리 수업에서)

-그래서 빠르게 delete하려면, truncate를 하면 됨

예제1)

emp18테이블을 truncate 하시오


TRUNCATE table emp18;

select * from emp18;

예제2)

emp18_backup 테이블의 데이터를 emp18 테이블에 입력하시오


insert into emp18
	select*
	from emp18_backup; 

#백업 테이블의 모든 데이터를 emp18테이블에 넣어라

commit;

select * from emp18;

문제풀며 익히기

Q1.

사원 테이블에서 커미션이 NULL 인 사원들을 지우시오.


A. DELETE from emp
		where comm is null;

		select * from emp;

		rollback;

		select * from emp;

Q2.

사원 테이블의 모든 데이터를 지우고 commit하시오


A. DELETE from emp;

		commit;

		select * from emp;

(MySQL) 데이터 저장 및 취소하기(COMMIT, ROLLBACK)

📖

데이터 저장 및 수정하기

COMMIT

앞에서 작업한 입력, 수정, 삭제한 내용을 DB에 영구히 저장하겠다는 명령어

ROLLBACK

앞에서 작업한 데이터 입력, 수정, 삭제한 내용을 취소하는 명령어

예제1) MySQL 로 해보기

mySQL로 접속해서 emp테이블을 지우기


use orcl; #설정했던 데이터베이스 

select * from emp;

delete from emp;

safe모드 때문에 안된다고 뜨면 해야할 행동

나갔다가 다시 접속하고 위의 SQL문 다시 실행

예제2)

롤백 되는지 확인


rollback;

select * from emp;

!!MySQL은 INSERT, UPDATE, DELETE를 하면 자동으로 COMMIT되는게 기본값!!

=> ROLLBACK 실패

081 데이터 저장 및 취소하기(COMMIT, ROLLBACK, SAVEPOINT)

📖

commit은 앞에서 작업한 입력, 수정, 삭제 한 내용을 db에 영구히 저장 ⇒ commit의 종류 2가지 1. 명시적 commit : commit 명령어를 직접 수행한 경우 2. 암시적 commit : DDL 명령어를 수행했을 때, DCL 명령어를 수행했을 때, 정상 종료 했을 때 ex) DDL : create, alter, drop, truncate, rename DCL : grant, revoke 정상 종료 : exit (# 오라클 교재 9-31) rollback은 앞에서 작업한 데이터 입력, 수정, 삭제한 내용을 내용을 취소하는 명령어 ⇒ 암시적 롤백이 발생하는 경우 1. 정전 등의 이유로 갑자기 db가 비정상적으로 shutdown 되었을 때 ex) 국민 은행 DB 예금 통장 이체 적금 통장 100만원 —————————-—> (delete) (insert)

※ autocommit 기능 활성화/비활성화 하기 DML문(insert, update, delete, merge)가 실행되었을 때 바로 자동 커밋하는 기능


-- dba가 DB 강제로 종료시키는 코드(cmd)
shutdown abort

-- DB 다시 올리는 코드(cmd)
startup

※ dba 계정으로 sqlplus 접속(DBA는 주로 도스창에서 작업함)

※ scott 계정으로 sqlplus 접속

문제풀며 익히기

예제1. mysql로 접속해서 emp 테이블 지우세요


delete from emp;

예제2. 롤백 되는지 확인


rollback;

⇒ mysql은 insert, update, delete를 하면 자동 커밋 되는 게 default

오늘의 마지막 문제1. 우리반 테이블에서 나이의 순위가 5등 , 9등, 15등인 학생들의 이름과 나이를 출력


select ename, age, 순위
from (select ename,age, row_number() over(order by age desc) 순위
        from emp18)
where 순위 in(5,9,15);

오늘의 마지막 문제2.(SQLP 시험응시자) 아래의 SQL의 결과에서 토탈 월급, 최대 월급, 최소 월급을 잘라내어 아래와 같이 출력하시오.


select ename, sal, (select sum(sal) || max(sal) || min(sal) 
										from emp
										where job = 'SALESMAN') 전체토탈
from emp;


select ename, sal
      ,substr(토탈,1,instr(토탈,'@')-1) as SUMSAL
      ,substr(토탈,instr(토탈,'@',1,1)+1,instr(토탈,'@',1,2)-instr(토탈,'@')-1) as MAXSAL
      ,substr(토탈,instr(토탈,'@',1,2)+1) as MINSAL
from (select  ename, sal, ( select sum(sal) ||'@'|| max(sal) ||'@'|| min(sal) 
                              from  emp
                             where job='SALESMAN' ) 토탈
        from  emp);

Q1. autocommit 기능을 다시 끄시오


set autocommit off
show autocommit;

Q2.(SQLD, SQLP, OCP 공통) 아래의 DML 작업 중 DB에 반영된 작업은 무엇인가

insert into emp ……

insert into emp ……

update emp …..

update emp …….

commit;

insert into emp ……

update emp ….

rollback;

⇒ 1,2,3,4

※ savepoint 기능 이용하기

p-29 그림

⇒ 롤백하는 지점을 지정(책갈피 기능)

082 데이터 입력, 수정, 삭제 한번에 하기(MERGE), 반정규화

📖

fun2의 4-34 페이지 merge 문법 캡쳐

⇒ merge는 insert 와 update 와 delete를 한번에 수행하는 명령어로 upsert라는 이름으로 알려짐

문제풀며 익히기

예제1. emp와 dept를 조인해서 이름과 부서 위치를 출력


select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno;

※ (SQLD 와 SQLP 공통 중에 모델링 파트) 반정규화

위와 같이 조인을 자주 해야 하는 상황인데 조인을 할 때 성능이 너무 느리다면 그냥 LOC 컬럼을 EMP 테이블에 추가 시키고 조인하지 않고 그냥 다음과 같이 검색


select ename, loc
from emp;

예제2. emp 테이블에 loc 컬럼을 추가(컬럼 추가 작업)


alter table emp
add loc varchar2(10);

예제3. 지금 방금 추가한 loc 컬럼의 데이터를 해당 사원의 부서 위치로 값을 갱신 하시오


merge into emp e
using dept d
on (e.deptno = d.deptno)
when matched then 
update set e.loc = d.loc;

Q1. EMP 테이블과 salgrade 테이블을 조인해서 이름과 월급과 grade를 출력


select e.ename, e.sal, s.grade
from emp e, salgrade s
where sal between losal and hisal;

⇒ 위의 조인문이 너무 느려서 반정규화를 하고 싶다(Q2)

Q2. emp 테이블에 grade 라는 컬럼을 추가하시오


alter table emp
add grade number(10);

Q3. 지금 추가한 grade 컬럼에 해당 사원의 급여 등급(grade)으로 값을 갱신하시오.(merge 문 이용)


merge into emp e
using salgrade s
on (sal between losal and hisal)
when matched then 
update set e.grade = s.grade;

⇒ merge문이 실행 속도가 빠름;

Q4. (복습 문제) emp18 테이블과 telecom_table을 조인해서 이름과 통신사와 통신비(t_price)를 출력하시오.


select e.ename, e.telecom, t.t_price
from emp18 e, telecom_table t
where lower(e.telecom) = t.telecom;

⇒ 위의 조인문장이 너무 느려서 반정규화를 할 때

Q5. emp18 테이블에 t_price 컬럼을 추가 하시오


alter table emp18
add t_price number(10);

⇒ 실무에서 사용할 땐 데이터 유형과 크기를 확인하고 사용

Q6. merge 문을 이용해서 emp18 테이블에 t_price에 해당 학생의 통신비로 값을 갱신하시오.


merge into emp18 e
using telecom_table t
on (lower(e.telecom) = t.telecom)
when matched then 
update set e.t_price = t.t_price;

Q7. emp 테이블과 구조는 똑같은데 데이터는 없는 테이블을 emp700이라는 이름으로 생성


create table emp700
as select *
    from emp
    where 1=2;  #성립되지 않음. 즉 항상 거짓이라 데이터를 넣지 못하고 구조만 생성됨

⇒ where 절이 거짓이라서 데이터는 못 바꾸고 구조만 만들어짐

Q8. emp700 테이블의 emp 테이블의 empno를 입력하시오


insert into emp700(empno)
select empno
   from emp;

Q9. emp 테이블과 똑같은 데이터와 구조로 emp_backup2라는 테이블을 생성하시오


create table emp_backup2
as
select *
from emp;

⇒ 중요한 작업하기 전에 백업(자주쓰는 명령어)

Q10. emp_backup2에서 직업이 SALESMAN 과 ANLAYST를 지우시오


delete from emp_backup2 where job in('SALESMAN','ANALYST');

Q11. emp_backup2 의 월급을 모두 0으로 변경


update emp_backup2
set sal =0;

Q12. merge 문을 이용해서 emp_backup2의 데이터를 emp와 똑같이 맞추시오


merge into emp_backup2 b
using emp e
on (b.empno = e.empno)
when matched then
update set b.sal = e.sal, 
            b.job = e.job,
            b.ename = e.ename,
            b.comm = e.comm,
            b.mgr = e.mgr,
            b.deptno = e.deptno,
            b.hiredate = e.hiredate
when not matched then 
insert ( b.empno,b.ename, b.job, b.sal, b.comm, b.hiredate,b.mgr,b.deptno)
values (e.empno,e.ename,e.job,e.sal,e.comm,e.hiredate,e.mgr,e.deptno);

⇒ when matched then 절은 merge update 절(조인에 성공하면 수행되는 절)

⇒ when not matched then 절은 merge insert 절(조인에 실패하면 수행되는 절)

※ dba에게 유용한 팁

위의 SQL을 편하게 짤 수 있게 스크립트 만드는 방법


select listagg('b.' || column_name, ',')
from dba_tab_columns
where table_name ='EMP';
------------------------------------------------
select listagg('e.' || column_name, ',')
from dba_tab_columns
where table_name ='EMP';

⇒ dba_tab_columns (데이터 사전), 컬럼 이름을 뽑아낼 때 사용

⇒ 결과 복붙해서 사용

Q13. 우리반 테이블을 backup 하는 emp18_backup7 이라는 테이블을 생성하시오.


create table emp18_backup7
as
select *
from emp18;

Q14. emp18_backup7의 데이터를 아래와 같이 지우고 update 하시오


delete from emp18_backup7
where lower(telecom) in ('kt','lg');

update emp18_backup7
set age = null;

Q15. emp18_backup7의 내용을 emp18 테이블의 내용으로 똑같이 merge 하시오


merge into emp18_backup7 b
using emp18 e
on (b.empno = e.empno)
when matched then
update set b.age=e.age
when not matched then 
insert (b.AGE,b.T_PRICE,b.EMPNO,b.ENAME,b.GENDER,b.BIRTH,b.TELECOM,b.MAJOR,b.BEFORE_JOB,b.EMAIL,b.ADDRESS)
values (e.AGE,e.T_PRICE,e.EMPNO,e.ENAME,e.GENDER,e.BIRTH,e.TELECOM,e.MAJOR,e.BEFORE_JOB,e.EMAIL,e.ADDRESS);

Q16. (SQLD,SQLP 공통)

emp와 dept 테이블을 다 drop하고 다시 생성


drop table emp;
drop table dept;
--SQL 200제 게시판

emp 테이블에 loc 컬럼 추가


alter table emp add loc varchar2(10);

update 문으로 emp 테이블의 loc 컬럼 갱신

튜닝 전 SQL:


update emp e
set loc = (select loc 
					from dept d 
					where e.deptno = d.deptno);

⇒ emp 테이블의 부서 번호가 하나씩 서브 쿼리에 들어가면서 해당 부서 위치를 찾아서 해당 사원의 부서 위치로 값을 갱신하는데 14번 갱신됨

⇒ 데이터가 많아지면 실행 시간이 오래 걸림

튜닝 후 SQL:


merge into emp e
using dept d
on (e.deptno = d.deptno)
when matched then
update set e.loc = d.loc;

⇒ 현장에서 많이 쓰는 SQL

083 락(LOCK) 이해하기

📖

scott 계정 두 개 접속 후

scott 계정 A

scott 계정 B

⇒ update를 하면 update를 하는 그 행을 잠궈버림, 잠궈버려서 그 누구도 그 행을 갱신하지 못하게 함. 그래서 B 세션에서 update를 못하고 멈춰버림 ⇒ 테이블 전체에 lock을 건 게 아니라 update하는 그 행만 잠굼

⇒ 따라서 B 세션은 KING이나 JONES의 월급은 갱신할 수 있음(ALLEN만 잠김)

⇒ A세션이 commit이나 rollback하면 풀림

※ dba를 위한 팁

⇒ 현업에서 이렇게 update 하다가 멈춰버리면서 update가 안되는 locking 현상이 종종 발생

⇒ dba에게 lock을 풀어달라고 요구

※ 세션 kill하는 방법

도구 → 세션 모니터 → 접

status에서 active 상태인 명령이 대기 중인 명령

status에서 inactive 상태인 명령이 lock 하고 있는 명령어

⇒ 우클릭해서 세션 종료

⇒ 락(lock)은 데이터베이스를 운영하려면 꼭 필요한 기능

왜냐하면 같은 데이터를 동시에 갱신하면 데이터의 일관성이 깨지기 때문

문제풀며 익히기

예제1. JONES의 월급보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력


select sal
from emp
where ename = 'JONES';
select ename, sal
from emp
where sal > 2975;

084 SELECT FOR UPDATE절 이해하기

📖

SELECT FOR UPDATE


select ename,age,address
from emp
where ename like ('김%')
for update; ---> 마지막에 FOR UPDATE 

COMMIT; ---> LOCK풀기

lock은 update 문을 수행할 때 update를 하려는 행들을 자동으로 잠그는 기능 그런데 select 할 때도 lock이 걸릴 수 있음 ※ select 할 때 왜 테이블에 락을 거는가 ⇒ 내가 이 데이터를 검색해서 보고 있는 동안 그 누구도 갱신하지 못하게 막는 것 ex) 쿠팡 매일 밤 자정에 하루 동안 생긴 총 매출액을 계산하는 쿼리문을 수행하는데 그 시간에 주문이 들어오면 그 주문 건은 매출에 잡히면 안됨 → 따라서 집계하려는 데이터에 락을 걸어서 잠궈버림

문제풀며 익히기

Q1. 우리반 테이블에서 성씨가 김씨인 학생들의 이름과 나이와 주소를 출력하는데 for update를 써서 lock을 걸면서 select 하시오


select ename,age,address
from emp
where ename like ('김%')
for update;

commit; --> commit으로 락 풂

■ 서브쿼리(Sub-query) 2

085 서브 쿼리를 사용하여 데이터 입력하기

📖

dba가 해야 하는 업무 1. db 관리 2. db 백업 3. db 튜닝 4. ETL (데이터 이행)(오라클 골든 게이트라는 툴을 사용해서 이행)

문제풀며 익히기

예제1. emp_backup9 테이블을 생성하는데 emp 테이블의 구조만 가지고 생성하시오


create table emp_backup9
as
select *
from emp
where 1=2;   

#성립되지않음. 즉, where절은 거짓이 됨.
#이렇게 되면 구조만 생성되고 데이터는 넘어가지 않음.

예제2. emp 테이블의 모든 데이터를 emp_backup9에 이행하시오.


insert into emp_backup9
select *
from emp;


delete from emp_backup9;

commit;

예제3. 다시 emp 테이블의 데이터를 emp_backup9에 입력하는데 월급은 0으로 해서 입력하시오.


insert into emp_backup9(empno,ename,sal,job,mgr,hiredate,comm,deptno)
select empno,ename, 0,job,mgr,hiredate,comm,deptno
from emp;

086 서브 쿼리를 사용하여 데이터 수정하기

📖

update 문에서 서브 쿼리를 쓸 수 있는 절


insert into [테이블명]
select *
from [테이블명];


update emp      ---------> 서브쿼리 사용 가능(튜닝시 필요) 
set sal = 0     ---------> 서브쿼리 사용 가능
where ename = 'JONES'; ---------> 서브쿼리 사용 가능

문제풀며 익히기

예제1. 이름이 MARTIN 인 사원의 월급을 8000으로 수정하시오


update emp 
set sal = 8000
where ename = 'MARTIN';

예제2. SCOTT과 같은 직업을 갖는 사원들의 월급을 8000으로 수정하시오


update emp 
set sal = 8000
where job = (select job 
            from emp 
            where ename = 'SCOTT');

예제3. ALLEN 보다 늦게 입사한 사원들의 커미션을 9000으로 수정하시오.


update emp 
set comm = 9000
where hiredate > (select hiredate
                  from emp 
                  where ename = 'ALLEN');

예제4. KING의 월급을 ALLEN의 월급으로 변경하시오


update emp
set sal = (select sal 
            from emp 
            where ename = 'ALLEN')
where ename = 'KING';

예제5. KING의 부서 번호를 JAMES의 부서 번호로 변경하시오


update emp
set deptno = (select deptno 
             from emp 
             where ename = 'JAMES')
where ename = 'KING';

예제6. emp 테이블에 loc 컬럼을 추가하시오.


alter table emp
add loc varchar2(10);

예제7. (복습문제) 이름, 사원 테이블의 부서 위치, 부서 테이블의 부서 위치를 출력


select e.ename, e.loc as emp_loc, d.loc as dept_loc
from emp e, dept d
where e.deptno=d.deptno;

예제8. update 절의 서브 쿼리를 이용해서 dept_loc의 컬럼을 emp_loc 컬럼에 갱신


update (select e.ename, e.loc as emp_loc, d.loc as dept_loc
				from emp e, dept d
				where e.deptno=d.deptno)
set emp_loc = dept_loc;

⇒ 속도가 매우 빠름;

⇒ 위의 update 문이 merge 문과 함께 SQL 튜너들이 자주 사용하는 튜닝 SQL

Q1. emp18 테이블에 t_price 컬럼의 데이터룰 null로 변경하고 commit하시오.


update emp18
set t_price = null;
commit;

Q2. emp18 테이블과 telecom_table을 조인해서 이름을 출력하고 emp18 테이블의 t_price를 출력하고 telecom_table 테이블의 t_price를 출력하시오(컬럼 별칭을 각각 다르게 주세요)


select e.ename, e.t_price e_t_tprice, t.t_price t_t_tprice
from emp18 e, telecom_table t
where lower(e.telecom) = t.telecom;

Q3. update 절의 서브 쿼리를 이용해서 emp18 테이블의 t_price 의 값을 해당 학생의 통신비로 값을 갱신하시오.


update (select e.ename, e.t_price e_t_tprice, t.t_price t_t_tprice
				from emp18 e, telecom_table t
				where lower(e.telecom) = t.telecom)
set e_t_tprice = t_t_tprice;

Q4. 아래의 환경을 구성하시오


drop  table sales100;

-- 실습 환경만들기
 
create table sales100
as
 select *
  from  sh.sales;

create table sales200
as
 select rownum rn, prod_id, cust_id, time_id, channel_id,
           promo_id, quantity_sold, amount_sold
  from sales100;

alter table sales200
 add date_id date;

create table time2
 ( rn  number(10),
   date_id  date );

begin
  for i in  1 .. 918843  loop
     insert into time2
        values( i  , to_date('1961/01/02','YYYY/MM/DD')+ i );
   end loop;
end;
/

commit;

⇒ sales200 테이블의 date_id가 null값인지 확인

오늘의 마지막 문제1. sales200의 date_id에 값을 갱신하는데 time2의 date_id로 값을 갱신하시오(현업 문제)(merge 문이나 update 절을 이용한 서브 쿼리 사용)


update (select s.date_id s_date_id, t.date_id t_date_id
from time2 t, sales200 s
where t.rn = s.rn)
set s_date_id = t_date_id;

오늘의 마지막 문제2. 직업이 SALESMAN 인 사원들의 이름과 직업과 월급과 월급의 누적치를 출력하시오


select ename, job, sal, sum (sal) over(order by sal desc rows between unbounded preceding and current row) 누적치
from emp
where job = 'SALESMAN';

087 서브 쿼리를 사용하여 데이터 삭제하기

📖

예제1)

사원 테이블에서 직업이 SALESMAN인 사원들을 지우시오.


delete from emp  <= 서브쿼리 사용
where job = 'SALESMAN'; <= 서브쿼리 사용

rollback;

예제2)

부서번호가 20번인 사원들을 지우시오.


delete from emp
	where depno = 20;

rollback;

예제3)

SCOTT보다 더 많은 월급을 받는 사원들을 지우시오.


delete from emp
    where sal > (select sal
                    from emp
                    where ename = 'SCOTT');

rollback;

문제풀며 익히기

Q1.

ALLEN 보다 늦게 입사한 사원들을 지우시오


A. delete from emp
    where hiredate > (select hiredate
                        from emp
                        where ename = 'ALLEN');
                        
select * from emp;            

rollback;

088 서브 쿼리를 사용하여 데이터 합치기

📖

MERGE문과 SUBQUERY

using절에 테이블명 대신 서브쿼리를 사용할 수 있음

예제1)

@demo 스크립트를 돌려서 emp 테이블을 초기화하시오


명령 프롬프트 창에서 @demo 하세요.

예제2)

emp 테이블에 loc 컬럼을 추가하시오


alter  table  emp
   add   loc   varchar2(10);

 select ename, loc
  from emp;

예제3)

merge문을 이용해서 방금 추가한 loc의 데이터를 해당 사원의 부서위치로 값을 갱신하시오.


marge into emp e
using dept d ----> using 절 다음에 테이블명 대신 서브쿼리 사용 가능
on (e.deptno = d.deptno)
when matched then
update set e.loc=d.loc;

select ename, loc from emp;

예제4)

DEPT테이블에 samsal이라는 컬럼을 추가하시오.


alter table dept
	add sumsal number(10);

select * from dept;

예제5)

사원테이블에서 부서번호를 출력하고 부서번호별 토탈월급을 출력하시오.


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

예제6)

merge문을 이용해서 dept테이블을 merge하는데 해당 부서의 토탈월급으로 값을 갱신하시오.


merge into dept d
using (select deptno, sum(sal) as sumsal
        from emp
        group by deptno) e
on (e.deptno = d.deptno)
when matched then
update set d.sumsal = e.sumsal;

select * from dept;

문제풀며 익히기

Q1.

dept 테이블에 cnt라는 컬럼을 추가하고 해당 부서위치의 인원수로 값을 갱신하시오.


alter table dept
add cnt number(10);

select * from dept;


A. merge into dept d
using(select deptno, count(*) as cnt
        from emp
        group by deptno) e
on (d.deptno = e.deptno)
when matched then
update set d.cnt=e.cnt;