📚 3. sql에서 group by, having, join

📖 A. group by, having

📝 시작하기전, group by와 having 절

select [distinct] [컬럼1,컬럼2,.....][as 별명][ || 연산자][*]  --- 6
from 테이블명     --- 1
 [where 조건절]    --- 2
 [group by컬럼명]  --- 3
  [having 조건절]   --- 4
  [order by 컬럼명 asc|desc ]  --- 5

• group by : 그룹함수(max,min,sum,avg,count..)와 같이 사용
• having : 묶어놓은 그룹의 조건절

💡 참고

• 업무ID : job id
• 부서ID : department_id
• 사원명 : last_name

 

✔️ 평균 구하기

사원급여평균

• format : 소수점 표시, 천단위 콤마 찍기, 날짜 문자열 변환
• floor : 소수점 첫째 자리에서 버림하는 함수

select format(avg(salary),0) as "사원급여평균" from employees;

 

✔️ 부서별로 급여 평균을 구해서 부서 ID, 급여 평균을 출력하시오.

select department_id as "부서ID", format(avg(salary),0)as "급여평균"
from employees
group by department_id
order by department_id desc;

• 부서를 그룹 지정한다.
• 부서를 내림차순 정렬한다.

 

✔️ 업무ID별 급여의 합계를 구해서 업무ID, 급여합계를 출력

select job_id as "업무ID", format(sum(salary),0) as "급여합계"
from employees
group by job_id
order by job_id;

 

✔️ 부서별 급여평균을 구해서 평균급여가 6000이상인 부서만 출력 (8건)

• (평균급여는 소수점 이하 절삭)

--      부서ID   평균급여
--     -----------------------
--        NULL    7000
--        20      9500

select department_id as "부서ID", floor(avg(salary)) as "평균급여"
from employees
group by department_id
having avg(salary) >= 6000;

 

✔️ 부서별 급여평균을 구하시오 (9건)

• 조건1) 소수이하는 반올림
• 조건2) 세자리마다콤마, 화페단위 ￦를 표시
• 조건3) 부서코드 평균급여

--            NULL    ￦7,000
--            20      ￦9,500

• 조건4) 부서별로 오름차순정렬하시오
• 조건5) 평균급여가 5000이상인 부서만 표시하시오

select department_id as "부서코드", concat('₩',format(round(avg(salary),0),0)) as "평균급여"
from employees
group by department_id
having avg(salary) >= 5000
order by department_id;

 

✔️ 10과 20 부서에서 최대급여를 받는사람의 급여를 구하시오, 부서별로 오름차순 정렬하시오

-- department_id     max_salary
-----------------------------
-- 10                    4400
-- 20                    13000 

select department_id, round(max(salary),0) as "max_salary"
from employees
where department_id in (10,20)
group by department_id
order by 2;

having절 (where + group by + having)

• 10과 20 부서에서 최대급여를 받는사람의 급여를 구하시오. --1건
• 부서별로 오름차순 정렬하시오
• 최대급여가 5000이상인 부서만 출력하시오

--         department_id     max_salary
--         -----------------------------------
--           20                    13000 

 

select department_id, round(max(salary), 0)
from employees
where department_id in (10, 20)
group by department_id
having max(salary) >= 5000
order by department_id;

 

 

📖 B. join

📝 시작하기전, join절

• 0. natural join(자연조인) : 같은컬럼이 여러개 있을때 같은 컬럼 모두를 and연산해서 연결
• 1. Inner join(내부조인) : 같은것 끼리만 연결
• 2. Outer join(외부조인) : 한쪽을 기준(모두포함)해서 연결
  • left join : 왼쪽컬럼 모두포함
  • right join : 오른쪽컬럼 모두포함
• self join : 자기자신 테이블과 연결
• cross join : 모든 경우의 수로 연결
• non equijoin : 범위에 속하는지 여부를 확인
• n개 테이블 조인 : 여러개의 테이블 조인

💡 참고
1. MySQL구문
2. Ansi표준구문

 

✔️ 사원테이블과 부서테이블에서 부서가 같을경우 사원번호,부서번호,부서이름을 출력

mysql 구문

select employee_id, e.department_id, department_name
from employees e, departments d
where e.department_id = d.department_id;

 

✔️ 부서테이블과 위치테이블을 연결하여 부서가 위치한 도시를 알아내시오(inner join)

select d.department_id, l.city 
from departments d, locations l
where d.location_id = l.location_id;

 

✔️ 사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 ==> 107건

• 조건 1) 사원이름, 부서ID, 부서이름을 출력하시오
• 조건 2) 사원테이블의 모든 사원을 포함하시오

select last_name, department_id, department_name
from employees 
left join departments using(department_id);

 

✔️ 사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오

• 조건 1) 사원이름, 부서ID, 부서이름
• 조건 2) 부서테이블의 모든 부서를 포함

select last_name, department_id, department_name
from employees
right join departments using(department_id);

 

✔️ departments 와 locations 자연조인의 비교(같은컬럼 : location_id), 두개의 테이블을 연결해서 부서위치(location_id), 도시(city), 부서이름(department_name)을 출력하시오

natural join

select location_id, city, department_name
from departments
natural join locations;

inner join

select location_id, city, department_name
from departments
inner join locations using(location_id);

 

✔️ 부서ID와 매니저ID가 같은 사원을 연결하시오

• last_name, department_id, manager_id

select last_name, department_id, manager_id
from employees e
inner join departments d using(department_id, manager_id);

select last_name, department_id, manager_id
from employees
natural join departments;

 

✔️ 사원과 관리자를 연결하시오. 모든 사원을 표시

• 사원번호 사원이름 관리자
• 101 Kochhar King

select e.employee_id as "사원번호", e.last_name as "사원이름", m.last_name as "관리자"
from employees e, employees m
where e.manager_id = m.manager_id