sqldeveloper-2

SELECT employee_id, first_name ||' '|| last_name AS full_name, hire_date, job_id, department_id
FROM employees
WHERE department_id = 30
AND NOT salary BETWEEN 10000 AND 15000;

--SQL 연산자 실습

--1) 산술연산자 : +, -, *, /(select, where)
--2) 연결연산자 : || (select, where)
--CONCAT(str1, str2)

SELECT (절)
FROM (절)
WHERE (절);

--3) 비교연산자 : >, >=, <, <=, =, != (where)
--4) 논리연산자 : AND, OR, NOT

NOT --> NOT BETWEEN A AND B, NOT IN, NOT LIKE ... 다른 연산자와 조합해서 사용
--5) 범위연산자 : BETWEEN A AND B
--6) 문자열 패턴 : LIKE
--7) 범위연산자 : BETWEEN A AND B
--8) IN 연산자 : OR 연산자

--4)논리연산자 실습
--AND : 조건이 모두 true일때 최종 true
--OR : 조건이 하나라도 true일 때 최종 true
-- NOT : true 조건 <--> false

--employees : 사원 (정보) 테이블
--Q. 사원 중 30번 부서에 근무하며 급여가 10000 이하(상) (소속된) 사원의 정보를 조회한다.

SELECT employee_id, first_name, job_id, salary, department_id
FROM employees
WHERE department_id =30 AND salary <= 10000;

DESC jobs;

SELECT * 
FROM jobs;

--Q. 사원 중 30번 부서에 근무하며 급여가 10000 이하(상)이고, 입사일이 05년도 이전(이후)에 입사한 (소속된) 사원의 정보를 조회한다.

SELECT employee_id,first_name,hire_date, salary, department_id 
FROM employees
WHERE department_id=30 AND salary <= 10000 AND hire_date <= '04/12/31';

--Q. 사원 중 30번 또는 50번 또는 70번 부서에 근무하는 사원의 정보를 조회하시오

SELECT employee_id,first_name,hire_date, salary, department_id 
FROM employees
WHERE department_id = 30 OR department_id = 50 OR department_id = 70;

 

--Q. 30번 부서에서 급여가 15000 이하인 사원과 50번 부서에서 급여가 5000 이상인 사원을 조회하시오.

SELECT employee_id,first_name,hire_date, salary, department_id 
FROM employees
WHERE (department_id = 30 AND salary <= 15000) 
OR (department_id = 50 AND salary >= 5000);

 

--Q. 사번이 110번에서 120번까지 사원의 정보(사번, 이름, 부서코드, 입사년도, 업무코드)를 조회하시오
--단, 이름은 성과 이름으로 합쳐서 full_name으로 조회하시오

SELECT employee_id, first_name ||' '|| last_name AS full_name, hire_date, job_id, department_id
FROM employees
WHERE employee_id >= 110
AND employee_id <= 120;

SELECT employee_id, first_name ||' '|| last_name AS full_name, hire_date, job_id, department_id
FROM employees
WHERE employee_id BETWEEN 110 AND 120;

 

--Q. 급여가 50번 부서원 중 급여각 10000 - 15000 사이에 해당하는 사원의 정보를 조회하시오

SELECT employee_id, first_name ||' '|| last_name AS full_name, hire_date, job_id, department_id
FROM employees
WHERE department_id = 30
AND salary BETWEEN 10000 AND 15000;

-- NOT 컬럼명 BETWEEN A AND B

-- 컬럼명 NOT BETWEEN A AND B

 

복제 : Ctrl + Shift + D

 

6) 문자열 패턴 실습
LIKE :특정문자열의 필터링
--1) % : 여러 개의 문자열
--2) _ : 한개의 문자

--Q. 사원의 이름이 s로 시작하는 (또는 s로 끝나는 ) 사원의 정보를 모두 조회하시오

SELECT *
FROM employees
where first_name LIKE 'S%';

--Q. 사원의 이름에 b가 포함된 사원의 정보중 사번, 이름,이름, 부서번호, 업무코드를 조회하시오.

