dual테이블 OracleDB에서 제공하는 더미테이블 테스트를 쓸때 쓴다
-- SUM() : 합계
SELECT SUM(SALARY)
FROM EMPLOYEES e
;
-- COUNT() : 카운트
-- 전체 값을 가져오는게 특정 값을 가져오는거 보타 빠르다
SELECT COUNT(*)
FROM EMPLOYEES e
;
SELECT COUNT(EMPLOYEE_ID)
FROM EMPLOYEES e
;
-- 중복제거가 되어 부서의 개수가 나온다
SELECT COUNT(DISTINCT DEPARTMENT_ID)
FROM EMPLOYEES e
;
-- EMPLOYEES 테이블에서 FIRST_NAME의 갯수와 FIRST_NAME의 중복을 제거한 갯수 조회
SELECT COUNT(EMPLOYEE_ID), COUNT(DISTINCT FIRST_NAME)
FROM EMPLOYEES e
;
SELECT COUNT(EMPLOYEE_ID), COUNT(DISTINCT FIRST_NAME) AS "dFIRST_NAME"
FROM EMPLOYEES e
;
-- AVG() : 평균
SELECT AVG(SALARY)
FROM EMPLOYEES e
;
-- EMPLOYEES 테이블에서 DEPARTMENT_ID = 80인 부서의 급여 평균
SELECT AVG(SALARY)
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 80
;
-- MAC() : 최대값
SELECT MAX(SALARY)
FROM EMPLOYEES e
;
-- MIN() : 최소값
SELECT MIN(SALARY)
FROM EMPLOYEES e
;
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY),
COUNT(*), COUNT(MANAGER_ID)
FROM EMPLOYEES e
;
-- EMPLOYEES 테이블에서 고용일자가 가장 높은 사람과, 낮은 사람
SELECT MAX(HIRE_DATE), MIN(HIRE_DATE)
FROM EMPLOYEES e
;
-- abs() : 절대값 리턴
SELECT ABS(-23)
FROM dual
;
SELECT ABS(25)
FROM dual
;
-- round() : 반올림 함수
SELECT ROUND(0.123), ROUND(0.543)
FROM dual
;
SELECT ROUND(0.123), ROUND(3.543)
FROM dual
;
SELECT AVG(SALARY), ROUND(AVG(SALARY))
FROM dual
;
-- trunc() : 잘라냄, 절삭
-- trunc(n1, n2) : n1을 소수정 이하 n2번째에서 잘라냄
SELECT TRUNC(1234.5678)
FROM dual
;
SELECT TRUNC(1234.5678, 0)
FROM dual
;
SELECT TRUNC(1234.5678, 2)
FROM dual
;
SELECT TRUNC(1234.5678, -1)
FROM dual
;