[6/100] 240417

인라인뷰 = 프롬절에 쓰는 서브쿼리이며 성능때문에 쓴다.

like의 경우 정규표현식이나 함수기반인덱스를 사용할수도 있지만 함수기반 인덱스같은 경우 권한이 없을 확률이 크다.

그러므로 dba에게 함수기반 인덱스를 추가해도 되는지 물어보던가 해달라고 하던가 아니면 그냥 like를 쓴다. 하지만 like를 쓰지 않아도 되는 경우에는 되도록 쓰지 않는다.

정규표현식은 오라클에서만 사용되고 ANSI문법은 아니다.

/*
CNT10    cnt20   cnt30
-------------------------
3          5       6
*/

select (select distinct count(1) from emp where deptno = 10) as CNT10,
       (select distinct count(1) from emp where deptno = 20) as CNT20,
       (select distinct count(1) from emp where deptno = 30) as CNT30
from dual
;

-- 각 부서별 사원수를 출력하시오.
--조건1. 부서별 사원수가 없더라도 부서번호,부서명은 출력 ?
--조건3. 부서번호 오름차순 정렬 order by deptno asc

select d.deptno, d.dname, count(d.deptno) as 사원수
from emp e, dept d
where e.deptno (+) = d.deptno
;

--강사님거
select d.deptno, d.dname, decode(count(empno), 0, '없음', count(empno))as 사원수
from emp e, dept d
where e.deptno(+) = d.deptno
group by d.deptno, d.dname
order by d.deptno asc
;

--decode
select deptno, dname
, DECODE(deptno, 10, '십',
                 20, '이십',
                 30, '삼십',
                     '아니다')as nn
from dept
;
-- case when-then
select (case
            when deptno = 10 then '십'
            when deptno = 20 then '이십'
            when deptno = 30 then '삼십'
            else '아니다'
        END) as deptno
from dept;

select (case
            when sal between 1000 and 2000 then '1등급'
            when sal between 2000 and 3000 then '2등급'
            when sal > 3000 then '3등급'
            else '규격 외'
        end) as nn, sal
from emp;

--사원테이블에서 각 사원의 사원번호, 사원명, 매니저번호, 매니저명을 출력하시오
--단 각 사원의 급여는 매니저 급여보다 많거나 같다.

select e1.empno 사원번호, e1.ename 사원명, e2.empno 매니저번호, e2.ename 매니저명
from emp e1, emp e2
where e1.empno = e2.mgr and e1.sal > e2.sal
;

--반올림 round(숫자, 보여주고싶은 자릿수)
select 10.98765, round(10.94,0), round(10.94,1) from dual;
select 57.123, round(57.123, -1), round(54.123,-1) from dual;
--올림 ceil(숫자)
select 10.98765, ceil(10.94), ceil(10.94) from dual;
select 57.123, ceil(57.123), ceil(54.123) from dual;
--내림 floor(숫자)
select 10.98765, floor(10.94), floor(10.94) from dual;
select 57.123, floor(57.123), floor(54.123) from dual;
--버림 trunc(숫자, 보여질자리수)
select 10.98765, trunc(10.94,1), trunc(10.94,2), trunc(12345,-1) from dual;
select 57.123, trunc(57.123,0), trunc(54.123,1), trunc(54.123,2) from dual;
--입사일로부터 지금까지 근무년수가 30년 이상인 사원의 사원번호, 사원명, 입사일, 근무년수 출력
-- 단 근무년수는 월을 기준으로 버림(ex 30.4년 = 30년)

select empno 사원번호, ename 사원명, hiredate 입사일, trunc(((sysdate-hiredate)/365),0) || '년' as 근무년수
from emp
where trunc(((sysdate-hiredate)/365),0) > 40
;

--where에서 알리어스 사용하는방법 = frmp에서 서브쿼리를 써서 인라인뷰로 만들경우
--where절에서 알리어스가 사용가능해진다.
select *
from (
        select empno, ename, hiredate
                , trunc((sysdate-hiredate)/365,0) 근무년수
        from emp)
where 근무년수 > 40;

-- LIKE연산자
select ename from emp
where ename like 'A%' --A로 시작
where ename like '%A%' -- A가 들어가기만하면됨
where ename like '%A' --A로 끝
where ename like '_A%' --두번째글자에 A가 들어간
;

--각 부서 별 평균 급여가 2000이상이면 초과, 그렇지 않으면 미만 출력

select deptno, trunc(avg(sal)),(case
                                when avg(sal) >= 2000 then '초과'
                                else '미만'
                                end) as nn
from emp
group by deptno
;
--이문제 주말에 다시 풀기
--각 부서별 입사일이 가장 오래된 사람을 한명씩 선별해 사원번호, 사원명, 부서번호, 입사일 출력
-- 서브쿼리 인라인뷰 이용
select e.empno, e.ename, m.deptno, m.mdate
from emp e,
     (select deptno, min(hiredate) mdate from emp group by deptno) m
where e. deptno = m.deptno and e.hiredate = m.mdate
;

--서브쿼리 : where
select empno, ename, hiredate, deptno
from emp
where (deptno, hiredate) in (select deptno, min(hiredate) from emp group by deptno)
;

--emp테이블에서 부서인원이 4명보다 많은 부서의 부서번호, 인원수 급여의합 출력
select deptno, count(empno) cnt, sum(sal) ssal
from emp
group by deptno
having count(deptno) >=4
;

--emp테이블에서 가장 많은 사원이 속해있는 부서번호와 사원수를 출력
--가장 많은 사원이 속해있는 부서 찾기
select deptno, count(*)
from emp
group by deptno
having count(deptno) = (select max(count(*)) from emp group by deptno)
;

select a.*,b.*
from
        (select deptno, count(1) as cnt from emp group by deptno) a,
        (select max(count(1)) as mcnt from emp group by deptno) b
where a.cnt = b.mcnt;

--가장 많은 사원을 갖는 mgr의 사원번호를 출력
select empno
from (

select * from emp;

select  from emp group by  having max(count(*));

--mgr 최대 카운트 값에 해당하는 사원번호
select mgr empno --이 위치에서 알리어스를 사용하면 mgr = empno
from emp
group by mgr
having count(mgr) = (select max(count(*)) from emp group by mgr)
;

select empno
from emp
group by empno
having empno = (select mgr from emp group by mgr having count(mgr) = (select max(count(*)) from emp group by mgr))
;