2024-07-30 DAY15

국비교육 15일차

<기본적인 SQL 문법>

1. 데이터 조회 조건 설정
   /*

• 1. EMPLOYEES 테이블에서 DEPARTMENT_ID가 100이고,
• job_id가 FI_MGR인 사람의 모든 데이터 조회
  /
  SELECT
  FROM EMPLOYEES e
  WHERE DEPARTMENT_ID = 100
  AND JOB_ID = 'FI_MGR';
  설명:
  SELECT *: 모든 열을 조회합니다.
  WHERE DEPARTMENT_ID = 100: DEPARTMENT_ID가 100인 행을 필터링합니다.
  AND JOB_ID = 'FI_MGR': JOB_ID가 'FI_MGR'인 행을 필터링합니다. 두 조건을 모두 만족하는 행을 조회합니다.

2. 특정 열 선택
   /*

• 2. EMPLOYEES 테이블에서 FIRST_NAME이 Guy인
• 사람의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
• JOB_ID 조회
  */
  SELECT FIRST_NAME, EMPLOYEE_ID, LAST_NAME, JOB_ID
  FROM EMPLOYEES e
  WHERE FIRST_NAME = 'Guy';
  설명:
  FIRST_NAME = 'Guy': FIRST_NAME이 'Guy'인 행을 조회합니다.

3. 복합 조건 사용

/*

• 3. EMPLOYEES 테이블에서 DEPARTMENT_ID가 50이고,
• MANAGER_ID가 121인 사람의 EMPLOYEE_ID,
• FIRST_NAME, LAST_NAME, JOB_ID 조회
  */
  SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID
  FROM EMPLOYEES e
  WHERE DEPARTMENT_ID = 50
  AND MANAGER_ID = 121;
  설명:
  두 개의 조건을 사용하여 DEPARTMENT_ID가 50이고 MANAGER_ID가 121인 행을 선택합니다.

<SQL 기능 설명>
1. 알리아스 (Alias) 사용

열의 별칭

-- as : 별칭, 알리아스
SELECT EMPLOYEE_ID AS "ID", SALARY "SAL"
FROM EMPLOYEES e;
설명:
AS "ID": EMPLOYEE_ID 열에 대한 별칭을 "ID"로 지정합니다. 출력 결과에 "ID"라는 이름으로 표시됩니다.
SALARY "SAL": SALARY 열에 대한 별칭을 "SAL"로 지정합니다. AS를 생략할 수도 있습니다.
또 ""를 생략하고 쓸 수 있다.

테이블의 별칭

FROM EMPLOYEES e -- e가 알리아스라고 불린다. 테이블 앞에 AS는 안 돼. 예) AS e는 안 된다
설명:
e는 EMPLOYEES 테이블의 알리아스입니다. 테이블 이름을 줄여서 쿼리를 간결하게 만듭니다.
AS 키워드는 테이블의 알리아스에 사용되지 않습니다.

2. 중복 제거
   DISTINCT 사용

-- distinct : 중복 제거
SELECT DISTINCT DEPARTMENT_ID -- NULL은 없는 거다. 따라서 11개의 부서, 하지만 행은 12개
FROM EMPLOYEES e;
설명:
DISTINCT: 결과에서 중복된 값을 제거합니다.
DEPARTMENT_ID 열의 중복 값을 제거하여 고유한 부서 ID를 가져옵니다. NULL 값은 고유한 값으로 간주되어 포함됩니다.

SELECT DISTINCT JOB_ID
FROM EMPLOYEES e;
설명:
JOB_ID 열의 중복된 값을 제거하고 고유한 직무 ID를 반환합니다.

3. 비교 연산자

   , <, >=, <= 사용

-- >, <, >=, <=
SELECT *
FROM EMPLOYEES e
WHERE SALARY >= 5000;
설명:
SALARY >= 5000: SALARY 열의 값이 5000 이상인 행을 조회합니다.

