12.22 오늘의 공부

▣오늘의 주요 키워드▣

1. case, decode에서 null check
2. 날짜에서 RR vs YY
3. 그룹함수
4. group by, having
5. sql 실행순서
6. join

 

---

[문제30] JOB_ID 열의 값을 기반으로 모든 사원의 등급을 표시하는 query를 작성합니다.
<화면출력>
JOB_ID	 	GRADE
---------	-------	
AD_PRES 	A
ST_MAN 		B
IT_PROG 	C
SA_REP 		D
ST_CLERK 	E
            Z
...

1) DECODE

SELECT job_id,
    DECODE(job_id,'AD_PRES','A',
        'ST_MAN','B',
        'IT_PROG','c',
        'SA_REP', 'D',
        'ST_CLERK','E','Z') GRADE
FROM employees;

2)case
SELECT job_id,
    case
        when job_id = 'AD_PRES' then
            'A'
        when job_id = 'ST_MAN' then    
            'B'
        when job_id = 'IT_PROG' then
            'c'
        when job_id = 'SA_REP' then
            'D'
        when job_id = 'ST_CLERK' then
            'E'
        else 'Z'
    end GRADE
FROM employees;
2-2)case2
SELECT job_id,
    case job_id
        when 'AD_PRES' then
            'A'
        when 'ST_MAN' then    
            'B'
        when 'IT_PROG' then
            'c'
        when 'SA_REP' then
            'D'
        when 'ST_CLERK' then
            'E'
        else 'Z'
    end GRADE
FROM employees;

 

문제30번 답 출력값

-decode 함수 괄호 안에서 마지막에 기준값 안 적으면 null이 출력

 

SELECT job_id,
        case 
            when job_id in('AD_PRES', 'AC_MGR') then 'A' --다른 비교연산자가 사용되면 기준값을 when절에 다 표현해줘야 함
            when job_id = 'ST_MAN' then  'B'
            when job_id = 'IT_PROG' then 'c'
            when job_id = 'SA_REP' then 'D'
            when job_id = 'ST_CLERK' then 'E'
            else 'Z'
        end GRADE
FROM employees;

SELECT employee_id,
        nvl2(commission_pct, (salary*12)+(salary*12*commission_pct), salary*12) ann_sal
FROM employees;     
/* --nvl2함수 
if commission_pct is not null then
    (salary*12)+(salary*12*commission_pct)
else
    salary*12
end if;    
*/
decode, case 표현식에서 null check 방법
※decode 함수에서는 null keyword를 이용해서 null check해야 한다.
SELECT employee_id, 
        decode(commission_pct,
                null, salary*12, --null키워드 사용, is null 사용x
                (salary*12)+(salary*12*commission_pct)) ann_sal
FROM employees;

※case 표현식에서 is null 연산자를 이용해서 null check 해야 한다.
SELECT employee_id,
    case
        when commission_pct is null then --null키워드 사용 x, is null or is not null연산자 사용해야함
            salary * 12
        else 
            (salary*12)+(salary*12*commission_pct)
    end ann_sal
FROM employees;

nvl2를 decode, case로 출력결과

select 
    to_char(to_date('95-10-27','yy-mm-dd'), 'yyyy-mm-dd') YY,
    to_char(to_date('95-10-27','rr-mm-dd'), 'yyyy-mm-dd') RR
FROM dual;

95-10-27
YY : 현재년도의 세기를 반영 : 2095-10-27
RR : 2000년도 표기법을 자동화로 변경해준다.

                    지정된(데이터입력)연도(년도)
                    0~49                    50~99
현재연도(년도)        
0~49            반환 날짜는 현재 세기를 반영     반환날짜는 이전세기를 반영
50~99           반환 날짜는 이후 세기를 반영     반환날짜는 현재세기를 반영

현재년도    데이터 입력날짜       YY     RR
1994        92-10-27        1995    1995
1994        17-10-27        1917    2017
2001        17-10-27        2017    2017
2048        52-10-27        2052    1952
2051        47-10-27        2047    2147

