--총사원수, 최대급여, 최소급여, 평균급여, 총 급여를 출력하시오.
select count(employee_id) 총사원수, max(salary) 최대급여, min(salary) 최소급여,
avg(salary) 평균급여, sum(salary) 총급여
from employees
--부서배치받은 사원수, 수당받는 사원수, 관리자(manager_id)가 있는 사원수
select count(DEPARTMENT_ID) "부서배치받은 사원수" ,
count(COMMISSION_PCT) "수당받는 사원수",
count(manager_id) " 관리자 사원수"
from employees
--부서별 사원수, 총급여, 평균급여
select department_id, count(*), sum(salary), avg(salary)
from employees
group by department_id
ORDER BY절
-> 모든 컬럼, 컬럼 별칭, PROJECTION 대상, 컬럼 인덱스 사용가능
SELECT 처리 순서/
-> FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
EX)
SELECT FIRST_name 이름
from employees
where 이름 like ‘%e%’
// 처리 순서로 인해, where에서는 select에서 지정한 별칭이 적용되어있지 않기 때문이다.
SELECT FIRST_name 이름
from employees
where first_name like ‘%e%’
order by 이름
// 처리순서상 order by가 가장 마지막이므로, 별칭을 지칭해도 에러가 발생하지 않는다.
그룹 함수
group by절
select절 처리 순서로 인해, 발생하는 group by의 논리
-> SQL처리 순서상 group by가 먼저 이루어지고 select가 이루어지므로,
원하는 데이터를 출력하기 위해서는 group by에서 데이터를 처리한 후,
select 절에서 그 컬럼을 불러내야만 출력이 가능하다.
-> 이것은 처리 순서로 인한 논리이므로, 반드시 이해를 요한다.
--부서별 부서번호, 최대급여를 출력하시오. 최대급여가 높은 부서부터 출력
select department_id "부서번호", max(salary) "최대급여"
from employees
group by department_id
order by 2 desc
--급여가 10000이상인 사원들의 부서별 부서번호, 최대급여 출력하시오.
select department_id "부서번호", max(salary) "최대급여"
from employees
where salary >= 10000
group by department_id
order by 2 desc
having 절
group by절의 하위 구문으로, group by 내부에서 조건문을 처리하는 역할을 담당하고 있다.
1~100까지의 데이터가 있고, 데이터 내부에서 10이하의 데이터를 제외시키고 싶다면.
group by절 밑에 having을 이용하여, 10이하의 데이터를 제외시켜주면 된다.
--평균급여가 7000이상인 부서의 부서별 부서번호, 최대급여 출력하시오.
select department_id "부서번호", max(salary) "최대급여"
from employees
group by department_id
having avg(salary) >= 7000
order by 2 desc
// group by가 이루어진 데이터에 대한 조건.. 즉 group by에 조건을 만드는 구문은 having이다.
having은 group by가 나와야만 사용할 수 있다.
--부서별, 부서에 속한 사원의 업무(job_id)별 부서번호, 업무번호, 사원수를 출력하시오.
select department_id "부서번호", job_id "업무별", count(*) "사원수"
from employees
group by department_id, job_id
order by 1
--년도별 입사자수를 출력하시오. 최근년도부터 출력한다.
select to_char(hire_date,'yyyy') "입사 년도", count(*) "총 사원수"
from employees
group by to_char(hire_date,'yyyy')
order by 1 desc
--월별 입사자수를 출력하시오. 단, 입사자가 가장 많은 월부터 출력한다
select to_char(hire_date,'mm') "입사 월", count(*) "총 사원수"
from employees
group by to_char(hire_date,'mm')
order by 2 desc
--부서의 부서번호, 최소급여와 최대급여를 출력하시오. 단, 최소급여와 최대급여가 같으면 출력에서 제외한다.
select DEPARTMENT_ID "부서번호", min(salary) "최소급여", max(salary) "최대급여"
from employees
group by DEPARTMENT_ID
having max(salary) <> min(salary)
order by 1
--사원수가 5명 이상인 부서에 속한 사원중 급여가 4000보다 많고 수당을 받지 않는 사원들의 부서별 인원수를 출력하시오.
select DEPARTMENT_ID "부서번호", count(*)
from employees
where salary > 4000 and COMMISSION_PCT is null
group by DEPARTMENT_ID
having count(*) >= 5
논리모델링 / 물리모델링
엔터티타입 테이블
속성 컬럼
엔터티 로우
속성의 종류
주 식별자 / 보조 식별자
주 식별자 : 엔터티를 구분할 수 있는 유일한 식별자
단일 식별자 / 복합 식별자
일반 속성
참조 문서 : http://wiki.gurubee.net/pages/viewpage.action?pageId=1966716
(ERD)엔터티 타입간의 관계
부모 엔터티 타입 / 자식 엔터티 타입
주식별자 관계 / 비식별자 관계
주식별자 관계 : 부모의 PK를 자식의 PK로 참조 -실선-
비식별자 관계: 부모의 PK를 자식의 일반컬럼으로 참조 -점선
참여수[카디넬리티]
1 : 1 , 1 : $ , $ : $
선택 참여 / 필수참여
자기 참조 관계 (self join)
동그라미
동그라미가 있다면, 선택참여, 필수참여(하나일수도 있고, 없을수도 있다)
동그라미가 없다면 반드시 한가지가 있어야한다 (필수)
선의 끝부분
점선과 실선
부모의 피 키를, 자식의 일반컬럼 참조할때 (일반 컬럼, 점선 처리) [주식별자
부모의 피 키를 , 자식의 피키로 참조할때 [비식별자]
Join (정규화)
데이터의 중복을 피하기 위해, 테이블을 나누어 놓게 되는데, 이때 정규화작업을 통해 여러 테이블의 데이터를 하나로 묶어서 원하는 결과값을 얻어낸다.
--사원의 사번, 이름, 업무번호(JOB_ID), 업무명(JOB_TITLE)을 출력하시오
equi join (p209)
select employee_id, first_name, e.job_id, job_title
from employees e, jobs j
where e.job_id = j.job_id
// employees, jobs 두 테이블의 값이 서로 일치하는 값만 보여줄수 있도록 조건을 준다
equi join의 호환성과 가독성 향상을 위해 ansi join을 사용한다 (같은 역할)
ansi join으로 변환(p225)
select employee_id, first_name, e.job_id, job_title
from employees e inner join jobs j
ON e.job_id = j.job_id
// inner는 생략이 가능하다
--job_title 업무명에 Manager로 끝나는 사원들의 사번, 이름, 업무번호, 업무명을 출력하시오.
equi join
select employee_id "사번", first_name "이름", e.job_id "업무번호", job_title "업무명"
from employees e, jobs j
where e.job_id = j.job_id and job_title like '%Manager'
ANSI join
select employee_id "사번", first_name "이름", e.job_id "업무번호", job_title "업무명"
from employees e inner join jobs j
ON e.job_id = j.job_id and job_title like '%Manager'
--각 부서가 속한 도시의 부서ID와 부서명, 도시명을 출력하시오.
select d.department_id, d.department_name, l.city
from departments d join locations l
on d.location_id = l.location_id
--사원의 사번, 이름, 부서ID, 부서명, 업무명
select e.employee_id, first_name, d.department_id, department_name, j.job_title
from employees e
join departments d on e.department_id = d.department_id
join jobs j on e.job_id = j.job_id
--도시별 부서수를 출력하시오.
select city, count(department_id)
from departments d
join locations l on d.location_id = l.location_id
group by city
-- self join
select first_name, employee_id, manager_id
from employees
--사원의 사번, 이름, 관리자 번호, 관리자 이름을 출력하시오.
select a.employee_id, a.first_name, a.manager_id, b.first_name
from employees a join employees b
on a.manager_id = b.employee_id
책정리
p184,192,199, 204,209,225,215
예제)
--총사원수, 최대급여, 최소급여, 평균급여, 총 급여를 출력하시오.
select count(employee_id) 총사원수, max(salary) 최대급여, min(salary) 최소급여,
avg(salary) 평균급여, sum(salary) 최대급여총급여
from employees
--부서배치받은 사원수, 수당받는 사원수, 관리자(manager_id)가 있는 사원수
select count(DEPARTMENT_ID) "부서배치받은 사원수" ,
count(COMMISSION_PCT) "수당받는 사원수",
count(manager_id) " 관리자 사원수"
from employees
--부서별 사원수, 총급여, 평균급여
select department_id, count(*), sum(salary), avg(salary)
from employees
group by department_id
--부서별 부서번호, 최대급여를 출력하시오. 최대급여가 높은 부서부터 출력
select department_id "부서번호", max(salary) "최대급여"
from employees
group by department_id
order by 2 desc
--급여가 10000이상인 사원들의 부서별 부서번호, 최대급여 출력하시오.
select department_id "부서번호", max(salary) "최대급여"
from employees
where salary >= 10000
group by department_id
order by 2 desc
--평균급여가 7000이상인 부서의 부서별 부서번호, 최대급여 출력하시오.
select department_id "부서번호", max(salary) "최대급여"
from employees
group by department_id
having avg(salary) >= 7000
order by 2 desc
// group by가 이루어진 데이터에 대한 조건.. 즉 group by에 조건을 만드는 구문은 having이다.
having은 group by가 나와야만 사용할 수 있다.
--부서별, 부서에 속한 사원의 업무(job_id)별 부서번호, 업무번호, 사원수를 출력하시오.
select department_id "부서번호", job_id "업무별", count(*) "사원수"
from employees
group by department_id, job_id
order by 1
--년도별 입사자수를 출력하시오. 최근년도부터 출력한다.
select to_char(hire_date,'yyyy') "입사 년도", count(*) "총 사원수"
from employees
group by to_char(hire_date,'yyyy')
order by 1 desc
--월별 입사자수를 출력하시오. 단, 입사자가 가장 많은 월부터 출력한다
select to_char(hire_date,'mm') "입사 월", count(*) "총 사원수"
from employees
group by to_char(hire_date,'mm')
order by 2 desc
--부서의 부서번호, 최소급여와 최대급여를 출력하시오. 단, 최소급여와 최대급여가 같으면 출력에서 제외한다.
select DEPARTMENT_ID "부서번호", min(salary) "최소급여", max(salary) "최대급여"
from employees
group by DEPARTMENT_ID
having max(salary) <> min(salary)
order by 1
--사원수가 5명 이상인 부서에 속한 사원중 급여가 4000보다 많고 수당을 받지 않는 사원들의 부서별 인원수를 출력하시오.
select DEPARTMENT_ID "부서번호", count(*)
from employees
where salary > 4000 and COMMISSION_PCT is null
group by DEPARTMENT_ID
having count(*) >= 5
--총사원수, 최대급여, 최소급여, 평균급여, 총 급여를 출력하시오.
select count(employee_id) 총사원수, max(salary) 최대급여, min(salary) 최소급여,
avg(salary) 평균급여, sum(salary) 최대급여총급여
from employees
--부서배치받은 사원수, 수당받는 사원수, 관리자(manager_id)가 있는 사원수
select count(DEPARTMENT_ID) "부서배치받은 사원수" ,
count(COMMISSION_PCT) "수당받는 사원수",
count(manager_id) " 관리자 사원수"
from employees
--부서별 사원수, 총급여, 평균급여
select department_id, count(*), sum(salary), avg(salary)
from employees
group by department_id
--부서별 부서번호, 최대급여를 출력하시오. 최대급여가 높은 부서부터 출력
select department_id "부서번호", max(salary) "최대급여"
from employees
group by department_id
order by 2 desc
--급여가 10000이상인 사원들의 부서별 부서번호, 최대급여 출력하시오.
select department_id "부서번호", max(salary) "최대급여"
from employees
where salary >= 10000
group by department_id
order by 2 desc
--평균급여가 7000이상인 부서의 부서별 부서번호, 최대급여 출력하시오.
select department_id "부서번호", max(salary) "최대급여"
from employees
group by department_id
having avg(salary) >= 7000
order by 2 desc
// group by가 이루어진 데이터에 대한 조건.. 즉 group by에 조건을 만드는 구문은 having이다.
having은 group by가 나와야만 사용할 수 있다.
--부서별, 부서에 속한 사원의 업무(job_id)별 부서번호, 업무번호, 사원수를 출력하시오.
select department_id "부서번호", job_id "업무별", count(*) "사원수"
from employees
group by department_id, job_id
order by 1
--년도별 입사자수를 출력하시오. 최근년도부터 출력한다.
select to_char(hire_date,'yyyy') "입사 년도", count(*) "총 사원수"
from employees
group by to_char(hire_date,'yyyy')
order by 1 desc
--월별 입사자수를 출력하시오. 단, 입사자가 가장 많은 월부터 출력한다
select to_char(hire_date,'mm') "입사 월", count(*) "총 사원수"
from employees
group by to_char(hire_date,'mm')
order by 2 desc
--부서의 부서번호, 최소급여와 최대급여를 출력하시오. 단, 최소급여와 최대급여가 같으면 출력에서 제외한다.
select DEPARTMENT_ID "부서번호", min(salary) "최소급여", max(salary) "최대급여"
from employees
group by DEPARTMENT_ID
having max(salary) <> min(salary)
order by 1
--사원수가 5명 이상인 부서에 속한 사원중 급여가 4000보다 많고 수당을 받지 않는 사원들의 부서별 인원수를 출력하시오.
select DEPARTMENT_ID "부서번호", count(*)
from employees
where salary > 4000 and COMMISSION_PCT is null
group by DEPARTMENT_ID
having count(*) >= 5
========================== 오후======================
JOIN
--사원의 사번, 이름, 업무번호(JOB_ID), 업무명(JOB_TITLE)을 출력하시오
select employee_id, first_name, e.job_id, job_title
from employees e, jobs j
// 사원 107 * 업무 19
select employee_id, first_name, e.job_id, job_title
from employees e, jobs j
where e.job_id = j.job_id
// employees, jobs 두 테이블의 값이 서로 일치하는 값만 보여줄수 있도록 조건을 준다
equi join의 호환성과 가독성 향상을 위해 ansi join을 사용한다 (같은 역할)
==> ANSI JOIN
select employee_id, first_name, e.job_id, job_title
from employees e inner join jobs j
ON e.job_id = j.job_id
// inner는 생략이 가능하다
--job_title 업무명에 Manager로 끝나는 사원들의 사번, 이름, 업무번호, 업무명을 출력하시오.
equi join
select employee_id "사번", first_name "이름", e.job_id "업무번호", job_title "업무명"
from employees e, jobs j
where e.job_id = j.job_id and job_title like '%Manager'
ANSI join
select employee_id "사번", first_name "이름", e.job_id "업무번호", job_title "업무명"
from employees e inner join jobs j
ON e.job_id = j.job_id and job_title like '%Manager'
--각 부서가 속한 도시의 부서ID와 부서명, 도시명을 출력하시오.
select d.department_id, d.department_name, l.city
from departments d join locations l
on d.location_id = l.location_id
--사원의 사번, 이름, 부서ID, 부서명, 업무명
equi join
select e.employee_id, first_name, d.department_id, department_name, j.job_title
from employees e, departments d, jobs j
where e.employee_id = d.department_id
and e.job_id = j.job_id
select e.employee_id, first_name, d.department_id, department_name, j.job_title
from employees e
join departments d on e.department_id = d.department_id
join jobs j on e.job_id = j.job_id
--도시별 부서수를 출력하시오.
select city, count(department_id)
from departments d
join locations l on d.location_id = l.location_id
group by city
-- self join
select first_name, employee_id, manager_id
from employees
--사원의 사번, 이름, 관리자 번호, 관리자 이름을 출력하시오.
select a.employee_id, a.first_name, a.manager_id, b.first_name
from employees a join employees b
on a.manager_id = b.employee_id
퀴즈
1.DEPARTMENT_NAME이 'IT'인 부서의 사원이름과 입사일을 출력하시오.
select first_name, hire_date
from employees e join departments d
on e.department_id = d.department_id
where DEPARTMENT_NAME = ‘IT’
2.부서별 부서명, 사원수, 평균급여를 출력하시오.
select d.department_name "부서명", count(*) "사원수", avg(e.salary) "평균급여"
from employees e join departments d
on e.department_id = d.department_id
group by department_name
3.last_name이 Smith인 사원의 입사일과 소속 부서명을 출력하시오.
select e.last_name, e.hire_date, d.department_name
from employees e join departments d
on e.department_id = d.department_id
where last_NAME = ‘Smith’
4.업무명(job_title)이 President인 사원의 사원명과 업무(job_id)을 출력하시오.
select e.first_name, j.job_id
from employees e join jobs j
on e.job_id = j.job_id
where job_title = 'President'
5.업무명(job_title)이 President인 사원을 제외한 사원명과 업무(job_id)를 출력하시오.
select e.first_name, j.job_id
from employees e join jobs j
on e.job_id = j.job_id
where job_title <> 'President'
6.job_title이 'Programmer'인 매니저로 부터 관리받는 사원중 'IT'부서의 사번, 이름을 출력하시오.
jobs, employees, departments
select e.employee_id, e.first_name
from employees e join employees em on e.manager_id = em.employee_id
join departments d on e.department_id = d.department_id
join jobs j on em.job_id = j.job_id
where j.job_title = 'Programmer' and d.department_name= 'IT'