Oracle 4

hr.sql

--@E:\test_data_eng_수정.sql

--professor테이블에서 교수의 이름과 학과 명을 출력하되
--학과 번호가 101번이면 'Computer Engineering',
--102번이면 'Multimedia Engineering',
--103번이면 'Software Engineering'
--나머지는 'ETC'로 출력하세요.
select name, deptno, 
case when deptno=101 then 'Computer Engineering'
    when deptno=102 then 'Multimedia Engineering'
    when deptno=103 then 'Software Engineering'
    else 'ETC'
end as dname
from professor;

--decode로
select profno, name, deptno,   
    decode(deptno, 101, 'Computer Engineering',
                102, 'Multimedia Engineering',
                103, 'Software Engineering',
                'ETC') 학과명
from professor;

--student table
select * from student;

--tel의 지역번호에서 02서울, 051부산, 052울산, 053대구
--나머지는 기타로 출력
--이름, 전화번호 ,지역 출력
select name, tel, 
case 
when substr(tel,1,2)=02 then '서울'
when substr(tel,1,3)=051 then '부산'
when substr(tel,1,3)=052 then '울산'
when substr(tel,1,3)=053 then '대구'
else '기타'
end as loc
from student;

--@@@@@@decode로
select name, tel,
    decode(substr(tel,1,instr(tel,')')-1), '02', '서울',
            '051', '부산',
            '052', '울산',
            '053', '대구',
            '기타') loc
from student;

--@@@@@professor 테이블에서
--학과별로 소속 교수들의 평균급여, 최소급여, 최대급여를 출력하라.
--단, 평균급여가 300인 넘는 것만 출력.
select deptno, round(avg(pay)) 평균급여, min(pay) 최소급여, max(pay) 최대급여
from professor
group by deptno
having avg(pay) > 300
order by deptno desc;

--@@@@@학생 수가 4명 이상인 학년에 대해서
--학년, 학생 수, 평균 키, 평균 몸무게를 출력하라.
--단, 평균 키와 평균 몸무게는 소숫점 첫 번째 자리에서 반올림하고,
--출력순서는 평균 키가 높은 순부터 내림차순으로 출력하라.
select *
from student;
--
select grade, count(*), round(avg(height)), round(avg(weight)) 
from student
group by grade
having count(*) > 4
order by avg(height) desc;

--학생들의 학생 이름, 지도교수 이름 출력
select s.name 학생이름, p.name 지도교수이름
from student s, professor p
where s.profno = p.profno;

--join~on (join on 으로 변경은 ',' 자리에 join, where 자리에 on 으로 바꾸면 끝)
select s.name, p.name
from student s join professor p
on s.profno = p.profno;

--
select * from customer;
select * from gift;

--@@@@@gift, customer table 사용
--고객이름, 포인트 ,선물 출력
select c.gname, c.point, g.gname
from gift g, customer c
where c.point between g_start and g_end;
--join~on
select c.gname, c.point, g.gname
from gift g join customer c
on c.point between g_start and g_end;

--
select * from student;
select * from score;
select * from hakjum;

--학생들의 이름과 점수(total) 학점 출력
select s.name, s1.total, h.grade 
from student s, score s1, hakjum h
where s.studno = s1.studno
and s1.total between h.min_point and h.max_point;

--between 없이 작성
select s.name, s1.total, h.grade 
from student s, score s1, hakjum h
where s.studno = s1.studno
and s1.total >= h.min_point 
and s1.total <=  h.max_point;

--@@@@@join~on 비등가조인??
select s.name, s1.total, h.grade
from student s join score s1 
                on s.studno = s1.studno
                join hakjum h
                on s1.total >= h.min_point and s1.total <= h.max_point;

--
select name, profno from student;

--@@@@@학생이름과 지도교수 이름을 출력하되 지도교수가 정해지지 않은 학생이름도 출력
--외부조인(outer join)
select s.name 학생이름, p.name 지도교수
from student s, professor p
where s.profno = p.profno(+);

--@@@@@표준(left)
select s.name 학생이름, p.name 지도교수
from student s left outer join professor p
on s.profno = p.profno;

--@@@@@표준(right)
select s.name 학생이름, p.name 지도교수
from professor p right outer join student s
on s.profno = p.profno;

--@@@@@모든 교수 이름 출력
select * from professor;
select distinct(profno) from student;
--
select s.name 학생이름, p.name 지도교수이름
from student s, professor p
where s.profno(+) = p.profno;

--@@@@@표준
select s.name, p.name, p.profno
from student s right outer join professor p
on s.profno = p.profno
order by p.profno;

--101번 학과에 소속된 지도교수 이름 출력,
--단 지도교수 없는 학생도 출력(학생이름, 지도교수 이름 출력)
select * from student; 

select s.name, p.name, s.deptno1
from student s, professor p
where s.profno = p.profno(+) 
and s.deptno1 = 101;

--@@@@@표준
select s.name, p.name, s.deptno1
from student s left outer join professor p
on s.profno = p.profno
where s.deptno1 = 101;

