[SQLdevelop] 실습(예제)

---

SELECT * FROM employees;

 

SELECT employee_id, hire_date
FROM employees
WHERE hire_date BETWEEN '03/01/01' AND '03/12/31'
ORDER BY hire_date;

SELECT employee_id, commission_pct
FROM employees
WHERE commission_pct = null;  --0행

 

 

--수당을 안 받는 사원
SELECT employee_id, commission_pct
FROM employees
WHERE commission_pct is null; --72행

 

 

 

--수당을 받는 사원
SELECT employee_id, commission_pct
FROM employees
WHERE commission_pct is not null; --35행

 

 

--부서가 없는 사원의 사번, 부서id를 출력하시오
SELECT employee_id, department_id
FROM employees
WHERE department_id is null; --1행

 

 

 

--관리자가 없는 사원의 모든 정보를 출력하시오
SELECT *
FROM employees
WHERE manager_id is null;

 

 

--100, 101, 145, 146번 사원의 수당을 출력하시오
SELECT commission_pct
FROM employees
WHERE employee_id IN (100,101,145,146);

 

 

--수당을 안 받거나 수당이 0.3 또는 0.4인 사원을 출력하시오
SELECT employee_id, commission_pct
FROM employees
WHERE  commission_pct IN (NULL, 0.3, 0.4); --(오답)commission_pct이 null인 값만 확인되지 않음

 

-- commission_pct = null OR commission_pct = 0.3 OR commission_pct = 0.4
SELECT employee_id, commission_pct
FROM employees
WHERE  commission_pct IS NULL OR commission_pct IN(0.3, 0.4); --(정답)80행

 

 

--수당을 받고 수당이 0.3이 아니고 0.4도 아닌 사원을 출력하시오
SELECT employee_id, commission_pct
FROM employees
WHERE  commission_pct NOT IN (NULL, 0.3, 0.4); --(오답)commission_pct이 null의 비교가 되지 않아 결과가 false로 나옴
-- commission_pct <> null AND commission_pct <> 0.3 AND commission_pct <> 0.4

SELECT employee_id, commission_pct
FROM employees
WHERE  commission_pct IS NOT NULL AND commission_pct NOT IN(0.3, 0.4); --(정답)27행

---