0804_Oracle : SQL

-- LIKE 보다는 INSTR() 함수가 속도가 더 빠르다.
-- FROM emp 를 가장 먼저 해석 (2)
-- WHERE name LIKE '김%';이 두 번째. (3)
-- SELECT empNo, name 이 마지막. 하지만 쓸 때는 (1)

SELECT empNo, name
FROM emp
WHERE name LIKE '김%';

SELECT empNo, name, tel
FROM emp
WHERE tel LIKE '%3%';  -- 3이 존재하는 전화번호

SELECT empNo, name, tel
FROM emp
WHERE tel LIKE '%3';  -- 3으로 끝나는 전화번호

SELECT empNo, name, rrn
FROM emp
WHERE rrn LIKE '_0%';  -- 태어난 년도가 10의 배수인 사람들만 출력

SELECT name, rrn, city
FROM emp
WHERE rrn LIKE '__03%' AND city = '서울';

SELECT name, rrn, city
FROM emp
WHERE rrn LIKE '__03%' AND (city IN ('서울', '경기') );

SELECT name, tel
FROM emp
WHERE tel LIKE '%3%' OR tel LIKE '%5%';
-- %는 LIKE 에서만 유효하다.

SELECT name
From emp
WHERE name > '가' AND name < '나';
-- WHERE name BETWEEN '가' AND '나'; 보다 위가 더 성능이 좋다

-- ESCAPE '%', '_' 를 실제 검색하기 위해 사용(패턴이 아닌 문자로 처리)

SELECT name, sal, bonus, sal+bonus pay From emp;

SELECT name, sal, bonus, sal+bonus pay 
From emp 
WHERE sal+bonus >= 3000000;

-- 가상의 이름을 줄 수 있다.
WITH emp_pay AS( 
    SELECT name, sal, bonus, sal+bonus pay From emp
)
SELECT name, sal, bonus, pay FROM emp_pay WHERE pay >=3000000;

WITH tb AS (
SELECT '김김김' name, '우리_나라' content  FROM dual
UNION ALL
SELECT '나나나' name, '자바%스프링' content  FROM dual
UNION ALL
SELECT '다다다' name, '우리나라' content  FROM dual
UNION ALL
SELECT '라라라' name, '안드로이드%모바일' content  FROM dual
 ) 
 SELECT * FROM  tb;
 
WITH tb AS (
    SELECT '김김김' name, '우리_나라' content  FROM dual -- dual은 가상으로 만든 것
    UNION ALL
    SELECT '나나나' name, '자바%스프링' content  FROM dual
    UNION ALL
    SELECT '다다다' name, '우리나라' content  FROM dual
    UNION ALL
    SELECT '라라라' name, '안드로이드%모바일' content  FROM dual
 ) 
 SELECT * FROM  tb WHERE content LIKE'%바#%%' ESCAPE '#';
-- #뒤의 문자는 패턴이 아니라 진짜 문자이다.  '%' '_' 빼고는 어떤 문자로 주던 상관없음

-- 행의 열에 값이 없는 경우, 길이가 0인 문자 값도 NULL

SELECT 10+5 FROM dual;
SELECT 10+NULL FROM dual; -- null 이 나온다. 

SELECT name, tel
FROM emp;

SELECT name, tel
FROM emp
WHERE tel=NULL; -- NULL은 =으로 확인불가

SELECT name, tel
FROM emp
WHERE tel IS NULL;  -- IS는 NULL인지를 확인하는 유일한 방법

SELECT name, tel
FROM emp
WHERE tel IS NOT NULL; 

SELECT MOD(10, 6) FROM dual; -- MOD 는 나머지 연산자
SELECT SUBSTR('korea', 2, 3) FROM dual; -- 2번째 위치부터 3개 추출(인덱스는 1부터 시작)
SELECT name, rrn, SUBSTR(rrn, 8, 1) FROM emp;