3-1. OR 연산자
-- or : 둘 중 하나만 만족해도 데이터를 가져온다. 근데 거의 and 조건을 쓴다, 속도가 느리다
SELECT EMPLOYEE_ID, DEPARTMENT_ID, MANAGER_ID, LAST_NAME
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 50
OR MANAGER_ID = 100;
설명:
OR: 두 조건 중 하나라도 만족하면 해당 행을 반환합니다. 여기서는 DEPARTMENT_ID가 50이거나 MANAGER_ID가 100인 행을 선택합니다.

3-2.NOT 연산자

-- not : ()값을 제외하는 값
SELECT EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES e
WHERE NOT(DEPARTMENT_ID = 50);
설명:
NOT: 괄호 안의 조건을 만족하지 않는 행을 선택합니다. DEPARTMENT_ID가 50이 아닌 행을 반환합니다.

-- not = <>
SELECT EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES e
WHERE (DEPARTMENT_ID <> 50);
설명:
<>: 같지 않다는 의미의 연산자입니다. DEPARTMENT_ID가 50이 아닌 행을 선택합니다.

-- not = !=
SELECT EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES e
WHERE DEPARTMENT_ID != 50;
설명:
!=: 같지 않다는 의미로, DEPARTMENT_ID가 50이 아닌 행을 선택합니다.

4. 범위 및 집합 사용
   BETWEEN 사용

-- salary 4000~8000 -> between A and B
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES e
WHERE SALARY >= 4000
AND SALARY <= 8000;

-- 위에랑 똑같은 코드
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES e
WHERE SALARY BETWEEN 4000 AND 8000;
설명:
BETWEEN A AND B: BETWEEN 연산자를 사용하여 SALARY 값이 4000 이상 8000 이하인 행을 선택합니다. BETWEEN은 주어진 범위에 포함되는 값들을 조회하는 데 사용됩니다.

OR 사용하여 여러 조건 확인
-- 급여가 6500 또는 7700 또는 13000인 사람 출력
-- employee_id, first_name, salary
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES e
WHERE SALARY = 6500
OR SALARY = 7700
OR SALARY = 13000;
설명:
OR: SALARY 값이 6500, 7700 또는 13000인 행을 선택합니다. 여러 조건 중 하나라도 만족하면 해당 행을 반환합니다.

IN 사용하여 조건 집합 확인
-- in() = or와 동일
-- 아래 코드는 위의 코드와 동일
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES e
WHERE SALARY IN (6500, 7700, 13000);
설명:
IN (value1, value2, ...): IN 연산자를 사용하여 여러 값 중 하나와 일치하는 행을 선택합니다. OR 조건을 간결하게 표현할 수 있습니다.

LIKE 연산자와 NULL 값 처리
-LIKE 연산자 사용 : 뒤에 항상 문자열만 옵니다
LIKE 사용하여 패턴 매칭

-- like -> D로 시작하는 사람, 속도가 느리다
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES e
WHERE FIRST_NAME LIKE 'D%'; -- 첫번째에 D가 오고 나머지는 상관없으면 % 사용
설명:
LIKE 'D%': FIRST_NAME이 'D'로 시작하는 행을 선택합니다. %는 0개 이상의 문자를 의미하는 와일드카드입니다.

-- like -> d로 끝나는 사람
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES e
WHERE FIRST_NAME LIKE '%d';
설명:
LIKE '%d': FIRST_NAME이 'd'로 끝나는 행을 선택합니다.

-- like -> a% = 3번째 알파벳이 a이고 뒤에는 뭐가 와도 상관없다
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES e
WHERE FIRST_NAME LIKE 'a%';
설명:
LIKE '_a%': FIRST_NAME의 세 번째 문자가 'a'이고, 뒤에 어떤 문자가 와도 상관없는 행을 선택합니다. 는 단일 문자를 의미하는 와일드카드입니다.

2. NULL 값 처리

IS NULL 사용
-- is null
SELECT *
FROM EMPLOYEES e
WHERE DEPARTMENT_ID IS NULL;
설명:
IS NULL: DEPARTMENT_ID가 NULL인 행을 선택합니다.

