교육 31DAY SQL

▶ SELECT : 데이터 조회의 기본
> 중복된 출력 값 제거하기
- job_id를 출력했는데 중복된 값이 그대로 출력
- 출력 결과만으로는 job_id에 어떤 종류가 있는지 한눈에 알아보기가 어려움
- 이럴때 중복 값을 제거하여 데이터 값을 종류별로 하나만 출력하는 명령어가 DISTINCT임

▶ SQL 문을 효율적으로 작성하기 위해 별칭 사용하기

▶ WHERE 조건 절을 활용한 데이터 검색
▶ WHERE 조건 절을 활용한 데이터 검색
- WHERE 절에는 연산자(operator)를 같이 쓸 수 있는데, 연산자는 operator라는 의미
  그대로 데이터 값을 조작하는 데 사용됨
- 복잡한 조건을 만족하는 SQL 문을 작성하려면 다양한 연산자를 사용해야함

- 연산자의 우선순위는 다음과 같음
■ 괄호 > 부정 연산 > 비교 연산 > SQL 연산 순으로 처리됨

▶ 비교 연산자 : 비교 조회 조건 주기
■ 등호 연산자 =
- 특정 데이터 값을 선택할 때 주로 사용하는 연산자는 '같다'라는 의미를 가진 등호연산자인 =임
- 여러분이 가장 많이 사용할 연산자 중 하나임
- WHERE 절 다음에 열 이름 = 데이터 값 형식으로 기술하면 됨

SELECT *
FROM employees
WHERE employee_id = 100;

SELECT *
FROM employees
WHERE job_id = 'IT_PROG';

SELECT *
FROM employees
WHERE first_name = 'David';

SELECT *
FROM employees
WHERE first_name = 'David' and salary > 4800;

▶ 비교 연산자 : 비교 조회 조건 주기

▶ SQL 연산자 : 조회 조건 확장하기
- SQL 연산자는 비교 연산자보다 조금 더 확장된 연산자로 자주 쓰는 연산자

BETWEEN a AND b 
IN(list)
LIKE '비교 문자'
IS NULL

■ BETWEEN 연산자
- BETWEEN 연산자는 두 값의 범위에 해당하는 행을 출력할 때 사용함

SELECT *
FROM employees
WHERE salary BETWEEN 10000 AND 20000;

SELECT *
FROM employees
WHERE employee_id BETWEEN 100 AND 200;

■ IN 연산자
- 조회하고자 하는 데이터 값이 여러 개일 때 사용함
- = 연산자와 유사하지만 = 연산자는 조회 조건으로 데이터 값을 하나만 지정할 수 있는데
  반해 IN 연산자는 데이터 값을 여러 개, 즉 목록(list)으로 지정할 수 있음
- 여러 개의 값 목록 중에서 하나의 값이라도 만족하면 조건에 해당하는 결과를 출력
- 이러한 연산자를 다중 행 연산자라고도 부름

SELECT *
FROM employees
WHERE salary IN (10000, 17000, 24000);

SELECT *
FROM employees
WHERE job_id IN ('AD_VP', 'IT_PROG');

■ LIKE 연산자
- job_id 값에서 AD를 포함하는 모든 정보를 조회해 보자
- %는 조건을 포함하는 '~ 모든 문자'라는 의미

SELECT *
FROM employees
WHERE job_id LIKE 'AD%';

SELECT *
FROM employees
WHERE last_name LIKE 'K%';

SELECT *
FROM employees
WHERE job_id LIKE 'AD___';

SELECT *
FROM employees
WHERE first_name LIKE 'Le%';

SELECT *
FROM employees
WHERE first_name LIKE '%ame%';

SELECT *
FROM employees
WHERE first_name LIKE '%in';

SELECT *
FROM employees
WHERE employee_id LIKE '1%';

SELECT *
FROM employees
WHERE employee_id LIKE '1%' and job_id = 'AD_VP' or job_id = 'ST_MAN';

■ IS NULL 연산자
- IS NULL 연산자는 데이터 값이 null인 경우를 조회하고자 할 때 사용함
- null은 값이 지정되지 않았기 때문에 값이 없어 알 수 없는 값을 말함
- null은 0이나 공백(space)과는 엄연히 다름
- 0은 숫자 값이고 공백은 문자 값이므로 다른 유형의 데이터 값임

