##다중행 함수
=> 그룹함수, 통계함수
=> 여러 행의 값 => 처리 => 하나의 값을 반환
=> sum, avg, count, max, min...
=>** null 값은 통계에서 제외한다.
## count 함수
- * : row의 수를 카운트 (NULL값도 참조)
- 컬럼명 : count(commission_pct) (NULL값 무시)
select count(*) as 총사원수, count(commission_pct) as "받는 사원수",
count(*) - count(commission_pct) as " 받지 않는 사원 수 "
from emp;
최고급여, 최저급여
select max(salary), min(salary)
from emp ;
월급을 15000 이상 받는 사원의 수 ?
select count(*) as "고액 연봉자의 수"
from emp
where salary >= 15000;
그룹
=>group by 컬럼명, 컬럼명...
=>select 절에 그룹함수 이외의 컬럼 또는 수식은 group by 절에 기술할 것 !
부서별 최고급여, 최저급여
select DEPARTMENT_ID, max(salary), min(salary)
from emp
group by DEPARTMENT_ID
order by DEPARTMENT_ID ;
ex)안되는 예
select DEPARTMENT_ID, max(salary), min(salary)
from emp
order by DEPARTMENT_ID ;
년도별 입사인원수
select to_char(hire_date, 'yyyy') as "입사년도", count(*) as "입사 인원수"
from emp
group by to_char(hire_date, 'yyyy')
order by 1 ;
부서별, 직군
select DEPARTMENT_ID, job_id, max(salary), min(salary)
from emp
group by DEPARTMENT_ID, job_id
order by 1,2 ;
---------------------------------------------------------------
부서별 평균 급여
select DEPARTMENT_ID, round(avg(salary),0)
from emp
group by DEPARTMENT_ID
order by DEPARTMENT_ID ;
## having 절
=> 그룹을 제한
=> 원하는 그룹만 표시
=> where절과 유사
=> having 조건식 and 조건식 or 조건식
=> group by 절이 있는 경우만 사용가능 !
=> select 절의 컬럼별칭을 사용할 수 없다!! => 실행순서 때문
부서별 평균 급여
단, 평균급여 8000 이상인 부서만 출력
select DEPARTMENT_ID, round(avg(salary),0) as 부서평균급여
from emp
group by DEPARTMENT_ID
having round(avg(salary),0) >= 8000
order by DEPARTMENT_ID ;
구매 ( 구매번호, 구매자, 상품번호, 상품갯수, 구매금액합계, 구매날짜)
단, 2021년 자료만 구매에 있다고 가정
1. 구매금액의 합계가 500000 이상인 구매자 명단
select 구매자, sum(구매금액합계) as 총 구매금액 from 구매
group by 구매자
having sum(구매금액합계) >= 500000
order by 2 desc ;
2.지난달 100개 이상 팔린 상품번호
select 상품번호, sum(상품갯수) as 총팔린갯수
from 구매
where to_char(구매날짜, 'mm') = to_char(sysdate, 'mm') -1
group by 상품번호
having sum(상품갯수) >= 100
order by 2 desc ;
고객( 고객번호, 고객명, 생일, 성별, .....)
1.고객 연령별 인원수
힌트) 나이를 우선 구한다 -> 연령대를 구한다 -> 연령별 인원수를 구한다
나이 : to_char(sysdate, 'yyyy') - to_char(생일,'yyyy') + 1 => 27
연령대 : 27 -> 20 : trunc( to_char(sysdate, 'yyyy') - to_char(생일,'yyyy') + 1, -1)
select trunc( to_char(sysdate, 'yyyy') - to_char(생일,'yyyy') + 1, -1) as 연령대, count(*) as 인원수
from 고객
gruop by trunc( to_char(sysdate, 'yyyy') - to_char(생일,'yyyy') + 1, -1)
order by 1 ;
연령대 인원수, 비율
select trunc( to_char(sysdate, 'yyyy') - to_char(생일,'yyyy') + 1, -1) as 연령대,
count(*) as 인원수,
trunc( count(*) / (select count(*) from 고객 ), 1) as 비율
from 고객
gruop by trunc( to_char(sysdate, 'yyyy') - to_char(생일,'yyyy') + 1, -1)
order by 1 ;
create table emp1
as
select * from ad30.emp1;
이름, 나이, 연령대
select last_name, hire_date, to_char(sysdate, 'yyyy') - to_char(hire_date, 'yyyy') + 1 as 나이,
trunc ( to_char(sysdate, 'yyyy') - to_char(hire_date, 'yyyy') + 1, -1) as 연령대
from emp1 ;
## 조인
create table dept
as
select * from hr.dept ;
create table locations
as
select * from hr.locations ;
create table job_grades
as
select * from hr.job_grades ;
------------------------
##조인
-테이블을 옆으로 합치는 것
-공통된 컬럼을 조인
-오라클 전용 구문, ansi 표준 구문
##조인의 종류
1. cross join
=> 조인조건 없음
=> 모든 경우의 수 조회 = 조인테이블1 행수 * 조인테이블2 행수
2. equi-join
=> 조인조건 있음
=> 조인테이블1.컬럼명 = 조인테이블명2.컬럼명
=> 조인조건에 맞는 레코드만 조회 : 조인테이블에 모두 존재해야만 조회 대상
3. outer-join
=> 조인조건 있음
=> 조인테이블1.컬럼명 = 조인테이블명2.컬럼명(+)
=> 조인조건에서 누락된 레코드도 조회 대상
=> 데이터가 존재하지 않는 조인측에 (+) 기호를 표기 >>기준이 되는거 반대에
=> outer-join시에는 null 데이터로 간주
4. self-join
=> 같은 테이블 참조
=> 테이블에 대한 알리아스를 지정해서 어떤 목적의 테이블인지 명시적 지정
5. non equi-join
=> 조인조건이 '=' 연산자가 아닌 조인
## 오라클 구문
## 조인 조건 where 절 기술
## equi join
사번, 이름 부서명
select *
from emp , dept
where emp.department_id = dept.department_id ;
select employee_id, last_name, department_name
from emp , dept
where emp.department_id = dept.department_id ;
--테이블의 별칭 사용시 as 쓰면 안된다 !
select e.employee_id, e.last_name, d.department_name
from emp e , dept d
where e.department_id = d.department_id ;
## 표준 구문
## 조인조건을 from 절 기술
## equi join
사번, 이름 부서명
-두 테이블에 동일한 컬럼이 있는 경우
select e.employee_id, e.last_name, d.department_name
from emp e join dept d using(department_id) ;
-두 테이블에 동일한 특성의 컬럼이 있지만 컬럼의 이름이 다른 경우
select e.employee_id, e.last_name, d.department_name
from emp e join dept d on (e.department_id = d.department_id) ;
## 오라클 구문
## outer join : left outer join, right outer join
## full outer join 지원하지 않음
##left outer join
select e.employee_id, e.last_name, d.department_name
from emp e , dept d
where e.department_id = d.department_id(+) ;
##right outer join
select e.employee_id, e.last_name, d.department_name
from emp e , dept d
where e.department_id(+) = d.department_id ;
## 표준 구문
## outer join : left outer join, right outer join, full outer join
## outer 생략가능
##left outer join
select e.employee_id, e.last_name, d.department_name
from emp e left outer join dept d using( department_id) ;
##right outer join
select e.employee_id, e.last_name, d.department_name
from emp e right outer join dept d using( department_id) ;
##full outer join
select e.employee_id, e.last_name, d.department_name
from emp e full outer join dept d using ( department_id) ;
##여러테이블 조인
a,b,c 테이블이 존재
select *
from a,b,c
where a.aa = b.bb and b.bb = c.cc ;
select *
from ( a join b using(aa) ) join c on (b.bb = c.cc)
where a.aa = b.bb and b.bb = c.cc ;
## self join
사번, 사원이름, 상관이름
select e.employee_id as 사번, e.last_name as 사원이름, m.last_name as 상관이름
from emp e, emp m
where e.manager_id = m.employee_id
select e.employee_id as "사번", e.last_name as "사원이름",
m.last_name as "상관이름"
from emp e join emp m on(e.manager_id=m.employee_id);
## non equi join
이름, 월급, 월급레벨
select e.last_name, e.salary, j.grade_level
from emp e, job_grades j
where e. salary between j.lowest_sal and j.highest_sal ;
'WORK > DB_Oracle' 카테고리의 다른 글
| ##SQL 6일차 0504(테이블 생성, 제약조건) (0) | 2021.05.04 |
|---|---|
| ##SQL 5일차 0503(개념적,논리적,물리적설계,데이터타입) (0) | 2021.05.03 |
| ##SQL 4일차 0430(서브쿼리,DML,TCL) (0) | 2021.05.01 |
| ##SQL 2일차 0428 (0) | 2021.04.28 |
| ##SQL 1일차 0427 (0) | 2021.04.28 |