YY와 RR의 차이

★단일행 함수 -행당 하나의 결과를 반환하는 함수 -문자함수, 숫자함수, 날짜함수, 형변환함수, NULL관련함수, 조건제어 관련함서

★그룹함수 -여러행당(행짐합) 하나의 결과를 반환하는 함수 -sum, avg, median, variance, stddev, max, min, count -그룹함수에 입력하는 값이 숫자형만 입력해야하는 그룹함수 : sum, avg, median, variance, stddev -그룹함수에 입력하는 값이 모든 타입이 가능하는 그룹함수 : count, max, min -그룹함수는 null을 포함하지 않는다. 단 count(*)만 null을 포함

count : 행의 수를 구하는 함수
SELECT count(*)
FROM employees;

SELECT count(*) -- null포함한 행의 수를 구한다.
FROM employees
WHERE department_id = 50;

SELECT count(commission_pct), count(employee_id) --null제외한 행수를 구한다.
FROM employees
WHERE department_id = 50;

sum : 합
SELECT sum(salary)
FROM employees
WHERE department_id = 50; --department_id가 50인 것들만 salary를 다 더한 값

sum함수

avg : 평균
SELECT avg(salary)
from employees;

SELECT avg(commission_pct) --null은 제외한 평균
FROM employees;

-소수점 자리수는 trunc함수로 제거하거나 round함수로 반올림하거나 하자

예)
1,null,2 --> (1+2)/2 --3명이지만 null은 빼고 2명만 계산함
만약에 전체 3으로 수행하려면?
--(1+2)/3 --> nvl함수로 null값을 0으로 대체하기

SELECT avg(nvl(commission_pct,0))
from employees;
--null값을 가지고 있는 인원도 포함시키기 위해서 null을 0으로 바꾸어서 계산한다

nvl활용한 avg출력

★median : 중앙값
-관측수(n) 홀수면 : (n+1)/2
-관측수(n) 짝수면 : 평균(n/2, (n+1)/2)

SELECT avg(salary), median(salary)
FROM employees;

avg vs median

자료의 중심 위치를 나타내는 대표값	자료의 퍼진 정도를 나타내는 값
평균(이상치데이터 때문에 문제스), 중앙값	분산, 표준편차, 범위, 사분위수

★variance : 분산,  편차 제곱의 합의 평균

SELECT variance(salary)
FROM employees;

variance함수

★stddev : 표준편차

SELECT stddev(salary)
FROM employees;

★max : 최대값, min : 최솟값

SELECT max(salary), min(salary), max(salary)-min(salary)
FROM employees;

max, min, 최대값-최소값

SELECT max(hire_date), min(hire_date), max(last_name), min(last_name) 
FROM employees;
 --가장 최근에 입사한 날짜, 가장 오래된 입사한 날짜, 끝 이름, 첫 이름
 --즉 문자도 가능

max,min 활용

★group by : 테이블의 행을 작은 그룹으로 나눌 수 있다.

SELECT department_id, sum(salary)
FROM employees
group by department_id;

group by절

※그룹 함수를 사용하실 때 주의할 점!!

1. null 값은 포함하지 않음
2. SELECT절에 그룹 함수에 포함되지 않는 개별 칼럼은 하나도 빠짐없이 group by절에 명시해야 함
3. group by 절에는 열 별칭, 위치 표기법을 사용할 수 없음

SELECT department_id ,job_id, sum(salary) --sum(salary)는 그룹함수, 나머지가 개별컬럼
FROM employees
group by department_id, job_id;

group by절

오류 1. 별칭 사용

SELECT department_id dep_id,job_id, sum(salary) 
FROM employees
group by dep_id, job_id; --group by절에서 컬럼의 별칭 사용 오류!

오류 2. 위치 표기법 사용

SELECT department_id dep_id,job_id, sum(salary) 
FROM employees
group by 1, job_id; --group by절에서 컬럼의 위치표기법 사용하면 오류

