SELECT
EMPLOYEE_ID
, FIRST_NAME
, LAST_NAME
, JOB_ID
FROM EMPLOYEES
WHERE email = 'SKING';
-- 주석설정->Ctrl + - // 주석해제->Ctrl+Shift + -
-- 테이블 NAME, --줄 맞춤 SELECT라인 FIRST라인 ,세미콜론주의
;
SELECT
COUNT(JOB_ID)
FROM EMPLOYEES
;
SELECT
DISTINCT JOB_ID
--중복행의 제거 DISTINCT + HOW FIND?
FROM EMPLOYEES
;
SELECT
JOB_ID
, FIRST_NAME
, LAST_NAME
, SALARY
FROM EMPLOYEES
WHERE SALARY <= 10000
;
SELECT
JOB_ID
, FIRST_NAME || ' ' || LAST_NAME AS NAME
, EMPLOYEE_ID
, SALARY
, DEPARTMENT_ID
FROM EMPLOYEES
WHERE LOWER(JOB_ID) = 'fi_mgr'
-- 대문자 소문자를 모두다 확인할 수 있는 필수 입력요소!!!
;
--2003년도에 입사한 사람을 전부 찾아라
SELECT
JOB_ID
, FIRST_NAME || ' ' || LAST_NAME AS NAME
, EMPLOYEE_ID
, SALARY
, HIRE_DATE
, DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE >= '01-JAN-03'
AND HIRE_DATE <= '31-DEC-03'
ORDER BY HIRE_DATE
-- TO_CHAR(HIRE_DATE,'YYYY') = '2003' TO_CHAR=어떤걸로 바꾼다
;
SELECT
JOB_ID
, FIRST_NAME || ' ' || LAST_NAME AS NAME
, EMPLOYEE_ID
, SALARY
, HIRE_DATE
, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY
BETWEEN 10000 AND 20000
;
SELECT
JOB_ID
, FIRST_NAME || ' ' || LAST_NAME AS NAME
, EMPLOYEE_ID
, SALARY
, HIRE_DATE
, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY >= 10000
AND SALARY <= 12000
;
SELECT
JOB_ID
, FIRST_NAME || ' ' || LAST_NAME AS NAME
, EMPLOYEE_ID
, SALARY
, HIRE_DATE
, DEPARTMENT_ID
FROM EMPLOYEES
WHERE JOB_ID NOT IN('SA_MAN','PU_CLERK','AD_ASST')
-- IN 함수
;
SELECT
DEPARTMENT_ID
, FIRST_NAME || ' ' || LAST_NAME AS NAME
, JOB_ID
, SALARY
, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE
LIKE '%05'
-- LIKE 구문
-- WHERE HIRE_DATE LIKE '%현%' <-중간만 찾는다
;
;
ALTER SESSION SET NLS_DATE_FORMAT = 'YY-MM-DD';
;
SELECT
JOB_ID
, FIRST_NAME || ' ' || LAST_NAME AS NAME
, EMPLOYEE_ID
, SALARY
, HIRE_DATE
, DEPARTMENT_ID
, MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL
-- 메니저 네임중에 NULL인 것을 찾는다 IS <-
;
SELECT
JOB_ID
, FIRST_NAME || ' ' || LAST_NAME AS NAME
, EMPLOYEE_ID
, SALARY
, HIRE_DATE
, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY >= 10000 AND JOB_ID = 'SA_MAN'
-- AND 연산자 : 두개의 조건을 모두 만족하는 것
;
SELECT
FIRST_NAME || ' ' || LAST_NAME AS NAME
, JOB_ID
, EMPLOYEE_ID
, SALARY
, HIRE_DATE
, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY >= 10000
OR UPPER(JOB_ID) = 'AD_PRES'
;
SELECT
FIRST_NAME || ' ' || LAST_NAME AS NAME
, JOB_ID
, EMPLOYEE_ID
, SALARY
, HIRE_DATE
, DEPARTMENT_ID
FROM EMPLOYEES
WHERE (UPPER(JOB_ID) = 'SA_MAN' OR JOB_ID = 'AD_PRES')
AND SALARY >= 12000
--NOT > AND > OR 우선순위에 의해서 OR가 우선순위일 경우는 ()로 처리한다.
--한줄삭제 Ctrl + Y
;
SELECT
FIRST_NAME || ' ' || LAST_NAME AS NAME
-- AS->알리안스
, HIRE_DATE
, JOB_ID
, EMPLOYEE_ID
, SALARY
, DEPARTMENT_ID
FROM EMPLOYEES
ORDER BY HIRE_DATE
;
SELECT
DEPARTMENT_ID
, SALARY
, JOB_ID
, FIRST_NAME || ' ' || LAST_NAME AS NAME
, EMPLOYEE_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (80,90) AND SALARY >= 12000
-- 차순의 순서는 SELECT->FROM->WHERE ORDER BY 순으로 진행된다
ORDER BY DEPARTMENT_ID , SALARY
--ORDER BY 문에서는 차순의 정렬이 ,로 표시되고 그 다음으로 이루어진다(표시없을시 오름차순)
'IT > Oracle' 카테고리의 다른 글
오라클 함수정리 4탄 (그룹함수) (0) | 2015.07.18 |
---|---|
오라클 정규화 (0) | 2015.07.15 |
오라클 삭제 (oracle 레지스트리 완전삭제) (0) | 2015.04.17 |
오라클 함수정리 3탄 (단일함수) (0) | 2014.12.15 |
오라클 함수정리 2탄 (0) | 2014.12.15 |