SQL - SELECT

SELECT FIRST_NAME

, LAST_NAME

FROM EMPLOYEES

;

SELECT *

FROM EMPLOYEES

;

SELECT CITY

FROM LOCATIONS

;

SELECT DEPARTMENT_NAME

FROM DEPARTMENTS

;

SELECT COUNTRY_ID

, COUNTRY_NAME

FROM COUNTRIES

;

SELECT DISTINCT MANAGER_ID

, FIRST_NAME

FROM EMPLOYEES

ORDER BY MANAGER_ID DESC

;

SELECT *

FROM EMPLOYEES

ORDER BY EMPLOYEE_ID ASC

;

SELECT *

FROM EMPLOYEES

ORDER BY SALARY DESC

;

SELECT *

FROM EMPLOYEES

ORDER BY HIRE_DATE ASC

;

SELECT *

FROM EMPLOYEES

ORDER BY HIRE_DATE

, SALARY DESC

;

SELECT FIRST_NAME

FROM EMPLOYEES

ORDER BY FIRST_NAME

;

SELECT *

FROM EMPLOYEES

WHERE SALARY = 24000

AND EMPLOYEE_ID = 100

;

SELECT *

FROM EMPLOYEES

WHERE FIRST_NAME = 'Steven'

AND SALARY = 2200

;

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

;