- 문제 317
직업, 직업별 최대 월급을 출력하시오.

 

- 답

1
2
3
select job, max(sal)
    from emp
    group by job;

 

 

 


 


- 문제 318
직업별 최대 월급들에 대한 평균값을 출력하시오.

 

- 답

1
2
3
select avg(max(sal))
    from emp
    group by job;

 

 

 


 


- 문제 319
직업, 직업별 최대월급을 출력하는데,
직업별 최대월급이 직업별 최대월급들에 대한 평균값보다 더 큰 것만 출력하시오.

 

- 답

1
2
3
select job, max(sal)
    from emp
    group by job  having max(sal) > ( select avg(max(sal)) from emp group by job );

 

 


 

※ with절을 사용한 튜닝

with job_maxsal as
  (
    select job, max(sal) 최대값
      from emp
      group by job
  )
  select job, 최대값
    from job_maxsal
    where 최대값 >
  (
    select avg(최대값)
      from job_maxsal
  );
with절 부분은 oracle temporary tablespace(임시 테이블스페이스 영역)에 테이블명 job_maxsal로 임시 저장한다.
with 절의 장점 : 두 번 이상 반복되고 있는 쿼리가 있는 SQL의 성능을 높일 수 있다.

 

 

 


 

 


- 문제 320
아래의 SQL을 with절로 변경하시오.

1
2
3
4
5
6
7
8
select d.loc, sum(e.sal)
    from emp e, dept d
    where e.deptno = d.deptno
    group by d.loc  having sum(e.sal) > ( select avg(sum(e.sal)) * 0.2 
                                              from emp e, dept d
                                              where e.deptno = d.deptno
                                              group by d.loc
                                        );

 

 

- 답

1
2
3
4
5
6
7
8
9
with loc_sumsal as (
    select d.loc, sum(e.sal) 총합 
        from emp e, dept d
        where e.deptno = d.deptno
        group by d.loc
    )
    select loc, 총합
        from loc_sumsal
        where 총합 > ( select avg(총합) * 0.2 from loc_sumsal );

 

 

 


 


- 문제 321
직업, 직업별 인원수를 출력하는데 직업별 인원수의 평균값보다 더 큰것만 출력하시오.(with 절)

 

- 답

1
2
3
4
5
6
7
8
with job_count as (
    select job, count(*) 총합
        from emp
        group by job
    )
    select job, 총합
        from job_count
        where 총합 > ( select avg(총합) from job_count );

 

 

- 설명
임시 테이블스페이스에 저장되는 with 절이 점점 쌓이다보면 수행시간이 길어지는 문제가 발생할 수 있다. 
 

 


 

 

※ 정규식함수 4가지
1. regexp_like(중요)
2. regexp_substr(중요)
3. regexp_instr
4. regexp_replace
5. regexp_count(중요)

 

 

 


 

 


- 문제 322
사원 이름에 EN 또는 IN을 포함하고 있는 사원들의 사원번호, 이름, 월급을 출력하시오.

 

- 답(튜닝 전)

1
2
3
4
select empno, ename, sal
    from emp
    where ename like '%EN%'
    or ename like '%IN%';

 

 

- 답(튜닝 후)

1
2
3
select empno, ename, sal
    from emp
    where regexp_like(ename,'(EN|IN)');

 

 

 


 


- 문제 323
이름의 첫글자가 S로 시작하고 끝 글자가 T 또는 H로 끝나는 사원들의 이름을 출력하시오.

 

- 답(튜닝 전)

1
2
3
4
select ename
    from emp
    where ename like 'S%'
    and (ename like '%T' or ename like '%H');

 

 

- 답(튜닝 전)

1
2
3
select ename
    from emp
    where regexp_like(ename, '^S.+(T|H)$');

 

 

- 설명
^ : 시작
$ : 끝
| : or
. : 자릿수
 

 


 


- 문제 324
이름, 월급을 출력하는데 월급이 0 대신에 *로 출력하시오.

 

- 답

1
2
select ename, replace(sal, 0'*')
    from emp;

 

 

 


 


- 문제325
이름, 월급을 출력하는데 숫자 0,1,2 는 * 로 출력하시오

 

- 답

1
2
select ename, regexp_replace(sal,'[0-3]','*')
    from emp;

 

 

 


 

 


- 문제 326
이름을 출력하는데 이름의 철자를 한칸씩 띄어서 출력하시오.

 

- 답

1
2
select ename, regexp_replace(ename,'(.)','\1 ') ename
    from emp;

 

 