IS NOT NULL 사용
-- commission_pct is null
SELECT *
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS NULL;

-- is not null
SELECT *
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS NOT NULL;
설명:
IS NOT NULL: COMMISSION_PCT가 NULL이 아닌 행을 선택합니다. NULL 값이 아닌 모든 행을 선택할 때 사용합니다.

정렬 및 집계 함수
1. 데이터 정렬

ORDER BY 사용
-- order by asc -> 오름차순
SELECT *
FROM EMPLOYEES e
ORDER BY SALARY ASC;
설명:
ORDER BY SALARY ASC: SALARY 열을 기준으로 오름차순으로 데이터를 정렬합니다.

SELECT *
FROM EMPLOYEES e
ORDER BY FIRST_NAME, LAST_NAME;
설명:
ORDER BY FIRST_NAME, LAST_NAME: FIRST_NAME 열을 기준으로 먼저 정렬하고, 같은 이름이 있을 경우 LAST_NAME 열로 추가 정렬합니다.

-- order by desc -> 내림차순
SELECT *
FROM EMPLOYEES e
ORDER BY SALARY DESC;
설명:
ORDER BY SALARY DESC: SALARY 열을 기준으로 내림차순으로 데이터를 정렬합니다.

2. 집계 함수

SUM 사용
-- sum -> 합계
SELECT SUM(SALARY)
FROM EMPLOYEES e;
설명:
SUM(SALARY): SALARY 열의 모든 값을 더한 합계를 반환합니다.

COUNT 사용
-- count() -> 카운트
SELECT COUNT(DEPARTMENT_ID) AS "cnt" -- 모든 행의 갯수, null값이 있는 행은 카운트하지 않는다
FROM EMPLOYEES e;
설명:
COUNT(DEPARTMENT_ID): DEPARTMENT_ID 값이 NULL이 아닌 행의 개수를 세어 반환합니다.
SELECT COUNT(FIRST_NAME), COUNT(DISTINCT FIRST_NAME) -- 중복된 first_name을 센다
FROM EMPLOYEES e;
설명:
COUNT(FIRST_NAME): FIRST_NAME 열의 NULL이 아닌 행 개수를 반환합니다.
COUNT(DISTINCT FIRST_NAME): FIRST_NAME 열의 고유한 값 개수를 반환합니다.

1. 평균 연봉 조회

SELECT AVG(SALARY)
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 80
;
설명 :
AVG(SALARY): SALARY 컬럼의 평균값을 계산합니다.
FROM EMPLOYEES e: EMPLOYEES 테이블에서 데이터를 조회합니다.
WHERE DEPARTMENT_ID = 80: 부서 번호가 80인 직원들의 평균 연봉을 계산합니다.

2. 최대 연봉 조회

SELECT MAX(SALARY)
FROM EMPLOYEES e;
설명 :
MAX(SALARY): SALARY 컬럼의 최대값을 반환합니다.
FROM EMPLOYEES e: EMPLOYEES 테이블에서 최대 연봉을 조회합니다.

3. 가장 최근에 입사한 사람

SELECT MAX(HIRE_DATE)
FROM EMPLOYEES e;
설명 :
MAX(HIRE_DATE): HIRE_DATE 컬럼의 최대값, 즉 가장 최근 입사일을 반환합니다.
FROM EMPLOYEES e: EMPLOYEES 테이블에서 가장 최근 입사일을 조회합니다.

4. 가장 오래된 입사일

SELECT MIN(HIRE_DATE)
FROM EMPLOYEES e;
설명 :
MIN(HIRE_DATE): HIRE_DATE 컬럼의 최소값, 즉 가장 오래된 입사일을 반환합니다.
FROM EMPLOYEES e: EMPLOYEES 테이블에서 가장 오래된 입사일을 조회합니다.