SELECT *
FROM employees
WHERE manager_id IS NULL;

SELECT *
FROM employees
WHERE commission_pct IS NULL;

▶ 논리 연산자 : 조건 논리를 계속 연결하기
- 논리 연산자는 계속 붙여나갈 수 있음

▶▶ 함수란 ?
- 오라클 데이터베이스 시스템에서 제공하는 함수는 미리 정의된 기능을 통해 데이터를 좀 더 편리하게
  조작할 수 있도록 도와줌
- 함수란 사용자가 입력 값 X를 넣으면 정해 놓은 출력 값 Y가 나오는 개념

- 오라클 데이터베이스 시스템의 함수를 이용하여 문자, 숫자, 날짜 값 등을 조작할 수 있으며,
  각 데이터 타입(data type, 자료형)끼리 변환할 수도 있음 (단일 행 함수 기능)
- 복수의 행을 조합하여 그룹당 하나의 결과로도 출력할 수 있음 (그룹 함수 기능)

▶ 단일 행 함수 : 데이터 값을 하나씩 계산하고 조작하기
- 단일 행 함수의 특징은 다음과 같음
■ 각 행에 대해 수행함
■ 데이터 타입에 맞는 함수를 사용해야 함
■ 행별로 하나의 결과를 반환

- 실무에서 가장 많이 쓰는 데이터 타입은 숫자, 문자, 날짜 타입임
- 열의 데이터 타입은 Oracle SQL Developer에서 확인할 수 있음

▶ 문자 타입 함수
- 문자 타입 함수는 주로 데이터 조작에 쓰임

문자함수 종류

SELECT last_name,
       LOWER(last_name) LOWER적용,
       UPPER(last_name) UPPER적용,
       email,
       INITCAP(email) INITCAP적용
FROM employees;

스크립트 출력

■ SUBSTR : 지정한 길이만큼 문자열 추출하기
- SUBSTR 함수는 데이터에서 지정된 길이만큼 문자열을 추출할 때 사용함

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

-- 1번 모든 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER를 출력하세요.
SELECT employee_id, first_name, last_name, email, phone_number
FROM employees;

-- 2번 JOB_ID가 'SH_CLERK'인 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER를 출력하세요.
SELECT employee_id, first_name, last_name, email, phone_number
FROM employees
WHERE job_id = 'SH_CLERK';

-- 3번 DEPARTMENT_ID가 50인 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY를 내림차순으로 출력하세요.
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 50
ORDER BY salary desc;

-- 4번 DEPARTMENT_ID가 80 또는 90인 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY를 출력하세요.
SELECT employee_id, first_name, last_name, salary
from employees
WHERE department_id in (80,90);

-- 5번 COMMISSION_PCT가 NULL이 아닌 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, COMMISSION_PCT를 출력하세요.

SELECT employee_id, first_name, last_name, commission_pct
FROM employees
WHERE commission_pct is not NULL;

-- 6번 DEPARTMENT_ID가 80인 사원 중 SALARY가 10000 이상인 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY를 출력하세요.
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 80
AND salary >= 10000;

-- 7번 HIRE_DATE가 '2017-03-07' 이후인 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE를 출력하세요.
SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE hire_date > '2003-06-17';

-- 8번 JOB_ID가 'MK_REP'이거나 'PU_CLERK'인 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID를 출력하세요.
SELECT employee_id, first_name, last_name, job_id
FROM employees
WHERE job_id in ('MK_REP', 'PU_CLERK');

-- 9번 SALARY가 5000 이상 10000 이하인 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, COMMISSION_PCT를 출력하세요.
SELECT employee_id, first_name, last_name, salary, commission_pct
FROM employees
WHERE salary >= 5000
AND salary <= 10000;

-- 10번 DEPARTMENT_ID가 20, 50, 80인 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID를 오름차순으로 출력하세요.
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id in (20, 50, 80)
ORDER BY department_id ASC;

