SQL-변환 함수, 그룹함수

* 변환함수

 

년도 : YYYY로 표기. 두 자리로는 표기하지 않는다.

월 : MONTH, NON도 있지만 공문서에는 MM으로 표기한다(ex : 03, 04)

일 : DD가 기본 형식(ex 01, 02)이며 필요할 경우 DAY(영문 또는 한글 요일)와 DY(약자로 표현된 요일)를 같이 써준다.

시 : HH24(24시 형식)이 기본이지만 HH(12시 형식)을 사용할 경우 AM/PM을 같이 사용한다.

분  : MI

초 : SS(두 자리 초 표시). SSSSSS 하루를 초로 환산한 표현 방식(0~86399)

 

TO_CHAR : 날짜나 숫자를 문자로 변환. 내가 원하는 형태로 출력하고 싶을때 사용

ex) TO_CHAR(날짜, 출력형식), TO_CHAR(숫자, 출력형식)

TO_DATE : 데이터를 날짜형으로 해석.  ex) TO_DATE(문자, 해석형식)

 

[예제 1]현재 날짜를 다양한 형식으로 출력.

SELECT TO_CHAR(sysdate, 'YYYY/MM/DD') 날짜,

TO_CHAR(sysdate, 'YYYY/MM/DD:HH24:MI:SS') 날짜, // 두번째가 표준서식

TO_CHAR(sysdate, 'YYYY/MM/DD:HH:MI:SS AM') 날짜

FROM dual;

SELECT TO_CHAR(sysdate, '"오늘은 "YYYY"년 "MM"월 "DD"일 입니다."') 날짜

FROM DUAL;

 

[예제 2]10번 부서 사원의 입사일을 'XXX 사원의 입사일은 XXXX년 XX월 XX일입니다.'의 형식으로 출력

SELECT ename||' 사원의 입사일은 '|| TO_CHAR(hdate, 'YYYY"년 "MM"월 "DD"일 입니다."') 입사일

FROM emp

WHERE dno = '10';

 

[예제 3]다양한 형식으로 숫자를 출력

SELECT TO_CHAR(12345.678, '999,999.99999') num FROM dual; // 소수점 이하는 지정한 자리에 0을 채우고 앞은 무시

 

[3-1]

SELECT TO_CHAR(12345.678, '099,999.999') num FROM dual;

 

[3-2]

SELECT TO_CHAR(12345.678, '9,9999.9') num FROM dual; // 소수점 이하가 부족하면 반올림

 

[3-3]

SELECT TO_CHAR(12345.678, '9,999.9') num FROM dual; / /소수점 앞부분이 부족하면 출력장애

 

[3-4]

SELECT TO_CHAR(1234, '$999,999') num FROM dual;

 

[3-5]

SELECT TO_CHAR(1234, 'L999,999') num FROM dual; // 원화를 지원하지 않을 경우 엔화나 위안이 출력.

 

[3-6]

SELECT TO_CHAR(123456789, '9.999EEEE') num FROM dual;

 

[예제 4]10번 부서 사원의 보너스가 급여의 몇 퍼센트인지 검색

SELECT eno 사번, ename 이름,

TO_CHAR(NVL(comm, 0)/(sal*12)*100, '90.99')||'%' 급여_비율 // 90.99에 0을 지정해야 0.96이 .96으로 나오는 것을 방지

FROM emp

WHERE dno='10';

 

[예제 5]1992년 이전에 입사한 사원의 정보를 검색

SELECT eno 사번, ename 이름, hdate 입사일

FROM emp

WHERE hdate < TO_DATE('19920101', 'YYYYMMDD');

 

[실습]

1. 학생의 평균 평점을 'XXX 학생의 평균 평점은 x.xx입니다.' 형식으로 소수점 이하 두 자리까지 검색

SELECT sname||' 학생의 평균 평점은 '|| TO_CHAR(avr, '0.99')||'입니다.' 평균_평점

FROM student;

 

2. 교수의 부임일을 'XXX교수의 부임일은 YYYY년 MM월 DD일입니다.' 형식으로 검색

