[Database] MySQL -03 (실습 위주)

실습 03

1. 실습

use std01;
select count(*)
from employees;
;

select *
from employees
where 성별 = 1
;

select distinct 부서번호
from employees
;


-- 각 부서에 속한 사원 출력
select 부서번호, count(*) as cnt
from employees
group by 부서번호
;
-- 각 부서에 속한 사원들이 5명 이상인 부서번호를 출력
select 부서번호,count(*)
from employees
group by 부서번호
having count(*) >= 5
order by 2 desc
;

-- 키가 같은 정보만 출력되는 것
-- inner join

select *
from departments;

--
/*
select *
from A테이블 조인방식 B테이블
ON 결합키
*/
;
-- JOIN방법 1 ON 사용
SELECT COUNT(*)
FROM EMPLOYEES INNER JOIN DEPARTMENTS
ON EMPLOYEES.부서번호 = DEPARTMENTS.부서번호
;
-- JOIN 방법 2 USING 사용
SELECT *
FROM EMPLOYEES INNER JOIN DEPARTMENTS
USING(부서번호)
;

/*
각 부서에 속한 사원들 중에서 부서번호가 10, 30, 50, 70, 80에 해당하는
사원들의 부서이름별 사원수와 평균급여를 산출하는 SQL을 작성하시오.
*/

SELECT COUNT(*) as 사원수
	,avg(급여)
    ,DEPARTMENTS.부서번호
FROM EMPLOYEES INNER JOIN DEPARTMENTS
ON EMPLOYEES.부서번호 = DEPARTMENTS.부서번호
WHERE departments.부서번호 = 10 
	OR departments.부서번호 = 30 
	OR departments.부서번호 = 50 
	OR departments.부서번호 = 70 
	OR departments.부서번호 = 80
GROUP BY DEPARTMENTS.부서번호
ORDER BY COUNT(*) DESC
;


-- WHERE IN을 사용하면 다중 옵션을 간단히 작성할 수 있다. 정해진 옵션 말구 NULL값 조회할 때는 OR 컬럼 IS NULL 로 찾아내면된다.
SELECT *
FROM EMPLOYEES
WHERE 부서번호 IN (10,30,50,70,80)
OR 부서번호 IS NULL
;


SELECT B.부서번호
	, COUNT(1) AS 사원수
    , AVG(A.급여) AS 평균급여
FROM EMPLOYEES A inner join departments B
WHERE B.부서번호 IN (10,30,50,70,80)
GROUP BY B.부서명
;

-- LEFT OUTER JOIN
SELECT *
FROM employees LEFT OUTER JOIN departments
ON employees.부서번호 = departments.부서번호
;

SELECT *
FROM employees INNER JOIN departments
ON employees.부서번호 = departments.부서번호
;

SELECT *
FROM employees LEFT JOIN departments
ON employees.부서번호 = departments.부서번호
;

-- RIGHT OUTER JOIN
-- DEPARTMENTS 에다가 EMPLOYEES정보를 갖다 붙이는 거
SELECT *
FROM employees RIGHT OUTER JOIN departments
ON employees.부서번호 = departments.부서번호
;

-- concat 합치는 함수
SELECT 사원번호
	, CONCAT(성, 이름) AS 사원이름
FROM employees
;

SELECT A.사원번호
	, CONCAT(A.성,A.이름) AS 사원이름
    , concat(A.성,B.이름) AS 관리자이름
FROM employees A inner join employees B
on A.관리자ID = B.사원번호;
;

-- SELF JOIN
-- 각 관리자가 관리하는 사원들의 수를 구하는 SQL을 작성하시오
SELECT B.부서번호
	, CONCAT(B.성, B.이름) AS 관리자
    , COUNT(1) AS 사원수
FROM employees A LEFT OUTER JOIN employees B
ON A.관리자ID = B.사원번호
WHERE B.부서번호 IS NOT NULL
GROUP BY B.부서번호,CONCAT(B.성, B.이름)
;

-- 사원TB을 기준으로 부서명별 직업명별 사원수와, 평균급여를 산출하는데
-- 각 부서에 속한 사원수가 4명 이상인 대상들만 출력되는 SQL을 작성하시오.

select b.부서명
	,c.직업명
    ,count(1) as 사원수
    ,ROUND(AVG(a.급여)) as 평균급여
from employees a left outer join departments b
on a.부서번호 = b.부서번호
left outer join jobs c
on a.직업코드 = c.직업코드
group by b.부서명, c.직업명
having count(1) >= 4
;