• dual테이블(오라클에만 존재)

  • oracle에서 기본으로 제공하는 dummy table = 의미없는 테이블

  • 간단하게 함수를 이용해서 계산 결과값을 확인 할 때 사용하는 테이블

  • dual 테이블은 사용자가 함수를 실행할때 임시로 사용하는 데 적합하다.

  • 함수에 대한 쓰임을 알고 싶을때 특정 테이블을 생성할 필요 없이 dual 테이블을
    이용하여 함수의 값을 리턴 받을 수 있다.

5. 절대값 계산

SELECT ABS(-33)
FROM dual;
설명 :
ABS(-33): 숫자의 절대값을 계산합니다. -33의 절대값은 33입니다.
FROM dual: dual 테이블은 Oracle에서 단일 행을 가진 더미 테이블로 함수 테스트에 사용됩니다.

6. 반올림

SELECT ROUND(0.123), ROUND(0.5678)
FROM dual;
설명 :
ROUND(0.123): 0.123을 가장 가까운 정수로 반올림합니다 (결과: 0).
ROUND(0.5678): 0.5678을 가장 가까운 정수로 반올림합니다 (결과: 1).

7. 절삭

SELECT TRUNC(1234.5678) trunc
FROM dual;
설명 :
TRUNC(1234.5678): 소수점을 제거하여 정수 부분만 반환합니다 (결과: 1234).

8. 소수점 자릿수 절삭

SELECT TRUNC(1234.5678, 2) trunc
FROM dual;
설명:
TRUNC(1234.5678, 2): 소수점 이하 두 자리까지 유지합니다 (결과: 1234.56).

9. 특정 자리수로 절삭

SELECT TRUNC(1234.5678, -1) trunc
FROM dual;
설명 :
TRUNC(1234.5678, -1): 소수점 위 자리로 절삭합니다 (결과: 1230).

10. 연봉이 12000 초과인 직원 조회

SELECT LAST_NAME, SALARY
FROM EMPLOYEES e
WHERE SALARY > 12000
ORDER BY SALARY ASC;
설명 :
WHERE SALARY > 12000: 연봉이 12000을 초과하는 직원들만 선택합니다.
ORDER BY SALARY ASC: 연봉을 오름차순으로 정렬합니다.

11. 사원번호가 176인 직원의 LAST_NAME 및 부서 번호 조회

SELECT LAST_NAME, DEPARTMENT_ID, EMPLOYEE_ID
FROM EMPLOYEES e
WHERE EMPLOYEE_ID = 176;
설명 :
WHERE EMPLOYEE_ID = 176: 사원번호가 176인 직원의 정보를 조회합니다.

12. 연봉이 5000에서 12000 사이가 아닌 직원 조회

SELECT LAST_NAME, SALARY
FROM EMPLOYEES e
WHERE NOT SALARY BETWEEN 5000 AND 12000;
설명 :
WHERE NOT SALARY BETWEEN 5000 AND 12000: 연봉이 5000에서 12000 사이가 아닌 직원들만 조회합니다.

13. 20번 및 50번 부서의 직원 조회

SELECT LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES e
WHERE DEPARTMENT_ID IN (20, 50)
ORDER BY LAST_NAME ASC;
설명 :
WHERE DEPARTMENT_ID IN (20, 50): 부서 번호가 20 또는 50인 직원들만 조회합니다.
ORDER BY LAST_NAME ASC: LAST_NAME을 알파벳 순으로 정렬합니다.

14. 20번 및 50번 부서에서 연봉이 5000~12000 사이인 직원 조회

SELECT LAST_NAME, SALARY
FROM EMPLOYEES e
WHERE DEPARTMENT_ID IN (20, 50)
AND 5000 < SALARY
AND SALARY < 12000;
설명:
AND 5000 < SALARY AND SALARY < 12000: 연봉이 5000 초과 12000 미만인 직원들만 조회합니다.

15. LAST_NAME 첫 글자가 A인 직원 조회

SELECT LAST_NAME
FROM EMPLOYEES e
WHERE LAST_NAME LIKE 'A%';
설명 :
WHERE LAST_NAME LIKE 'A%': LAST_NAME이 'A'로 시작하는 직원들만 조회합니다.

