캐넌 - 개발자 블로그

※ EMP, DEPT, SALGRADE 테이블 구조 ※ 집합연산자 union union all minus intersect 1. union VS union all # 비교 / 결과는 동일한 쿼리 union은 데이터 중복을 제거하면서 데이터가 정렬 select deptno, sum(sal) from emp group by deptno union select null as deptno, sum(sal) from emp; union all은 중복이 있고 데이터가 무작위 정렬 정렬작업이 없어서 성능은 더 좋다. 따라서 정렬작업이 필요 없다면 union all을 쓰는 것이 바람직 하다. select deptno, sum(sal) from emp group by deptno union select null as d..

※ EMP, DEPT, SALGRADE 테이블 구조 ※ 튜닝 set autot traceonly statistics - 위의 쿼리는 시간이 얼마나 걸리는 지 알게 해준다. 1. select ~ scalar subquery 튜닝 # 문제 116. 다음을 튜닝하시오. # 튜닝 전 select ename, sal, (select sum(sal) from emp) 총월급 from emp; # 튜닝 후 select ename, sal, sum(sal) over () 총월급 from emp; - 테이블을 많이 불러올수록 느려짐을 명심하자!! # 문제 117. 다음을 튜닝하시오. # 튜닝 전 select ename, sal, (select sum(sal) from emp) 총월급, (select max(sal) fr..

※ EMP, DEPT, SALGRADE 테이블 구조 ※ Subquery single row subquery 서브쿼리에서 메인쿼리로 하나의 값이 리턴되는 경우 multiple row subquery 서브쿼리에서 메인쿼리로 여러 개의 값이 리턴되는 경우 multiple column subquery 서브쿼리에서 메인쿼리로 여러 개의 컬럼값들이 리턴되는 경우 구문 subquery의 이름 select scalar subquery from in line view where subquery group by having subquery order by scalar subquery 1. single row subquery # 문제 111. DALLAS에서 근무하는 사원들의 이름과 월급을 출력하시오. (JOIN 쓰지 마시..

※ EMP, DEPT, SALGRADE 테이블 구조 ※ 1999 ANSI JOIN ~ Oracle, My-SQL, Maria DB 등 공용 left right full outer natural cross 1. full outer join # 문제 103. 이름, 부서위치를 출력하는데 (+) outer 사인을 양쪽에 작성해 보시오. select e.ename, d.loc from emp e, dept d where e.deptno (+) = d.deptno (+); select e.ename, d.loc from emp e full outer join dept d on (e.deptno = d.deptno); - 첫번째 쿼리는 작동을 안함. 따라서 양쪽을 모두 outer join하고 싶을 땐, ANSI 방..

※ EMP, DEPT, SALGRADE 테이블 구조 ※ JOIN equi join outer join non equi join self join 1. equi join(inner join) ~ 연결고리가 "="인 조인문법 # 문제 94. 이름, 월급, 부서명, 직업, 부서번호를 출력하는데 월급이 1000~3000 사이만 출력하시오. select e.ename, e.sal, d.dname, e.job, e.deptno from emp e, dept d where e.deptno = d.deptno and e.sal between 1000 and 3000; # 문제 95. 아래와 같이 출력되게 하시오. select d.loc, listagg(e.ename,',') within group (order by e..

※ EMP 테이블 구조 ※ 데이터 분석 함수 1. rank 함수 # 문제 82. 1981년도에 입사한 사원들의 이름과 입사일과 순위를 출력하는데 먼저 입사한 사원 순으로 순위를 부여하시오. - 튜닝 전 select ename, hiredate, rank() over (order by hiredate asc) 순위 from emp where to_char(hiredate,'RRRR') = '1981'; - 튜닝 후 select ename, hiredate, rank() over (order by hiredate asc) 순위 from emp where hiredate between to_date('81/01/01','RR/MM/DD') and to_date('81/12/31','RR/MM/DD'); 2. ..

※ EMP 테이블 구조 ※ 리포팅 함수 rollup cube grouping 1. rollup # 문제 74. 직업, 직업별 총 월급을 출력하는데, 총월급이 맨 아래 출력되게 하시오. select job, sum(sal) from emp group by rollup(job); - group by의 rollup함수를 쓰면 저렇게 sum의 경우에는 모든 그룹의 총 합이 나온다. max, min, count의 경우에도 한번 해보면 도움이 될다. select job, sum(sal), max(sal), min(sal), count(*) from emp group by rollup(job) - 보통 생각하기 귀찮을 때 union all을 사용해서 저런 형식을 만드려고 하는 데 이는 SQL 초보가 하는 것이다. 위..

※ EMP 테이블 구조 ※ 그룹 함수 그룹함수 avg, count, max, min, stddev, sum, variance 1. max 함수 # 문제 65. 부서번호가 20번인 사원들 중에서 최대월급을 출력하시오. select deptno, max(sal) from emp where deptno = 20 group by deptno; 2. avg 함수 # 문제 66. 커미션이 없는 사원은 0으로 처리하고 평균을 구하시오. select avg(comm) from emp; select avg(nvl(comm,0)) from emp; - 첫번째 쿼리는 null값을 무시하기 때문에 나누는 값이 4이고 두번째 쿼리는 null값을 처리했기 때문에 나누는 값이 12이다. # 문제 67. 직업 평균 월급이 3000 ..

※ EMP 테이블 구조 ※ 일반 함수 nvl null을 바꾸는 함수 nvl2 null을 조건부로 출력하는 함수 decode (equal) 조건부 함수 case 조건부 함수 1. nvl 함수 # 문제 55. 이름과 커미션을 출력하는데, 커미션이 null인 사원들은 0으로 출력하시오. select ename, nvl(comm,0) from emp; # 문제 56. 이름과 커미션을 출력하는데, 커미션이 null인 사원들은 no comm이란 글자를 출력하시오. select ename, nvl(to_char(comm), 'no comm') from emp; - comm의 형태는 int만 받을 수 있기 때문에 꼭 to_char을 써서 문자형으로 바꾼 후 사용해야 한다. 2. nvl2 함수 # 문제 57. 이름, 월..

※ EMP 테이블 구조 ※ 변환 함수 to_char 문자형 데이터로 변환 to_number 숫자형 데이터로 변환 to_date 날짜형 데이터로 변환 명시적 형변환 위의 형변환 함수를 사용 암시적 형변환 오라클이 자동으로 형변환을 수행하는 것 1. select ename, sal from emp where sal = 3000; 2. select ename, sal from emp where sal = '3000'; - 숫자의 우선순위가 높아서 암시적으로 문자를 숫자로 형변환 해주는 데 이것을 암시적 형변환이라고 한다. - 오라클은 암시적 형변환을 수행해 에러가 나지 않고 결과를 출력해 주지만, 성능을 느리게 한다. 따라서 가급적이면 암시적 형변환이 발생하지 않도록 쿼리문을 작성하는 것이 중요하다. - 형면..