SUBQUERY

-- SUBQUERY
-- SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY 골조
-- SUBQUERY는 SELECT( SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY) 처럼 SELECT안에 QUERY가 들어가는데 이런 QUERY를 SUBQUERY라고 한다.
-- SUBQUERY는 ALIAS로 지정
SELECT 사원이름
FROM (SELECT CONCAT(성,이름) AS 사원이름
			FROM employees
            ) A ;
            
-- 사원테이블의 평균급여보다 많은 급여를 받는 사원들의 정보를 출력하는 SQL을 작성하시오.
-- 1. 평균급여
SELECT AVG(급여) FROM employees;

-- 2. 평균급여보다 많은 ROW들을 출력
SELECT *
FROM EMPLOYEES
WHERE 급여 > 3647933.1776
;

SELECT *
FROM employees
WHERE 급여 > (SELECT AVG(급여) FROM employees)
;

• case when

-- case when >> if 문과 같다.
-- sytax >> case when ~ then ~ else ~ end
select *
from employees;

select 성별
	, case when 성별 = 1 then '남성'
			when 성별 = 2 then '여성' else '여성' end as 구분
from employees
;

-- employees tb을 기준으로 4개 테이블 전체 조회 할 수 있는 SQL 작성, join,join,join 3개
select a.*
from employees a left outer join departments b
on a.부서번호 = b.부서번호 
left outer join jobs c
on a.직업코드 = c.직업코드
left outer join locations d
on b.지역ID = d.지역ID
-- 국가코드가 KR을 가진 사원정보를 출력하시오 -> emp정
where d.국가코드 = 'KR'
;

-- 국가코드별 성별 사원수를 구하는데, 성별이 1인사원을 남성으로 2인사원을 여성으로 나타내는 sql을 작성하시오
select d.국가코드 
	, case when a.성별 = 1 then '남자' else '여자' 
    end as 성별
	,count(*) as 사원수
from employees a left outer join departments b
on a.부서번호 = b.부서번호 
left outer join jobs c
on a.직업코드 = c.직업코드
left outer join locations d
on b.지역ID = d.지역ID
group by d.국가코드 , 성별
;

-- 직업이 프로그래머인 사원들의 평균급여보다 많은 급여를 받는 사원들의
-- 부서이름별, 직업별, 사원수와, 평균급여를 산출하는 sql을 작성하시오.
;
set @v1 = '프로그래머';
select round(avg(a.급여))
from employees a left outer join jobs b
on a.직업코드 = b.직업코드
where b.직업명 = '프로그래머';

;
select b.부서명
	,c.직업명
    , count(*) as 사원수
    , avg(a.급여) as 평균급여
from employees a left outer join departments b
on a.부서번호 = b.부서번호
left outer join jobs c
on a.직업코드 = c.직업코드
where a.급여 > (select round(avg(a.급여))
from employees a left outer join jobs b
on a.직업코드 = b.직업코드
where b.직업명 = '프로그래머')
group by b.부서명
		,c.직업명
;

select (select avg(급여) from employees b where b.직업명 = '프로그래머') as 평균급여
from employees a inner join jobs b
on a.직업코드 = b.직업코드
;	

학습 키워드
-- select ~ order by 절
-- subquery
-- 사용자지정함수 --case
-- 조인

• Table handling

/*
create table 테이블명(
  컬럼 속성정의
);

create table test(
  col1 varchar(50)

);
*/

-- insert into test values('aaaaaaaaaaaaaaaaaaa')
/*
create table test2(
  col1 varchar(10)
)ENGINE = InnoDB default charset='utf8' 
;

;
insert into test2
select 이름
from employees
;
*/

/* table 삭제는
drop table 테이블명;
truncate table test; >> schema를 제외한 나머지를 다날린다.
delete from test;

데이터 넣기는
insert into table명 values('')

데이터 수정
update test
*/

/*
*/

/*
*/

/*
*/

/*
*/

2. 연습문제

• 사원테이블 기준으로 급여가 350만 ~ 450만 사이에 해당하는
  사원들의 부서이름별 사원수와 평균급여, 최대, 최소 급여를 출력하는
  SQL을 작성하시오

SELECT b.부서명
	,COUNT(1) as 사원수
    ,avg(a.급여) as 평균급여
    ,max(a.급여) as 최대급여
    ,min(a.급여) as 최소급여
FROM EMPLOYEES a 
left outer JOIN departments b
ON a.부서번호 = b.부서번호
where 급여 between 3500000 and 4000000
group by b.부서명
;