SELECT name, rrn,
    CASE SUBSTR(rrn, 8, 1) 
            WHEN '1' THEN '남자' -- WHEN 1은 오류 발생
            WHEN '2' THEN '여자'
    END "성별"
FROM emp; -- 성별이 1,2 가 아니면 NULL로 출력

SELECT name, rrn, -- 자바의 switch 와 비슷
    CASE MOD(SUBSTR(rrn, 8, 1),2) -- 숫자로 처리되어서
            WHEN 0 THEN '여자' -- 여기서 홑따옴표 사용안한 것
            WHEN 1 THEN '남자'
    END "성별" -- 컬럼명을 부여해준다.
FROM emp; 

SELECT name, sal, bonus, sal+bonus "총급여", -- 컬럼은 콤마를 써야 함.
    CASE 
        WHEN sal+bonus >= 2500000 THEN (sal+bonus) * 0.03   -- 이 조건을 만족하면 밑으로 내려오지 않음.
        WHEN sal+bonus >= 2000000 THEN (sal+bonus) * 0.02   
        ELSE 0 -- 이거 없으면 나머지 애들이 NULL 이 됨. NULL != 0
    END 세금
FROM emp;

WITH emp_pay AS ( -- emp_pay 라는 이름의 테이블로 SELECT 에서 추출한 것을 만듦
    SELECT name, sal, bonus, sal+bonus pay 
    FROM emp
)
SELECT name, sal, bonus, pay, -- 컬럼은 콤마를 써야 함.
    CASE 
        WHEN pay >= 2500000 THEN pay * 0.03   -- 이 조건을 만족하면 밑으로 내려오지 않음.
        WHEN pay >= 2000000 THEN pay * 0.02   
        ELSE 0 -- 이거 없으면 나머지 애들이 NULL 이 됨. NULL != 0
    END 세금
FROM emp_pay;   

WITH emp_pay AS (
    SELECT name, sal, bonus, sal+bonus pay,
        CASE
            WHEN sal+bonus >= 2500000 THEN (sal+bonus) * 0.03
            WHEN sal+bonus >= 2000000 THEN (sal+bonus) * 0.02
        ELSE 0
    END tax
FROM emp
)
SELECT name, sal, bonus, pay, tax, pay - tax net_pay
FROM emp_pay;

-- DECODE(a, 'b', 1, 2); a(컬럼 또는 수식)가 'b'이면 1, 그렇지 않으면 2

SELECT name, rrn,
    DECODE(SUBSTR(rrn, 8, 1), '1', '남자')
FROM emp; -- 성별이 1이면 남자 그렇지 않으면 null

SELECT name, rrn,
    DECODE(SUBSTR(rrn, 8, 1), '1', '남자', '여자')
FROM emp; -- 성별 2이면 남자 그렇지 않으면 '여자' : 성별 3도 여자가 됨.

SELECT name, rrn,
    DECODE(SUBSTR(rrn, 8, 1), '1', '남자', '2', '여자', '3', '남자', '4', '여자')
FROM emp; 
-- 성별 5, 6은 NULL
-- '4'가 빠지면 성별 5, 6은 '여자'

SELECT name, rrn,
    DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 0,'여자', '남자')
FROM emp; -- 성별 출력하는 가장 간단한 방법

SELECT name, dept FROM emp;
SELECT ALL name, dept FROM emp; -- 해당 열에 모든 항목이 다 나옴

-- DISTINCT은 선택 행 중에서 중복적인 행은 한번만 출력
-- DISTINCT은 UNIQUE와 동일
SELECT dept FROM emp; -- 60개 전체 중복적인 행도 출력
SELECT DISTINCT dept FROM emp; -- 중복적인 행은 한번만 출력
SELECT DISTINCT dept, pos FROM emp; -- dept+pos결과 중 중복적인 행은 한번만 출력
SELECT UNIQUE dept, pos FROM emp;

SELECT name, dept, sal FROM emp;
SELECT name, dept, sal FROM emp ORDER BY sal ASC; -- sal 오름차순 ASC 는 기본 값으로 생략 가능
SELECT name, dept, sal FROM emp ORDER BY sal DESC; -- 내림차순. 꼭 DESC를 써야함.

