교육 32DAY SQL

▶ 변환 함수
■ 숫자 형식 변환하기
■ TO_CHAR
- TO_CHAR는 숫자 값을 지정한 형식의 문자열로 변환하는 함수
- 즉, NUMBER 데이터 타입을 지정한 형식의 CARCHAR2 데이터 타입으로 변환할때 사용함

숫자 지정 형식

SELECT salary,
TO_CHAR(salary,'L9999999')
FROM employees;

SELECT salary,
TO_CHAR(salary, '999999.99')
FROM employees;

SELECT salary,
TO_CHAR(salary, '$9999999')
FROM employees;

- 문자 데이터 값을 날짜 데이터 값으로 변환 하는 예

SELECT TO_DATE('20171007', 'YYMMDD')
FROM dual;

▶ 일반 함수
- 다양한 함수가 있지만 데이터를 조작하거나 프로그래밍할 때 자주 쓰는 함수를 살펴보자
- 유용한 함수이므로 꼭 익혀 두기 바람

NVL : NULL 값 처리하기

- 특정 열의 행에 대한 데이터 값이 없다면 데이터 값은 null이 됨
- null은 그 자체로 의미가 있는데 '값이 없다'는 것을 나타내는 값임
- 테이블을 정의할 때 null값을 가지지 못하도록 지정할 수도 있음
- 이런 경우를 not null이라고함
- null 값은 다음과 같은 특징이 있음

■ 할당되지 않았거나 알려져 있지 않아 적용이 불가능한 값임
■ 0이나 공백(space)과는 다름
■ null 값을 포함하는 산술 연산의 결과는 null임

SELECT *
FROM employees
ORDER BY commission_pct;

- commission_pct에 대해 오름차순으로 정렬하여 출력
- commission_pct 열을 살펴보면 35행까지는 데이터 값이 있지만 36행부터는 데이터 값이 null임
- 해당 값에 간단한 산술 연산을 적용해보자

SELECT salary * commission_pct ← salary와 commission_pct를 곱한 결과 출력
FROM employees
ORDER BY commission_pct;

- commission_pct 열의 데이터 값이 null이면 일괄적으로 null 값을 1로 변환하여 salary 열과 곱해보자

NVL(열 이름, 치환 값)

SELECT salary * NVL(commission_pct, 1)
FROM employees
ORDER BY commission_pct;

- commission_pct 열의 데이터 값이 null이면 1로 치환하는 NVL 함수를 적용한 결과임
- NVL 함수는 null 값을 어떤 특정한 값으로 변환하는데 사용함
- NVL(commission_pct.0.5)처럼 응용하여 사용할 수 있음
- 비슷한 유형에는 NVL2 함수가 있음
- NVL2 함수는 NVL2(열 이름 1, 열 이름 2, 열 이름 3) 형태로 사용하며, 열 이름 1이 null이 아니면
  열 이름 2를 출력, null이면 열 이름 3을 출력

SELECT first_name, last_name, salary, commission_pct,
       NVL2(commission_pct, salary * (1 + commission_pct), salary) AS total_compensation
FROM employees;

■ DECODE : 조건 논리 처리하기
- DECODE 함수는 하나의 값을 다른 값으로 변환하거나 조건에 따라 다른 값을 반환하는데 사용됨
- 주로 데이터를 쿼리하거나 보고서를 작성할 때 데이터의 값을 특정 조건에 따라
  다르게 표시하거나 분류할때 유용하게 사용됨

DECODE 함수의 일반적인 구조

DECODE(expr, search1, result1, searcch2, result2, ···, default_result)

■ expr : 비교할 대상 값
■ search1, search2, ··· : expr과 비교할 값
■ result1, result2, ··· : 각 검색 조건에 대한 결과 값
■ default_result (선택사항) : expr이 어떠한 검색 값과도 일치하지 않을 경우 반환할 기본 결과 값

SELECT first_name, last_name, department_id, salary 원래급여,
       DECODE(department_id, 60, salary * 1.1, salary) 조정된급여,
       DECODE(department_id, 60, '10%인상', '미인상') 인상여부
FROM employees;