16. 매니저가 없는 직원 조회

SELECT LAST_NAME, JOB_ID, MANAGER_ID
FROM EMPLOYEES e
WHERE MANAGER_ID IS NULL;
설명 :
WHERE MANAGER_ID IS NULL: MANAGER_ID가 NULL인 직원들, 즉 매니저가 없는 직원들만 조회합니다.

17. 문자열 연결

SELECT CONCAT('hello', ' bye'), CONCAT('good', ' bad'),
'good' || ' bad'
FROM dual;
설명 :
CONCAT('hello', ' bye'): 두 문자열을 연결합니다 (결과: 'hello bye').
'good' || ' bad': 문자열 연결의 또 다른 방법 (결과: 'good bad').

18. 첫 글자를 대문자로 변환

SELECT INITCAP('good morning')
FROM dual;
설명 :
INITCAP('good morning'): 문자열의 첫 글자를 대문자로 변환합니다 (결과: 'Good Morning').

19. 대/소문자 변환 , 문자열은 항상 ''를 사용한다!

SELECT LOWER('GOOD'), UPPER('good')
FROM dual;
설명 :
LOWER('GOOD'): 문자열을 소문자로 변환합니다 (결과: 'good').
UPPER('good'): 문자열을 대문자로 변환합니다 (결과: 'GOOD').

20. LPAD - 문자열 앞에 문자 추가

SELECT LPAD('good', 6), LPAD('good', 7, '#'), LPAD('good', 8, 'A')
FROM dual;
설명 :
LPAD('good', 6): 'good' 문자열을 총 6자리로 맞추기 위해 왼쪽에 공백을 추가합니다 (결과: ' good').
LPAD('good', 7, '#'): 'good' 문자열을 총 7자리로 맞추기 위해 왼쪽에 '#'을 추가합니다 (결과: '###good').
LPAD('good', 8, 'A'): 'good' 문자열을 총 8자리로 맞추기 위해 왼쪽에 'A'를 추가합니다 (결과: 'AAAAgood').

21. RPAD - 문자열 뒤에 문자 추가

SELECT RPAD('good', 6), RPAD('good', 7, '#'), RPAD('good', 8, 'A')
FROM dual;
설명 :
RPAD('good', 6): 'good' 문자열을 총 6자리로 맞추기 위해 오른쪽에 공백을 추가합니다 (결과: 'good ').
RPAD('good', 7, '#'): 'good' 문자열을 총 7자리로 맞추기 위해 오른쪽에 '#'을 추가합니다 (결과: 'good###').
RPAD('good', 8, 'A'): 'good' 문자열을 총 8자리로 맞추기 위해 오른쪽에 'A'를 추가합니다 (결과: 'goodAAAA').

22. 현재 시간 조회

SELECT SYSDATE
FROM dual;
설명 :
SYSDATE: 현재 시스템 날짜와 시간을 반환합니다.

23. 현재 날짜에서 7개월 후의 날짜 조회

SELECT ADD_MONTHS(SYSDATE, 7)
FROM dual;
설명:
ADD_MONTHS(SYSDATE, 7): 현재 날짜에서 7개월을 더한 날짜를 반환합니다.

24. 현재 달의 마지막 날짜 조회

SELECT LAST_DAY(SYSDATE)
FROM dual;
설명 :
LAST_DAY(SYSDATE): 현재 날짜가 포함된 달의 마지막 날짜를 반환합니다.

25. 날짜 및 시간 간격 계산

