SQL 기본 조회 및 필터링(SELECT, WHERE, AND, OR, GROUP BY 등)

기본 조회 및 필터링

1.SELECT: 데이터 조회

SELECT column1, column2 FROM table_name;
SELECT * FROM table_name;

2.WHERE: 조건 필터링

SELECT * FROM employees WHERE department_id = 10;

3.AND, OR: 복합 조건

SELECT * FROM employees WHERE salary > 5000 AND department_id = 20;

4.IN: 여러 값 중 일치하는 경우

SELECT * FROM employees WHERE department_id IN (10, 20, 30);

5.BETWEEN: 범위 조건

SELECT * FROM employees WHERE salary BETWEEN 3000 AND 5000;

6.LIKE: 패턴 매칭

SELECT * FROM employees WHERE last_name LIKE 'S%';

정렬 및 제한

7.ORDER BY: 결과 정렬

SELECT * FROM employees ORDER BY salary DESC;

8.DISTINCT: 중복 제거

SELECT DISTINCT department_id FROM employees;

집계 및 그룹화

9.GROUP BY: 그룹화

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

10.HAVING: 그룹 조건

SELECT department_id, AVG(salary) FROM employees 
GROUP BY department_id HAVING AVG(salary) > 5000;

조인

11.INNER JOIN: 내부 조인

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;

12.LEFT JOIN: 왼쪽 외부 조인

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;

서브쿼리

13.서브쿼리 사용

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

데이터 조작

14.INSERT: 데이터 삽입

INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
VALUES (1, 'Smith', 'smith@example.com', SYSDATE, 'IT_PROG');

15.UPDATE: 데이터 수정

UPDATE employees SET salary = salary * 1.1 WHERE department_id = 20;

16.DELETE: 데이터 삭제

DELETE FROM employees WHERE employee_id = 1;

기타 유용한 함수

17.NVL: NULL 값 대체

SELECT employee_id, NVL(commission_pct, 0) FROM employees;

18.TO_CHAR: 날짜/숫자를 문자열로 변환

SELECT employee_id, TO_CHAR(hire_date, 'YYYY-MM-DD') FROM employees;

19.DECODE: 조건부 값 반환

SELECT employee_id, DECODE(department_id, 10, 'A', 20, 'B', 'C') AS dept_category
FROM employees;

---