3day

-- 3일차 hr 계정

SELECT * FROM TAB; -- 해당 스키마에서 접근할 수 있는 테이블 리스트

DESC EMPLOYEES;

SELECT * FROM EMPLOYEES WHERE LOWER(LAST_NAME) LIKE '%king%';

SELECT regexp_replace(
REGEXP_REPLACE(PHONE_NUMBER,
  '(\d{3})\.(\d{2,3})\.([0-9]{4})', '(\1) \2-\3'), '\.', '-'), phone_number
FROM EMPLOYEES;

 

SELECT FIRST_NAME, JOB_ID,
REGEXP_SUBSTR(JOB_ID,'(.+)(_)([A-Z]+)',1, 1,'i', 3)
FROM EMPLOYEES;

 

SELECT FIRST_NAME, JOB_ID, REGEXP_SUBSTR(JOB_ID, '[a-zA-Z]+$')
from employees;

 

SELECT REPLACE(PHONE_NUMBER, '.', '-') FROM EMPLOYEES;

-- instr(문자열, 찾을문자열, 시작점, 몇번째)
SELECT INSTR('배가 고파요', '고') FROM DUAL;

SELECT INSTR('미스터빈스스스스터스미', '스', -1, 2) FROM DUAL;

-- substr(문자열, 시작점, 길이)
SELECT SUBSTR('미스터빈.avi', 4) FROM DUAL;

select substr('미스터빈.avi', 4, 2) from dual;

 

drop table filetbl;

 

CREATE TABLE FILETBL
(NO NUMBER
,NAME VARCHAR2(4000)
,IDATE DATE);

-- delete filetbl where no = 1;
INSERT INTO FILETBL VALUES(1, 'c:/incoming/도영.avi', SYSDATE);
INSERT INTO FILETBL VALUES(2, 'c:/incoming/수학/미분적분.mpg', SYSDATE);
INSERT INTO FILETBL VALUES(3, 'c:/incoming/숨김/아오 이소라.mkv', SYSDATE);
INSERT INTO FILETBL VALUES(4, 'c:/incoming/숨김/아오 이소라.m', SYSDATE);
COMMIT; -- 커밋은 데이터를 실제로 입력하는 명령
-- 폴더, 파일명, 확장자를 출력하세요
-- c:/incoming   도영.avi   avi

SELECT * FROM FILETBL;

 

-- by 나현
SELECT REGEXP_SUBSTR(NAME, '^((.+[/\\])*.+)[/\\]' ,1 ,1, 'i', 1) 폴더명
, REGEXP_SUBSTR(NAME, '([^/\.]+\.[a-zA-Z]+)$') 파일명
, REGEXP_SUBSTR(NAME, '[a-zA-Z가-힝0-9_$]+$') 확장자명
from filetbl;

 

SELECT REGEXP_REPLACE(NAME, '[/\\][^/\\]+$') 폴더,
    SUBSTR(NAME, 1, INSTR(NAME, '/', -1)-1) 폴더,
    REGEXP_REPLACE(NAME, '.+[/\\]') 파일명,
    SUBSTR(NAME, INSTR(NAME, '/', -1)+1) 파일명2,
    REGEXP_REPLACE(NAME, '.+\.') 확장자,
    substr(name, instr(name, '.', -1)+1) 확장자
FROM FILETBL;

-- length(문자열)
SELECT LENGTH('미스터빈'), LENGTHB('미스터빈') FROM DUAL;

-- 날짜 함수
SELECT ADD_MONTHS(SYSDATE, -12) "달을 뺀 결과",
        SYSDATE-365 "날을 뺀 결과" FROM DUAL;

 

SELECT EXTRACT(YEAR FROM SYSDATE), EXTRACT(MONTH FROM SYSDATE),
    EXTRACT(DAY FROM SYSDATE), EXTRACT(HOUR FROM SYStimestamp) FROM DUAL;

SELECT SYSDATE FROM DUAL;

-- last_day, months_between(날짜, 날짜)
SELECT LAST_DAY(SYSDATE), SYSDATE+30, MONTHS_BETWEEN(SYSDATE, SYSDATE+30) FROM DUAL;

-- next_day
SELECT NEXT_DAY(SYSDATE, '월') FROM DUAL;

-- to_char(날짜), 숫자도 가능
-- 날짜포맷 : http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements004.htm#CDEHIFJA
SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd day am hh:mi:ss') FROM DUAL;

-- 문제 : '12년 07월 05일 15시 51분' 식으로 출력
SELECT TO_CHAR(SYSDATE, 'fmyy"년" mm"월"fm dd"일" hh24"시" mi"분"') FROM DUAL;

-- to_date(문자열, 포맷)
select to_char(to_date('85/07/12', 'rr/mm/dd'), 'yyyy') from dual;

-- 숫자를 문자로
select to_char(123456, 'L099,999,999') from dual;

-- 문자를 숫자로
SELECT TO_NUMBER('001,234,567', '099,999,999') FROM DUAL;

-- trunc(날짜)
SELECT TO_CHAR(TRUNC(SYSDATE), 'hh24:mi:ss') FROM DUAL;

SELECT TRUNC(SYSDATE, 'month') FROM DUAL;

-- 숙제 : '850916' 주민번호 앞자리로 나이 출력
select '3' + '4' from dual;

 

SELECT extract(year from sysdate) year,
TO_CHAR(SYSDATE,'yyyy')+1-TO_CHAR(TO_DATE('850916', 'rrmmdd'),'yyyy') 나이
from dual;

 

-- null 처리 함수
SELECT COALESCE(NULL, NULL, NULL, NULL, 3, 4) FROM DUAL;
SELECT COALESCE(COMMISSION_PCT, 0) FROM EMPLOYEES;
SELECT NVL(COMMISSION_PCT, 0) FROM EMPLOYEES;
SELECT JOB_ID,
  NVL2(COMMISSION_PCT, '영업사원', '영업사원아님') FROM EMPLOYEES;
SELECT NULLIF(3, 3), NULLIF(3, 4) FROM DUAL;

-- 숙제2 : 월급에 커미션퍼센트를 곱한 것이 이번 월급이다.
-- 이름(First_name Last_name 합쳐서), 월급, 근무년수를 출력하세요
select * from employees;

 

select first_name || ' ' || last_name 이름, salary, salary + salary * nvl(commission_pct,0) 월급,
trunc(months_between(sysdate,hire_date)/12) 근무년수 from employees;