2023.02.10

select 컬럼명1,...,컬럼명n,집계함수

 from

where

group by 컬럼명1,...,컬럼명n

having 집계함수에 대한 조건

(having count(*)>1)

 

join
catasian join	조건(where)없이 복수개의 테이블이 모든 경우로 조인된 것
	ex) select  worker.이름, depart.부서명 / a.이름, b.부서명 from    worker a, depart b =>별명 붙이기 where  a.부서코드=b.부서코드

<코드>

select a.employee_id, a.emp_name, b.department_name
from employees a, departments b
where a.department_id = b.department_id;

<결과>

106개가 나온다.

<코드>

employees, jobs를 조인해서 사번,이름,직급명 나오게 조인문 작성

select employee_id,emp_name,job_title
from employees a, jobs b
where a.job_id=b.job_id;

<결과>

107개 나옴

<코드>

select a.employee_id, a.emp_name, b.job_title, c.department_name
from employees a, jobs b, departments c
where a.job_id=b.job_id and 
      a.department_id=c.department_id
      and a.salary between 5000 and 10000;

<결과>

42개 나옴

<코드>

부서명, 부서장 이름

select a.department_name, b.emp_name
from departments a, employees b
where a.department_id = b.department_id
      and a.manager_id = b.employee_id;

<결과>

ERD(Entity Relationship Digram)

<코드>

select b.emp_name, a.start_date, a.end_date, c.job_title, d.department_name
from job_history a,employees b, jobs c, departments d
where a.employee_id = b.employee_id and
      a.job_id = c.job_id and
      a.department_id = d.department_id;

<결과>

셀프조인

<코드>

매니저 이름emp

select a.employee_id, a.emp_name, b.emp_name manager_name
from employees a, employees b
where a.manager_id=b.employee_id;

<결과>

106개 나옴

<코드>

부서코드, 부서명, 상위부서명, 부서장 이름을 출력

select a.department_id, a.department_name, b.department_name 상위부서, c.emp_name 
from departments a, departments b , employees c
where a.department_id = b.parent_id
  and b.manager_id = c.employee_id;

<결과>

<코드>

부서이름, 부서명, 직원수, 월급합계

select b.department_name, a.emp_name,count(c.department_id), sum(c.salary)
from employees a, departments b, employees c 
where a.department_id = c.department_id
and a.employee_id = b.manager_id
group by b.department_name, a.emp_name;

<결과>

불금이야 불금이라굿