0. 준비 (한 번만 실행)

-- 스키마 우선순위 설정
SET search_path TO codeit;
COMMIT;

-- 테이블 메타데이터 확인 예시
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'codeit' AND table_name = 'employee';

주의: SET search_path TO codeit;를 하지 않았다면 반드시 codeit.employee처럼 스키마명.테이블명을 사용하십시오.

1. SELECT 기본

1-1. 전체 컬럼 조회

-- search_path가 설정된 경우
SELECT * FROM employee;

-- search_path 미설정 시(권장)
SELECT * FROM codeit.employee;

*는 급할 때만 쓰십시오. 실무에선 네트워크/메모리 낭비를 줄이기 위해 필요한 컬럼만 명시합니다.

1-2. 특정 컬럼만 조회

SELECT
  emp_id, emp_name, emp_no, email, phone,
  dept_code, job_code, sal_level, salary, bonus, manager_id,
  hire_date, ent_date, ent_yn
FROM employee;

이 방식의 장점:

• 반환 컬럼 순서가 고정됩니다.
• 불필요한 컬럼 전송을 줄여 성능에 유리합니다.

1-3. 테이블 접두어로 명확히 표기

SELECT employee.emp_id, employee.emp_name, employee.email
FROM employee;

여러 테이블을 JOIN할 때 컬럼 출처를 명확히 하는 습관을 들이십시오.

2. 와일드카드와 혼합 사용

-- 특정 컬럼 + 나머지 전체
SELECT emp_name, employee.* FROM employee;

실습·디버깅 시에는 편리하지만, 운영 쿼리에는 지양하십시오(스키마 변경 시 리스크)

3. AS: 별칭(Alias)

3-1. 컬럼 별칭

SELECT
  emp_id     AS id,
  emp_name   AS name,
  salary     AS base_salary,
  bonus      AS bonus_rate
FROM employee;

3-2. 테이블 별칭

SELECT e.emp_id, e.emp_name, e.salary
FROM employee AS e;

• 별칭은 가독성과 JOIN 시 충돌 최소화에 유리합니다.
• PostgreSQL에서는 AS 생략 가능하지만, 명시를 권장합니다.

4. 시스템/메타 정보 조회

4-1 데이터베이스 목록

SELECT datname FROM pg_database;

4-2 스키마 목록

SELECT schema_name
FROM information_schema.schemata;

4-3 테이블 목록

SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'codeit'

4-4 컬럼 전체

SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'codeit'
ORDER BY table_name, ordinal_position;

4-5. 제약조건 참조 테이블/컬럼

-- 어떤 테이블이 어떤 제약조건에 걸려있는지
SELECT *
FROM information_schema.constraint_table_usage
WHERE table_schema = 'codeit';

-- 어떤 컬럼이 어떤 제약조건에 걸려있는지
SELECT *
FROM information_schema.constraint_column_usage
WHERE table_schema = 'codeit';

5. 실습 쿼리 모음 (필수 패턴)

5-1. WHERE로 행 필터링

-- 부서코드가 D5인 직원
SELECT emp_id, emp_name, dept_code
FROM employee
WHERE dept_code = 'D5';

5-2. 정렬(ORDER BY)

-- 급여 높은 순, 같다면 이름 오름차순
SELECT emp_id, emp_name, salary
FROM employee
ORDER BY salary DESC, emp_name ASC;

5-3. NULL 처리(IS NULL / IS NOT NULL)

-- 전화번호가 없는 직원
SELECT emp_id, emp_name, phone
FROM employee
WHERE phone IS NULL;

5-4. 계산 컬럼(표현식)

-- 연봉(보너스율 포함 가정: salary * (1 + COALESCE(bonus, 0)))
SELECT
  emp_id,
  emp_name,
  salary,
  bonus,
  salary * (1 + COALESCE(bonus, 0)) AS total_comp
FROM employee
ORDER BY total_comp DESC;

5-5. DISTINCT

-- 존재하는 부서 코드 목록
SELECT DISTINCT dept_code
FROM employee
WHERE dept_code IS NOT NULL
ORDER BY dept_code;

5-6. LIMIT / OFFSET (페이징)

-- 상위 5명만
SELECT emp_id, emp_name, salary
FROM employee
ORDER BY salary DESC
LIMIT 5;

-- 6~10위 (OFFSET 5 뒤에서 5개)
SELECT emp_id, emp_name, salary
FROM employee
ORDER BY salary DESC
LIMIT 5 OFFSET 5;

