Database D+3

database D+3

## SQL 3일차 database

 

##SQL 3일차

 

그룹함수란 인수들을 다중행함수로 결과를 도출하는것

 

##다중행 함수

=> 그룹함수, 통계함수

=> 여러행의 값 => 처리 => 하나의 값을 반환

=> sum, avg, count, max, min.....

=> **null 값은 통계에서 제외한다.

 

 

## count 함수

-* : row 의 수를 카운드

- 컬럼명 : count(COMMISSION_PCT)

 

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;

 

from 절을 where 절의 줄의 갯수를 새어준다 -> count(*)

 

 

select count(*) as " 수당을 받는 사원수 "

from emp

where COMMISSION_PCT is not null;

count(*) - count (COMMISSION_PCT) as "받지 않는 사원수"

 

from emp;

 

 

##그룹

=> group by 컬럼명, 컬럴명.....

=> select 절에 그룹함수 이외의 컬럼 또는 수식은 group by절에 기술할것! 아니면 오류가 생김

 

 

부서별 최고급여, 최저급여

 

select DEPARTMENT_ID , max ( salary ), min (salary)

from emp

group by DEPARTMENT_ID

order by DEPARTMENT_ID ;

 

와 비교

 

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 조건식 (where 절과 비슷)

=> group by 절이 있는 경우만 사용가능 !!

=> select 절의 컬럼별칭을 사용할 수 없다.!!

 

-> where 절은 행을 제한 having 은 그룹절을 제한

having 절을 그룹 바이 절 밑에 주로 쓴다

 

 

부서별 평균 급여

단, 평균 급여 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 ;

 

select DEPARTMENT_ID , round(avg(salary), 0) as 부서평균급여

from emp

group by DEPARTMENT_ID

having round(avg(salary), 0) > 8000

order by 부서평균급여 ;

 

그러나

 

select DEPARTMENT_ID , round(avg(salary), 0) as 부서평균급여

from emp

group by DEPARTMENT_ID

having 부서평균급여) > 8000

order by 부서평균급여 ; -> 오류 발생 왜냐면 round(avg(salary), 0)가 부서평균 급여라고 정해지지 않아서

 

 

 

구매 (구매번호, 구매자, 상품번호, 상품갯수, 구매금액합계, 구매 날짜)

단 2021년 자료만 구매에 있다고 가정

1. 구매금액의 합계가 500000 이상은 구매자 명단

 

select 구매자, sum (구매금액합계) as 총 구매금액

from 구매

group by 구매자

having sum(구매금액합계) > 50000

order by 2 desc;

 

2.지난달 100개 이상 팔린 상품번호

 

select 상품번호 , sum(상품갯수) as 총 팔린갯수

from 구매

where to_char( 구매날짜, 'mm') = to_char(sysdate-'mm') -1

/*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대

 

연령대 :27 -> 20 : trunc (to_char(sysdate, 'yyyy')- to_char(생일, 'yyyy') + 1, -1)

-> 20대

 

select trunc (to_char(sysdate, 'yyyy')- to_char(생일, 'yyyy') + 1, -1) as 연령대, count(*) as 인원수

from 고객

group 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 고객

group by trunc (to_char(sysdate, 'yyyy')- to_char(생일, 'yyyy') + 1, -1)

order by 1;

 

 

creat table emp 1

as

select * from ad30.emp1;

 

이름, 나이, 연령대

 

 

 

 

part-2--------------------------------------

 

 

 

JOIN 의 유형 오라클 버전과 표준 구분 둘다 외워야한다.

 

##조인

 

create table dept

as

select * from hr.dept;

 

create table locations

as

select * from hr.locations;

 

create table job_grades

as

select * from hr.grades;

 

 

 

##조인

-테이블을 옆으로 합치는 것

-공통된 컬럼을 조인

- 오라클 전용 구문, ansi 표준 구문

##조인의 종류

1. cross join

2. equi join

3. non- equi join

4. outer join

5. self join

 

 

##오라클 구문

## equi join

#조인 조건을 where 절에 기술

사번, 이름 부서명

 

 

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 );

-----------------------------------------

where e.department_id = d.department_id ; /* 표준 구문에는 필요 x*/

 

------------------------------------------

 

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 //join은 딱 맞는것만 조인하는 느낌이면 outer는 다 나온다

 

outer join : left outer join, right outer join

fullouter join는 지원 x

 

##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

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) ;

-----------------------------------------

select e.EMPLOYEE_ID, e.last_name, d.department_name

from emp e left join dept d using (department_id) ; /*outer*/ 생략

 

 

##right outer join

select e.EMPLOYEE_ID, e.last_name, d.department_name

from emp e rigth 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)

 

##self join

 

사번, 사원이름, 상관이름

 

select e.employee_id as 사번, e.last_name as 사원 이름, as 상관이름, m.last_name as 상관이름

from emp e, emp m

where e. manger_id = m.employee_id ;

 

 

## non equi join

 

이름,월급, 월급레벨을 검색하고 싶다

월급의 레벨은 JOB_GRADES에 있다

 

select e.last_name, e.salary, j.GRADE_LEVEL

from emp e, JOB_GRADES j

where e.salary between j.LOWEST_SAL and HIGHEST_SAL ;