-- 부서(dept) 오름차순으로 출력하고 부서가 같으면 sal 내림차순
SELECT name, dept, sal FROM emp ORDER BY dept DESC, sal DESC;

-- 부서(dept) 오름차순으로 출력하고 부서가 같으면 sal 오름차순
SELECT name, dept, sal FROM emp ORDER BY dept DESC, sal ;

-- sal + bonus 오름차순 : ORDER BY 실행 순서가 가장 낮으므로 ORDER BY 절에 별명(pay) 사용 가능
SELECT name, dept, sal, bonus, sal+ bonus pay FROM emp ORDER BY pay;

-- sal + bonus 내림차순 : 숫자로 컬럼의 위치를 나타냄
SELECT name, dept, sal, bonus, sal+ bonus pay FROM emp ORDER BY 5 DESC;

-- 남자 중 출신도(city) 오름차순, 출신도가 같으면 sal 내림차순
SELECT name, rrn, city, dept, sal
FROM emp
WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 1
ORDER BY city, sal DESC;

-- dept 오름차순 정렬하고 dept가 같으면 남자를 먼저 출력, name, rrn, dept, sal
SELECT name, rrn, dept, sal
FROM emp
ORDER BY dept, MOD(SUBSTR(rrn, 8, 1), 2) DESC ; -- 1인 경우에(남자인 경우) 내림차순

SELECT name, dept
FROM emp
ORDER BY CASE WHEN dept = '영업부' THEN 0 END;
--오름차순 정렬에서  NULL인 데이터가 뒤에 출력

SELECT name, dept, CASE WHEN dept = '영업부' THEN 0 END
FROM emp;

SELECT name, dept, pos
FROM emp
ORDER BY 
    CASE 
        WHEN pos = '부장' THEN 0
        WHEN pos = '과장' THEN 1
        WHEN pos = '대리' THEN 2
        WHEN pos = '사원' THEN 3
    END;

SELECT name, dept, pos
FROM emp
ORDER BY DECODE(pos, '부장', 0, '과장', 1, '대리', 2, 3);

SELECT name, rrn, sal
FROM emp
ORDER BY MOD(SUBSTR(rrn, 8, 1), 2), sal DESC ;

SELECT name, city, sal+bonus pay
FROM emp
WHERE city = '서울'
ORDER BY pay DESC;

SELECT name, rrn, dept, sal
FROM emp
WHERE SUBSTR(rrn, 8, 1) in (2, 4, 6, 8)
ORDER BY dept, sal DESC;

SELECT name, tel FROM emp;

SELECT name, tel 
FROM emp
ORDER BY tel NULLS FIRST;

SELECT name, tel 
FROM emp
ORDER BY tel NULLS LAST;

SELECT name, dept
FROM emp
ORDER BY CASE WHEN dept = '영업부' THEN 0 END DESC;

SELECT name, dept
FROM emp
ORDER BY CASE WHEN dept = '영업부' THEN 0 END NULLS FIRST; -- 위랑 같은 방법

SELECT name, dept
FROM emp
ORDER BY DECODE(dept, '영업부', 1, 2) DESC;

-- 사원 60명 중에서 5명을 선택하여 이벤트 상품을 증정하려고 합니다. 임의적으로 5명만 뽑는 방법은 ?
SELECT DBMS_RANDOM.VALUE, name FROM emp;
-- DBMS_RANDOM.VALUE 임의적인 난수 발생
SELECT * FROM emp
ORDER BY DBMS_RANDOM.VALUE;

SELECT ROWNUM, tb.* FROM (
    SELECT * FROM emp
    ORDER BY DBMS_RANDOM.VALUE
) tb WHERE ROWNUM <= 5;
-- 서브 쿼리 SELECT 안에 SELECT가 오는 것 tb는 없어도 결과는 동일
-- 서브 쿼리의 결과에게 별명을 준 것

