Intro to Database (5) : Oracle SQL developer 3 - 함수

2019-07-03~2019-07-04 

단일행 함수(Single Row Functions)

: 한 줄로 된 함수로 쿼리를 처리한 후 각 줄에 대한 값을 리턴한다 

1. 숫자 함수(Numeric Functions)

함수	반환값	예시	반환값
ABS(x)	절댓값을 돌려줌	ABS(3) ABS(-3)	3 3
CEIL(x)	같거나 더 큰 정수값을 돌려줌	CEIL(2.5) CEIL(5.7) CEIL(10.3)	3 6 11
FLOOR(x)	작거나 같은 정수값을 돌려줌	FLOOR(2.4) FLOOR(7) FLOOR(-4.3)	2 7 -5
TRUNC(x,y)	정해진 소수점 이하는 버림	ROUND(120.123 , 1)	120.1
ROUND(x,y)	정해진 소수점 위로 반올림	TRUNC(140.234, 2) TRUNC(142, -1)	140.23 140

2. 문자 함수 Text Function 

함수	반환 값	예시	반환 값
LOWER(문자열_값)	모두 소문자로 전환	LOWER("Good MORNING")	good morning
UPPER(문자열_값)	모두 대문자로 전환	UPPER("Good Morning")	GOOD MORNING
INITCAP(문자열_값)	첫 글자가 대무자로 전환	INITCAP("GOOD MORNING")	Good Morning

실습  07-03

-- 2019.07.03 오후 5시 40분 
--18. 학생들의 체중을 5만큼씩 증가시켜 검색
SELECT STU_WEIGHT, STU_WEIGHT + 5 "INCEREASED BY 5" FROM STUDENT;
--19. 학생들의 체중을 5만큼씩 감소시켜 검색
SELECT STU_WEIGHT, STU_WEIGHT - 5 "DECEREASED BY 5" FROM STUDENT;

--23. 학과명과 이름을 합쳐서 검색
SELECT STU_DEPT || ' ' || STU_NAME FROM STUDENT; --
SELECT CONCAT(STU_DEPT, STU_NAME) FROM STUDENT;
--24. 컴퓨터정보학과 옥한빛입니다로 만들어서 검색
SELECT '컴퓨터정보학과' || ' '|| STU_NAME || '' || '입니다' FROM STUDENT WHERE STU_NAME = '옥한빛';
SELECT STU_DEPT || '학과' || STU_NAME || '입니다' FROM STUDENT;
--추가1 :.. 저는 ~과 ~학년 ~반 ~입니다 만들어서 검색 
SELECT '저는 '||STU_DEPT||'학과 '|| STU_GRADE || '학년 ' || STU_CLASS || '입니다' FROM STUDENT;

--추가2: '이름', '과목', '성적'으로 만들어서 출력
SELECT STU_NAME || ', ' || SUB_NAME || ', '|| ENR_GRADE "성적" 
FROM STUDENT S, ENROL E SUBJECT U
WHERE S.STU_NO = E.STU_NO AND E.SUB_NO = U.SUB_NO;
--각 테이블에 공통적으로 들어가있는 번호와 과목 번호라는 조건을 주고 검색을 하게 된다
SELECT STU_NAME || '는 ' || SUB_NAME || '을 수강합니다  ' "수강" 
FROM STUDENT S, ENROL E SUBJECT U
WHERE S.STU_NO = E.STU_NO AND E.SUB_NO = U.SUB_NO;

---

2019-07-04

Date Functions (날짜 함수)

