💾 공통 SQL 완전 가이드

-- 부서 테이블 생성
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL UNIQUE
);

-- 사원 테이블 생성
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    dept_id INT,
    hire_date DATE DEFAULT CURRENT_DATE,
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE SET NULL
);

-- 데이터 삽입
INSERT INTO departments (dept_id, dept_name) VALUES (10, 'Engineering');
INSERT INTO employees (emp_id, emp_name, salary, dept_id) VALUES (1, 'John Doe', 75000.00, 10);

-- 데이터 조회 (필터링 및 정렬)
SELECT emp_name, salary
FROM employees
WHERE salary >= 50000
ORDER BY salary DESC;

-- 데이터 수정 및 삭제
UPDATE employees SET salary = salary * 1.1 WHERE emp_id = 1;
DELETE FROM employees WHERE emp_id = 1;

-- INNER JOIN: 양쪽 테이블에 모두 일치하는 행만 조회
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- LEFT OUTER JOIN: 왼쪽 테이블 전체와 오른쪽의 일치하는 행 조회 (일치하지 않으면 NULL)
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT OUTER JOIN departments d ON e.dept_id = d.dept_id;

-- 부서별 사원 수와 평균 급여 조회 (평균 급여 60000 이상인 부서만)
SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) >= 60000;

-- 1. 사용자 생성 (공통 RDB 개념)
CREATE USER read_only_user IDENTIFIED BY 'secure_password';

-- 2. 오브젝트 권한 부여 (특정 테이블의 조회 권한만 허용)
GRANT SELECT ON employees TO read_only_user;

-- 3. 여러 권한을 묶은 롤(Role) 생성 및 부여
CREATE ROLE app_developer_role;
GRANT SELECT, INSERT, UPDATE ON employees TO app_developer_role;
GRANT SELECT, INSERT ON departments TO app_developer_role;
GRANT app_developer_role TO read_only_user;

-- 4. 권한 회수
REVOKE UPDATE ON employees FROM app_developer_role;

-- ── 데이터베이스 캐릭터셋 확인용 공통 쿼리 ──

-- [MySQL / MariaDB]
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

-- [Oracle]
SELECT * FROM NLS_DATABASE_PARAMETERS 
WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

-- [PostgreSQL]
SELECT datname, pg_encoding_to_char(encoding), datcollate, datctype 
FROM pg_database 
WHERE datname = current_database();

-- 인덱스 생성
CREATE INDEX idx_emp_salary ON employees(salary);

-- 실행 계획 확인 (DBMS 제품군에 따라 EXPLAIN 형식은 상이)
EXPLAIN
SELECT emp_name, salary
FROM employees
WHERE salary > 80000;