SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES e
WHERE SALARY = 4000
AND SALARY = 8000
;
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES e
WHERE SALARY BETWEEN 4000 AND 8000
;
SELECT FIRST_NAME, LSAT_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY = 6500
OR SALARY = 7700
OR SALARY = 13000
;
SELECT FIRST_NAME , LAST_NAME , SALARY
FROM EMPLOYEES e
WHERE SALARY IN (6500, 7700, 13000)
;
-- employees 테이블에서 employee_id, first_name, last_name, job_id, manager_id, salary를 조회
-- department_id 가 10 또는 30 또는 100 또는 90에 속하고,
-- salary가 5000이상 10000이하 이고
-- manager_id가 100이 아닌 사람을 조회
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, MANAGER_ID, SALARY
FROM EMPLOYEES e
WHERE DEPARTMENT_ID IN(10, 30, 100 ,90)
AND SALARY BETWEEN 5000 AND 10000
AND MANAGER_ID != 100
-- AND MANAGER_ID <> 100
-- AND NOT(MANAGER_ID = 100)
;
or과 in()은 하나 이상의 조건이 만족되면 데이터 출력
_ : 해당 위치에 있는 문자 하나 (ex) 컬럼명 LIKE 'J_n')
% : 해당 위치에 있는 0개 이상의 문자 (ex) 컬럼명 LIKE 'Jan%')
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES e
WHERE FIRST_NAME LIKE 'D%'
;
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES e
WHERE FIRST_NAME LIKE '%d'
;
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES e
WHERE FIRST_NAME LIKE '--e%'
;
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE '%A%'
;
SELECT FIRST_NAME , LAST_NAME , JOB_ID , COMMISSION_PCT
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS NULL
;
SELECT FIRST_NAME , LAST_NAME , JOB_ID , COMMISSION_PCT
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS NOT NULL
;
SELECT *
FROM EMPLOYEES e
ORDER BY FIRST_NAME ASC
;
SELECT *
FROM EMPLOYEES e
ORDER BY FIRST_NAME
;
SELECT *
FROM EMPLOYEES e
ORDER BY SALARY DESC
;
SELECT SUM(SALARY)
FROM EMPLOYEES e
;
SELECT COUNT(EMPLOYEE_ID)
FROM EMPLOYEES e
;
-- EMPLOYEES 테이블의 EMPLOYEE_ID의 총 갯수
SELECT COUNT(FIRST_NAME), COUNT(DISTINCT FIRST_NAME)
FROM EMPLOYEES e
;
SELECT AVG(SALARY)
FROM EMPLOYEES e
;
SELECT AVG(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
;
SELECT MAX(SALARY)
FROM EMPLOYEES e
;
SELECT MIN(SALARY)
FROM EMPLOYEES e
;
SELECT ABS(-23)
FROM dual
;
SELECT ABS(23) AS "abs"
FROM dual
;
SELECT ROUND(0.123), ROUND(0.543)
FROM dual
;
SELECT ROUNT(5.623), ROUND(2.446)
FROM dual
;
SELECT TRUNC(1234.123456789)
FROM dual
;
SELECT TRUNC(1234.123456789, 2)
FROM dual
;
-- 소숫점 두째자리까지 남기고 버림
SELECT TRUNC(1234.123456789, -1)
FROM dual
;
-- 일의 자리까지 버림
-- 1. salary가 12000 이상되는 직원들의
-- last_name, salary를 오름차순으로 조회
-- salary 로 오름차순
SELECT LAST_NAME ,SALARY
FROM EMPLOYEES e
WHERE SALARY >= 12000
ORDER BY SALARY, LAST_NAME
;
SELECT FIRST_NAME , LAST_NAME
FROM EMPLOYEES e
ORDER BY FIRST_NAME , LAST_NAME DESC
-- 내림차순
;
-- 2. 사원번호가 176인 사람의 last_name, department_id 조회
SELECT LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES e
WHERE EMPLOYEE_ID = 176
;
-- 3. 연봉이 5000에서 12000의 범위 이외인 사람들의
-- last_name 및 salary 조회
SELECT LAST_NAME , SALARY
FROM EMPLOYEES e
WHERE NOT SALARY BETWEEN 5000 AND 12000
;
-- 4. 20번 부서 또는 50번 부서 에서 근무하는 모든 사원들의
-- last_name, department_id를
-- 알파벳 순으로 조회
SELECT LAST_NAME , DEPARTMENT_ID
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 20
OR DEPARTMENT_ID = 50
ORDER BY LAST_NAME ASC
;
-- 5. 20번 및 50번 부서에서 근무하며, 연봉이 5000 이상
-- 12000 사이인 사원들의
-- last_name, salary를 조회
SELECT LAST_NAME , SALARY , DEPARTMENT_ID
FROM EMPLOYEES e
WHERE SALARY BETWEEN 5000 AND 12000
AND DEPARTMENT_ID IN (20, 50)
;
-- 6. last_name 첫 글자가 A인 사원들의 last_name을 조회
SELECT LAST_NAME
FROM EMPLOYEES e
WHERE LAST_NAME LIKE 'A%'
;
-- 7. manager_id가 없는 사람의 last_name과 job_id를 조회
SELECT LAST_NAME , JOB_ID
FROM EMPLOYEES e
WHERE MANAGER_ID IS NULL
;
-- 8. 커미션이 있는 모든 사원들의 last_name, salary, 커미션을 조회.
-- 연봉 역순으로 조회한다.
SELECT LAST_NAME , SALARY , COMMISSION_PCT
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY SALARY DESC
;