Oracle DBA SQL 230623

▩ 그동안 배운 내용 복습

1. 오라클 과 mySQL 설치 ( 네카라쿠배당토)

2. 기본 select 문 :

   3 select 보고 싶은 컬럼명
   1 from 테이블명
   2 where 검색조건
   4 order by 정렬할 컬럼명

3. 함수 :

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

   문자함수 : upper, lower, initcap, substr, instr, replace, length, trim

▣ 어는 7년차 개발자 질문했던 SQL:

이름과 태어난 요일을 출력하는데 태어난 요일이 월화수목금토일로
정렬이 되어서 출력되게 하시오! (우리반 테이블)

select ename, birth, to_char(birth, 'dy'), 
  from emp17
  order by to_char(birth-1, 'd');

-> 생각해볼 내용 column 자리에 birth가 없어도 가능할까?

▣ 023 반올림해서 출력하기(ROUND)

함수의 종류 2가지 ?

1. 단일행 함수 : 문자, 숫자
2. 복수행 함수

숫자함수 3가지 ?

1. round 함수 : 반올림하는 함수
2. trunc 함수
3. mod 함수

select   round( 787.678, -1 )
  from  dual;
결과 : 790
**dual -->  결과값만 보기 위한 가상의 테이블(dummy  table)**

문제86. emp 테이블에서 이름과 월급 , 월급의 십의 자리에서 반올림해서 출력하시오 !

select  ename, sal, round( sal,  -2  )
  from   emp;

	A%B	  3000	3000
	A%%B	4000	4000
	JACK 	3400	3400
	KING	5000	5000
	BLAKE	 2850 	2900
	CLARK	 2450	 2500
	JONES	 2975	 3000
	MARTIN  1250	1300
	ALLEN	 1600 	1600
	TURNER  1500	1500
	JAMES 	950 	1000
	WARD  1250	1300
	FORD	3000	3000
	SMITH	 800	800

▣ 024 숫자를 버리고 출력하기(TRUNC)

    7 8 7 . 6 7 8
            1 2

설명:  소수점 2번째 자리 이후를 버려 버립니다.

select   trunc(  787.678,  2 )
from   dual;

문제87. 우리반 테이블에서 이름, 나이, 나이의 일의 자리숫자를
버리고 출력하시오 !

SELECT ename, age, TRUNC(age, -1)
FROM emp17;

문제88. 위의 SQL을 수정해서 이름, 나이대를 출력하시오 !

SELECT ename as "이름", TRUNC(age, -1) || '대' as "나이대"
FROM emp17;

이름 나이대
김하람 30대
윤영민 20대
: :

MYSQL> SELECT ename as "이름", TRUNCATE(age, -1) as "나이대"
FROM emp17;

설명: 오라클의 trunc 함수가 mySQL 에서는 truncate 입니다.
30대로 출력하는 것은 뒤에서 보겠습니다.

문제89. (복습문제) 나이가 20대인 학생들의 이름과 나이를 출력하는데
나이가 높은 학생부터 출력하시오 !

select ename, age
from emp17
where age between 20 and 29
order by age desc;

▣ 025 나눈 나머지 값 출력하기(MOD)

select mod(10, 3 )
from dual ;

문제90. 이름, 나이, mod 함수를 이용해서 나이를 2로 나눈 나머지값을
출력하시오 !

select ename, age, mod( age, 2 )
from emp17;

문제91. 우리반에서 나이가 짝수인 학생들의 이름과 나이를 출력하시오!

select ename, age
from emp17
where mod( age, 2 ) = 0 ;

▣ 026 날짜 간 개월 수 출력하기(MONTHS_BETWEEN)

단일행 함수  :     1. 문자
                2. 숫자
                3. 날짜 함수 :  MONTHS_BETWEEN
                4. 변환
                5. 일반

날짜 - 날짜 =  숫자
날짜 - 숫자 =  날짜
날짜 + 숫자 = 날짜

- 오늘날짜 보는 키워드
    
    select   sysdate
      from  dual;

문제92. 오늘날짜 - 입사일을 출력하시오 !

select sysdate - hiredate
from emp;

