[Oracle] SELECT * FROM +(WHERE,GROUP,ORDER)

 

WHERE

• ANY() / ALL()
  -조건 '또는'='or'과 같은 역할인 any / 조건 '그리고'='and'와 같은 역할인 all

select first_name, salary
from employees
where salary = ANY(6000,3200);
--salary = 6000 or salary = 3200

select first_name, salary
from employees
where salary = ALL(6000,3200);
--salary = 6000 and salary = 3200

 

• LIKE-특정 글자를 포함하는지 확인하기

select first_name
from employees
where first_name LIKE 'G_ra_d';
--'_'는 한글자의 칸을 의미하여 아무 단어나 와도 되지만 글자수는 6개를 초과하면 안된다.

select first_name
from employees
where first_name LIKE 'K%y';
--'%'는 글자갯수 상관없음. 
--'Kally', 'Key'처럼 주어진 첫글자와 마지막 글자 k와 y만 있다면 가능

select first_name
from employees
where first_name LIKE 'A%';
--'A'로 시작하는 first_name을 조회

 

• BETWEEN AND / NOT BETWEEN AND-조건의 이상 이하일때 / 조건의 이상 이하가 아닐때

select first_name, salary
from employees
where salary BETWEEN 3200 and 9000;
--where salary >= 3200 and salary <= 9000

select first_name, salary
from employees
where salary not BETWEEN 3200 and 9000;

 

• IN() / NOT IN()-포함된 값이 있는지 없는지 

select first_name, salary
from employees
where salary in(6000,3200);
--salary = 6000 or salary = 3200

select first_name, salary
from employees
where salary not in(6000,3200);
--salary != 6000 or salary != 3200

 

• IS NULL / IS NOT NULL-null값인지 아닌지

select first_name
from employees
where manager_id IS NULL;

select first_name
from employees
where commission_pct IS NULL;

 

 

GROUP BY + HAVING

-그룹으로 묶기(group by) + 그룹 후 조건(having)

select job_id, count(*), sum(salary), avg(salary)
from employee
where job_id > 100
group by job_id
having sum(salary) >= 10000;

 

ORDER BY

-오름차순(asc), 내림차순(desc) 정렬하기

select hire_date, salary
from employees
order by hire_date asc, salary desc;

select commission_pct
from employees
order by commission_pct nulls first;

 

+ 날짜형 데이터 변환

 

TO_DATE('20211225','YYYYMMDD') -> 2021년 12월 25일