ORACLE SQL 문자함수 SCOTT 문제 풀이

1.1. 문자값 'DataBase'를 소문자로 변환

SELECT 'DataBase', LOWER('DataBase')
FROM DUAL;

1.2. 문자값 'DataBase'를 대문자로 변환

SELECT 'DataBase', UPPER('DataBase')
FROM DUAL;

1.3. 'Smith'란 이름을 갖는 사원의 사번과 이름과 급여와 커미션을 출력

SELECT EMPNO, INITCAP(ENAME), SAL, COMM
FROM EMP
WHERE ENAME='SMITH';

1.4. 문자값 'Data'와 'Base'를 연결

SELECT CONCAT('Data', 'Base')
FROM DUAL;

1.5. 이름이 6글자 이상인 사원의 사번과 이름과 급여를 출력

SELECT EMPNO, ENAME
FROM EMP
WHERE LENGTH(ENAME) = 6;

1.6.1. 81년도에 입사한 사원 알아내기

--방법1
SELECT ENAME, HIREDATE
FROM EMP
WHERE SUBSTR(HIREDATE, 1, 2)='81';

--방법2
SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE>='81/01/01';

--방법3
SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE BETWEEN '81/01/01' AND '81/12/31';

--방법4
SELECT ENAME, HIREDATE
FROM EMP
WHERE SUBSTR(HIREDATE, 1, 1)='8' AND SUBSTR(HIREDATE, 2, 1)='1';

1.6.2. 이름이 K로 끝나는 직원 검색

--방법1
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '%K';

--방법2
SELECT ENAME
FROM EMP
WHERE SUBSTR(ENAME, -1, 1)='K';

1.7.1. 이름의 세 번째 자리가 R로 끝나는 직원을 검색

SELECT ENAME
FROM EMP
WHERE ENAME LIKE '__R%';

1.7.2. 이름의 두 번째 자리에 A가 있는 사원의 사번, 이름, 직급을 출력

--방법1
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE ENAME LIKE '_A%';

--방법2
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE SUBSTR(ENAME, 2, 1)='A';

--방법3
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE INSTR(ENAME, 'A')=2;

--방법4
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE INSTR(ENAME, 'A', 1, 1)=2;

1.8.1. 문자값 'DataBase'를 왼쪽에 나타내고 남은 오른쪽 자리를 $로 채운 20자리 문자를 만드시오

SELECT RPAD('DataBase', 20, '$')
FROM DUAL;

1.8.2. 문자값 'DataBase'를 오른쪽에 나타내고 남은 왼쪽 자리를 $로 채운 20자리 문자를 만드시오

SELECT LPAD('DataBase', 20, '$')
FROM DUAL;

1.9. SMITH란 사람의 이름에서 S와 H를 각각 잘라내기

SELECT TRIM('S' FROM ENAME), TRIM('H' FROM ENAME)
FROM EMP
WHERE ENAME='SMITH';