Oracle DBA SQL 230626

sskit·2023년 8월 21일
DBADBMSoraclesql
0

OracleSQL

목록 보기
3/19
post-thumbnail

▣ 복습

  1. 기본 select 문 : 3. select empno, ename, sal
    1. from emp
    2. where deptno = 20
    4. order by hiredate desc ;

  2. 함수 :

    1. 단일행 함수 : 문자, 숫자, 날짜, 변환, 일반
    2. 복수행 함수 : max, min, sum, avg, count

문제121. (복습문제) 부서번호가 10번, 20번인 사원들의 이름과 월급과
부서번호를 출력하는데 월급이 높은 사원부터 출력하시오 !

 select    ename, sal, deptno
      from   emp
      where  deptno   in   ( 10,  20 )
      order   by  sal   desc;

문제122. (복습문제) 이름의 첫글자가 S 로 시작하는 사원들의 이름과
월급과 입사일을 출력하는데 월급을 출력할 때 다음과 같이
천단위를 부여해서 출력하시오 ! (책 P94)

 예:  3,000

select   ename,  **to_char( sal, '999,999')** ,  hiredate
   from   emp
   where    ename    like   'S%';

문제123. (복습문제) 이름, 보너스(sal * 2300) 을 출력하는데 한글로
이름과 보너스라고 컬럼명이 나오게 하고 보너스를 출력할때
천단위, 백만단위의 콤마가 출력되게 하시오 !

select  ename  as   이름,
to_char( sal * 2300, '$999,999,999')   as  보너스

  from   emp ;

설명: 숫자 --> 문자로 변환하는 to_char 함수를 사용해야 단위표시를
할 수 있습니다. to_char( 컬럼명, 'data 포멧' )

▣ 033 NULL 값 대신 다른 데이터 출력하기(NVL, NVL2)

테이블의 결측치 데이터를 오라클에서는 null 값이라 합니다.

null 값 ?

→ null이 알 수 없는 값이기 때문에 숫자 + null = null 이 나온다. 알 수 없는 값이 나온다.

  1. 데이터가 없는 상태

  2. 알 수 없는 값 (unknown)

문제124. 이름과 월급, 커미션, 월급 + 커미션을 출력하시오 !

select ename, sal, comm, sal + comm
from emp ;

설명: 커미션(comm) 이 null 이면 sal + comm 이 null 로 출력됩니다.
왜냐면 null 이 알 수 없는 값이기 때문입니다.
그래서 계산이 되게 하려면 null 값을 다른 값으로 치환해야 합니다.

select ename, sal, nvl( comm, 0 )
from emp;

문제125. 이름과 월급과 커미션, 월급 + 커미션을 출력하는데
월급 + 커미션에서 커미션이 null 이면 자기 월급이라도
출력되게하시오 !

select   ename, sal, comm,  sal  +  nvl( comm, 0 )
  from   emp;

설명: nvl(comm, 0) 을 쓰게 되면 실제로 데이터가 null 에 0 으로 변경
되는것은 아니고 이 출력되는 sql 에서만 변경이 되는것입니다.

문제126. nvl2 함수를 사용해서 이름, 월급, 커미션, 월급 + 커미션을
출력하시오 ! ( ocp 시험용 )

select ename, sal, comm, nvl2( comm, sal + comm, sal )
from emp;

설명: nvl2( comm, comm 이 null 아닐때 , comm 이 null 일 때 )

문제127. (복습문제) 커미션이 null 인 사원들의 이름과 월급과 커미션을 출력하는데 커미션이 null 인 사원들은 0 으로 출력하시오 !

select ename, sal, nvl( comm, 0 )
from emp
where comm is null ;

문제128. (ocp 시험 문제) 이름, 커미션을 출력하는데 커미션이 null
인 사원들은 no comm 이라는 글씨로 출력하시오 !

  • 사원번호(empno) 가 1234, 2222 인 사원의 행을 지우시오 ! delete from emp
    where empno in ( 1234, 2222) ; commit; select ename, nvl( to_char( comm) , 'no comm')
    from emp;
  • 명시적 형변환 함수 3가지 ?
    1. to_char : 문자형으로 형변환하겠다
    2. to_number : 숫자형으로 형변환
    3. to_date : 날짜형으로 형변환

문제129. (ocp 시험문제) 이름, 관리자번호(mgr) 을 출력하는데
관리자번호가 null 인 사원들은 no manager 라는 글씨로
출력되게하시오 !

select ename, nvl( to_char( mgr ), 'no manager' )
from emp;

문제130.(복습문제) market_2022 에 시군구명 컬럼의 데이터를
출력하는데 중복을 제거해서 출력하시오 !

