[Oracle DB] 문제 모음

16. 대소문자 변환 함수 (중요)

- 사원 테이블의 이름을 출력하는데 첫번째 이름은 대문자, 두번째 이름은 소문자, 세번째 이름은 첫글자만 대문자로 출력

SELECT upper(ename), lower(ename), initcap(ename)
FROM emp;

-> 데이터베이스의 저장된 이름의 철자가 대, 소문자를 확인하기 힘들기 때문에 하나로 통일해서 검색할 때 용이함.

SELECT ename, sal
FROM emp
WHERE lower(ename)= 'scott';

 

17. 문자열에서 특정 철자 추출하기 (아주 중요)

substr('이거를', 어디부터, 얼만큼)

 

- SMITH 에서 SM까지만 출력하시오.

SELECT substr('SMITH', 1, 2)
FROM dual;

 

- 012345678 에서 23만 출력하시오.

SELECT substr('012345678', 3, 2)
FROM dual;

 

- emp 테이블에서 직업의 앞 두글자만 중복 없이 출력하시오.

SELECT distinct substr(job, 2, 1)
FROM emp;

18. 문자열의 길이를 출력하기 (중요)

- 가나다라마의 문자열 길이를 출력하시오.

SELECT LENGTH('가나다라마')
FROM dual;

- 문자열의 바이트의 길이를 출력하시오.

SELECT LENGTHB('가나다라마')
FROM dual;

19. 문자에서 특정 철자의 위치 출력하기

- SMITH에서 알파벳 M이 몇 번째 자리에 있는지 출력하시오.

SELECT INSTR('SMITH','M')
FROM dual;

 

- test@sungil-i.kr 이메일에서 도메인(sungil-i.kr)만 추출하시오.