SELECT first_name, last_name, department_id,
       DECODE(department_id,
       10, '과학',
       100, '수학',
       110, '영어', '기타') AS 과목명
FROM employees;

■ CASE 표현식 : 복잡한 조건 논리 처리하기
- 복잡한 조건식을 여러 개 적용해야 할 때는 DECODE 함수보다 CASE 표현식을 이용하는 것이 유용할 수 있음
- DECODE 함수는 데이터 값이 정확히 맞거나 틀린 조건을 처리하기 쉬운 반면 CASE 함수는
  조건의 범위가 다양한 경우에 쉽게 처리할 수 있음

CASE
    WHEN 조건 1 THEN 출력 값 1
    WHEN 조건 2 THEN 출력 값 2
    ···
    ELSE 출력 값 3
END

SELECT employee_id, first_name, last_name, salary,
       CASE
            WHEN salary >= 9000 THEN '상위급여'
            WHEN salary BETWEEN 6000 AND 8999 THEN '중위급여'
            ELSE '하위급여'
       END AS 급여등급
FROM employees
WHERE job_id = 'IT_PROG';

- CASE 표현식의 수식이 너무 길어 열 이름을 별칭인 '급여등급'으로 변경함
- 데이터 값의 범위를 모르는 상태에서 특정 조건에 맞춰 출력하거나 조작해야 한다면 CASE 표현식이 유용할 수 있음
- CASE 문은 매우 다양하게 활용할 수 있는데 예제와 같이 숫자에 대한 구간 등급을 나누어
  표현하는 것은 물론 나이, 지역, 날짜를 구분하여 출력하고자 할 때도 얼마든지 응용할 수 있음

■ RANK, DENSE_RANK, ROW_NUMBER : 데이터 값에 순위 매기기
- RANK, DENSE_RANK, ROW_NUMBER는 데이터 값에 순위를 매기는 함수
- 순위를 매기는 것은 동일하지만 사용법이 조금씩 다름
- 즉, 공통 순위가 있을 때 출력을 어떻게 하느냐에 따라 용도가 달라짐

순위 함수의 출력 방법 차이

RANK() OVER ([PARTITION BY partition_expression] ORDER BY sort_expression [ASC | DESC])

■ PARTITION BY partition_expression : 이 부분은 옵션 / 
                                      지정이 되면 데이터를 분할하여 각 그룹 내에서 순위를 계산함
■ ORDER BY sort_expression [ASC | DESC] : 순위를 결정할 때 사용할 정렬 기준 / 
                                          정렬 기준에 따라 순위가 매겨짐
                                          ASC는 오름차순(기본값), DESC는 내림차순으로 정렬

SELECT employee_id, salary,
       RANK()       OVER(ORDER BY salary DESC) RANK_급여,
       DENSE_RANK() OVER(ORDER BY salary DESC) DENSE_RANK_급여,
       ROW_NUMBER() OVER(ORDER BY salary DESC) ROW_NUMBER_급여
FROM employees;

예제 : RANK, DENSE_RANK, ROW_NUMBER 함수를 각각 이용해 employees 테이블 직원이 속한
       department_id 안에서 salary 값이 높은 순서대로 순위를 매겨 출력해보기

SELECT A.employee_id,
       A.department_id,
       B.department_name, salary,
       RANK()       OVER(PARTITION BY A.department_id ORDER BY salary DESC) RANK_급여,
       DENSE_RANK() OVER(PARTITION BY A.department_id ORDER BY salary DESC) DENSE_RANK_급여,
       ROW_NUMBER() OVER(PARTITION BY A.department_id ORDER BY salary DESC) ROW_NUMBER_급여
FROM employees A, departments B
WHERE A.department_id = B.department_id
ORDER BY B.department_id, A.salary DESC;

▶ 그룹함수 : 그룹으로 요약하기
- 그룹 함수는 단일 행 함수와 달리 여러 행에 대해 함수가 적용되어 하나의 결과를 나타내는 함수
- 집계 함수라고 부르기도 함
- 기준 열에 대해 같은 데이터 값끼리 그룹으로 묶고 묶은 행의 집합에 대해 그룹 함수 연산이 필요하다면
  GROUP BY 절을 이용하여 처리할 수 있음