--
select * from emp2;
select * from dept2;
--'Seoul Branch Office'에 근무하는 사원의 사원번호, 사원이름, 부서번호 출력
select e.empno, e.name, e.deptno, d.area
from emp2 e, dept2 d
where e.deptno = d.dcode
and d.area = 'Seoul Branch Office';

select empno, name, deptno
from emp2
where deptno in (
    select dcode
    from dept2
    where area = 'Seoul Branch Office'); 

scott.sql

select
from emp e, dept d, salgrade s, emp e2
where e.deptno(+) = d.deptno
and e.sal between s.losal(+) and s.hisal(+)
and e.mgr = e2.empno(+)
order by d.deptno, e.empno;

--join~on
select d.deptno, d.dname,
    e.empno, e.ename, e.mgr, e.sal, e.deptno,
    s.losal, s.hisal, s.grade,
    e2.empno mgr_empno, e2.ename mgr_ename
from emp e RIGHT outer join dept d on (e.deptno = d.deptno)
    Left OUTER join salgrade s 
        on(e.sal between s.losal and s.hisal)
    Left OUTER join emp e2 on(e.mgr = e2.empno)
order by d.deptno, e.empno;

--p.242 서브쿼리
--WARD보다 월급이 많은 사원의 이름 출력
select sal
from emp
where ename = 'WARD';
select ename, sal
from emp
where sal>1250;
-----
select ename
from emp
where sal > (
        select sal
        from emp
        where ename = 'WARD'
    );
--ALLEN의 직무(JOB)와 같은 사람의 이름, 부서명, 급여, 직무를 출력
select job
from emp
where ename='ALLEN';
select e.ename, d.dname, e.sal, e.job
from emp e, dept d
where e.deptno=d.deptno and e.job = (
    select job
    from emp
    where ename='ALLEN'
);
-----
select e.ename, d.dname, e.sal, e.job
from emp e, dept d
where e.deptno = d.deptno
and e.job = (
    select job
    from emp
    where ename='ALLEN'
);
---
select e.ename, d.dname, e.sal, e.job
from emp e, dept d
where e.deptno = d.deptno
and e.job = (
    select job
    from emp
    where ename='ALLEN'
) and e.ename <> 'ALLEN';
--SMITH보다 일찍 입사한 사원의 정보
select hiredate
from emp
where ename = 'SMITH';

select *
from emp
where hiredate < (
    select hiredate
    from emp
    where ename = 'SMITH'
);
--ALLEN보다 먼저 입사한 사원 수
select count(*)
from emp
where hiredate < (
    select hiredate
    from emp
    where ename='ALLEN'
);
--전체 사원의 평균 임금보다 많은 사원의 사원번호, 이름, 부서명, 입사일
select e.sal, d.deptno, e.ename, d.dname, e.hiredate 
from emp e, dept d
where e.deptno = d.deptno
and e.sal > (
    select avg(sal)
    from emp
);
--전체 사원의 평균 급여보다 작거나 같은 급여를 빋고있는 20번 부서의 사원 및 부서정보
--사원번호, 이름, 직무, 급여, 부서번호, 부서명, 부서지역을 출력하라
select e.empno, e.ename, e.job, e.sal, e.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno=d.deptno
    and e.deptno=20
    and e.sal <= (
        select avg(sal)
        from emp
    );
--각 부서별 최고 급여와 동일한 급여를 받는 사원 정보 출력
select max(sal)
from emp
group by deptno;
--
select *
from emp
where sal in (
    select max(sal)
    from emp
    group by deptno
);
--10번 부서 중에서 30번 부서에는 없는 업무(job)를 하는 
--사원의 사원번호, 이름, 부서명, 입사일, 지역 출력
select e.empno, e.ename, d.dname, e.hiredate, d.loc, e.job
from emp e, dept d
where e.deptno=d.deptno
and e.deptno=10
and e.job not in (
    select job
    from emp   
    where deptno=30
);
--
select job
from emp
where deptno=10
minus
select job
from emp
where deptno=30;

---
--mgr이 KING인 사원의 이름과 JOB 출력(서브쿼리 사용, emp테이블)
select * from emp;

select empno
from emp
where ename = 'KING'; 
    
select ename, job 
from emp
where mgr = (
    select empno
    from emp
    where ename = 'KING' 
);

--@@@@@서브쿼리 사용 안함
select e.ename, e.job
from emp e, emp m
where e.mgr = m.empno
and m.ename = 'KING';

--매니저가 KING이거나 FORD인 사원들의 이름과 직급을 출력하라(서브쿼리 사용)
select * from emp;

select ename, job
from emp
where mgr in (select empno
                from emp
                where ename='KING' or ename='FORD');
--서브쿼리 사용x
select e.ename, e.job
from emp e, emp m
where e.mgr = m.empno 
and (m.ename='KING' or m.ename='FORD');

--join~on
select e.ename, e.job
from emp e join emp m
on e.mgr = m.empno 
where m.ename='KING' or m.ename='FORD';

--p.251
select ename, sal, job
from emp
where sal < any(select sal
                from emp
                where job='SALESMAN');

select ename, sal, job
from emp
where sal < all(select sal
                from emp
                where job='SALESMAN');
                
