[6일차] 쿼리 작성

use scott_db; 

show tables;

desc scott_emp;

select *
from scott_emp;

select ename, job, sal
from scott_emp;

select ename, job, sal
from scott_emp
where sal >= 1200; # sal 1200이상 조건

select ename,job,sal
from scott_emp
where job != 'CLERK'; # 데이터는 대문자로 쓰자

select distinct job # 중복제거
from scott_emp;

select ename,job,sal
from scott_emp
where job = 'CLERK';

select ename,job,sal
from scott_emp
where job <> 'CLERK'; # != 과 동일

select ename,job,sal
from scott_emp
where job in ('CLERK','SALESMAN'); 
# 직업이 CLERK 이거나 SALESMAN인 경우 or 과 동일

select ename,job,sal
from scott_emp
where job ='CLERK'
or job = 'SALESMAN';

select ename, job, hiredate
from scott_emp
where hiredate >= '1981-01-01' 
and hiredate <= '1981-07-31'; # 사이에 있는 값

select ename, job, hiredate
from scott_emp
where hiredate between '1981-01-01' and '1981-07-31'; 
# 조금 더 간단

select *
from scott_emp
where ename like 'S%'; # 이름이 S로 시작하는

select *
from scott_emp
where ename like '%R%'; # 이름에 R이 들어가는

select *
from scott_emp
where ename like '_A%'; # 이름 두번째 글자가 A

select ename, (sal + 200) * 12 as "가상 연봉"
from scott_emp; # 임금 1년 치 계산 
# 알리아스 Alias 사용 as 연봉 이렇게
# 알리아스 별칭만 "" 더블

select *
from scott_emp
where comm is not null; #comm이 null값이 아닌 걸 보여달라

select *
from scott_emp
where comm is null; #comm이 null인 경우를 보여달라


#order by 사용 마지막에 온다.
select empno, ename, job
from scott_emp
order by ename asc; # 이름순으로 정렬 (오름차순) -> asc 생략가능

#Order by 마지막에 온다!
#이름순으로 정렬 (내림차순) -> desc 써주기
#order by 동일한 이름으로 오는 경우 여러 컬럼으로 가능
select empno, ename, job
from scott_emp
order by ename desc;

select empno, ename, job
from scott_emp
order by 2 desc; # 2번째인 ename으로 정렬하라
# 그런데 추천하지 않음.. 쓰지말 것

select empno, ename, job
from scott_emp
order by empno desc; # 사원번호 역순으로 추출

select ename, sal, ifnull(comm, 0) #내장 함수 ifnull 반환할 때 다른 값으로 출력할 수 있는 함수
from  scott_emp; # comm가 null이면 null대신 0으로 찍어라 as commintion

select sum(sal) #월급의 합계
from scott_emp;

select avg(sal) #월급의 평균
from scott_emp;

# CASE문 자체가 하나의 덩어리
# Case문 as 생략 가능
# Job 'CLERK' 에 10% 곱하기, MANAGER에 15% 곱하기 PRESIDENT에 20% 곱하기 나머지는 그대로 출력
# CASE~END 까지 케이스문
# END 뒤에 AS 생략
# REVISE_SALAR로
SELECT ename, job, sal,
CASE job WHEN 'CLERK' THEN 1.10*sal
WHEN 'MANAGER' THEN 1.15*sal
        WHEN 'PRESIDENT' THEN 1.20*sal
        ELSE sal
        END REVISE_SALARY
FROM scott_emp;


# <<<<<<<JOIN>>>>>>
#관계형 모델에서는 데이터의 일관성이나 효율을 위하여
#데이터의 중복을 최소화 (정규화)
#정규화된 테이블로부터 결합된 형태의 정보를 추출할 필요가 있음.
#FK를 이용하여 참조
#모든 데이터를 1:1로 연결하는 join 방법

# Cross Join(cartesian Product) : 모든 가능한 쌍이 나타남
# Inner-join : 조건을 만족하는 튜플만 나타남
# Outer join : 조건을 만족하지 않는 튜플(짝이 없는 튜플)도 null과
# 함께 나타남
# Theta join : 조건(theta)에 의한 조인
# Equi-join : Theta join & 조건이 Equal(=)
# natural join : Equi-join & 동일한 칼럼명이 합쳐짐
# self Join : 자기 자신과 조인

select *
from scott_dept;

# 두 테이블 join
# inner join 이면서 equi join
select ename, job, scott_dept.deptno, dname
from scott_emp join scott_dept
on scott_emp.deptno = scott_dept.deptno;

# AS 생략 가능 EP와 DT로 줄이기
# Join 시 on으로 제약 걸기
select ename, job, EP.deptno, dname
from scott_emp EP join scott_dept DT
on EP.deptno = DT.deptno
where job != 'CLERK';

# 아래는 옛날 방식 위가 더 표준
select ename, job, EP.deptno, dname
from scott_emp EP, scott_dept DT
where EP.deptno = DT.deptno
and job != 'CLERK';

# CROSS JOIN 모든 조합을 다 묶기! 모든 쌍이 나타남
select ename, job, EP.deptno, dname
from scott_emp EP cross join scott_dept DT;

