[SQL][TIL] Quiz - 2021/07/07

Q1. EMP 테이블에서 급여가 1500이상 3000이하이면서 커미션이 NULL이거나 0인 사원을 검색하세요.

alter session set nls_date_format  = 'rr/mm/dd';

select *
from emp
where (sal between 1500 and 3000)
and (comm is null or comm = 0);

---

Q2. emp 테이블에서 12월에 입사한 사원을 입사일을 기준으로 검색하세요.

select *
from emp
where to_char(hiredate, 'mm') = '12'
order by hiredate;

---

Q3. EMP 테이블에서, 이름(ENAME)이 'S' 또는 'A'로 시작하는 사원을 검색하세요.

select empno, ename, sal, deptno
from emp
where ename like 'S%' or ename like 'A%';

// where절에 조건문을 or로 두개이상 연결할 경우, 완전한 문장으로 적는다.

---

Q4. EMP 테이블에서, 급여(SAL)보다 커미션(COMM)을 많이 받는 사원을 검색하세요.

select empno, ename, sal, comm, deptno
from emp
where sal < comm;

---

Q5. EMP 테이블에서, 급여(SAL)가 2000 보다 작고, 3000 보다 많은 사원을 검색하시오.

select empno, ename, sal, deptno
from emp
where sal not between 2000 and 3000;

---

Q6. EMP 테이블에서, 커미션(COMM)을 기준으로 내림차순 정렬된 결과를 검색하세요. 단, 커미션이 NULL인 행은 마지막에 검색되도록 한다.

select empno, ename, sal, comm
from emp
order by comm desc nulls last;

SELECT *
FROM emp
ORDER BY NVL(comm,-1) desc;

// comm값이 null이면 -1을 출력해라

// 오름차순 : asc 내림차순 : desc

// null값인 행을 마지막에 검색 : nulls last

---

Q7. EMP 테이블에서, 매니저(MGR)가 없는 사원은 'No Manager'의 문자를 다음과 같이 검색하세요.

select empno, ename, job, 
        decode(mgr,null,'No Manager',mgr) as manager
from emp;

// No Manager가 맨 아래로 오게 하려면?!

---

Q8. EMP 테이블에서 DEPTNO, JOB 컬럼으로 Grouping 된 급여의 합계를 다음과 같이 검색하세요.

방법1) decode 활용하기

select deptno, 
        sum(decode(job,'ANALYST',sal)) as analyst,
        sum(decode(job,'CLERK',sal)) as clerk,
        sum(decode(job,'MANAGER',sal)) as manager,
        sum(decode(job,'PRESIDENT',sal)) as president,
        sum(decode(job,'SALESMAN',sal)) as salesman
from emp
group by deptno
order by 1;

// 그룹핑하기 전 출력결과는 아래와 같다. 

decode( , , ) -> 하나의 컬럼이 된다. 따라서, sum(decode( , , ))와 같이 sum을 바깥에 씌워줘야 함

 

방법2) PIVOT 함수 적용하기

SELECT *
FROM ( SELECT deptno, job, sal
           FROM emp )
PIVOT (SUM(sal) FOR job IN ('ANALYST' AS analyst,
                                      'CLERK' AS clerk,
                                     'MANAGER' AS manager,
                                     'PRESIDENT' AS president,
                                     'SALESMAN' AS salesman))
ORDER BY deptno ;

+) ROLLUP 함수 : 마지막 행이 추가되어 각 컬럼들의 SUM값을 출력한다

select DEPTNO
,SUM(decode(job, 'ANALYST',SAL)) AS ANALYST
,SUM(decode(job, 'CLERK',SAL)) AS CLERK
,SUM(decode(job, 'MANAGER',SAL)) AS SALESMAN
,SUM(decode(job, 'PRESIDENT',SAL)) AS MANAGER
,SUM(decode(job, 'SALESMAN',SAL)) AS PRESIDENT
from emp
GROUP BY ROLLUP(DEPTNO);

---

Q9. EMP 테이블에서, 'JONES' (ENAME)보다 더 많은 급여(SAL)를 받는 사원을 검색하세요. 단, JONES의 급여도 함께 검색합니다.

방법1)

select empno, ename, sal,
    decode(ename, 'JONES', sal) "Jones's Salary"
from emp
where sal > (select sal
               from emp
               where ename = 'JONES');

// Jones's Salary값이 출력이 안 됨ㅠㅠ

방법2)

select e.empno, e.ename, e.sal,
        decode(s.ename,'JONES',s.sal) as "Jones's Salary"