--30번 부서보다 적은 급여를 받는 사원 출력(any, all사용)
select * from emp;

select sal from emp where deptno=30;

select *
from emp
where sal < any(select sal
                from emp
                where deptno=30); 
--any와 같은 결과                
select sal
from emp
where sal < (select max(sal)
            from emp
            where deptno = 30);
            
--exists는 조건이 있으면 모든 내용 반환
select * 
from dept 
where exists (select deptno
            from dept
            where deptno=20);

select * from dept;

----------DDL
create table test(
    no number(3) DEFAULT 0,
    name VARCHAR2(20) default 'NONAME',
    hiredate date default sysdate
);

select * from test;
insert into test values(1, '홍길동', '2022-03-03);
insert into test(no, name) values(2, 'aaa');

--이름에 'bbb' 추가
insert into test(name) values('bbb);
commit;

--test1(no, name, address, tel)
--number(5), 문자열(20), 문자열(50), 문자열(20)
create table test1(
    no number(5),
    name VARCHAR2(20),
    address VARCHAR2(50),
    tel VARCHAR(20)
    );

select * from test1;
-- (1, 'aaa') 추가
insert into test1(no, name) values(1, 'aaa');
-- (2, 'bbb', '부산', '010-1111-2222')
insert into test1(no, name, address, tel) values(2,'bbb','부산','010-1111-2222');
insert into test1 values(3,'bbb','서울','010-1111-2222');
insert into test1(no, address) values(3,'서울');
insert into test1(no, tel) values(4,'010-3333-4444');
select * from test1;
commit;
------
-- no가 2번인 사람의 이름을 홍길동으로 수정
update test1
set name='홍길동'
where no=2;
--no가 4인 name을 test, address를 서울로 수정
update test1
set name='test', address='서울'
where no=4;
--no가 4인 name을 test1, address를 부산으로 수정
update test1
set name='test1', address='부산'
where no=4;

--삭제
--test1에서 1번을 삭제
delete test1
where no=1;
select * from test1;
delete from test1 where no=2;
delete from test1;

--test1
--insert 번호 5 추가
insert into test1(no) values(5);

--5번글에 name='ccc', address='인천'으로 수정
update test1
set name='ccc', address='인천'
where no=5;

--4번 글 삭제
delete test1 where no=4;

-----------
--CRUD(create, select, update, delete)
--DDL(create), DML(select,update,delete)

--p.226(CTAS: create table as)
create table dept_temp
as select * from dept;
select * from dept_temp;
select * from dept;

--dept_temp 테이블에 50, DATABASE, SEOUL 추가
insert into dept_temp(deptno, dname, loc) values(50, 'DATABASE', 'SEOUL');

--테이블 구조만 복사
create table emp_temp
as select * 
from emp
where 1<>1;

select * from emp_temp;

insert into emp_temp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(2111,'이순신','MANAGER',9999,to_date('07/01/2019','dd/mm/yyyy'),4000,null,20);

--emp_temp 테이블에 (3111, 입사일 오늘로)
insert into emp_temp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(3111,'강감찬','MANAGER',9999,sysdate,4000,null,20);

--3111번 사원의 급여를 5000으로 변경
update emp_temp
set sal=5000
where empno=3111;

--emp_temp 모든 사원 삭제
delete from emp_temp;
select * from emp_temp;
commit;

--p.275 서브쿼리 이용해서 한번에 여러 데이터 추가
--급여등급(salgrade)이 1인 사원만을 emp_temp 추가
select * from salgrade;
select * from emp
where sal between 700 and 1200;

insert 
into emp_temp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
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;
select * from emp_temp;
commit;

--dept 테이블을 복사해서 dept_temp2 테이블 생성
create table dept_temp2
as 
select * from dept;
select * from dept_temp2;

--40번 부서명을 DATABASE, 지역을 SEOUL로 수정하라
update dept_temp2
set dname='DATABASE', loc='SEOUL'
where deptno=40;
commit;

--dept테이블에서 부서번호가 40번인 부서명, 지역으로 수정
update dept_temp2
set dname=(select dname from dept where deptno=40), loc=(select loc from dept where deptno=40)
where deptno=40;
select * from dept_temp2;
rollback;

update dept_temp2
set (dname, loc) = ( 
    select dname, loc from dept where deptno=40
)
where deptno=40;
commit;

delete from dept_temp2;
drop table dept_temp2;

--dept 테이블을 이용해서 dept_tmp 테이블 생성하기
create table dept_tmp
as 
select * from dept;

select * from dept_tmp;
alter table dept_tmp
add(location varchar2(50));

--location을 뉴욕으로 10번 부서만 수정
update dept_tmp
set location='뉴욕'
where deptno=10;
commit;

alter table dept_tmp
modify(location varchar2(70));
commit;
--구조확인 describe
describe dept_tmp; 

alter table dept_tmp
drop column location;
commit;

select * from dept_tmp;

alter table dept_tmp
rename column loc to location;

rename dept_tmp to dept_temptmp;
rename dept_temptmp to dept_tmptmp;

select * from dept_tmptmp;
truncate table dept_tmptmp;
drop table dept_tmptmp;
rollback;