※ select (해석순서 1 → 2 → 3 → 4 → 5)
select [distinct] [컬럼1,컬럼2,.....][as 별명][ || 연산자][*] --- 6
from 테이블명 --- 1
[where 조건절] --- 2
[group by컬럼명] --- 3
[having 조건절] --- 4
[order by 컬럼명 asc|desc ] --- 5
group by : 그룹함수(max,min,sum,avg,count..)와 같이 사용
having : 묶어놓은 그룹의 조건절
------------------------------------------------------------------
ex1) 사원테이블에서 급여의 평균을 구하시오
조건)소수이하는 절삭, 세자리마다 콤마(,)
사원급여평균
------------
6,461
select to_char(trunc(avg(salary),0), '99,999') as 사원급여평균 from employees;
ex2)부서별 급여평균을 구하시오
조건1) 소수이하는 반올림
조건2) 세자리마다콤마, 화페단위 \를 표시
조건3) 부서코드 평균급여
---------------------------
10 \8,600
조건4) 부서별로 오름차순정렬하시오
조건5) 평균급여가 5000이상인 부서만 표시하시오
select department_id as 부서코드,
to_char(round(avg(salary),0),'L99,999,999') as 평균급여
from employees
group by department_id
having avg(salary)>=5000
order by department_id asc;
ex3) 부서별 급여평균을 구해서 사원명(last_name),부서별 급여평균을 출력하시오 - X
select last_name, avg(salary)
from employees
group by department_id;
★ last_name 때문에 error
group by절에 없는것을 select에 조회하면 error
[문제1] 업무ID별 급여의 합계를 구해서 업무ID, 급여합계를 출력하시오
ex4) 비효율적인 having절
10과 20 부서에서 최대급여를 받는사람의 최대급여를 구하여 정렬하시오
department_id max_salary
-----------------------------
10 4400
20 13000
select department_id, max(salary) as max_salary
from employees
group by department_id
having department_id in(10,20)
order by department_id;
전체 부서에 대해 그룹을 잡아서 최대급여를 구한후에 부서가 10 과 20인것을 추려내기 때문인것
select department_id, max(salary) as max_salary
from employees
where department_id in(10,20)
group by department_id
order by department_id;
부서번호가 10과 20인 것만 골라내서 그룹잡기 때문에 속도가 좀 빠르다
조인(join)
EMPLOYEES DEPARTMENTS JOIN
사 부 부 부 사 부 부
원 서 + 서 서 = 원 서 서
이 번 번 이 이 번 이
름 호 호 름 름 호 름
※ 종류
1. Inner join : 같은것 끼리만 연결
2. Outer join : 한쪽을 기준(모두포함)해서 연결
left join : 왼쪽컬럼 모두포함
right join : 오른쪽컬러 모두포함
3. full join : 왼쪽,오른쪽 모두 포함
4. self join : 자기자신 테이블과 연결
5. cross join : 모든 경우의 수로 연결
6. non equijoin : 범위에 속하는지 여부를 확인
7. n개 테이블 조인 : 여러개의 테이블 조인
※ 방법
1. 오라클 구문전용
2. Ansi 표준구문
ex5) inner join : 같은것끼리만 조인
사원테이블과 부서테이블에서 부서가 같을 경우 사원번호, 부서번호, 부서이름을 출력하시오
방법1(오라클전용구문)
select employee_id,
employees.department_id,
department_name
from employees, departments
where employees.department_id = departments.department_id;
방법2(오라클전용구문)
select e.employee_id,
e.department_id,
d.department_name
from employees e, departments d
where e.department_id = d.department_id;
방법3(Ansi표준)
select employee_id, department_id, department_name
from employees
join departments using(department_id);
[문제2] 부서테이블(DEPARTMENTS d)과 위치테이블(LOCATIONS l)을 연결하여 부서가 위치한 도시를 알아내시오
department_id city
----------------------------------
10 Seattle
ex6) outer join(left) : 왼쪽 테이블은 모두 포함하여 조인
사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 107레코드
방법1(오라클전용구문)
select e.last_name, d.department_id, d.department_name
from employees e, departments d
where e.department_id=d.department_id(+);
방법2(Ansi표준)
select last_name, department_id, department_name
from employees
left join departments using(department_id);
ex7) outer join(right) : 오른쪽 테이블은 모두포함하여 조인
사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 122레코드
방법1(오라클전용구문)
select e.last_name, d.department_id, d.department_name
from employees e, departments d
where e.department_id(+)=d.department_id;
방법2(Ansi표준)
select last_name, department_id, department_name
from employees
right join departments using(department_id);
ex8) full join(right) : 왼쪽,오른쪽 테이블을 모두 포함하여 조인
사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 123레코드
--방법1(오라클전용구문) : 없다
--방법2(Ansi표준)
select last_name, department_id, department_name
from employees
full join departments using(department_id);
ex9) inner join : 두개의 컬럼이 일치 하는 경우
부서ID와 매니저ID가 같은 사원을 연결 하시오
(관련테이블 : departments, employees) : 32 레코드
last_name department_id manager_id
------------------------------------------
방법1(오라클전용구문)
select e.last_name, d.department_id, d.manager_id
from employees e, departments d
where e.department_id=d.department_id and e.manager_id=d.manager_id;
방법2(Ansi표준)
select last_name, department_id, manager_id
from employees
inner join departments using(department_id, manager_id);
ex10) 내용은 같은데 컬럼명이 다른 경우에 조인으로 연결하기
departments(location_id) , locations2(loc_id)
테이블 복사
create table locations2 as select * from locations;
select * from locations2;
alter table locations2 rename column location_id to loc_id;
방법1(오라클전용구문)
select d.department_id, l.city
from departments d, locations2 l
where d.location_id=l.loc_id;
방법2(Ansi표준)
select department_id, city
from departments
join locations2 on(location_id=loc_id);
방법3(Ansi표준)
select department_id, city
from departments d
join locations2 l on(d.location_id=l.loc_id);
ex11) self 조인 : 자기자신의 테이블과 조인하는경우 사원과 관리자를 연결하시오
사원번호 사원이름 관리자
----------------------------------
101 Kochhar King
EMPLOYEES EMPLOYEES
employee_id, last_name(사원이름) last_name(관리자)
조건 employee_id = manager_id
select employee_id,manager_id, last_name from employees; → e
select employee_id, last_name from employees; → m
방법1
select e.employee_id as 사원번호,
e.last_name as 사원이름,
m.last_name as 관리자
from employees e, employees m
where m.employee_id=e.manager_id;
방법2
select e.employee_id as 사원번호,
e.last_name as 사원이름,
m.last_name as 관리자
from employees e
join employees m on(m.employee_id=e.manager_id);
ex12) cross join : 모든행에 대해 가능한 모든조합을 생성하는 조인
select * from countries, locations; → 575레코드
select * from countries cross join locations;
ex13) Non Equijoin (넌 이큐조인)
컬럼값이 같은 경우가 아닌 범위에 속하는지 여부를 확인 할 때
on ( 컬럼명 between 컬럼명1 and 컬럼명2)
create table salgrade(
salvel varchar2(2),
lowst number,
highst number);
insert into salgrade values('A', 20000, 29999);
insert into salgrade values('B', 10000, 19999);
insert into salgrade values('C', 0, 9999);
commit;
select * from salgrade;
select last_name, salary, salvel
from employees
join salgrade on(salary between lowst and highst)
order by salary desc;
ex14) n(여러)개의 테이블은 조인
업무ID 같은 사원들의 사원이름, 업무내용, 부서이름을 출력하시오
(EMPLOYEES, JOBS, DEPARTMENTS 테이블을 조인)
<분석>
EMPLOYEES JOBS DEPARTMENTS
------------------------------------------------------------
department_id job_id department_id
job_id
<출력>
last_name job_title department_name
--------------------------------------------------------------
select last_name, job_title, department_name
from employees
join departments using(department_id)
join jobs using(job_id);
[문제3] 위치ID, 부서ID를 연결해서 사원이름,도시,부서이름을 출력하시오
(관련테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS)
조건1 : 사원이름, 도시, 부서이름로 제목을 표시하시오
조건2 : Seattle 또는 Oxford 에서 근무하는 사원
조건3 : 도시순으로 오름차순정렬하시오
사원이름 도 시 부서이름
---------------------------------------------
Hall Oxford Sales
[문제4] 부서ID, 위치ID, 국가ID를 연결해서 다음과 같이 완성하시오
(관련테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS, COUNTRIES)
조건1 : 사원번호,사원이름,부서이름,도시,도시주소,나라명로 제목을 표시하시오
조건2 : 도시주소에 Ch 또는 Sh 또는 Rd가 포함되어 있는 데이터만 표시하시오
조건3 : 나라명, 도시별로 오름차순 정렬하시오
조건4 : 모든 사원을 포함한다
※ select (해석순서 1 → 2 → 3 → 4 → 5)
select [distinct] [컬럼1,컬럼2,.....][as 별명][ || 연산자][*] --- 6
from 테이블명 --- 1
[where 조건절] --- 2
[group by컬럼명] --- 3
[having 조건절] --- 4
[order by 컬럼명 asc|desc ] --- 5
group by : 그룹함수(max,min,sum,avg,count..)와 같이 사용
having : 묶어놓은 그룹의 조건절
------------------------------------------------------------------
ex1) 사원테이블에서 급여의 평균을 구하시오
조건)소수이하는 절삭, 세자리마다 콤마(,)
사원급여평균
------------
6,461
select to_char(trunc(avg(salary),0), '99,999') as 사원급여평균 from employees;
ex2)부서별 급여평균을 구하시오
조건1) 소수이하는 반올림
조건2) 세자리마다콤마, 화페단위 \를 표시
조건3) 부서코드 평균급여
---------------------------
10 \8,600
조건4) 부서별로 오름차순정렬하시오
조건5) 평균급여가 5000이상인 부서만 표시하시오
select department_id as 부서코드,
to_char(round(avg(salary),0),'L99,999,999') as 평균급여
from employees
group by department_id
having avg(salary)>=5000
order by department_id asc;
ex3) 부서별 급여평균을 구해서 사원명(last_name),부서별 급여평균을 출력하시오 - X
select last_name, avg(salary)
from employees
group by department_id;
★ last_name 때문에 error
group by절에 없는것을 select에 조회하면 error
[문제1] 업무ID별 급여의 합계를 구해서 업무ID, 급여합계를 출력하시오
ex4) 비효율적인 having절
10과 20 부서에서 최대급여를 받는사람의 최대급여를 구하여 정렬하시오
department_id max_salary
-----------------------------
10 4400
20 13000
select department_id, max(salary) as max_salary
from employees
group by department_id
having department_id in(10,20)
order by department_id;
전체 부서에 대해 그룹을 잡아서 최대급여를 구한후에 부서가 10 과 20인것을 추려내기 때문인것
select department_id, max(salary) as max_salary
from employees
where department_id in(10,20)
group by department_id
order by department_id;
부서번호가 10과 20인 것만 골라내서 그룹잡기 때문에 속도가 좀 빠르다
조인(join)
EMPLOYEES DEPARTMENTS JOIN
사 부 부 부 사 부 부
원 서 + 서 서 = 원 서 서
이 번 번 이 이 번 이
름 호 호 름 름 호 름
※ 종류
1. Inner join : 같은것 끼리만 연결
2. Outer join : 한쪽을 기준(모두포함)해서 연결
left join : 왼쪽컬럼 모두포함
right join : 오른쪽컬러 모두포함
3. full join : 왼쪽,오른쪽 모두 포함
4. self join : 자기자신 테이블과 연결
5. cross join : 모든 경우의 수로 연결
6. non equijoin : 범위에 속하는지 여부를 확인
7. n개 테이블 조인 : 여러개의 테이블 조인
※ 방법
1. 오라클 구문전용
2. Ansi 표준구문
ex5) inner join : 같은것끼리만 조인
사원테이블과 부서테이블에서 부서가 같을 경우 사원번호, 부서번호, 부서이름을 출력하시오
방법1(오라클전용구문)
select employee_id,
employees.department_id,
department_name
from employees, departments
where employees.department_id = departments.department_id;
방법2(오라클전용구문)
select e.employee_id,
e.department_id,
d.department_name
from employees e, departments d
where e.department_id = d.department_id;
방법3(Ansi표준)
select employee_id, department_id, department_name
from employees
join departments using(department_id);
[문제2] 부서테이블(DEPARTMENTS d)과 위치테이블(LOCATIONS l)을 연결하여 부서가 위치한 도시를 알아내시오
department_id city
----------------------------------
10 Seattle
ex6) outer join(left) : 왼쪽 테이블은 모두 포함하여 조인
사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 107레코드
방법1(오라클전용구문)
select e.last_name, d.department_id, d.department_name
from employees e, departments d
where e.department_id=d.department_id(+);
방법2(Ansi표준)
select last_name, department_id, department_name
from employees
left join departments using(department_id);
ex7) outer join(right) : 오른쪽 테이블은 모두포함하여 조인
사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 122레코드
방법1(오라클전용구문)
select e.last_name, d.department_id, d.department_name
from employees e, departments d
where e.department_id(+)=d.department_id;
방법2(Ansi표준)
select last_name, department_id, department_name
from employees
right join departments using(department_id);
ex8) full join(right) : 왼쪽,오른쪽 테이블을 모두 포함하여 조인
사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 123레코드
--방법1(오라클전용구문) : 없다
--방법2(Ansi표준)
select last_name, department_id, department_name
from employees
full join departments using(department_id);
ex9) inner join : 두개의 컬럼이 일치 하는 경우
부서ID와 매니저ID가 같은 사원을 연결 하시오
(관련테이블 : departments, employees) : 32 레코드
last_name department_id manager_id
------------------------------------------
방법1(오라클전용구문)
select e.last_name, d.department_id, d.manager_id
from employees e, departments d
where e.department_id=d.department_id and e.manager_id=d.manager_id;
방법2(Ansi표준)
select last_name, department_id, manager_id
from employees
inner join departments using(department_id, manager_id);
ex10) 내용은 같은데 컬럼명이 다른 경우에 조인으로 연결하기
departments(location_id) , locations2(loc_id)
테이블 복사
create table locations2 as select * from locations;
select * from locations2;
alter table locations2 rename column location_id to loc_id;
방법1(오라클전용구문)
select d.department_id, l.city
from departments d, locations2 l
where d.location_id=l.loc_id;
방법2(Ansi표준)
select department_id, city
from departments
join locations2 on(location_id=loc_id);
방법3(Ansi표준)
select department_id, city
from departments d
join locations2 l on(d.location_id=l.loc_id);
ex11) self 조인 : 자기자신의 테이블과 조인하는경우 사원과 관리자를 연결하시오
사원번호 사원이름 관리자
----------------------------------
101 Kochhar King
EMPLOYEES EMPLOYEES
employee_id, last_name(사원이름) last_name(관리자)
조건 employee_id = manager_id
select employee_id,manager_id, last_name from employees; → e
select employee_id, last_name from employees; → m
방법1
select e.employee_id as 사원번호,
e.last_name as 사원이름,
m.last_name as 관리자
from employees e, employees m
where m.employee_id=e.manager_id;
방법2
select e.employee_id as 사원번호,
e.last_name as 사원이름,
m.last_name as 관리자
from employees e
join employees m on(m.employee_id=e.manager_id);
ex12) cross join : 모든행에 대해 가능한 모든조합을 생성하는 조인
select * from countries, locations; → 575레코드
select * from countries cross join locations;
ex13) Non Equijoin (넌 이큐조인)
컬럼값이 같은 경우가 아닌 범위에 속하는지 여부를 확인 할 때
on ( 컬럼명 between 컬럼명1 and 컬럼명2)
create table salgrade(
salvel varchar2(2),
lowst number,
highst number);
insert into salgrade values('A', 20000, 29999);
insert into salgrade values('B', 10000, 19999);
insert into salgrade values('C', 0, 9999);
commit;
select * from salgrade;
select last_name, salary, salvel
from employees
join salgrade on(salary between lowst and highst)
order by salary desc;
ex14) n(여러)개의 테이블은 조인
업무ID 같은 사원들의 사원이름, 업무내용, 부서이름을 출력하시오
(EMPLOYEES, JOBS, DEPARTMENTS 테이블을 조인)
<분석>
EMPLOYEES JOBS DEPARTMENTS
------------------------------------------------------------
department_id job_id department_id
job_id
<출력>
last_name job_title department_name
--------------------------------------------------------------
select last_name, job_title, department_name
from employees
join departments using(department_id)
join jobs using(job_id);
[문제3] 위치ID, 부서ID를 연결해서 사원이름,도시,부서이름을 출력하시오
(관련테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS)
조건1 : 사원이름, 도시, 부서이름로 제목을 표시하시오
조건2 : Seattle 또는 Oxford 에서 근무하는 사원
조건3 : 도시순으로 오름차순정렬하시오
사원이름 도 시 부서이름
---------------------------------------------
Hall Oxford Sales
[문제4] 부서ID, 위치ID, 국가ID를 연결해서 다음과 같이 완성하시오
(관련테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS, COUNTRIES)
조건1 : 사원번호,사원이름,부서이름,도시,도시주소,나라명로 제목을 표시하시오
조건2 : 도시주소에 Ch 또는 Sh 또는 Rd가 포함되어 있는 데이터만 표시하시오
조건3 : 나라명, 도시별로 오름차순 정렬하시오
조건4 : 모든 사원을 포함한다
