/* 2일 오전 강의 */
-- 교재 P.75 문자함수
-- LOWER(), UPPER(), INITCAP(), CONCAT(char1, char2), LENGTH(char)
-- emp 테이블에서 사용자를 가져올 것
SELECT ename FROM emp;
-- INITCAP()함수
-- 문자열의 첫번째 문자를 대문자로 나머지 문자는 소문자로 변환
SELECT INITCAP(ename) FROM emp;
SELECT 'ename' FROM emp; -- 14개 데이터만큼 'ename'을 14번 출력
SELECT INITCAP('ename') FROM emp; -- 14개 데이터만큼 'Ename'을 14번 출력
SELECT INITCAP('ename') FROM dept; -- 5개 부서수 만큼 'Ename'을 5번 출력
SELECT INITCAP('ename') FROM dual; -- 한번만 출력하기 위해서 FROM dual 테이블을 쓴다.
-- 더미 테이블로써 단건만 조회할 경우 dual 테이블을 사용(데이터 반복을 피함)
SELECT * FROM dual;
-- CONCAT() 함수
-- CONCAT() 함수 제약사항 : 문자 2개만 연결할 수 있음
SELECT CONCAT(ename, job) FROM emp;
-- 2개이상 문자열을 연결
SELECT ename||job FROM emp; -- 결과 같음
-- 교재 P.77 substr() 함수
-- substr('string', start, length) 함수 : 글자수로 짜름
SELECT SUBSTR('가나다라', 1, 3) 결과 FROM dual;
-- substrb('string', start, length) 함수 : Byte 수로 문자열 짜름
-- 깨진 문자열은 자동 삭제처리
SELECT SUBSTRB('가나다라', 1, 3) 결과 FROM dual;
-- substr('string', start) 형태도 가능 : 시작점부터 문자열 끝까지 가져옴
SELECT substr('가나다라', 3) 결과2 FROM dual;
-- INSTR('문자열','찾는문자열', 검색시작점, n번째 문자열)함수
-- 결과값으로 n번째문자열의 위치(index)를 반환한다.
-- n번째문자열이 없을 경우 0을 반환
-- 응용 : 문자열에서 찾는 문자열이 몇번째 이상 포함되어 있는지 검색
-- ename 문자열에서 1번째 'T' 문자열을 처음부터 검색하여 위치(index) 반환
SELECT ename, INSTR(ename, 'T', 1, 1) T문자위치 FROM emp;
-- ename 문자열에서 5번째 'T' 문자열을 처음부터 검색
SELECT ename, INSTR(ename, 'T', 1, 5) T문자위치 FROM emp; -- 결과 : 0
-- ename 문자열에서 3번째 'T' 문자열을 처음부터 검색
SELECT ename, INSTR(ename, 'T', 1, 3) T문자위치 FROM emp; -- 결과 : 0
-- 교재 P. 78 ASCII(char) 함수
-- 특정 문자의 ASCII 코드 값을 반환한다.
-- DB 내부적으로 정렬시 오름차순 정렬(대문자 먼저, 소문자 다음)
-- DB 내부적으로 정렬시 내림차순 정렬(소문자 먼저, 대문자 다음)
SELECT ASCII('A') 대문자, ASCII('a') 소문자 FROM dual;
-- 교재 P. 79 LPAD(), RPAD() 함수
-- 교재 P. 80 LTRIM(), RTRIM(), TRIM() 함수
-- 교재 P. 82 REPLACE(char, search_string, replace_string) 함수
SELECT ename, REPLACE (ename, 'SC', '?*') 결과 FROM emp;
-- 교재 P. 82 TRANSLATE(expr, from_string, to_string)
-- TRANSLATE()함수는 문자열 하나를 일대일로 변환하는 작업을 수행한다.
SELECT ename, TRANSLATE(ename, 'SC', '?*') 결과 FROM emp;
-- 문제풀이
-- 문제1> player_t 선수중에서 이름이 1글자, 3글자 아닌 선수들의
-- 이름을 첫글자--두번째글자부터 남은글자 형태 나오도록
-- ex) 가--비, 히--카르도
-- 이름글자수 : length(player_name)
-- 이름 첫글자만 추출 : substr(player_name, 1, 1)
-- 이름 두번째 남은글자 추출 : substr(plaer_name, 2)
SELECT SUBSTR(player_name, 1, 1)||'--'||substr(player_name, 2)
FROM PLAYER_t
WHERE LENGTH(player_name) != 1 AND LENGTH(player_name) != 3;
-- WHERE length(player_name) NOT IN (1, 3, 5, 7) 등으로 표현 가능
-- 문제2> player_t 선수중에서 e_player_name에 E가 4번이상 나오는
-- 선수들의 이름, 영문이름 조회하기.
-- 1) LIKE 사용
SELECT player_name, e_player_name FROM PLAYER_T
WHERE e_player_name LIKE('%E%E%E%E%');
-- 2) instr함수 이용.
-- instr함수는 찾는 문자열의 index를 반환함. 찾는 문자열이 없으면 0
SELECT player_name, e_player_name, INSTR(e_player_name, 'E', 1, 4) 결과
FROM PLAYER_T
WHERE player_name LIKE '%희';
-- 정답 >>
SELECT player_name, e_player_name
FROM PLAYER_T
WHERE INSTR(e_player_name, 'E', 1, 4) > 0;
/* 문제3> player_t선수중 이름이 2글자인 선수들의 한글이름, 영문이름,
영문이름 중 첫번째공백 앞글자만 나오도록 조회하기.
ex) KIM, HO 인경우==> KIM,까지만
Gabriel Popescu 인경우==> Gabriel까지만 나오도록.
★힌트
A. 고객 email 정보에서 @ 앞부분만 추출 substr(email, 1, @위치-1)
- @위치 : instr(email, '@', 1, 1)
B. 고객 email 정보에서 @ 뒷부분만 추출 substr(email, @위치+1)
-- 영문이름 공백앞부분만 추출시 : substr(e_player_name, 1, 공백위치-1)
-- 영문이름에서 공백문자 나오는 위치 : instr(e_player_name, ' ', 1, 1)
*/
-- 정답 >>
SELECT player_name, e_player_name, SUBSTR(e_player_name, 1, INSTR(e_player_name, ' ', 1, 1)-1) AS 영문이름일부
FROM player_t
WHERE LENGTH(player_name) = 2;
-- 문제4> team_t테이블 이용해서 team_name, tel, tel정보를 한글로 표시해서 조회
-- ex)전화번호가 202-9998인경우==> 이영이-구구구팔
SELECT team_name, tel, REPLACE(tel, '123', '일이삼') FROM team_t;
-- 정답 >>>
SELECT team_name, tel, TRANSLATE(tel, '1234567890', '일이삼사오육칠팔구영') tel정보
FROM team_t;
-- 교재 p. 85 round() 함수는 length 미지정시 소수점 첫째자리에서 반올림 : 결과1
-- round(number, length) length 자리까지 결과값을 보여줌
SELECT ROUND(7677.567) 결과1, ROUND(7677.567, 2) 결과2, ROUND(7677.567, -3) 결과3
FROM dual;
-- 결과1 : 7678, 결과2 : 7677.57, 결과3 : 8000
-- ceil(), floor() 함수 : 정수값으로 올림/내림
SELECT CEIL(3.7) 결과1, FLOOR(3.7) 결과2 FROM dual;
-- 결과1 : 4, 결과 2 : 3
-- 숫자 계산하기
-- 100을 3으로 나눈 결과?
SELECT 100/3 FROM dual; -- 결과 : 33.33333333333333333333333333333333333333
-- 100을 3으로 나눈 몫?
SELECT TRUNC(100/3) FROM dual; -- 결과 : 33
-- 100을 3으로 나눈 나머지?
SELECT MOD(100, 3) FROM dual; -- 결과 : 1
-- 1/7788 ?
SELECT 1/7788 FROM dual; -- 결과 : 0.000128402670775552131484334874165382639959
SELECT SIGN(1/7788) FROM dual; -- 결과 : 1
-- 33*0 ?
SELECT 33*0 FROM dual; -- 결과 : 0
SELECT SIGN(33*0) FROM dual; -- 결과 : 0
-- 4-9999 ?
SELECT 4-9999 FROM dual; -- 결과 : -9995
SELECT SIGN(4-9999) FROM dual; -- 결과 : -1
/* 숫자함수
abs(n) : 절대값 계산
floor() : 내림
ceil() : 올림
power() : 승수 계산
sign() : 양수/음수 여부 반환 */
/* 회사에서 사용중인 날짜 데이터 저장 컬럼
A. 정산년도 : CHAR(4)
정산일자 : CHAR(8)
B. 주문일자 : date
C. 실험일자 : timestamp
단, 날짜 자료는 프로그램 화면세팅에 따라 일부정보만 display 한다.
sql*plus에서 보기로 합니다.
*/
-- 1. SQL*Plus 프로그램에서 날짜 정보 확인
-- SQL*Plus 프로그램사용시 날짜 정보 표시형태 지정방법
-- SQL*Plus 실행 후
SELECT SYSDATE FROM dual; -- 날짜 정보 확인 : 15/12/08
SELECT ename, hiredate FROM emp;
-- 화면세팅 정보 변경법
ALTER SESSION SET nls_date_fromat='yyy-mm=dd hh24:mi:ss';
SELECT SYSDATE FROM dual; -- 결과 : 015-12=08 11:43:12
-- 2. SQL GATE 프로그램에서 날짜 정보 확인
-- 도구 메뉴 -> 옵션에서 선택해서 변경
-- ********************************************
-- 날짜 데이터를 이용한 계산 작업 수행결과 확인
-- 1) 날짜 + 정수값 결과
SELECT SYSDATE, SYSDATE + 5 FROM dual;
SELECT SYSDATE, SYSDATE + 100 FROM dual;
-- 응용> 오늘날짜 현재시간기준 100분 후는 언제인지 조회
SELECT SYSDATE, SYSDATE + 1 FROM dual; -- 결과 +1일이 된다.
SELECT SYSDATE, SYSDATE + 1/24/60*100 FROM dual; -- 100분 후 시간
SELECT SYSDATE, SYSDATE + 1/(24*60*60) FROM dual; -- 1초 더하기
-- 2) 오늘날짜 현재시간 기준 10개월 후는 언제인지 조회
-- 30일 x 10개월 = 300일을 더함
SELECT SYSDATE, SYSDATE + 300 FROM dual; -- 날짜 계산 오류
-- ADD_MONTHS(날짜, 개월수) 함수를 이용하여 월 계산하도록 한다. ★
SELECT SYSDATE, SYSDATE + 300, ADD_MONTHS(SYSDATE, 10) FROM dual;
-- 3) 날짜 - 날짜 계산결과 확인 : 두날짜 사이 경과일 수
SELECT ename, SYSDATE - hiredate FROM emp;
-- 4) 두 날짜 사이에 몇 개월이 지났는지 구해서 알려주는 함수 : MONTHS_BETWEEN(날짜1, 날짜2)
SELECT ename, MONTHS_BETWEEN(SYSDATE, hiredate) FROM emp;
-- 5) 해당월의 마지막 일자가 언제인지 알려주는 함수
SELECT LAST_DAY(sysdate) FROM dual; -- 2015-12-31 오후 12:01:51
--**********************************************************************
-- 문제1> 포지션GK인 선수들의 이름, 생일, 70번째 생일날이 언제인지 조회하기
SELECT * FROM PLAYER_T;
-- 비교
SELECT player_name AS 이름, birth AS 생일, birth+1*365*70 AS "70생일"
FROM PLAYER_T
WHERE position = 'GK'; -- birth+1*365*70 : 잘못된 결과
-- 정답
SELECT player_name 이름, birth AS 생일, ADD_MONTHS(birth, 12*70) AS "70생일"
FROM PLAYER_T WHERE position = 'GK';
-- 별칭(AS) 처리시 숫자, 특수문자로 시작하는 별칭은 ""(큰따옴표로 묶을 것, 작은따옴표(X)) ★
-- 문제2> 선수이름, 생일, 만나이 조회
SELECT player_name, birth, ROUND(MONTHS_BETWEEN(SYSDATE, birth)/12) AS 만나이 FROM player_t;
-- 1) 몇개월 살았는지 구하기 형식> **세 @@월
-- **세 : 개월수를 12로 나눈 정수부분 TRUNC(개월수/12)
-- @@월 : 개월수를 12로 나눈 나머지 부분 MOD(개월수, 12)
SELECT player_name, birth, TRUNC(MONTHS_BETWEEN(SYSDATE, birth)/12) ||'세 ' ||
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, birth), 12))||'개월'
AS 나이정보 FROM player_t;
--**********************************************************************
/* 2일 오후 강의 */
-- 7 vs. '7' : 7은 숫자(오른쪽 정렬), '7'은 문자(왼쪽 정렬)로 인식한다.
SELECT '00700' 결과1, 00700 결과2 FROM dual;
-- 문자와 숫자의 덧셈
SELECT '00700' + 700 FROM dual; -- 결과 : 1400(숫자)
-- 문제
DESC PLAYER_T; -- 결과 : PLAYER_T 테이블 구조 VIEW, player_id type 확인
-- DB 내부처리과정 index에서 바로 검색해서 가져옴
SELECT player_name, player_id FROM PLAYER_T WHERE player_id='1997051'; -- 결과 : 고종수 '1997051'
-- TYPE 자동변환이 일어남. 속도 차이가 발생함.
SELECT player_name, player_id FROM PLAYER_T WHERE player_id=1997051; -- 결과 : 고종수 '1997051'
-- 교재 P.96 변환함수
-- 오라클은 연산을 수행하면서 자동형 변환을 수행한다. TO_NUMBER, TO_DATE, TO_CHAR ...
/*
NUMBER -> CHARACTER : TO_CHAR,
CHARACTER -> NUMBER : TO_NUMBER
DATE -> CHARACTER : TO_CHAR,
CHARACTER -> DATE : TO_DATE
NUMBER -> DATE, DATE -> NUMBER 직접 변환 불가
*/
--DATE -> CHAR (TO_CHAR) 사용시점
--날짜(년, 월, 일, 시, 분, 초) 정보 중 일부요소만 추출해서 사용시 : TO_CHAR(날짜, '필요한 정보)
-- SQL 작성전 알아둘 사항 : 오늘은 2015-12-08 입니다.
SELECT SYSDATE, TO_CHAR(SYSDATE, 'day') FROM dual; -- 결과 : tuesday
SELECT SYSDATE, TO_CHAR(SYSDATE, 'd') FROM dual; -- 주 기준 몇째날(1~7 : 일~토)
SELECT SYSDATE, TO_CHAR(SYSDATE, 'dd') FROM dual; -- 월 기준 몇째날 (08)
SELECT SYSDATE, TO_CHAR(SYSDATE, 'ddd') FROM dual; -- 365일 중 몇째날 (342)
SELECT SYSDATE, TO_CHAR(SYSDATE, 'yyyymm') FROM dual; -- 년, 월 표기 (201512)
SELECT SYSDATE, TO_CHAR(SYSDATE, 'sssss') FROM dual; -- 자정을 기준으로 하루 단위의 초 표시
/* 옵션 -- 교재 P.99 ★
날짜 포맷 요소
YYYY:2015, YYY:015, YY:15, Y:3
YEAR : Two thousand three
SYYY:-1120(BC인 경우, 음수로 표시)
Q : 1, 2, 3, 4(4분기)
MM : 01, 02 .... 12 (두자리 월)
MONTH : January, February, December ...
*/
-- NUMBER -> CHAR (TO_CHAR) 사용시점 : 숫자값 표시형태지정(천단위, 추가, 마이너스값:123-, <123>
SELECT TO_CHAR(12345, '999,999') FROM dual; -- 결과 : 12,345
SELECT TO_CHAR(12345, '99,9999') FROM dual; -- 결과 : 1,2345
SELECT TO_CHAR(12345, '000,000') FROM dual; -- 결과 : 012,345 // 자릿수 없을 경우 0으로 패딩처리
SELECT TO_CHAR(12345, '111,111') FROM dual; -- 결과 : 012,345 // 자릿수 없을 경우 0으로 패딩처리
SELECT TO_CHAR(-12345, '999,999') FROM dual; -- 결과 : -12,345
SELECT TO_CHAR(-12345, '999,999mi') FROM dual; -- 결과 : 12,345-
SELECT TO_CHAR(1234567, '000,000') FROM dual; -- 결과 : ######## (자릿수가 부족할 경우 ########로 return함)
SELECT TO_CHAR(1234567, '000,000,000') FROM dual; -- 결과 : 001,234,567 (남은자리 0으로 패딩)
SELECT TO_CHAR(1234567, '999,999,999') FROM dual; -- 결과 : 1,234,567 (패딩하지 않음)
-- # 업무팁! 화폐단위 + 화폐단위 하지말고, 숫자 + 숫자 의 결과값을 화폐단위화 할것 ★
SELECT * FROM PLAYER_T;
-- 문제 1. player_t K01팀 선수들의 이름, 생일, 태어난 요일정보 조회
SELECT player_name, birth, TO_CHAR(birth, 'day') FROM PLAYER_T;
-- 문제 2. 일요일에 태어난 선수들의 이름, 생일 조회
-- TO_CHAR(birth, 'day')값이 자동 패딩되어 보이지않는 공백이 포함되어 있음
-- 따라서 결과값 비교시 TRIM()함수를 사용하여 공백 삭제
SELECT player_name, birth, LENGTH(TO_CHAR(birth, 'day')) LENGTH
FROM PLAYER_T
WHERE TRIM(TO_CHAR(birth, 'day')) = 'sunday';
-- RPAD(TO_CHAR(birth, 'day'), 10, '*') : sunday *, thursday *,
-- RPAD 사용 : 문자열을 10글자까지 '*' 패딩처리함 ex) sunday****, thursday**
SELECT player_name, birth, LENGTH(TO_CHAR(birth, 'day')) LENGTH, RPAD(TO_CHAR(birth, 'day'), 10, '*') 결과
FROM PLAYER_T
WHERE TRIM(TO_CHAR(birth, 'day')) = 'sunday';
-- 요일이 영문(sun)으로 결과값을 가져오기
SELECT player_name, birth, TO_CHAR(birth, 'd')
FROM PLAYER_T
WHERE TO_CHAR(birth, 'dy') = 'sun';
-- 요일이 영문이나 한글로 작성되어 있을 경우
SELECT player_name, birth, TO_CHAR(birth, 'd')
FROM PLAYER_T
WHERE TO_CHAR(birth, 'dy') = 'sun' OR TO_CHAR(birth, 'dy') = '일';
-- 개발자의 경우 TO_CHAR 'd' 옵션을 사용하여 작성 Best Choice !! ★
SELECT player_name, birth, TO_CHAR(birth, 'd')
FROM PLAYER_T
WHERE TO_CHAR(birth, 'd') = '1'; -- 1 : 일, 2 : 월, 3 : 화, 4 : 수, 5 : 목, 6 : 금, 7 : 토
/*
********************************************************
교재 P.101 TO_DATE 함수
* 이체시간 등록 / 주민시간 등록
---> 오늘날짜 현재시간으로 처리시 sysdate 값 사용함
2012년 11월 날짜로 처리하고 싶은 경우
과거/미래 특정날짜정보로 처리하고 싶은 경우
TO_DATE함수 사용함
ex) '201211' ---> TO_DATE('201211', 'yyyymm')
# 시스템이 '201211'의 값을 20시12분11초로 해석하거나 20년도 12월 11일 로 자의석 해석이 가능
# 이것을 방지하기 위하여 TO_DATE()함수를 사용하여 제한
*/
/*
==> 날짜정보(년도/월/일 시/분/초) 만들때 ★
년도/월 일자 시분초 정보 중 사용자가 지정하지 않은 요소는 기본값으로 설정됨
(1) 년도/월 기본값 : sysdate의 년도/월
(2) 일자 시분초 정보기본값 : 01일 오전12시 00분 00초
*/
SELECT TO_DATE('12', 'yy') 결과1,
TO_DATE('12','dd') 결과2,
TO_DATE('12','mi') 결과3
FROM dual;
-- 결과1 : 2012-12-01 오전 12:00:00
-- 결과2 : 2015-12-12 오전 12:00:00
-- 결과3 : 2015-12-01 오전 12:12:00
/* to_date 함수로 날짜 만들때 주의사항 */
SELECT TO_DATE('13','dd') FROM dual; -- 결과 : 2015-12-13 오전 12:00:00
SELECT TO_DATE('13','mm') FROM dual; -- ORA-01843: not a valid month
SELECT TO_DATE('1140','mmdd') FROM dual; -- ORA-01839: date not valid for month specified
/* 교재 p.102 - 년도 2자리만 사용시 년도 4자리로 구성할때 */
SELECT TO_DATE('77', 'yy') 결과1, TO_DATE('33', 'yy') 결과2 FROM dual;
-- yy 옵션(★)은 오늘날짜 앞2자리 사용해서 년도 구성함
SELECT TO_DATE('77', 'rr') 결과1, TO_DATE('33', 'rr') 결과2 FROM dual;
-- rr옵션(★)은 00-49까지는 20을, 50-99까지는 19를 년도 앞2자리로 사용
/* ******************************************************** */
-- 알아둘 사항 :
-- TO_DATE('지정날짜(년/월/일)', 'yyyymmdd')로 날짜 데이터 생성시
-- 기본시간은 12:00:00 자정시간으로 default 되어 생성됨
-- 날짜 데이터 추출시 주의할 것(방법 4-2. 추천!!)★★★★★
SELECT * FROM PLAYER_T;
-- 문제 1> player_t 선수들의 이름, 60번째 생일이 무슨요일인지 조회
SELECT player_name, TO_CHAR(ADD_MONTHS(birth, 12*60), 'day') 생일요일 FROM PLAYER_T;
-- 문제 2> 1973년 12월 25일에 태어난 선수들만 선수이름 조회
-- 방법 1.
SELECT player_name FROM PLAYER_T WHERE birth = '1973-12-25';
-- 방법 2. 자정시간00분00초 자료만 추출해서 보여주기 때문에 신뢰성 떨어짐
SELECT player_name
FROM PLAYER_T
WHERE birth=TO_DATE('19731225', 'yyyymmdd');
-- 방법 3. birth 컬럼을 직접 변환할 경우 index 활용이 안되기 때문에 속도가 느림
SELECT player_name FROM PLAYER_T
WHERE TO_CHAR(birth,'yyyymmdd') = '19731225';
-- 방법 4-1.
SELECT player_name FROM PLAYER_T
WHERE birth BETWEEN TO_DATE('19731225', 'yyyymmdd') AND TO_DATE('19731225:23:59:59', 'yyyymmdd:hh24:mi:ss');
-- 방법 4-2.
SELECT player_name FROM PLAYER_T
WHERE birth BETWEEN TO_DATE('19731225','yyyymmdd') AND TO_DATE('19731226','yyyymmdd');
-- 문제 3> 1975년도에 태어난 선수들의 이름, 생일 조회(★★★)
-- 방법 1.
SELECT player_name, birth FROM player_t where TO_CHAR(birth, 'yyyy')='1975';
-- 방법 2.
SELECT player_name, birth FROM PLAYER_T
WHERE birth >= TO_DATE('1975','yyyy') AND birth < TO_DATE('1976','yyyy');
-- TO_DATE()함수 사용시 기본생성되는 월, 일, 시, 분, 초를 꼭 확인할 것
-- TO_DATE('1975','yyy')를 생성하면 1975-12-01 오전 12:00:00를 return함
-- TO_DATE 함수값 확인해보기
SELECT TO_DATE('1975','yyyy') FROM dual; -- 결과 : 1975-12-01 오전 12:00:00
SELECT TO_DATE('1976','yyyy') FROM dual; -- 결과 : 1976-12-01 오전 12:00:00
-- 문제 4> 2088년 11월 27일은 무슨 요일인가?
-- 비교>
SELECT TO_CHAR('2088-11-27','day')
FROM dual; -- ORA-01722: invalid number
-- 정답>
SELECT TO_CHAR(TO_DATE('20881127','yyyymmdd'), 'day') "2088년11월27일은 무슨 요일"
FROM dual; -- 결과 : saturday
SELECT TO_CHAR(TO_DATE('20881127','yyyymmdd'), 'Day') "2088년11월27일은 무슨 요일"
FROM dual; -- 결과 : Saturday
SELECT TO_CHAR(TO_DATE('20881127','yyyymmdd'), 'DAY') "2088년11월27일은 무슨 요일"
FROM dual; -- 결과 : SATURDAY
/*
-------------
주문일자 정보
-------------
1973-12-24 오전11시25분30초
1973-12-25 오전7시15분27초
1973-12-25 오전11시35분20초
1973-12-25 오전12시31분20초
1973-12-25 오전16시19분20초
1973-12-26 오전18시23분20초
DB상에 저장되어 있는 데이터가 날짜데이터인지 문자데이터인지 확인할 것(★)
날짜데이터의 경우 문자타입 검색어로 직접 비교할 수 없음
반드시 데이터 변환을 통해 원하는 데이터를 가져올 것
*/
-- 불가능한 구문
SELECT * FROM shop WHERE 주문일자 = '1973-12-25'; 구문으로 해당 날짜데이터를 가져올 수 없음
-- (날짜타입) (문자타입) --> 비교 불가
-- 가능한 구문이지만, 추출 데이터가 100% 신뢰할 수 없음(추출데이터가 들쑥날쑥함)
SELECT * FROM shop WHERE 주문일자 = TO_DATE('1973-12-25','yyyy-mm-dd');
-- 그러나 '1973-12-25 12:00:00'데이터를 가져오게 되어 추출데이터가 신뢰 불가
-- 주문일자 정보에서 TO_CHAR()로 '년/월/일' 정보를 뽑아서 비교하는 구문
-- 가능한 구문이지만, index 활용이 불가하여 속도가 느림
SELECT * FROM shop WHERE TO_CHAR(주문일자,'yyyymmdd')='19731225';
-- index 활용이 불가하여 속도가 느린 윗 구문보다는 TO_DATE()를 활용하여 구문 작성
-- 가장 유효한 구문(★)
SELECT *
FROM shop
WHERE 주문일자 >= TO_DATE('1973-12-25','yyyy-mm-dd') AND 주문일자 < TO_DATE('1973-12-26','yyyy-mm-dd');
-- 1. NULL 값을 다른 값으로 치환해주는 함수 */
-- NVL(비교대상, null일 때 변경값)
SELECT ename, sal, comm, NVL(comm, -77) 결과 FROM emp;
-- 결과
-- SMITH 800 (null) -77
-- ALLEN 1600 300 300
-- JONES 2975 (null) -77
SELECT ename sal, comm, sal+comm FROM emp;
-- 숫자 + null 값은 null 을 반환하기 때문에
-- 데이터값이 (null) 일 경우 0으로 치환해서 봉급+보너스 결과를 보임
SELECT ename, sal, comm, sal+NVL(comm, 0) FROM emp;
-- 비교>
SELECT ename, comm, NVL(comm, '미정') FROM emp; -- ORA-01722: invalid number
-- NVL()함수는 변경하고자 하는 데이터의 인수의 타입이 일치하여야 한다.
-- NVL(변경전 데이터, 변경후 데이터)
SELECT ename, comm, NVL(TO_CHAR(comm), '미정') FROM emp; -- comm 타입변환 TO_CHAR
-- 2. NVL2(비교대상, null아닐때 값, null일때 값)
SELECT ename, comm, NVL2(comm, comm+700, 300) 결과 FROM emp;
SELECT ename, comm, NVL2(TO_CHAR(comm), TO_CHAR(comm+700), '미정') 결과 FROM emp;
-- 3. NULLIF(exp1, exp2) : 비교대상 이 지정한 비교값이랑 같은경우 null로 표시
-- <expr1> = <expr2>일 경우 NULL을, 그렇지 않을 경우 <expr1>을 리턴한다.
SELECT ename, deptno, nullif(deptno, 30) FROM emp ;
-- 사용할 경우는 ?
/*
배송테이블 -> 배송상태 '완료'인것 보고 싶지 않을때
--------------------------------------------------
배송번호 배송상태 nullif(배송상태, '완료')
--------------------------------------------------
1 완료
1 완료
3 완료
... ...
... ...
... ...
170 반품중 반품중
... ...
... ...
199 반품중 반품중
--------------------------------------------------
*/
SELECT player_name, solar, nullif(solar, '1')
FROM PLAYER_T
WHERE position = 'GK';
-- COALESCE 함수 : COALESCE(3차협상, 2차협상, 1차협상, 500)
SELECT ename, comm, sal, COALESCE(comm, sal, 500) result FROM emp;
-- CASE 표현식
SELECT player_name, height,
CASE WHEN height<185 THEN 'C'
WHEN height<190 THEN 'B'
ELSE 'A' END 결과
FROM PLAYER_T
WHERE position='GK';
SELECT ename, sal, deptno, CASE WHEN deptno=20 THEN 'A팀'
WHEN deptno=30 THEN 'B팀'
WHEN deptno=40 THEN 'C팀'
ELSE 'F팀'
END
FROM emp;
-- DECODE 함수
-- DECODE(비교대상, 값1, 결과1, 값2, 결과2, ...., 값N, 결과N, 기타결과) 형식
SELECT ename, sal, deptno,
DECODE(deptno, 20, sal+200, 30, sal+300, 40, sal+400, sal)
FROM emp;
SELECT player_name, height, DECODE(SIGN(height-185), -1, 'C',
DECODE(SIGN(height-190), -1, 'B', 'A'))
FROM PLAYER_T
WHERE position='GK';
-- 문제
-- emp 사원이름, sal, sal 값이 2500 이상은 'A팀', 2500 미만은 'B팀'으로 표시
-- CASE 표현식을 이용한 구문
SELECT ename, sal, CASE WHEN sal>=2500 THEN 'A팀'
ELSE 'B팀' END 결과
FROM emp;
-- DECODE()함수를 이용한 구문 (★)
SELECT ename, sal, DECODE(SIGN(sal-2500), 1, 'A팀', 0, 'A팀', -1, 'B팀') 결과
FROM emp; --SIGN()함수가 양수이면 1, 0이면 0, 음수이면 -1)
-- 줄여쓰면
SELECT ename, sal, DECODE(SIGN(sal-2500), -1, 'B팀', 'A팀') 결과
FROM emp; --SIGN()함수가 양수이면 1, 0이면 0, 음수이면 -1)
-- DECODE()함수를 이용한 구문
SELECT ename, sal, DECODE(TRUNC(sal/2500), 0, 'B팀', 'A팀') 결과
FROM emp;
-- DECODE() 함수는 예전에 많이 썼으며, 현재 CASE 구문으로 많이 변환됨
-- 교재 P.117 CHAPTER 4. 그룹함수
-- 그룹함수 특징 : 여러 Row를 묶어서 처리후 결과 표시함
-- 별도 지정없으면 tbl 전체를 하나로 묶어서 처리
SELECT LOWER(ename) FROM emp;
-- 14개 ALL PRINT
SELECT MIN(ename) FROM emp;
-- 결과 : ADAMS 전체값 중 제일 작은 문자열 값(ALLEN, ADAMS 중 ADAMS return!)
SELECT * FROM emp WHERE ename LIKE 'A%';
-- 결과 : ALLEN, ADAMS
SELECT COUNT(ename), MAX(hiredate), SUM(sal) FROM emp;
-- 14, 1983-01-12 오전 12:00:00 29025
SELECT COUNT(ename), MIN(hiredate), SUM(sal) FROM emp;
-- 14, 1980-12-17 오전 12:00:00 29025
SELECT COUNT(empno), COUNT(mgr), COUNT(comm) FROM emp;
-- COUNT(empno) : 14, COUNT(mgr) : 13, COUNT(comm) : 4
-- 그룹함수 계산시 null 값 자동제외함
SELECT comm FROM emp;
SELECT NVL(comm, 0) FROM emp; -- NVL()함수 : null 값을 가진 경우 0 으로 대체
SELECT SUM(NVL(comm, 0)) 결과1, SUM(comm) 결과2 FROM emp; -- 결과 : 2200, 2200
-- 그룹함수 계산시 null 값 자동제외하여 계산함
-- 안전성을 위해서는 SUM(NVL(comm, 0))
-- 속도를 위해서는 NVL() 과정없이 SUM(comm) 결과를 사용할 것(★)
-- NVL()은 데이터수만큼 데이터 대체과정을 거침(ex) 100만개 -> 100만번 check)
SELECT AVG(NVL(comm, 0)) 결과1, AVG(comm) 결과2 FROM emp;
-- 결과1 값 : 157.142857142857142857142857142857142857
-- 결과2 값 : 550
-- 실제 시험응시자의 경우 0점, 그 이상점수 처리하는데,
-- 시험접수했으나 시험미응시자는 시험점수가 0이 아니라, 미응시로 NULL 값
-- 평균 계산할 경우 실제 시험응시자를 대상으로 평균을 내야함
-- 따라서 AVG(NVL(comm, 0))을 쓰면 안되며, 반드시 AVG(comm)을 써야 함(★)
-- A. 전체 부서의 합계
SELECT SUM(sal) FROM emp; -- 결과 : 29025
-- B. 특정 부서의 합계 ex) depno = 10 인 부서의 합계
SELECT SUM(sal) FROM emp WHERE deptno=10; -- 결과 : 8750
-- C. 작은 그룹으로 자동으로 나누어서 처리시 GROUP BY 절 사용
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;