5-7. LIKE / ILIKE (부분 일치)

-- 성(姓)이 '김'으로 시작하는 직원 (대소문자 구분X: ILIKE)
SELECT emp_id, emp_name
FROM employee
WHERE emp_name ILIKE '김%';

5-8. BETWEEN / IN

-- 급여가 2,000,000 ~ 3,000,000
SELECT emp_id, emp_name, salary
FROM employee
WHERE salary BETWEEN 2000000 AND 3000000;

-- 특정 직급만
SELECT emp_id, emp_name, job_code
FROM employee
WHERE job_code IN ('J6','J7');

6. 다중 테이블 조회

6-1 직원 + 부서명

SELECT
  e.emp_id, e.emp_name, e.dept_code,
  d.dept_title
FROM employee AS e
LEFT JOIN department AS d
  ON e.dept_code = d.dept_id;

6-2. 직원 + 직급명 + 지역/국가

SELECT
  e.emp_id, e.emp_name, e.dept_code, e.job_code,
  j.job_name,
  d.location_id,
  l.local_name,
  n.national_name
FROM employee AS e
LEFT JOIN job       AS j ON e.job_code   = j.job_code
LEFT JOIN department AS d ON e.dept_code  = d.dept_id
LEFT JOIN location   AS l ON d.location_id = l.local_code
LEFT JOIN national   AS n ON l.national_code = n.national_code;

8. 중복 없는 값의 개수 구하기

-- 부서 코드의 종류(중복 제거 후) 갯수
SELECT COUNT(DISTINCT dept_code) AS 부서_전체_코드_갯수
FROM employee;

-- 부서 코드 + 직급 코드의 조합 갯수
SELECT COUNT(DISTINCT (dept_code, job_code)) AS 부서_잡코드_전체_코드_갯수
FROM employee;

• DISTINCT를 사용하면 중복을 제거한 유일한 값의 수를 구할 수 있습니다.

9. WHERE 절의 비교 연산자

• = : 값이 같은지 비교 (Java의 ==와 유사)
• !=, <>, ^= : 같지 않음
• >, <, >=, <= : 대소 비교
• BETWEEN A AND B : A 이상, B 이하 범위 비교
• LIKE / NOT LIKE : 문자열 패턴 매칭
• IN / NOT IN : 특정 집합에 포함 여부
• IS NULL / IS NOT NULL : NULL 여부 검사
• AND / OR / NOT : 논리 연산

주의: NULL은 =로 비교할 수 없고 반드시 IS NULL / IS NOT NULL을 써야 합니다.

10. PK 기반 조회

SELECT * FROM employee WHERE emp_id = 200;
SELECT * FROM employee WHERE emp_id = '200';       -- 문자열 비교도 동작
SELECT * FROM employee WHERE emp_id = '200'::INT; -- :: 캐스팅 문법

11. 숫자 조건

-- 월급이 300만원 이상
SELECT emp_name, salary, dept_code
FROM employee
WHERE salary >= 3000000;

-- 월급이 300만원 이상이 아닌 사람
SELECT emp_name, salary, dept_code
FROM employee
WHERE NOT salary >= 3000000;

12. 문자열 조건

-- 부서가 D1인 직원
SELECT emp_name, salary, dept_code
FROM employee
WHERE dept_code = 'D1';

-- 대소문자 무시 (ILIKE)
SELECT emp_name, salary, dept_code
FROM employee
WHERE dept_code ILIKE 'd1';

문자열은 대소문자를 구분합니다. PostgreSQL에서는 ILIKE를 쓰면 무시할 수 있습니다.

13. 날짜 조건

-- 2015-01-01 이전 입사자
SELECT emp_name, hire_date
FROM employee
WHERE hire_date < '2015-01-01';

-- 오늘 이전 입사자
SELECT emp_name, hire_date
FROM employee
WHERE hire_date < NOW();

14. AND / BETWEEN

-- 부서가 D1이고 급여가 300만원 이상
SELECT emp_name, salary, dept_code
FROM employee
WHERE dept_code = 'D1' AND salary >= 3000000;

-- 급여 200만 ~ 300만
SELECT emp_name, salary, dept_code
FROM employee
WHERE salary BETWEEN 2000000 AND 3000000;

15. LIKE 패턴 매칭

• % : 0개 이상의 모든 문자 대체
• _ : 정확히 1개의 문자 대체
• ESCAPE : _나 %를 리터럴 문자 그대로 쓰고 싶을 때

