기록/자바_국비
[배운내용정리] 1215 자바 국비교육 _ Oracle
mois
2020. 12. 26. 05:46
728x90
2020년 12월 15일 9시 ~ 15시 30분 zoom으로 수업 진행
12월 14일은 학교 시험때문에 학원 휴가 냈당
--날짜 처리 함수
--SYSDATE : 현재 컼퓨터의 날짜를 반환하는 함수
SELECT SYSDATE
FROM DUAL;
--MONTHS_BETWEEN : 두 날짜 사이의 개월 수
SELECT HIRE_DATE 입사일,
MONTHS_BETWEEN(SYSDATE, HIRE_DATE) "입사 후 개월 수"
FROM employee;
--370,XXX 일케 나오는데 그냥 370개월 지났다고 생각하면 된다. 나누기 10 해주면 된다
--ADD_MONTHS : 날짜에 개월 수 더해주기
SELECT ADD_MONTHS(SYSDATE, 6) FROM DUAL;
--EXTRACT : 지정한 날짜로부터 날짜 값을 추출하는 함수
SELECT EXTRACT(YEAR FROM HIRE_DATE),
EXTRACT(MONTH FROM HIRE_DATE),
EXTRACT(DAY FROM HIRE_DATE)
FROM EMPLOYEE;
--형변환 함수
--TO DATE() , TO_CHAR() , TO_NUMBER()
--TO_CHAR()
--날짜 정보 변경
SELECT HIRE_DATE,
TO_CHAR(HIRE_DATE, 'YYYY-MM-DD'),
TO_CHAR(HIRE_DATE, 'YY-MON-DD')
FROM employee;
--숫자 정보 변경
--L은 \표시
--999는 그냥 숫자를 표시
--000은 저만큼 자리를 잡아야 하는걸로 생각
--기본 100만원부터일 때 TO_CHAR(SALARY, 'L999,999')로 백만원 이하로 표시하면 #############출력 TO_CHAR(SALARY, 'L000,000')도 마찬가지
SELECT SALARY,
TO_CHAR(SALARY, 'L999,999,999'),
TO_CHAR(SALARY, 'L000,000,000')
FROM EMPLOYEE;
--TO_DATE()
SELECT 20201215,
TO_DATE(20201215, 'yyyymmdd'),
TO_DATE(20201215, 'YYYY/MM/DD')
FROM DUAL;
--DECODE()
--JAVA의 3항 연산자라고 생각하자.
--DECODE(컬럼|데이터, 비교값, 결과1, 비교값2, 결과2 , ..., 기본값)
--현재 근무하는 직원의 성별을 남 , 여로 구분짓기
SELECT EMP_NAME, EMP_NO,
DECODE(SUBSTR(EMP_NO, 8, 1), '2', '여' , '1', '남') 성별
FROM EMPLOYEE
ORDER BY 성별;
--실습1
--EMPLOYEE 테이블에서
--모든 직원의 사번, 사원명, 부서코드, 직급코드, 근무여부, 관리자 여부를 조회하되
--만약 근무여부가 'Y' 퇴사자
-- 'N' 근무자,
--관리자 사번(MANAGER_ID)이 있으면 사원, 없으면 관리자
--로 작성해서 조회
--사번EMP_NO, 사원명EMP_NAME, 부서코드_DEPT_CODE, 직급코드JOB_CODE, 근무여부ENT_YN, 관리자 여부MANAGER_ID (NULL 존재)
SELECT * FROM EMPLOYEE;
SELECT EMP_NO 사번, EMP_NAME 사원명, DEPT_CODE 부서코드, JOB_CODE 직급코드,
DECODE(ENT_YN, 'N', '퇴사자', 'Y', '근무자') "근무여부",
DECODE(MANAGER_ID, NULL , '관리자' , '사원') "관리자여부"
FROM EMPLOYEE;
--NULL 을 데이터로 인식하는 느낌인데, NOT NULL은 값으로 보기 어려워서 NOT NULL 입력하면 에러난다
--CASE 문
--자바의 IF, SWITCH
--CASE
-- WHEN(조건식1) THEN 결과값1
-- WHEN(조건식2) THEN 결과값2
-- ELSE 결과값3
--END
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE,
CASE
WHEN ENT_YN = 'Y' THEN '퇴사자'
ELSE '근무자'
END "근무 여부",
CASE
WHEN MANAGER_ID IS NULL THEN '관리자'
ELSE '사원'
END "관리자 여부"
FROM EMPLOYEE;
--숫자 데이터 함수
--ABS() : 특정 숫자의 절대값 표현
SELECT ABS(10), ABS(-10)
FROM DUAL;
--MOD() : 나머지를 반환하는 함수 -> 정수
SELECT MOD(10, 3) , MOD(10, 2), MOD(10, 7)
FROM DUAL;
--ROUND() : 지정한 숫자를 반올림 할 때 사용
--ROUND(숫자) : 이렇게 하면 소수점 첫번째 자리를 반올림 해버린다. 0이 디폴트
--ROUND(123.456, 2) : 123.46으로 소수점 세번째 자리를 반올림 해서, 결과적으로 소수점 첫번째 자리까지 출력시킨다.
SELECT ROUND(123.456),
ROUND(123.456, 0),
ROUND(123.456, 1),
ROUND(123.456, 2),
ROUND(123.456, -1),
ROUND(123.456, -2)
FROM DUAL;
--CEIL() : 소수점 첫째 자리에서 올림하는 함수
--FLOOR() : 소수점 이하 자리를 버리는 함수
SELECT CEIL(123.456), FLOOR(123.456)
FROM DUAL;
--TRUNC() : 지정한 위치까지 버리는 함수
SELECT TRUNC(123.456, 0), TRUNC(123.456, 1), TRUNC(123.456, 2),
TRUNC(123.456, -1),
TRUNC(123.456, -2),
TRUNC(123.456, -3)
FROM DUAL;
--TRUNC(123.456, 0) : 123 -> 소수점 0번째자리 남기고 다 버림 -> 소수점 다 버림
--TRUNC(123.456, 1) : 123.4 -> 소수점 1번째자리 남기고 다 버림
--TRUNC(123.456, 2) : 123.45
--TRUNC(123.456, -1): 120 -> 일의 자리 버림
--TRUNC(123.456, -2): 100 -> 십의 자리까지 다 버림
--TRUNC(123.456, -3): 0 -> 0
--실습2
--EMPLOYEE 테이블에서
--입사한 달의 숫자가 홀수 달인
--직원의 사번, 사원명, 입사일 정보를 조회
--사번(EMP_NO), 사원명(EMP_NAME), 입사일(HIRE_DATE)
SELECT EMP_NO 사번, EMP_NAME 사원명, HIRE_DATE 입사일
FROM EMPLOYEE
WHERE MOD(EXTRACT(MONTH FROM HIRE_DATE), 2) != 0;
SELECT EMP_NO 사번, EMP_NAME 사원명, HIRE_DATE 입사일
FROM EMPLOYEE
WHERE MOD(SUBSTR(HIRE_DATE, 5, 1), 2) = 1;
--날짜 데이터
--SYSDATE, MONTHS_BETWEEN, ADD_MONTHS
--EXTRACT, LAST_DAY, NEXT_DAY
--오늘 날짜 불러오는 함수
--SYSDATE : 오늘 날짜만을 표시
--SYSTIMESTAMP : 시간까지 표시
SELECT SYSDATE, SYSTIMESTAMP FROM DUAL;
--NEXT_DAY() : 앞으로 다가올 가장 가까운 요일을 반환
SELECT NEXT_DAY(SYSDATE, '토요일'),
NEXT_DAY(SYSDATE, '토'),
NEXT_DAY(SYSDATE, 7)
FROM DUAL;
--LAST_DAY() : 주어진 날짜의 마지막 일자를 조회하는 함수
SELECT LAST_DAY(SYSDATE)
FROM DUAL;
--날짜는 +, - 연산 가능
--가장 최근 날짜 일수록 큰 값으로 판단한다
SELECT SYSDATE "오늘 날짜",
(SYSDATE-10)"날짜1",
TRUNC(SYSDATE-TO_DATE('19/03/01', 'YY/MM/DD'))"날짜2"
FROM DUAL;
--실습3
--EMPLOYEE 테이블에서
--근무년수가 20년 이상인 사원들의
--사번, 사원명, 부서코드, 입사일을 조회
--MONTHS_BETWEEN(), ADD_MONTHS() 등을 활용
--사번(EMP_NO), 사원명(EMP_NAME), 부서코드(DEPT_CODE), 입사일(HIRE_DATE)
--내가 푼 것
SELECT EMP_NO 사번, EMP_NAME 사원명, DEPT_CODE 부서코드, HIRE_DATE 입사일
FROM EMPLOYEE
WHERE EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) >= 20;
--선생님 코드
SELECT EMP_NO 사번, EMP_NAME 사원명, DEPT_CODE 부서코드, HIRE_DATE 입사일
FROM EMPLOYEE
WHERE ADD_MONTHS(HIRE_DATE, 240) <= SYSDATE;
SELECT EMP_NO 사번, EMP_NAME 사원명, DEPT_CODE 부서코드, HIRE_DATE 입사일
FROM EMPLOYEE
WHERE MONTHS_BETWEEN(SYSDATE, HIRE_DATE)/12 >= 20;
--형변환
--TO_CHAR()
--날짜 정보를 특정 형식의 문자로 변경하여 조회
--HH24:MI:SS 시(24) 분 초 AM은 오전 오후 펴시
SELECT TO_CHAR(SYSDATE, 'AM HH24:MI:SS'),
TO_CHAR(SYSDATE, 'AM HH:MI:SS')
FROM DUAL;
--DAY는 화요일 , DY는 화
--YEAR는 숫자가 아닌 영어로 년도가 나오고
--Q는 분기가 나온다 12월인 현재 출력하면 4가 나온다 4분기~
SELECT TO_CHAR(SYSDATE, 'MON, YYYY'),
TO_CHAR(SYSDATE, 'MON DY, YYYY'),
TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY'),
TO_CHAR(SYSDATE, 'YEAR, Q')
FROM DUAL;
--TO_DATE('190325', 'YYMMDD') 를 이용해 문자열 '190325'를 날짜데이터 190325으로 변경 후
--날짜 데이터를 원하는 데이터 형식 'YYYY'로 형변환
SELECT TO_CHAR(TO_DATE('190325', 'YYMMDD'), 'YYYY') 결과1,
TO_CHAR(TO_DATE('190325', 'RRMMDD'), 'RRRR') 결과2,
TO_CHAR(TO_DATE('800325', 'YYMMDD'), 'YYYY') 결과3,
TO_CHAR(TO_DATE('800325', 'RRMMDD'), 'YYYY') 결과3_1,
TO_CHAR(TO_DATE('800325', 'RRMMDD'), 'RRRR') 결과4
FROM DUAL;
--결과3은 1980을 원했는데 2080이 나와버림
--결과3_1은 1980이 나온다 -> 입력받은날짜를 날짜 데이터로 저장할 때 RR(반세기) 기준으로 저장했기 때문
--결과4는 1980이 잘 나온다
--년도를 4자리 입력받았으면 1980으로 다 잘 나오는데
--YY는 현세기를 기준으로 연산
--뭘 입력받든 현세기인 2000년대 기준으로 연산
--80을 입력받으면 2080
--RR은 반세기를 기준으로 연산
--51~99를 입력받으면 1900년대
--00~50을 입력받으면 2000년대로 생각
--TO_NUMBER() : 주어진 값을 숫자로 변경
SELECT '123'+'567'
FROM DUAL;
--'123'+'567'의 결과로는 숫자 2개가 더한 690이 나옴
--숫자가 아니라고 에러난다
--오라클은 문자열 연산 할 때 concat사용해야함
SELECT '123'+'567abc'
FROM DUAL;
SELECT to_number('123456')
FROM DUAL;
--실습4
--오늘은 연봉 협상의 날
--다음에 해당하는 사원들의 급여를 인상하고자 한다
--직급코드가 J5인 사원들은 급여의 20%,
-- J6인 사원들은 급여의 15%,
-- J7인 사원들은 급여의 10%
--그외의 직급은 급여의 5%를 인상하려고 할 때,
--EMPLOYEE 테이블에서
--해당 조건을 만족하는 사원들의 사번, 사원명, 직급코드, 인상된 급여 정보를 조회
--사번 EMP_ID , 사원명 EMP_NAME, 직급코드 JOB_CODE, 급여 SALARY
SELECT EMP_ID,
EMP_NAME,
JOB_CODE,
SALARY "원래 급여",
SALARY * DECODE(JOB_CODE, 'J5', 1.2, 'J6' , 1.15, 'J7', 1.1 , 1.05) "인상된 급여"
FROM EMPLOYEE;
--CASE
SELECT EMP_ID,
EMP_NAME,
JOB_CODE,
SALARY * CASE
WHEN JOB_CODE = 'J7' THEN 1.1
WHEN JOB_CODE = 'J6' THEN 1.15
WHEN JOB_CODE = 'J5' THEN 1.2
ELSE 1.05
END "인상된 급여"
FROM EMPLOYEE
ORDER BY "인상된 급여";
--ORDER BY는 별칭으로도 사용 가능!
--SELECT 문의 실행 순서
/*
5 : SELECT 컬럼명 AS 별칭, 계산식, 함수, 리터럴 ...
1 : FROM 테이블명
2 : WHERE 조건
3 : GROUP BY 그룹을 묶을 컬럼명
4 : HAVING 그룹에 대한 조건식, 함수식
6 : ORDER BY 컬럼명 | 별칭 | 컬럼의 순서 ;
*/
--ORDER BY
SELECT EMP_ID, EMP_NAME 이름, SALARY, DEPT_CODE
FROM EMPLOYEE
--ORDER BY EMP_NAME;--기본 값 : ASC 오름차순
--ORDER BY EMP_NAME DESC;--내림차순
--ORDER BY DEPT_CODE DESC, EMP_ID;
--ORDER BY 이름 DESC;
ORDER BY 2 DESC;--1은 SELECT 안에 적은 첫 번째 컬럼 : EMP_ID, 2는 두번째 컬럼 : EMP_NAME 이름, 3은 세번째 컬럼 : SALARY
--GROUP BY
--급여 평균
SELECT TRUNC(AVG(SALARY), -3)
FROM EMPLOYEE;
--D1 평균 급여
SELECT TRUNC(AVG(SALARY), -3)
FROM EMPLOYEE
WHERE DEPT_CODE='D1';
--GROUP BY를 통해 특정 컬럼이나 계산식을 하나의 그룹으로 묶어
--한 테이블 내에서 소그룹별로 조회하고자 할 때 사용
SELECT DEPT_CODE, TRUNC(AVG(SALARY), -3)
FROM EMPLOYEE
GROUP BY DEPT_CODE;
--GROUP BY로 DEPT_CODE를 그룹별로 묶고, 해당 그룹의 평균을 출력시킴
--실습5
--EMPLOYEE 테이블에서
--부서별 총 인원, 급여 합계, 급여 평균, 최대 급여, 최소 급여를 조회하여
--부서 코드 오름차순으로 정렬
--숫자 데이터는 100자리까지만 처리, 100미만 자리는 내림 처리하여 조회
SELECT DEPT_CODE "부서",
COUNT(*) "총 인원",
SUM(SALARY) "급여 합계",
TRUNC(AVG(SALARY), -2) "급여 평균",
MAX(SALARY) "최대 급여" ,
MIN(SALARY) "최소 급여"
FROM employee
GROUP BY DEPT_CODE
ORDER BY 1;
--COUNT(DEPT_CODE)하지 않는 이유 -> NULL은 연산하지 않음;
--실습6
--EMPLOYEE 테이블에서
--직급코드와 직급코드 별 보너스를 받는 사원수를 조회
--직급 코드 순으로 내림차순 정렬하여
--내 코드
SELECT JOB_CODE "직급코드", COUNT(BONUS) "보너스 받는 사원 수"
FROM EMPLOYEE
GROUP BY JOB_CODE
ORDER BY 1 DESC;
--선생님 코드
SELECT JOB_CODE "직급코드", COUNT(BONUS) ||'명' "사원 수"
FROM EMPLOYEE
GROUP BY JOB_CODE
ORDER BY 1 DESC;
--EMPLOYEE 테이블에서
--남성 직원과 여성 직원의 수를 조회
--GROUP BY 에서 주어진 컬럼만이 아닌 함수 식도 사용 가능
SELECT DECODE(SUBSTR(EMP_NO, 8, 1), 1 , '남성', 2, '여성') 성별,
COUNT(*)||'명' 직원수
FROM EMPLOYEE
GROUP BY SUBSTR(EMP_NO, 8, 1);
--맞는지는 모르겠는데 내가 이해한대로 설명쓰
--GROUP BY로 SUBSTR(EMP_NO, 8, 1) : EMP_NO의 8번째 문자를 가져온게 1인 것과 아닌것으로 그룹을 묶고
--묶은 그룹을 SELECT에서 DECODE를 통해 묶은 그룹의 8번째 문자 값이 1이면 '남성'으로 분류, 2이면 '여성'으로 분류 해서 테이블에 값을 넣고
--묶은 그룹을 1인경우, 2인경우를 COUNT를 이용해 숫자 세서 직원수 컬럼에 출력
--HAVING 구문
--GROUP BY 한 각 소그룹에 대한 조건 설정
--그룹 함수와 함께! 사용하는 조건 구문
SELECT DEPT_CODE,
AVG(SALARY) 평균
FROM EMPLOYEE
WHERE SALARY > 3000000
GROUP BY DEPT_CODE;
--(FROM)EMPLOYEE 테이블에서 , (WHERE) SALARY가 300만이 넘는 경우만 조회하는데,
--(GROUP BY) 그 조회한 걸 DEPT_CODE로 그룹지었다 -> 급여가 300만이 넘는 DEPT_CODE들을 그룹지음
--(SELECT) 그룹 지은 것에서 DEPT_CODE 컬럼을 출력하고, 그 그룹지은 것에서 급여를 평균내서 컬럼 출력했다
SELECT DEPT_CODE,
AVG(SALARY) 평균
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING AVG(SALARY) > 3000000
ORDER BY 1;
--최종적으로 그룹핑한거에서 300만이 넘는 결과만 나온다. -> 그룹의 조건
--실습7
--부서별 그룹의 급여 합계 중 900만원을 초과하는
--부서의 코드와 급여 합계를 조회하시오
SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING SUM(SALARY) > 9000000;
--실습8
--급여 합계가 가장 높은 부서를 찾고
--해당 부서의 부서 코드와 급여 합계를 조회하시오
--1) 급여 합계가 가장 높은 부서
SELECT MAX(SUM(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE;
--2) 급여 합계가 가장 높은 급여 합계와 같은 부서
SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING SUM(SALARY) = 17700000;
--꼭 이렇게 값이 뭔지 구하고 조회를 해야하는가?
--SUB QUERY
SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE);
--실습9
--EMPLOYEE 테이블에서 직급 별
--그룹을 편성하여, 직급코드, 급여 합계, 급여 평균, 인원수 를 조회
--단, 조회 결과는 인원수 내림차순으로 출력
--인원수는 3명을 초과하는 직급만을 조회
SELECT JOB_CODE "직급코드", SUM(SALARY) "급여 합계", TRUNC(AVG(salary), -2) "급여 평균", COUNT(*) "인원 수"
FROM EMPLOYEE
GROUP BY JOB_CODE
HAVING COUNT(*) > 3
ORDER BY "인원 수";