SQL SELECT WHERE 기초 예제 (BETWEEN, IN, NOT IN, ANY, LIKE, IS NULL)

문제1) EMPLOYEES 테이블에서 급여가 3000이상인 사원의 사원번호, 이름, 담당업무, 급여를 출력하라.

SELECT employee_id as "사원번호", first_name as 이름, job_id as "담당업무", salary 급여
FROM employees
WHERE salary >= 3000;

 

문제2) EMPLOYEES 테이블에서 담당 업무가 ST_MAN인 사원의 사원번호, 성명, 담당업무, 급여, 부서번호를 출력하라.

SELECT employee_id as "사원번호", first_name as 성명, job_id as "담당업무", salary 급여, department_id 부서번호
FROM employees
WHERE job_id = 'ST_MAN';

- 컬럼명은 대소문자 구분 하지 않지만, 데이터 들어간 부분은 구분

 

문제3) EMPLOYEES 테이블에서 입사일자가 2006년 1월 1일 이후에 입사한 사원의 정보를 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호로 출력하라.

SELECT employee_id as "사원번호", first_name as 성명, job_id as "담당업무", salary 급여, hire_date 입사일자, department_id 부서번호
FROM employees
WHERE hire_date > TO_DATE('20060101', 'YYYYMMDD');

 

문제4) EMPLOYEES 테이블에서 급여가 3000에서 5000사이의 사원의 성명, 담당업무, 급여, 부서번호를 출력하라.

SELECT first_name as 성명, job_id as "담당업무", salary 급여, department_id 부서번호
FROM employees
WHERE salary BETWEEN 3000 AND 5000;

- WHERE salary >= 3000 AND salary <= 5000; 와 동일

 

문제5) EMPLOYEES 테이블에서 사원번호가 145,152,203인 사원의 정보를 사원번호, 성명, 담당업무, 급여, 입사일자를 출력하라

SELECT employee_id as "사원번호", first_name as 성명, job_id as "담당업무", salary 급여, hire_date 입사일자
FROM employees
WHERE employee_id IN('145', '152', '203');

- WHERE EMPLOYEE_ID = ANY('145', '152', '203'); 와 동일

 

문제6) EMPLOYEES 테이블에서 입사일자가 05년도에 입사한 사원의 정보를 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하라.

SELECT employee_id as "사원번호", first_name as 성명, job_id as "담당업무", salary 급여, hire_date 입사일자, department_id 부서번호
FROM employees
WHERE hire_date LIKE '05%';

 

문제7) EMPLOYEES 테이블에서 보너스가 없는 사원의 사원번호, 성명, 담당업무, 급여, 입사일자, 보너스, 부서번호를 출력하라.

SELECT employee_id as "사원번호", first_name as 성명, job_id as "담당업무", salary 급여, hire_date 입사일자, salary * NVL(COMMISSION_PCT, 0) 보너스, department_id 부서번호
FROM employees
WHERE COMMISSION_PCT IS NULL;

 

문제8) EMPLOYEES 테이블에서 급여가 1100이상이고 JOB이 ST_MAN인 사원의 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하라

SELECT employee_id as "사원번호", first_name as 성명, job_id as "담당업무", salary 급여, hire_date 입사일자, department_id 부서번호
FROM employees
WHERE salary >= 1100 AND job_id = 'ST_MAN';

 

문제9) EMPLOYEES 테이블에서 급여가 10000이상이거나 JOB이 ST_MAN인 사원의 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하라

SELECT employee_id as "사원번호", first_name as 성명, job_id as "담당업무", salary 급여, hire_date 입사일자, department_id 부서번호
FROM employees
WHERE salary >= 10000
    OR job_id = 'ST_MAN';

 

문제10) EMPLOYEES 테이블에서 JOB이 ST_MAN, SA_MAN, SA_REP가 아닌 사원의 사원번호, 성명, 담당업무, 급여, 부서번호를 출력하라

SELECT employee_id as "사원번호", first_name as 성명, job_id as "담당업무", salary 급여, department_id 부서번호
FROM employees
WHERE job_id NOT IN('ST_MAN', 'SA_MAN', 'SA_REP');

 

문제11) 업무가 PRES이고 급여가 12000이상이거나 업무가 SA_MAN인 사원의 사원번호, 이름, 업무, 급여를 출력하라.

SELECT employee_id as "사원번호", first_name as 이름, job_id as "업무", salary 급여
FROM employees
WHERE job_id LIKE '%PRES%' AND salary >= 12000 OR job_id = 'SA_MAN';

 

문제12) 업무가 AD_PRES 또는 SA_MAN이고 급여가 12000이상인 사원의 사원번호, 이름, 업무, 급여를 출력하라.

SELECT employee_id as "사원번호", first_name as 이름, job_id as "업무", salary 급여
FROM employees
-- WHERE job_id IN('AD_PRES', 'SA_MAN') AND salary >= 12000;
WHERE job_id = 'AD_PRES' 
    OR job_id = 'SA_MAN'
        AND salary >= 12000;