select distinct 시군구명
from market_2022;

문제131. (복습문제) market_2022 에서 상호명이 스타벅스 를 포함하고
있는 모든 행과 모든 컬럼을 출력하시오 !

select *
from market_2022
where 상호명 like '%스타벅스%';

문제132. (복습문제) 위의 결과를 다시 출력하는데 시군구명이 강남구인
것만 출력하시오 !

select *
from market_2022
where 상호명 like '%스타벅스%' and 시군구명='강남구';

  • 함수 :
    1. 단일행 함수 : 문자, 숫자, 날짜, 변환, 일반
    2. 복수행 함수 : max, min, avg, sum, count

▣ 034 IF문을 SQL로 구현하기 1(DECODE)

문법: 이름, 부서번호, 보너스를 출력하는데

보너스가 부서번호가 10번이면 5000 을 출력하고
부서번호가 20번이면 3000 을 출력하고
나머지 부서번호는 0 을 출력하시오 !

                 **코드 없이 if 문을 구현하겠다.**
                            ↑
select  ename,  deptno,  **decode( deptno, 10,  5000, 20,  3000,   0  )**  as  보너스
  from   emp;

설명: decode( 컬럼명, 찾을값1, 출력할 값1,

찾을값2, 출력할값2, 기본값)

문제133. (점심시간 문제) 이름, 직업, 보너스를 출력하는데
보너스가 직업이 SALESMAN은 9000 을 출력하고
직업이 ANALYST 면 7000 을 출력하고
직업이 MANAGER 면 6000 을 출력하고
나머지 직업은 0 을 출력하시오 !

힌트:
select ename, deptno, decode( deptno, 10, 5000, 20, 3000, 0 ) as 보너스
from emp;

문제134. 이름, 월급, 입사일, 입사한 년도(4자리) 를 출력하시오 !

select ename, sal, hiredate, to_char(hiredate, 'RRRR')

from emp;

※ 설명: to_char( 날짜컬럼, 날짜포멧 )

문제135. 이름, 월급, 입사한 년도 4자리, 보너스를 출력하는데
입사한 년도가 1981 년도면 보너스를 9000 으로 출력하고
나머지 년도는 그냥 0 으로 출력하시오 !

select ename, sal, to_char(hiredate, 'RRRR') as 년도,
decode( to_char(hiredate,'RRRR') , '1981', 9000, 0 ) as 보너스
from emp;

문제136. (복습문제) 사원 테이블에서 이름, 이름의 첫번째 철자만
출력하시오 !

힌트: substr 을 이용하세요 !

select ename, substr( ename, 1, 1 )

from emp;

문제137. 이름, 보너스를 출력하는데 보너스가 이름의 첫번째 철자가
S 이면 보너스를 5000을 출력하고 A 이면 3000 을 출력하고
나머지 사원들은 0 을 출력하시오 !

select ename, decode( substr( ename, 1, 1 ), 'S', 5000, 'A', 3000, 0 ) as 보너스
from emp;

★ decode 관련해서 우리나라 금융권에서 예전에 발생했던 이슈 !

문제138. 이름과 직업과 월급을 출력하는데 월급이 높은 사원부터
출력하시오 !

select ename, job, sal
from emp
order by sal desc;

문제139. 이름과 보너스를 출력하는데 보너스가 직업이 PRESIDENT 면
null 로 출력하고 나머지 사원들은 자기 자신의 월급이
보너스로 출력되게하시오 !

select ename, decode( job, 'PRESIDENT', null, sal ) as 보너스
from emp;

문제140. 위의 결과를 다시 출력하는데 보너스가 높은 사원부터
출력하시오 !

select ename, decode( job, 'PRESIDENT', null, sal ) as 보너스
from emp
order by 보너스 desc ;

※ decode( job, 'PRESIDENT', null, sal )

세번째 인자값의 데이터 유형에 의해서 네번째 인자값의 데이터 유형이
결정이 됩니다. null 은 문자형입니다. 그래서 sal 이 문자형으로 출력이
되었습니다.

문제141. (SQLP 시험) 위의 결과가 제대로 나오게 SQL 을 튜닝하시오 !

select ename, decode( job, 'PRESIDENT', to_number(null), sal ) as 보너스
from emp
order by 보너스 desc ;

※ decode 의 유명한 암시적 형변환 사례 ! 오라클 bug 인데
아직도 해결이 안되었습니다.

▣ 035 IF문을 SQL로 구현하기 2(CASE)

decode 는 이퀄(=) 비교만 가능합니다. 그런데 case 는 이퀄(=) 비교
뿐만 아니라 non equal 비교도 가능합니다.

