SQL

SQL (Structured Query Language)

사용자와 관계형 데이터베이스를 연결시켜 주는 표준 검색 언어
오라클에서만 사용 가능한 SQL문과 모든 DBMS에서 사용 가능한 ANSI(American National Standards Institute) SQL문으로 구분된다.

SQL 문장의 특징

1. 사용하기가 쉬워 프로그래밍에 경험이 없는 사용자라도 쉽게 배울 수 있다.
2. 비 절차적 언어이다.
3. DBMS를 만든 회사마다 자체적으로 추가된 SQL문법이 존재하기 때문에 SQL 문장이 약간씩 다르다. 하지만 모든 DBMS에서 공통적으로 사용할 수 있는 ANSI SQL문이 제공되기 때문에 큰 문제는 없다.

SQL 문장의 종류

1.질의어 ( Data Query Language : DQL)

질의어는 SELECT문을 사용하여 테이블에 저장된 데이터를 검색할 때 사용하는 SQL문이다

2. 데이터 조작어 ( Data Manipulation Language : DML)

데이터베이스에 저장된 데이터를 조작하기 위해 사용되는 SQL문
INSERT문 / UPDATE문 / DELETE문 / MERGE문

3. 트랜잭션 처리어 ( Transaction Control Language : TCL)

데이터베이스의 트랜잭션과 관련된 작업을 처리하기 위한 SQL문
COMMIT문 / ROLLBACK문 / SAVEPOINT문

3. 데이터 정의어 ( Data Definition Language : DDL)

CREATE문 / ALTER문 / DROP문 / TRUNCATE문
DDL은 자동으로 COMMIT 되기 때문에 생성된 테이블을 ROLLBACK으로 취소할 수 없으며 제거하기 위해서는 DROP문을
사용해야 된다.

4. 데이터 제어어 ( Data Control Language : DCL)

데이터베이스에 저장된 데이터를 보호하기 위한 강력한 방법으로 인증(Authentication)과 권한(Authorization)이 필요하다.
GRANT문 / REVOKE문

---

SELECT문

데이터베이스에 저장된 데이터를 검색할 때 사용되는 SQL문장으로서 Data Query Language(DQL)라고 한다.

1) Selection 기능 : SELECT 문 요청시 테이블의 행(레코드)이 반환되는 기능이다.
2) Projection 기능: SELECT 문 요청시 테이블의 열(컬럼)이 반환되는 기능이다.
3) Join 기능: 여러 테이블에 공통적으로 존재하는 컬럼을 사용하여 한꺼번에 서로 다른 테이블에 저장된 데이터를 가져오는 기능이다.

1. 모든 컬럼 보기

   SELECT * 
   FROM departments;

   SELECT * 
   FROM employees; 

   select * 
   from EMPLOYEES;

2. 특정 컬럼 보기

   SELECT employee\_id 
   FROM employees;

   SELECT employee_id, last_name, hire_date, salary 
   FROM employees;

3. 연산 가능

    SELECT last\_name, salary, salary * 12 
    FROM employees;

4. 별칭

   SELECT last\_name as "사원 이름", salary "사원 월급", salary * 12 as "연 봉"
   FROM employees;

   SELECT last\_name as 이름, salary 월급,
   salary \* 12 as 연봉 
   FROM employees;

5. null

   SELECT last\_name 이름, salary 월급, commission\_pct 수수료, 
   salary \* 12+NVL(commission\_pct,0) as 연봉 
   FROM employees;

   SELECT last\_name 이름, salary 월급, commission\_pct 수수료, salary\* 12+commission\_pct as 연봉 
   FROM employees;

6. 연결: 컬럼 || 컬럼

    SELECT last_name || salary as "이름 월급" 
    FROM employees;

    SELECT last_name || ' 사원' 
    FROM employees;

    SELECT last_name || '의 직업은 ' || job_id || ' 입니다' as "사원별 직급" 
    FROM employees;

7. 중복제거

    SELECT job_id
    FROM employees;

    SELECT DISTINCT job_id
    FROM employees;

8. where 절의 비교 연산자

    SELECT employee_id,last_name,job_id, salary
    FROM employees
    WHERE salary >= 10000;

SQL문의 식별자는 대소문자 구별 안하지만 값(리터럴)은 대소문자를 구별한다.

SELECT employee_id,last_name,job_id, salary
FROM employees
WHERE last_name = 'King';




SELECT employee_id,last_name,job_id, salary
FROM employees
WHERE last_name = 'KING';




SELECT employee_id,last_name,salary,hire_date
FROM employees
WHERE hire_date > '07/12/31';

8. Between a And b

    SELECT employee_id,last_name,salary,hire_date
    FROM employees
    WHERE salary BETWEEN 7000 AND 8000;

    SELECT employee_id,last_name,salary,hire_date
    FROM employees
    WHERE hire_date BETWEEN '07/01/01' AND '08/12/31';

