2020-09-10, SQL_3일차

9월 10일 목요일, 오늘의 SQL 강의에서는 아직 남았던 단일행 문자 함수중 DECODE와 CASE라는 조건 함수에 대해서 배웠구요. 이후로는 COUNT, SUM, AVG, MAX, MIN과 같은 그룹 함수에 대해서 배웠습니다.
마지막에는 Oracle에서 쓰이는 Oracle join 중 equi, non-equi, self 조인에 대해 배웠고 잠깐 catesian product가 뭔지 까지 배웠습니다.

 

---

문자 함수의 단일행 함수 중 조건 함수입니다.

1) DECODE 함수  :   조건이 반드시 일치해야 되는 경우 사용합니다. ANSI언어가 아닌 Oracle에서만 사용되는 Oracle SQL입니다.

< 문법 >

DECODE ( 컬럼, 비교값1, 결과값1,
               비교값2, 결과값2,
               기본결과 값)

< 예제 >

SELECT  last_name, salary,
        DECODE(salary, 24000, salary*1.3,
                       17000, salary*1.2,  
                       salary) "보너스+월급"
FROM employees
ORDER BY 2 desc;

--문제
--1.  각각의 사원번호에 대해 휴가 시작일과 휴가 종료일을 보여주되, 오전오후의 속성인
-- SMA_VC와 EMA_VC를 이용하여 1일 경우는 오전, 2일 경우는 오후로 출력하시오.

select seq_nu 사원번호, stdate_vc "휴가 시작일", eddate_vc "휴가 종료일",
        DECODE ( SMA_VC, 1, '오전',
                         2, '오후',
                         'null') "휴가 시간대"
from T_HOLHISTORY; 

--문제
--2. 사원번호와 휴가신청 상태를 표시하되 상태( STATE_VC)의 
--현재상태 코드가 0일 경우 허가 , 10일 경우대기 , 20일 경우 불허가로 표시하시오.

select seq_nu as 사원번호, DECODE ( state_vc, 0,  '허가',
                                            10, '대기',
                                            20, '불허가',
                                            'null') as 휴가신청상태
from T_HOLHISTORY;

---

 

2) CASE 함수    ==> DECODE 함수와 마찬가지로 여러 조건에 대해서 선택적으로 SQL문을 실행
DECODE 함수는 반드시 조건이 일치하는 경우에 사용이 가능하지만 CASE 함수는 다양한 비교 연산자를 이용하여 조건 설정 가능

< 문법 1 >

CASE 컬럼 WHEN 비교값1 THEN 결과값1
          WHEN 비교값2 THEN 결과값2
          ELSE 결과값n
END 

< 예제 >

SELECT last_name,salary,
        CASE salary WHEN 24000 THEN salary*1.3
                    WHEN 17000 THEN salary*1.2
                    ELSE salary
        END "보너스+월급"
FROM employees
ORDER BY 2 desc;    

< 문법 2 >

CASE WHEN 조건1 THEN 결과값1
      WHEN 조건2 THEN 결과값2
      ELSE 결과값n
END

< 예제 >

SELECT last_name,salary,
    CASE WHEN salary >=20000 THEN 1000
         WHEN salary >=15000 THEN 2000
         WHEN salary >=10000 THEN 3000
         ELSE 4000
    END 보너스
FROM employees
ORDER BY 2 desc;

SELECT last_name,salary,
        CASE WHEN salary BETWEEN 20000 AND 25000 THEN '상'
             WHEN salary BETWEEN 10000 AND 20001 THEN '중'
             ELSE '하'
        END 등급
FROM employees
ORDER BY 2 desc;

---

 

그룹 함수 (4장, p159) : 입력 처리되는 행의 개수와 무관하게 단 하나의 결과만 반환

1.SUM 함수  : 널(null)값을 제외한 해당 컬럼 값들의 총합을 구하는 함수

< 문법 >

SUM( DISTINCT | ALL | 컬럼명 )

< 예제 >

SELECT SUM(DISTINCT salary),SUM(ALL salary), SUM(salary) 
FROM employees;     --Distinct 중복 제거

---

 

2. AVG 함수     : 널(null)값을 제외한 해당 컬럼 값들의 평균을 구하는 함수

< 문법 >

AVG( DISTINCT | ALL | 컬럼명 )

< 예제 >

SELECT SUM(salary), AVG(distinct salary), AVG(all salary), AVG(salary)
FROM employees;

---

 

