https://velog.io/@kpark2000/프로그래머스-SQL-함수-정리

SELECT

SELECT NAME, AGE
FROM EMPLOYEES;

--- 모든 칼럼 조회
SELECT * FROM EMPLOYEES;

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

WHERE 조건절

SELECT *
FROM EMPLOYEES
WHERE SALARY >= 3000 AND name LIKE '김%'

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

--- NULL 체크
WHERE MANGER_ID IS NULL
WHERE MANGER_ID IS NOT NULL

ORDER BY LIMIT

--- 내립 차순 정렬 후, 상위 3개
SELECT
    NAME, SALARY
FROM
	EMPLOYEES
ORDER BY
	SALARY DESC
LIMIT 3

--- 다중 컬럼 정렬
ORDER BY DEPARTMENT, SALARY DESC

--- OFFSET : 3번 건너뛰고 5개
LIMIT 5 OFFSET 3

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
    DEPARTMENT,
    COUNT(*) AS COUNT,
    AVG(SALARY) AS AVG_SALARY
FROM
	EMPLOYEESS
WHERE
	AGE >= 20   --- 집계 전 필터링
GROUP BY
	DEPARTMENT
HAVING
	COUNT(*) >= 3 --- 집계 후 필터링

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