문제93. 위의 결과를 다시 출력하는데 소수점 이후는 버리고 출력하시오

select trunc( sysdate - hiredate )
from emp;

문제94. 이름, 태어난 날짜부터 오늘까지 총 몇일
살았는지 출력하시오

select trunc( sysdate - birth )
from emp17;

문제95. 이름, 태어난 날짜부터 오늘까지 총 몇 주 살았는지 출력하시오!

select ename, trunc( trunc( sysdate - birth ) / 7 )
from emp17;

문제96. 이름, 태어난 날짜 부터 오늘까지 총 몇 달 살았는지 출력하시오!

select ename, months_between( sysdate, birth )
from emp17;

※  months_between( 날짜1, 날짜2 )
		날짜1 ~ 날짜2 사이의 개월수를 출력합니다.

※ ocp 시험에 출제 : months_between( 최신날짜, 예전날짜 )
로 작성해야합니다.

문제97. 그동안 살아온 달이 300달 이상인 학생들의 이름과 나이와
살아온 개월수를 출력하시오 !

select ename, age, months_between( sysdate, birth )
from emp17
where months_between( sysdate, birth ) >= 300;

▣ 027 개월 수 더한 날짜 출력하기(ADD_MONTHS)

예제. 오늘날짜에서 3일을 더한 날짜를 출력하시오 !

select   sysdate  +  3
    from   dual;

예제. 오늘날짜에서 3달뒤의 날짜를 출력하시오 !

select    **add_months(  sysdate,  3 )**
     from  dual;

문제98. 이름, 입사일, 입사한 날짜 + 100달 을 출력하시오 !

select ename, hiredate, add_months( hiredate, 100 )
from emp;

문제99. 위의 결과를 mySQL 에서 수행하시오 !

SELECT ename, hiredate, date_add( hiredate, interval 1 MONTH )
from emp;

문제100. 아래의 SQL을 MySQL에서 구현하시오 !

select ename, hiredate, months_between( sysdate, hiredate )
from emp;

답:
SELECT ename, hiredate, timestampdiff( month, hiredate, sysdate() )
FROM emp;

SELECT ename, hiredate, PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM CURDATE()), EXTRACT(YEAR_MONTH FROM hiredate))
FROM emp;

select ename, hiredate, datediff(sysdate(),hiredate)
from emp;

문제101. 오늘날짜, 오늘날짜에서 100달뒤의 날짜를 출력하시오 !
( 오라클 기준 )

select sysdate, add_months( sysdate, 100 )
from dual;

문제102. 오늘날짜 , 오늘날짜에서 10년뒤의 날짜를 출력하시오 !

설명 : add_years 라는 함수가 없습니다.

select sysdate, sysdate + ( interval '10' year )
from dual;

설명:  interval   '숫자'   year
      interval   '숫자'   month
      interval   '숫자'   day
      interval   '숫자'   hour
      interval   '숫자'   minute
      interval   '숫자'   second

▣ 028 특정 날짜 뒤에 오는 요일 날짜 출력하기(NEXT_DAY)

• 오라클 날짜 함수 4가지 :

1. months_between : 날짜와 날짜 사이의 개월수를 출력하는 함수
2. add_months : 날짜에서 개월수를 더한 날짜를 출력하는 함수
3. next_day : 특정 날짜 뒤에 돌아오는 특정요일의 날짜를 출력하는 함수
4. last_day : 특정 날짜의 달에 마지막 날짜를 출력하는 함수

예제. 오늘날짜에서 바로 돌아오는 월요일의 날짜를 출력하시오 !

select next_day( sysdate, '월요일')
from dual;

설명: next_day( 특정날짜, 요일 ) --> 특정 날짜뒤에 돌아오는 요일을 출력합니다.

문제103. 오늘부터 100달뒤에 돌아오는 월요일의 날짜를 출력하시오!

select next_day( add_months( sysdate, 100 ), '월요일' )
from dual;

※ 설명: 함수를 위와 같이 중첩해서 사용할 수 있습니다.

