ORACLE SQL 숫자·날짜·변환·일반·그룹함수 SCOTT 문제풀이

숫자함수

1.1. -15의 절대값 반환

SELECT ABS(-15)
FROM DUAL;

1.2. 34.5678의 소수점 아래를 버림

SELECT FLOOR(34.5678)
FROM DUAL;

2. 653.54의 십 단위에서 반올림

SELECT ROUND(653.53, -2)
FROM DUAL;

3.1. 78.901의 일의 자리수를 버린 값 

SELECT TRUNC(78.901, -1)
FROM DUAL;

3.2. 34를 7로 나눈 나머지값 반환

SELECT MOD(34, 7)
FROM DUAL;

3.3. 사번이 짝수인 사원들의 사번과 이름과 직급을 출력

SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE MOD(EMPNO, 2) =0;

 

날짜함수

1. 어제 날짜를 반환

SELECT SYSDATE-1
FROM DUAL;

1.2. 부서 번호가 10인 사원을 대상으로 입사한 날로부터 오늘까지 며칠이 흘렀는지 근무 일수 구하기

SELECT ENAME, TRUNC(SYSDATE-HIREDATE)
FROM EMP
WHERE DEPTNO = 10;

2. 부서 번호가 10인 사원을 대상으로 입사한 날로부터 오늘까지 몇 개월이 흘렀는지 

SELECT ENAME, TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE))
FROM EMP
WHERE DEPTNO = 10;

3. 부서 번호가 10인 사원을 대상으로 입사일에서 3개월이 지난 날짜

SELECT ENAME, ADD_MONTHS(HIREDATE, 3)
FROM EMP
WHERE DEPTNO = 10;

4.1. 오늘을 기준으로 최초로 다가오는 수요일이 언제인지 한글과 영어로 표현하시오

--방법1
SELECT NEXT_DAY(SYSDATE, '수요일'), NEXT_DAY(SYSDATE, 4)
FROM DUAL;

--방법2
ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
SELECT NEXT_DAY(SYSDATE, 'WEDNESDAY'), NEXT_DAY(SYSDATE, 4)
FROM DUAL;

4.2. 이번 달의 마지막 날짜를 반환

SELECT LAST_DAY(SYSDATE)
FROM DUAL;

5. 10번 부서의 사원들의 입사일을 달 기준으로 반올림

SELECT ENAME, HIREDATE, ROUND(HIREDATE, 'MONTH')
FROM EMP
WHERE DEPTNO = 10;

6. 10번 부서의 사원들의 입사일을 월 기준으로 잘라내기

SELECT ENAME, HIREDATE, TRUNC(HIREDATE, 'MONTH')
FROM EMP
WHERE DEPTNO = 10;

변환함수

1.1. 10번 부서 사원들의 입사일을 요일까지 함께 출력

SELECT TO_CHAR(HIREDATE, 'YYYY/MM/DD DAY')
FROM EMP
WHERE DEPTNO = 10;

1.2. 현재 날짜와 시간을 출력

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS')
FROM DUAL;

2.1. 10번 부서 사원들의 급여를 지역 통화 기호와 함께 999,9.9 형식으로 표현

SELECT ENAME, SAL, TO_CHAR(SAL, 'L999,9.9')
FROM EMP
WHERE DEPTNO = 10;

 

2.2. 1981년 2월 20일에 입사한 사원을 검색

SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE = TO_DATE('19810220', 'YYYY/MM/DD');

2.3. 올해 며칠이 지났는지 날짜 계산

SELECT TRUNC(SYSDATE-TO_DATE('2022/01/01', 'YYYY/MM/DD')) 
FROM DUAL;

2.4. 천단위 구분 쉼표가 포함된 10,000과 20,000을 더하시오

SELECT TO_NUMBER('10,000', '999,999') + TO_NUMBER('20,000', '999,999')
FROM DUAL;

 

 

일반함수

1. EMP테이블에 유일하게 상관이 없는 로우가 있는데 그 사원의 MGR 칼럼 값이 NULL이다. 상관이 없는 사원을 출력하되 MGR 칼럼 값을 NULL 대신 CEO로 출력

SELECT ENAME, NVL(TO_CHAR(MGR), 'CEO') AS MGR
FROM EMP;

2. 사원번호가 10이면 'ACCOUNTING', 사원번호가 20이면 'RESEARCH', 사원번호가 30이면 'SALES', 사원번호가 40이면 'OPERATIONS'부서에 속함

SELECT ENAME, DEPTNO, DECODE(DEPTNO, 10, 'ACCOUNTING',
20, 'RESEARCH',
30,  'SALES',
40, 'OPERATIONS') AS DNAME
FROM EMP;

3. 직급이 'ANALYST/인 사원은 5%, 'SALESMAN'인 사원은 10%, 'MANAGER'인 사원은 15%, 'CLERK'인 사원은 20% 급여 인상

SELECT ENAME, JOB, SAL,
CASE WHEN JOB='ANALYST' THEN SAL*1.05
WHEN JOB='SALESMAN' THEN SAL*1.1
WHEN JOB='MANAGER' THEN SAL*1.15
END NEWSAL
FROM EMP;

 

그룹함수

1.1 전체 사원 수와 커미션을 받는 사원 수

SELECT COUNT(*), COUNT(COMM)
FROM EMP;

1.2 직업의 종류가 몇 개인지 즉, 중복되지 않은 직업의 개수를 카운트

SELECT COUNT(DISTINCT JOB)
FROM EMP;

2. 부서별로 사원의 수와 커미션을 받는 사원의 수를 카운트

SELECT DEPTNO, COUNT(ENAME), COUNT(COMM)
FROM EMP
GROUP BY DEPTNO;

3. 부서별 최대급여와 최소급여를 출력하되 최대 급여는 2900을 초과

SELECT DEPTNO, MAX(SAL), MIN(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MAX(SAL)>2900;