Study Web Development

11월 22일

Oracle SQL

2. SQL(Standard Query Language)

2-3 SELECT문

함수

날짜 함수

내부 숫자 형식(세기, 연, 월, 일, 시, 분, 초)으로 날짜 저장
SYSDATE

-- Oracle 서버의 현재 날짜와 시간을 반환
SELECT SYSDATE FROM dual;

산술 연산자 사용 가능

SELECT ename , ROUND((SYSDATE-hiredate)/7) AS weeks FROM emp; -- 두 날짜의 뺄셈 결과는 일 단위로 계산되며, SYSDATE가 시, 분, 초 정보를 갖고 있으므로 계산 결과도 소숫점 이하 자리를 갖게 됨

MONTHS_BETWEEN

-- 두 날짜 사이의 개월 수 반환
SELECT MONTHS_BETWEEN('2021-11-22', '2012-03-23') FROM dual;
SELECT MONTHS_BETWEEN('2012-03-23', '2021-11-22') FROM dual; -- 음수 반환
SELECT ename, TRUNC(MONTHS_BETWEEN(SYSDATE, hiredate)) months_worked
FROM emp ORDER BY months_worked;

ADD_MONTHS

-- 월에 지정한 정수를 더한 날짜를 반환
SELECT ADD_MONTHS('2021/01/01', 8) FROM dual;

NEXT_DAY

-- 기준일 다음으로 지정한 요일이 돌아오는 날짜를 반환
SELECT NEXT_DAY('2021-11-22', '금요일') FROM dual;
-- SELECT NEXT_DAY('2021-11-22', 'FRIDAY') FROM dual; -- 한글 윈도우에 설치되어 있기 때문에 영문 요일명 입력시 에러
SELECT NEXT_DAY('2021-11-22', 6) FROM dual; -- 1=일요일, 7=토요일

LAST_DAY

-- 월의 마지막 날짜 반환
SELECT LAST_DAY('2021-11-22') FROM dual;

실습 문제

-- 1) 오늘부터 이번 달의 마지막 날까지의 남은 일 수를 구하시오.
SELECT LAST_DAY(SYSDATE)-SYSDATE AS "남은 일 수" FROM dual;
-- 2) emp 테이블에서 각 사원에 대해 사원 번호, 이름, 급여 및 15% 인상된 급여를 정수로 표시하시오.
	-- 인상된 급여는 반올림하고 별칭은 New Salary로 지정하시오.
SELECT empno, ename, sal, ROUND(sal*1.15) AS "New Salary" FROM emp;
-- 3) 각 사원의 이름을 표시하고 근무 월 수(입사일로부터 현재까지의 월 수)를 계산하여 별칭은 MONTHS_WORKED로 지정하시오.
	-- 결과는 정수로 반올림하여 표시하고 근무 월 수를 기준으로 오름차순으로 정렬하시오.
SELECT ename, ROUND(MONTHS_BETWEEN(SYSDATE, hiredate)) AS MONTHS_WORKED
FROM emp ORDER BY MONTHS_WORKED;
-- 4) 이름(소문자로 표시), 업무, 근무 연차(소수점 이하 자리 절사)를 출력하시오.
SELECT LOWER(ename), job, TRUNC(MONTHS_BETWEEN(SYSDATE, hiredate)/12) AS "근무 연차" FROM emp;

변환 함수(Conversion Functions)

TO_CHAR

-- 날짜를 문자로 변환
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;
-- SELECT TO_CHAR(SYSDATE, 'YYYY년 MM월 DD일') FROM dual; -- 포맷 문자와 구분자 외에 다른 문자 입력시 에러
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') FROM dual; -- (자바와 달리) MM과 mm 모두 월을 나타내는 포맷 문자이며, MI가 분을 나타내는 포맷 문자