문제104. (점심시간 문제) 직업이 SALESMAN 이 아닌 사원들이고
월급이 1200 이상인 사원들의 이름과 월급과 직업을 출력하는데
월급이 높은 사원부터 출력하시오 !

select ename, sal

from emp

where job ≠ ‘SALESMAN’ and sal ≥ 1200

order by sal desc;

▣ 029 특정 날짜가 있는 달의 마지막 날짜 출력하기(LAST_DAY)

• 오라클 날짜 함수 4가지 :

1. months_between : 날짜와 날짜 사이의 개월수를 출력하는 함수
2. add_months : 날짜에서 개월수를 더한 날짜를 출력하는 함수
3. next_day : 특정 날짜 뒤에 돌아오는 특정요일의 날짜를 출력하는 함수
4. last_day : 특정 날짜의 달에 마지막 날짜를 출력하는 함수

예제. 오늘날짜, 오늘날짜의 달에 마지막 날짜를 출력하시오 !

select sysdate, last_day( sysdate )
from dual;

23/06/23 23/06/30

문제105. 오늘부터 요번달 말일까지 총 몇일 남았는지 출력하시오 !

select last_day( sysdate ) - sysdate
from dual;

문제106. 위의 문제를 mySQL 에서 수행하시오 !

SELECT datediff( LAST_DAY(sysdate() ), sysdate() )
from dual;

문제107. (복습문제) 사원 테이블에서 이름에 S 자를 포함하고 있는
사원들의 사원이름과 입사일을 출력하는데
입사일이 최근에 입사한 사원부터 출력하시오 !

select ename, hiredate
from emp
where ename like '%S%'
order by hiredate desc ;

▣ 030 문자형으로 데이터 유형 변환하기(TO_CHAR)

• 함수는 2가지 ?

  1. 단일행 함수 : 문자, 숫자, 날짜, 변환, 일반

  2. 복수행 함수 : max, min, avg, sum, count

     변환함수 ? 데이터 유형을 변환하는 함수 입니다.

오라클의 데이터 유형이 크게 3가지 ?

1. 문자형 : 사원 테이블의 이름, 직업 컬럼
2. 숫자형 : 사원 테이블의 사원번호, 월급, 커미션, 부서번호
3. 날짜형 : 사원 테이블의 입사일

emp 테이블의 컬럼이 뭐가 있고 컬럼의 데이터 유형이 뭐가 있는지 확인

describe emp;

이름       널?       유형
-----    ------    -----

EMPNO              NUMBER(4)       <--- 숫자형
ENAME              VARCHAR2(10)    <---  문자형
JOB                VARCHAR2(9)
MGR                NUMBER(4)
HIREDATE           DATE            <--- 날짜형
SAL                NUMBER(7,2)
COMM               NUMBER(7,2)
DEPTNO             NUMBER(2)

내가 태어난 요일이 어떻게 되는가 ?
변환함수를 사용하면 알 수 있습니다

      to_char	          to_char
숫자형 ---------> 문자형 <---------- 날짜형
    <----------        ---------->
     to_number          to_date

1. to_char : 문자형으로 변환하는 함수
2. to_number : 숫자형으로 변환하는 함수
3. to_date : 날짜형으로 변환하는 함수

문제108. 오늘날짜, 오늘요일을 출력하시오 !
↓ ↓
날짜형 문자형

select sysdate, to_char( sysdate, 'day' )
from dual;

**- 날짜형식 to_char( 날짜, 날짜형식 )**

    1. 년도 : RRRR, YYYY, RR, YY
    2. 월 : MM, MON
    3. 일 : DD
    4. 주 : WW, IW, W
    5. 시간 : HH, HH24
    6. 분 : MI
    7. 초 : SS
    8. 요일 : DAY, DY, D

문제109. 우리반 테이블에서 이름, 생일, 생일의 요일을 출력하시오 !

select ename, birth, to_char( birth, 'day')
from emp17;

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

문제110. 이름, 입사일, 입사한 년도, 입사한 달, 입사한 일, 입사한 요일을 출력하시오 !

select ename, hiredate, to_char( hiredate, 'RRRR'),
to_char( hiredate, 'MM'),
to_char( hiredate, 'dd'),
to_char( hiredate, 'day')
from emp;

