20.11.06. 오전 - 데이터베이스 join, having, group by, 그룹함수

vol, p194

오라클 사용 전용 조인 4가지를 살펴봅시다.

 

• 등가 조인 - 같은 값의 데이터가 존재했을 때 그 값을 '같다'라는 조건으로 행을 매칭하는 조인.
• 비등가 조인 - 전혀 다른 데이터를 기준으로 '다르다'라는 조인을 수행.
• 포괄(아우터) 조인 - 어느 한쪽의 데이터가 없을 때 데이터가 많은 쪽에 매칭.
• 자체(셀프) 조인 - 대상 테이블이 하나.

어제 등가조인을 수행했을 때 107개가 아닌 106개가 나온 것을 보았을 것이다. 이럴 때 포괄..?

 

p196

데이터만 같으면 컬럼이 다르더라도 얼마든지 수행 가능.

이큐조인이 수행하는 연산자가 '='라는 연산자를 사용하기 때문.

숫자는 숫자와 비교, 문자는 문자와 비교가 되는 datatype만 같으면 된다.

프라이머리키(pk, 기본키)와 포린키(fk, 외래키)로 연결되어있는 형태.

대부분 이큐 조인을 사용한다고 생각하시면 되겠습니다.

 

p197

테이블과 테이블의 연관관계, 테이블의 구조를 이해하고 있으면 전혀 어려울 것이 없습니다.

이큐절은 where절에 표시한다.

프라이머리키 포린키 연결 관계 의심표현

1.  양쪽 테이블에 똑같은 이름이 있는지 찾아보라.

거의 대부분 연결된 것들은 컬럼 이름이 같다. 달라도 얼마든지 참조는 가능하나 대부분 같다.

2. 프라이머리키 - 중복이 없다. / 포린키 - 중복을 허용한다.

이것을 보고 찾으면 좋다.

 

p198

예제를 봅시다.

조인조건이 들어오면 반드시 and로 묶어줘야 한다. or로 묶게 되면 단일로 인식되어 카티션프로덕트가 발생된다.

일반조건이 먼저오고 조인조건이 나중에 와도 상관없다. but, 성능적인 측면에서는 조인조건이 먼저 들어오고 일반조건이 들어오는 것이 좋다.

조인조건은 행을 합쳐주는 역할을 하고 거기에서 추가적인 행 필터링하는 것이 훨씬 빠르게 되기 때문에 이 형태를 익숙하게 연습하도록 하자.

 

Q. 우리 회사에 근무하는 사원의 이름과 그 사원이 근무하는 부서의 지역번호를 출력하시오.

자꾸 106 row가 나오네..

 

p201

n-1만큼 조인조건이 들어가야한다..

(이 부분 이해 잘 못했으니 이따 읽어보자)

 

null에 해당하는 값이 없어서 출력 안되는 것은 강제로 행이 부족한 곳에 null을 추가해서 매칭을 시켜주는 것을 아우터조건이라고 한다.

 

우리 사원들 중에 부서번호가 없는 사람을 위해 새로운 행을 추가했지만,

이번엔 저기에 추가한다면?

107 27

106 16

 1　11

 

106 + 16이니 122가 나와야한다.

다시 설명하자면,

처음 출력이 우리회사에 근무하는 모든 사원을 나열하고 사원이 근무하는 부서가 있으면 출력해라.

두번째 출력은 우리회사에 모든 부서를 나열하고, 그 사원이 근무하는 부서를 출력해라.

인 것이다.

 

 

 

 

★★ 여기↑↑ 이따가 제대로 복습하자 ★★

 

p202

넌이큐조인은 아쉽지만 실습할 수가 없습니다.

그래도 예제만 보면 이해되실 겁니다.

이렇게 쓰는거구나 정도만 이해하도록 하시죠.

 

p206

아우터 조인..

 

 

 

---

자, 연습문제를 또 풀어봅시다.

 