-- 숫자를 문자로 변환
SELECT TO_CHAR(1234, 9999) FROM dual;
SELECT TO_CHAR(1234, '9999') FROM dual;
SELECT TO_CHAR(1234, '0000') FROM dual;
SELECT TO_CHAR(1234, 0000) FROM dual; -- 포맷 문자 0은 작은 따옴표로 감싸지 않으면 #으로 출력됨
-- 포맷 문자를 실제 자리수보다 부족하게 지정하면 #으로 출력됨
SELECT TO_CHAR(1234, 999) FROM dual;
SELECT TO_CHAR(1234, '999') FROM dual;
SELECT TO_CHAR(1234, '000') FROM dual;
-- 포맷 문자 9를 실제 자리수보다 많게 지정하면 남는 자리는 공백으로 처리됨
SELECT TO_CHAR(1234, 99999) FROM dual;
SELECT TO_CHAR(1234, '99999') FROM dual;
-- 포맷 문자 0을 실제 자리수보다 많게 지정하면 남는 자리는 0으로 처리됨
SELECT TO_CHAR(1234, '00000') FROM dual;
-- 소수점 이하 자리
SELECT TO_CHAR(1234, 9999.99) FROM dual;
SELECT TO_CHAR(1234, '9999.99') FROM dual;
SELECT TO_CHAR(1234, '0000.00') FROM dual;
-- 반올림해서 소수점 이하 둘째 자리까지 표시
SELECT TO_CHAR(25.897, 99.99) FROM dual;
SELECT TO_CHAR(25.897, '99.99') FROM dual;
SELECT TO_CHAR(25.897, '00.00') FROM dual;
-- 인상된 급여를 소수점 이하 첫째 자리까지 표시
SELECT TO_CHAR(sal*1.15, '9,999.0') FROM emp; -- 포맷 문자에 , 사용시 작은 따옴표로 감싸지 않으면 에러
-- 통화 표시
SELECT TO_CHAR(1234, '$0000') FROM dual;
-- 지역 통화 표시
SELECT TO_CHAR(1234, 'L0000') FROM dual; -- 원화 표시를 직접 명시하면 에러

TO_DATE

-- 문자를 날짜로 변환
SELECT TO_DATE('2021-11-22', 'YYYY-MM-DD') FROM dual;
SELECT TO_DATE('2021-11-22') FROM dual; -- 포맷 형식 생략 가능

TO_NUMBER

-- 문자를 숫자로 변환
SELECT TO_NUMBER('100', '999') FROM dual;
SELECT TO_NUMBER('100', '000') FROM dual;
SELECT TO_NUMBER('100') FROM dual; -- 포맷 형식 생략 가능

실습 문제

-- 1) emp 테이블에서 각 사원에 대해 사원 번호, 이름, 급여 및 15% 인상된 급여를 정수로 표시하시오.
	-- 인상된 급여는 반올림하고 별칭은 New Salary로 지정(TO_CHAR 사용)
SELECT empno, ename, sal, TO_CHAR(sal*1.15, 9999) AS "New Salary" FROM emp;
-- 2) emp 테이블에서 각 사원의 이름을 표시하고 근무 월 수(입사일로부터 현재까지의 월 수)를 계산하시오.
	-- 별칭은 MONTHS_WORKED로 지정하고 결과는 정수로 반올림하여 표시하고 근무 월 수를 기준으로 오름차순 정렬(TO_CHAR)
SELECT ename, TO_CHAR(MONTHS_BETWEEN(SYSDATE, hiredate),'999') AS MONTHS_WORKED
FROM emp ORDER BY MONTHS_WORKED;
-- 3) emp 테이블에서 월급이 4의 배수인 사원의 이름과 월급을 출력하시오. (세 자리 단위 쉼표 표시, TO_CHAR)
SELECT ename, TO_CHAR(sal, '9,999') FROM emp WHERE MOD(sal, 4)=0;

일반 함수

NVL(값1, 값2)

-- 값1이 null이면 값2를 사용
-- 값1과 값2의 자료형이 일치해야 함
SELECT ename, comm, comm+sal*12 FROM emp;
SELECT ename, comm, NVL(comm, 0)+sal*12 FROM emp;
-- SELECT ename, NVL(comm, 'No Commission') AS comm FROM emp; -- 자료형이 일치하지 않아 에러
SELECT ename, NVL(TO_CHAR(comm), 'No Commission') AS comm FROM emp;

NVL2(값1, 값2, 값3)

-- 값1이 NULL이면 값3을 사용하고, NULL이 아니면 값2를 사용
-- 값2와 값3의 자료형이 일치해야 함
SELECT ename, comm, NVL2(comm, 'Commission', 'No Commission') FROM emp;

NULLIF(값1, 값2)

-- 두 값이 일치하면 NULL, 일치하지 않으면 값1 사용
SELECT LENGTH(ename), LENGTH(job), NULLIF(LENGTH(ename), LENGTH(job)) AS NULLIF FROM emp;

COALESCE(값1, 값2, 값3, ...)

-- NULL이 아닌 값을 사용
-- 값들의 자료형이 일치해야 함
SELECT comm, sal, COALESCE(comm, sal, 0) FROM emp; -- comm이 NULL인 경우 sal 사용
SELECT comm, mgr, sal, COALESCE(comm, mgr, sal) FROM emp; -- comm이 NULL인 경우 mgr 사용하고, mgr이 NULL인 경우 sal 사용

CASE 컬럼 WHEN 비교값 THEN 결과값 ... END

-- ANSI 표준 표현식으로 비교 연산자 사용 가능

-- 직업에 따른 보너스 지급
SELECT ename, sal, job,
	CASE job WHEN 'SALESMAN' THEN sal*0.1
		WHEN 'MANAGER' THEN sal*0.2
		WHEN 'ANALYST' THEN sal*0.3
		ELSE sal*0.4 -- ELSE 결과값은 생략 가능
	END "Bonus"