오류 3. where절과 그룹 함수의 관계

SELECT department_id, sum(salary)
FROM employees
WHERE sum(salary) >= 10000 
GROUP BY department_id; 
--where절은 행을 제한하는 절이기 때문에 그룹함수의 결과를 제한하면 오류(그룹함수보다 먼저 돌아감)

★having : 그룹함수의 결과를 제한하는 함수(그룹 함수는 where이 아닌 having)

SELECT department_id, sum(salary)
FROM employees
GROUP BY department_id
having sum(salary) >= 10000 ;

having 함수

SELECT department_id, job_id, sum(salary)
FROM employees
group by department_id, job_id
having count(*) > 5;

having 함수2

-여기서 난 헷갈린 부분이 있었다. having count(*)>5이면 department_id와 job_id를 그룹하고 거기서 5명 이상이 되는 department_id와 job_id를 뽑아내고 그들의 salary의 합을 구한다? 왜 3개 밖에 안 나올까? 뭔가 애매하고 무엇을 뜻하는지 잘 몰랐다. 

-select 문에서 먼저 우선 생각해야 할 것이 sum(salary) 대신에 count(*)를 넣어서 출력해보면 밑에 사진처럼 출력이 된다. 이렇게 출력해보니까 왜 id가 3개가 나오는지 알게 되었다.

★SQL 실행 순서

-1.FROM -> 2.WHERE -> 3.GROUP -> 4.HAVING -> 5.SELECT -> 6.SORT

-먼저 테이블을 선택 - WHERE절 조건에 맞는 행 선택 - 그룹핑 -그룹핑된 행 그룹 함수조건으로 제한 -그룹함수 적용하고 난 뒤 출력할 컬럼 선택 - 정렬 -출력

SELECT department_id, sum(salary)
FROM employees
WHERE last_name like '%i%' 
group by department_id
having sum(salary) >= 20000
order by 1;

오류! 그룹함수

-그룹 함수를 두 번 중첩할 때 개별 칼럼이 있으면 오류 발생

SELECT department_id, max(avg(salary)) 
FROM employees
GROUP BY department_id;

※여기서 궁금증

-그렇다면 department_id(개별 칼럼)을 없애면 department_id는 어떻게 열에 출력하지?

-정답은 sub query에 있다? (내일이나 모레 배운다고 넘어간다! 구글링으로 혼자 찾아보자)

 

[문제31] 2008년도에 입사한 사원들의 job_id별 인원수를 구하고 인원수가 많은 순으로 출력하세요.

SELECT department_id,job_id, count(job_id)
FROM employees
WHERE hire_date between to_date('2008/01/01', 'yyyy/mm/dd') and to_date('2008/12/31 23:59:59', 'yyyy/mm/dd hh24:mi:ss')
GROUP BY department_id,job_id
ORDER BY 3 desc;

내 답

 

------------강사님 답---------------
SELECT job_id, count(*) cnt
FROM employees
WHERE hire_date>=to_date('2008-01-01','yyyy-mm-dd')
and hire_date < to_date('2009-01-01', 'yyyy-mm-dd')
group by job_id
order by cnt desc;

 

강사님 답

[문제32] 년도별 입사한 인원수를 출력해주세요.
SELECT to_char(hire_date,'yyyy'), count(*)
FROM employees
group by to_char(hire_date,'yyyy')
order by 1;

문제32

[문제33] 월별 입사한 인원수 출력해주세요. 
SELECT  to_number(to_char(hire_date, 'mm')) month, count(employee_id)
FROM employees
group by to_number(to_char(hire_date, 'mm'))
order by 1;

문제33

[문제34] 아래 화면과 같이 출력해주세요.

     TOTAL     2001년     2002년     2003년
---------- ---------- ---------- ----------
       107          1          7          6