문제142. 이름, 월급, 보너스를 출력하는데 월급이 3000 이상이면
보너스를 9000 을 출력하고 월급이 1000이상이고 3000 보다 작으면
보너스를 2000을 출력하고 나머지 사원들은 0 을 출력하시오 !

select ename, sal, case when sal >= 3000 then 9000
when sal >= 1000 then 2000
else 0 end as 보너스
from emp;

문제143. 이름, 부서번호, 보너스를 출력하는데 보너스가
부서번호가 10번이면 9000 을 출력하고
부서번호가 20번이면 6000 을 출력하고
나머지 부서번호는 0 을 출력하시오 ! (case 문)

답:
select ename, deptno , case when deptno = 10 then 9000
when deptno = 20 then 6000
else 0 end as 보너스
from emp;

또는

select ename, deptno , case deptno when 10 then 9000
when 20 then 6000
else 0 end as 보너스
from emp;

문제144. 이름, 보너스를 출력하는데 커미션이 null 이면 보너스를
9000 을 출력하고 커미션이 null 이 아니면 보너스를 5000을
출력하시오 !

select ename, comm, case when comm is null then 9000
else 5000 end as 보너스
from emp;

▣ order by 절에서 null 값을 다루는 옵션

예제. 이름과 커미션을 출력하는데 커미션이 높은 사원부터 낮은 사원
순으로 출력하시오 !

select   ename,  comm
   from   emp
   order   by    comm   desc ;

이렇게만 출력하면 null 이 맨 위에 나오면서 보기가 불편해졌습니다.

  • OCP 시험문제 select ename, comm
    from emp
    order by comm desc nulls last ;

※ nulls last 또는 nulls first 를 쓸 수 있습니다.

문제145. 아래의 결과에서 보너스 null 값을 맨 아래에 출력되게하시오!

select ename, decode( job, 'PRESIDENT', to_number(null), sal ) as 보너스
from emp
order by 보너스 desc ;

답:

select ename, decode( job, 'PRESIDENT', to_number(null), sal ) as 보너스
from emp
order by 보너스 desc nulls last ;

복습)  함수 :  
			 1. 단일행 함수 :   문자, 숫자, 날짜, 변환, 일반(single  row  function)
       2. 복수행 함수  :  max, min, avg, sum, count
                         ( multiple  row  function )

**하나의 SQL문에 둘은 같이 쓸 수 없다!**

▣ 036 최대값 출력하기(MAX)

예제. 사원 테이블에서 최대 월급을 출력하시오 !

select max(sal)
from emp;

문제146. 직업이 SALESMAN 인 사원들 중에서의 최대월급을 출력하시오!

select max(sal)
from emp
where job='SALESMAN';

문제147. 우리반 테이블에서 서울에서 사는 학생중에 최대나이를
출력하시오 !

select max(age)
from emp17
where address like '서울%';

문제148. (복습문제) 직업이 SALESMAN, ANALYST 인 사원들중의
최대월급을 출력하시오 !

select max(sal)
from emp
where job in ('SALESMAN', 'ANALYST' );

문제149. 통신사가 KT 인 학생들중에서의 최대나이를 출력하시오 !
( 우리반 테이블 emp17 )

select max(age)
from emp17
where lower(telecom) = 'kt' ;

문제150. 직업이 SALESMAN 인 사원들중에서의 최대월급을 출력하는데
직업도 앞에 같이 나오게 하시오 !

3. select job,  --> 여러개가 출력하려고 한다.
          max(sal)  --> 1개가 출력 되는데
1. from emp
2. where job = 'SALESMAN';
-> 따라서 에러가 발생한다.

4. select   job,  max(sal)
1.    from  emp
2.    where  job='SALESMAN'
3.    group  by   job;

※ group by 절의 역활 : 데이터를 grouping 해주는 역활을 합니다.

앞의 번호는 실행 순서를 나타내는 것.

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

문제151. 부서번호, 부서번호별 최대월급을 출력하시오 !

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

문제152. 부서번호, 부서번호별 최대월급을 출력하는데 부서번호가 10,20,30번
순으로 정렬되어서 출력되게하시오 !

  1. select deptno, max(sal)
  2. from emp
  3. group by deptno
  4. order by deptno asc;

※ order by 절을 무조건 맨 마지막에 작성합니다. 실행도 맨 마지막에 됩니다.

▣ 037 최소값 출력하기(MIN)

※ 함수는 어찌되었든 결과를 리턴해줍니다. (SQLP 시험용)

select max(sal)

from emp
where job='SALESMA';

함수는 WHERE 절의 조건이 false 여도 결과는 리턴이 됩니다.