- 묶은 그룹에 대해 조건이 필요하다면 HAVING 절을 이용함

▶ 그룹 함수의 종류와 사용법

그룹 함수의 종류

- 모든 함수가 NULL 값은 무시 하지만 COUNT() 함수는 (*)의 경우 null 값도 개수로 셈
- 나머지는 NULL 값을 제외하고 연산

■ SUM, AVG 함수
- SUM은 열의 합계를 구하는 함수고 AVG는 열의 평균을 구하는 함수
- 그룹 함수의 결괏값끼리 계산할 수 있음

SUM(열 이름) / AVG(열 이름)

SELECT SUM(salary) 합계, 
       AVG(salary) 평균, 
       SUM(salary)/COUNT(salary) 계산된평균
FROM employees;

■ MAX, MIN 함수

MAX(열 이름) / MIN(열 이름)

SELECT MAX(salary) 최댓값,
       MIN(salary) 최솟값,
       MAX(first_name) 최대문자값,
       MIN(first_name) 최소문자값
FROM employees;

▶ GROUP BY : 그룹으로 묶기
- 특정 열을 기준으로 데이터를 그룹화하는 역할
- 이를 통해 그룹화된 데이터를 사용하여 집계 함수를 적용하거나 원하는 데이터를 추출할 수 있음

GROUP BY 구문의 기본 구조

SELECT		기준 열, 그룹 함수(열 이름)
FROM		테이블 이름
[WHERE		조건식]
GROUP BY 	열 이름
[ORDER BY 	열 이름];

SELECT column_name1, aggregate_function(column_name2)
FROM table_name
WHERE conditions
GROUP BY column_name1;

■ 'aggregate_function : 집계 함수는 'sum()', 'count()', 'avg()', 'min()', 'max()' 등과 같은
  함수를 말하며, GROUP BY 절에 명시된 열을 기준으로 그룹화된 데이터에 적용

SELECT job_id 직무, 
       SUM(salary) 직무별_총급여,
       AVG(salary) 직무별_평균급여
FROM employees
WHERE employee_id >= 10
GROUP BY job_id
ORDER BY 직무별_총급여 DESC, 직무별_평균급여;

SELECT job_id job_id_대그룹,
       manager_id manager_id_중그룹,
       SUM(salary) 그룹핑_총급여,
       AVG(salary) 그룹핑_평균급여
FROM employees
WHERE employee_id >= 10
GROUP BY job_id, manager_id     ← job_id별로 한 번 그룹화 하고 manager_id 별로 다시 한번 그룹화
ORDER BY 그룹핑_총급여 DESC, 그룹핑_평균급여;

▶ HAVING : 연산된 그룹 함수 결과에 조건 적용하기
- HAVING 절은 그룹화된 값에 조건식을 적용할 때 사용함
- 즉, WHERE 절에서는 그룹 함수를 사용할 수 없으므로
  HAVING 절을 사용해 그룹 함수의 결괏값에 대해 조건식을 적용함
- 일반적으로 HAVING 절은 GROUP BY 절 다음에 기술하는 것이 논리적이고 가독성도 좋음

SELECT job_id 직무, 
       SUM(salary) 직무별_총급여, 
       AVG(salary) 직무별_평균급여
FROM employees
WHERE employee_id >= 10
GROUP BY job_id
HAVING SUM(salary) > 30000
ORDER BY 직무별_총급여 DESC, 직무별_평균급여;

▼ ▼ ▼ ▼ ▼ 연습문제 ▼ ▼ ▼ ▼ ▼

-- 1번 employees 테이블에서 직원의 이름, 입사일, 그리고 입사일을 'YYYY년 MM월 DD일' 형식으로 표시하세요.
SELECT first_name, hire_date,
       TO_CHAR(hire_date, 'YYYY"년" MM"월" DD"일"') AS 입사일자
FROM employees;

