DB/oracle

[sql] 집계함수, 숫자함수

힘히힘 2021. 1. 21. 15:36
반응형

/*not commission*/

SELECT first_name, LAST_name, COMMISSION_PCT FROM EMPLOYEES
WHERE commission_pct IS NULL;

 

SELECT first_name, LAST_name, COMMISSION_PCT FROM EMPLOYEES
WHERE commission_pct IS NOT NULL;

 

/*asc sort*/

SELECT employee_id, last_name FROM EMPLOYEES 
ORDER BY EMPLOYEE_ID ASC;
--asc? 순서대로 출력해라 --

 


/*desc sort*/ : 역순서로 출력해라.

SELECT employee_id, last_name FROM EMPLOYEES 
ORDER BY EMPLOYEE_ID desc;

 

/*sum()*/

SELECT sum(salary) FROM EMPLOYEES;

 

/*count()*/

SELECT count(*) FROM EMPLOYEES;

SELECT count(ALL employee_id), count(DISTINCT employee_id)
FROM employees;

SELECT count(ALL FIRST_name), count(DISTINCT FIRST_name) FROM employees;

 

/*avg()*/

SELECT avg(salary) FROM employees WHERE department_id=50;

 

/*max()*/

SELECT min(salary) FROM EMPLOYEES;
SELECT min(hire_date) FROM employees;

SELECT employee_id, FIRST_name FROM EMPLOYEES WHERE salary=max(salary);

 

/*min()*/

SELECT abs(-23) FROM dual;


SELECT sign(23) FROM dual;

SELECT sign(23), sign(-23), sign(0) FROM dual;

 

 

/*round()*/ : 소수점 자리 보여주기

SELECT round(0.123), round(0.543) FROM dual;

SELECT round(0.12345678,6), round(2.3423455,4) FROM dual;

 

/*trunc(n1, n2)*/ : n1을 n2 번째 자리 까지 나타내어라 (0이 정수부분, 1 입력시 소수 1자리)

SELECT trunc(1234.1234567) zero FROM dual;
SELECT trunc(1234.1234567,0) zero FROM dual;

SELECT trunc(1234.1234567,2) zero FROM dual;
SELECT trunc(1234.1234,-1) FROM dual;

 

/*ceil()*/ : 올림 하기

SELECT CEIL(32.8) CEIL FROM dual;
SELECT CEIL(32.3) CEIL FROM dual;

 

/*floor()*/ : 소수점 버리기

SELECT floor(32.8) floor FROM dual;
SELECT floor(32.3) floor FROM dual;

 

/*power() : 제곱을 만드는 함수*/

SELECT power(4, 2) power1 FROM dual;

 

/*sqrt()*: 제곱근 구하는 함수*/

SELECT sqrt(36) sqrt1 FROM dual;

 


/*mod(): 중간값*/

SELECT mod(7,4) mod1 FROM dual;

'DB > oracle' 카테고리의 다른 글

[oracle] scott 계정 없음 생성하기  (0) 2021.01.25
[오라클] sqlplus 접속계정 비번 잊어버림... 변경하기!  (0) 2021.01.23
[sql] 집계함수, 숫자함수  (0) 2021.01.21
[sql] select 구문  (0) 2021.01.21
sql basic (4) -select  (0) 2021.01.15
SQL basic (3) -data  (0) 2021.01.13