[SQL 활용] 2. WHERE(조건문)절과 ORDER BY(정렬)절

문법

SELECT * | { [DISTINCT] column / expression [alias], ... }
FROM table
[WHERE condition(s)]
[ORDER BY { column, expr, alias } [ASC | DESC] ];

---

1.WHERE(조건문)절 - 행 제한 조건문

좌변(컬럼명) = 우변(리터럴값);

SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90;

---

SELECT last_name, job_id, department_id
FROM employees
WHERE last_name = 'Whalen';

---

SELECT last_name
FROM employees
WHERE hire_date = '17-FEB-96';

---

단일행 비교연산자 : =　>　<　>=　<=　<>　!=

SELECT last_name, salary
FROM employees
WHERE salary <= 3000;

---

범위연산자 : BETWEEN A AND B 모든 데이터 다 사용 가능

SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500; 

---

다중행 비교연산자 : IN( =, OR )

SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);

---

% : 0개의 문자 또는 여러 개의 문자가 올 수 있다.

_ : 반드시 1개의 문자가 와야한다.

ex)

a로 시작되는 문자열 → 'a%'

= abcd(o) a(o) defadb(x)

a가 포함된 문자열 → '%a%'

= abcd(o) a(o) defadb(o)

a로 끝나는 문자열 → '%a'

두번째 문자가 a인 문자열 → '_a%'

끝에서 세번째 문자가 a인 문자열 → '%a__'

SELECT last_name
FROM employees
WHERE last_name LIKE '_0%';

---

IS NULL : 비교 연산자. 값이 NULL인 것

SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;

---

SELECT employee_id, salary, commission_pct
FROM employees
WHERE commission_pct IS NULL;

---

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
AND job_id LIKE '%MAN';

---

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN';

---

비교연산자에 NOT(논리연산자)이 조합으로 사용된 경우

... WHERE job_id         NOT IN ('AC_ACCOUNT', 'AD_VP')
... WHERE salary         NOT BETWEEN 10000 AND 15000
... WHERE last_name      NOT LIKE '%A%'
... WHERE commission_pct IS NOT NULL

---

2. ORDER BY절 (항상 쿼리문 마지막에 절어주는 절)

ASC(Ascending order, default) : 오름차순, default(생략), 작은 수에서 큰 수로

DESC(Descending order) : 큰 수에서 작은 수로

=　　　　　↔　<>

>, >=　　　↔　<. <=

BETWEEN　↔　NOT BETWEEN A AND B

IN(=, OR)　 ↔　NOT IN(<>, AND)

LIKE　　　 ↔　NOT LIKE

IS NULL　  ↔　IS NOT NULL

DESC : 최근 날짜부터 정렬

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC;

 

---

SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;

---

SELECT절에 나열되어있는 순서부터(왼쪽 → 오른쪽) 자동적으로 위치가 주어진다. 3번째 컬럼 오름차순

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY 3;

 

---

SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;

department_id : 오름차순

salary : 내림차순

---

연습문제

1. employees 테이블로부터 04년도에 입사한 모든 사원의 last_name과 hire_date를 출력하시오.

SELECT last_name, hire_date
FROM employees
WHERE hire_date BETWEEN '01-JAN-04' and '31-dec-04';

SELECT last_name, hire_date
FROM employees
WHERE hire_date like '%04';

---

2. employees 테이블로부터 커미션을 받지 않는 모든 사원의 last_name, salary, commission_pct를 출력하되 salary를 기준으로 내림차순 정렬하시오.

SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NULL
ORDER BY salary DESC;