SELECT SYSDATE + (INTERVAL '1' YEAR)
, SYSDATE + (INTERVAL '1' MONTH)
, SYSDATE + (INTERVAL '1' DAY)
, SYSDATE + (INTERVAL '1' HOUR)
, SYSDATE + (INTERVAL '1' MINUTE)
, SYSDATE + (INTERVAL '1' SECOND)
FROM dual;
설명 :
SYSDATE + (INTERVAL '1' YEAR): 현재 날짜에서 1년을 더한 날짜를 반환합니다.
SYSDATE + (INTERVAL '1' MONTH): 현재 날짜에서 1개월을 더한 날짜를 반환합니다.
SYSDATE + (INTERVAL '1' DAY): 현재 날짜에서 1일을 더한 날짜를 반환합니다.
SYSDATE + (INTERVAL '1' HOUR): 현재 시간에서 1시간을 더한 시간을 반환합니다.
SYSDATE + (INTERVAL '1' MINUTE): 현재 시간에서 1분을 더한 시간을 반환합니다.
SYSDATE + (INTERVAL '1' SECOND): 현재 시간에서 1초를 더한 시간을 반환합니다.

26. 날짜를 문자열로 변환

SELECT TO_CHAR(SYSDATE, 'yyy/mm/dd'), SYSDATE
FROM dual;
설명:
TO_CHAR(SYSDATE, 'yyy/mm/dd'): 현재 날짜를 'yyyy/mm/dd' 형식의 문자열로 변환합니다.

27. 다양한 날짜 형식으로 변환
    SELECT TO_CHAR(SYSDATE, 'yyyymmdd')
    , TO_CHAR(SYSDATE, 'yyy/mm/dd')
    , TO_CHAR(SYSDATE, 'yyyy-mm-dd')
    , TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:MI:SS')
    FROM dual;
    설명:
    TO_CHAR(SYSDATE, 'yyyymmdd'): 날짜를 'yyyymmdd' 형식으로 변환합니다.
    TO_CHAR(SYSDATE, 'yyy/mm/dd'): 날짜를 'yyy/mm/dd' 형식으로 변환합니다.
    TO_CHAR(SYSDATE, 'yyyy-mm-dd'): 날짜를 'yyyy-mm-dd' 형식으로 변환합니다.
    TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:MI
    '): 날짜와 시간을 'yyyy-mm-dd HH24:MI
    ' 형식으로 변환합니다.

28. 문자열을 날짜로 변환

SELECT TO_DATE('2024/07/30', 'yyyy/mm/dd')
FROM dual;
TO_DATE('2024/07/30', 'yyyy/mm/dd'): 문자열을 'yyyy/mm/dd' 형식의 날짜로 변환합니다.

29. NULL 값을 다른 값으로 변경

SELECT FIRST_NAME, LAST_NAME, COMMISSION_PCT
, NVL(COMMISSION_PCT, 0) commission
FROM EMPLOYEES e;
설명 :
NVL(COMMISSION_PCT, 0): COMMISSION_PCT가 NULL인 경우 0으로 변경합니다.

30. DECODE 함수 사용

SELECT DEPARTMENT_ID, DECODE(DEPARTMENT_ID, 20, 'MA', 60, 'IT', 90, 'EX', 'ETC')
FROM DEPARTMENTS d;
설명 :
DECODE(DEPARTMENT_ID, 20, 'MA', 60, 'IT', 90, 'EX', 'ETC'): DEPARTMENT_ID 값에 따라 다른 문자열을 반환합니다. 20이면 'MA', 60이면 'IT', 90이면 'EX', 그 외에는 'ETC'.

31. CASE 문 사용

SELECT FIRST_NAME, DEPARTMENT_ID
, CASE
WHEN DEPARTMENT_ID = 20 THEN 'MA'
WHEN DEPARTMENT_ID = 60 THEN 'IT'
WHEN DEPARTMENT_ID = 90 THEN 'EX'
ELSE 'ETC'
END "departments"
FROM EMPLOYEES e;
설명 :
CASE WHEN ... THEN ... ELSE ... END: DEPARTMENT_ID 값에 따라 다른 문자열을 반환하는 구문입니다. 20이면 'MA', 60이면 'IT', 90이면 'EX', 그 외에는 'ETC'.

32. 연봉 오름차순 정렬

SELECT *
FROM EMPLOYEES e
ORDER BY SALARY;
설명 :
ORDER BY SALARY: 연봉을 기준으로 오름차순으로 정렬합니다.