1)       
SELECT  count(*) total,
        count(decode(to_char(hire_date,'yyyy'), '2001', 1)) "2001년", --이렇게 가로열로 2050년까지 뽑는다고 하면 비효율적이므로 서브쿼리를 사용해야함(다음주 수업까지 기다려야 한다고 함)
        count(decode(to_char(hire_date,'yyyy'), '2002', 1)) "2002년",
        count(decode(to_char(hire_date,'yyyy'), '2003', 1)) "2003년"
FROM employees;

2)
SELECT  count(*) total,
        count(case to_char(hire_date, 'yyyy') when '2001' then 'x' end) "2001년", --count는 문자든 숫자든 가능
        count(case to_char(hire_date,'yyyy') when '2002' then 'x' end) "2002년",
        count(case to_char(hire_date,'yyyy') when '2003' then 'x' end) "2003년"
FROM employees;

3)
SELECT  count(*) total,
        sum(case to_char(hire_date, 'yyyy') when '2001' then 1 end) "2001년", --sum은 문자가 안되고 숫자로 
        sum(case to_char(hire_date,'yyyy') when '2002' then 1 end) "2002년",
        sum(case to_char(hire_date,'yyyy') when '2003' then 1 end) "2003년"
FROM employees;

문제34

-문제 34번은 내가 못 풀었다. 조건은 어떻게 넣지? 만 생각하다가 case, decode 배웠던 것을 까먹었었다..

문제를 해결하기 위해 어떻게 풀어나갈지 생각해보기 훈련!!

 

※강사님의 해결책 제시
count(if 입사한 년도 = '2001' then
end if)

이런 식으로 먼저 틀을 생각해보자

 

---

★JOIN (파이썬이나 R에서도 똑같이 사용됨)
-두 개 이상의 테이블에서 내가 원하는 데이터를 가져오는 방법

1.catesian product

• 조인 조건 생략된 경우
• 조인 조건 잘못 만든 경우
• 첫 번째 테이블 행의 수와 두 번째 테이블 행의 수가 곱해지는 현상

SELECT employee_id, department_name --employee_id 107명, department_name 27개 
FROM employees, departments;

-위 코드는 107명의 employee_id와 부서 이름 출력하려고 하는 것인데 107 * 27로 한 명의 employee_id마다 27개의 department_name이 모두 출력되었다.

 

2.equi join, inner join, simple join, 등가 조인

SELECT employee_id, department_name
FROM employees, departments
WHERE department_id = department_id;
--ORA-00918: column ambiguously defined 오류

-위 코드는 employees테이블의 department_id와 department_id를 구분해야 하는 문제에 대한 조인

 

3. 해결책

SELECT e.employee_id, d.department_name
FROM employees e, departments d --테이블의 별칭을 사용하는 습관을 갖자
WHERE e.department_id = d.department_id; --조인조건 술어

[문제35]사원들의 사원번호, 근무 도시를 출력해주세요
SELECT e.employee_id, l.city
FROM employees e, locations l, departments d
WHERE e.department_id = d.department_id
and d.location_id = l.location_id;

-이 코드에서는 employees테이블의 employee_id와 location테이블의 city 칼럼을 중간다리 역할을 하는 departments 테이블까지 작성한 코드로 자세한 부분은 테이블들에 연결되어있는 밑에 있는 모델링 그림을 통해서 알아보자. 

 

※n개의 테이블을 조인하려면 조인 조건 술어는 몇 개를 만들어야 하나요?
n-1개의 조인 조건 술어를 작성해야 한다.

 

[문제 36]사원들의 사원번호, 국가 이름을 출력해주세요
SELECT e.employee_id, c.country_id
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; --조인조건술어

★employees 테이블

★departments 테이블

★locations 테이블

★countries 테이블

sql 테이블들의 model을 들어가 보면 이렇게 표현되어 있는데 후에 더 공부하고 해석할 예정이다. 

대학교 다니던 시절에 소프트웨어공학에서 클래스 다이어그램이라고 배웠던 기억을 다시 떠올리며 오늘은 여기서 마무리하고 내일은 더 발전하고 성장하는 내가 되겠다.