SQL-집합연산자, 문자, 숫자, 날짜함수

* 집합연산

SELECT

[UNION ALL | UNION | INTERSECT | MINUS]

SELECT ...

 

- 사실상 거의 UNION빼고는 사용하는 경우가 없다.

 

[예제 1]2000년 이후에 입사한 사원과 부임한 교수의 명단을 검색

SELECT pno 번호, pname 이름, hiredate 입사일_부임일

FROM professor

WHERE hiredate >= '2000/01/01'

UNION ALL

SELECT eno, ename, hdate

FROM emp

WHERE hdate >= '2000/01/01';

 

[예제 2]제갈씨 성을 가진 사원 중에 지원 업무를 하지 않는 사원을 검색

SELECT eno, ename ,job

FROM emp

WHERE ename LIKE '제갈%'

MINUS

SELECT eno, ename, job

FROM emp

WHERE job = '지원';

 

[예제 3]화학, 물리학과 학생들 중 학점이 3.0 이상인 학생을 검색

SELECT sno 학번, sname 이름, major 학과, avr 학점 FROM student

WHERE major IN ('화학', '물리')

INTERSECT

SELECT sno, sname, major avr FROM student

WHERE avr >= 3;

 

[실습 1]화학과 학생과 교수를 검색(집합 연산자를 이용)

SELECT pno 교번or학번, pname 이름, section 학과

FROM professor

WHERE section = '화학'

UNION ALL

SELECT sno, sname, major

FROM student

WHERE major = '화학';

 

[실습 2]정교수의 명단과 모델링이 업무인 직원의 이름, 입사일(부임일)을 검색

SELECT pno 교번or사번, pname 이름, orders 지위또는업무, hiredate 부임일or입사일

FROM professor

WHERE orders = '정교수'

UNION ALL

SELECT eno, ename, job, hdate

FROM emp

WHERE job = '모델링';

 

* 단일 행 함수

단일 행 함수의 종류

- 문자 함수

- 숫자 함수

- 날짜 함수

- 변환 함수

- 일반 함수

 

* 문자 함수

대소문자 변환 함수

LOWER :  문자열을 소문자로 변환

UPPER : 문자열을 대문자로 사용

INITCAP :  첫 문자만 대문자로, 나머지는 소문자로 변환

 

[예제 1]ERP 부서가 있는 지역을 검색

SELECT loc ERP_부서_지역

FROM dept

WHERE LOWER(dname) = 'erp';

 

[예제 2] 'XX 부서는 XX에 위치합니다.'형식으로 부서 정보를 검색

SELECT INITCAP(dname) || '부서는 ' || loc ||'에 위치합니다.' 부서_위치

FROM dept;

 

문자 연산 함수

CONCAT : 두 개의 문자열을 연결

SUBSTR : 문자열 내에 지정된 위치의 문자열 반환

LENGTH(LENGTHB) : 문자열의 길이나 BYTER를 반환

INSTR : 지정된 문자의 위치를 반환

TRIM " 접두어나 접미어를 잘라낸다. 제외문자를 지정하지 않으면 공백문자를 잘라낸다.

LPAD, RPAD : 지정된 문자열의 길이 만큼 빈 부분에 문자를 채운다.

 

[예제 3]부서의 명과 위치를 하나의 컬럼으로 검색

SELECT CONCAT(dname, ' ' || loc) 부서_위치

FROM dept;

 

[예제 4]부서명과 문자열의 길이를 출력

SELECT dname, LENGTH(dname), LENGTHB(dname)

FROM dept;

 

[예제 5]substr 함수를 이용해서 컬럼에 일부 내용만을 검색

SELECT ename,

SUBSTR(ename, 2),

SUBSTR(ename, -2),

SUBSTR(ename, 1, 2),

SUBSTR(ename, -2, 2)

FROM emp;

 

[예제 6]다양한 방법을 'a'가 나오는 위치를 출력

SELECT INSTR('database', 'a'),

INSTR('database', 'a', 3),

INSTR('database', 'a', 1, 3)

FROM dual;

 

[예제 7]TRIM 함수를 이용, 다양한 방법으로 문자열을 검색

SELECT TRIM('남' from '남기남'),

TRIM(leading '남' from '남기남'),

TRIM(trailing '남' from '남기남'),

TRIM(' 남기남 ')

FROM dual;

 

[예제 8]이름과 급여를 각각 10 컬럼으로 검색

