SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN 100
ORDER BY EMPLOYEE_ID
;
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (60, 90, 100)
ORDER BY EMPLOYEE_ID
;
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID NOT IN (60, 90, 100)
ORDER BY EMPLOYEE_ID
;
SELECT *
FROM LOCATIONS
WHERE COUNTRY_ID IN JP
;
SELECT *
FROM LOCATIONS
WHERE COUNTRY_ID IN (JP, CA)
;
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (60, 90, 100)
AND JOB_ID = 'IT_PROG' -- 값을 하나만 가질때는 = 쓰는게 좋다(연산처리), 문자는 ' '
ORDER BY EMPLOYEE_ID
;
SELECT *
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL
;
SELECT *
FROM EMPLOYEES
WHERE MANAGER_ID IS NOT NULL
;
SELECT CITY
FROM LOCATIONS
WHERE STATE_PROVINCE IS NULL
ORDER BY CITY DESC
;
SELECT CITY
FROM LOCATIONS
WHERE COUNTRY_ID IN ('JP', 'US', 'CA', 'UK')
AND STATE_PROVINCE IS NOT NULL
ORDER BY COUNTRY_ID ASC
, CITY DESC
;
SELECT *
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'S%'
AND lAST_NAME LIKE 'S%'
;
SELECT *
FROM EMPLOYEES
WHERE FIRST_NAME LIKE '%ev%'
OR LAST_NAME LIKE '%ev%'
;
SELECT FIRST_NAME || ' ' || LAST_NAME NAME
FROM EMPLOYEES
;
SELECT FIRST_NAME || ' ' || LAST_NAME "이름"
FROM EMPLOYEES
;
SELECT SALARY / 12 MONTHLY_PAY
FROM EMPLOYEES
;
SELECT ROUND(SALARY / 12, 0) MONTHLY_PAY
FROM EMPLOYEES
;
SELECT EMPLOYEE_ID, ROUND (SALARY + (SALARY * COMMISSION_PCT)) SALARY
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
;
-- 현재 시간 출력하기
SELECT SYSDATE
FROM DUAL -- DUMMY TABLE
;
SELECT *
FROM DUAL
;
SELECT HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE = '2003/06/17' -- 성능에 문제
;
SELECT HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE = TO_DATE('2003/06/17') -- 문자를 날짜로 바꿈
;
SELECT TO_CHAR(SYSDATE, 'YYYY. MM. DD. HH24:MI:SS') -- 2018-08-01 HH24는 24시간기반
FROM DUAL
;
SELECT TO_DATE(SYSDATE, 'YYYY-MM-DD 00:00:00') -- 날짜 검색때 많이 쓰임
FROM DUAL
;
SELECT *
FROM DUAL
;
SELECT FIRST_NAME || ' ' || LAST_NAME NAME
, CASE
WHEN COMMISSION_PCT IS NOT NULL THEN
SALARY + SALARY * COMMISSION_PCT
ELSE
SALARY
END SALARY
, TO_CHAR(HIRE_DATE, 'YYYY. MM. DD. ') HIRE_DATE
FROM EMPLOYEES
;