[저장]oracle cheat sheet

실습용 데이터 획득

https://m.blog.naver.com/PostView.nhn?blogId=chin1001&logNo=60177951428&proxyReferer=https:%2F%2Fwww.google.com%2F

 

cheatsheet 시작

 

SELECT ename, sal, (sal*12)+nvl(COMM,0) AS annsal, comm FROM emp ORDER BY annsal DESC, sal desc;

SELECT ename, sal, (sal*12)+nvl2(COMM,0,9) AS annsal, comm FROM emp ORDER BY annsal DESC, sal desc;

SELECT * FROM emp WHERE JOB != 'MANAGER'

SELECT empno, ename, sal, deptno FROM EMP
WHERE deptno = 10 OR deptno = 20

SELECT * FROM emp WHERE sal >= 2000 AND sal <= 3000

SELECT * FROM emp
WHERE ename NOT LIKE '_L%'
--AND sal < 1000 OR sal > 2000

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

SELECT * FROM emp WHERE upper(ENAME) = upper('scott')

SELECT ename, length(ENAME), lengthb(ename), '한글', LENGTH('한글'),lengthb('한글') FROM emp

SELECT * FROM dual;

SELECT job, substr(job, 1,2), substr(job,3,2), substr(job,5), substr(job,-5) FROM emp

SELECT job, LENGTH(job), substr(job, LENGTH(job)),substr(job , -LENGTH(job),2) FROM emp

SELECT instr('hello, oracle!', 'l') AS inst_1, instr('hello, oracle!', 'l', 5) AS instr_2, instr('hello, oracle!', 'l', 2, 2) AS instr_3 FROM dual;

SELECT '010-1234-5678' AS replace_before, REPLACE('010-1234-5678', '-',' ') FROM dual

SELECT rpad('aaa',5,'*') FROM dual

주민등록번호 필터링
SELECT '880309-1919191' AS num, rpad(substr('880309-1919191', 1, 8),14,'*') AS sinsang FROM dual

SELECT sysdate AS now, ADD_MONTHS(sysdate, 2) AS after_2m FROM dual

SELECT empno, ename, hiredate, ADD_MONTHS(hiredate, 32*12) AS work32year FROM emp WHERE ADD_MONTHS(hiredate, 32*12) <= sysdate

SELECT empno, ename, hiredate, months_between(sysdate, hiredate)/12 AS years FROM emp

SELECT sysdate, next_day(sysdate, '월요일'), last_day(sysdate) FROM dual

SELECT to_date('10.10.10', 'YYYY-MM-DD') FROM dual

SELECT sysdate, to_char(sysdate, 'mon', 'nls_date_language = korean') AS mon_kor FROM dual

SELECT sysdate, to_char(sysdate, 'hh24:MI:SS') AS HH24MISS FROM dual

SELECT empno, ename, job, sal, 
	decode(job,
		'MANAGER', SAL*1.1,
		'SALESMAN', SAL*1.05,
		'ANNALYST', SAL,
		SAL*1.03) AS UPSAL
FROM emp;

SELECT empno, ename, job, sal,
	CASE job
		WHEN 'MANAGER' THEN sal*1.1
		WHEN 'SALESMAN' THEN SAL*1.05
		WHEN 'ANALYST' THEN sal
		ELSE sal*1.03
	END AS upsal
FROM emp;

SELECT empno, ename, comm,
 CASE
 	WHEN comm IS NULL THEN '해당사항 없음'
 	WHEN comm = 0 THEN '수당없음'
 	WHEN comm > 0 THEN '수당 : ' || COMM
 END AS comm_text
 FROM emp;

SELECT sum(sal), CEIL(avg(sal)), count(sal), max(sal), max(sal) FROM emp
 
SELECT max(hiredate) FROM emp WHERE deptno = 10;

SELECT avg(sal), '10' AS deptno FROM emp WHERE deptno = 10
UNION ALL 
SELECT avg(sal), '20' AS deptno FROM emp WHERE deptno = 20
UNION ALL
SELECT avg(sal), '30' AS deptno FROM emp WHERE deptno = 30;


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

SELECT deptno, job, avg(sal)
FROM EMP e GROUP BY deptno, JOB
	HAVING avg(sal) >= 2000
ORDER BY deptno, job;

SELECT deptno, job, count(*), max(sal), sum(sal), avg(sal)
	FROM EMP
GROUP BY deptno, job
ORDER BY deptno, job;

