01. AS 별칭, 산술연산, DISTINCT 중복제거, || 연결, DESC 테이블 정보 확인.
SELECT * FROM departments;
SELECT DEPARTMENT_ID FROM DEPARTMENTS;
SELECT DEPARTMENTS_ID AS 부서번호 FROM DEPARTMENTS;
SELECT FIRST_NAME 이름, JOB_ID 직책, SALARY 월급 FROM EMPLOYEES;
SELECT FIRST_NAME 이름, JOB_ID 직책, SALARY 월급, SALARY-100 월급수정 FROM EMPLOYEES;
SELECT FIRST_NAME 이름,
JOB_ID 직책,
SALARY 월급,
(SALARY-1000)*0.05 보너스
FROM EMPLOYEES;
DISTINCT, ||' '||, DESC
SELECT DISTINCT JOB_ID FROM EMPLOYEES;
select employee_id 직원번호, first_name ||' '|| last_name 풀네임
from employees;
select employee_id 직원번호, first_name ||' '|| last_name 진짜이름, email ||'@company.com' 회사이메일 from employees;
DESC employees;
DESC COUNTRIES;
02. where절 사용, 논리연산, IN 연산자, Between연산자 , Like 연산자, Order 정렬.
select *
from employees
where salary > 14000;
select *
from employees
where last_name = 'King';
select *
from employees
where hire_date < '2002/06/10';
Select *
from employees
where employee_id= 100;
Select *
from employees
where first_name= 'David';
Select *
from employees
where employee_id < 105;
Select *
from JOB_HISTORY
where start_date > '2006/03/03';
Select *
from departments
where location_id != 1700;
SELECT last_name 이름, department_id 부서번호, salary 월급
from employees
Where (department_id = 60 OR department_id = 80)
And salary >10000;
Select *
from employees
WHERE SALARY > 4000 AND JOB_ID ='IT_PROG';
Select *
from employees
WHERE SALARY > 4000 AND (JOB_ID ='IT_PROG' OR JOB_ID ='FI_ACCOUNT' );
IN
SELECT *
FROM employees
WHERE salary IN ( 4000, 3000, 2700 );
SELECT *
FROM employees
WHERE salary NOT IN ( 4000, 3000, 2700 );
SELECT *
FROM employees
WHERE salary IN ( 10000, 17000, 24000 );
SELECT *
FROM employees
WHERE department_ID IN ( 30 , 50, 80, 100, 110 );
BETWEEN A and B
SELECT *
FROM employees
WHERE salary BETWEEN 9000 AND 10000;
SELECT *
FROM employees
WHERE salary BETWEEN 10000 AND 20000;
SELECT *
FROM employees
WHERE hire_date BETWEEN '2004/01/01' AND '2004/12/30';
SELECT *
FROM employees
WHERE salary NOT BETWEEN 7000 AND 17000;
LIKE '', NOT LIKE '', '%%'
SELECT * FROM employees WHERE last_name Like 'B%';
SELECT * FROM employees WHERE last_name Like '%b%';
SELECT * FROM employees WHERE last_name Like '_____%y';
SELECT * FROM employees WHERE job_id Like '%AD%';
SELECT * FROM employees WHERE job_id Like 'AD___' ;
SELECT * FROM employees WHERE job_id Like 'AD%___' ;
SELECT * FROM employees WHERE phone_number Like '%1234';
SELECT * FROM employees WHERE phone_number NOT Like '%3%' AND phone_number LIKE '%9';
SELECT * FROM employees WHERE job_id Like '%MGR%' or job_id Like '%ASST%';
null check
SELECT * From employees where commission_pct is null;
SELECT * From employees where commission_pct is not null;
SELECT * From employees where manager_id is null;
ORDER BY
SELECT * FROM employees ORDER BY employee_id DESC;
SELECT department_id, employee_id, first_name, last_name FROM employees ORDER BY department_id, employee_id DESC;
SELECT department_id, last_name, salary*12 연봉 FROM employees ORDER BY 연봉 DESC;
SELECT department_id, last_name, salary*12 연봉 FROM employees ORDER BY 3;
SELECT employee_id, first_name, last_name FROM employees ORDER BY employee_id;
SELECT * FROM employees WHERE job_id LIKE '%CLERK%' ORDER BY SALARY DESC;
SELECT * FROM employees WHERE employee_id Between 120 AND 150
ORDER BY department_id DESC , salary desc;