오늘 날짜를 반환(SYSDATE), 두 날짜의 차이를 반환(MONTHS_BETWEEN(m, n), 해당 날에서 가장 가까운 날짜를 반환하는 함수(NEXT_DAY(date, char)등이 있다.

 

변환 함수 

• TO_CHAR : 숫자나 날짜를 문자열로 변환
  select empno, ename, to_char(hiredate, ‘yyyy-mm’) as 입사 년월 from emp;
• TO_NUMBER : 문자를 숫자로 변환
  select to_char(to_number(1234.5678), ‘999.999’)
• TO_DATE : 문자를 날짜로 변환 select empno, ename from emp where hiredate = to_date(‘1980-12-17’, ‘yy-mm-dd’);

날짜 형식 요소

• YEAR : 연도를 알파벳으로 spelling
• YYYY : 4자리 연도로 표시
• YY : 끝의 2자리 연도로 표시
• MONTH : 월을 알파벳으로 spelling
• MON : 월의 알파벳 약어
• MM : 월을 2자리 숫자로 표시
• DAY : 일에 해당하는 요일
• DY : 일에 해당하는 요일의 약어
• DDD, DD, D표시

---

실습 (1)

*Dual 이라는 테이블은 오라클이 설치될 때 만들어지는 테이블이며, 산술, 날짜 처리, 계산 등을

하는 기본 임시 테이블입니다. 

1. 현재의 시각을 출력하시오 (SYSDATE이용하여 ..시..분..(SYSDATE 이용하여.. 시.. 분.. 초 형식으로)
2.  오늘이 올해의 몇 번째 날인지 출력하시오 (DDD이용하여 …일)
3. 오늘의 요일을 출력하시오

SELECT SYSDATE FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL; /*24시 형식 */
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') FROM DUAL; /*년월일 8자리*/

SELECT TO_CHAR(SYSDATE, 'HH"시"MI"분"SS"초"') "현재시각" FROM DUAL; /*시,분,초 문자열에 별칭 '현재시각' 삽입*/

SELECT TO_CHAR(SYSDATE, 'DDD') FROM DUAL /*몇 번째 날인지 출력*/

SELECT TO_CHAR(SYSDATE, 'DAY') "오늘의 요일" FROM DUAL; /*요일 출력하기*/

추가실습
SELECT SYSTIMESTAMP AT TIME ZONE 'UTC' FROM DUAL
SELECT SYSTIMESTAMP FROM DUAL
SELECT TO_CHAR(SYSTIMESTAMP, 'HH":"MI":"SS","MM"/"DD"/"YYYY')FROM DUAL /*시간:분:초, 달/일/년도*/

---

실습 (2) : REVIEW 

지금까지 배운 내용을 REVIEW하는 시간을 갖기 위해 

https://docs.oracle.com/cd/E17781_01/appdev.112/e18147/tdddg_connecting.htm#TDDDG99997에서 나오는 예제를 코딩해보며 연습하도록 하겠습니다.

지난 시간에 사용했던 HR 계정을 사용해서, EMPLOYEES 테이블을 불러와서 데이터를 조건부 출력하고 출력 형식을 포매팅하는 예제입니다. 

--REVIEW--
/*조건부 출력에 별칭지정, 연봉계산*/
SELECT * FROM EMPLOYEES;
SELECT LAST_NAME, SALARY "MONTHLY PAY" , 
SALARY*12 "ANNUAL PAY" FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;

/*나오는 형식 포매팅도 해본 코딩입니다*/
SELECT LAST_NAME, TO_CHAR(SALARY, '$999,999') "MONTHLY PAY" , 
TO_CHAR(SALARY*12, '$999,999') "ANNUAL PAY" FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90; 

/* DAILY PAY */
SELECT LAST_NAME, ROUND((SALARY*12)/365, 2) "DAILY PAY"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100; 

/* 일봉 절삭과 이름정렬 */
SELECT LAST_NAME, TRUNC((SALARY*12)/365) "하루치 SALARY"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

/* CONCAT FIRST NAME & LAST NAME */
SELECT FIRST_NAME || ' ' || LAST_NAME AS "NAME" FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

/*대소문자 함수 활용*/
SELECT UPPER(LAST_NAME) "LAST", INITCAP(FIRST_NAME) "FIRST", LOWER(EMAIL) "E-MAIL"
FROM EMPLOYEES;

/*YEARS EMPLOYED*/
SELECT EXTRACT (YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
"YEARS EMPLOYED" 
FROM EMPLOYEES ORDER BY "YEARS EMPLOYED" DESC;

/*SYSTEM DATA AND TIME*/
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP)|| ':' ||
EXTRACT(MINUTE FROM SYSTIMESTAMP)|| ':' ||
EXTRACT(SECOND FROM SYSTIMESTAMP )|| ', ' ||
EXTRACT(MONTH FROM SYSTIMESTAMP)|| '/' ||
EXTRACT(DAY FROM SYSTIMESTAMP)|| '/' ||
EXTRACT(YEAR FROM SYSTIMESTAMP) "SYSTEM DATE AND TIME" FROM DUAL;

/*FORMAT FIRE DATE*/
SELECT LAST_NAME, HIRE_DATE,
TO_CHAR(HIRE_DATE, 'MONTH DD YYYY') "DATE STARTED" FROM EMPLOYEES;

---

실습 (3) : 함수 연습 : 지난 시간에 만든 학사관리시스템 테이블을 이용해서(HR사용자)

--Login as your account and complete the exercise problems
--62. 학생들의 성별을 소문자로 검색
SELECT LOWER(STU_NAME), LOWER(STU_GENDER) FROM STUDENT;

--63. 학생들의 성별을 대문자로 검색
SELECT STU_NAME, UPPER(STU_GENDER) FROM STUDENT; 

--66. 학생의 이름과 이름의 첫 2글자 검색 (substr)
SELECT STU_NAME, SUBSTR(STU_NAME,0,2) FROM STUDENT; 

--67. 학생들의 이름, 학과, 학과의 두번째부터 1자리를 검색
SELECT STU_NAME,STU_DEPT, SUBSTR(STU_DEPT,2,1) FROM STUDENT; 

--68. 학생들의 이름과 이름의 길이를 검색
SELECT STU_NAME, LENGTH(STU_NAME) FROM STUDENT; 

--69. 학생들의 학과와 학과명의 길이를 검색
SELECT STU_DEPT, LENGTH(STU_DEPT) FROM STUDENT; 

--70.!! 학생들의 이름에 ‘김’이 몇번째 있는지 검색 (instr)
SELECT STU_NAME, INSTR(STU_NAME,'김') FROM STUDENT; /*따옴표는 하나만 쓰자*/

--71!!. 학생들의 학과에 ‘기＇가 몇번째 있는지 검색 
SELECT  STU_DEPT ,INSTR(STU_DEPT,'기') FROM STUDENT

--72!. 학생의 이름을 15자리로 하고, 뒤에 &로 채워 검색 (rpad)
SELECT STU_NAME, RPAD(STU_NAME,15,'&') FROM STUDENT; 

--73!. 학생의 학과를 20자리로 하고, 앞에 %를 채워 검색
SELECT LPAD(STU_DEPT,20,'%') FROM STUDENT; 

--74!!. 학생의 학번, 이름, 신장을 검색 (신장은 첫번째 자리에서 반올림)
SELECT STU_NO, STU_NAME, ROUND(STU_HEIGHT,-1) FROM STUDENT;

--75. 학생의 학번, 이름, 신장을 검색 (신장은 두째 자리에서 절삭)
SELECT STU_NO, STU_NAME, ROUND(STU_HEIGHT,-2) FROM STUDENT;

--76. 체중을 30으로 나눈 나머지를 검색 (mod) FLPPR은 소숫점 이하의 수 버림
SELECT MOD(STU_WEIGHT,30) FROM STUDENT;

--85. 가장 큰 학생의 신장을 검색
SELECT MAX(STU_HEIGHT) FROM STUDENT;

--86. 가장 작은 학생의 신장을 검색
SELECT MIN(STU_HEIGHT) FROM STUDENT;

--87. 가장 작은 체중을 검색
SELECT MIN(STU_WEIGHT) FROM STUDENT;

--88. 학생이름중 max와 min값을 검색 
SELECT MAX(STU_NAME), MIN(STU_NAME) FROM STUDENT;

--89. 체중과 신장의 max와 min값을 검색
SELECT MAX(STU_HEIGHT), MIN(STU_HEIGHT), MAX(STU_WEIGHT),MIN(STU_WEIGHT) FROM STUDENT;

--90. 학번의 max와 min값을 검색
SELECT MAX(STU_NO), MIN(STU_NO) FROM STUDENT;

--91!!!. 학과의 수를 검색
SELECT STU_DEPT,COUNT(STU_DEPT) FROM STUDENT GROUP BY STU_DEPT; /*중복을 배제*/
SELECT COUNT(STU_DEPT) FROM STUDENT 

--92. 학생들의 성씨의 수
SELECT SUBSTR(STU_NAME,0,1) FROM STUDENT GROUP BY STU_NAME;

--93. 학생테이블의 레코드의 수를 검색
SELECT COUNT(*) FROM STUDENT;

--94. entrol테이블의 레코드의 수를 검색
SELECT COUNT(*) FROM ENROL;

---

그룹함수 Group Functions

여러행에 대한 연산 즉 평균, 개수 같은 기초 통계치 결과값을 반환하는 함수이다

이에 대해 실습(4)을 통해 알아보도록 하겠다. (HR계정으로)

Instructions - https://docs.oracle.com/cd/E17781_01/appdev.112/e18147/tdddg_connecting.htm#TDDDG20000

실습(4) (HR계정으로)

-- COUNT BY MANAGER_ID 
SELECT MANAGER_ID, COUNT(*) FROM EMPLOYEES GROUP BY MANAGER_ID;

-- ANNUAL SALARY BY DEPT
SELECT DEPARTMENT_ID, SUM(SALARY * 12) FROM EMPLOYEES
GROUP BY DEPARTMENT_ID HAVING SUM(SALARY *12) > 1000000;

--STATISTICS BY JOB_ID
SELECT JOB_ID, MIN(SALARY), AVG(SALARY), MEDIAN(SALARY), /*기본*/
MAX(SALARY), STDDEV(SALARY) FROM EMPLOYEES GROUP BY JOB_ID;

SELECT JOB_ID, MIN(SALARY), AVG(SALARY), MEDIAN(SALARY), /*표준편차 소수점처리*/
MAX(SALARY), ROUND(STDDEV(SALARY)) FROM EMPLOYEES GROUP BY JOB_ID;

SELECT JOB_ID, MIN(SALARY) "MIN", AVG(SALARY) "AVG", MEDIAN(SALARY) "MEDIAN",
MAX(SALARY) "MAX", ROUND(STDDEV(SALARY)) "STD" FROM EMPLOYEES GROUP BY JOB_ID; /*이름까지 부여*/

---

실습(5)

다시 각자의 계정에서 실습하도록 하겠습니다. (학사정보관리시스템 예제)

--95. 학과별 학생들의 인원수를 검색
SELECT STU_DEPT,COUNT(STU_NAME) FROM STUDENT GROUP BY STU_DEPT;

--96!. 학과별 학생들의 인원수를 인원수가 많은 순으로 검색
SELECT STU_DEPT,COUNT(STU_NAME) "COUNTS" FROM STUDENT 
GROUP BY STU_DEPT ORDER BY "COUNTS" DESC;

--97. 학년별 학생들의 인원수를 검색
SELECT STU_GRADE, COUNT(STU_NAME) FROM STUDENT GROUP BY STU_GRADE;

--98. 학년별 학생들의 인원수가 많은 순으로 검색
SELECT STU_GRADE, COUNT(STU_NAME) "COUNTS" FROM STUDENT 
GROUP BY STU_GRADE ORDER BY "COUNTS" DESC;

--99. 학과별 학생들의 평균신장을 검색
SELECT STU_DEPT, AVG(STU_HEIGHT) FROM STUDENT GROUP BY STU_DEPT;

--100. 학과별 학생들의 체중의 표준편차를 검색 (STDDEV)
SELECT STU_DEPT, STDDEV(STU_WEIGHT) FROM STUDENT GROUP BY STU_DEPT;

--102. 학과별 학년별 학생들의 평균체중
SELECT STU_DEPT, STU_GRADE, AVG(STU_WEIGHT) FROM STUDENT 
GROUP BY STU_DEPT, STU_GRADE ORDER BY STU_DEPT;

--103. 학과별 학년별 학생들의 학번의 max와 min값을 검색
SELECT STU_DEPT, STU_GRADE, MAX(STU_NO), MIN(STU_NO) FROM STUDENT
GROUP BY STU_DEPT, STU_GRADE ORDER BY STU_DEPT;

--104. 학과별 학생들의 인원수를 인원수가 많은 순으로 검색
SELECT STU_DEPT, COUNT(STU_NO) "COUNTS" FROM STUDENT
GROUP BY STU_DEPT ORDER BY COUNTS DESC;

--105. 학과별 학생들의 평균신장을 평균신장 순으로 검색
SELECT STU_DEPT, AVG(STU_HEIGHT) "AVH" FROM STUDENT
GROUP BY STU_DEPT ORDER BY AVH;

--106. 학과별 학년별 학생들의 평균체중을 평균체중이 많은 순으로 검색
SELECT STU_DEPT, STU_GRADE, AVG(STU_WEIGHT) "AVW" FROM STUDENT 
GROUP BY STU_DEPT, STU_GRADE ORDER BY AVW DESC;

--107. 학과별 학생들의 평균신장을 평균신장이 높은 순으로 검색
SELECT STU_DEPT, AVG(STU_HEIGHT) "AVH" FROM STUDENT
GROUP BY STU_DEPT ORDER BY AVH DESC;

--108. 학과별 학생들의 평균신장이 170이상인 학과를 평균신장이 낮은 순으로 검색 (having)
SELECT STU_DEPT, AVG(STU_HEIGHT) AS "AVH" FROM STUDENT 
GROUP BY STU_DEPT HAVING AVG(STU_HEIGHT) >= 170 ORDER BY AVH; /*HAVING 이하에서는 축약어 사용 불가*/

SELECT STU_DEPT, AVG(STU_HEIGHT) AS "AVH" FROM STUDENT 
GROUP BY STU_DEPT HAVING AVG(STU_HEIGHT) >= 170 ORDER BY 2; /*숫자로 ORDER BY 이하 대신 할 수 있음*/

---

NULL VALUE 처리하는 방안 3가지 

널 값이 존재할 시 선택적으로 처리할 수 있는 방법을 익히고 예제로 실습해보자

1) NVL 

첫번째 계수가 널값이면 두번 째 값을 리턴, 널값이 아니면 그 값을 리턴

EX) NVL(TO_CHAR(COMMISSION_PCT), 'Not Applicable')

2) NVL2

