5.14 SQL ( 3 )
오전수업 ( 6장 )
- 조인과 집합
가로로 합치기 = join
----------------------------------------------------------------------------
-잘못된 조인 ( 키가 없음 )
select * from employees;
select * from jobs;
/* 잘못된 조인법 */
select * 행이 어마어마하게 나옴
from employees , jobs;
------------------------------------------------------------------------------
- 조인
select *
from employees e , jobs j
where (e.job_id = j.job_id); /*기준 부여해서 조인 -> 107개 행 나옴 */
------------------------------------------------------------------------------
- inner join ( 동등조인 )
select *
from employees e , departments d , locations l, COUNTRIES c , REGIONS r, job_history j , jobs j2
where (e.department_id = d.department_id)
and (d.location_id = l.location_id)
and (l.country_id = c.country_id)
and (j.employee_id = e.employee_id);
------------------------------------------------------------------------------
- 외부조인
select * from employees e full outer join departments d
on ( e.DEPARTMENT_ID = d.DEPARTMENT_ID );
------------------------------------------------------------------------------
(select salary, last_name from employees
union all
select manager_id, department_name from departments)
minus
select min_salary, job_id from jobs;
------------------------------------------------------------------------------
select department_id from employees
where department_id > 100
union all
select department_id from departments
where department_id > 200
order by 1;
------------------------------------------------------------------------------
select department_id from employees
where department_id > 100
minus
select department_id from departments
where department_id > 200
order by 1;
------------------------------------------------------------------------------
select * from job_history;
------------------------------------------------------------------------------
오후 수업 ( 7장 )
------------------------------------------------------------------------------
- 회사에서 가장 월급을 적게받는 사람의 이름
select first_name from EMPLOYEES
where salary = (select min ( salary ) from employees) ; + 서브쿼리만 블럭 잡아서 실행해보고 되면 메인쿼리 해보기
------------------------------------------------------------------------------
- 값이 하나 출력이면 = 쓰면되는데, 두개 이상이면 any를 써야함
select *
from employees A
where A.salary = any ( select salary from employees
where last_name='Taylor' );
------------------------------------------------------------------------------
오후 8장
------------------------------------------------------------------------------
Done --> commit ; /*완료*/
wait --> LOCK ; /*진행중*/
Cancle --> RollBack /*취소*/
-----------------------------------------------------------------------------
[예제 8-1]
INSERT INTO departments ( department_id, department_name, manager_id, location_id)
VALUES
(271, 'Sample_Dept', 200, 1700);
------------------------------------------------------------------------------
[예제 8-2]
INSERT INTO departments
VALUES
(272, 'Sample_Dept', 200, 1700);
------------------------------------------------------------------------------
INSERT INTO departments
VALUES
(271, Sample_Dept, 200, 1700);
---------------------------------------------------
commit;
---------------------------------------------------
8.3
UPDATE departments
SET manager_id = 201,
location_id = 1800
WHERE department_name = 'Sample_Dept';
---------------------------------------------------
8.3
[예제 8-3]
UPDATE departments
SET (manager_id, location_id) = ( SELECT manager_id, location_id
FROM departments
WHERE department_id = 40)
WHERE department_name = 'Sample_Dept';
---------------------------------------------------
[예제 8-4]
UPDATE departments
SET department_id = null
WHERE department_name = 'Sample_Dept';
---------------------------------------------------
8.4
[예제 8-5]
DELETE FROM departments
WHERE department_name = 'Sample_Dept';
---------------------------------------------------
DELETE FROM departments
WHERE department_id IN (SELECT department_id
FROM departments
WHERE department_name = 'Sample_Dept');
---------------------------------------------------