문제153.아래의 출력되는 결과가 null 값이 맞는지 확인해보시오 !

select nvl( max(sal) , 0 )
from emp
where job='SALESMA';

선택된 레코드가 없다고 하지 않습니다 . null 값이라도 출력합니다.

문제154. 사원 테이블에서 최소월급을 출력하시오 !

select min(sal)
from emp;

문제155. 직업이 SALESMAN 인 사원들중에서의 최소월급을 출력하시오

select min(sal)
from emp
where job='SALESMAN';

문제156. 직업, 직업별 최소월급을 출력하시오 !

select job, min(sal)
from emp
group by job;

문제157. 위의 결과를 다시 출력하는데 직업이 SALESMAN 은 제외하고
출력하시오 !

  1. select job, min(sal)
  2. from emp
  3. where job != 'SALESMAN'
  4. group by job ;

문제158. 위의 결과를 다시 출력하는데 직업별 최소월급이 높은것부터
출력하시오 !

select job, min(sal) select 보고 싶은 컬럼명
from emp from 테이블명
where job != 'SALESMAN' where 검색조건
group by job group by 그룹핑할 컬럼
order by 2 desc; order by 정렬할 컬럼

문제159. 우리반 테이블에서 성별, 성별별 최소나이를 출력하시오 !

select gender, min(age)
from emp17
group by gender;

문제160. (복습문제) 부서번호, 부서번호별 최소월급을 출력하는데
부서번호가 10 번과 20번만 출력되게하시오 !

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

문제161. (복습문제) 우리반 테이블에서 이름의 성씨만 출력하시오 !

select substr( ename, 1, 1 )
from emp17;

문제162. 우리반 테이블에서 성씨, 성씨별 최소나이를 출력하시오 !

select substr( ename, 1, 1 ) , min(age)
from emp17
group by substr( ename, 1, 1 ) ;

문제163. emp 테이블에서 입사한 년도(4자리), 입사한 년도별 최소월급을
출력하시오!

select to_char( hiredate, 'RRRR'), min(sal)
from emp
group by to_char( hiredate, 'RRRR');

문제164. 우리반 테이블에서 통신사, 통신사별 최소나이를 출력하시오!

select telecom, min(age)
from emp17
group by telecom;

설명: 위의 결과는 통신사가 9개나 출력되고 있습니다.

제대로 통신사가 3개만 나오게 해줘야 합니다.

문제165. 위의 문제164번을 다시 수행하는데 통신사를 소문자로 출력
하면서 출력하시오 !

select lower(telecom) , min(age)
from emp17
group by lower(telecom);

문제166. 이름, 통신사를 출력하는데 통신사를 출력할때
소문자로 출력하고 통신사 skt 를 sk 로 출력하시오
( 오늘 배운 decode 함수를 사용해야 합니다.)

select ename, decode( lower(telecom), 'skt', 'sk', lower(telecom) )

from emp17;

문제167. (오늘의 마지막 문제) 아래와 같이 통신사와 통신사별
최소 나이가 출력되게하시오 !

통신사 최소나이
kt 25
lg 25
sk ?

마지막 문제 올리시고 자유롭게 자습 또는 복습하시면 됩니다. ~~

3가지 방법으로~!

select decode(lower(telecom), 'skt', 'sk','lgt', 'lg','lgu+', 'lg',lower(telecom)) as 통신사, MIN(age) as 최소나이
from emp17
group by decode(lower(telecom), 'skt', 'sk','lgt', 'lg','lgu+', 'lg',lower(telecom))
order by 통신사;

SELECT CASE WHEN LOWER(telecom) LIKE 'sk%' THEN TO_CHAR('sk')
WHEN LOWER(telecom) LIKE 'kt%' THEN TO_CHAR('kt')
WHEN LOWER(telecom) LIKE 'lg%' THEN TO_CHAR('lg')
ELSE '0' END"통신사" , MIN(age)"최소나이"
FROM emp17
GROUP BY CASE WHEN LOWER(telecom) LIKE 'sk%' THEN TO_CHAR('sk')
WHEN LOWER(telecom) LIKE 'kt%' THEN TO_CHAR('kt')
WHEN LOWER(telecom) LIKE 'lg%' THEN TO_CHAR('lg')
ELSE '0' END
ORDER BY 1;

SELECT SUBSTR(LOWER(telecom), 1, 2) as 통신사 , MIN(age) as 최소나이
FROM emp17
GROUP BY SUBSTR(LOWER(telecom), 1, 2)
ORDER BY 1 asc ;
profile
sskit
이전 포스트

Oracle DBA SQL 230623

다음 포스트

Oracle DBA SQL 230627

0개의 댓글