SELECT name, city, dept, sal FROM emp WHERE dept = '개발부'
    UNION
SELECT name, city, dept, sal FROM emp WHERE city = '인천';

-- 컬럼명은 동일하지 않아도 되지만 각 순서의 데이터 타입은 일치해야함.
SELECT name, city, dept, sal FROM emp WHERE dept = '개발부'
    UNION
SELECT name, dept, city, sal FROM emp WHERE city = '인천';

SELECT name, city, dept, sal FROM emp WHERE dept = '개발부'
    UNION ALL
SELECT name, city, dept, sal FROM emp WHERE city = '인천';

SELECT name, city, dept, sal FROM emp WHERE dept = '개발부'
    MINUS
SELECT name, city, dept, sal FROM emp WHERE city = '인천';

SELECT name, city, dept, sal FROM emp WHERE dept = '개발부'
    INTERSECT
SELECT name, city, dept, sal FROM emp WHERE city = '인천';

SELECT name, sal FROM emp;
SELECT ROWID, name, sal FROM emp; -- 각 행에 부여된 주소로 유일하다.

SELECT ROWNUM, name, sal FROM emp; 
-- 쿼리 결과로 나오는 각각의 행들에 대한 순서 값.
-- 실제 컬럼이 아닌 가상 컬럼.
SELECT ROWNUM, name, sal FROM emp WHERE ROWNUM <= 10;

-- 양수보다 큰 ROWNUM 값은 항상 거짓
SELECT ROWNUM, name, sal FROM emp WHERE ROWNUM > 1; -- 아무것도 출력되지 않음
-- ROWNUM 은 1부터 시작하는 쿼리의 순서 값의 집합임.

-- 1보다 큰 양수와 ROWNUM 을 =으로 비교하면 항상 거짓
SELECT ROWNUM, name, sal FROM emp WHERE ROWNUM = 10; -- 아무것도 출력되지 않음. 
--처음이 거짓이라 1부터 시작하는 것부터.. 따라서 그 다음도 출력되지 않음.

-- 1과 ROWNUM 을 =으로 비교하면 행이 존재하면 처음 행 출력
SELECT ROWNUM, name, sal FROM emp WHERE ROWNUM = 1;

SELECT name, sal 
FROM emp 
ORDER BY sal;
-- 둘의 결과가 다름
SELECT name, sal 
FROM emp 
WHERE ROWNUM <11 
ORDER BY sal;
-- ROWNUM을 사용하는 경우 ORDER BY 사용하지 말 것

-- sal오름차순 정렬해서 처음부터 10명만 출력 
-- 반드시 서브쿼리로 작성 (쿼리 안에 쿼리가 들어가는 것)
SELECT * FROM(
    SELECT name, sal FROM emp ORDER BY sal
) WHERE ROWNUM < 11;
-- SORT 전에 ROWNUM을 부여
SELECT ROWNUM, name, sal 
FROM emp 
ORDER BY sal;

-- sal 내림차순 정렬하여 6번째에서 10번째 행 출력
(SELECT * FROM (
SELECT name, dept, pos, sal 
FROM emp 
ORDER BY sal DESC) 
WHERE ROWNUM < 11 )

MINUS

(SELECT * FROM (
SELECT name, dept, pos, sal 
FROM emp 
ORDER BY sal DESC) 
WHERE ROWNUM < 6 )
-- 나올 순 있겠지만 성능이 매우 떨어짐

-- 오라클 11g 페이징 처리 방법 : 반드시 암기해야 함
SELECT * FROM (
    SELECT ROWNUM rnum, tb.* FROM ( -- ROWNUM의 연산결과에 별명을 줘서 새로운 열 추가
        SELECT name, dept, pos, sal
        FROM emp
        ORDER BY sal DESC
    ) tb WHERE ROWNUM <= 10 -- ROWNUM은 적거나 같다로만 비교가능
) WHERE rnum >= 6;

