ORACLE

오라클 3일

※ 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

방법2(오라클전용구문)

방법3(Ansi표준)

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) : 오른쪽 테이블은 모두포함하여 조인

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

방법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 : 모든사원을 포함한다

[DB] ORACLE - 3일

ORACLE

오라클 3일

🐯

ORACLE

오라클 3일

Share this post