from emp e, emp s
where s.ename = 'JONES'
and e.sal > s.sal;

// 빨간색 글자 부분을 모두 s 로 적어야 한다.

그렇지 않고, e와 s를 혼용해서 적으면 아래처럼 Jones's Salary가 출력이 안 된다.

왜?

3 select

1 from

2 where

순서로 해석을 하므로 2 where에서 조건 걸어놓은 테이블명으로 3 select에서도 찾게 된다.

또, 모두 e로 적으면 JONES행 값만 반복 출력된다.

---

Q10. DEPT, EMP 테이블을 사용하여 부서별 급여의 합계를 다음과 같이 검색하세요. 단, 근무하는 사원이 없는 부서도 함께 표시합니다.

select d.deptno, d.dname, sum(e.sal) as sum_sal
from dept d, emp e
where d.deptno = e.deptno(+)
group by d.deptno, d.dname
order by 1;

---

Q11. DEPT, EMP 테이블을 사용하여 각 부서의 소속 사원 유무를 확인하는 검색 결과를 만드시오.
EMP 컬럼은 소속 사원이 존재할 때 'YES', 아니면 'NO'를 검색합니다.

select d.deptno, d.dname, d.loc, 
        case count(e.ename) when 0 then 'NO'
        else 'YES' end as "EMP"
from dept d, emp e
where d.deptno = e.deptno(+)
group by d.deptno, d.dname, d.loc
order by 1;

// 조건부 표현식 decode가 아직 잘 안됨

---

Q12. COUNTRIES, EMPLOYEES 테이블을 이용하여, 'Canada'에서 근무 중인 사원 정보를 다음과 같이 검색하세요. 만약 추가적으로 필요한 테이블이 더 있다면 함께 사용합니다.

select e.first_name as first_name
     , e.last_name as last_name 
     , e.salary as salary
     , e.job_id as job_id
     , c.country_name as country_name
from employees e, departments d, locations l, countries c
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.country_id = c.country_id
and c.country_name = 'Canada';

// 테이블간 관계를 잘 알아야 함. 연관된(중복된) 컬럼을 찾아서 계속 연결~연결~

---

Q13. EMP 테이블에서 1981년도에 입사한 사원들을 입사 월별로 인원수를 검색하세요. 단, 사원이 없는 월도 함께 출력

select to_char(hiredate,'yyyy/mm') hire, count(*) cnt
from emp
where hiredate between to_date('1981/01/01','yyyy/mm/dd')
                   and to_date('1982/01/01','yyyy/mm/dd') - 1/86400
group by to_char(hiredate,'yyyy/mm');

+ (join)
select '1981/'|| lpad(level,2,0)
from dual
connect by level <= 12;

 

select b.hire, nvl(a.cnt,0) cnt
from (select to_char(hiredate,'yyyy/mm') as hire, count(*) cnt
        from emp
        where hiredate between to_date('1981/01/01','yyyy/mm/dd')
                    and to_date('1982/01/01','yyyy/mm/dd') - 1/86400
       group by to_char(hiredate,'yyyy/mm')) a
      ,(select '1981/'|| lpad(level,2,0) as hire
       from dual
       connect by level <= 12) b
where a.hire(+) = b.hire
order by 1;

 

 

 

---

select to_char(e.hiredate,'yyyy/mm') as hire
    , count(e.ename) as cnt
from emp e join emp h
on e.hiredate = h.hiredate
and to_char(e.hiredate,'yyyy')= '1981'
group by to_char(e.hiredate,'yyyy/mm')
order by 1;

// 사원이 없는 월도 함께 출력 => 해결하지 못함

 

drop table t1 purge;
create table t1 (hiredate varchar2(20));
insert into t1 values('1981/01');
insert into t1 values('1981/02');
insert into t1 values('1981/03');
insert into t1 values('1981/04');
insert into t1 values('1981/05');
insert into t1 values('1981/06');
insert into t1 values('1981/07');
insert into t1 values('1981/08');
insert into t1 values('1981/09');
insert into t1 values('1981/10');
insert into t1 values('1981/11');
insert into t1 values('1981/12');

select to_char(e.hiredate,'yyyy/mm') as hire
    , count(e.ename) as cnt
from t1 t, emp e, emp h
where t.hiredate = e.hiredate(+)
and e.hiredate= h.hiredate
and to_char(e.hiredate,'yyyy')= '1981'
group by e.hiredate
order by 1;

// 점점 이상해지고 있음