환경설정 -> SQLDEVELOPER(ORACLE)/MAC
LIST
- SUBGROUP
- JOIN(+ SET연산자)
- SUBQUERY
1.SUBGROUP
Group By , Having
Group by : 데이터 그룹으로 묶어서 처리하기 위해서 사용
Having -> group에 대한 조건 서술
where -> Group 이 아닌 다른 곳에 대해서 서술
-- Group By && Having 문 순서 구조
select 칼럼명, group 함수
from 테이블 명
group by 칼럼명 -- 전체 데이터를 세분화 그룹으로 나눌 칼럼 을
HAVING 조건식 그룹에대한조건식 -- GROUP 대한 조건
ORDER by 칼럼이나 식
;
---->
실습 1
-- 부서별로 직책이 사원인 직원들에 대해서만 평균 급여를 구하시오
select dept_id 부서코드,title 직책, avg(salary) 평균급여
from s_emp
where title ='사원'
group by dept_id ,title
order by avg(salary) desc;
-- title 이 '사원' dept_id & title column 값을 가진 데이터 끼리 같이 그룹화
결과값
실습 2
--각 직책별로 급여의 총합을 구하되 직책이 부장인 사람은 제외하시오.
-- --단, 급여총합이 8000(만원) 이상인 직책만 나타내며
-- -- 급여 총합에 대한 오름차순으로 정렬하시오.
select title,sum(salary)
from s_emp
where title
not like '%부장'
group by title
having sum(salary) >'8000'
order by sum(salary) ASC
;
결과값
실습 3
--각 부서내에서 각 직책별로 몇 명의 인원이 있는지를 나타내시오.
select dept_id 부서코드, title 직책, count(*) 인원
from s_emp
group by dept_id, title
order by dept_id Asc
;
결과값
2.JOIN
join이란? 하나이상의 테이블로 부터 연관된 테이블을 검색해오는 법이다.
PrimaryKEY(PK) & Foreign KEY(FK)
값의 연관에 의해 JOIN 성립
그러나 어떤 경우에는 이러한 PK,FK 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립되기도 한다.
JOIN 종류
- EQUIJOIN : column 간의 값들이 서로 정확히 일치하는 경우에 사용. 일반적으로 PK, FK 관계에 의함
- NON-EQUIJOIN : 한 칼럼의 값이 다른 칼럼의 값과 정확히 일치하지 않는 경우에 사용
- OUTER JOIN : JOIN조건을 만족하지 않는 경우에도 모든 행들을 다 보려는 경우에 사용
- SELF JOIN : 같은 테이블에 있는 행들을 JOIN하고자 하는 경우에 사용
EQUIJOIN(INNER JOIN)의 기본 구조
SELECT 테이블명.칼럼명, 테이블명.칼럼명, ...
FROM 테이블1 테이블2
WHERE 테이블1.칼럼1 = 테이블2.칼럼2;-- 직원 테이블과 부서 테이블을 조인하여 .. 사원의 이름과 이름과 부서명을 나타냇오
select e.name 사원명 ,e.dept_id, d.name 부서명
from s_emp e, s_dept d
where e.dept_id = d.id;
결과값
컬럼 과 테이블에 ALIAS 사용
테이블명, 컬럼명 형식으로 기술 하면 테이블명이 길면 많은 시간이 소요된다.
해서 테이블에 대한 ALIAS 를 지정하는 것이 좋다.
일단 테이블에 ALIAS를 지정했으면, 테이블명 대신에 반드시 ALIAS를 써야 한다.
칼럼에 대한 ALIAS를 지정하면 출력시 칼럼 표기를 명확히 할 수 있다
-- 서울지역에 근무하는 사원의 대해 각 사원의 이름과 근무하는 부서명을 나타내시오
select s.name 사원명 ,e.dept_id, d.name 부서명
from s_region s, s_dept d
where s.region_id = d.region_id
결과값
2,NON-EQUIJOIN- 연산자 외에 다른 연사자를 사용하여 조인하는 방법 등급처리에 용이
--NON-EQUIJOIN의 기본 구조
SELECT e.name, e.salary, g.grade 급여동급
FROM e_emp e, salgrade g
WHERE e.salary BETWEEN g.losal AND g.hisal;
--NON-EQUIJOIN이란 한 컬럼의 값이 다른 컬럼의 값과 정확히 일치하지 않는 경우 '=' 연산자 외의 다른 연산자를 사용하여 JOIN하는 방법을 말한다.
--즉, 정확한 값의 비교가 아니라 값의 범위를 비교해야 할 때 사용한다.
--직원 테이블(S_EMP)과 급여 테이블(SALGRADE)을 JOIN하여 사원의 이름과 급여, 그리고 해당 급여등급을 나타내시오.
SELECT E.NAME, E.SALARY, G.GRADE
FROM S_EMP E, SALGRADE G
WHERE E.SALARY BETWEEN G.LOSAL AND G.HISAL AND E.TITLE = '사원';
결과값
3.OUTER JOIN
1. OUTER JOIN을 하는 경우에는 (+)연산자를 사용한다.
2. (+)를 사용하는 위치는 JOIN할 데이터가 부족한 쪽(즉, 상대 테이블과 비교할 데이터가 존재하지 않는 쪽)에 위치시킨다.
3. (+)는 WHERE절에서 비교연산자 기준으로 좌변 또는 우변의 어느 한쪽에 위치시킨다.
4. OUTER JOIN에서 IN이나 OR을 사용할 수 없다.
-- outer join
select e.name 사원명 , e.id, c.name 고객명
from s_emp e, s_customer c
where e.id = c.sales_rep_id
order by 2;
결과값
4.SELF JOIN
1. SELF JOIN으로 하나의 테이블을 마치 여러개인 것처럼 사용가능
2. 반드시 테이블에 대한 ALIAS를 각기 다르게 지정해야 한다.
3. 칼럼의 이름 앞에 반드시 테이블의 ALIAS를 붙여야 한다.
4. SELF JOIN의 횟수는 제한되어 있지 않다.-- self join 같은 테이블이지만 마치 여러개 인것 처럼 사용 가능
select w.id 사번 , w.name 사원명,
m.id 부사장사번, m.name 부서장명
from s_emp w , s_emp m
where w.manager_id = m.id;
-- '김정미'
-- 와 같은 직책을 가지는 사원의 이름과
-- 직책 급여 , 부서번호를 나타내시오 (self- join 을 사용할것
--
select a.title ,a.salary, a.dept_id,b.name
from s_emp a, s_emp b
where a.manager_id= b.manager_id
and a.name='김정미'
and b.name <> '김정미'
;
결과값
추가
SET 연산자
SET연산자의 종류
- UNION : 각 QUERY결과의 합집합
- UNION ALL : 각 QUERY결과의 합집합에 공통부분을 더함
- INTERSECT : 각 QUERY결과의 교집합
- MINUS : 첫번째 QUERY결과와 두번째 QUERY결과의 차집합
SET연산자의 기본 구조
SELECT 칼럼1, 칼럼2 ...
FROM 테이블1 ...
SET연산자
SELECT 칼럼1', 칼럼2' ...
FROM 테이블2 ...
ORDER BY ;1. 첫번째 SELECT구문에서 기술된 칼럼들과 두번째 SELECT구문에서 기술된 칼럼들은 그 개수와 타입이 일치해야 한다. 2. FROM절 뒤에 기술된 테이블은 서로 다를 수도, 같을 수도 있다. 3. 칼럼 HEADING는 첫번째 SELECT 구문의 컬럼명이 출력된다. 4. ORDER BY 절은 마지막에 한번만 기술한다.
-- SET연산자 예
select name, dept_id,title
from s_emp
where dept_id =110
UNION
Select name, dept_id, title
from s_emp
where dept_id =113
order by 1;
결과값
3. SUBQUERY
SUBQUERY-> 하나의 SELECT문 안에 포함되어 있는 또 다른 SELECT 문장
--SUBQUERY의 기본 구조
SELECT 검색할 컬럼들
FROM 테이블명
WHERE 형식 연산자 (SELECT 검색할 컬럼들
FROM 테이블
WHERE ...);
* SUBQUERY는 괄호로 묶여 있어야 한다.
* SUBQUERY구문에서는 ORDER BY절을 포함할 수 없다.
* SUBQUERY는 연산자의 오른쪽에 나타나야 한다.
* SUBQUERY에서 사용할 수 있는 연산자의 종류
* 단일행 연산자(=,>,>=,<,<=,<>)
* 복수행 연산자(IN, NOT IN)SUBQUERY 실습 ↙↙↙↙↙---SINGLE ROW SUBQUERY
---SINGLE ROW SUBQUERY란 SUBQUERY에서 MAIN QUERY로 전달되는 행이 단 하나인 경우다.
-- --이런 경우는 단일 행 연산자를 사용한다.
SELECT name, title, dept_id
FROM s_emp
WHERE dept_id = (SELECT dept_id
FROM s_emp
WHERE name = '김정미');
--전달되는 값이 1행
--MULTI ROW SUBQUERY
--MULTI ROW SUBQUERY란 SUBQUERY에서 MAIN QUERY로 전달되는 행이 여러 개인 경우를 말한다.
-- --이런 경우는 다중 행 연산자를 사용한다.
SELECT name, dept_id
FROM s_emp
WHERE dept_id IN (SELECT id
FROM s_dept
WHERE region_id = 3);
--전달되는 값이 여러 행
--MULTI-COLUMN SUBQUERY
--MULTI-COLUMN SUBQUERY란 SUBQUERY구문을 작성할 때 WHERE절에서 비교하는 칼럼이
--하나가 아니라 여러 개의 칼럼을 동시에 비교하는 경우를 말하며 이런 경우를 pair-wise되었다고 한다.
SELECT name, dept_id, salary
FROM s_emp
WHERE (salary, dept_id) IN
(SELECT MIN(salary), dept_id
FROM s_emp
GROUP BY dept_id);
--FROM절에서의 SUBQUERY 예시
SELECT e.name, e.title, d.name
FROM(SELECT name, title, dept_id
FROM s_emp
WHERE title = '사원') e, s_dept default:
WHERE e.dept_id = d.id;
--한 테이블에 데이터의 양이 많은 경우에는 FROM절에 테이블 전체를 기술하여 사용하면 효율이 떨어질 수 있으므로
--일부의 행과 칼럼을 선택하여 FROM절에 SUBQUERY로 효율적인 검색을 가능하게한다.
-- --이러한 SUBQUERY를 INLINE VIEW라고 부른다.
--HAVING절에서의 SUBQUERY 예시
SELECT dept_id, AVG(salary)
FROM s_emp
GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary)
FROM s_emp
WHERE dept_id = 113);
--CREATE절에서의 SUBQUERY 예시
CREAT TABEL emp_113(id, name, mailid, start_date)
AS SELECT id, name, mailid, start_date
FROM s_emap
WHERE dept_id = 113;
--기존의 테이블을 이용하여 특정 컬럼, 행으로 원하는 테이블 생성.
-- 데이터는 제외하고 테이블만 생성하려면 조건절이 항상 거짓이게 하면 된다.
--DML문에서의 SUBQUERY 예시1)
INSERT INTO emp_113(id, name, mailid, start_date)
SELECT id, name, mailid, start_date
FROM s_emp
WHERE start_date < '16/01/01';
2)
UPDATE s_emp
SET dept_id = ( SELECT dept_id
FROM s_emp
WHERE title = '사장')
WHERE name = '안창환';
