형변환 함수
문자열(CHAR), 숫자(NUMBER), 날짜(DATE) 끼리 형변환 가능
문자열로 변환 TO_CHAR
TO_CHAR(날짜, [포맷]) : 날짜형 데이터를 문자형 데이터로 변경
TO_CHAR(숫자, [포맷]) : 숫자형 데이터를 문자형 데이터로 변경
< 숫자 변환 시 포맷 패턴 >
9 : 숫자 한칸을 의미, 여러개 작성 시 오른쪽 정렬
0 : 숫자 한칸을 의미, 여러개 작성 시 오른쪽 정렬 + 빈칸 0 추가
L : 현재 DB에 설정된 나라의 화폐 기호
SELECT TO_CHAR(1234, '999999') FROM DUAL; -- ' 1234'
SELECT TO_CHAR(1234, '000000') FROM DUAL; -- '001234'
SELECT TO_CHAR(1000000, 'L9,999,999') FROM DUAL; -- '￦1,000,000'
SELECT TO_CHAR(1000000, '$9,999,999') FROM DUAL; -- '$1,000,000'
< 날짜 변환 시 포맷 패턴 >
YYYY : 년도 / YY : 년도(짧게)
RRRR : 년도 / RR : 년도(짧게)
MM : 월
DD : 일
AM / PM : 오전/오후 표시
HH : 시간 / HH24 : 24시간 표기법
MI : 분 / SS : 초
DAY : 요일(전체) / DY : 요일(요일명만 표시)
SELECT SYSDATE FROM DUAL; -- 2023-01-10 10:21:22.000
2023-01-10 10:21:00 화요일
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS DAY') FROM DUAL ;
2023년 01월 10일 (화)
SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일" (DY)') FROM DUAL;
SQL Error [1821][22008]: ORA-01821: 날짜 형식이 부적합합니다
년,월,일이 날짜를 나타내는 패턴으로 인식이 안되서 오류 발생
"" 쌍따옴표를 이용해서 단순한 문자로 인식시키면 해결
날짜로 변환 TO_DATE
TO_DATE(문자형 데이터, [포맷]) : 문자형 데이터를 날짜로 변환
TO_DATE(숫자형 데이터, [포맷]) : 숫자형 데이터를 날짜로 변환
SELECT TO_DATE('2023-01-10') FROM DUAL; -- DATE 타입으로 변환
SELECT TO_DATE(20230110) FROM DUAL; -- DATE 타입으로 변환
SELECT TO_DATE('510505', 'YYMMDD') FROM DUAL; -- 2051-05-05
SELECT TO_DATE('510505', 'RRMMDD') FROM DUAL; -- 1951-05-05
Y 패턴 : 현재 세기(21세기 == 20XX년 == 2000년대)
R 패턴 : 1 세기를 기준으로 절반(50년) 이상인 경우 이전세기(1900년대)
절반(50년) 미만인 경우 현재 세기(2000년대)
EMPLOYEE 테이블에서 각 직원 태어난 생년월일(1990년 05월 13일) 조회 (별칭 생년월일)
SELECT EMP_NAME,
TO_CHAR(TO_DATE(SUBSTR(EMP_NO, 1, INSTR(EMP_NO, '-') -1 ), 'RRMMDD'),
'YYYY"년" MM"월" DD"일"') AS 생년월일
FROM EMPLOYEE;
숫자 형변환 TO_NUMBER
TO_NUMBER(문자데이터, [포맷]) : 문자형 데이터를 숫자 데이터로 변경
SELECT '1,000,000' + 500000 FROM DUAL;
SQL Error [1722][42000]: ORA-01722: 수치가 부적합합니다
SELECT TO_NUMBER('1,000,000', '9,999,999') + 5000000 FROM DUAL;
NULL 처리 함수
NVL(컬럼명, 컬럼값이 NULL 일때 바꿀 값) : NULL인 컬럼값을 다른 값으로 변경
NULL과 산술 연산을 진행하면 결과는 무조건 NULL
SELECT EMP_NAME, SALARY, BONUS, NVL(BONUS, 0), SALARY * NVL(BONUS, 0)
FROM EMPLOYEE;
NVL2(컬럼명, 바꿀값1, 바꿀값2)
해당 컬럼의 값이 있으면 바꿀값1로 변경
해당 컬럼이 NULL면 바꿀값2로 변경
EMPLOYEE테이블에서 보너스를 받으면 '0', 안받으면 'X' 조회
SELECT EMP_NAME, BONUS, NVL2(BONUS, 'O', 'X')
FROM EMPLOYEE;
선택 함수
여러가지 경우에 따라 알맞은 결과를 선택할 수 있음.
DECODE(계산식 | 컬럼명, 조건값1, 선택값1, 조건값2, 선택값2,......., 아무것도 일치하지 않을 때)
비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과값 반환
일치하는 값을 확인(자바의 SWITCH와 비슷함)
직원의 성별 구하기(남:1 / 여:2)
SELECT EMP_NAME, EMP_NO, DECODE( SUBSTR(EMP_NO, 8, 1), '1', '남성', '2', '여성') 성별
FROM EMPLOYEE;
-- 직원의 급여를 인상하고자 한다.
-- 직급 코드가 J7인 직원은 20% 인상
-- 직급 코드가 J6인 직원은 15% 인상
-- 직급 코드가 J5인 직원은 10% 인상
-- 그 외 직급은 5% 인상
-- 이름, 직급코드, 원래 급여, 인상률, 인상된 급여
SELECT EMP_NAME, JOB_CODE, SALARY,
DECODE(JOB_CODE, 'J7', '20%', 'J6', '15%', 'J5', '10%', '5%') 인상률,
DECODE(JOB_CODE, 'J7', SALARY 1.2,
'J6', SALARY 1.15,
'J5', SALARY 1.1,
SALARY 1.05) "인상된 급여"
FROM EMPLOYEE;
CASE WHEN 조건식 THEN 결과값
WHEN 조건식 THEN 결과값
ELSE 결과값
END
비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과값 반환
조건은 범위 값 가능
EMPLOYEE테이블에서
급여가 500만원 이상이면 '대'
급여가 300만원 이상 500만 미만 이면 '중'
급여가 300만원 미만이면 '소'
SELECT EMP_NAME, SALARY,
CASE
WHEN SALARY >= 5000000 THEN '대'
WHEN SALARY >= 3000000 THEN '중'
ELSE '소'
END "급여 받는 정도"
FROM EMPLOYEE;
그룹 함수
하나 이상의 행을 그룹으로 묶어 연산하여 총합, 평균 등의 하나의 결과 행으로 반환하는 함수
SUM (숫자가 기록된 컬럼명) : 합계
모든 직원의 급여 합
SELECT SUM(SALARY) FROM EMPLOYEE; -- 70096240
AVG (숫자가 기록된 컬럼명) : 평균
전직원 급여 평균
SELECT ROUND(AVG(SALARY)) FROM EMPLOYEE; -- 3047663
부서코드가 'D9'인 사원들의 급여 합, 평균
SELECT SUM(SALARY), ROUND(AVG(SALARY)) -- 3
FROM EMPLOYEE -- 1
WHERE DEPT_CODE = 'D9'; -- 2
MIN(컬럼명) : 최소값
MAX(컬럼명) : 최대값
타입 제한 없음 (숫자 : 대/소, 날짜 : 과거/미래, 문자열 : 문자 순서)
급여 최소값, 가장빠른 입사일, 알파벳순서가 가장빠른 이메일
SELECT MIN(SALARY), MIN(HIRE_DATE), MIN(EMAIL)
FROM EMPLOYEE;
급여 최대값, 가장늦은 입사일, 알파벳순서가 가장느린 이메일
SELECT MAX(SALARY), MAX(HIRE_DATE), MAX(EMAIL)
FROM EMPLOYEE;
EMPLOYEE 테이블에서 급여를 가장 많이 받는 사원의
이름, 급여, 직급 코드 조회
SELECT EMP_NAME, SALARY, DEPT_CODE
FROM EMPLOYEE
WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEE);
-- 서브쿼리 + 그룹함수
COUNT( | 컬럼명) : 행 개수를 헤아려서 리턴
COUNT([DISTINCT] 컬럼명) : 중복을 제거한 행 개수를 헤아려 리턴
COUNT() : NULL을 포함한 전체 행 개수 리턴
COUNT(컬럼명) : NULL을 제외한 실제 값이 기록된 행 개수를 리턴
EMPLOYEE 테이블의 행의 갯수
SELECT COUNT(*) FROM EMPLOYEE;
BONUS를 받는 사원 수
SELECT COUNT(BONUS) FROM EMPLOYEE; -- 9
SELECT COUNT(*) FROM EMPLOYEE
WHERE BONUS IS NOT NULL; -- 9
EMPLOYEE 테이블에서 성별이 남성인 사원의 수 조회
SELECT COUNT(*)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '1';
SELECT문 해석 순서
5: SELECT 컬럼명 AS 별칭, 계산식, 함수식
1: FROM 참조할 테이블명
2: WHERE 컬럼명 | 함수식 비교연산자 비교값
3: GROUP BY 그룹을 묶을 컬럼명
4: HAVING 그룹함수식 비교연산자 비교값
6: ORDER BY 컬럼명 | 별칭 | 컬럼순번 정렬방식[ NULLS FIRST / LAST ]
GROUP BY절 : 같은 값들이 여러개 기록된 컬럼을 가지고 같은 값들을 하나의 그룹으로 묶음
GROUP BY 컬럼명 | 함수식,...
여러개의 값을 묶어서 하나로 처리할 목적으로 사용함.
그룹으로 묶은 값에 대해서 SELECT절에서 그룹함수를 사용함.
EMPLOYEE 테이블에서 부서코드, 부서별 급여 합 조회
1) 부서코드만 조회
SELECT DEPT_CODE FROM EMPLOYEE; -- 23행
2) 전체 급여 합 조회
SELECT SUM(SALARY) FROM EMPLOYEE; -- 1행
SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE; -- DEPT_CODE가 같은 행 끼리 하나의 그룹이 됨
EMPLOYEE 테이블에서
직급코드가 같은 사람의 직급코드, 급여 평균, 인원 수를
직급코드 오름차순으로 조회
SELECT JOB_CODE, ROUND(AVG(SALARY)), COUNT(*)
FROM EMPLOYEE
GROUP BY JOB_CODE
ORDER BY JOB_CODE;
EMPLOYEE 테이블에서
성별(남/여)과 각 성별 별 인원 수, 급여 합을
인원 수 오름 차순으로 조회
SELECT DECODE(SUBSTR(EMP_NO , 8, 1), '1', '남', '2', '여') 성별,
COUNT(*) "인원 수",
SUM(SALARY)
FROM EMPLOYEE
GROUP BY DECODE(SUBSTR(EMP_NO , 8, 1), '1', '남', '2', '여') -- 별칭 사용이 X (SELECT절 해석 X)
ORDER BY "인원 수"; -- 별칭 사용이 O (SELECT절 해석 완료)
EMPLOYEE 테이블에서 부서코드가 D5, D6인 부서의 평균급여, 인원 수 조회
SELECT DEPT_CODE, ROUND(AVG(SALARY)), COUNT(*)
FROM EMPLOYEE
WHERE DEPT_CODE IN('D5', 'D6')
GROUP BY DEPT_CODE;
EMPLOYEE 테이블에서 직급별 2000년도 이후 (2000년도 포함) 입사자들의 급여 합을 조회
직급코드 오름차순
SELECT JOB_CODE, SUM(SALARY)
FROM EMPLOYEE
WHERE EXTRACT(YEAR FROM HIRE_DATE) >= 2000
-- HIRE_DATA >= TO_DATE('2000-01-01')
-- TO_NUMBER(SUBSTR(TO_CHAR(HIRE_DATE), 1, 4)) >= 2000
GROUP BY JOB_CODE
ORDER BY 1; -- 1 == JOB_CODE
EMPLOYEE 테이블에서 부서별로 같은 직급인 사원의 수를 조회
부서코드는 오름차순, 직급코드 내림차순
SELECT DEPT_CODE, JOB_CODE, COUNT(*)
FROM EMPLOYEE
GROUP BY DEPT_CODE, JOB_CODE -- DEPT_CODE로 그룹을 나누고,
-- 나눠진 그룹 내에서 JOB_CODE 또 그룹을 분류
ORDER BY DEPT_CODE, JOB_CODE DESC;
GROUP BY 사용시 주의사항
SELECT문에 GROUP BY절을 사용할 경우
SELECT절에 명시한 조회하려는 컬럼 중
그룹함수가 적용되지 않는 컬럼을모두 GROUP BY절에 작성해야함.
부서별 평균 급여가 300만원 이상인 부서를 조회(부서코드 오름차순)
SELECT DEPT_CODE, AVG(SALARY)
FROM EMPLOYEE
--WHERE AVG(SALARY >= 3000000) --> 한 사람의 급여가300백만 이상이라는 조건
GROUP BY DEPT_CODE
HAVING AVG(SALARY) >= 3000000 --> DEPT_CODE 그룹 중 급여 평균이 300백만 이상인 그룹만 남음
ORDER BY DEPT_CODE;
EMPLOYEE 테이블에서 직급별 인원수가 5명 이하인 직급코드, 인원 수 조회( 직급코드 오름차순 )
SELECT JOB_CODE, COUNT()
FROM EMPLOYEE
GROUP BY JOB_CODE
HAVING COUNT() <= 5 -- HAVING절에는 그룹 함수가 반드시 작성된다!!!
ORDER BY 1;
- UPPER 함수의 역할
SELECT EMP_NO, UPPER(EMP_NAME)
FROM EMPLOYEE;
-- 조회한 컬럼이 영문자일 경우 대문자로 바꿔주는 함수