FROM emp;

-- 급여 등급 구하기
SELECT ename, sal,
	CASE WHEN sal>=4000 AND sal<=5000 THEN 'A'
		WHEN sal>=3000 AND sal<4000 THEN 'B'
		WHEN sal>=2000 AND sal<3000 THEN 'C'
		WHEN sal>=1000 AND sal<2000 THEN 'D'
		ELSE 'F'
	END "Grade"
FROM emp;

DECODE(컬럼, 비교값, 반환값, ... 반환값)

-- Oracle SQL 전용 함수로 = 비교만 가능

-- 직업에 따른 보너스 지급
SELECT ename, sal, job,
	DECODE(job, 'SALESMAN', sal*0.1,
		'MANAGER', sal*0.2,
		'ANALYST', sal*0.3,
		sal*0.4) AS "Bonus"
FROM emp;

-- 급여 등급 구하기
SELECT ename, sal,
	DECODE(TRUNC(sal/1000), 5, 'A',
		4, 'A',
		3, 'B',
		2, 'C',
		1, 'D',
		'F') AS "Grade"
FROM emp;

실습 문제

-- 1) emp 테이블에서 사원 이름, 월급, 월급과 커미션을 더한 값의 별칭을 실급여로 해서 출력하시오.
SELECT ename, sal, sal+NVL(comm, 0) AS 실급여 FROM emp;
-- 2) 월급과 커미션을 합친 금액이 2,000 이상인 사원의 이름, 업무, 월급, 커미션, 고용 날짜(1980-12-17 형태)를 출력하시오.
SELECT ename, job, sal, comm, TO_CHAR(hiredate, 'YYYY-MM-DD') AS "고용 날짜"
FROM emp WHERE sal+NVL(comm,0)>2000;
-- 3) DECODE 함수를 사용하여 다음 데이터에 따라 job 컬럼의 값을 기준으로 모든 사원의 등급을 표시하시오.
	-- 업무	등급
	-- PRESIDENT A
	-- ANALYST B
	-- MANAGER C
	-- SALESMAN D
	-- CLERK E
	-- 기타	0
SELECT ename, job, DECODE(job, 'PRESIDENT', 'A',
	'ANALYST', 'B',
	'MANAGER', 'C',
	'SALESMAN', 'D',
	'CLERK', 'E',
	0) AS grade FROM emp; -- 숫자가 문자로 자동 변환됨
SELECT ename, job, CASE job WHEN 'PRESIDENT' THEN 'A'
	WHEN 'ANALYST' THEN 'B'
	WHEN 'MANAGER' THEN 'C'
	WHEN 'SALESMAN' THEN 'D'
	WHEN 'CLERK' THEN 'E'
	ELSE '0' -- 숫자가 문자로 자동 변환되지 않으며 0 명시하면 에러
END AS grade FROM emp;

그룹 함수

하나 이상의 행을 그룹으로 묶어 연산을 수행하고 그룹별로 하나의 결과를 산출하는 함수
AVG()

-- 지정한 컬럼에서 NULL을 제외한 모든 관측치 값의 평균을 반환
SELECT ROUND(AVG(sal)) FROM emp;
SELECT AVG(comm) FROM emp;

COUNT()

-- 지정한 컬럼에서 NULL을 제외한 모든 관측치의 수를 반환
-- 특정 컬럼을 명시하지 않고 *를 인자로 사용시 NULL을 포함한 모든 관측치의 수를 반환

-- 총 사원 수
SELECT COUNT(*) FROM emp;
SELECT COUNT(empno) FROM emp; -- empno는 NULL을 허용하지 않는 변수

-- 10번 부서의 사원 수
SELECT COUNT(*) FROM emp WHERE deptno=10;

-- 커미션 계약이 되어 있는 사원(NULL 제외)
SELECT COUNT(comm) FROM emp;

MAX()

-- 지정한 컬럼의 관측치 가운데 가장 큰 값을 반환
SELECT MAX(sal) FROM emp;
SELECT MAX(empno) FROM emp;
SELECT MAX(ename) FROM emp;
SELECT MAX(hiredate) FROM emp;

MIN()

-- 지정한 컬럼의 관측치 가운데 가장 작은 값을 반환
SELECT MIN(sal) FROM emp;
SELECT MIN(empno) FROM emp;
SELECT MIN(ename) FROM emp;
SELECT MIN(hiredate) FROM emp;

SUM()

-- 지정한 컬럼의 모든 관측치 값을 더하여 반환
SELECT SUM(sal) FROM emp;

사원들의 급여의 최댓값, 최솟값, 평균값, 합계 구하기