14. 모든 사원들의 이름, 부서 이름 및 부서 번호를 출력하시오.

여기서 where절을 해야지.

그리고 또 정정도..

 

16. 커미션을 받는 모든 사람들의 이름, 부서명, 지역ID 및 도시명을 출력하시오.

 

그럼 셀렉절에 넣을 컬럼은 어디서 가져와야하죠? 기준점을 보고 넣어주는 것이 코드상으론 맞습니다.

그러나, 결과로는 똑같습니다. 그래도, 코드상을 놓고 점수를 준다고 하면 점수를 주는 매체가 어디냐에 따라 달라지므로 기준점을 따라주시는게 좋습니다.

 

추가문제!

사원의 번호와 이름과 그 사원을 관리하는 관리자번호와 관리자 이름을 출력하시오.

현재 이 테이블은 관리자와 관리자가 아닌 사람까지 다 저장된 테이블.

매니저테이블이 있었으면 조인을 이용해서 할 수 있었겠지만 중복이 많으니 합쳐졌다.

그럼 우리는 어떻게 해야하는가?

그 테이블 안에서 컬럼이 컬럼을 참조할 수 있다. 이것을 바로 자체참조라고 함.

자체 참조는 무조건 첫번째 행이 null이 된다.

문제는, 매니저테이블이 없으니.... 없는 테이블을 우리가 만들면 된다.

 

자, 마지막 조인인 셀프조인을 알려드리겠습니다.

☆궁금해요 알리아스!

select절은 출력할 보고서의 틀을 바꿔주는 명령어.

from절은 출력용이 아닌, 가상의 테이블을 만들어서 거기의 이름을 지정해주는 명령어. 마치 여러개의 테이블인것처럼 바꿔서 사용할 수 있게 해준다.

 

 

 

오늘의 숙제!

17. 자신의 매니저보다 먼저 고용된 사원들의 이름 및 고용일을 출력하시오.

나의 고용일과 관리자의 고용일을 비교할 수 있어야합니다.

 

조인 작성법만 아셔도..^^;

 

자, 쉬어가는 타임입니다.

p233 함수타임~

 

오라클에서는 함수를 그룹함수라는 이름을 사용한다.

예를 들어, 부서별 월급의 총합을 보고 싶습니다. = 부서1, 부서2, 부서3 이렇게 따로따로 들어온 것을 하나의 그룹으로 인식해서 리턴하겠다.

 

p236

실제 사용하는 5가지만 알아두셔도 좋습니다. avg, count, max, min, sum

계산, 통계, 순위 정보들을 돌려주는 함수들이 각각 다양합니다.

더 추가적인 많은 함수를 사용하기 위해서는 윈도우 함수를 사용하기도 합니다.

윈도우 함수에는 순위, 집계 등등 굉장히 다양한 함수가 존재합니다.

오늘은 일단 그룹함수만 사용하는 법을 배워보겠음.

 

sum과 avg는 산술연산이 이뤄지므로 오로지 숫자값에 대해서만 반응하는 함수이다.

 

고용일에 min과 max를 하면.. 입사한지 오래된 일자와 최근 일자를 찾겠죠?

min과 max는 아스키코드값을 기준으로 최저값과 최고값을 찾아주는 것이기 때문에 모든 데이터타입에 적용할 수 있다.

 

count는 출력이 되는 행의 갯수를 돌려주는 함수.

50번 부서에 근무하는 사원은 45명이다~.~

80번 부서 같은 곳에서 일해야겠네 ㅋㅋㅋㅋ

 

자.. 여기서 뭔가 이상하죠? 커미션 받는 사람 했더니 107명이 아니라 35명이 나왔단 말이에요?

어라.. 두번째 사진의 sum값이 맞을텐데?? avg가 이상하네??

아하.... 문제를 알았다..

└ 그룹함수는 null값을 무시합니다. 함수라는 것 자체가 계산식을 포함하다보니 값이 없으면 수행할 수 없기 때문에 null값을 인정하지 않고 무시해버립니다.

 

