#0915 데이터베이스 뿌시기 5일차! ( SQL, 그룹함수 , NULL , SQL 실행 우선순위 )

< 데이터베이스 5일차 내용 >

 

---

◆ 데이터베이스 기본

개념정리

#0915 데이터베이스 5일차
#0915 데이터베이스 5일차
◆ 그룹함수에서 NULL

● 그룹함수들은 NULL값을 무시함 ー＞값이 아니니 인지를 못함 ★ ● NVL(컬럼명 , 넣을 값) => 이걸로 중첩함수 해서 그룹함수 해야함 ● avg(nvl(commission_pct,0) 이렇게 하면됨 ● 다음 코드를 실행하면 오류가 생김 => 급여를 어느 기준으로 합하는지가 없기때문임.

=> 그룹화가 되어있지않다고 되어있음. 그룹함수이기 때문에 그룹화를 해주어야 그룹별로 결과를 받을 수 있음
● 그룹 함수에 없는 열은 모두 그룹 바이 절에 포함되어야함.
=> 급여는 그룹함수인 sum이 사용되었지만 department_id는 그룹함수가 사용되지않음. 따라서, 사용되지않은 department_id는 group by department_id를 해주어야함.

select department_id , sum(salary) from employees group by department_id

Q. 사원들의 보너스 평균을 구하시오.

select avg(nvl(commission_pct,0)) , sum(commission_pct)/107 from employees

=> 실제로는 그룹바이절에 있는 것들이 select절에 없는 경우도 많음. 차라서 잘 봐야함

● 여러개의 컬럼에 대해 그룹화를 하면 첫 번째 컬럼에 대한 대 그룹이 만들어지고 뒤 따라오는 컬럼명이 소그룹이 만들어져 그룹화가 이루어짐. ex) group by department_id , job_id 가 있으면 부서번호로 큰 그룹을 만들고 그 안에 직업명으로 소그룹으로 그룹화됨

Q. 사원테이블에서 부서별 , 직급별로 총 합을 구하세요.

select department_id dept_id , job_id , sum(salary) from employees group by department_id , job_id order by department_id

< where절에 그룹함수 실행 시 에러 >

select department_id , AVG(salary) from employees where AVG(salary) > 8000 group by department_id

=> where절은 행에 대한 조건을 주는거지 그룹에 대한 조건을 주는 것이 아님
=> 따라서, 그룹에 대한 조건을 주려면 having절을 주면됨 having -> where절이 된 것

select department_id , avg(salary) from employees group by department_id having avg(salary) > 8000

< SQL 실행 우선순위 >

from 절 where절 group by절 having 절 selected 절 order by 절

# SELECT절 총 집합 예제

select job_id , sum(salary) payroll from employees where job_id not like '%REP%' group by job_id having sum(salary) > 13000 order by sum(salary)

5. select job_id , sum(salary) payroll 1. from employees 2. where job_id not like '%REP%' 3. group by job_id 4. having sum(salary) > 13000 6. order by sum(salary) => 순서대로 해석해보면, 사원테이블에서(1) 직업에 REP로 끝나는 직업은 제외하고(2) 직업별로 그룹화를 하는데(3) 직업 별 급여의 합이 13000 이상인 직업을 가진 직업명 , 급여합을 출력하고(5) 급여의 합을 기준으로 정렬하시오(6)

# 그룹함수 에러이슈

select department_id , max(avg(salary)) from employees group by department_id

=> 위 코드를 실행하면 에러가 남. avg는 하나인데 max는 다수의 개수를 출력하라했으므로 에러가 날 수 밖에없음.


#그룹함수 관련 실습 예제
01. 회사 전체의 최대 급여, 최소 급여 , 급여 총 합 및 평균 급여를 출력하시오.

select max(salary) , min(salary), sum(salary) , avg(salary) from employees

02. 각 직업별 , 최대 급여 , 최소 급여 , 급여 총 합 및 평균 급여를 출력하시오.
(단, 최대 급여는 MAX , 최소 급여는 MIN , 급여의 총 합은 SUM , 평균 급여는 AVG로 출력하고 직업을 오름차순으로 정렬하시오. )

select job_id , max(salary) as max, min(salary) as min , sum(salary) as sum, avg(salary) as avg from employees group by job_id order by job_id

03. 100번 부서를 제외한 각 부서별 평균급여가 7000이상인 부서를 출력하시오.

select department_id , avg(salary) as avg from employees where department_id != 100 group by department_id having avg(salary) >7000 order by department_id

=> NOT IN(100)을 해도 되지만 얘는 값이 두 개이상일때가 좋은거지 아니면 좋지않음.

# 양식 1

• 필요할 때 언제든 활용가능하도록 데이터를 저장하는 공간
• 무결성이 보장된 데이터를 모아놓은 집합(저장소)
• 데이터 정보는 그대로 사용하는 것이 아니라 가공을 해야함
• 데이터를 가공할 시에는 데이터의 정확성이 매우 중요함 => 떨어진 정확성 - 잘못된 정보로 이어질 확률 Up!

04. 50번 부서에 근무하는 매니저별 평균급여를 출력하시오.

select manager_id , avg(salary) from employees where department_id = 50 group by manager_id order by manager_id

05. 동일한 직업을 가진 사원들의 총 수를 출력하시오.

select job_id, count(employee_id) from employees group by job_id

06. 직원이 4명 이상인 부서의 부서번호와 인원을 출력하시오.

select department_id , count(employee_id) from employees group by department_id having count(employee_id) >= 4 order by department_id

07. 매니저로 근무하는 사원들의 총 수를 출력하시오.

select count(distinct manager_id) as mgr_count from employees

08. 사내의 최대 급여 및 최소 급여의 차이를 출력하시오.

select max(salary) - min(salary) from employees

09. 매니저의 사번 및 그 매니저 밑 사원들 중 최소 급여를 받는 사원의 급여를 출력하시오.
( 1. 매니저가 없는 사원들은 제외한다. 2. 최소 급여가 5000 미만인 경우는 제외한다
3. 급여 기준 역순으로 조회한다. )

select manager_id , min(salary) from employees where manager_id is not null group by manager_id having min(salary) >= 5000 order by min(salary) desc

=> order by절의 주의사항은 select절에 존재하는 것을 기준으로 정렬이 되는 것이므로 그냥 salary desc 하면 에러가 생김. 따라서, select 절에 존재하는 min(salary)를 기준으로 정렬함