sql 함수...펌
■ SQL 함수 개념
기존의 프로그래밍 언어에서 함수를 사용하듯이 SQL 언어에서도 다양한 종류의 SQL 함수를 제공한다.
■ SQL 함수의 유형
단일 행 함수 : 입력 값이 단일 행이며 결과값 또한 하나를 반환
복수 행 함수 : 입력 값이 복수이며 결과값은 하나를 반환
■ 대 소문자 변환 함수
예제) 학생 테이블에서 학번이 ‘20101’ 인 학생의 사용자 아이디를 소문자와 대문자로 변환하여 출력하여라.
SQL) select userid, lower(userid), upper(userid)
from student
where studno=20101;
■ 문자열 길이 반환 함수
예제) 부서 테이블(department)에서 부서 이름의 길이를 문자수와 바이트수로 각각 출력하여라
SQL) SELECT dname, LENGTH(dname), LENGTHB(dname)
FROM department;
■ 문자 조작 함수
1. SUBSTR 함수
예제
학생 테이블에서 1학년 학생의 주민등록번호에서 생년월일과 태어난 달을 추출하여 이름, 주민등록번호, 생년월일, 태어난 달을 출력하여라
1 select name, idnum, substr(idnum,1,6) birth_date, substr(idnum,3,2) birth_mon
2 from student
3 where grade=1;
실습문제 1 : 학생 테이블의 전화번호 칼럼에서 지역번호를 출력하여라.
1 select tel, substr(tel,1,(instr(tel,')',1,1))-1) locno
2 from student
1 select rtrim(substr(tel,1,3),')')
2 from student
실습문제2 : 학생테이블에서 userid 에서 2번째 a의 위치가 5번째인 학생의 name,userid 를 출력하세요
1 select name, userid
2 from student
3 where instr(userid,'a',1,2)=5;
2. LPAD, RPAD 함수
교수 테이블에서 직급 칼럼의 왼쪽에 ‘*’ 문자를 삽입하여 10바이트로 출력하고 교수 아이디 칼럼은 오른쪽에 ‘+’ 문자를 삽입하여 12 바이트로 출력하여라.
1 select position, lpad(position,10,'*') lpad, userid, rpad(userid,12,'+') rpad
2 from professor;
문제 : TDEPT 테이블의 dept_name 컬럼을 아래처럼 출력되게 하세요
1 select dept_name, lpad(dept_name,10,'123456')
2 from TDEPT
문제 : TDEPT 테이블의 DEPT_NAME 컬럼을 아래 예시처럼 출력 되게 쿼리를 작성하세요
1 select dept_name, rpad(dept_name,10,substr('1234567890',lengthb(dept_name)+1))
2 from TDEPT
3. REPLACE 함수
첫 번째 인수를 두 번째 인수로 바꾼다
문법) Replace(컬럼 또는 문자열,문자1,문자2);
문제1) student 테이블의 name 컬럼에서 성 부분을 # 처리하고 이름만 보이게 출력하세요.
1 select name, replace(name, substr(name,1,1),'#')
2 from student;
문제2) student 테이블의 idnum 컬럼에서 주민번호 뒷자리 7자리를 * 로 보이게 출력하세요
1 select idnum, replace(idnum,substr(idnum,7,7),'*******')
2 from student
replace는 스트링입력을 반복하지 않는다.
국번만 ###으로 바꾸기
1 select NAME, TEL, replace(tel,substr(tel,instr(tel,')',1,1)+1,3),'###') REPLACE
2 from student
■ 숫자함수
1. ROUND 함수 예제
교수 테이블에서 101번 학과 교수의 일급을 계산(월 근무일은 22일)하여 소수점 첫째 자리와 셋째 자리에서 반올림 한 값과 소수점 왼쪽 첫째 자리에서 반올림 한 값을 출력하여라.
SQL> SELECT name, sal, sal/22, ROUND(sal/22), ROUND(sal/22,2),
ROUND (sal/22,-1)
FROM professor
WHERE deptno = 101;
2. TRUNC 함수 예제
교수 테이블에서 101번 학과 교수의 일급을 계산(월 근무일은 22일)하여 소수점 첫째 자리와 셋째 자리에서 절삭한 값과 소숫점 왼쪽 첫째자리에서 절삭한 값을 출력하여라.
SQL> SELECT name, sal, sal/22, TRUNC(sal/22), TRUNC(sal/22,2), TRUNC(sal/22,-1)
FROM professor
WHERE deptno=101;
3. MOD 함수 예제
교수 테이블에서 101번 학과 교수의 급여를 보직수당으로 나눈 나머지를 계산하여 출력하여라.
SQL> SELECT name, sal, comm, MOD(sal,comm)
FROM professor
WHERE deptno = 101;
4. CEIL,FLOOR 함수 예제
19.7보다 큰 정수 중에서 가장 작은 정수와 12.345보다 작은 정수 중에서 가장 큰 정수를 출력하여라
SQL> SELECT CEIL(19.7),FLOOR(12.345)
FROM dual;
■ 날짜 함수
날짜 계산
예) 교수번호가 9908인 교수의 입사일을 기준으로 입사 30일 후와 60일 후의 날짜를 출력하여라
SQL> SELECT name, hiredate, hiredate+30, hiredate+60
FROM professor
WHERE profno = 9908;
날짜 함수
날짜 년도 YYYY-2012, YY-12, YEAR -전체이름, RRRR-2012, RR-12
월 MM -01, MON-JAN, month-전체이름
일 DD-03, DAY-전체이름
시간 HH-12시간제, HH24-24시간제
분 MI
초 SS
* 예제 1) SYSDATE 시스템의 현재 날짜를 출력하여라.
SQL> SELECT SYSDATE FROM dual;
* 예제 2) MONTHS_BETWEEN, ADD_MONTHS
MONTHS_BETWEEN(date1,date2) ? date1과 date2 사이의 개월 수 계산
ADD_MONTHS(date,개월 수) ? date에 개월 수를 더한 날짜 계산
예) 입사한지 120개월 미만인 교수의 교수번호, 입사일, 입사일로부터 현재일까지의 개월수, 입사일에서 6개월 수의 날짜를 출력하여라.
SQL> SELECT profno, hiredate,
MONTHS_BETWEEN(SYSDATE,hiredate) TENURE,
ADD_MONTHS(hiredate,6) REVIEW
FROM professor
WHERE MONTHS_BETWEEN(SYSDATE,hiredate) < 120;
* 예제 3) LAST_DAY,NEXT_DAY
NEXT_DAY : date 날짜 이후의 첫 번째 ‘day’ 요일의 날짜를 계산
LAST_DAY : date 날짜가 속한 달의 마지막 날짜를 계산
예) 오늘이 속한 달의 마지막 날짜와 다가오는 토요일의 날짜 출력하기
SQL> SELECT SYSDATE,LAST_DAY(SYSDATE),
NEXT_DAY(SYSDATE,’SAT’)
FROM dual;
* 예제 4) ROUND, TRUNC 함수
ROUND (date[, ‘format’]) ? 날짜를 반올림
TRUNC (date[, ’format’]) ? 날짜를 절삭
예) 시간 정보 생략하는 경우 ROUND함수,TRUNC함수 비교하기
SQL> SELECT TO_CHAR(SYSDATE,’YY/MM/DD HH24:MI:SS’)NORMAL,
TO_CHAR(TRUNC(SYSDATE), ‘YY/MM/DD HH24:MI:SS’) TRUNC,
TO_CHAR(ROUND(SYSDATE), ‘YY/MM/DD HH24:MI:SS’) ROUND
FROM dual;
출력결과
NORMAL TRUNC ROUND
-------------- --------------- ---------------
07/12/01 20:56:26 07/12/01 00:00:00 07/12/02 00:00:00
해설: ROUND 함수는 정오를 넘으면 다음날을 출력한다.
TRUNC 함수는 시간정보와 상관없이 당일 날을 출력한다.
두 함수 모두 시간정보는 00:00:00을 출력한다.
그룹함수는 테이블의 전체 행을 하나 이상의 칼럼을 기준으로 칼럼 값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수이다.
그룹 함수는 SELECT 절이나 HAVING 절에서 사용할 수 있다. 그룹함수는 그룹별로 합계, 평균, 최대, 최소,개수 등을 구하기 위해 주로 사용한다.
문법)
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_ｅxpression]
[HAVING group_condition]
그룹함수의 종류
1. COUNT 함수
테이블에서 조건을 만족하는 행의 개수를 반환하는 함수
문법) COUNT ({* |[ DISTINCT | ALL] expr} )
예제 1)
101번 학과 교수중에서 보직 수당을 받는 교수를 출력하여라.
SQL> SELECT COUNT(COMM) // 컬럼을 직접 집어넣으면 null값은 제외한다.
FROM professor
WHERE deptno = 101;
출력결과)
COUNT(COMM)
------------
2
예제 2)
SQL> SELECT COUNT(*) //하지만 *을 집어 넣으면 null값을 포함한다.
FROM professor
WHERE deptno = 101 AND comm IS NOT NULL
출력결과)
COUNT(*)
--------
2
SQL> SELECT COUNT(*) //COUNT(*) 는 NULL을가진 행을 포함하므로 두 문장의 결과는다르게 나온다
FROM professor
WHERE deptno = 101;
출력결과)
COUNT(*)
--------
4
2. AVG, SUM, MIN, MAX 함수 //항상 nvl(컬럼,0)을 확인해주어 null 값은 0으로 확인하고 진행
문법)
AVG([ DISTINCT | ALL] expr)
SUM([ DISTINCT | ALL] expr)
expr의 데이터 타입은 NUMBER만 가능함
예제1)
101번 학과 학생들의 몸무게 평균과 합계를 출력하여라.
SQL> SELECT AVG(weight), SUM(weight)
FROM student
WHERE deptno = 101;
출력결과)
AVG(WEIGHT) SUM(WEIGHT)
----------- -----------
68 544
예제2)
101번 학과 학생들 중에서 최대 키와 최소 키를 출력하여라.
SQL> SELECT MAX(height), MIN(height)
FROM student
WHERE deptno = 101;
출력 결과)
MAX(HEIGHT) MIN(HEIGHT)
------------ ----------
177 160
3. STDDEV, VARIANCE 함수
예제)
교수테이블에서 급여의 표준 편차와 분산을 구하여라.
SQL> SELECT STDDEV(sal), VARIANCE(sal)
FROM professor;
출력결과)
STDDEV(SAL) VARIANCE(SAL)
---------- -------------
100.959681 10192.8571
4. 데이터 그룹 생성
1) GROUP BY 절
특정 칼럼 값을 기준으로 테이블의 전체 행을 그룹별로 나누기 위한 절.
사용 규칙)
* 그룹핑 전에 WHERE절을 사용하여 그룹 대상 집합을 먼저 선택가능.
* GROUP BY 절에는 반드시 칼럼이름이 포함되어야 하며 alias 사용 불가.
* 그룹별로 출력 순서는 오름차순으로 정렬됨.
* SELECT 절에서 나열된 칼럼 이름이나 표현식은 GROUP BY절에 반드시 포함 되어야 함.
* GROUP BY 절에 나열된 칼럼 이름은 SELECT 절에 명시하지 않아도 됨.
* where에는 그룹함수를 사용할수 없고 Having을 이용하여 조건 나열해준다.
예제1)
SQL> SELECT deptno, position, AVG(sal)
FROM professor
GROUP BY deptno;
출력결과
SELECT deptno, position, AVG(sal) //SELECT 절에서 나열된 칼럼 이름이나 표현식은 GROUP BY절에 반드시 포함 되어야 함.
*
1행에 오류:
ORA-00979: GROUP BY의 식이 없습니다.
GROUP BY 예제 2) 교수 테이블에서 학과별로 교수 수와 보직 수당을 받는 교수 수를 출력하여라.
SQL> SELECT deptno, COUNT(*), COUNT(comm)
FROM professor
GROUP BY deptno;
출력결과)
DEPTNO COUNT(*) COUNT(COMM)
------- -------- ------------
101 4 2
102 2 1
201 1 0
202 1 1
예제 2)
학과별로 소속 교수들의 평균 급여, 최소 급여, 최대 급여를 출력하여라.
SQL> SELECT deptno, AVG(sal), MIN(sal), MAX(sal)
FROM professor
GROUP BY deptno;
출력결과)
DEPTNO AVG(SAL) MIN(SAL) MAX(SAL)
------- -------- -------- --------
101 372.5 210 500
102 345 240 450
201 320 320 320
202 400 400 400
5. 다중 칼럼을 이용한 그룹별 검색
예제)
학생 테이블에서 전체 학생을 소속 학과별로 나누고, 같은 학과 학생은 다시 학년별로 그룹핑하여, 학과와 학년별로 인원수, 평균 몸무게를 출력하여라. 단, 평균 몸무게는 소수점 이하 첫번째 자리에서 반올림한다
SQL> SELECT deptno, grade, COUNT(*), ROUND(AVG(weight))
FROM student
GROUP BY deptno, grade;
출력결과)
DEPTNO GRADE COUNT(*) ROUND(AVG(WEIGHT))
------- ------ -------- ------------------
101 1 2 62
102 1 1 68
201 1 3 65
6. ROLLUP, CUBE 연산자
1) ROLLUP : GROUP BY 절의 그룹 조건에 따라 전체 행을 그룹화하고 각 그룹에 대해 부분합을 구하는 연산자
2) CUBE: ROLLUP에 의한 그룹 결과와 GROUP BY 절에 기술된 조건에 따라 그룹 조합을 만드는 연산자.
GROUP BY 절에 기술된 칼럼 수가 n개인 경우,
ROLLUP 연산자의 그룹핑 조합 : n+1
CUBE 연산자의 그룹핑 조합 : 2n
문법)
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY [ROLLUP|CUBE] group_by_ｅxpression]
[HAVING group_condition]
RoLLUP 예제)
소속 학과별로 교수 급여 합계와 모든 학과 교수들의 급여 합계를 출력하여라.
SQL> SELECT deptno, SUM(sal)
FROM professor
GROUP BY ROLLUP(deptno);
출력결과)
DEPTNO SUM(SAL)
------- --------
101 1490
102 690
201 320
202 400
2900 <- ROLLUP 이 적용되어 계산된 부분
CUBE 예제)
CUBE 연산자를 이용하여 학과 및 직급별 교수 수, 학과별 교수 수, 직급별 교수 수, 전체 교수 수를 출력하여라.
SQL> SELECT deptno, position, COUNT(*)
FROM professor
GROUP BY CUBE(deptno, position);
출력결과)
DEPTNO POSITION COUNT(*)
---------- ---------------------------------------- ----------
8
교수 2
부교수 2
조교수 2
전임강사 2
101 4
101 교수 1
101 부교수 1
101 조교수 1
101 전임강사 1
102 2
DEPTNO POSITION COUNT(*)
---------- ---------------------------------------- ----------
102 교수 1
102 전임강사 1
201 1
201 조교수 1
202 1
202 부교수 1
7. HAVING 절
SELECT 명령문의 WHERE절과 비슷한 기능을 하는 것으로 GROUP BY절에서 조건 검색을 할 경우 반드시 HAVING 절을 사용해야 한다.
문법)
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_ｅxpression]
[HAVING group_condition]
[ORDER BY column]
학생 수가 4명 이상인 학년에 대해서 학년, 학생 수, 평균 키, 평균 몸무게를 출력하여라. 단, 평균 키와 평균 몸무게는 소수점 첫 번째 자리에서 반올림하고, 출력순서는 평균 키가 높은 순부터 내림차순으로 출력하여라.
* HAVING 절을 사용하지 않은 경우
SQL> SELECT grade, count(*), ROUND(AVG(height)) avg_height, ROUND(AVG(weight)) avg_weight
FROM student
GROUP BY grade
ORDER BY avg_height DESC;
출력결과)
GRADE COUNT(*) AVG_HEIGHT AVG_WEIGHT
------- -------- ----------- ----------
4 3 176 85
1 6 175 65
3 2 171 79
2 5 165 53
* HAVING 절을 사용한 경우
SQL> SELECT grade, count(*), ROUND(AVG(height)) avg_height, ROUND(AVG(weight)) avg_weight
FROM student
GROUP BY grade
HAVING COUNT(*) > 4
ORDER BY avg_height DESC;
출력결과)
GRADE COUNT(*) AVG_HEIGHT AVG_WEIGHT
------- -------- ----------- ----------
1 6 175 65
2 5 165 53
별첨 : 그룹함수 문제 풀이(제가 틀릴수도 있으니 틀린부분에 대해서는 언제든지...)
중요한건 자신이 해봐야 한다는 것입니다. 절대 참고는 하시지만 보고 배끼지는 마세요.