# CROSS JOIN 아래는 옛날 방식 위가 더 표준
select ename, job, EP.deptno, dname
from scott_emp EP, scott_dept DT;

# Inner join 이면서 theta join
select E.ename, E.sal, S.grade
from scott_emp E join scott_salgrade S
on E.sal between S.losal and S.hisal; 
#join해서 on을 조건절로 붙임

select E.empno, E.ename, D.dname, D.loc
from scott_emp E join scott_dept D
on E.deptno = D.deptno; # Innerjoin Join 앞에 inner join 생략

select distinct deptno
from scott_emp; # 40번 없음 scott_emp

select E.empno, E.ename, D.dname, D.loc
from scott_emp E right join scott_dept D # Right outer join인데 Outer 생략가능
on E.deptno = D.deptno;

# 셀프조인 
# 사번, 이름, 매니저 사번, 매니저 이름
# mgr = empno 사장만 mgr null.. 13개 출력한다!
select E1.empno, E1.ename, E2.empno, E2.ename
from scott_emp E1 join scott_emp E2
on E1.mgr = E2.empno
order by E1.empno;

# count(*) *는 전체 개수
select count(*)
from scott_emp;

# 월급의 최대를 찍어라
select max(sal)
from scott_emp;

# 월급의 최소를 찍어라
select min(sal)
from scott_emp;

# 월급의 평균을 찍어라
select avg(sal) as "평균급여"
from scott_emp;

# 부서별로 평균 급여구하기
select deptno, avg(sal)
from scott_emp 
group by deptno;

# 부서별로 평균 급여구하기
# 부서 번호, 부서 이름, 평균(sal)
select E.deptno, dname, avg(sal), sum(sal)
from scott_emp E join scott_dept D
# where avg(sal) >= 2000 group by를 지나야 그룹핑이 되는데
# 그전에 쓰면 안된다.
on E.deptno = D.deptno
group by E.deptno, dname;

# 부서 번호, 부서 이름, 평균(sal)
select E.deptno, dname, avg(sal), sum(sal)
from scott_emp E join scott_dept D
on E.deptno = D.deptno
group by deptno # 부서별로 묶기 
order by sum(sal) desc; #sum(sal) 내림차순

# 부서 번호, 부서 이름, 평균(sal)
select E.deptno, dname, avg(sal), sum(sal)
from scott_emp E join scott_dept D
on E.deptno = D.deptno
group by E.deptno, dname
having avg(sal) > 2000 
# 먼저 부서별로 평균계산을 해서 부서별 평균이 2000이상인 자료 출력
# having 으로 조건 달기 where을 쓸 수 없으니 having 으로 대신 쓴다.
order by sum(sal) desc;  #sum(sal) 오름차순

# <<< 서브 쿼리>>>
# ename = 'SCOTT' 보다 월급이 많은 직원 정보 추출
select ename, sal
from scott_emp 
where sal > (select sal
from scott_emp
where ename = 'SCOTT');

# 서브쿼리 아무때나 들어가도 된다.
select ename, sal, (select sal
from scott_emp
where ename = 'SCOTT') as scott_sal
from scott_emp;

# 이름의 철자 순 (asc) 으로 정렬할 경우, 맨 앞에 오는 사람의
# ename, sal, deptno를 출력하세요
# 알파벳 순으로 해달라.. Min(ename) 1번째 A가 나오게 
# Orderby 사용하지 않고

#1.
select ename, sal, deptno 
from scott_emp 
where ename = (select min(ename)
from scott_emp);

#1-1. 첫번째 값 출력
select min(ename)
from scott_emp;

#2. 전체 평균 급여보다 적게 받는 직원의 이름과 급여를 출력
select ename, sal
from scott_emp 
where sal < (select avg(sal)
from scott_emp);

#2-2. 평균과 같이 출력
select ename, sal, A.avg_sal
from scott_emp join (select avg(sal) as avg_sal from scott_emp) A
where sal < A.avg_sal;

#3. JOIN 대신에 서브쿼리를 이용해 SALES 부서 deptno가 30인 사람 
# ename, deptno 알기
select ename, deptno
from scott_emp
where deptno = (select deptno
from scott_dept
where dname = 'SALES');

#4. 각 부서의 이름 첫 번째인 사람의 ename, sal, deptno
# 서브쿼리 자체가 3줄 .. -> ERROR -> = 대신 in으로 고치기
select ename, sal, deptno
from scott_emp
where ename in (select min(ename)
   from scott_emp
   group by deptno);

#4-1. 각 부서의 이름 첫번째인 사람 출력 
select min(ename)
from scott_emp
group by deptno;

#5. 해당 부서의 평균 급여보다 많이 받고 있는 사람
# 서브 쿼리 자체가 밖에 있는 쿼리 참조
select ename, sal, deptno 
from scott_emp E1 
where sal > (select avg(sal)
from scott_emp
where deptno = E1.deptno);

#5-1. 해당 부서의 평균 급여보다 많이 받고 있는 사람
select avg(sal)
from scott_emp
group by deptno;
use scott_db;