-- 11번 JOB_ID가 'SA_REP'이 아닌 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID를 출력하세요.
SELECT employee_id, first_name, last_name, job_id
FROM employees
WHERE job_id != 'SA_REP';

-- 12번 MANAGER_ID가 NULL인 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID를 출력하세요.
SELECT employee_id, first_name, last_name, manager_id
FROM employees
WHERE manager_id is NULL;

-- 13번 SALARY가 10000 이상이고 DEPARTMENT_ID가 80인 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID를 출력하세요.
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE salary >= 10000
AND department_id = 80;

-- 14번 JOB_ID가 'SA_REP'이거나 'ST_CLERK'이고, SALARY가 10000 이상인 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY를 출력하세요.
SELECT employee_id, first_name, last_name, job_id, salary
FROM employees
WHERE job_id in ('SA_REP', 'ST_CLERK')
AND salary >= 10000;

-- 15번 FIRST_NAME에 'a'가 포함된 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, DEPARTMENT_ID를 출력하세요.
SELECT employee_id, first_name, last_name, job_id, department_id
FROM employees
WHERE first_name LIKE '%a%';

-- 16번 FIRST_NAME에 'e'가 2개 이상 포함된 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY를 출력하세요.
SELECT employee_id, first_name, last_name, job_id, salary
FROM employees
WHERE first_name LIKE '%e%e%';

-- 17번 JOB_ID에 'CLERK'이 포함된 사원의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, DEPARTMENT_ID를 출력하세요.
SELECT employee_id, first_name, last_name, job_id, department_id
FROM employees
WHERE job_id LIKE '%CLERK';

■ LPAD, RPAD : 특정 문자로 자릿수 채우기
- LPAD는 왼쪽부터 특정 문자로 자릿수를 채우는 함수고
  RPAD는 오른쪽부터 특정 문자로 자릿수를 채우는 함수
- 리포트나 프로그래밍에서 데이터 값의 자릿수를 맞추어야 할 때 유용함

LPAD('문자열' or 열 이름, 만들어질 자릿수, '채워질 문자')

▶ 숫자 타입 함수
■ ROUND : 숫자 반올림
- ROUND는 특정 소수점을 반올림하고 나머지를 버리는 함수

SELECT salary,
       salary/30 일급,
       ROUND(salary/30, 0)  적용결과0,
       ROUND(salary/30, 1)  적용결과1,
       ROUND(salary/30, -1) 적용결과MINUS1
FROM employees;

■ TRUNC : 숫자 절삭하기
- TRUNC는 지정한 숫자 자리에서 숫자를 절삭(숫자를 버림)하는 함수

SELECT salary,
       salary/30 일급,
       TRUNC(salary/30, 0)  적용결과0,
       TRUNC(salary/30, 1)  적용결과1,
       TRUNC(salary/30, -1) 적용결과MINUS1
FROM employees;

▶ 날짜 타입 함수
- 데이터를 다루다 보면 날짜를 계산하고 처리해야 하는 경우가 많음
- 이때 사용하는 것이 날짜 타입 함수
- 날짜 타입 함수는 날짜를 연산하여 숫자로 출력하는 MONTHS_BETWEEN 외에는
  모두 결과를 날짜 타입으로 출력

- 날짜를 연산하는 다양한 방법을 살펴보자
- 오늘 날짜와 시간, 오늘 날짜에서 1을 더한 값, 1을 뺀 값, 2017년 12월 2일에서 2017년 12월 1일을 뺀 값,
  오늘 날짜에서 13시간을 더한 값을 출력해보자
- SYSDATE는 오라클 데이터베이스 시스템이 설치되어 있는 시스템의 현재 날짜와 시간을 반환하는 함수

SELECT TO_CHAR(SYSDATE, 'YY/MM/DD/HH24:MI') 오늘날짜,
       SYSDATE +1 더하기1,
       SYSDATE -1 빼기1,
       TO_DATE('20171202')-TO_DATE('20171201') 날짜빼기,
       SYSDATE + 13/24 시간더하기
FROM DUAL;

스크립트 출력

- 한글판 오라클 익스프레스는 기본으로 년(YY)/월(MM)/일(DD) 형태로 출력