-- 2번 employees 테이블에서 직원의 이름, 급여, 그리고 숫자 형식의 급여를 표시하세요.(\)
SELECT first_name, salary,
       TO_CHAR(salary, 'L99,999,999') AS 급여
FROM employees;

-- 3번 employees 테이블에서 직원의 이름, 커미션 비율, 그리고 커미션 비율이 NULL인 경우 0을 표시하세요.
SELECT first_name, NVL(commission_pct, 0) AS commission_pct
FROM employees;

-- 4번 departments 테이블에서 부서 ID와 부서명을 표시하세요. 
	  부서 ID가 10, 20, 30인 경우 '관리 부서'로, 부서 ID가 40 또는 
	  50인 경우 '지원 부서'로, 그 외에는 '영업 부서'로 표시하세요.
SELECT department_id,
       DECODE(department_id,
              10, '관리부서',
              20, '관리부서',
              30, '관리부서',
              40, '지원부서',
              50, '지원부서',
                  '영업부서') AS department_name
FROM departments; 

-- 5번 employees 테이블에서 직원의 이름, 부서 ID, 부서명을 표시하세요. 
      부서 ID가 10이면 '관리 부서', 20이면 '연구 부서', 30이면 '영업 부서', 
      그 외에는 '기타 부서'로 표시하세요.
SELECT first_name 이름, department_id 부서ID,
       CASE 
            WHEN department_id = 10 THEN '관리부서'
            WHEN department_id = 20 THEN '연구부서'
            WHEN department_id = 30 THEN '영업부서'
            ELSE '기타부서'
       END 부서이름
FROM employees;

-- 6번 employees 테이블에서 직원의 이름, 급여, 급여 등급을 표시하세요. 
	  급여가 3000 미만이면 'C 등급', 3000 이상 6000 이하이면 'B 등급', 
      그 외에는 'A 등급'으로 표시하세요.
SELECT first_name, salary, 
       CASE
            WHEN salary < 3000 THEN 'C 등급'
            WHEN salary >= 3000 AND salary <= 6000 THEN 'B 등급'
            ELSE 'A 등급'
       END AS 등급
FROM employees;

-- 7번 employees 테이블에서 직원의 이름, 급여, 급여 순위를 표시하세요.
SELECT first_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS 급여순위
FROM employees;

-- 8번 employees 테이블에서 직원의 이름, 입사일, 그리고 입사일을 '2023-05-15' 형식으로 표시하세요.
SELECT first_name,hire_date,
       TO_CHAR(hire_date, 'YYYY-MM-DD') AS 입사일
FROM employees;

-- 9번 employees 테이블에서 직원의 이름, 급여, 그리고 급여를 '50,000' 형식으로 표시하세요.
SELECT first_name, salary,
       TO_CHAR(salary, '99,999') AS 급여
FROM employees;

-- 10번 부서별 총 급여 합계와 평균 급여를 구하세요.
SELECT department_id,
       SUM(salary) 총급여,
       AVG(salary) 평균급여
FROM employees
GROUP BY department_id;
     
-- 11번 부서별 직원 수를 구하세요.
SELECT department_id,
       COUNT(*) AS 직원수
FROM employees
GROUP BY department_id;

-- 12번 부서별 총 급여 합계가 30,000 이상인 부서 정보를 구하세요.
SELECT department_id 직무ID,
       SUM(salary) AS 총급여합계
FROM employees
GROUP BY department_id
HAVING SUM(salary) >= 30000;
       
-- 13번 직책별 최대 급여와 최소 급여를 구하세요.
SELECT job_id,
       MAX(salary) AS 최대급여,
       MIN(salary) AS 최소급여
FROM employees
GROUP BY job_id;

-- 14번 입사년도별 직원 수를 구하세요.
SELECT TO_CHAR(hire_date, 'YYYY') AS 입사년도,
       COUNT(*) AS 직원수
FROM employees
GROUP BY TO_CHAR(hire_date, 'YYYY');

 

▶▶ ERD
▶ 키와 제약 조건 : 개체 속성 표기법