문제111. 1981년도에 입사한 사원들의 이름과 입사일을 출력하시오 !
( like 와 between ..and 쓰지 말고 지금 배운 to_char 를 이용해서
하세요 )

select ename, hiredate
from emp
where to_char( hiredate, 'RRRR')='1981';

문제112. 우리반에서 월요일에 태어난 학생들의 이름과 생일을
출력하시오 !

select ename, birth
from emp17
where to_char( birth , 'day') ='월요일';

문제113. 우리반 테이블에서 5월에 태어난 학생들의 이름과 생일을
출력하시오 !

select ename, birth
from emp17
where to_char( birth, 'mm' ) ='05';

문제114. 이름, 생일, 태어난 요일, 태어난 요일의 축약, 태어난 요일의 번호를 출력하시오 !

select ename, birth , to_char( birth, 'day'),
to_char( birth, 'dy'),
to_char( birth, 'd' )
from emp17;

문제115. 이름, 생일, 태어난 요일을 출력하는데 태어난 요일을
월화수목금토일로 정렬해서 출력하시오 !

select ename, birth, to_char(birth, 'day'), to_char(birth, 'd')
from emp17
order by to_char( birth,'d') asc;

select ename, birth, to_char(birth, 'day'),
to_char(birth, 'd'),

                                    to_char(birth-1, 'd')

from emp17
order by to_char( birth-1,'d') asc;

설명 :  날짜형 --->  문자형        
       숫자형 --->  문자형  ( 금융쪽, 제조업 )

문제116. 이름, 월급을 출력하는데 월급을 출력할 때
천단위 표시를 하시오 !

select ename, to_char( sal, '$999,999')
from emp;

설명: 9는 해당 자리를 의미하고 이 자리에 0~9까지 뭐가 와도
관계없다. L 은 local 화폐단위가 표시됩니다.

문제117. 위의 SQL을 다시 출력하는데 월급이 높은 사원부터
출력하시오!

select ename, to_char( sal, '$999,999')
from emp
order by 2 desc;

▣ 031 날짜형으로 데이터 유형 변환하기(TO_DATE)

	“문자형 ---> 날짜형”

예제. 81년 11월 17일에 입사한 사원들의 이름과 입사일을 출력하시오!

select ename, hiredate
from emp
where hiredate = to_date('81/11/17', 'RR/MM/DD');

튜닝전:
select ename, hiredate
from emp
where to_char(hiredate,'RR/MM/DD') = '81/11/17';

튜닝후:
select ename, hiredate
from emp
where hiredate = to_date('81/11/17', 'RR/MM/DD');

문제118. 81년도에 입사한 사원들의 이름과 입사일을 출력하시오 !
( LIKE 쓰지 마세요 )

튜닝전:

select ename, hiredate
from emp
where to_char( hiredate, 'RRRR') = '1981';

튜닝후:
select ename, hiredate
from emp
where hiredate between to_date('1981/01/01','RRRR/MM/DD')
and to_date( '1981/12/31','RRRR/MM/DD');

문제119. 우리반 테이블에서 1993년과 1994년에 태어난 학생들의
이름과 생일과 나이를 출력하시오 !

select ename, birth, age
from emp17
where birth between to_date('1993/01/01', 'RRRR/MM/DD')
and to_date('1994/12/31', 'RRRR/MM/DD');

select ename, birth, age
from emp17
where birth between '1993/01/01' and '1994/12/31' ;

설명: 위의 날짜 형식은 우리나라 날짜 형식이어서 결과가 잘 출력되고
있습니다. 그런데 만약 미국이나 독일에 가서 위와 같 검색하면
오류가 납니다.

우리나라 날짜 형식            vs           미국의 날짜 형식
  년도/달/일                               일/달/년도

• 지금 현재 database 에서 설정된 날짜 형식 확인하기

select  *
from  **nls_session_parameters** ;

NLS_DATE_FORMAT	RR/MM/DD

미국에서 위의 쿼리문을 수행한다면 다음과 같이 출력이 됩니다.

select  *
from  nls_session_parameters ;