■ MONTHS_BETWEEN : 두 날짜 사이의 개월 수 계산하기
- MONTHS_BETWEEN 함수는 날짜와 날짜 사이의 개월 수를 계산함
- 결과는 음수나 양수가 될 수 있음
- 이후 문법의 '날짜' 부분에는 날짜 데이터의 열 이름을 기술해도 됨

MONTHS_BETWEEN(날짜, 날짜)

SELECT SYSDATE, hire_date, MONTHS_BETWEEN(SYSDATE, hire_date) 적용결과
FROM employees
WHERE department_id = 100;

스크립트 출력

SELECT hire_date,
       ADD_MONTHS(hire_date, 3) 더하기_적용결과,
       ADD_MONTHS(hire_date, -3) 빼기_적용결과
FROM employees
WHERE employee_id BETWEEN 100 AND 106;

스크립트 출력

- employees 테이블에서 employee_id가 100과 106 사이인 직원의 hire_date에서 가장 가까운
  금요일의 날짜가 언제인지 문자로 지정해서 출력하고, 숫자로도 지정해서 출력하세요.

SELECT hire_date,
       NEXT_DAY(hire_date, '금요일') 적용결과_문자지정,
       NEXT_DAY(hire_date, 6) 적용결과_숫자지정
FROM employees
WHERE employee_id BETWEEN 100 AND 106;

스크립트 출력

■ LAST_DAY : 돌아오는 월의 마지막 날짜 계산하기
- LAST_DAY는 월의 마지막 날짜를 계산해서 출력하는 함수

LAST_DAY (날짜)

- employees 테이블에서 employee_id가 100과 106 사이인 직원의 hire_date를
  기준으로 해당 월의 마지막 날짜를 출력해보세요

SELECT hire_date,
       LAST_DAY(hire_date) 적용결과
FROM employees
WHERE employee_id BETWEEN 100 AND 106;

스크립트 출력

ROUND or TRUNC(날짜, 지정 값)

SELECT hire_date,
       ROUND(hire_date, 'MONTH') 적용결과_ROUND_M,
       ROUND(hire_date, 'YEAR')  적용결과_ROUND_Y,
       TRUNC(hire_date, 'MONTH') 적용결과_TRUNC_M,
       TRUNC(hire_date, 'YEAR')  적용결과_TRUNC_Y
FROM employees
WHERE employee_id BETWEEN 100 AND 106;

스크립트 출력

- 날짜 함수는 연차나 주차 등 달력 형태의 계산이 필요할 때 주로 사용됨
- 예를 들어 날짜별 예약 접수 현황, 월 단위 또는 주 단위에 가장 많이 팔린 상품,
  고객의 최초 등록 날짜로부터 지금까지 거래한 총 기간 등 날짜와 관계된 데이터 값을 날짜 함수를 이용해 연산함
- 일별, 월별, 요일별 매출액 추이도 계산할 수 있음
- 현재 날짜를 기준으로 가장 오래 거래한 고객이 누구인지 찾아낼 수 있고
  거래를 유지한 기간을 계산해서 고객의 등급을 매길 때도 사용할 수 있음


▶ 변환 함수
- 오라클 데이터베이스 시스템은 각 열에 대해 데이터 타입을 규정하고 있음
- SQL 문을 실행하기 위해 데이터 값의 데이터 타입을 변환해야 할 때도 있음
- 이럴 때 사용하는 것이 변환 함수임
- 다만 오라클 데이터베이스 시스템은 정해진 열 데이터 형식에 대해 다른 데이터 타입의 데이터를
  사용하는 것을 일부 허용함
- 데이터 타입 변환은 오라클 데이터베이스 시스템에 의해 자동으로(암시적으로) 혹은
  사용자에 의해서 수동으로(명시적으로) 실행될 수 있음

■ 자동 데이터 타입 변환
- SQL 문을 조작할 때 오라클 데이터베이스 시스템은 특정한 경우에 데이터 타입을 자동으로 변환
- 예를 들어 오라클 데이터베이스 시스템에 VARCHAR2 타입으로 입력되어 있는
  데이터 값 100은 NUMBER 타입으로 자동 변환되어 산술 계산될 수 있음
