day02
// μΆλ ₯λ λ 컬λ₯¨μ μ΄λ¦μ΄ μ€μ ν κ°μΌλ‘ λ°λμ΄μ λμ΄
SELECT e.EMPLOYEE_ID AS "ID"
, e.SALARY "SAL" // AS μλ΅νκ³ " " μΌλ‘ λ£μ΄λ κ°λ₯
FROM EMPLOYEES e
;
SELECT DISTINCT JOB_ID
FROM EMPLOYEES e
;
SELECT EMPLOYEE_ID , LAST_NAME , MANAGER_ID , DEPARTMENT_ID FROM EMPLOYEES e WHERE DEPARTMENT_ID = 50 OR MANAGER_ID = 100 ;
SELECT FIRST_NAME , LAST_NAME
, DEPARTMENT_ID
FROM EMPLOYEES e
WHERE NOT (DEPARTMENT_ID = 50)
;
SELECT FIRST_NAME , LAST_NAME , DEPARTMENT_ID FROM EMPLOYEES e WHERE DEPARTMENT_ID <> 50 ;
SELECT FIRST_NAME , LAST_NAME
, DEPARTMENT_ID
FROM EMPLOYEES e
WHERE DEPARTMENT_ID != 50
;
SELECT FIRST_NAME , LAST_NAME , SALARY
FROM EMPLOYEES e
WHERE SALARY >= 7000
AND SALARY <= 10000
;
SELECT FIRST_NAME , LAST_NAME , SALARY
FROM EMPLOYEES e
WHERE SALARY BETWEEN 6000 AND 10000 -- SALARY 6000μμ 10000 μ¬μ΄
;
SELECT *
FROM EMPLOYEES e
WHERE SALARY IN (6500,7700,13000)
;
employees ν μ΄λΈμμ employee_id, first_name, last_anem, job_idλ₯Ό μΆλ ₯νλλ°,
job_idκ° ST_MANμ΄κ³ , manager_idκ° 100μ΄κ³ , salaryκ° 5000μ΄μμΈ μ¬λ.SELECT EMPLOYEE_ID , FIRST_NAME , LAST_NAME , JOB_ID FROM EMPLOYEES e WHERE JOB_ID = 'ST_MAN' AND MANAGER_ID = 100 AND SALARY >= 5000 ;
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 MANAGER_ID != 100 AND SALARY BETWEEN 5000 AND 10000 ;
: dλ‘ λλλ μ¬λ
SELECT FIRST_NAME , LAST_NAME ,
EMPLOYEE_ID
FROM EMPLOYEES e
WHERE FIRST_NAME LIKE '%d'
;
: __aμΈ μ¬λ % μ무거λ μλ μκ΄μμ
SELECT FIRST_NAME , LAST_NAME , EMPLOYEE_ID
FROM EMPLOYEES e
WHERE FIRST_NAME LIKE '__a%'
;
: nullκ°μΈ μ¬λ
SELECT FIRST_NAME , LAST_NAME ,
EMPLOYEE_ID , COMMISSION_PCT
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS null
;
: null κ°μ΄ μλμ¬λ
SELECT FIRST_NAME , LAST_NAME ,
COMMISSION_PCT
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS NOT NULL
;
: μ€λ¦μ°¨μ, μλ΅κ°λ₯
SELECT FIRST_NAME , LAST_NAME ,
COMMISSION_PCT
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY FIRST_NAME
;
: λ΄λ¦Όμ°¨μ
SELECT FIRST_NAME , LAST_NAME ,
COMMISSION_PCT
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY FIRST_NAME DESC
;
: κ° λνκΈ°
SELECT sum(SALARY)
FROM EMPLOYEES e
;
: λͺλͺ μΈμ§ μΈμ£Όλκ²
SELECT COUNT(*)
FROM EMPLOYEES e
;
SELECT COUNT(EMPLOYEE_ID) "μ 체 μΈμ"
FROM EMPLOYEES e
;
SELECT COUNT(DEPARTMENT_ID) "μ μΈ λ―Έν¬ν¨ μΈμ"
FROM EMPLOYEES e
;
SELECT COUNT(COMMISSION_PCT) "컀미μ
μΈμ"
FROM EMPLOYEES e
;
SELECT COUNT(DISTINCT DEPARTMENT_ID) "λΆμμ κ°―μ"
FROM EMPLOYEES e
;
: νκ·
SELECT AVG(SALARY)
FROM EMPLOYEES e
;
: μ΅λκ°
SELECT MAX(SALARY) "λ΄κΈ νκ· "
FROM EMPLOYEES e
;
SELECT MAX(HIRE_DATE) "μ μΌ λ¦μ μ
μ¬μΌ"
FROM EMPLOYEES e
;
: μ΅μκ°
SELECT MIN(SALARY) "μ μ κΈμ¬"
FROM EMPLOYEES e
;
SELECT MIN(HIRE_DATE) "μ μΌ λΉ λ₯Έ μ
μ¬μΌ"
FROM EMPLOYEES e
;
- μ€λΌν΄μμ κΈ°λ³ΈμΌλ‘ μ 곡νλ dummy table
- κ°λ¨νκ² ν¨μλ₯Ό μ΄μ©ν΄μ κ³μ°κ²°κ³Όκ°μ
νμΈ ν λ μ¬μ©νλ ν μ΄λΈ- dual ν μ΄λΈμ μ¬μ©μκ° ν¨μ(κ³μ°)λ₯Ό μ€νν λ
μμλ‘ μ¬μ©νλλ° μ ν©νλ€.- ν¨μμ λν μ°μμ μκ³ μΆμλ νΉμ ν μ΄λΈμ
μ΄μ©νμ¬ ν¨μμ κ°μ λ¦¬ν΄ λ°μ μ μλ€.
: μ λκ°
SELECT ABS(-23)
FROM dual
;
: λ°μ¬λ¦Ό
SELECT ROUND(0.123) , ROUND(0.5678) -- 0 / 1 μΆλ ₯
FROM dual
;
: μμ«μ μ리 μλ₯΄κΈ°
SELECT TRUNC(1234.56789) -- 1234 μΆλ ₯
FROM dual
;
SELECT TRUNC(1234.56789,2) -- 1234.56 κ° μΆλ ₯
FROM dual
;
SELECT TRUNC(1234.56789,-1) -- 1230 κ° λμ¨λ€
FROM dual
;
μ°λ΄μ΄ 12000 μ΄μλλ μ§μλ€μ LAST_NAME λ° μ°λ΄μ μ‘°ννλ€.
λ¨, κΈμ¬ μ€λ¦μ°¨μμΌλ‘ μ‘°ν
SELECT LAST_NAME , SALARY
FROM EMPLOYEES e
WHERE SALARY >= 12000
ORDER BY SALARY
;
μ¬μλ²νΈκ° 176 μΈ μ¬λμ LAST_NAME κ³Ό λΆμ λ²νΈλ₯Ό μ‘°ννλ€.
SELECT EMPLOYEE_ID , LAST_NAME , DEPARTMENT_ID
FROM EMPLOYEES e
WHERE EMPLOYEE_ID = 176
;
μ°λ΄μ΄ 5000 μμ 12000μ λ²μ μ΄μΈμΈ μ¬λλ€μ
LAST_NAME λ° μ°λ΄μ μ‘°ννλ€.
SELECT LAST_NAME , SALARY
FROM EMPLOYEES e
WHERE SALARY NOT BETWEEN 5000 AND 12000
;
20 λ² λ° 50 λ² λΆμμμ 근무νλ λͺ¨λ μ¬μλ€μ
LAST_NAME λ° λΆμ λ²νΈλ₯Ό μνλ²³μμΌλ‘ μ‘°ννλ€.
SELECT LAST_NAME , DEPARTMENT_ID
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 20
OR DEPARTMENT_ID = 50
ORDER BY LAST_NAME ASC
;
20 λ² λ° 50 λ² λΆμμ 근무νλ©°,
μ°λ΄μ΄ 5000 ~ 12,000 μ¬μ΄μΈ μ¬μλ€μ
LAST_NAME λ° μ°λ΄μ μ‘°ννλ€.
SELECT LAST_NAME , SALARY , DEPARTMENT_ID
FROM EMPLOYEES e
WHERE DEPARTMENT_ID IN (20, 50)
AND SALARY BETWEEN 5000 AND 12000
;
LAST_NAME 첫 κΈμκ° A μΈ μ¬μλ€μ LAST_NAME μ μ‘°ννλ€.
SELECT LAST_NAME
FROM EMPLOYEES e
WHERE LAST_NAME LIKE 'A%'
;
맀λμ κ° μλ μ¬λλ€μ LAST_NAME λ° JOB_ID λ₯Ό μ‘°ννλ€.
SELECT LAST_NAME , JOB_ID , MANAGER_ID
FROM EMPLOYEES e
WHERE MANAGER_ID IS NULL
;
컀미μ
μ λ²λ λͺ¨λ μ¬μλ€μ LAST_ANME,
μ°λ΄ λ° μ»€λ―Έμ
μ μ‘°ννλ€.
λ¨,μ°λ΄ μμ μ λ ¬νλ€.
SELECT LAST_NAME , SALARY , COMMISSION_PCT
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY SALARY DESC
;