240110 DB - 그룹함수, JOIN 1

단일 행 함수 : 행 당 함수가 적용되어 반환

	lower(last_name)
	substr(phone_number, 1, 3)
	nvl(commission_pct, 0)

 

 

다중 행 함수 : 행 그룹을 조작해서 결과를 한 개 반환함

 

그룹함수

행 그룹을 조작해서 하나의 결과 값을 반환함

특징 : null값은 제외하고 작업함

• avg
• max
• min
• sum

그림 1.

sum과 avg는 숫자 데이터 타입인 컬럼만 사용이 가능하다.

그림 2.

단, max, min은 모든 데이터 타입의 컬럼에 사용이 가능하다.

 

• count
  count(*) : * 모든 컬럼, 행 하나가 1이기 때문에, 테이블의 전체 행의 개수 반환, null이 있어도 상관 없음.
                  즉, null과 중복값 포함 
  count(expr) : 특정 컬럼 지정했을 때, null인 값은 무시하고 개수를 반환
                        즉, null 제외, 중복값 포함
  count(distinct expr) : 컬럼에 중복된 값은 하나의 값으로 하여 개수를 반환, null이 있으면 무시하고 반환함.
                                    즉, null과 중복값 제외

그림 3.

 

퀴즈) employees 테이블로부터 전 직원의 커미션 평균을 출력

그림4.

cf) select avg(commission_pct) from employees; → 커미션을 받는 사원들의 평균

group by, having

select 컬럼명, 그룹함수
from 테이블명
where 조건
group by 그룹 표현식
having 그룹 조건
order by 컬럼명

 

그림 5.

 

그룹함수에 포함된지 않는 컬럼은 반드시 group by절에 포함되어야함

select 절의 컬럼 리스트들 중 그룹 함수에 포함된 컬럼과 그룹함수에 포함되지 않는 컬럼이 같이 출력되려면

그룹함수에 포함되지 않는 컬럼은 반드시 group by절에 포함되어 있어야함

그림 6.

그림 7.

 

where절 : 행을 제한하는 조건문

having절 : 행 그룹(그룹 함수가 포함된 조건문) 을 제한하는 조건문

그림 9.

※ 오라클의 경우 group by, having 순서가 바뀌어도 상관없음!(단 group by, having 순서로하는 것을 권장함)

 

연습문제

1

2

 

JOIN

※Oracle Software

1. sqlplus

TUI 환경의 프로그램

SQL 구문(DB 관리 명령어, Object 관련 명령어) 입력/실행/결과 를 볼 수 있음.

DBA, 개발자가 사용

 

2. sql developer

GUI 환경의 프로그램

SQL 구문(Object 관련 명령어) 입력/실행/결과 를 볼 수 있음.

개발자가 사용

 

3. em

DBA가 사용

 

sql developer

→db는 켜져있어야함 (lsnr, db)

소프트웨어 다운받아야함. 단 실습에서는 깔려져있음

그림 10.

 

SQL Developer 접속해서 사용하는 방법(그림 11참조)

 

1. 좌측 상단 초록색 + (접속을 도와주는 아이콘)

2.

접속 이름 : My conn_hr

사용자 이름 : hr

비밀번호 : -

SID : DB이름이 들어가야함 orcl

3. 접속

그림 11.

※호스트 이름에 serverPC IP주소를 입력해야함. 지금은 호스트이름을 기본값으로 해줘도 상관없다.

 

그림 12.

도구 - 환경설정에서 글자 크기 및 행 번호 설정

 

조인(join)

여러 테이블의 컬럼을 함께 출력하는 구문

조인을 하기 위해서는 두 테이블에서 공통된 컬럼이 존재해야하고, 그 공통된 컬럼을 찾아서 on절에 작성

select 컬럼명1, 컬럼명2, 컬럼3
from 테이블A join 테이블B
on 테이블A.컬럼명 = 테이블B.컬럼명
[where 조건문]
[group by 컬럼명]
[having 조건문]
[order by 컬럼명];

-- ※양 쪽 테이블에서 공통된 컬럼을 찾아야함. 즉 양 테이블에서 공통된 컬럼이 있어야 join이 가능하다.
-- 컬럼명이 같은 것을 찾는 것이 아니라 데이터가 공통된 데이터이여야 한다.

 

 

ex1) employees 테이블과 departments 테이블로부터 직원 정보(employee_id, last_name, salary, job_id)와

직원이 소속된 부서 정보(department_id, department_name)를 함께 출력

 

select employee_id, last_name, salary, job_id, department_id, department_name
from employees join departments 
on employees.department_id = departments.department_id;

-- 열의 정의가 애매하다 라는 오류 발생.
-- 이유는 department_id 컬럼은 employees테이블과 department 테이블에 공통으로 가지고 있기 때문에,
-- DBMS가 어느 컬럼에서 가져와야하는지 판단할 수 없기 때문이다.
-- 양쪽 테이블에 공통으로 존재하는 컬럼은 반드시 접두어로 테이블명을 가져다주어야함.

select e.employee_id, e.last_name, e.salary, e.job_id, e.department_id, d.department_name
from employees e join departments d
on e.department_id = d.department_id;

-- ==

select e.employee_id, e.last_name, e.salary, e.job_id, d.department_id, d.department_name
from employees e join departments d
on e.department_id = d.department_id;

-- 테이블에 공통으로 존재하지 않는 컬럼은 접두어를 붙혀줄 필요는 없지만, 붙혀주는 것을 습관화하자

select employee_id, last_name, salary, job_id, employees.department_id, department_name
from employees join departments
on e.department_id = d.department_id;

-- ※
select employee_id, last_name, salary, employees.manager_id, employees.department_id, department_name
from employees join departments
on e.department_id = d.department_id;

 

ex2) departments 테이블과 locations 테이블로부터 부서정보(department_id, department_name)와 부서별 위치 정보(location_id,city)를 함께 출력

select department_id, department_name, departments.location_id, city
from departments join locations
on departments.location_id = locations.location_id

-- ==

select department_id, department_name, locations.location_id, city
from departments join locations
on departments.location_id = locations.location_id

 

그림 13.