9. in 연산자

    SELECT employee_id,last_name,salary,hire_date
    FROM employees
    WHERE 컬럼명 IN ( 100, 200, 300 );

    SELECT employee_id,last_name,salary,hire_date
    FROM employees
    WHERE last_name IN ( 'King','Abel','Jones');

10. like + 와일드카드문자(%, _)

    SELECT employee_id,last_name,salary
    FROM employees
    WHERE last_name LIKE 'J%';

    SELECT employee_id,last_name,salary
    FROM employees
    WHERE last_name LIKE '%ai%';

    SELECT employee_id,last_name,salary
    FROM employees
    WHERE last_name LIKE '%in';

    SELECT employee_id,last_name,salary
    FROM employees
    WHERE last_name LIKE '_b%';

    SELECT employee_id,last_name,salary
    FROM employees
    WHERE last_name LIKE '_____d';

    SELECT employee_id,last_name,salary
    FROM employees
    WHERE last_name LIKE '%_%';

    SELECT employee_id,last_name,salary,job_id
    FROM employees
    WHERE job_id LIKE '%E___' ESCAPE 'E';

11. 논리 연산자 - AND

    SELECT last_name,job_id,salary
    FROM employees
    WHERE job_id = 'IT_PROG' AND salary >= 5000;

12. 논리 연산자 - OR

    SELECT last_name,job_id,salary
    FROM employees
    WHERE job_id = 'IT_PROG' OR salary >= 5000;

11. 논리 연산자 - NOT

    SELECT last_name,job_id,salary
    FROM employees
    WHERE NOT salary < 20000;

    SELECT last_name,job_id,salary
    FROM employees
    WHERE salary NOT IN (9000,8000,6000 );

    SELECT last_name,job_id,salary
    FROM employees
    WHERE last_name NOT LIKE 'J%';

    SELECT last_name,job_id,salary
    FROM employees
    WHERE salary NOT BETWEEN 2400 AND 20000;

    SELECT last_name,job_id,salary
    FROM employees
    WHERE commission_pct IS NULL;

    SELECT last_name,job_id,salary
    FROM employees
    WHERE manager_id IS NULL;  -- 사장

    SELECT last_name,job_id,salary
    FROM employees
    WHERE manager_id IS NOT NULL;

논리 연산자 사용시 주의: AND가 OR보다 우선순위가 높다
SELECT last_name,job_id,salary,commission_pct FROM employees WHERE job_id ='AC_MGR' OR job_id='MK_REP' AND commission_pct IS NULL AND salary >=4000 AND salary <= 9000;

```
SELECT last_name,job_id,salary,commission_pct
FROM employees
WHERE ( job_id ='AC_MGR' OR job_id='MK_REP' )   -- 괄호()부터 시행된다
AND commission_pct IS NULL
AND salary >=4000
AND salary <= 9000;
```

12. 정렬

    SELECT employee_id,last_name,job_id,salary
    FROM employees
    ORDER BY salary DESC;

    SELECT employee_id,last_name,job_id,salary
    FROM employees
    ORDER BY salary ASC;

    SELECT employee_id,last_name,job_id,salary
    FROM employees
    ORDER BY salary;  --기본은 오름차순

    SELECT employee_id,last_name,job_id,salary as "월급" FROM employees
    ORDER BY 월급 DESC;

    SELECT employee_id,last_name,job_id,salary as "월급" FROM employees
    ORDER BY 4 DESC;

    SELECT employee_id,last_name as 이름,job_id,salary 
    FROM employees
    ORDER BY last_name ASC;  --A: 65 B: 66

    SELECT employee_id,last_name as 이름,job_id,salary 
    FROM employees
    ORDER BY 2 ASC;

    SELECT employee_id,last_name,salary,hire_date as 입사일
    FROM employees
    ORDER BY hire_date DESC;

    SELECT employee_id,last_name,salary,hire_date as 입사일
    FROM employees
    ORDER BY 입사일 DESC;

    SELECT employee_id,last_name,salary,hire_date as 입사일
    FROM employees
    ORDER BY 4 DESC;

    SELECT employee_id,last_name,salary,hire_date
    FROM employees
    ORDER BY salary DESC, hire_date;

    SELECT employee_id,last_name,salary,hire_date
    FROM employees
    ORDER BY salary DESC, hire_date desc;

    SELECT employee_id,last_name,salary,hire_date
    FROM employees
    ORDER BY 3 DESC, 4;

    SELECT employee_id,last_name,salary,hire_date, COMMISSION_PCT
    FROM employees
    order by COMMISSION_PCT desc;  --오라클에서는 null 값을 가장 큰 값으로 처리한다.