- 설명
ename,'(.)','\1 '
한글자마다 한칸 씩 더 띄고 싶으면 regexp_replace(ename,'(.)','\1 <여기 칸을 늘림> ')
두글자마다 한칸 씩 띄고 싶으면 regexp_replace(ename,'(..)','\1 ')
 

 


 


- 문제 327
사원 테이블에 아래의 정보를 입력하시오.

empno 9203
ename JANE
sal 3400
deptno  20

 

- 답

1
2
insert into emp(empno, ename, sal, deptno)
    values (9203'JANE'340020);

 

 

 


 


- 문제 328
위의 문제를 다시 수행하는데 치환변수(&)를 이용해서 입력할 값들을 하나씩 물어보게 하시오.

 

- 답

1
2
insert into emp(empno, ename, sal, deptno)
    values (&empno, '&ename', &sal, &deptno);

 

 

- 설명
sqlgate 툴
insert into emp(empno, ename, sal, deptno)
  values (&empno, &ename, &sal, &deptno);

실행하면 파라미터 입력하는 창이 나오는데, 문자열을 입력 받는 ename 값은 싱글쿼터로 감싸준다.

 


 


- 문제 329
문제328번을 다시 수행하는데, 부서번호를 입력할 때 30번이 아닌 다른 부서번호를 입력하면 에러가 출력되도록 하시오.

- 답

1
2
3
4
5
6
7
8
9
10
insert into (
    select empno, ename, sal, deptno
        from emp
        where deptno = 30 with check option
    )
    values ( &empno,
             '&ename',
             &sal,
             &deptno
    );

 

 

 


 


- 문제 330
사원번호, 월급, 부서번호를 각각 물어보게하고,
데이터를 입력하는 insert문장을 작성하는데 월급을 0~9000 사이의 데이터만 입력될 수 있도록 하시오.

 

- 답

1
2
3
4
5
insert into(
    select empno, sal, deptno
        from emp
        where sal between 0 and 9000 with check option )
    values(&empno, &sal, &deptno);

 

 

 


 

 


- 문제 331
emp테이블과 똑같은 구조를 갖는 테이블을 생성하시오.

 

- 답

1
2
3
4
create table emp744as
    select *
        from emp
        where 1=2;

 

 

 


 


- 문제 332
사원 테이블의 모든 데이터를 emp744에 입력하시오.

 

- 답

1
2
3
insert into emp744
    select *
        from emp;

 

 

 


 


- 문제 333
사원 테이블의 직업이 SALESMAN인 사원들의 사원번호와 이름과 월급과 직업을 emp744 테이블에 입력하시오.

 

- 답

1
2
insert into emp744(empno, ename, sal, job)
    select empno, ename, sal, job  from emp  where job = 'SALESMAN';

 

 

 


 


- 문제 334
위의 테이블에 데이터를 입력하는데,
자기의 월급이 자기가 속한 부서번호의 평균월급보다 크거나 같으면 세금을 자기의 월급의 40%로 해서 high_tax 에 입력하고
자기의 월급이 자기가 속한 부서번호의 평균월급보다 작으면 세금을 자기의 월급을 10%로 해서 low_tax에 입력하는 쿼리를 작성하시오.

 

- 답

1
2
3
4
5
6
7
8
9
10
11
12
13
insert ALL
    WHEN sal >= 평균월급 then
        into high_tax(empno, ename, sal, tax)
        values(empno, ename, sal, tax1)
    WHEN sal < 평균월급 then    
        into low_tax(empno, ename, sal, tax)
        values(empno, ename, sal, tax2)
select *
    from ( select empno, ename, sal, sal*0.4 tax1, 
                                     sal*0.1 tax2, 
                                        avg(sal) over (partition by deptno) 평균월급
               from emp  
    );

 

 

 


 


- 문제 335
사원이름, 사원별로 총 받은 일당의 합을 출력하시오.

 

- 답

1
2
3
4
5
6
7
8
9
10
11
12
insert all 
    into sales_info values(empno,ename,sales_mon)
    into sales_info values(empno,ename,sales_tue)
    into sales_info values(empno,ename,sales_wed)
    into sales_info values(empno,ename,sales_thur)
    into sales_info values(empno,ename,sales_fri)
 
select empno, ename,sales_mon,sales_tue,sales_wed, sales_thur, sales_fri  
    from sales_source_data;   
 
select * from sales_info;
select * from sales_source_data;

 

 

 

'Personal > Big Data Course' 카테고리의 다른 글

PL/SQL : FOR문, WHILE문, CURSOR  (0) 2015.04.23
SQL : With절을 사용한 튜닝, 정규식 함수  (0) 2015.04.20
Private comment