오라클 sql Practice2 [LOWER, INITCAP, LENGTH, SUBSTR...]

SQL 처음 공부했을 때 썼던 코드 복습하는 느낌으로 기록합니다.

연습 Table은 오라클에서 처음 제공하는 emp, dept 를 이용하였습니다.

SELECT * FROM EMP;

SELECT EMPNO,ENAME FROM EMP;

SELECT EMPNO,ENAME FROM EMP WHERE EMPNO = 7844;

SELECT ENAME AS "사원이름", UPPER(ENAME) AS UPPER, LOWER(ENAME) AS "lower", INITCAP(ENAME) AS "INITCAP" FROM EMP;

SELECT * FROM EMP WHERE UPPER(ENAME) = UPPER('ford');

SELECT * FROM EMP WHERE UPPER(ENAME) LIKE UPPER('%f%');

SELECT ENAME,LENGTH(ENAME) FROM EMP;

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

SELECT LENGTH('한글'),LENGTHB('한글') FROM DUAL;

SELECT JOB,SUBSTR(JOB,1,2),SUBSTR(JOB,3,2),SUBSTR(JOB,5) FROM EMP;

SELECT ENAME,SUBSTR(ENAME,1,2),SUBSTR(ENAME,3) FROM EMP;

SELECT INSTR('HELLO, ORACLE!','L') AS INSTR_1,INSTR('HELLO, ORACLE!','L',5) AS INSTR_2,INSTR('HELLO, ORACLE','L',2,2) AS INSTR_3 FROM DUAL;

SELECT * FROM EMP WHERE INSTR(ENAME,'S') > 0;

SELECT SUBSTR(ENAME,1,2) FROM EMP;

SELECT ENAME,SUBSTR(ENAME,LENGTH(ENAME)) FROM EMP;

SELECT JOB,SUBSTR(JOB,-LENGTH(JOB)),SUBSTR(JOB,-LENGTH(JOB),2),SUBSTR(JOB,-3) FROM EMP;

SELECT '010-1234-5678' AS REPLACE_BEFORE, REPLACE('010-1234-5678','-',' ') AS REPLACE_1,REPLACE('010-1234-5678','-') AS REPLACE_2 FROM DUAL;

SELECT 'ORACLE',LPAD('ORACLE',10,'#') AS LPAD_1,RPAD('ORACLE',10,'*') AS RPAD_1,LPAD('ORACLE',10) AS LPAD_2,RPAD('ORACLE',10) AS RPAD_2 FROM DUAL;

SELECT RPAD(SUBSTR(ENAME,1,2),LENGTH(ENAME),'#')FROM EMP;

SELECT CONCAT(EMPNO,ENAME),CONCAT(EMPNO,CONCAT(':',ENAME)) FROM EMP;

SELECT ENAME,LENGTH(ENAME) FROM EMP WHERE DEPTNO ='20';
SELECT EMPNO,ENAME FROM EMP WHERE EMPNO IN (7654,7900);
SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE '%R';
SELECT EMPNO,ENAME FROM EMP WHERE SUBSTR(ENAME,LENGTH(ENAME))='R';
SELECT EMPNO,ENAME FROM EMP WHERE SUBSTR(ENAME,3,1)='A';
SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE '__A%';
SELECT INITCAP(ENAME) FROM EMP WHERE ENAME LIKE 'A%' OR ENAME LIKE 'M%';
SELECT INITCAP(ENAME) FROM EMP WHERE SUBSTR(ENAME,1,1) IN ('A','M');
SELECT LOWER(ENAME) FROM EMP WHERE LENGTH(ENAME) >=6;
SELECT LOWER(RPAD(ENAME,3)) FROM EMP WHERE LENGTH(ENAME) >= 6;

SELECT TRIM('      _ _ORACLE_ _      ') AS TRIM, LTRIM('      _ _ORACLE_ _      ') AS LTRIM, RTRIM('      _ _ORACLE_ _      ') AS RTRIM FROM DUAL;
SELECT TRIM('_' FROM '_ _ORACLE_ _') AS TRIM FROM DUAL;
SELECT TRIM(LEADING '_' FROM '_ _ORACLE_ _') AS TRIM_LEADING FROM DUAL;
SELECT TRIM(TRAILING '_' FROM '_ _ORACLE_ _') AS TRIM_TRAILING FROM DUAL;
SELECT TRIM(BOTH '_' FROM '_ _ORACLE_ _') AS TRIM_BOTH FROM DUAL;

/* *SUBSTR()
  1. 문자 추출하는 함수
  2. SUBSTR(A,B,C)
     A. 추출 대상 : 컬럼명, 문자열 -> '문자(들)'
     B. 시작 위치 ; 추출할 문자의 시작 위치
     C. 추출길이 ; 시작 위치를 포함한 길이의 값
     EX) SUBSTR(ENAME,1,2)*/
     
     SELECT ROUND(1234.5678) AS PROND,
     ROUND(1234.5678,0) AS ROUND,
     ROUND(1234.5678,1) AS ROUND,
     ROUND(1234.5678,2) AS ROUND,
     ROUND(1234.5678,-1) AS ROUND,
     ROUND(1234.5678,-2) AS ROUND FROM DUAL;
     
     SELECT TRUNC(1234.5678) AS TRUNC,
     TRUNC(1234.5678,0) AS TRUNC,
     TRUNC(1234.5678,1) AS TRUNC,
     TRUNC(1234.5678,2) AS TRUNC,
     TRUNC(1234.5678,-1) AS TRUNC,
     TRUNC(1234.5678,-2) AS TRUNC FROM DUAL;
     
     SELECT CEIL(3.14),FLOOR(3.14),CEIL(-3.14),FLOOR(-3.14) FROM DUAL;
     
     SELECT MOD(15,6),MOD(10,2),MOD(11,2) FROM DUAL;
     
     SELECT SYSDATE,ADD_MONTHS(SYSDATE,3) FROM DUAL;
     
     SELECT EMPNO,ENAME,HIREDATE, ADD_MONTHS(HIREDATE,120) AS WORK10YEAR FROM EMP;
     
     SELECT EMPNO,ENAME,HIREDATE,SYSDATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE),3) AS MONTHS1 FROM EMP;
     
     SELECT SYSDATE,NEXT_DAY(SYSDATE,'월요일') FROM DUAL;
     
     SELECT ENAME FROM EMP WHERE HIREDATE BETWEEN '80/12/17' AND '81/04/02';
     
     SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL BETWEEN 2000 AND 5000 AND DEPTNO IN(10,30);
     
     SELECT EMPNO,ENAME,EMPNO+'500' FROM EMP WHERE ENAME = 'SMITH';SQL