첫번째가 널값이 아니면 두번째 값을 리턴, 널값이면 세번째 값을 리턴

EX) NVL2 (COMMISSION_PCT + (SALARY * COMMISSION_PCT), SALARY)

3) NULL IF 

두 값이 같으면 널값을 반환, 다르면 첫 번째 값을 리턴

EX) nullif('A', 'A') -> 같다(null로 반환)

실습(6) 

--77. 신장열의 값이 널인 학생의 경우 ‘미기록＇으로 기록
SELECT NVL(TO_CHAR(STU_HEIGHT), '미기록')FROM STUDENT

--78. 신장과 체중을 합한 값을 학번, 이름과 함께 검색 (nvl)
SELECT STU_NO, STU_NAME, 
NVL(TO_NUMBER(STU_HEIGHT+STU_WEIGHT), STU_WEIGHT+STU_HEIGHT) 
FROM STUDENT;

SELECT STU_NO, STU_NAME, 
NVL2(TO_NUMBER(STU_HEIGHT+STU_WEIGHT), (STU_HEIGHT+STU_WEIGHT) , STU_WEIGHT+AVG(STU_HEIGHT) 
FROM STUDENT;

SELECT STU_NO, STU_NAME,
NVL(STU_WEIGHT+STU_HEIGHT ,STU_WEIGHT+SELECT AVG(STU_HEIGHT) FROM STUDENT)
FROM STUDENT;

SELECT STU_NO, STU_NAME, AVG(STU_HEIGHT) AS "AH"
NVL(STU_WEIGHT+STU_HEIGHT ,T0_NUMBER(STU_WEIGHT+ AH))
FROM STUDENT;

SELECT AVG(STU_HEIGHT), STU_NO, STU_NAME
FROM STUDENT;

/*정답*/



SELECT AVG(STU_HEIGHT)
FROM STUDENT;


--79. 신장에서 체중을 뺀값을 학번, 이름과 함께 검색 (nvl)

--80. 신장이 null인 경우 ‘입력요망’으로 바꾸어 학번, 이름을 검색
SELECT STU_NO, STU_NAME, NVL(TO_CHAR(STU_HEIGHT), '입력요망')  FROM STUDENT 

실패한 부분이 많으므로 참고 X - 추후 수정예정 

 

여기까지 2019-07-04(목)