[구디아카데미 후기]DAY 7 WEHRE절, HAVING절, INNER JOIN(내부조인), OUTER JOIN(외부조인)

NA YE SOM·2023년 7월 6일

IT 국비지원 구디아카데미 후기 민경태 강사님

▶ 한줄평 : 조인이 실무에서 많이 쓰인다는 것을 알았고 민경태 강사님께서 1회차 시험의 채점기준도 세세하게 알려주셔서 도움이 많이 되었다.

< 학습 목표 >
GROUP BY 해야만 체크 -> HAVING
GROUP BY 안해도 체크 -> WHERE
(이유 : 성능)

두개 이상의 테이블 조회
쿼리 안에 쿼리 적는 방법

GROUP BY 절

GROUP BY(같은 GROUP을 모으기 위해서 씀)

가지고 있는 값이 같으면 같은 그룹을 가짐
목적 : 통계(계산 위해서) -> 모아서 계산을 했더니 값이 이렇게 나옴
통계 함수랑 같이 쓰는 것(SUM, AVG, MAN, MIN, COUNT)
GROUPING 시 ) 함수가 나오지 않으면 : 굳이 GROUPING할 이유가 없는 쿼리문임(GROUPING시 계산이 들어감)

SELECT
FROM
[WHERE][GROUP BY]

사원 테이블에서 동일한 부서번호를 가진 사원들을 그룹화하여 조회하시오.(풀 수 없음)

풀수가없음 (이유 : GROUP BY는 통계를 내는 목적이 있어야 함)
하나의 그룹으로 묶으면 하나의 행으로 보임
동일한 부서 번호를 가진 사원 10명이면 하나의 그룹으로 묶으면) 한행으로 변함
(10명을 한줄로 묶을수가 없음)
-> 통계를 내겠다는 말이 없음

갯수 구하는 함수

★ 사원 수 조회하기 COUNT(*) AS

사원 수 조회하기
(1) PK(NOT NULL이 확실한 칼럼)
SELECT COUNT(EMPLOYEE_ID) AS 전체사원수
FROM EMPLOYEES;
-> 모든 사원이 사원번호를 가지고 있어서 빠짐없이 가능
★(2) 모든 칼럼 -- 가장 많이 쓰임!COUNT() AS
SELECT COUNT() AS 전체사원수
FROM EMPLOYEES;
들어있는 값도, 비어있는 값도 있을수 있다
비어있는 값이 있어서 상관없이 모든 칼럼을 보기때문에 어느칼럼이라도 데이터를 가지고 있으면 값이 있는 것으로 봄

EMPLOYEES 테이블에서 DEPARTMENT_ID와 JOB_ID가 모두 같은 사원들을 그룹화하여 각 그룹의 사원수를 조회하시오. DEPARTMENT_ID가 NULL인 사원은 제외하시오.
SELECT DEPARTMENT_ID, JOB_ID
,COUNT(*) AS 사원수
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID , JOB_ID
ORDER BY 사원수;

DEPARTMENTS 테이블에서 LOCATION_ID로 그룹화하여 각 그룹의 부서수를 조회하시오. MANAGER_ID가 없는 지역은 제외하시오.

SELECT LOCATION_ID
, COUNT(*) AS 부서수
FROM DEPARTMENTS
WHERE MANAGER_ID IS NOT NULL
GROUP BY LOCATION_ID;

사원 테이블에서 동일한 부서번호를 가진 사원들을 그룹화하여 각 그룹별로 몇명의 사원이 있는지 조회하시오.

-> 같은 DEPARTMENT_ID를 하나로 모아서 실행하기 시작
-> 어떤 부서가 45이고 1명인지 알수 없음
-> "통계"를 내기 위함(목적)

-> 부서 번호 찍고 어떤 부서에 몇명인지 알 수 있음

사원번호? 사원번호는 찍을 수 없음 -> 애초에 문법이 틀림
GROUP BY절에서 지정한 칼럼만 조회할 수 있다.

-> SELECT 절에서 조회하려는 칼럼은 "반드시" GROUP BY 절에 명시되어 있어야 한다. (이해하기! )
-> (EX) 쇼핑몰 구현 - 월간 구매건수

사원 테이블에서 같은 직업을 가진 사원들을 그룹화화여 각 그룹별로 연봉의 평균이 얼마인지 조회하시오.

