sql 기본 / 문자함수, 숫자함수, 변환함수

017 문자에서 특정 철자 추출하기(SUBSTR)

substr('컬럼명이나 문자열', 시작순서, 출력할 문자 개수) 

--SMITH만 출력하고 싶을때
select 'SMITH'
 from dual;  -- <- dual은 가상의 table
--오라클은 반드시 select-from 문장을 구성해야하기때문에 가상의 테이블 dual을 만들어줘야함

--SMITH라는 단어에서 SMI만 추출해서 출력하세요
select substr('smith',1,3)
 from dual;
 
--SMITH라는 단어에서 MI만 추출해서 출력하세요
select substr('smith',2,2)
 from dual;

문제58. smith라는 단어에서 첫 번째 철자 하나만 출력하세요

select substr('smith',1,1)
 from dual;

문제59. 사원테이블에서 이름을 출력하고 그 옆에 이름의 첫번째 철자를 출력하세요

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

문제60. 이름의 첫벌째 철자를 출력하는데 소문자로 출력하시오

select ename, lower(substr(ename,1,1))
 from emp;

문제61. 이름을 출력하는데 이름의 첫번재 철자는 대문자로 출력하고 나머지는 소문자로 출력하시오

select initcap(ename)
 from emp;

문제62. 위의 결과를 initcap을 사용하지 말고 upper, lower, ||를 사용해서 똑같이 출력하시오

select upper(substr(ename,1,1)) || lower(substr(ename,2)) --보통 이름의 길이는 10개가 안넘어감
 from emp;
 
--substr(ename,2) 2 다음에 그냥 괄호를 닫으면 끝까지 읽는 것

문제63. 81년도에 입사한 사원들의 이름과 입사일을 출력하시오

select ename, hiredate
 from emp
 where substr(hiredate,1,2) = '81';

문제64. 이름, 입사일, 입사한 달을 출력하시오

select ename, hiredate, substr(hiredate,4,2)
 from emp;

문제65. 12월에 입사한 사원들의 이름과 입사일을 출력하시오

select ename, hiredate
 from emp
 where substr(hiredate,4,2) = '12'; -- substr이 문자이기 때문에 12에 따옴표 붙여야 함. 
--		문자					문자
-- 따옴표를 안붙인다해도 어차피 오라클이 숫자를 문자로 변형함

문제66. 우리반 테이블에서 이름, 성을 출력하세요

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

문제67. 성이 김씨인 학생들의 이름과 주소를 출력하시오

select ename, address
 from emp14
 where substr(ename,1,1) = '김';

 

 

018 문자열의 길이를 출력하기(LENGTH)

철자의 개수를 세는 함수

--이름을 출력하고 이름의 철자의 길이를 출력하세요
select ename, length(ename)
 from emp;

문제68. 이름의 철자의 길이가 5이상인 사원들의 이름과 이름의 철자의 길이를 출력하세요

select ename, length(ename)
 from emp
 where length(ename) >= 5;

문제69. 우리반 테이블에서 이름과 이메일과 이메일 철자의 길이를 출력하고 이메일의 철자의 길이가 높은 학생부터 출력하시오

select ename, email, length(email)
 from emp14
 order by length(email) desc;
 
select ename, email, length(email)
 from emp14
 order by 3 desc;

 

 

019 문자에서 특정 철자의 위치 출력하기(INSTR)

instr(컬럼명 또는 문자열, 찾고 싶은 철자)

--SMITH라는 단어에서 알파벳 M이 몇번째 자리에 있는지 출력하세요
select instr('SMITH','M')
 from dual;
 
--만약 없는 철자를 넣는다면, 0 출력됨
select instr('SMITH','K')
 from dual;

문제70. 이름, 이름의 철자에 알파벳 A가 몇번째에 있는지 출력하세요

select ename, instr(ename,'A')
 from emp;

문제71. 이름에 알파벳 A를 포함하고 있는 사원들의 이름을 출력하시오

select ename
 from emp
 where instr(ename, 'A') > 0;
 #where instr(ename, 'A') != 0;

문제72. 우리반 테이블에서 이름에 '은'자를 포함하고 있는 학생들의 이름과 나이를 출력하시오

select ename, age
 from emp14
 where instr(ename, '은') > 0;

 

 

020 특정 철자를 다른 철자로 변경하기(REPLACE)

replace(컬럼명, 특정 철자, 변경할 철자)

