1. dual
- oracle에서 기본으로 제공하는 dummy table
- 오라클 자체에서 제공되는 테이블
- 간단하게 함수를 이용해서 계산 결과값을 확인 할 때 사용하는 테이블
- dual 테이블은 사용자가 함수(계산)을 실행할 때 임의로 사용하는데 적합하다.
- 함수에 대한 쓰임을 알고 싶을 때 특정 테이블을 생성할 필요 없이 daul 테이블을
사용하여 함수의 값을 리턴 받을 수 있다.
기능
distinct : 중복 제거
SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES e
;
OR
SELECT *
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 50
OR MANAGER_ID = 100
;
not, <> , != 3가지 다 not 의 의미를 가진다
SELECT *
FROM EMPLOYEES e
WHERE NOT (DEPARTMENT_ID = 50)
;
SELECT *
FROM EMPLOYEES e
WHERE DEPARTMENT_ID <> 50
;
SELECT *
FROM EMPLOYEES e
WHERE DEPARTMENT_ID != 50
;
salary xx 이상 ~ xx 이하
SELECT *
FROM EMPLOYEES e
WHERE SALARY >= 4000 AND SALARY <= 8000
;
between A and B
SELECT *
FROM EMPLOYEES e
WHERE SALARY BETWEEN 4000 AND 8000
;
in( ) , or 와 동일
SELECT *
FROM EMPLOYEES e
WHERE SALARY = 6500
OR SALARY = 7700
OR SALARY = 13000
;
SELECT *
FROM EMPLOYEES e
WHERE SALARY IN (6500, 7700, 13000)
;
like : 이름이 D 로 시작하는 사람
SELECT FIRST_NAME,
LAST_NAME
FROM EMPLOYEES e
WHERE FIRST_NAME LIKE 'D%'
;
'd'로 끝나는 사람
SELECT FIRST_NAME,
LAST_NAME
FROM EMPLOYEES e
WHERE FIRST_NAME LIKE '%d'
;
__a (a 앞에 언더바 2개)
SELECT FIRST_NAME,
LAST_NAME
FROM EMPLOYEES e
WHERE FIRST_NAME LIKE '__a%'
;
null 조회
SELECT *
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS NULL
;
not null 인 경우
SELECT *
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS NOT NULL
;
order by ASC : 오름차순 정렬
ASC 는 생략가능하다
SELECT *
FROM EMPLOYEES e
ORDER BY SALARY ASC
;
order by DESC: 내림차순 정렬
SELECT *
FROM EMPLOYEES e
ORDER BY SALARY DESC
;
sum()
SELECT sum(salary)
FROM EMPLOYEES e
;
count (컬럼 / *)
SELECT COUNT(*) q
FROM EMPLOYEES e
;
SELECT COUNT(EMPLOYEE_ID) "cnt"
FROM EMPLOYEES e
;
first_name을 기준으로 count, 중복을 제거한 frist_name 을 count
SELECT COUNT(FIRST_NAME)
COUNT(DISTINCT FIRST_NAME)
FROM EMPLOYEES e
;
평균(avg)
SELECT AVG(SALARY)
FROM EMPLOYEES e
;
SELECT AVG(SALARY)
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 80
;
max : 최대값
SELECT MAX(SALARY)
FROM EMPLOYEES e
;
SELECT MAX(HIRE_DATE)
FROM EMPLOYEES e
;
min : 최소값
SELECT MIN(SALARY)
FROM EMPLOYEES e
;
SELECT MIN(HIRE_DATE)
FROM EMPLOYEES e
;