SELECT * FROM emp;
SELECT emp.* FROM emp;
SELECT ROWNUM, * FROM emp; -- 오류가 나옴.
SELECT ROWNUM, emp.* FROM emp;
SELECT ROWNUM, e.* FROM emp e;

-- 숫자 함수 ABS(n) : 절대 값
SELECT ABS(20), ABS(-20) FROM dual;
-- SIGN(n) : 양수 -> 1, 음수-> -1, 0 -> 0
SELECT SIGN(20), SIGN(-20), SIGN(0) FROM dual;
-- MOD(n2, n1) : 나머지 
-- 연산 방식 : n2 - n1 * FLOOR(n2/n1)
-- REMAINDER(n2, n1) : 나머지
-- 연산 방식 : n2 - n1 * ROUND(n2/n1)
SELECT MOD(13, 5) FROM dual; -- 3
SELECT 13 - 5 * FLOOR(13/5) FROM dual; -- 3 FLOOR는 소수점 절삭

SELECT REMAINDER(13, 5) FROM dual; 
SELECT 13 - 5 * ROUND(13/5) FROM dual; -- -2
-- REMAINDER 라는 게 있다 정도만 알고 그냥 나머지 연산자는 MOD만 기억할 것

-- CEIL(n) : n보다 크거나 같은 수 중 가장 적은 정수
SELECT CEIL(20.5), CEIL(-20.5) FROM dual;-- 21, -20

-- FLOOR(n) : n 이하의 가장 큰 정수
SELECT FLOOR(20.5), FLOOR(-20.5) FROM dual; -- 20, -21

-- ROUND(n [, integer ]) : 반올림 
SELECT ROUND(15.693, 1)FROM dual;  
-- 소수점 1자리 표시 (소수점 둘째자리에서 반올림)
SELECT ROUND(15.6933, 2)FROM dual; --15.69
SELECT ROUND(15.693)FROM dual; -- 16
-- 소수점 첫째자리에서 반올림
SELECT ROUND(15.693, 0)FROM dual; -- 16
-- 소수점 첫째자리에서 반올림
SELECT ROUND(15.693, -1)FROM dual; -- 20
-- 일의 자리 반올림

-- TRUNC(n1[, n2 ]) : 절삭
SELECT TRUNC(15.693, 1)FROM dual;  --15.6
-- 소수점 1자리 표시 (소수점 둘째자리에서 절삭)
SELECT TRUNC(15.6933, 2)FROM dual; --15.69
SELECT TRUNC(15.693)FROM dual; -- 15
-- 소수점 첫째자리에서 절삭
SELECT TRUNC(15.693, 0)FROM dual; -- 15
-- 소수점 첫째자리에서 절삭
SELECT TRUNC(15.693, -1)FROM dual; -- 10
-- 일의 자리에서 절삭

SELECT name, sal, 
    TRUNC ( sal / 50000) "5만원권", 
    TRUNC( MOD(sal ,  50000) / 10000) "1만원권", 
    MOD ( sal , 10000) "나머지금액"
FROM emp; -- " " 라벨. 

SELECT SIN(30 / 180 * 3.141592) FROM dual;

-- LOWER(char) : 모든 영문자를 소문자로 
SELECT LOWER('KOREA seoul 2021') FROM dual;

SELECT * FROM col WHERE tname = 'EMP';
SELECT * FROM col WHERE LOWER(tname) = LOWER('Emp');

-- UPPER(char) : 모든 영문자를 대문자
SELECT UPPER('KOREA seoul 2021') FROM dual;

-- INITCAP(char) : 각 영어 단어에서 첫글자는 대문자, 다음 문자는 모두 소문자로
SELECT INITCAP('KOREA seoul 2021') FROM dual;

-- CHR(n [ USING NCHAR_CS ]) : ASCII 코드 (n)에 해당하는 문자
SELECT CHR(65) || CHR(66) || CHR(67) FROM dual;

