-- 1. equijoin
-- inner join
SELECT *
FROM STUDENT.S_EMP, STUDENT.S_DEPT; -- 275개 생성. for문처럼 개수 내려옴.
where -- 을 써야지 필요한 경우의 내용만 추려서 나온다.
;
-- 직원(s.emp)테이블과 부서(s.dept)테이블을 join하여 사원의 이름,부서,부서명 나타내시오.
SELECT se.NAME , sd.NAME
FROM STUDENT.S_EMP se , student.S_DEPT sd
WHERE se.DEPT_ID =sd.ID ;
-- 서울 지역(sregin.name)에 근무하는 사원에 대해
-- 사원의 이름(semp.name)과 근무하는 부서명(sdep.name) 출력
SELECT sr.NAME , se.title, se.NAME , sd.NAME
FROM STUDENT.S_EMP se ,student.S_DEPT sd , STUDENT.S_REGION sr
WHERE
se.DEPT_ID = sd.ID And sd.REGION_ID =sr.ID
And sr.NAME = '서울특별시'
AND se.TITLE ='사원'
;
-- 2. non-equijoin
-- between
-- 각 사원에 대하여 급여와 급여 동급이 무엇인지 알고 싶을 경우
SELECT se.NAME , se.SALARY , s.GRADE
FROM STUDENT.S_EMP se , STUDENT.SALGRADE s
WHERE se.SALARY BETWEEN s.LOSAL AND s.HISAL
;
-- 3. outer join
-- inner join 반대 의미로, 부족한 부분을 채워준다.
--
SELECT se.NAME , se.ID , sc.NAME
FROM STUDENT.S_EMP se ,STUDENT.S_CUSTOMER sc
WHERE se.ID (+) = sc.SALES_REP_ID -- 데이터 양이 부족한 부분에 (+) 작성
ORDER BY se.ID
;
-- 4. self join
-- 자기 자신, 계층 구조 때 사용 - 대중소 비교, 상사-부하 관
-- 각 직원의 이름(semp.name)과 상사의 이름(semp.manerger id)을 출력
SELECT se.NAME , se.MANAGER_ID , se2.NAME
FROM STUDENT.S_EMP se , STUDENT.S_EMP se2
WHERE se.MANAGER_ID = se2.ID
;
-- 김정미와 같은 직책(se1.title)을 가지는 사원(se.title)의
-- 이름(se.name), 직책(se.title) 급여(se.salary), 부서번호(se.dept)
SELECT se.NAME ,se.DEPT_ID , se1.name, se1.TITLE ,se1.SALARY , SE1.DEPT_ID
FROM STUDENT.S_EMP se , STUDENT.S_EMP se1
WHERE se.NAME = '김정미' and se1.TITLE = '사원' and se.DEPT_ID = se1.DEPT_ID
;
-- set 연산자
-- union(합), intersect(교), unionAll(합), MINUS(차)
-- 첫번째 select 구문에서 기술된 칼럼들과, 2번째 select 기술된 칼럼들은 개수와 타입이 일치해야함
-- 규칙을 지키려면 select 다음에 나오는 column명 순서를 일치 시켜주면 된다.
SELECT NAME , DEPT_ID , TITLE
from STUDENT.S_EMP se
WHERE DEPT_ID = 110
UNION
SELECT NAME , DEPT_ID , TITLE
from STUDENT.S_EMP se
WHERE DEPT_ID = 113
;
/* 7. subQUERY */
--subQUERY -> main query 순서
-- 4가지 형태
-- 괄호롤 묶여있음
-- 연산자 오른쪽에 존재
--사용 가능한 연산자 종류
-- 단일행 연산자: < = <= >= <>
-- 복수행 연산자 IN, NOT IN
--1. single row
-- 김정미와 같은 직책(se1.title)을 가지는 사람의
-- 이름(se.name), 직책(se.title) 급여(se.salary), 부서번호(se.dept)
SELECT se.NAME , se.TITLE , se.DEPT_ID
FROM STUDENT.S_EMP se
WHERE DEPT_ID = ( SELECT DEPT_ID
FROM STUDENT.S_EMP
WHERE NAME = '김정미')
;
--2. multi row subQUERY
-- 여러 행이 전달 된 경우
SELECT se.NAME , se.DEPT_ID
FROM STUDENT.S_EMP se
WHERE DEPT_ID IN
(SELECT sd.ID
FROM STUDENT.S_DEPT sd
WHERE sd.REGION_ID = 3
)
;
--3. multi column subQUERY <중요>
-- 여러 종류의 column을 비교하는 경우
-- group 만 하면 누구인지 이름을 알 수 없다.
SELECT se.NAME , se.DEPT_ID, se.SALARY
FROM STUDENT.S_EMP se
WHERE (SALARY, DEPT_ID) IN -- 2개를 묶어서 물어볼수도 있다. ()로 묶음
(SELECT MIN(SALARY), DEPT_ID
FROM STUDENT.S_EMP
group by DEPT_ID
)
;
-- 4. from 절에서의 subQUERY
SELECT e.name, e.title, d.name , e.dept_id, d.id
FROM (
SELECT name, title, dept_id
from STUDENT.S_EMP
WHERE TITLE = '사원'
) e, -- from 일 때는 이름 명시
student.S_DEPT d
WHERE e.dept_id = d.id
;
-- 5. having 절에서의 subQUERY
SELECT DEPT_ID , AVG(SALARY)
FROM STUDENT.S_EMP
group by DEPT_ID
HAVING AVG(SALARY) > (SELECT AVG(SALARY)
from STUDENT.S_EMP
WHERE DEPT_ID =113
)
;
-- 심화
-- gruop by로 이미 새로 만든 table은 dept_id , avg(salary)를 column명 으로 갖고 있다.
-- min을 하게 되면 해당 min 값이 dept_id row는 여러개고 min은 column값들을 합한 값이기 때문에 출력할 수 없다.
SELECT min(AVG(SALARY))
FROM STUDENT.S_EMP
group by dept_id
;
-- 이해를 위한 기본 SELECT 그룹함수 내용
SELECT sum(salary)
from STUDENT.S_EMP
;
-- 가장 적은 평균급여를 받는 직책에 대해 그 직책과 평균 급여를 나타내시오
SELECT DEPT_ID, avg(SALARY)
from STUDENT.S_EMP
group by DEPT_ID
HAVING AVG(SALARY) = (SELECT Min(AVG(SALARY))
FROM STUDENT.S_EMP
group by dept_id)
;
-- 6.create 절에서의 subQUERY
CREATE table emp_113(id, name, mailid, start_date) as
select id, name, mailid, start_date
from STUDENT.S_EMP
WHERE DEPT_ID = 113;
--7. dml문에서의 subQUERY
-- INSERT
INSERT INTO emp_113
(id, name, mailid, start_date)
select id, name, mailid, start_date
from STUDENT.S_EMP
WHERE start_date="16/01/01";
--update
UPDATE INTO STUDENT.S_EMP
set dept_id(
select dept_id
from STUDENT.S_EMP
where title="사장"
)
WHERE name="안창환"
;