⑴ 행 : 특정 employee(직원)에 대한 모든 데이터를 나타내는 단일 행(row, 로우)임
        employee_id 값에 의해 유일하게 식별됨
        실습을 위해 설치한 HR 데이터베이스에는 행이 총 107개 있음
        키본 키(primary key) : employee_id 열은 employee_id 데이터 값들로 구성되어있음

⑵ 기본 키(primary key) : employee_id 열은 유일하게 데이터를 구분한다고 해서 기본 키 또는 주 키라고 부름
                         기본 키는 데이터를 식별하는 '식별자' 역할을 함

⑶ 키 값이 아닌 일반 열임
   phone_number, hire_date, salary, commission_pct 열도 일 반 열에 속함

⑷ 고유 키(unique key) : 행에서 유일한 값을 갖는 데이터 값으로 구성된 열임
                        중복 값이 없는 유일한 값을 갖지만 기본 키가 아닌 데이터 값이 존재함
                        유니크 값 또는 유니크 키라고 부르기도 함

⑸ 왜래 키(foreign key) : job_id, department_id, manager_id 열은 테이블 간에 서로 연결 관계를 정의하는 외래 키
                         외래 키는 참조 테이블의 기본 키 또는 고유 키를 참조함
                         테이블의 구성 열이자 다른 테이블과 연결을 위한 열임

- ERD는 ER 다이어그램이라고도 부르는데 E는 개체(entity)라는 의미고 R은 관계(relation)라는 의미
- 개체는 정보를 저장하고 관리하기 위한 집합이자 식별 가능한 것
- ERD는 개체가 담고 있는 내용과 이들 간의 관계를 표현하는 좋은 수단임

▶▶ 조인과 집합 : 여러 개의 테이블 연결하기
▶ 조인이란 ?
- 계속해서 언급하는 '관계형 데이터베이스'라는 명칭은 테이블들이
  관계(relationship)를 맺고 조작되는 원리에서 유래함
- 테이블에는 각 유형에 맞는 데이터가 저장되어 있고 테이블들은 특정한 규칙에 따라 상호 관계를 맺음
- 데이터는 테이블에 흩어져 저장되어 있으므로 사용자가 원하는 형태로 데이터를 조작하려면
  특별한 방법이 필요한데 이를 위해 사용하는 기법이 조인임
- 조인은 한개 이상의 테이블과 테이블을 서로 연결하여 사용하는 기법을 말함
- 다음은 조인 기법의 종류임
- 실무에서 가장 많이 쓰는 동등 조인, 외부 조인, 자체 조인을 알아보자

조인 기법의 종류

SELECT *
FROM employees A, departments B
WHERE A.department_id = B.department_id;

▶ 동등 조인 : 똑같은 데이터끼리 연결하기
- 부서에 대한 상세한 정보를 얻기 위해 employees 테이블에 속한 외래 키
  department_id와 departments 테이블의 기본 키 department_id를 조인한 결과
- 데이터 값이 같은 것끼리 연결되어 조회된 것을 확인할 수 있음
- 열 전체를 조회(*)하기 위해 사용했으므로 양쪽 테이블에 모두 존재하는 department_id를 구별하기 위해 자동으로
  department_id와 department_id_1로 제목이 구분되어 출력(별칭이 department_id_1로 바뀐것은 아님)

SELECT A.employee_id, A.department_id, B.department_name, C.location_id, C.city
FROM employees A, departments B, locations C
WHERE A.department_id = B.department_id
AND B.location_id = C.location_id;

▶ 외부 조인 : 모든 데이터를 연결하기
- 동등 조인은 데이터 값이 정확히 일치하는 경우에만 결과를 출력
- 데이터 값이 일치하지 않는다면 결과가 조회되지 않음

SELECT COUNT(*)조인된건수
FROM employees A, departments B
WHERE A.department_id = B.department_id;

- 외부 조인(outer join)은 조건을 만족하지 않는 행도 모두 출력하기 위한 조인 기법

SELECT	테이블 이름 1, 열 이름 1, 테이블 이름 2, 열 이름 2, ···
FROM 	테이블 이름 1, 테이블 이름 2
WHERE	테이블 이름 1.열 이름 1 = 테이블 이름 2.열 이름2(+)

