SQL 정리 - Group by / having

오늘의 SQL 수업

like Amazon Web Services

• 한글자 '_'

select * from s_emp
where name like '박_'; // '박근수' 검색 불가, '박__'으로 해야 가능

• 여러글자 '*'

select * from s_emp
where name like '박*'; // '박근수' 검색가능

Note DUAL 은 가상 테이블

문자 출력

select chr(65) from dual; // 영어 'A'
select nchr(44032) from dual; // 한글 '가'

날짜 출력

SELECT SYSDATE FROM DUAL; // 오늘 날짜

말일 구하기

SELECT TO_DATE('19/05/01')-1 FROM DUAL; // 익월에서 하루 빼면

월 반올림

SELECT ROUND(SYSDATE,'MONTH') FROM DUAL;

(오늘 4/16일 때)

SELECT ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'),1)-1 FROM DUAL;

TRUNC - 버림 4/1
ADD_MONTHS - 한달 더하기 5/1
-1 하면 - 4월 마지막날 4/30

결과 4/30

날짜-> 문자

SELECT TO_CHAR(SYSDATE, 'yyyy/MM/dd') FROM DUAL;

update시 select 먼저!!!

퀴즈 ) 박구곤이라는 사람의 mailid를 null로 변경하시오

--update s_emp set mailid = null
select * from s_emp;
where name='박구곤';

count(*) vs count(컬럼)시 - null값 고려

select count(*) from s_emp; --e_emp 테이블의 행의 개수 : 25
select count(mailid) from s_emp; --s_emp 테이블의 mailid컬럼의 값의 개수 : 24 // null 값은 카운팅 되지 않음

GROUP BY - 부서별로 그룹화

직책이 사원인 사람들의 평균연봉을 부서별로 구하고 연봉이 높은 순으로 정렬합니다!!!!!!!

select dept_id, AVG(salary) -- 1. 평균연봉을 구한다
from s_emp              
where title = '사원'      -- 2. 사원인 사람들만
group by dept_id         -- 3. 부서별로 구한다(dept_id)
order by 2 desc;         -- 4. 두번째 컬럼AVG(salary)로 내림차순 정렬!

sql 할때 꼭 한두개 정도 평균 확인 해 볼 필요가 있다!
계산기 ㅎㅎㅎ 해보기 크으으으으
쿼리 결과랑 비교해서 값이 맞는지 확인!

Q. 각 부서별 평균 급여를 계산해서 보여주시오

select dept_id, trunc(AVG(salary),2) --1. 평균급여를 구한다.
from s_emp
group by dept_id  --2. 부서별로 구한다
order by 2 desc
;

Q. 각 부서별로 직책이 사원인 직원들의 평균 급여를 계산해서 보여주시오

select dept_id, avg(salary) from s_emp
where title = '사원'
group by dept_id
order by 1;

그룹화 한 경우에는 그룹함수와 group by 에 지정된 컬럼만 사용가능

select dept_id, avg(salary) from s_emp
group by dept_id;

Q. 각 지역 별(region_id)로 몇개의 부서가 있는지를 나타내시오

select region_id, count(name) from s_dept  -- 1. 몇개부서인지count(name) 
group by region_id
order by region_id;  -- 2. 지역별 그룹화

Q. 각 부서별(dept_id)로 평균 급여(avg(salary))를 구하되 평균 급여가 2000 이상(avg(salary)>=2000)인 부서만 나타내시오

select dept_id,avg(salary) from s_emp
group by dept_id
having avg(salary) >= 2000 -- 그룹함수 조건은 where 절이 아닌 having에 넣는다
;

직책을 두개 이상 가지고 있는 그룹 평균 연봉을 보여줌

select title, avg(salary), count(*) -- 직책 / 평균연봉 / 직책 수 를
from s_emp                              
group by title                      -- 직책별로
having count(*) > 2;                -- 직책의 개수가 2 초과 하면 출력

Q. 각 직책별(title)로 급여의 총합(sum(salary))을 구하되 직책이 부장인 사람은 제외하시오,

-- 단, 급여 총합이 8000만원 이상인 직책만 나타내며, 급여 총합에 대한 오름차순으로 정렬하시오

select title, sum(salary) from s_emp--1. 급여의 총합 - (sum(salary))
where title not like '%부장' -- 부장인 사람은 제외
group by title--2. 직책별로 - (title)
having sum(salary) >= 8000--3. 급여 총합이 8000만원 이상
order by 2 asc;--4. 오름차순

Q. 각 부서별로 직책이 사원인 직원들에 대해서만 평균 급여를 구하시오

select dept_id, avg(salary) from s_emp--1. 평균급여 출력
where title='사원' --3. 직책이 사원
group by dept_id--2. 부서별
;

Q. 각 부서내에서(dept_id) 각 직책별(title)로 몇명의 인원(count(*))이 있는지 나타내시오

select title, count(*) from s_emp--1. 몇명있는지 출력
group by title--3. 직책별로 그룹화
order by title
;

Q. 각 부서내(dept_id)에서 몇명(count(*))의 직원이 근무하는지를 나타내시오

select dept_id, count(*) from s_emp--1. 몇명있는지 출력
group by dept_id--2. 부서별로
;

Q. 각 부서별(dept_id)로 급여의 최소값(min(salary))과 최대값(max(salary))을 나타내시오

-- 단, 최소값과 최대값이 같은 부서((min(salary))=(max(salary))는 출력하지 마시오.

select dept_id, min(salary), max(salary) from s_emp --1. 급여의 최소값과 최대값
group by dept_id--2. 부서별로
--having min(salary) not like max(salary)--3. 부서별로 최소값과 최대값이 같으면 출력 안함
having min(salary) <> max(salary)--3. 부서별로 최소값과 최대값이 같으면 출력 안함
order by dept_id;