3. MAX 함수와 MIN 함수     : 해당 컬럼 값들 중에서 최대값/최소값을 구하는 함수

< 문법 >

MAX( DISTINCT | ALL | 컬럼명 ) , MIN(DISTINCT | ALL | 컬럼명 )

< 예제 >

desc employees;
SELECT last_name, MAX(salary), MIN(salary)
FROM employees
group by last_name; -- 그룹함수 => 도출된 값이 모든 컬럼 값과 매칭 X, + 싱글 컬럼 값 => 에러

---

 

4. COUNT 함수   : 테이블에서 조건을 만족하는 행의 개수를 반환하는 함수
--COUNT 함수에 특정 컬럼을 지정하면 널(null)값을 제외한 해당 컬럼값이 가지고 있는 행의 개수를 반환
--모든 그룹함수는 null 값 제외,    * -> Null 포함 

< 문법 >

COUNT( DISTINCT | ALL | 컬럼명 | * )

< 예제 >

SELECT COUNT(last_name), COUNT(commission_pct), count(*) "테이블의 총 컬럼 수"
FROM employees;

SELECT COUNT(job_id), COUNT(DISTINCT job_id)
FROM employees;

---

 

5. Group by 절

SELECT last_name, MAX(salary)
FROM employees; --> Error 그룹함수인 MAX(salary)의 실행 결과는 하나인데, 그룹함수를 적용하지 않은 단순 컬럼인 last_name의 결과는 107개이기 때문
                  --두 값의 실행 결과가 달라서 error

1) group by

< 문법 >

SELECT 단순 컬럼 , 그룹함수 , 그룹함수2
FROM 테이블명
[WHERE 조건식]
GROUP BY 단순 컬럼
[ORDER BY 표현식];

< 예제 >

SELECT department_id 부서번호, AVG(salary) 평균월급
FROM employees
GROUP BY department_id
ORDER BY 1;

SELECT TO_CHAR( hire_date , 'YYYY' ) 년,
       TO_CHAR( hire_date , 'MM') 월 , SUM(salary)
FROM employees
GROUP BY TO_CHAR( hire_date , 'YYYY'),
         TO_CHAR( hire_date , 'MM')
ORDER BY 년 ASC;

---

 

2) Having 절 : GROUP BY절에 의해서 생성된 결과 중에서 조건과 일치하는 데이터를 추출할 때 사용
--Group By절에 의해 생성된 결과를 갖고 조건처리하는 where문

< 문법 >

처리 순서 
5		SELECT  단순 컬럼 , 그룹함수 , 그룹함수2
1		FROM 테이블명
2 		[WHERE 조건식]
3 		GROUP BY 단순 컬럼
4 		[HAVING 조건식]
6 		[ORDER BY 표현식];

< 예제 >

--사원 테이블에서 부서별 월급 총액이 90000 이상인 부서만 조회하는 SQL문
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id
HAVING SUM(salary) >= 90000
ORDER BY 1;

--사원 테이블에서 부서별 인원수가 6명 이상인 부서명을 조회하는 SQL문
SELECT department_id, COUNT(department_id)
FROM employees
GROUP BY department_id
HAVING COUNT(salary) >= 6
ORDER BY 1;

---

 

---

조인 join   : 여러 테이블을 연결해서 필요한 데이터를 조회하는 방법   (p.177)
--검색하고자 하는 데이터가 여러 테이블에 분산되어 있는 경우, 여러 테이블을 연결해서 필요한 데이터를 조회
--참조 당하는 table = master, 참조하는 table = slave, child
--foreign key = 반드시 master table의 컬럼 중 primary key 또는 unique로 되어있는 column만 참조 가능
--foreign key = 참조하는 퀄럼의 값만 저장 가능, Null 가능
--n개의 테이블을 join하기 위해서는 반드시 n-1개의 join조건이 필요.

종류		설명
오라클 조인	cartesian product	조인 조건을 생략하거나 조인이 잘못된 경우에 발생된다.
	equi 조인	기본키(Primary Key)와 참조키(Foreign Key)을 사용하여 반드시 조건이 일치하는 데이터만 조회하는 방법이다.
	non-equi 조인	조건이 반드시 일치하지 않더라고 범위에 포함되는 경우에 조회하는 방법이다.
	self 조인	자신의 테이블과 조인하는 방법이다.
	outer 조인	조건에 일치하지 않아도 조인 결과에 포함시키는 방법이다.