ROUND, 소수 2자리까지만 (소숫점이 있다면) , 원래 소수점이 없었음
GROUP BY 가 먼저(JOB_ID) -> SELECT JOB_ID 조회 가능 (순서)

사원 테이블에서 전화번호 앞 3자리가 같은 사원들을 그룹화하여 각 그룹별로 연봉의 합계가 얼마인지 조회하시오.

전화번호 3글자 - 문자함수

일부만 반환하기
SELECT SUBSTR(PHONE_NUBER, 1, 3)(전화번호 1번째 글자 ~ 3글자까지 반환하기)
, SUBSTR(PHONE_NUBER, 5)(전화번호 5번째 글자에서 끝까지 반환하기)

COUNT(*) OVER(PARTITION BY) : GROUP BY 절 없이 통계내기

-> 1번 문제와 똑같아짐
-> 50 45 똑같은 것 많이 나옴 : 중복제거(DISTINCT) (중복을 제거할 COLUMN이름 앞에 붙이기)

-> 소수점 많이 나옴 : ROUND 나 TRUC(절사) 씀

HAVING 절

[HAVING] : GROUP의 조건
VS WHERE 절 :원래는 조건 작성할때 쓰는 란

사원 테이블에서 각 부서별 사원수가 20명 이상인 부서를 조회하시오

-> 1번 쿼리랑 똑같음
-> 각 부서별 사원수를 (GROUP BY)로 처리해야 만 -> 20명 이상인지 알 수 있다(HAVING)

HAVING 절 : 들여쓰기 없이 쓰기

(GROUP BY 안써도 체크 가능 -> HAVING 안씀) -> WEHRE로 씀
부서별 사원수를 조회해야만 알 수 있는 조건이 아님
부서번호가 있는지 없는지 는 전에 알 수 있음

사원 테이블에서 각 부서별 직원수를 조회하시오. 단, 부서번호가 없는 직원은 제외하시오.

100명(모수 : Sampling) (모수를 줄이기: 성능이 더 좋음)
(HAVING절)
(EX) 100명 사원 있음 -> 4그룹으로 묶음 -> 특정그룹 제외 -> 나머지 3그룹으로 묶음

(WEHRE절)
(EX) 100명 사원 있음 -> 먼저 내보냄(70명 남음) -> 나머지 3그룹으로 묶음
-> 쿼리문 성능 우수(이유: GROUPING(모으는데)하는데 시간이 오래걸림, 어치피 버릴건데 모을 이유 없음 -> 미리 제거하기 위해서)

BUT

-> WHERE절 -> HAIVNG절로 바꿔도 동작은 된다

조건에서 사용되는 부서별사원수는 GROUP BY절이 필요하므로 HAVING절로 처리
조건에서 사용되는 부서번호는 GROUP BY절이 필요없으므로 WHERE절로 처리

SELECT문의 실행 순서
1. FROM 테이블
2. WHERE 조건
3. GROUP BY 그룹
4. HAVING 그룹조건
5. SELECT 칼럼(순서)
6. ORDER BY 정렬

테이블의 별명 확인 : 공백(한칸띄고)

공백 (한칸 띄고 E)(별명은 한글자만 주는건 아니다)
EMPLOYEES 테이블 있는 DEPARTMENT_ID이다

칼럼의 별명 확인 : AS

EPLOYEES 아이디에 DEPT가 없음
: 실행순서가 맞지 않음 (SELECT절의 별명은 GROUP BY에 쓸 수 없음)
(먼저 만들어졌으면 사용가능 만들어지지 않으면 사용할 수 없음)

부서별 사원수 HAVING절에서 실행할 수 없음
-> 실행순서 맞지않음

부서별 사원수 키워드 알 수 없음

-> 부서별로 먼저 정리(ORDER BY)

ORDER BY 사용가능
-> 실행순서 가능