SELECT RPAD(ename, 10, '*'), LPAD(sal, 10, '*')

FROM emp;

 

[예제 9]부서명의 마지막 글자를 제외하고 검색

SELECT dname, SUBSTR(dname, 1, LENGTH(dname)-1) dname

FROM dept;

 

문자 치환 함수

TRANSLATE : 문자 단위 치환된 값을 반환

REPLACE : 문자열 단위 치환된 값을 반환. 실제로는 REPLAC를 훨씬더 많이 사용

 

[예제 10]TRANSLATE와 REPLACE 비교

SELECT TRANSLATE('World of Warcraft', 'Wo', '--') translate,

REPLACE('Wolrd of Warcraft', 'Wo', '--') replace

FROM dual;

 

 

[실습]

1. 이름이 두 글자인 학생의 이름을 검색

SELECT sno 학번, sname 이름

FROM student

WHERE LENGTH(sname) = 2;

 

2. '강'씨 성을 가진 학생의 이름을 검색

SELECT sno 학번, sname 이름

FROM student

WHERE SUBSTR(sname, 1, 1)='강';

 

3. 교수의 지위를 한글자로 검색(ex 조교수 -> 조)

SELECT pno 교수번호, pname 이름, SUBSTR(orders, 1, 1) 지위

FROM professor;

 

4. 일반 과목을 기초 과목으로 변경해서 모든 과목을 검색(ex 일반화학 - > 기초화학)

SELECT cno 과목번호, REPLACE(cname, '일반', '기초') 변경된_과목명

FROM COURSE;

 

5. student 테이블의 sname컬럼에 데이터가 입력될때 문자열 마지막에 공백이 추가된 경우 이를 제외하고 검색

SELECT sno 학번, TRIM(sname) 학번

FROM student;

 

6. 직원의 연봉을 10자리로 검색. 단 공백은 임의의 채움문자로 한다.

SELECT eno 사번, ename 이름, LPAD(sal*12+NVL(comm, 0), 10, '*') 연봉

FROM emp;

 

7. 학생의 이름을 검색. 단, 성이 '심'인 학생은 성을 '사마'로 바꿔서 검색

SELECT sno 학번, CONCAT(REPLACE(SUBSTR(sname, 1, 1) , '심', '사마'), SUBSTR(sname, 2)) 이름

FROM student;

 

* 숫자 함수

ROUND : n자리까지 반올림 ex)ROUND(123.4567, 3) -> 123.467

TRUNC : n자리 미만을 절삭 

MOD : m을 n으로 나눈 나머지를 계산

POWER : m의 n승을 계산한다. 거의 사용되지 않음.

CEIL : m보다 큰 가장 작은 정수를 찾는다.

FLOOR : m보다 작은 가장 큰 수를 찾는다.

ABS : m의 절대 값을 계산한다.

SQRT : m의 제곱근을 계산한다. 거의 사용되지 않음.

SIGN : m이 음수일때 -1, 양수일때 1, 0이면 0을 반환

 

[예제 2]10번 부서의 연봉을 계산(단, 100 단위 미만은 절삭)

SELECT eno, ename, TRUNC(sal*12+NVL(comm, 0), -2) 연봉 // -2는 100단위 미만 절삭이기 때문에

FROM emp

WHERE dno = '10';

 

* 날짜 함수와 날짜 연산의 이해

[예제 3] 현재 날짜를 검색하고 날짜 출력 양식을 수정

ALTER SESSION SET nls_date_format = 'YYYY/MM/DD:HH24:MI:SS';

SELECT sysdate FROM dual;

ALTER SESSION SET nls_date_format = 'YYYY/MM/DD';

SELECT sysdate FROM dual;

 

[예제 4] 오늘 날짜, 입사 일자, 입사일로부터 오늘까지 기간, 입사일 이후 100일째 되는날 등을 검색

SELECT sysdate 오늘, hdate 입사일, TRUNC(sysdate)-TRUNC(hdate)+1 근무일, hdate+99 "100일"

FROM emp;

 

* 날짜 함수

ROUND : 형식에 맞춰 반올림한 날짜를 반환

TRUND : 형식에 맞춰 절삭한 날짜를 반환

MONTHS_BETWEEN : 두 날짜간의 기간을 월 수로 계산. MONTHS_BETWEEN(날짜1, 날짜2)

ADD_MONTHS : 날짜에 n달을 더한 날짜 계산