NLS_DATE_FORMAT	DD/MM/RR

미국환경으로 현재 세션의 날짜형식을 변경하겠습니다.

alter   session   set   nls_date_format ='DD/MM/RR';

select  *
from  nls_session_parameters;

NLS_DATE_FORMAT   	DD/MM/RR

select ename, birth, age
from emp17
where birth between '1993/01/01' and '1994/12/31' ;

위와 같이하면 에러가 발생합니다. 다음과 같이 TO_DATE 를 사용해서
SQL을 작성해야합니다.

select ename, birth, age
from emp17
where birth between to_date('1993/01/01','RRRR/MM/DD') and to_date('1994/12/31','RRRR/MM/DD');

다시 우리나라 날짜형식으로 변경합니다.

alter session set nls_date_format='RR/MM/DD';

select * from nls_session_parameters;

▣ 032 암시적 형 변환 이해하기

형변환 작업 2가지 ?

1.  명시적 형변환 :   사람이 직접 형변환 합니다.
		1. to_char
		2. to_date
		3. to_number

2. 암시적 형변환 :   오라클이 알아서 형변환을 수행

예제. 다음의 SQL을 에러가 안나고 수행이 잘 될까요 ?

select ename, sal
from emp
where sal = '3000';

설명: 숫자는 양쪽에 싱글 쿼테이션 마크를 쓸 필요가 없는데 개발자가
실수로 써준 SQL입니다. 그러면 오라클이 알아서 다음과 같이
수행합니다.

select ename, sal
from emp
where sal = 3000;

진짜로 그랬는지 확인해보겠습니다. 오라클이 내부적으로 수행한
실행계획 과 SQL을 확인 합니다.

explain   plan   for
select  ename, sal
  from   emp
  where  sal = '3000';

select  * from  table(dbms_xplan.display);

       SAL = '3000'
              ↓
1 - filter("SAL"=3000)  <----   **오라클이 알아서 문자를 숫자로**
						 ↓     ↓                **변경했습니다**
						숫자   숫자

 SAL = '3000'
 문자    숫자         --->  문자와 숫자를 비교하면 **숫자가 우선순위가
                            높아서  문자를 숫자로 변경해줍니다.**

 SAL = 3000

select ename, sal
from emp
where sal like '30%';
숫자 문자

   1 - filter(TO_CHAR("SAL") LIKE '30%')

숫자와 문자를 비교하면 **숫자가 우선순위가 높아서 문자를 숫자로
변환해야하는데 위의 경우는 %를 숫자로 변경할 수 없으므로
sal 을 문자로 변경한것 입니다.**  암시적 형변환이 발생한겁니다.

이 SQL을 튜닝하는 방법을 인덱스 단원 배울때 알려주겠습니다.

create   table   emp100
( ename   varchar2(10),
  sal     varchar2(10) );

insert  into  emp100  values('scott', '3000' );
insert  into  emp100  values('smith', '1200' );
commit;

select   ename, sal
  from  emp100
  where  sal = '3000';  <--- 결과 나옵니다.

select   ename, sal
  from  emp100
  where  sal =  3000 ;   <---- 결과 나올까요 ?
         문자    숫자

explain  plan  for
select   ename, sal
  from  emp100
  where  sal =  3000 ;
         문자    숫자             --> 문자와 숫자 비교하면 숫자가 우선순위가 
																		높아서 문자를 숫자로 형변환 합니다.

select * from  table(dbms_xplan.display);

**1 - filter(TO_NUMBER("SAL")=3000)**

**그렇다면 튜닝 어떻게 할꺼야??

select ename, sal
  from emp100
  where sal = '3000';**

그냥 이렇게 하면 된다!!!

문제120. (오늘의 마지막 문제) 우리반 테이블에서 이름과 이메일을
출력하는데 이메일을 출력할때 도메인 만 나오게하세요

김하람 naver
김희선 naver
김동휘 naver
최서우 gmail
: :

마지막 문제 답글 올리시고 자유롭게 자습하시면 됩니다.

select ename, RTRIM(substr(email, instr(email, ‘@’) + 1), ‘.com’)

from emp17;