--이름과 월급을 출력하는데 숫자 0을 *로 출력하세요
select ename, replace(sal,0,'*')
 from emp

문제73. emp14에서 이름의 두번째 글자만 출력하시오

select substr(ename,2,1)
 from emp14;

문제74. 병원 환자명 전광판의 이름을 출력하세요

select replace(ename,substr(ename,2,1),'*')
 from emp14;

 

 

021 특정 철자를 N개 만큼 채우기(LPAD, RPAD)

rpad or lpad(컬럼명, 설정할 자릿수, 채워넣을 철자)

/*이름과 월급을 출력하는데 월급 컬럼의 자릿수를 10자리로 하고, 
 월급을 출력하고 남은 나머지 자리에 별표(*)를 채워서 출력하세요*/
select ename, lpad(sal,10,'*')
 from emp;

 

 

022 특정 철자 잘라내기(TRIM, RTRIM, LTRIM)

--다음과 같이 smith 에서 앞에 s를 잘라내서 출력하고 뒤에 h를 잘라서 출력하고 양쪽 s를 잘라서 출력하세요
select 'smith', ltrim('smith','s'), rtrim('smith', 'h'), trim('s' from 'smiths')
 from dual;

 

문제75. 이메일을 출력하는데 이메일 끝쪽에 .com을 잘라내어서 출력하시오

select rtrim(email,'.com')
 from emp14;

문제76. 위의 결과에서 .net도 잘라내서 출력하시오

select rtrim(rtrim(email, '.com'),'.net')
 from emp14;

문제77. 위의 결과에서 @의 위치를 출력하시오

select instr(rtrim(rtrim(email, '.com'),'.net'),'@')
 from emp14;

문제78. (점심시간 문제) 우리반 이메일에서 도메인을 출력하시오

select substr(substr(email, 1,instr(email, '.')-1),instr(email,'@')+1)
 from emp14;
 
/* rtrim(rtim(email, '.com'),'.net') 로 작성할 경우
 맨 마지막 문장에서 '.','c','o','m','n','e','t'가 들어가는 모든 문자열이 trim됨.
 따라서 nate, kakao 같은 문자열이 잘려서 na, kaka 와 같이 출력되는 에러발생*/

 

 

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

-- 876.567를 출력하는데 소수점 두번째 자리인 6에서 반올림해서 출력하세요
select round(876.567,1)
 from dual;
 
-- round(876.567, 0) = round(876.567) > 소수점 반올림 정수만 출력 877

--정수에서 반올림
select round(867.567,-1)
 from dual; -- 870 출력

문제79. 사원테이블에서 월급을 출력하는데 십의 자리에서 바로 반올림하여 출력하시오

select round(sal,-2)
 from emp;

 

 

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

-- 876.567를 출력하는데 소수점 두번째 자리인 6과 그 이후의 숫자들을 모두 버리고 출력하세요
select trunc(876.567,1) #버리고 남은 값을 생각해 자릿수 입력
 from dual;
 
select trunc(876.567,-1)
 from dual;
 
-- 소수점 이후는 지정된 자리 이후의 숫자들을 버리는 것이고,
-- 소수점 이전은 지정된 자리를 포함해서 버리는 것

비식별처리 기법으로 데이터를 암호화할 때 유용하게 사용

ex) 27 > 20대, 32 > 30대

문제80. 우리반 테이블에서 이름과 나이를 출력하는데 나이를 비식별처리하기 위해 20대는 20대, 30대는 30대라고 출력되게 하시오

select ename, trunc(age,-1)||'대'
 from emp14;

 

 

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

-- 10을 3으로 나눈 나머지 값을 출력하세요
select mod(10,3)
 from dual;

문제81. emp14에서 이름과 나이를 출력하는데 나이가 홀수인 학생들만 출력하시오

select ename, age
 from emp14
 where mod(age,2) = 1;

 

 

* 날짜함수 4가지

1. month_between : 날짜와 날짜 사이의 개월수를 출력

2. add_month : 날짜에서 개월수를 더한 날짜를 출력

3. last_day : 특정 날짜의 달의 마지막 날짜를 출력

4. next_day : 해당 날짜의 달에서 앞으로 돌아올 특정 요일의 날짜를 출력

 

ex) 오늘 날짜를 출력하기

select sysdate
 from dual;

문제82. 내일 날짜를 출력하세요

select sysdate +1
 from dual;

날짜 - 날짜 = 숫자

날짜 + 숫자 = 날짜

