SELECT 기초 — 원하는 데이터 조회하기

CREATE TABLE employees (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary     INTEGER,
    hire_date  DATE,
    is_active  BOOLEAN DEFAULT TRUE
);

INSERT INTO employees (name, department, salary, hire_date) VALUES
    ('김철수', '개발팀', 5000000, '2021-03-15'),
    ('이영희', '마케팅팀', 4200000, '2020-07-01'),
    ('박민준', '개발팀', 6500000, '2019-01-20'),
    ('최지수', '디자인팀', 4800000, '2022-05-10'),
    ('정태양', '개발팀', 5800000, '2020-11-30'),
    ('한소희', '마케팅팀', 3900000, '2023-02-14');

-- 개발팀만 조회
SELECT * FROM employees
WHERE department = '개발팀';

-- 급여 500만 이상
SELECT name, salary FROM employees
WHERE salary >= 5000000;

-- 두 조건 모두 (AND)
SELECT name, department, salary FROM employees
WHERE department = '개발팀' AND salary >= 5500000;

-- 둘 중 하나 (OR)
SELECT * FROM employees
WHERE department = '개발팀' OR department = '디자인팀';

-- 부정 (NOT)
SELECT * FROM employees
WHERE NOT department = '마케팅팀';

-- =  같음
-- != 또는 <>  다름
-- >  초과
-- >= 이상
-- <  미만
-- <= 이하

SELECT * FROM employees WHERE salary != 5000000;
SELECT * FROM employees WHERE hire_date > '2022-01-01';

-- 급여 400만 ~ 550만
SELECT name, salary FROM employees
WHERE salary BETWEEN 4000000 AND 5500000;

-- 날짜 범위
SELECT * FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2021-12-31';

-- 특정 부서들
SELECT * FROM employees
WHERE department IN ('개발팀', '디자인팀');

-- 부정
SELECT * FROM employees
WHERE department NOT IN ('마케팅팀');

-- % : 0개 이상의 문자
SELECT * FROM employees WHERE name LIKE '김%';      -- '김'으로 시작
SELECT * FROM employees WHERE name LIKE '%수';      -- '수'로 끝
SELECT * FROM employees WHERE name LIKE '%철%';    -- '철' 포함

-- _ : 정확히 1개의 문자
SELECT * FROM employees WHERE name LIKE '김_수';   -- '김'+'한글자'+'수'

-- NULL 확인 (= NULL은 동작 안 함!)
SELECT * FROM employees WHERE department IS NULL;
SELECT * FROM employees WHERE department IS NOT NULL;

-- COALESCE: NULL 대체값
SELECT name, COALESCE(department, '미배정') AS dept
FROM employees;

-- 오름차순 (기본값 ASC)
SELECT name, salary FROM employees
ORDER BY salary;

-- 내림차순
SELECT name, salary FROM employees
ORDER BY salary DESC;

-- 여러 열 정렬
SELECT name, department, salary FROM employees
ORDER BY department ASC, salary DESC;

-- 상위 3건만
SELECT * FROM employees ORDER BY salary DESC LIMIT 3;

-- 페이지네이션 (페이지당 2건, 2페이지)
-- 건너뛸 행 수 = (페이지 번호 - 1) × 페이지 크기
SELECT * FROM employees ORDER BY id LIMIT 2 OFFSET 2;
-- 페이지 1: OFFSET 0
-- 페이지 2: OFFSET 2
-- 페이지 3: OFFSET 4

-- 부서 목록 (중복 없이)
SELECT DISTINCT department FROM employees;

-- 결과
-- 개발팀
-- 마케팅팀
-- 디자인팀