SELECT A.employee_id, A.first_name, A.last_name, B.department_id, B.department_name
FROM employees A, departments B
WHERE A.department_id = B.department_id(+)
ORDER BY A.employee_id;

- 외부 조인은 연결하는 방법에 따라 레프트 아웃터 조인(Left Outer Join),
  라이트 아웃터 조인(Right Outer Join) 이라고 부르기도함
- 오라클 데이터베이스에서는 (+)가 붙지 않는 쪽을 기준으로 부름
- 예를 들어 A=B (+)라면 오른쪽에 null이 생성되어 왼쪽이 기준이 되어 조인되므로 레프트 아웃터 조인이라 부름

▶ 자체 조인 : 자기 자신의 데이터와 연결하기

예제 : employees 테이블을 자체 조인하여 직원별 담당 매니저가 누구인지 조회하기
SELECT A.employee_id, A.first_name, A.last_name, A.manager_id,
       B.first_name||' '||B.last_name manager_name
FROM employees A, employees B
WHERE A.manager_id = B.employee_id
ORDER BY A.employee_id;

- employees 테이블의 직원 정보중에 manager_id 열이 있음
- 담당 매니저의 정보를 담고 있는 열인데, 데이터 값으로 담당 매니저의 employee_id(코드값)만 갖고 있으므로
  담당 매니저가 누구인지 인적 정보를 조회하려면 결국 다시 employees 테이블을 조인해야함
- 자기 자신의 테이블을 조인하는 것을 자체 조인(self join)이라고 함

- 동일한 employees 테이블을 사용했지만 employees A 테이블은 manager_id, employees
  B 테이블은 employee_id로 조인하여 매니저가 누구인지 이름을 출력
- 동등 조인과 원리는 같지만 테이블을 한 개만 사용한다는 차이점이 있음

자체 조인 특징

■ 자기 자신의 테이블을 이용하여 조인함
■ 같은 테이블을 사용하기 때문에 각기 다른 별칭을 사용함으로써
  마치 서로 다른 두 개의 테이블을 조인하는 것처럼 보임
■ 자체 조인을 할 때 테이블은 동일한 열로 구성되어 있기 때문에 조회할 때 
  반드시 '별칭.열 이름'의 형태로 명확하게 구별하여 조회해야함 그렇지 않으면 오류 발생

▶ 집합 연산자 : 집합으로 연결하기
- 조인 기법 외에도 테이블에서 데이터를 조회하는 방법이 한 가지 더 있는데
  바로 집합 연산자 (set operators)를 이용하는 방법
- 집합 연산자는 SELECT 문을 여러 개 연결하여 작성하며, 각 SELECT 문의 조회 결과를 하나로 합치거나 분리할 수 있음
- 집합 연산자는 합집합, 교집합, 차집합의 논리와 같음

집합 연산자의 종류

SELECT department_id
FROM employees
UNION
SELECT department_id
FROM departments;

SELECT department_id
FROM employees
UNION ALL
SELECT department_id
FROM departments
ORDER BY department_id;

SELECT department_id
FROM employees
INTERSECT
SELECT department_id
FROM departments
ORDER BY department_id;

SELECT department_id
FROM departments
MINUS
SELECT department_id
FROM employees;

▼ ▼ ▼ ▼ ▼ 연습문제 ▼ ▼ ▼ ▼ ▼

-- 1번 오른쪽 조인 (RIGHT JOIN)
SELECT e.first_name,
       e.last_name,
       d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

-- 2번 왼쪽 조인 (LEFT JOIN)
SELECT j.job_title,
       e.first_name,
       e.last_name
FROM jobs j
LEFT JOIN employees e ON j.job_id = e.job_id;

-- 3번 등속 조인 (INNER JOIN)
SELECT e.first_name,
       e.last_name,
       d.department_id,
       l.city
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id;

-- 4번 전체 조인 (FULL OUTER JOIN)
SELECT c.country_name,
       l.city,
       d.department_name
FROM countries c 
FULL OUTER JOIN locations l ON c.country_id = l.country_id
FULL OUTER JOIN departments d ON l.location_id = d.location_id;