이번 시간에는 오라클에 있는 각종 함수를 활용하는 예제를 올려봅니다.
예제와 설명을 같이 쓰면서 글을 작성하겠습니다.
==========================================================
변환 함수
TO_CHAR(날짜,출력패턴) : DATE 형식인 날짜를 출력패턴에 맞게 VARCHAR2로 변환하여 반환
TO_CHAR(숫자,출력패턴) : NUMBER형식인 숫자를 출력패턴에 맞게 VARCHAR2 반환
SELECT NAME, REG_DATE, TO_CHAR(REG_DATE,'YYYY-MM-DD') FROM MEMBER;
[1] 고객(MEMEBER) 테이블에서 등록 년도가 2012년인 고객의 정보를 보여주세요
SELECT NAME,REG_DATE,JOB FROM MEMBER WHERE TO_CHAR(REG_DATE, 'YY')=12;
SELECT TO_CHAR(10000,'99,999') FROM DUAL;
[2] 상품(PRODUCTS) 테이블에서 상품의 공급 금액을 가격 표시 방법으로 표시하세요 W800,000
SELECT PRODUCTS_NAME, INPUT_PRICE, TO_CHAR(INPUT_PRICE,'L9,999,999') FROM PRODUCTS;
TO_NUMBER(값, 출력형식) : CHAR 또는 VARCHAR2를 NUMBER값으로 변환하여 반환
TO_DATE(값, 출력형식) : CHAR/VARCHAR2를 DATE 형식으로 변환하여 반환
SELECT TO_NUMBER('8,500', '9,999') * 100 FROM DUAL;
$1,234=> 숫자로 변환하여 2배로 불렀을 때 값을 출력하세요
SELECT TO_NUMBER('$1,234','$9,999') * 2 FROM DUAL;
SELECT TO_NUMBER('123.45','999D99')*2 FROM DUAL;
SELECT TO_DATE('20161201','YYYYMMDD') FROM DUAL;
20161201년도까지 몇일이 남았는지 출력하세요
DATE - DATE => 일수
SELECT SYSDATE-TO_DATE('20161201','YYYYMMDD') FROM DUAL;
그룹함수
COUNT(컬럼)=>NULL 값은 제외하고 카운트를 센다.
전체 사원이 몇명인지 출력하세요
SELECT * FROM EMP;
SELECT COUNT(EMPNO) "전체 사원수",
COUNT(MGR) "관리자를 갖는 사원수",
COUNT(COMM) "보너스를 받는 사원수"
FROM EMP;
관리자수를 출력하세요
SELECT COUNT(DISTINCT MGR)"관리자수" FROM EMP;
SELECT COUNT(*) FROM EMP;
COUNT(*)==> NULL 값을 인정한다.
CREATE TABLE TEST(
ID NUMBER,
NAME VARCHAR(20));
SELECT COUNT(*) FROM TEST;
INSERT INTO TEST VALUES(NULL, NULL);
COMMIT;
SELECT * FROM TEST;
SELECT COUNT(ID), COUNT(*) FROM TEST;
EMP의 급여합계, 최소급여, 최대급여, 급여 평균을 구하세요
SELECT SUM(SAL), MIN(SAL), MAX(SAL), ROUND(AVG(SAL),2) FROM EMP;
SELECT MIN(ENAME), MAX(ENAME), MIN(HIREDATE), MAX(HIREDATE) FROM EMP;
[3] EMP 테이블에 등록되어 있는 부서의 수, 보너스 받는 인원수, 보너스 합계, 보너스 평균을 구해 출력하세요
SELECT COUNT(DISTINCT DEPTNO)"부서 수", COUNT(COMM)"보너스 받는 인원수", SUM(SAL)"보너스 합계", AVG(SAL)"보너스 평균" FROM EMP;
부서별 평균 급여를 구해 출력하세요
GROUP BY 절을 이용
: 특정 컬럼이나 값을 기준으로 해당 레코드를 묶어서 자료를 관리할 때 사용한다. 그룹 함수와 함께 사용
SELECT DEPTNO, ROUND(AVG(SAL),2) FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;
SELECT 할 때 GROUP BY 에서 사용된 컬럼과 그룹함수만 가져올 수 있다.
업무별 금여 총액을 구하세요
SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB;
[4] EMP에서 입사년도별 몇명이 입사했는지 출력하세요
SELECT TO_CHAR(HIREDATE,'YY'), COUNT(*)
FROM EMP
GROUP BY TO_CHAR(HIREDATE,'YY') ORDER BY 1;
[5] 상품 테이블에서 상품 카테고리별로(CATEGORY_FK)
총 몇개의 상품이 있는지, 최대 판매기, 최소 판매기를 함께 보여주세요
SELECT CATEGORY_FK, COUNT(PRODUCTS_NAME), MAX(OUTPUT_PRICE), MIN(OUTPUT_PRICE)
FROM PRODUCTS
GROUP BY CATEGORY_FK ORDER BY 1;
[6] 회원(MEMBER)테이블에서 직업의 종류별로 최대 마일리지를 보여주세요
SELECT JOB, MAX(MILEAGE) FROM MEMBER GROUP BY JOB;
HAVING절
-GROUP BY와 항상 함께 사용한다
-GROUP BY 결과에 조건을 주어 재현할 때 사용한다.
[7] EMP에서 업무(JOB)별 인원수(COUNT)를 출력하되, 각 업무에 속한 사람의 수가 3명 이상인 업무 군의 정보를 보여주세요
SELECT JOB, COUNT(EMPNO)
FROM EMP
GROUP BY JOB
HAVING COUNT(EMPNO)>=3
ORDER BY JOB DESC;
WGHO 순서 외우자
[8] 고객테이블에서 직업에 종류와 각 직업에 속한 최대 마일리지를 보여주되, 최대 마일리지가 0인 경우는 제외 시키세요
SELECT JOB, MAX(MILEAGE)
FROM MEMBER
GROUP BY JOB
HAVING MAX(MILEAGE)<>0;
[9] 상품테이블에서 각 공급업체 코드(EP_CODE_FK) 별로 상품 판매가의 평균 값 중 단위가 100단위로 떨어지는 항목의 정보(EP_CODE_FK, 평균판매가)를 보여주세요
SELECT EP_CODE_FK, AVG(OUTPUT_PRICE)
FROM PRODUCTS
GROUP BY EP_CODE_FK
HAVING MOD(AVG(OUTPUT_PRICE) ,100)=0;
또는 아래와 같이 처리가 가능하다
SELECT EP_CODE_FK, AVG(OUTPUT_PRICE)
FROM PRODUCTS
GROUP BY EP_CODE_FK
HAVING AVG(OUTPUT_PRICE) LIKE '';
기타 함수
- NVL(컬럼, 값1) : 컬럼값이 NULL일 경우 값1로 대처한다.
- NVL2(컬럼, 값1, 값2) : 컬럼값이 NULL이 아닐 경우 값1을 반환하고 NULL일 경우는 값2를 반환
SELECT ENAME, COMM, NVL2(COMM, COMM||'', '보너스 없음') FROM EMP;
- DECODE(컬럼, 검색값, 결과값, 기본값) :
DECODE(expr, search, result, default)
: expr에서 search 값을 비교하여 같으면 result 값을 반환하고, 같지 않으면 default 값을 반환한다.
SELECT ENAME, JOB, DECODE(JOB, 'SALESMAN', '영업사원', '사원')"DECODE"
FROM EMP WHERE DEPTNO=30;
[10] 30번 부서 사원들을 분류하되 'SALESMAN'은 영업사원, 'MANAGER'는 관리자, 그 외의 사원은 '일반사원'으로 출력하세요
SELECT ENAME, JOB, DECODE(JOB, 'SALESMAN', '영업사원', 'MANAGER', '관리자', '일반사원')"사원분류"
FROM EMP
WHERE DEPTNO=30;
위 문장을 CASE 문을 이용해 구현해보면
SELECT ENAME, JOB,
CASE JOB
WHEN 'SALESMAN' THEN '영업사원'
WHEN 'MANAGER' THEN '관리자'
ELSE '일반사원'
END CASE
FROM EMP WHERE DEPTNO=30;
