[Oracle] 오라클 SQL Where절 문법 연습하기

1. employees 테이블로부터 급여가 ＄5,000 ~ ＄12,000 범위에 속하지 않는 사원의 last_name과 salary를 출력하시오.

 

SELECT last_name, salary
FROM employees
WHERE salary<5000 OR salary>12000;

 

SELECT last_name, salary
FROM employees
WHERE NOT BETWEEN salary 5000 and 12000;

 

2. employees 테이블로부터 last_name이 Matos이거나 Taylor인 사원의 last_name, job_id, hire_date를 출력하되, hire_date를 기준으로 오름차순 정렬하시오.

 

SELECT last_name, job_id, hire_date
FROM employees
WHERE last_name = 'Matos' OR last_name = 'Taylor'
ORDER BY hire_date;

 

SELECT last_name, job_id, hire_date
FROM employees
WHERE last_name IN('Matos', 'Taylor')
ORDER BY hire_date;

 

3. employees 테이블로부터 부서 20 또는 50에 속하는 모든 사원의 last_name 과 department_id를 last_name을 기준으로 오름차순 정렬하여 출력하시오.

 

SELECT last_name, department_id
FROM employees
WHERE department_id = 20 OR department_id = 50
ORDER BY last_name;

 

SELECT last_name, department_id
FROM employees
WHERE department_id IN(20, 50)
ORDER BY last_name;

 

4. employees 테이블로부터 salary가 $5,000 ~ $12,000의 급여를 받으면서 department_id가 20 또는 50에 속하는 사원의 last_name과 salary를 출력하되 컬럼 alias를 각각 Employee 및 Monthly Salary로 출력하시오.

 

SELECT last_name as 'Employee', salary as 'MONTHLY SALARY'
FROM employees
WHERE salary BETWEEN 5000 and 12000 AND (department_id = 20 OR department_id = 50);

 

SELECT last_name as 'Employee', salary as 'MONTHLY SALARY'
FROM employees
WHERE salary BETWEEN 5000 and 12000 AND department_id IN (20, 50);