SELECT pname||' 교수의 부임일은 '|| TO_CHAR(hiredate, 'YYYY"년 "MM"월 "DD"일 입니다."') 부임일

FROM professor;

 

3. 교수 중에 3월에 부임한 교수의 명단을 검색

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

FROM professor

WHERE TO_CHAR(hiredate, 'MM') = 03;

 

4. 화학과 1학년 학생들의 4.5 환산 평점을 'XXX 학생의 4.5 환산 평점은 x.xx입니다.' 형식으로 소수점 이하 두 자리까지 검색.

SELECT sname||' 학생의 4.5 환산 평점은 '|| TO_CHAR(avr*4.5/4, '0.99')||'입니다.' "4.5 환산_평점"

FROM student

WHERE syear = '1' AND major = '화학';

 

* 그룹 함수

MAX : 최대 값을 반환. MAX(컬럼)

MIN : 값들 중에 최소 값을 반환. MIN(컬럼)

AVG : 평균 값을 계산 AVG(컬럼)

COUNT : 반환된 행의 수를 계산. COUNT(컬럼 | *)
SUM : 합계를 계산. SUM(컬럼)
STDDEV : 표준편차를 계산. STDDEV(컬럼)
VARIANCE : 분산을 계산. VARIANCE(컬럼)

 

그룹 함수를 사용하는 경우 고려 사항
- NULL값은 무시.
- 반드시 단하나의 값만을 반환.
- GROUP BY 설정 없이 일반 컬럼과 기술될 수 없다.

 

[예제 1]사원의 급여 평균을 검색

SELECT AVG(sal) 평균급여, ROUND(AVG(sal)) 평균급여 // ROUND외에 to_char도 사용 가능 
FROM emp;

 

[예제 2] 사원들에게 지급된 보너스 총합과 보너스 평균을 검색.

SELECT SUM(comm) 총액,
ROUND(AVG(comm)) 평균, COUNT(comm) 수령인원, // 보너스를 받는 사람으로만 평균 계산
ROUND(AVG(NVL(comm,0))) 환산평균, COUNT(*) 전체인원 // 보너스를 받지 않는 사람까지 포함해서 평균 계산
FROM emp;

SELECT COUNT(*)

FROM emp
WHERE comm IS NOT NULL; // null이 아닌 사원만 검색

 

숫자컬럼에 NULL이 들어가면 안되는 이유를 보여준 문제. 계산오류가 날 가능성이 엄청 높다.

 

[예제 3] 10번 부서원들보다 급여가 높은 사원을 검색.

SELECT eno 사번, ename 이름, dno 부서번호
FROM emp
WHERE sal > (SELECT MAX(sal)
                  FROM emp
                  WHERE dno = '10');

 

* 그룹 함수와 GROUP BY절

형식

SELECT [DISTINCT/ALL] 컬럼 or 그룹함수, ...
FROM 테이블
WHERE 조건
GROUP BY Group대상 -> GROUP의 위치.
ORDER BY 정렬대상 [ASC/DESC]

 

그룹함수 사용할때만 GROUP BY절 사용

 

[예제 4]업무별 평균 급여, 평균 연봉과 부서별 평균 연봉을 검색.

SELECT job 업무, ROUND(AVG(sal)) 평균_급여,
ROUND(AVG(sal*12+NVL(comm,0))) 평균_연봉
FROM emp
GROUP BY job;

GROUP BY가 없으면 기수가 다르기때문에 출력이 안됨. 일반 컬럼에 있으면 반드시 GROUP BY절에 반드시 있어야 한다.

 

SELECT d.dno 부서번호, dname 부서명, ROUND(AVG(sal*12+NVL(comm,0))) 평균_연봉
FROM dept d, emp e
WHERE d.dno = e.dno
GROUP BY d.dno, dname
ORDER BY d.dno;

 

[예제 5] 부서별로 급여 평균의 최대 값과 최소 값을 검색.