- 숫자 타입 데이터 값 100은 VARCHAR2 타입으로 자동 변환되어 저장될 수 있음
- 이런 경우 데이터 타입을 목표(target) 값의 데이터 타입으로 변환할 수 있는 경우에만 SQL문이 올바르게 수행됨
- 예를들어 이런 문자열을 숫자로 나타낼 수 있는 경우에만 VARCHAR2 타입이 NUMBER 타입으로 변환
- 문자열이 데이터베이스 시스템에 설정된 날짜 데이터 타입과 같은 경우에만 VARCHAR2 타입이 DATE 타입으로 변환

- 자동 데이터 타입 변환 사례를 통해 알아보자

SELECT 1 + '2' ← 작은따옴표('')로 묵었기 때문에 문자 데이터 타입 
FROM DUAL;

■ 수동 데이터 타입 변환
- SQL은 사용자가 데이터 타입의 값을 다른 데이터 타입의 값으로 변환할 수 있도록 변환 함수를 제공함

함수	설명
TO_CHAR	숫자, 문자, 날짜 값을 지정 형식의 VARCHAR2 타입으로 변환한다
TO_NUMBER	문자를 숫자 타입으로 변환한다
TO_DATE	날짜를 나타내는 문자열을 지정 형식의 날짜 타입으로 변환한다.

- 날짜 형식을 변환하는 예를 몇 가지 살펴보자

SELECT TO_CHAR(SYSDATE, 'YY'),
       TO_CHAR(SYSDATE, 'YYYY'),
       TO_CHAR(SYSDATE, 'MM'),
       TO_CHAR(SYSDATE, 'MON'),
       TO_CHAR(SYSDATE, 'YYYYMMDD') 응용적용1,
       TO_CHAR(TO_DATE('20171008'), 'YYYYMMDD') 응용적용2
FROM dual;

- 다음은 시간 지정 혁식에 대해 알아보자
- 시간 지정 형식은 날짜의 시간 부분에 대해 표현함
- 날짜 지정 형식을 시간 지정 형식으로 지정하는 예를 살펴보자

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS PM') 시간형식,
       TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MI:SS PM') 날짜와시간조합
FROM dual;

- 기타 형식 요소를 활용해 날짜와 시간 데이터를 출력해보자

SELECT TO_CHAR(SYSDATE, 'HH-MI-SS PM') 시간형식,
       TO_CHAR(SYSDATE, ' "날짜:" YYYY/MM/DD "시각:" HH:MI:SS PM') 날짜와시각표현
FROM dual;

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

-- 1번 EMPLOYEE FIRST_NAME 열의 모든 문자를 대문자로 변환하는 쿼리를 작성하시오.
SELECT first_name, 
       UPPER(first_name)
FROM employees;

-- 2번 FIRST_NAME과 LAST_NAME 열을 합쳐서 FULL_NAME 열로 출력하는 쿼리를 작성하시오.
SELECT first_name, last_name,
       first_name ||' '|| last_name as full_name
from employees;

-- 3번 DEPARTMENT_NAME 열의 문자열 길이를 구하는 쿼리를 작성하시오.
SELECT department_name,
       LENGTH(department_name)
FROM departments;

-- 4번 JOB_TITLE 열에서 'Manager'라는 단어가 포함된 행을 찾는 쿼리를 작성하시오.
SELECT job_title
FROM jobs
WHERE UPPER(JOB_TITLE) LIKE '%MANAGER%';

-- 5번 PHONE_NUMBER 열의 값에서 마지막 4자리 숫자만 추출하는 쿼리를 작성하시오.
SELECT phone_number,
       SUBSTR(phone_number, -4)
FROM employees;

-- 6번 EMAIL 열의 값에서 첫번째 문자만 대문자로 변환하는 쿼리를 작성하시오.
SELECT email,
       INITCAP(email)
FROM employees;

-- 7번 HIRE_DATE 열의 값에서 년, 월, 일을 각각 추출하는 쿼리를 작성하시오.
SELECT hire_date,
       TO_CHAR(hire_date, 'YYYY'),
       TO_CHAR(hire_date, 'MM'), 
       TO_CHAR(hire_date, 'DD') 
FROM employees;