SELECT MAX(sal), MIN(sal), ROUND(AVG(sal),2), SUM(sal) FROM emp;
SELECT MAX(sal), MIN(sal), ROUND(AVG(sal),2), SUM(sal) FROM emp WHERE deptno=10;
SELECT COUNT(*) FROM emp WHERE deptno=20;

GROUP BY절 & HAVING절

GROUP BY절

-- SELECT문에 그룹 함수 사용시, 그룹 함수를 적용하지 않은 단순 컬럼은 GROUP BY절에 명시한 경우에만 사용 가능

-- 부서별 최고 급여, 최저 급여, 평균 급여, 합계 급여 구하기
SELECT deptno, MAX(sal), MIN(sal), ROUND(AVG(sal),2), SUM(sal)
FROM emp GROUP BY deptno; -- GROUP BY에 deptno를 명시하지 않으면 에러

-- 부서에서 직업별 최고 급여
SELECT deptno, job, MAX(sal)
FROM emp GROUP BY deptno, job ORDER BY deptno;
-- FROM emp ORDER BY deptno GROUP BY deptno, job; -- GROUP BY절이 ORDER BY절보다 선행하지 않으면 에러

-- 10번 부서를 제외한 부서별 합계 급여 구하기
SELECT deptno, SUM(sal) FROM emp WHERE deptno!=10 GROUP BY deptno;
-- SELECT deptno, SUM(sal) FROM emp GROUP BY deptno WHERE deptno!=10; -- WHERE절이 GROUP BY절보다 선행하지 않으면 에러

HAVING절

-- 그룹 처리 결과를 제한하고자 할 때 이용

-- 평균 급여가 2000 이상인 부서들의 평균 급여 구하기
-- SELECT deptno, ROUND(AVG(sal))
-- FROM emp WHERE ROUND(AVG(sal))>=2000 GROUP BY deptno; -- 그룹 함수의 결과값을 제한할 경우 HAVING절을 사용하지 않으면 에러
SELECT deptno, ROUND(AVG(sal))
FROM emp HAVING ROUND(AVG(sal))>=2000 GROUP BY deptno;
SELECT deptno, ROUND(AVG(sal))
FROM emp GROUP BY deptno HAVING ROUND(AVG(sal))>=2000; -- HAVING절은 일반적으로 GROUP BY절 다음에 배치

실습 문제

-- 1) 모든 사원의 급여 최고액, 최저액, 총액 및 평균액을 표시하시오.
	-- 별칭은 maximum, minimum, sum 및 average로 지정하고 결과를 정수로 반올림한 후 세 자리 단위로 ,를 명시하시오.
SELECT TO_CHAR(MAX(sal), '9,999') "maximum", TO_CHAR(MIN(sal), '9,999') "minimum",
	TO_CHAR(SUM(sal), '99,999') "sum", TO_CHAR(ROUND(AVG(sal)), '9,999') "average" FROM emp;
-- 2) 업무별 사원 수를 표시하시오.
SELECT job 업무, COUNT(*) "사원 수" FROM emp GROUP BY job;
-- 3) emp 테이블에서 30번 부서의 사원 수를 구하시오.
SELECT COUNT(*) "사원 수" FROM emp WHERE deptno=30;
-- 4) emp 테이블에서 업무별 최고 급여를 구하고 업무, 최고 급여를 출력하시오.
SELECT job AS 업무, MAX(sal) AS "최고 급여" FROM emp GROUP BY job;
-- 5) emp 테이블에서 부서별로 지급되는 총 월급이 9000 이상인 부서 번호와 총 월급을 출력하시오.
SELECT deptno "부서 번호", SUM(sal) "총 월급" FROM emp
GROUP BY deptno HAVING SUM(sal)>9000;
-- 6) emp 테이블에서 업무별로 사번이 제일 늦은 사람을 구하고 그 결과 내에서 사번이 79로 시작하는 결과만 보여주시오.
SELECT job, MAX(empno) FROM emp WHERE empno LIKE '79%' GROUP BY job;
SELECT job, MAX(empno) FROM emp GROUP BY job HAVING MAX(empno) LIKE '79%';
-- 7) emp 테이블에서 업무별 총 월급을 출력하시오.
-- 업무가 'MANAGER'인 경우는 제외하고, 총 월급이 5000보다 많은 경우를 출력하시오.
SELECT job, SUM(sal) FROM emp WHERE job!='MANAGER'
GROUP BY job HAVING SUM(sal)>5000;
SELECT job, SUM(sal) FROM emp
GROUP BY job HAVING SUM(sal)>5000 AND job!='MANAGER';
-- 8) emp 테이블에서 업무별 사원의 수가 4명 이상인 업무와 인원 수를 출력하시오.
SELECT 	job 업무, COUNT(*) AS "인원 수" FROM emp
GROUP BY job HAVING COUNT(*)>=4;

다음으로