SELECT deptno, job, count(*), max(sal), sum(sal), avg(sal)
FROM emp
GROUP BY rollup(deptno, job);

SELECT deptno, job, count(*), max(sal), sum(sal), avg(sal)
FROM EMP
GROUP BY cube(deptno, JOB)
ORDER BY deptno, job;

SELECT deptno, job, count(*)
FROM EMP
GROUP BY GROUPING sets(deptno, JOB)
ORDER BY deptno, job;

SELECT deptno, job, count(*), max(sal), sum(sal), avg(sal),
grouping(deptno),
grouping(job)
FROM EMP
GROUP BY cube(deptno, JOB)
ORDER BY deptno, job;

SELECT decode(GROUPING(deptno), 1, 'all_dept', deptno) AS deptno,
		decode(GROUPING(job), 1, 'all_job', job) AS job,
		count(*), max(sal), sum(sal), avg(sal)
		FROM EMP
GROUP BY cube(deptno, JOB)
ORDER BY deptno, job;

SELECT deptno, job, count(*), sum(sal),
grouping(deptno),
grouping(job),
grouping_id(deptno, job)
FROM EMP
GROUP BY cube(deptno, JOB)
ORDER BY deptno, job;

SELECT deptno,
listagg(ename, ', ')
WITHIN GROUP(ORDER BY sal desc) AS enames
FROM EMP
GROUP BY deptno;

SELECT * 	FROM(
				SELECT deptno, job, sal FROM emp
			) pivot(max(SAL)
				FOR deptno IN (10, 20, 30)
			)ORDER BY JOB ;
			
SELECT *
	FROM(SELECT job, deptno, sal FROM emp)
	pivot(max(sal)
		FOR job IN ('CLERK' AS clerk,
					'SALESMAN' AS SALESMAN,
					'PRESIDENT' AS PRESIDENT,
					'MANAGER' AS MANAGER,
					'ANALYST' AS ANALYST
					)
	)
ORDER BY deptno;

SELECT *
	FROM EMP e, SALGRADE s 
	WHERE e.sal BETWEEN s.losal AND s.HISAL ;
	
SELECT e1.empno, e1.ename, e1.mgr, e2.empno AS mgr_empno, e2.ename AS mgr_ename
	FROM emp e1, emp e2
WHERE e1.mgr = e2.empno;

SELECT e1.ename AS name , e2.ename AS mgr_name, e2.JOB 
	FROM emp e1, emp e2
WHERE e1.mgr = e2.empno;

-- left join
SELECT e1.empno, e1.ename, e1.mgr, e2.empno AS mgr_empno, e2.ename AS mgr_ename
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno(+)
ORDER BY e1.empno;

-- right join
SELECT e1.empno, e1.ename, e1.mgr, e2.empno AS mgr_empno, e2.ename AS mgr_ename
FROM emp e1, emp e2
WHERE e1.mgr(+) = e2.empno
ORDER BY e1.empno;

-- sub query
SELECT *
	FROM EMP
	WHERE sal > (SELECT SAL
					FROM emp
					WHERE ename = 'JONES'
	);
	
SELECT * FROM EMP
WHERE sal IN (SELECT max(SAL)
				FROM EMP
				GROUP BY deptno);
				
SELECT * FROM emp
WHERE sal=SOME (SELECT max(sal) FROM EMP
				GROUP BY deptno);
				
SELECT * FROM emp
WHERE sal < ANY (SELECT sal FROM emp WHERE deptno = 30)
ORDER BY sal, empno;

SELECT *
FROM emp
WHERE sal < ALL (SELECT sal FROM emp WHERE deptno = 30);

SELECT * FROM emp
WHERE EXISTS ( SELECT dname
				FROM dept
				WHERE deptno = 10)
--			AND deptno = 10
;


SELECT *
FROM emp
WHERE (deptno, sal) IN (SELECT deptno, max(sal)
						FROM emp
						GROUP BY deptno);
						

					
WITH
e10 AS (SELECT * FROM emp WHERE deptno = 10),
d AS (SELECT * FROM DEPT)
SELECT e10.empno, e10.ename, e10.deptno, d.dname, d.loc FROM e10, d
WHERE e10.deptno = d.deptno;

SELECT empno, ename, job, sal,
	(SELECT grade
		FROM salgrade
		WHERE e.sal BETWEEN losal AND hisal) AS salgrade, deptno,
		(SELECT dname
		FROM dept
		WHERE e.deptno = dept.deptno
		) AS dname
		FROM emp E;