SELECT SUBSTR(tt@sungil-i.kr',INSTR('tt@sungil-i.kr','@')+1)
FROM dual;

 

- test@sungil-i.kr 에서 @ 다음 글자의 순서를 출력하시오.

SELECT INSTR(test@sungil-i.kr','@')+1
FROM dual;

 

20. 특정 철자를 다른 철자로 변경하기
- 이름과 월급을 출력하는데, 월급을 출력할 때 숫자0을 *로 출력하시오.

SELECT ename, REPLACE(sal,0,'*')
FROM emp;

- 사원의 이름 중 두번째 자리를 *로 출력하시오.

SELECT ename, REPLACE(ename,SUBSTR(ename,2,1),'*')
FROM emp;

21. 특정 철자를 N개 만큼 채우기 (중요)
- 이름과 월급을 출력하는데 월급의 자릿수를 10자리로 하고, 월급을 출력하고 남은 나머지 자리에 별표를 채우시오.

SELECT ename, LPAD(sal,10,'*') sal1, RPAD(sal,10,'*') sal2
FROM emp;

- 월급 100당 0 하나로 표현하여 사원 테이블의 월급을 시각화 하여 출력하시오.

SELECT ename, sal, LPAD('0',ROUND(sal/100), '0')
FROM emp;

22. 특정 철자 잘라내기 (TRIM, LTRIM, RTRIM)
-'SMITH'에서 첫번째는 그대로 출력, SMITH에서 S를 잘라서 출력, SMITH에서 H를 잘라서 출력, SMITHS에서 양쪽 S를 잘라서 출력

SELECT 'SMITH', LTRIM('SMITH','S'), RTRIM('SMITH','H'), TRIM('S' from 'SMITHS')
FROM dual;

 

+ 다음 문제를 풀기 위해 JACK의 값을 넣음
insert into emp(empno, ename, sal, job, deptno)
values (8291, 'JACK ', 3000, 'SALESMAN', 30);

- 사원의 이름이 JACK 인 사람의 이름, 월급을 출력하시오. 단, 오른쪽 공백을 제거하고 대문자로 찾아야 함.

오답

SELECT ename, sal
FROM emp
WHERE ename='JACK'; 

답

SELECT ename, sal
FROM emp
WHERE RTRIM(UPPER(ename))='JACK'; 

23. 반올림해서 출력하기
- 876.567 숫자를 출력하는데 소수점 두 번째 자리인 6에서 반올림해서 출력하시오.

SELECT '876.567' as 원본숫자, ROUND(876.567, 1)
FROM dual;

24. 숫자를 버리고 출력하기
- 876.567 숫자를 출력하는데 소수점 두번째 자리인 6과 그 이후의 숫자를 모두 버리고 출력하시오.

select '876.567' as 숫자, trunc(876.567,1) 
from dual;

25. 나눈 나머지 값 출력하기
- 숫자 10을 3으로 나눈 나머지 값을 출력하시오.

select MOD(10, 3)
from dual;

- 사원번호가 홀수이면 1, 짝수이면 0을 출력하시오.

select empno, mod(empno, 2)
from emp;

- 사원번호가 짝수인 사람들의 사원 번호와 이름을 출력하시오.

select ename, empno
from emp
where mod(empno,2) = 0;

26. 날짜 간 개월 수 출력하기
- 사원들의 이름을 출력하고, 입사한 날짜부터 오늘까지 총 몇 달을 근무했는지 출력하시오.

select ename, MONTHS_BETWEEN(sysdate, hiredate)
from emp;

- 2018년 10월 1일에서 2019년 6월 1일 사이의 총 일수를 출력하시오.

select TO_DATE('2019/06/01', 'RRRR/MM/DD') - TO_DATE('2018/10/01', 'RRRR/MM/DD')
from dual;

- 2018년 10월 1일에서 2019년 6월 1일 사이의 총 주수를 출력하시오.

select ROUND((TO_DATE('2019/06/01', 'RRRR/MM/DD') - TO_DATE('2018/10/01', 'RRRR/MM/DD')) / 7)
from dual;

27. 개월 수 미만 날짜 출력하기
- 2019년 5월 1일부터 100일 위의 날짜는 어떻게 되는지 출력하시오.

SELECT ADD_MONTHS(TO_DATE('2019-05-10', 'RRRR-MM-DD'), 100)
FROM dual;

- 2019년 5월 1일부터 100일 위의 날짜는 어떻게 되는지 출력하시오.

SELECT TO_DATE('2019-05-10', 'RRRR-MM-DD') + 100
FROM dual;

다른 범용적인 방법

SELECT TO_DATE('2019-05-10', 'RRRR-MM-DD') + interval '100' month
FROM dual; 

- 2019년 5월 1일부터 1년 3개월 후의 날짜를 출력하시오

SELECT TO_DATE('2019-05-10', 'RRRR-MM-DD') + interval '1-3' year to month
FROM dual;

28. 특정 날짜 뒤에 오는 요일 날짜 출력하기
- 2021년 4월 2일로부터 바로 돌아오는 월요일의 날짜를 출력하시오.

SELECT '2021/04/02', NEXT_DAY('2021/04/02', '월요일')
FROM dual;

- 오늘 날짜를 출력하시오.

SELECT SYSDATE as "오늘 날짜"
FROM dual;

- 오늘 날짜로부터 가장 먼저 오는 화요일의 날짜

SELECT SYSDATE as "오늘 날짜", NEXT_DAY(SYSDATE, '화요일') as "돌아오는 화요일"
FROM dual;

- 오늘부터 100달이 지난 뒤 돌아오는 월요일의 날짜를 구하시오

SELECT NEXT_DAY(ADD_MONTHS(SYSDATE, 100), '월요일')
FROM dual;

29. 특정 날짜가 있는 달의 마지막 날짜 출력하기
- 2021년 4월 2일 해당 달의 마지막 날짜를 구하시오.

SELECT '21-04-02' as "오늘 날짜", LAST_DAY('2021-04-02') as "마지막 날짜"
FROM dual;

- 오늘이 포함된 달의 마지막 날짜를 구하시오.

SELECT SYSDATE, LAST_DAY(SYSDATE)
FROM dual;

- 이름이 KING인 사원의 이름, 입사일, 입사한 달의 마지막 날짜를 출력하시오

SELECT ename, hiredate, LAST_DAY(hiredate)
FROM emp
WHERE ename='KING';

- 오늘부터 이번 달 말까지 남은 일수 구하기

SELECT LAST_DAY(SYSDATE) - SYSDATE as "남은 일수"
FROM dual;

 

30. (엄청 중요함) 문자형으로 데이터 유형 변환하기
- 이름이 SCOTT인 사원의 이름과, 입사한 요일을 출력하고 SCOTT의 월급을 천단위 구분하여 출력하시오.

SELECT ename as "이름", TO_CHAR(hiredate, 'DD') as "입사한 요일", TO_CHAR(sal, '999,999') as 월급
FROM emp
WHERE ename='SCOTT';

31. 날짜형으로 데이터 유형 변환하기
- 21년 3월 12일에 입사한 사원의 이름과 입사일을 출력하시오

SELECT ename, hiredate
FROM emp
WHERE TO_DATE(hiredate, 'RR/MM/DD') = TO_DATE('21/03/12', 'RR/MM/DD');

-날짜 포맷 확인 및 변경 (절대 외우지 말고 필요할 때 구글링 해서 쓰면 됨)

SELECT *
FROM SYS.nls_session_parameters
WHERE parameter = 'NLS_DATE_FORMAT';

ALTER SESSION SET NLS_DATE_FORMAT = 'RR/MM/DD'; 

32. 암시적 형 변환 이해하기

SELECT ename, sal
FROM emp
WHERE  sal = '3000';

33. null 값 대신 다른 데이터 출력
- 이름과 커미션을 출력하는데, 커미션이 null 인 사원들은 0으로 출력하시오

오답

SELECT ename, comm
from emp;

답

SELECT ename, NVL(comm, 0)
FROM emp;

- SALESMAN, ANALYST 인 사람의 이름, 월급, 커미션, 월급+커미션을 구하시오.

SELECT ename, sal, comm, sal+NVL(comm, 0)
FROM emp
WHERE job in('SALESMAN', 'ANALYST');

* NULL 값은 알 수 없는 값이라 연산이 불가하니 숫자값으로 변경후에 연산해야 함. 

- 커미션이 NULL이 아닌 사원들은 SAL+COMM을 출력하고, NULL인 사원들은 그냥 SAL만 출력하시오

SELECT ename, sal, comm, NVL2(comm, sal+comm, sal)
FROM emp;

34. if문을 sql로 구현하기 (1) - DECODE (엄청중요)
- 이름과 부서번호와 보너스를 출력하시오. 단, 보너스는 부서번호가 10이면 300을 주고, 20은 400, 나머지는 0으로 출력하시오

SELECT ename, deptno, DECODE(deptno, 10, 300, 20, 400, 0) as "보너스"
from emp;

* DECODE(속성, 조건1, 값1, 조건2, 값2, ... , 조건N, 조건N, 디폴트값) 

- 사원 번호와, 사원번호가 홀수인지 짝수인지 출력하시오.

SELECT empno, DECODE(mod(empno, 2), 0, '짝수', 1, '홀수')
from emp;

* 디폴트값은 생략이 가능하다.

- 이름, 직업, 보너스를 출력하시오. 단, 보너스는 직업이 SALESMAN은 5000, 나머진 2000으로 처리하시오

SELECT ename, job, DECODE(job, 'SALESMAN', 5000, 2000) as "보너스"
FROM emp;

* else if 생략 가능

35. if문을 sql로 구현하기 (2) (더중요)
- 이름, 직업, 보너스를 출력하시오. 단, 보너스는 월급이 3000이상이면 500, 2000이상이면 300, 1000 이상이면 200, 나머지는 0

SELECT ename, job, sal, 
CASE WHEN sal >= 3000 THEN 500
     WHEN sal >= 2000 THEN 300
     WHEN sal >= 1000 THEN 200
     ELSE 0 END as "보너스"
FROM emp;

(중요) DECODE와 차이는 DECODE는 등호비교만 가능하지만, CASE는 등호비교와 부등호 비교 둘 다 가능함. 

- 이름, 직업, 커미션, 보너스를 출력하시오, 단, 보너스는 커미션이 NULL이면 500을 출력, NULL이 아니면 0을 출력하시오.

SELECT ename, job, comm,
CASE WHEN comm is null THEN 500
     ELSE 0 END as "보너스"
FROM emp;

DECODE로 출력할때 🔽

SELECT ename, job, comm, DECODE(comm, null, 500, 0) FROM emp; 

- 이름, 직업, 보너스를 출력하시오. 단, 보너스는 직업이 SALESMAN, ANALYST는 500, CLERK/MANAGER는 400, 나머지는 0

SELECT ename, job,
CASE WHEN job in('SALESMAN', 'ANALYST') THEN 500
     WHEN job in('CLERK', 'MANAGER') THEN 400
     ELSE 0 END as "보너스"
FROM emp;

36. 최대값 출력하기
- 사원테이블에서 최대 월급을 출력하시오

SELECT MAX(sal)
FROM emp;

- 직업이 SALSESMAN인 사원들 중 최대 월급을 출력하시오

SELECT MAX(sal)
FROM emp
WHERE job = 'SALESMAN';

- 위의 작업을 확인하기 위해서 JOB 도 같이 출력

오답

SELECT job, MAX(sal)
FROM emp
WHERE job = 'SALESMAN';

이유는 job컬럼의 값은 여러 개의 행이 출력되려고 하는데, MAX(sal)는 값이 하나만 출력되려고 하기 때문임
group by 절을 통해 해결, 이는 데이터를 grouping함. (중요)

답

SELECT job, MAX(sal)
FROM emp
WHERE job = 'SALESMAN'
GROUP BY job;

37. 최소값 출력하기
- 직업이 SALESMAN인 사원들 중 최소 월급을 출력하시오

SELECT MIN(sal)
FROM emp
WHERE JOB = 'SLESMAN';

- 직업과 직업별 최소 월급을 출력하는데 최소월급이 높은 것부터 출력하시오

SELECT job, MIN(sal)
FROM emp
GROUP BY job
ORDER BY MIN(sal) DESC;

SELECT MIN(sal) FROM emp WHERE 1=2;
그룹 함수의 특징은 where 절의 조건이 거짓이어도 결과를 항상 출력하고, NULL 값으로 리턴함

38. 평균 값 구하기
- 사원 테이블에 평균 월급을 출력

SELECT AVG(sal)
FROM emp;

- 사원 테이블의 평균 커미션을 출력

SELECT AVG(comm) FROM emp;

- 사원 테이블의 평균 커미션을 출력하시오. 단, 널은 0으로 생각하고 계산하시오. 단, 일의 자리까지만 출력하시오

SELECT ROUND(AVG(NVL(comm, 0)))
FROM emp;

39. 토탈값 출력하기
- 부서번호와 부서번호별 토탈 월급을 출력하시오.

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;

- 직업과 직업별 토탈 월급을 출력하는데, 직업별 토탈 월급이 4000 이상인 것만 출력

SELECT job, SUM(sal)
FROM emp
GROUP BY job
HAVING sum(sal) >= 6000;

40. 건수 출력하기
- 사원 테이블의 전체 사원 수를 출력하시오

SELECT COUNT(empno)
FROM emp;

41. 데이터 분석 함수로 순위 출력하기
- 직업이 ANALYST, MANAGER인 사원들의 이름, 직업, 월급의 순위를 출력하시오

SELECT ename, job, sal, RANK() over (ORDER BY sal DESC) as 순위
FROM emp
WHERE job in('ANALYST', 'MANAGER');

- 직업별로 월급이 높은 순서대로 순위를 부여해서 각각 출력하시오.

SELECT ename, job, sal, RANK() OVER (PARTITION BY job ORDER BY sal DESC) as 순위
FROM emp;