날짜 - 숫자 = 날짜

ex) select sysdate - hiredate

        from emp;

문제83. 여러분들이 태어난 날짜부터 오늘까지 총 몇 일 살았는지 출력하시오

select round(sysdate - birth)
 from emp14;

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

months_between(최근날짜, 특정날짜) : 날짜와 날짜 사이의 개월수를 출력하는 함수

문제84. 내가 태어난 날짜부터 오늘까지 총 몇 달 살았는지 출력하시오

select months_between(sysdate, birth)
 from emp14;

문제85. 사원 테이블의 사원 이름을 출력하고 입사한 날짜부터 오늘까지 몇 달 근무했는지 출력하시오(소수점 이후 잘라버리세요)

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

문제86. 아래와 같이 결과가 출력되게 하시오

KING은 479달 근무했습니다.

select ename || '은 ' || trunc(months_between(sysdate, hiredate)) || '달 근무했습니다.'
 from emp;

 

 

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

add_months(특정 날자, 더하고 싶은 개월 수) : 특정 날짜에서 특정 개월수를 더한 날짜

-- 2019년 5월1일부터 100달 뒤의 날짜는 어떻게 되는지 출력하시오
select add_months('2019/05/01', 100)
 from dual;

문제87. 오늘날짜에서 200달 뒤의 날짜를 출력하시오

select add_months(sysdate, 200)
 from dual;

 

 

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

-- 2021년 5월5일로부터 바로 돌아오는 월요일의 날짜가 어떻게 되는지 출력하세요
select next_day('2021/05/05', '월요일')
 from dual;

문제88. 오늘날짜에서 100달 뒤에 돌아오는 월요일의 날짜를 출력하세요

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

 

 

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

-- 2021년 5월 5일의 해당 달의 마지막 날의 날짜를 출력하세요
select last_day('2021/05/05')
 from dual;

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

select last_day(sysdate) - sysdate
 from dual;

 

*함수의 종류

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

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

 

*변환함수 ? 

데이터 유형(문자형, 숫자형, 날짜형)을 변환하는 함수

날짜 > 문자, 숫자 > 문자로 바꾸는 경우가 많음

 

*변환함수의 종류 3가지

1. to_char : 문자형으로 변환하는 함수

2. to_number : 숫자형으로 변환하는 함수

3. to_date : 날짜형으로 변환하는 함수

 

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

to_char( 입력값, 포맷)

문제90. 오늘이 무슨 요일인지 출력하시오

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

* to_char 포맷의 종류(p.93)

RRRR, YYYY : 년도 4자리

RR, YY : 년도 2자리

MM, MON : 달

DD : 일

DAY, DY, D : 요일

WW, IW : 주

HH,HH24 : 시간

MI : 분

SS : 초

문제91. 내가 무슨 요일에 태어났는지 출력하시오

select to_char(birth, 'DAY')
 from emp14
 where ename = '차은재';

문제92. 수요일에 태어난 학생들의 이름과 생일을 출력하시오

select ename, birth
 from emp14
 where to_char(birth, 'DAY') = '수요일';

문제93. 이름과 입사일과 입사년도(4자리)를 출력하시오

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

문제94. 이름과 입사한 달을 출력하시오

select ename, to_char(hiredate, 'MON')
 from emp;

문제95. 이름, 입사한 달을 출력하는데 입사한 달이 asc하게 출력하시오

select ename, to_char(hiredate, 'MM')
 from emp
 order by to_char(hiredate, 'MM') asc;

문제96. 이름, 생일, 태어난 요일을 출력하시오.

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

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

select ename, birth, to_char(birth, 'day')
 from emp14
 order by replace(to_char(birth,'d'),1,8) asc;
 

select ename, birth,to_char(birth, 'day')
 from emp14
 order by to_char(birth-1,'d') asc; 
 -- birth에서 1일을 빼면 전날. 고로 일요일은 1 > 7, 월요일은 2 > 1이 됨

문제98. (마지막 문제) 월급이 1000에서 4000사이인 사원들의 이름과 월급과 입사일과 입사한 년도를 4자리로 출력하는데 입사한 년도를 ascending하게 정렬해서 출력하고 컬럼명을 한글로 출력하시오.

select ename as 이름 , sal as 월급, hiredate as 입사일, to_char(hiredate,'RRRR') as 입사년도
 from emp
 where sal between 1000 and 4000
 order by to_char(hiredate,'RRRR') asc;