대신 중첩이 가능합니다.

p242

기본적으로 함수는 디폴트값이 all이다. 그룹함수가 사용하는 옵션이 2가지. all과 distinct.

distinct는 중복을 제거하고 실행해라라는 뜻.

count 컬럼 함수는 null값을 무시해주다보니, 12개가 아닌 11개가 나온다.

지금까지 전체에 영향을 주는 distinct언어와 구분해서, 이것은 전체에 영향을 미치는 distinct 옵션이 아님을 주의하자.

 

p250

멀티플 정렬, 멀티플 중복을 생각해보자.

첫번째 기준, 셀렉리스트 순서대로 나열된다. 이 순서가 바뀌면 그룹의 방향이 틀려진다.

 

 

p252

where절이 먼저 실행되는데 출력할 것을 넣어놔서

 

 

 

where절은 행을 제한 avg(sal)>=000 불가능

having절은 그룹을 제한 avg(sal)>=000 가능

 

 

---

select distinct * column alias

from

where

group by

having

order by

 

조인조건 1. n-1개를 where절에

2. 똑같은 컬럼은 소유주테이블을 명시해라.

이큐조인은 pk=fk 에서 빈번.

논이큐조인은 다른연산자와 함께

아우터는 어느 한쪽에 데이터가 더 많을때. 서로 맞춰주기 위해 null값을 인정.

셀프는 알리아스를 이용해서 가상테이블 생성 자체참조.

 

★그룹바이절에 모두 반드시 명시되어있어야한다... 이 부분 다시 공부할 것.

 

 

지금부터 select 절을 쓸겁니다. 결과는 몰라요. 그러니 틀린 것을 찾아내세요.

뭘 구하는지 교수님도 몰라요 @_@ㅋㅋㅋ

컬럼이름 테이블이름 약어로 쓸거니 틀린거 아닙니다!!ㅋㅋ

삭제요. 살려주세요. 죽인다?

허어허허헣

 

SELECT name, distinct sal, sum(sal), 12*sal AnnSal

from dept

where sum(sal)>=2000 and name = %a

group by name, job

having sum(sal) <= 2000

order by comm

 

dept 아닌 employees로

distinct를 name 앞으로

name 앞으로 distinct

"AnnSal"

'%a'

...

 

select name, distinct, sal, sum(sal), 12*sal "AnnSal", comm, job

from employees

where sum(sal)>=2000 and name = '%a'

group by name, salary, job, comm

having sum(sal) <=2000

order by comm

까지 왔는데..

 

자.. 답은

select distinct name, sal, sum(sal), 12*sal "AnnSal"

from emp

where name like '%a'

group by name, job, sal, 12*sal, comm

having sum(sal) <=2000 and sum(sal)>=2000

order by comm

 

 

 

---

 

다시 연습문제를 풀어봅시다.

 

15. 30번 부서내의 모든 직업들을 유일한 값으로 출력하시오.

90번 부서 또한 포함하고, 직업을 오름차순으로 출력하시오.

select

where department_id = 30

job_title을 가져와야하나? job_id는 jobs테이블과 employees테이블에 있고 job_title은 jobs테이블에만.

select disctinct job_title

from employees e, jobs j

where e.job_id=j.job_id

and e.department_id=30

호오잉 ㅋㅋㅋ 교수님이 보여주신 답은 다르다. 내가 너무 복잡하게 생각했나.

 

18. 회사 전체의 최대 급여, 최소 급여, 급여 총 합 및 평균 급여를 출력하시오.

19는 아까 풀었으니 넘어가고

 

20. 각 직업별, 최대 급여, 최소 급여, 급여 총 합 및 평균 급여를 출력하시오.

단 최대급여는 MAX, 최소 급여는 MIN, 급여 총 합은 SUM 및 평균 급여는 AVG로 출력하고,

직업을 오름차순으로 정렬하시오.