SELECT employee_id, first_name, department_id, job_id
FROM employees
WHERE first_name LIKE '%b%';

SELECT employee_id, first_name, department_id, job_id
FROM employees
WHERE NOT first_name LIKE '__b%';

SELECT employee_id, first_name, department_id, job_id
FROM employees
WHERE first_name NOT LIKE '__b%';

-- 8) NULL : 빈값 / 이상한 값 ==> 잘못된 값 (가능성)
-- NULL은 비교의 대상이 아님 ==> 필요에 따라 NULL을 다른 값으로 치환해야할 수 있다.

 

https://www.w3schools.com/

W3Schools.com

 

SELECT * 
FROM employees
WHERE commission_pct IS NULL;
--WHERE commission_pct=NULL (x)

 

-- 데이터 정렬
-- ORDER BY (절)

SELECT (절)
FROM (절)
WHERE (절)
-- . . . (추가절)
ORDER BY 기준컬럼 [ASC | DESC] : 항상 마지막에 위치 (특별한 경우를 제외하고..)
-- Ascending : 오름차순 (작은 수 --> 큰 수), 정렬의 기본 값(=생략 가능)
-- Descending : 내림차순 (큰 수 --> 작은 수)

 

--ORDER BY 절에 컬럼명 또는 컬럼번호 또는 ALIAS를 사용할 수 있다.
--ORDER BY 절에 기준에 따른 정렬 순서를 여러 개 지정할 수 있다.
--ex) 급여는 오름차순, 부서번호는 내림차순.. => ORDER BY salary ASC, department_id DESC

 

SQL 연산자 복습

--1)사번이 200인 사원의 이름과 부서번호를 조회하는 쿼리문을 작성한다.
SELECT employee_id, first_name, department_id
from employees
where employee_id = 200;

--2)급여가 3000에서 15000 사이에 포함되지 않는 사원의 사번, 이름, 급여 정보를 조회하는 쿼리문  을 작성한다. (단, 이름은 성과 이름을 공백문자를 두어 합쳐서 조회한다.  예를 들어 이름이 John 이고 성이 Seo 이면 John Seo 로 조회되도록 한다.)
SELECT employee_id, first_name || ' ' || last_name AS full_name, salary
from employees
where NOT salary BETWEEN 3000 AND 15000
ORDER BY 3;

--3) 부서번호 30과 60 에 소속된 사원의 사번, 이름, 부서번호, 급여를 조회하는데, 이름을 알파벳 순서로 정렬하여 조회하는 쿼리문을 작성한다.
SELECT employee_id, first_name, department_id, salary
FROM employees
--where department_id IN(30, 60)
WHERE department_id = 30
OR department_id = 60
ORDER BY first_name;

--4) 급여가 3000에서 15000 사이 이면서, 부서번호 30또는 60에 소속된 사원의 사번, 이름, 급여를 조회하는 쿼리문을 작성한다. (단, 조회되는 컬럼명을 이름은 성과 이름을 공백문자를 두어 합쳐 name 으로, 급여는 Monthly Salary 로 조회되도록 한다.)
SELECT employee_id, first_name || ' ' || last_name AS name, salary AS "Monthly Salary"
FROM employees
WHERE department_id IN(30, 60) 
AND salary BETWEEN 3000 AND 15000;

--5) 소속된 부서번호가 없는 사원의 사번, 이름, 업무ID를 조회하는 쿼리문을 작성한다.
SELECT employee_id, first_name, job_id
FROM employees
WHERE department_id IS NULL;
 
--6) 커미션을 받는 사원의 사번, 이름, 급여, 커미션을 조회하는데, 커미션이 높은 사원부터 낮은 사원 순서로 정렬하여 조회하는 쿼리문을 작성한다.
SELECT employee_id, first_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY commission_pct DESC;


--7) 이름에 문자 z 가 포함된 사원의 사번과 이름을 조회하는 쿼리문을 작성한다.
SELECT employee_id, first_name
FROM employees
WHERE first_name LIKE '%z%';