SQL문 동작 순서 및 조인 개념

yoog·2023년 1월 20일
0

데이터베이스

목록 보기
2/2

SQL문 작성 순서

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

select department_id 부서ID, to_char(trunc(avg(salary)), '999,999') 부서별급여평균
from employees
group by department_id
order by 2

select job_id 업무ID, to_char(sum(salary), 'L999,999') 급여합계
from employees
group by job_id
order by 2

select last_name, to_char(trunc(avg(salary)), '999,999') 부서별급여평균
from employees
group by department_id   -- 데이터를 묶는다
-- 부서를 묶으면 사원이 안나옴

*실행이 되지 않는 코드! 부서를 묶은 데이터만 select 가능.

select department_id 부서ID, to_char(trunc(avg(salary))) "평균급여"
from employees
group by department_id
having avg(salary)>=6000;

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

select department_id, max(salary) max_salary
from employees
group by department_id	-- 부서를 전부 묶은다음
having department_id = 10 or department_id = 20 -- 10번, 20번을 뽑아낸다.	
order by department_id	-- 묶는 횟수가 많을 수록 비효율적이다. 20만건이면..
-
 --효율적인 방법 
select department_id, max(salary) max_salary
from employees
where department_id = 10 or department_id = 20 -- 부서가 10, 20 뽑아낸 다음 묶음
group by department_id	-- 그룹화 횟수가 줄어든다.
order by 1;

select department_id, max(salary) "max_salary"
from employees
where department_id in(10, 20)
group by department_id
having max(salary) >=5000
order by 1;

select department_id, min(salary) min_salary
from employees
group by department_id
having 20<=department_id and department_id <=80
order by 1;

조인 개념

OUTER JOIN

-- 오라클 구문
select employee_id, employees.department_id, department_name
from employees, departments
where employees.department_id=departments.department_id;
-
select employee_id, e.department_id, department_name
from employees e, departments d
where e.department_id=d.department_id;
-
--ANSI 표준
select employee_id, department_id, department_name
from employees
inner join departments using(department_id) -- inner 생략 가능

-- 오라클 구문
select department_id, city
from departments d, locations l
where d.location_id = l.location_id   -- 같은 것에 대해서 조인을 해달라
order by 1 desc
-
-- ansi 표준
select department_id, city
from departments
join locations using(location_id)
order by 1

select last_name, e.department_id, department_name
from employees e, departments d
where e.department_id = d.department_id(+); 
-
select * from departments 
-
select last_name, department_id, department_name
from employees
left join departments using(department_id)  -- left outter join으로 표기 가능

select last_name, e.department_id, department_name
from employees e, departments d
where e.department_id(+) = d.department_id
-
select last_name, department_id, department_name
from employees
right join departments using(department_id)  -- right outter join으로 표기 가능

select last_name, department_id, department_name
from employees
full join departments using(department_id)

select location_id, city, department_name
from departments
join locations using(location_id);

-- 오라클 구문
select last_name, d.department_id, d.manager_id
from departments d, employees e
where (d.department_id = e.department_id) and (d.manager_id = e.manager_id)

--ansi 표준
select last_name, department_id, manager_id
from employees
join departments using(department_id, manager_id);


--자연조인
select last_name, department_id, manager_id
from employees
natural join departments

create table locations2
as
select * from locations; -- 테이블 복사

select * from locations2;

alter table locations2
rename column location_id to loc_id;

--오라클 구문
select department_id 부서ID, department_name 부서명, city 도시
from departments d, locations2 l
where d.location_id = l.loc_id;

--ansi표준
select department_id 부서ID, department_name 부서명, city 도시
from departments
join locations2 on(location_id = loc_id)  -- 컬럼명이 다른 경우 on 사용

--오라클 구문
select e.employee_id 사원번호, e.last_name 사원이름, m.last_name 관리자
from employees e, employees m
where e.manager_id = m.employee_id(+);

-- ansi 표준
select e.employee_id 사원번호, e.last_name 사원이름, m.last_name 관리자
from employees e
left join employees m on (e.manager_id = m.employee_id)

select count(*) from countries;	-- 25
select count(*) from locations; -- 23

-- 오라클 구문
select * from countries, locations; -- 25*23 = 575

--ansi표준
select *
from countries
cross join locations;

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  -- 우측 상단에 auto commit이 있으면 실행안해도 커밋

select * from salgrade;
select * from tab;

select last_name 사원이름,salary 급여, salvel "급여등급"
from employees
left join salgrade on (salary between lowst and highst)
order by salary desc;

select * from employees;
select * from jobs;
select * from departments;

select last_name 사원이름, department_name 부서이름, job_title 업무명
from employees	
join departments using(department_id)
join jobs using(job_id)
order by 2, 3

;  -- 세미콜론은 범위지정, 다른 편집기에선 컨트롤+엔터로 실행함.

select last_name 이름, department_name 부서이름, manager_id 매니저ID
from employees
join departments using (manager_id)
where department_name = 'IT'
order by 1

select department_id, city
from departments
join locations using (location_id)
where city = 'Seattle'
order by 1 desc

select last_name, department_id, department_name
from employees
join departments using(department_id)
where department_id in (30, 90)
order by 1;

select last_name 사원이름, city "도    시", department_name 부서이름
from employees
left join departments using (department_id)	-- 이 결과를 바탕으로 아래와 조인,
left join locations2 on (location_id = loc_id)
where city in ('Seattle', 'Oxford')
order by 2

select employee_id 사원번호, last_name 사원이름, department_name 부서이름,
	city 도시, street_address 도시주소, country_name 나라명
from employees
left join departments using(department_id)
left join locations2 on(loc_id =location_id)
left join countries using(country_id)
where street_address like '%St%' or street_address like '%Vi%'
order by 6, 4
profile
웹 개발자로 취업하고 싶은 사람입니다.

0개의 댓글