NEXT_DAY : 날짜 이후 지정된 요일에 해당하는 날짜를 계산. 한글을 지원한다 ex) NEXT_DAY('2013/07/14', '일요일')

LAST_DAY : 날짜를 포함한 달의 마지막 날짜를 계산한다. 

 

[예제 5]숫자와 날짜를 반올림하거나 잘라내고 출력해본다.

SELECT sysdate,

ROUND(sysdate, 'YY') 년,

ROUND(sysdate, 'MM') 월,

ROUND(sysdate, 'DD') 일

FROM dual;

SELECT sysdate,

TRUNC(sysdate, 'YY') 년,

TRUNC(sysdate, 'MM') 월,

TRUNC(sysdate, 'DD') 일

FROM dual;

 

[예제 6]문시현이 오늘까지 일한 일수를 검색

SELECT ename, TRUNC(sysdate)-TRUNC(hdate)+1 DAY

FROM emp

WHERE ename = '문시현';

 

[예제 7] 20번 부서 직원들이 현재까지 근무한 개월 수 검색

SELECT eno, ename, TRUNC(MONTHS_BETWEEN(sysdate, hdate)) 근무_개월

FROM emp

WHERE dno = '20';

 

[예제 8] 20번 부서 직원들이 입사 100일째 되는 날과 10년째 되는 날을 검색

SELECT eno, ename, hdate 입사일,

hdate+99 "100일", ADD_MONTHS(hdate, 120) "10년"

FROM emp

WHERE dno = '20';

 

[예제 9] 20번 부서 직원들이 입사한 이후 첫 번째 일요일 검색

SELECT eno, ename, hdate, NEXT_DAY(hdate, '일요일') Sunday

FROM emp

WHERE dno = '20';

 

[예제 10]20번 부서 직원들이 입사한 달의 마지막 날짜와 입사한 달 근무 일수를 검색

SELECT eno, ename, hdate,

LAST_DAY(hdate) 마지막날, LAST_DAY(TRUNC(hdate))-TRUNC(hdate)+1  "입사한 달 근무일수"

FROM emp

WHERE dno = '20';

 

[실습]

1. 교수들이 부임한 달에 근무한 일수는 몇 일인지 검색

SELECT pno 교번, pname 이름, hiredate 부임날, LAST_DAY(TRUNC(hiredate))-TRUNC(hiredate)+1 입사한_달_근무일수

FROM professor;

 

2. 교수들이 오늘까지 근무한 주가 몇 주인지 검색

SELECT pno 교번, pname 이름, hiredate 부임일, TRUNC((TRUNC(sysdate)-TRUNC(hiredate)+1)/7) 근무한_주

FROM professor;

 

3. 1991년에서 1995년 사이에 부임한 교수를 검색

SELECT pno 교번, pname 이름, hiredate 부임일

FROM professor

WHERE TRUNC(hiredate) BETWEEN '1991/01/01' AND '1995/12/31'

ORDER BY hiredate;

 

4. 학생들의 4.5 환산 평점을 검색(단, 소수점 이하 둘째자리 까지)

SELECT sno 학번, sname 이름, ROUND(avr*4.5, 2) "4.5 환산평점"

FROM student;

 

5. 사원들이 일한 날짜에 대해서만 급여를 받는다면 현재 받는 급여가 입사시 급여라고 할때 입사한 달의 급여를 검색

SELECT eno 사번, ename 이름, sal 연봉, hdate 입사일, LAST_DAY(hdate) 입사달의_마지막날,

LAST_DAY(TRUNC(hdate))-TRUNC(hdate,'MM')+1 첫달날짜수,
LAST_DAY(TRUNC(hdate))-TRUNC(hdate)+1 첫달근무일,
ROUND(sal*(LAST_DAY(TRUNC(hdate))-TRUNC(hdate)+1)/(LAST_DAY(TRUNC(hdate))-TRUNC(hdate,'MM')+1)) 지급액
FROM emp;

 

6. 오늘까지 사원들의 근무 기간을 몇 년, 몇 개월, 몇 일인지 검색

SELECT ename 이름, hdate 입사일, sysdate 오늘,

TRUNC(MONTHS_BETWEEN(sysdate, hdate)/12)||'년 '||
TRUNC(MOD(MONTHS_BETWEEN(sysdate, hdate),12))||'개월 '||
TRUNC(sysdate-ADD_MONTHS(hdate,TRUNC(MONTHS_BETWEEN(sysdate, hdate)))+1)||'일' 근무_기간
FROM emp;