SELECT dno 부서번호,
MAX(AVG(sal)) 최대평균, MIN(AVG(sal)) 최소평균 // dno는 여러개지만 나머지는 1개라서 출력 불가
FROM emp
GROUP BY dno; 

 

[예제 7] 각 부서별 최소 급여를 받는 사원의 정보를 검색.

SELECT d.dno, dname, eno, ename, sal
FROM emp e, dept d
WHERE d.dno=e.dno
AND (d.dno, sal) IN (SELECT dno, MIN(sal)
                           FROM emp
                           GROUP BY dno)
ORDER BY d.dno;

 

[실습] 
1. 각 학과별 학생 수를 검색한다.

SELECT major 전공, COUNT(sno)

FROM student

GROUP BY major;

2. 화학과와 생물학과 학생 4.5 환산 평점의 평균을 각각 검색.

SELECT sno 학번, sname 이름, major 학과, TO_CHAR(AVG(avr*4.5/4), '0.99') "4.5 환산 평점"

FROM student

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

GROUP BY sno, sname, major;

3. 부임일이 10년 이상 된 직급별(정교수, 조교수, 부교수) 교수의 수를 검색.

SELECT orders 직위, COUNT(pno)

FROM professor

WHERE 120 <= MONTHS_BETWEEN(sysdate, hiredate)

GROUP BY orders;

4. 과목명에 화학이 포함된 과목의 학점수 총합을 검색.

SELECT SUM(st_num) 이름에_화학이_포함된_과목

FROM course

WHERE cname like '%화학%';

 

5. 화학과 학생들의 기말고사 성적을 성적순으로 검색.

SELECT s.sno 학번, s.sname 이름, s.major 전공, TO_CHAR(AVG(sc.result), '00.99') 기말고사_성적

FROM student s, score sc

WHERE s.sno = sc.sno

AND major = '화학'

GROUP BY s.sno, s.sname, s.major

ORDER BY AVG(sc.result) desc;

6. 학과별 기말고사 평균을 성적순으로 검색한다.

SELECT s.major 학과, TO_CHAR(AVG(sc.result), '00.99') 기말고사_성적

FROM student s, score sc

WHERE s.sno = sc.sno

GROUP BY s.major

ORDER BY AVG(sc.result) desc;

7. 30번 부서의 업무별 연봉의 평균을 검색.(단, 출력 양식은 소수 이하 두 자리까지 통일된 형식으로 출력)

SELECT d.dno 부서번호, e.job 업무,TO_CHAR(AVG(sal*12+NVL(comm,0)), 'L99999.99') 평균_연봉

FROM dept d, emp e

WHERE d.dno = e.dno

AND d.dno = '30'

GROUP BY d.dno, e.job

ORDER BY AVG(sal*12+NVL(comm,0)) desc;

8. 물리학과 학생 중에 학년별로 성적이 가장 우수한 학생의 평점을 검색.

SELECT s.sno 학번, s.sname 이름, major 전공, syear 학년, TO_CHAR(MAX(avr), '0.99') 성적

FROM student s

WHERE s.major = '물리'

AND (s.syear, avr) IN (SELECT syear, MAX(AVR)

                            FROM student

                            WHERE major='물리' 

                            GROUP BY syear)

GROUP BY s.sno, s.sname, s.major, s.syear

ORDER BY syear desc;

 

9. 학년별로 환산 평점의 평균값을 검색한다. 단 출력 양식은 소수이하 두 자리까지 통일된 양식으로 출력.

SELECT syear 학년별, TO_CHAR(AVG(avr*4.5/4), '0.00') 평점

FROM student

GROUP BY syear;

10. 화학과 1학년 학생 중 평점이 평균 이하인 학생을 검색한다.

SELECT sno 학번, sname 이름, major 전공, syear 학년, avr 평점

FROM student

WHERE avr <= (SELECT AVG(avr)

                     FROM student

                     WHERE major = '화학' AND syear = 1)

AND major = '화학' AND syear = 1;

 

 

* 그룹 함수와 HAVING절

