22년 2월 4일_오라클 조인

select * from emp where(deptno, sal) in (select deptno, max(sal) from emp group by deptno);
--방법 1
select e10.empno, e10.ename, e10.deptno, d.dname, d.loc
from (select * from emp where deptno = 10)e10,
(select * from dept) d
where e10.deptno = d.deptno;
--방법 2
with e10 as (select * from emp where deptno = 10),
d as (select * from dept)
select e10.empno, e10.ename, e10.deptno, d.dname, d.loc
from e10, d
where e10.deptno = d.deptno;

--select 절에도 서브쿼리를 사용
select empno, ename, job, sal
(select grade from salgrade where e.sal between losal and hisal) as salgrade, deptno(selec dname from dept where e.deptNo,
(select dname from empt where e.deptno = dept.deptno )as dname from emp e;

--3번
select empno, ename, job, deptno, deptno, ename, loc from dept,emp 
where deptno = 30 not in
select job from emp where deptno = 10;
-- 10번 직무이면서 30 번부서에는 없는 부서
select e.empno, e.ename, e.job, e.deptno, d.deptno, e.ename, d.loc from dept d ,emp e
where d.deptno = e.deptno
and d.deptno = 10
and e.job not in (select job from emp where deptno = 30);


--select 로 emp출력
select * from emp;

--+로 붙여서 출력
select e.ename, d.deptno, d.dname from emp e, dept d 
where e.deptno(+) = d.deptno

--left outer join :기준이 왼쪽 테이블
-- 직원의 매니저를 출력
select e1.ename, e2.ename from emp e1 left outer join emp e2 where on e1.mgr = e2.empno;

--right outer join : 기준이 오른쪽 테이블
--emp - dept
select e.ename, d.deptno, d.dname
from emp e right outer join dept d
on e.deptno = d.deptno ;

select e1.ename ||'의 매니저는'||e2.ename||'이다.'
from emp e1, emp e2
where e1.mgr = e2.empno;

--이름이 allen인 사원의 부서 이름을 출력
select d.dname from emp e, dept d
where e.deptno = d.deptno
and e.ename = 'ALLEN';
또는
select dname from dept where deptno = (select * from emp where ename = 'ALLEN');

--직무가 analyst인 어떤 사원보다 급여가 적으면서, 직무가 'analyst'가 아닌 사원들의 사원번호, 이름, 직무, 급여를 출력하여라.
select empno, ename, job, sal from emp
where job not in 'ANALYST'
and sal < (select sal from emp where job = 'ANALYST');
또는
select empno, ename, job, sal from emp 
where sal < (select min(sal) from emp 
where job = 'ANALYST') and not job = 'ANALYST';

--부서 이름이 sales인 사원의 평균 급여보다 급여가 많고, 부서 이름이 research인 사원들의 평균 급여보다 급여가 적은 사원들의
이름, 부서 번호, 급여 직무를 출력하여라
select ename, deptno, sal, job from emp
where sal > any (select avg(sal) from emp where deptno = 30)
and sal < any (select avg(sal) from emp where deptno = 20);

-- ford와 부서가 같은 사원들의 이름, 부서 이름, 직무, 급여를 출력하여라
select e.ename, d.dname, e.job, e.sal from emp e, dept d
where e.deptno = d.deptno 
and e.deptno = (select deptno from emp where ename = 'FORD');

--각 부서별 부서 번호(모든 부서에 대해서), 부서이름, 부서위치, 사원의 수를 출력
select d.deptno, e.deptno, d.dname, d.loc, (select count(e.ename) from emp) from dept d, emp e
where e.deptno = d.deptno;
또는
select d.deptno, d.dname, d.loc, count(e.ename)
from emp e, dept d
where d.deptno = e.deptno(+)
group by d.deptno, d.dname, d.loc
order by deptno;
또는
select d.deptno, d.dname, d.loc,count(e.ename)from emp e right outer join dept d 
on e.deptno = d.deptno
group by d.deptno, d.name, d.loc;

(+)는 부족한쪽에 기입

-- 전체 사원의 평균 급여보다 급여가 많은 사원의 사원번호, 이름, 부서이름, 입사일 부서위치, 급여를 출력하여라
select e.empno, e.ename, d.dname, e.hiredate, d.loc, e.sal from emp e, dept d
where e.deptno = d.deptno and sal > (select avg(sal) from emp);

-- 10 _1 테이블 갖고옴 // 테이블을 복사하면 튜플만 가지고 오고, 제약사항은 copy되지 않음
create table dept_temp
as select * from dept;

--table 삭제
drop table 테이블 이름 cascade constraints;
무조건 삭제됨

--10_3
insert into dept_temp(deptno, dname, loc)
values(50, 'DATABASE','SEOUL');

--undo rolbark; (dml만 undo가 됨)

insert into dept_temp(deptno, dname, loc)
values(50, 'DATABASE','SEOUL');
insert into dept_temp(deptno, dname, loc)
values(70, 'WEB',NULL);
insert into dept_temp(deptno, dname, loc)
values(80, 'MOBILE','');
insert into dept_temp(deptno, loc)
values(90, 'INCHEON');
--sysdate 넣기
insert into emp_temp
values(3111,'심청이','manager',999,sysdate, 4000,null,30);

insert into emp_temp
select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno from emp e, salgrade s where e.sal between s.losal and s.hisal and s.grade =1;

update 테이블이름
set 바꿀값
where 조건;

update dept_temp2
set dname ='DATABASE', loc ='SEOUL'
where deptno = 40

--서브쿼리로 update 실행
update dept_temp2
set (dname, loc) = (select dname, loc from dept where deptno = 40)
where deptno = 40;

update dept_temp2
set loc = 'SEOUL'
where deptno = (select deptno from dept_temp2 where dname = 'OPERATIONS');

CREATE TABLE EMP_TEMP2
AS SELECT * FROM EMP;

--삭제
delete from emp_temp2
where job = 'MANAGER';

--30번부서에 근무하면서 호봉이 3호봉인 사람을 삭제
delete from emp_temp2
where empno in (select e.empno from emp_temp2 e, salgrade s where e.sal vetween s. losal and s.hisal and s.grade = 3 and deptno = 30);

--10_25
delete from emp_temp2;

--문제 1
create table chap10hw_emp as select * from emp;
create table chap10hw_dept as select * from dept;
create table chap10hw_salgrade as select * from salgrade;

--문제 3번
update chap10hw_emp
set deptno = 30
 where sal > (select avg(sal) from chap10hw_emp where deptno = 20);

--문제 4번
update chap10hw_emp
set sal = sal*1.1, deptno = 20
where hiredate > (select min(hiredate) from chap10hw_emp where deptno = 30);

 

//yesterday wished to today list

 

. 수업 100프로 다 이해하기 👎

. 몸, 정신 다듬고 챙기기 🤨

. 컨디션 300% 돌아오기 🤨

 

tomorrow wish list

 

. 이것이 자바다 6장까지 보기