-- ASCII(char) : 첫 번째 문자의 ASCII 코드값 (10진수)
SELECT ASCII('ABC') FROM dual; -- 65 첫 번째 것만 나옴

-- ASCIISTR(char) : 영문자나 숫자는 그대로 출력하고, 한글 등 non-ASCII 문자는 UTF-16 코드(16진수로)
SELECT ASCIISTR('korea 123'), ASCIISTR('대한') FROM dual;

-- SUBSTR(char ,position [, substring_length] ) : 문자열중 지정 위치에서 개수만큼 문자열 추출
SELECT SUBSTR('seoul korea', 7, 3) FROM dual;
-- 7번째 위치부터 3개 추출
-- 오라클은 인덱스가 1부터 시작
SELECT SUBSTR('seoul korea', -5, 3) FROM dual;
-- 뒤에서 앞으로 5번째 위치에서 3개
SELECT SUBSTR('seoul korea', 7) FROM dual;
-- 7번째 위치부터 끝까지

-- 오라클에서 '30'처럼 숫자로 변환 가능한 문자열은 숫자로 자동 변환 가능하다.

-- emp 테이블에서 생년월일이 78~82년생만(rrn를 이용)
SELECT name, rrn
FROM emp
WHERE SUBSTR(rrn, 1, 2) >= 78 AND SUBSTR(rrn, 1, 2) <= 82;

-- emp 테이블에서 생년월일이 80~89년생만 rrn 오름차순
SELECT name, city, rrn
FROM emp
-- WHERE SUBSTR(rrn, 1, 2) > 79 AND SUBSTR(rrn, 1, 2) < 90
WHERE SUBSTR(rrn, 1, 1) = 8
ORDER BY rrn;

-- emp 테이블에서 서울사람이면서 김씨만
SELECT name, city, sal
FROM emp
WHERE city = '서울' AND SUBSTR(name, 1, 1) = '김';

-- emp 테이블에서 김씨, 이씨, 최씨 출력
SELECT name, city, sal
FROM emp
WHERE SUBSTR(name, 1, 1) IN ('김', '이', '최');

-- emp 테이블에서 홀수달에 태어난 사람만 출력
SELECT name, rrn, sal
FROM emp
WHERE MOD(SUBSTR(rrn, 3, 2), 2) = 1
ORDER BY sal DESC;

-- INSTR(string, substrng[, position [, occurrence ] ])
-- 문자열의 위치를 검색하여 반환
-- 문자열이 존재하지 않으면 0을 반환
-- 검색에서 LIKE 보다 속도가 빠름

SELECT INSTR('korea seoul', 'e') FROM dual;
-- 처음부터 검색해서 처음 만나는 위치
SELECT INSTR('korea seoul', 'ab') FROM dual;

SELECT INSTR('korea seoul', 'e', 7) FROM dual;
-- 7번째 위치부터 검색
SELECT INSTR('korea seoul', 'e', 1, 2) FROM dual;
-- 첫번째 위치부터 검색해서 두번째 나오는 e의 위치

-성이 김씨인 사람 
SELECT name, city, sal
FROM emp
WHERE INSTR(name, '김') = 1;

-- 이름(성포함)에 '이' 라는 단어가 포함되어 있는 모든 사람
SELECT name, city, sal
FROM emp
WHERE INSTR(name, '이') > 0;

-- 전화번호에서 서비스(지역)번호
SELECT name, tel
FROM emp;

SELECT name, tel,
    SUBSTR(tel, 1, INSTR(tel, '-') -1) 서비스번호
FROM emp;

-- 전화번호에서 서비스(지역)번호, 국번, 번호로 분리 -> 자바에서 하는 것이 효율적이다.
SELECT name, tel, SUBSTR(tel, 1, INSTR(tel, '-') - 1) 서비스번호,
    SUBSTR(tel, INSTR(tel, '-') + 1, INSTR(tel, '-', 1, 2) - INSTR(tel, '-') -1) 국번,
    SUBSTR(tel, INSTR(tel, '-', 1, 2) +1) 번호
FROM emp;