형식

SELECT [DISTINCT/ALL] 컬럼 or 그룹함수, ...
FROM 테이블
WHERE 조건
GROUP BY Group대상
HAVING <그룹 함수 포함 조건>
ORDER BY 정렬대상 [ASC/DESC];

 

[예제 1] 부서별 급여 평균이 3천 달러 미만인 부서의 부서번호와 평균 급여를 검색.

SELECT dno 부서번호, ROUND(AVG(sal)) 평균급여
FROM emp
GROUP BY dno
HAVING AVG(sal) < 3000;

 

[예제 2] HAVING절의 다양한 사용법

SELECT dno 부서번호, COUNT(*) 인원수
FROM emp
GROUP BY dno
HAVING job != '개발'; // 그룹 함수가 들어가지 않기 때문에 오류. WHERE절에 써야함.

 

[예제 3]부서 중 가장 급여를 많이 받는 부서를 검색.

SELECT dno, AVG(sal)
FROM emp
GROUP BY dno
HAVING AVG(sal) = (SELECT MAX(AVG(sal))
                           FROM emp
                           GROUP BY dno);

 

[실습] 
1. 화학과를 제외하고 학과별로 학생들의 평점 평균을 검색한다.

SELECT major 전공, TO_CHAR(AVG(avr),'0.90') 평균평점 
FROM student
WHERE major != '화학'
GROUP BY major;

2. 화학과를 제외한 각 학과별 평균 평점 중에 평점이 2.0 이상인 정보를 검색한다.

SELECT major, TO_CHAR(AVG(avr),'0.90')
FROM student
WHERE major != '화학'
GROUP BY major
HAVING avg(avr) > 2.0;

3. 기말고사 평균이 60점 이상인 학생의 정보를 검색한다.(학번과 기말고사 평균)

SELECT sno, ROUND(AVG(result))
FROM score
GROUP BY sno
HAVING avg(result) >= 60;

4. 강의 학점수가 3학점 이상인 교수의 정보를 검색한다.(교수번호, 이름과 담당 학점수)

SELECT p.pno, pname, sum(st_num)
FROM professor p, course c
WHERE p.pno = c.pno
GROUP BY p.pno, pname
HAVING sum(st_num) >= 3;

5. 기말고사 성적이 핵 화학과목보다 우수한 과목의 과목명과 담당 교수명 검색한다.

SELECT c.cno, cname, p.pno, pname
FROM course c, professor p
WHERE c.pno=p.pno
AND cno IN (SELECT cno
             FROM score
             HAVING AVG(result) > (SELECT AVG(result) 
                                    FROM score r, course c
                                    WHERE  r.cno = c.cno
                                    AND cname='핵화학')
             GROUP BY cno);

6. 근무 중인 직원이 4명 이상인 부서를 검색한다.

SELECT dept.dno, dname, COUNT(*)
FROM emp, dept
WHERE dept.dno = emp.dno
GROUP BY dept.dno, dname
HAVING COUNT(*) > 4;

7. 업무별 평균 년봉이 2만불 이상인 업무를 검색한다.

SELECT job, ROUND(AVG(sal*12+NVL(comm,0)))
FROM emp
GROUP BY job
HAVING AVG(sal*12+NVL(comm,0)) >= 20000;

8. 각 학과의 학년별 인원중 인원이 5명 이상인 학년을 검색한다.

SELECT major, syear, COUNT(*)
FROM student
GROUP BY major, syear
HAVING COUNT(*) >= 5;

 

9. 인원수가 가장 많은 학과를 검색한다.

SELECT major
FROM student
GROUP BY major
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) 
                      FROM student
                      GROUP BY major);

10. 학생 중 기말고사 성적이 가장 낮은 학생의 정보를 검색한다.

SELECT s.sno, sname, ROUND(AVG(result))
FROM student s, score r
WHERE s.sno=r.sno
GROUP BY s.sno, sname
HAVING AVG(result) = (SELECT MIN(AVG(result))
                       FROM score
                       GROUP BY sno);