-> GROUP BY 절 없이 통계내기
-> , ROUND(AVG(SALARY) OVER(PARITION BY DEPARMTENT_ID,2)

조인 : ★ 실무에서 많이 쓰임
★ 내부 조인써야하는지 외부 조인써야하는지 판단하기!

사원의 정보를 하나의 결과로 만들기 위해서 조인 결과를 씀
1: 다(PK : FK) 관계 테이블들을 조인할 수 있는 것이다. (관계있는 아이들)

★ (면접)내부 조인(Inner join) , 외부 조인(Outer join) 차이점을 말하시오

[ANSI기준으로 설명할 예정]

ANSI 구문 쓰기 : 호환성 위해서 다른 데서 돌리고 싶다면
ORACLE 구문 : 점유율이 높아서 공부해야함
-> 둘다 공부하기

-조인조건 잘 만들면 됨

필수키 : 회원이름으로 묶음 (1)
외래키 : 구내내역 (M)

내부 조인 : a,b라는 사람이 조회내용이 양쪽에 모두 있어야 함

외부 조인 : c라는 사람은 회원에만 있고 구매에는 없음
구매내역이 없는 사람도 포함해서 구매내역을 뽑아보자(외부조인)(가입을 했는데 사지 않음) -> 광고 타켓(회사입장에서 중요)

JOIN : (쓰고 싶은것이 2군데 있음)
사원번호,사원명, 부서번호 -> 사원TABLE
부서번호, 부서명 -> 부서TABLE
JOIN 의 조건 : ON

PK와 FK 연결되어 있는 2개 COLUMN

사원번호(E.EMPLYOEE_ID), 사원명(E.FIRST_NAME,E.LAST_NAME), 부서번호(D.DEPARTMENT_ID), 부서명(D.DEPARTMENT_NAME)을 조회하시오.

-> EMPLOYEE_ID 의 아이디랑 DEAPRTMENT의 DEPARTMENT_ID같음

-> 부서번호(DEPARTMENT_ID) 가 어느테이블에 있는지 알수 없음(ambiguously)

-> TABLE 이름에 별명 주기 (한칸 띄고 D)

-> 두개 테이블에 있는 칼럼이면 반드시 '주인'을 적어주기

-> D라고 적어도 E라고 적어도 차이 없음

★ 2개 TABLE에 모두 있는 COLUMN은 반드시 OWNER 명시하기

BUT

일반적으로 OWNER 명시를 함께 한다

사원번호, 사원명, 직업, 직업별 최대연봉, 직업별 최소연봉을 조회하시오.

-> 3가지 오너 명시가필요

-> J. E. 상관없음

-> 최대 연봉에 가까운지 최소 연봉에 가까운지 파악 가능

외부조인

-> 1번 조회시) 106명 (부서번호가 없는 사람이 조회가 안됨)
-> 양쪽에 다 있어야 조회가 되는것(내부조인)이라 나오지 않음

EMPLOYEES E(부서번호가 없는 사람이 이쪽에 있음)

-> ★ 방향을 맞춰서 적어야 함(왼쪽에 있는지 오른쪽에 있는지 보고!)

-> 마지막 빠진 사람 Kimbery (null) (null)

*방향 바꾸기

-> 유령부서 조회(사원이 아무도 근무하지 않음)
-> 방향으로 지정한 TALBE은 데이터 일치여부랑 상관없이 무조건 조회됨

★ 작성순서 1:M(일대다) 관계라면)) 1쪽을 '앞'에 M(다)쪽을 '뒤'에 두기
-> 데이터 양이 적은 쪽(앞) : 데이터 양이 많은 쪽(뒤)(일반적으로 1:M 순서도 같음)
(1:1 관계를 테이블로 쪼갤 이유가 없음-> 합치면 됨)
-> 성능에 도움이 됨
-> 필수는 아님

-'정규화' : 테이블을 왜 분리하는가? 왜 1:M(다)관계로 만드는가?
-> 테이블 위로 2,3개 관계짓는 거 어려우면) 필연적으로 나오는것 ) "테이블 하나로 합칠까?"
-> (테이블 정의서) 테이블이 하나인 시스템도 있음
-> 은행 DB는 하나의 테이블에 다 들어있음(복잡해서) (EX) 칼럼 80개 (SELECT하나라서 조회 잘됨)(불필요한 값NULL값 다 돈인데, 테이블이 안정되고 좋아서 그냥 쓰는경우 많음)
-> 일반적으로는 분리함.

-처음시작시) '개발'쪽으로 가는 것이 좋음
-만든걸 '유지,보수'하는 일은 없음(성능까지 잡아가면서 하는건 어려움, 기능 돌아가면 다음 기능해야함)
-3,6개월 자리 PROJECT 많음
-4개월차에 급구하는 이유? 버틴사람이 나감