SELECT

--- 중복 제거
SELECT DISTINCT DEPARTMENT FROM EMPLOYEES;

WHERE 조건절

--- IN 연산자 (OR 대체)
WHERE DEPARTMENT IN ('IT', 'HR', 'Finance')
    
--- BETWEEN (이상, 이하 범위)
WHERE SALARY BETWEEN 2000 AND 5000;

--- NULL 체크
IFNULL(column, '대체값')       -- NULL이면 대체값
WHERE column IS NULL           -- NULL인 행
WHERE column IS NOT NULL       -- NULL 아닌 행
COALESCE(col1, col2, '기본값') -- 첫 번째 NULL 아닌 값

LIKE

--- % : 0개 이상의 임의 문자
WHERE NAME LIKE '김%' --- 김으로 시작
WHERE NAME LIKE '춘%' --- 춘으로 끝
WHERE NAME LIKE '%민%' --- 민 포함

--- _ : 정확히 1개 임의 문자
WHERE CODE LIKE 'A_01' --- A?01 형태

집계 함수 그룹별로 집계 후, HAVING으로 그룹 필터링

SELECT
    COUNT(*),           --- NULL 포함
    COUNT(MANAGER_ID),  --- NULL 제외
    SUM(SALARY),
    AVG(SALARY),
    MAX(SALARY),
    MIN(SALARY)
FROM EMPLOYEES

GROUP BY (~별로)

-- 기본: 그룹별 집계
SELECT dept, COUNT(*) AS cnt
FROM employees
GROUP BY dept

-- HAVING: 집계 결과에 조건
SELECT dept, COUNT(*) AS cnt
FROM employees
GROUP BY dept
HAVING cnt >= 5
-- 규칙: SELECT에서 집계함수 안 쓴 컬럼은 전부 GROUP BY에
-- SELECT에 집계함수, 컬럼있으면 GROUP BY 필수

COUNT

--- 조건부 COUNT (CASE 활용)
SELECT
	COUNT(CASE WHEN GENDER = 'M' THEN 1 END) AS MALE
  COUNT(CASE WHEN GENDER = 'F' THEN 1 END) AS FEMALE
FROM EMPLOYEES;

--- DISTINCT + COUNT L: 중복 없는 갯수
SELECT COUNT(DISTINCT USER_ID) AS UNIQUE_USERS
FROM ORDERS

JOIN

SELECT * FROM A JOIN B ON A.id = B.id -- INNER JOIN: 양쪽 다 있는 것만
SELECT * FROM A LEFT JOIN B ON A.id = B.id -- LEFT JOIN: A 기준, B에 없으면 NULL
SELECT * FROM A JOIN B ON (A.CODE & B.SKILL_CODE) = A.CODE -- 비트 연산 JOIN 

-- 3개 테이블 JOIN
SELECT * FROM A
JOIN B ON A.id = B.a_id
JOIN C ON B.id = C.b_id

--- 없는 것 찾기 (부서가 없는 직원)
SELECT e.name
FROM employees AS e
LEFT JOIN department AS d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;
	
--- 직원과 그 상사 이름 함께 조회
SELECT e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.emp_id;

FROM 서브쿼리 (인라인뷰)

SELECT dept_id, avg_sal
FROM (
	SELECT department AS dept, 
	       AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
) AS dept_avg
WHERE avg_sal >= 4000

WHERE 서브 쿼리

--- 평균 이상 급여 직원
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)

--- IN + 서브 쿼리
SELECT name
FROM employees
WHERE dept_id IN (
    	SELECT dept_id
      FROM departments
      WHERE location = 'Seoul'
);