ANSI 조인	cross 조인	오라클 조인의 cartesian product와 동일한 방법이다.
	natural 조인	오라클 조인의 equi 조인과 동일하며 자동으로 두 개의 테이블에서 일치하는 컬럼을 찾아서 조인된다.
	using(컬럼)	오라클 조인의 equi 조인과 동일하며 명시적으로 일치하는 컬럼을 작성한다.
	join ~ on절	오라클 조인의 non-equi 조인과 동일한 조인방법이다. on절에 조인조건을 명시한다.
	left | right | full  outer 조인	오라클 조인의 outer 조인과 동일한 방법이다.
	self 조인	오라클 조인의 self 조인과 동일한 방법이다.

---

 

---

오라클 Join
1)Equi 조인  (equivalent)  : 두 테이블에서 공통적으로 존재하는 컬럼의 값이 반드시 일치하는 행을 연결하여 데이터를 반환하는 조인
--대부분 기본키(Primary Key)을 가진 테이블(master)과 참조키(Foreign Key)을 가진 테이블(slave)을 조인할 때 사용, 동등연산자 사용

< 문법 >

SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 , 테이블2
WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼;

< 예제 >

--사원 테이블(employees)과 부서 테이블(departments)을 Equi 조인을 사용하여 사원명과 부서명을 출력하는 SQL문
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
        -- foreign key, 얘가     얘의 값을 참조해서 가져옴 department table의 primary key

---

--테이블에 별칭(alias) 사용 가능

< 문법 >

SELECT alias1.컬럼 , alias2.컬럼
FROM 테이블1 alias1, 테이블2 alias2
WHERE alias1.공통컬럼 = alias2.공통컬럼;

< 예제 >

--사원 테이블과 부서 테이블 조인시 테이블 별칭(alias)을 사용한 SQL문
SELECT emp.last_name, department_name, emp.department_id
FROM employees emp, departments dept
WHERE emp.department_id = dept.department_id;

---

--검색 조건 추가

< 예제 >

--사원 테이블과 부서 테이블 조인시 검색 조건으로 이름이 Whalen 사원만 검색하는 조건이 추가된 SQL문
SELECT emp.last_name,salary,department_name
FROM employees emp, departments dept
WHERE emp.department_id = dept.department_id
                        AND last_name='Whalen';

SELECT d.department_name 부서명, COUNT(e.employee_id) 인원수
FROM employees e, departments d
WHERE e.department_id = d.department_id
        AND TO_CHAR( hire_date , 'YYYY') <= 2005
GROUP BY d.department_name;

---

 

2) Non-Equi 조인    : WHERE절에 조인 조건을 지정할 때 동등 연산자(=) 이외의 비교 연산자를 사용하는 조인

< 예제 >

--사원들의 월급 등급을 조회 할 수 있는 SQL문
SELECT last_name, salary, grade_level
FROM employees e, job_grades g
WHERE e.salary BETWEEN g.lowest_sal AND g.highest_sal;

--사원들의 월급 등급 및 부서명까지 조회하려면 3개의 테이블을 조인
SELECT last_name, salary, department_name, grade_level
FROM employees e, departments d, job_grades g
WHERE e.department_id = d.department_id
        AND e.salary BETWEEN g.lowest_sal AND g.highest_sal;

---

 

cartesian Product
일치하는 데이터만 반환하지 않고 조인하는 각 테이블의 행 개수를 서로 곱한 결과가 반환
따라서 유효한 데이터로 사용되지 못하며 일반적으로 조인 조건이 생략된 경우에 발생 => Trash

SELECT last_name, department_name, employees.department_id
FROM employees, departments;

---

 

3) self 조인    : 하나의 테이블만 사용하여 자기 자신을 조인
--tip : 하나의 테이블을 논리적으로 2개로 분리해서 작업

< 예제 >

--사원의 관리자명 출력
SELECT e.last_name 사원명, m.last_name 관리자명
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;

---

 

 어제 또 저녁먹고 그냥 자버려서.... 수업 전에 급하게 마무리.
조인부분은 학부시절에 DB 강의들을 때 첫 시작부터 배웠던거라. 기대 중.
내가 기억을 할지 안 할지 ㅋㅋ;;;;
그런데 나는 Mysql로 배웠던 것 같아서... 위에 Oracle 문법은 뭔가 생소한 듯;;

쨋든 이만.
이제 수업 들으러..... ( 취업하면 일상이 이렇겠구나.. 깨닫는 중.)