-- '이'로 시작
SELECT emp_name FROM employee WHERE emp_name LIKE '이%';

-- '이'로 시작하는 정확히 두 글자
SELECT emp_name FROM employee WHERE emp_name LIKE '이_';

-- 이름에 '동'이 들어감
SELECT emp_name FROM employee WHERE emp_name LIKE '%동%';

-- 전화번호가 011로 시작
SELECT emp_name, phone
FROM employee
WHERE phone LIKE '011%';

-- 전화번호가 정확히 10자리
SELECT emp_name, phone
FROM employee
WHERE LENGTH(phone) = 10;

-- '_' 앞에 글자가 3개인 이메일 (escape 예시)
SELECT emp_name, email
FROM employee
WHERE email LIKE '___#_%' ESCAPE '#';

16. NULL 비교

-- 전화번호 없는 직원
SELECT emp_name, phone
FROM employee
WHERE phone IS NULL;

-- 전화번호 있는 직원
SELECT emp_name, phone
FROM employee
WHERE phone IS NOT NULL;

17. NULL 처리

• NULL은 값이 없음을 의미하며, = 비교 불가 → IS NULL / IS NOT NULL 사용.
• 특정 값을 기본값으로 치환할 때는 COALESCE(ANSI 표준 함수) 사용.

SELECT emp_name,
       salary AS 월급,
       bonus,
       salary + (salary * COALESCE(bonus, 0)) AS 지급금액
FROM employee;

→ bonus가 NULL이면 0으로 간주해 계산

18. 다중 값 비교 (IN, NOT IN)

• 여러 개의 값과 비교할 때 OR 대신 IN을 쓰면 간결해짐

-- OR 절
SELECT emp_name, dept_code
FROM employee
WHERE dept_code = 'D5'
   OR dept_code = 'D6'
   OR dept_code = 'D7'
   OR dept_code = 'D8';

-- IN 절
SELECT emp_name, dept_code
FROM employee
WHERE dept_code IN ('D5', 'D6', 'D7', 'D8')
ORDER BY dept_code;

Subquery와 결합

SELECT emp_name, dept_code
FROM employee
WHERE dept_code IN (
    SELECT dept_id FROM department
    WHERE dept_id BETWEEN 'D5' AND 'D8'
)
ORDER BY dept_code;

19. 연산자 우선순위

-- 잘못된 예시
SELECT emp_name, dept_code
FROM employee
WHERE dept_code IN ('D5', 'D6', 'D7', 'D8') AND emp_name LIKE '정%'
   OR emp_name LIKE '전%';

-- 올바른 예시
SELECT emp_name, dept_code
FROM employee
WHERE dept_code IN ('D5', 'D6', 'D7', 'D8')
  AND (emp_name LIKE '정%' OR emp_name LIKE '전%');

20. ORDER BY (정렬)

• ORDER BY 컬럼명 [ASC|DESC]
• 숫자, 문자, 날짜 모두 정렬 가능.
• ASC = 오름차순(기본), DESC = 내림차순.

-- 이름 오름차순
SELECT emp_name FROM employee ORDER BY emp_name;

-- 급여 내림차순
SELECT emp_name, salary
FROM employee
ORDER BY salary DESC;

-- 입사일 오름차순
SELECT emp_name, hire_date
FROM employee
ORDER BY hire_date ASC;

컬럼 순서(Index)로 정렬 (비권장)

SELECT emp_id, emp_name
FROM employee
ORDER BY 1 DESC;  -- emp_id

별칭(Alias)으로 정렬

SELECT emp_id, emp_name AS 이름
FROM employee
ORDER BY 이름 DESC;

다중 정렬

SELECT *
FROM employee
ORDER BY dept_code ASC, job_code DESC, salary DESC;

21. LIMIT / OFFSET (페이징)

• 결과 행 수 제한: LIMIT
• 특정 개수 건너뛰기: OFFSET
• 반드시 ORDER BY와 함께 써야 의미 있음.

-- 상위 5개 (emp_id 기준)
SELECT * FROM employee ORDER BY emp_id LIMIT 5;

-- 급여 상위 5명
SELECT * FROM employee ORDER BY salary DESC LIMIT 5;

-- 2페이지 (6~10위)
SELECT * FROM employee ORDER BY emp_id LIMIT 5 OFFSET 5;

-- 급여 6~10위
SELECT * FROM employee ORDER BY salary DESC LIMIT 5 OFFSET 5;

→ LIMIT + OFFSET 조합은 pagination 구현에 필수