DAY 27 - Oracle DB 연습3 - GROUP BY / JOIN (2024.08.08)

※ 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 : 묶어놓은 그룹의 조건절

---

select to_char(trunc(avg(salary), 0), '99,999') 사원급여평균 from employees;

 

---

부서별에대한 평균급여를 얘기하는 것이므로 having 이용해야 한다는 거 캐치 !!

select department_id 부서코드, 
       to_char(round(avg(salary), 0), 'L99,999,999') 평균급여
from employees
group by department_id
having avg(salary) >= 5000
order by department_id;

---

 

select 해오는 애들만 group by로 해야한다 !

위에서는 last_name과 avg(salary)로만 그룹을 지을 수 있는 것이다.

 

---

PARTITION BY는 select 안 된 것도 가능함.

---

select job_id, sum(salary)
from employees
group by job_id;

---

select last_name, job_id, sum(salary)
from employees
group by job_id;

 

※ 이렇게하면 error가 뜬다. group by를 하려면  select한 것 중에 해야하면서 

     select했으면 group by를 하던지 해야한다.

 

여러개 select 할 거면 partition by를 써야한다. 

---

select department_id, max(salary) as max_salary
from employees
group by department_id
having department_id in(10, 20)
order by department_id;

---

select department_id, max(salary) max_salary
from employees
where department_id in(10, 20)
group by department_id
order by department_id;

 

한 줄 한 줄 읽을 때 이미 10과 20인 것만 골라내서 그룹으로 잡기 때문에 having 절보다는 where 절을 추천한다.

 

---

조인(JOIN)

 

select 과일.코드, 과일명, 개수

from 과일, 판매

where 과일.코드 = 판매.코드

 

※ 종류

1. Inner join : 같은 것끼리만 연결

2. Outer join : 한쪽을 기준(모두포함)해서 연결

                          left join : 왼쪽 컬럼 모두포함

                          right join : 오른쪽 컬럼 모두포함

3. full join : 왼쪽, 오른쪽 모두 포함

4. self join : 자기 자신 테이블과 연결

5. cross join : 모든 경우의 수로 연결

6. non equijoin : 범위에 속하는지 여부를 확인

7. n개 테이블 조인 : 여러 개의 테이블 조인

 

---

---

[ INNER JOIN ]

 

• 방법1(오라클 전용 구문)

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

---

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

select employee_id, e.department_id, 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);

---

select department_id, city
from departments
join locations using(location_id)
order by 1;

---

[ OUTER JOIN ] - 한 쪽에 있는 애들은 다 가져오는 것

 

select 과일.코드, 과일명, 개수

from 과일, 판매

where 과일.코드 = 판매.코드

 

 outer join(left) - 이 경우 판매 안 된 과일도 볼 수 있다.

 

 

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

---

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

---

full join은 잘 사용하지 않음.

• 방법1(오라클 전용 구문) : 없다

• 방법2(Ansi 표준)

select last_name, department_id, department_name
from employees
full join departments using(department_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);

---

create table locations2 as select * from locations;

select * from locations;
select * from locations2;

똑같은 테이블이 된다.

alter table locations2 rename column location_id to loc_id;

select * from locations2; -- 컬럼명이 바뀌었다

컬럼명이 바뀐 것을 확인할 수 있다.

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

 

join으로 할 때는 on을 이용해야한다. 이름이 달라졌으므로 !

 

• 방법3(Ansi 표준)

select department_id, city
from departments d
join locations2 l on(d.location_id = l.loc_id);

---

select last_name, job_title, department_name
from employees
join departments using(department_id)
join jobs using(job_id);

---

select last_name 사원이름, city "도 시", department_name 부서이름
from departments
join employees using(department_id)
join locations2 on(location_id = loc_id)
where city in('Seattle', 'Oxford')
order by city;

 

뭘 먼저 적어도 and 조건이므로 상관없다.

---

select employee_id 사원번호, last_name 사원이름, 
       department_name 부서이름, 
       city 도시, street_address 도시주소,
       country_name 나라이름
from departments 
join employees using(department_id)
join locations2 on(location_id = loc_id)
join countries using(country_id)
where street_address like '%Ch%'
      or street_address like '%Sh%'
      or street_address like '%Rd%'
order by country_name, city ,department_name;

추가로 부서이름으로 오름차순도 해주었다.

select employee_id 사원번호, last_name 사원이름, 
       department_name 부서이름, 
       city 도시, street_address 도시주소,
       country_name 나라이름
from departments 
right join employees using(department_id)
join locations2 on(location_id = loc_id)
join countries using(country_id)
where street_address like '%Ch%'
      or street_address like '%Sh%'
      or street_address like '%Rd%'
order by country_name, city ,department_name;

 

모든사원을 포함하라 해서 employees에  right join을 걸었지만 어차피 where로 걸렀기 때문에 똑같은 결과가 나옴.

 

---

[ GROUP BY / HAVING ]

 

문제1) 화학과를 제외하고 학과별로 학생들의 평점 평균을 검색하시오 (GROUP, HAVING)
       평균을 소수이하 2째 자리에서 반올림
       테이블 : STUDENT

select major 학과, round(avg(avr), 2) "평점 평균"
from student
group by major
having major not in('화학');

select major 학과, round(avg(avr), 2) "평점 평균"
from student
group by major
having major != '화학';

 

오 이렇게 !=  해도 된다 !!

---

문제2) 화학과를 제외한 각 학과별 평균 평점 중에 평점이 2.0 미만인 정보를 검색하시오
       테이블 : STUDENT

select major 학과, round(avg(avr), 2) "평점 평균"
from student
group by major
having round(avg(avr), 2) < 2.0 and major not in('화학');

 

---

[ JOIN]

 

문제1) 화학과 1학년 학생의 기말고사 성적을 검색하시오
테이블 : STUDENT ST, SCORE SC, COURSE CO
컬럼 : SNO, SNAME, MAJOR, SYEAR, CNO, CNAME, RESULT

select sno, sname, major, syear, cno, cname, result
from score
join student using(sno)
join course using(cno)
where major = '화학' and syear = '1';

---

문제2) 화학과 1학년 학생의 일반화학 기말고사 점수를 검색하시오
테이블 : STUDENT ST, SCORE SC, COURSE CO
컬럼 : SNO, SNAME, MAJOR, SYEAR, CNO, CNAME, RESULT

select sno, sname, major, syear, cno, cname, result
from score
join student using(sno)
join course using(cno)
where major = '화학' and syear = '1' and cname = '일반화학';

 

---