[SQL] 20190603

11. 20번 부서의 최대 급여보다 최대 급여가 큰 부서의 번호와 최대 급여를 검색하라.
select deptno, max(sal)
from emp
group by deptno, sal
having sal > (select distinct max(sal)
        from emp
        where deptno = 20);

12. 급여가 모든 부서들의 평균 급여보다 많은 사원 정보를 검색하라. (ALL)
모든 부서들의 평균 급여
select dname, avg(sal)
from dept natural join emp
group by dname;

->
select *
from emp
where sal > all(select avg(sal)
from dept natural join emp
group by dname);

SELECT *
FROM EMP
WHERE SAL >= ALL(SELECT ROUND(AVG(SAL))
  FROM EMP
  GROUP BY DEPTNO);


UNION : 쿼리 결과값의 합이며 중복 제거
UNION ALL : 쿼리 결과값의 합이며 중복 포함
INTERSECT : 쿼리 결과값의 중복 값
MINUS : 첫 번째 쿼리 결과값에서 두 번째 쿼리 결과값을 뺀 값

기계/전기전자과 학생 찾기
select *
from student
where stu_dept in ('기계', '전기전자');

select *
from student
where stu_dept = '기계'
union
select *
from student
where stu_dept = '전기전자';


select *
from student
where stu_dept = '컴퓨터정보'
union/union all/intersect/minus
select *
from student
where stu_grade = '2';

1. SALES 부서와 RESEARCH 부서에 근무중인 사원 정보를 모두 출력하시오.
- union
select *
from emp natural join dept
where dname = 'SALES'
union
select *
from emp natural join dept
where dname = 'RESEARCH';

-
select *
from emp natural join dept
where (dname = 'SALES'
or dname = 'RESEARCH');

-union
select *
from emp join dept
on emp.deptno = dept.deptno
where dept.dname = 'SALES'
union
select *
from emp join dept
on emp.deptno = dept.deptno
where dept.dname = 'RESEARCH';

2. SALESMAN 이지만, 보너스를 받지 않는 사원의 정보를 출력하시오.
- minus
select *
from emp
where job = 'SALESMAN'
minus
select *
from emp
where comm is not null;

-intersect
select *
from emp
where job = 'SALESMAN'
intersect
select *
from emp
where comm is null;


DML 종류

INSERT INTO [테이블명] VALUES ~
- 자료 삽입 시 사용
- 접미사 INTO를 항상 붙임

UPDATE [테이블명] SET ~ WHERE ~
- 자료 수정 시 사용

DELETE FROM [테이블명] WHERE ~
- 자료 삭제 시 사용
- 접미사 FROM을 항상 붙임


INSERT INTO emp_test
VALUES(7369, 'SMITH', 'CLERK', 7902, TO_DATE('80/12/17'), 800, NULL, 20);

원하는 데이터만 입력할 경우
INSERT INTO dept (deptno, dname)
VALUES (10, 'ACCOUNTING');

SELECT 문장을 이용한 INSERT
INSERT INTO dept2
SELECT *
FROM dept;


1. EMP_TEST에 'SMITH'를 제외한 모든 사원의 정보를 입력하시오.
INSERT INTO EMP_TEST
SELECT *
FROM EMP
WHERE ename != 'SMITH';

-> empno로 삽입 추천
INSERT INTO EMP_TEST
SELECT *
FROM EMP
WHERE empno != 7369;

2. EMP_TEST 테이블에 아래 정보를 입력하시오.
월급 : 10000.00
보너스 : 1000
이름 : SON
사원번호 : 1112

INSERT INTO EMP_TEST (SAL, COMM, ENAME, EMPNO)
VALUES (10000.00, 1000, 'SON', 1112);

UPDATE 테이블 명
SET 수정되어야 할 칼럼 명 = 수정되기를 원하는 새로운 값
WHERE 조건;

UPDATE 테이블 명
SET 수정되어야 할 칼럼 명 = (SubQuery)
WHERE 조건;

UPDATE EMP_TEST
SET ENAME = 'YEO'
WHERE EMPNO = 1112;

- 사원번호가 7900번인 사원의 부서 번호를 30으로 수정
update emp_test
set deptno = 30
where empno = 7900;

- 부서번호 20의 사원들 급여가 10% 인상됨
update emp_test
set sal = sal*1.1
where deptno = 20;

- 모든 사원의 입사일을 오늘로 수정 (sysdata)
update emp_test
set hiredate = sysdate;

- EMP_TEST에 사원번호 9999, 사원이름 KIRTI 입력하시오.
그 후에 사원번호가 9999인 사원의 이름을 KITRI00으로 변경하시오.

INSERT INTO EMP_TEST (empno, ename)
VALUES (9999, 'KIRTI');
COMMIT;

UPDATE EMP_TEST
SET ENAME = 'KITRI00'
WHERE EMPNO = 9999
COMMIT;

- subject_test 테이블을 새로 만드시오.
- subject_test 테이블에 subject 테이블 정보를 모두 저장합니다.
- subject_test 테이블에 아래 정보를 입력하시오.
(112, 오라클DB, 여동빈, 1, 컴퓨터정보)

create table subject_test(
sub_no char(3),
sub_name varchar2(30),
sub_prof varchar2(12),
sub_grade number(1),
sub_dept varchar2(20),
constraint p_test_sub_no primary key(sub_no));

insert into subject_test
select *
from subject;

insert into subject_test
values (112, '오라클DB', '여동빈', 1, '컴퓨터정보');

- enrol_test 테이블을 새로 만드시오.
- enrol_test 테이블에 enrol 정보를 모두 저장하시오.
- enrol_test 테이블에 시스템분석 설계를 듣는 학생들의 점수는 10점씩 증가 시키시오.

create table enrol_test(
sub_no char(3),
stu_no char(9),
enr_grade number(3),
constraint p_tset_course primary key(sub_no,stu_no));

insert into enrol_test
select *
from enrol;

update enrol_test
set enr_grade = enr_grade + 10
where sub_no = (select sub_no
from subject
where sub_name = '시스템분석설계');

6. emp_test 테이블에서 월급 등급이 3인 직원의 월급을 15% 인상하여 저장하시오.

월급 등급이 3인 직원
select empno, grade
from salgrade natural join emp
where grade = 3;

update emp_test
set sal = sal * 1.15
where empno = (select empno, grade
         from salgrade natural join emp
         where grade = 3);

->
update emp_test
set sal = sal*1.15
where empno in (select empno
         from emp_test, salgrade 
         where emp_test.sal between losal and hisal
         and grade = 3);

7. emp_test 테이블에서 DALLAS에서 일하는 COMM을 100씩 추가하시오.
update emp_test
set comm = nvl(comm,0) + 100
where deptno = (select deptno 
         from dept 
         where loc = 'DALLAS');

8. emp_test 테이블에서 SCOTT의 상급자를 BLAKE로 변경하시오.
update emp_test
set mgr = (select empno
 from emp_test
 where ename = 'BLAKE')
where empno = (select empno
         from emp_test
         where ename = 'SCOTT');

DELETE FROM 삭제하기를 원하는 정보가 들어있는 테이블 명
WHERE 조건;

delete from emp_test
where empno = 1113;

delete from emp_test;

rollback;

- 사원번호가 7902번인 사원의 데이터를 삭제
delete from emp_test
where empno = 7902;

- 평균급여보다 적게 받는 사원 삭제
delete from emp_test
where sal < (select avg(sal)
   from emp_test);

- 모든 행을 삭제
delete from emp_test;


* MERGE Structure
조건에 따라서 해당 행이 존재하는경우 UPDATE(DELETE 포함 할 수 있음)를 수행,
새로운 행 일 경우 INSERT를 수행
대상 테이블에 대한 UPDATE/INSERT 조건은 ON 절에 의해 결정됨

12. emp에 다음 정보를 입력하시오.
사원번호 1112
이름 PARK
월급 1000
만약에 데이터가 존재하면 UPDATE 하고,
데이터가 존재하지 않으면 INSERT 하시오.

merge into emp e
using dual t
on (e.empno = 1112)
when matched then
update set e.ename = 'PARK', e.sal = 1000
when not matched then
insert (e.empno, e.ename, e.sal)
values (1112, 'PARK', 1000);

 

13. 컴퓨터정보 1,2학년 학생이면 몸무게를 10kg씩 증가시키고,
컴퓨터정보 3학년 학생이면 몸무게 20kg를 증가시켜 새로운 student_test 데이터를 저장하시오.
(새로운 student_test 테이블 생성이 필요함.)

MERGE INTO STUDENT_TEST A
    USING STUDENT B ( using (select * from student where stu_dept = '컴퓨터정보')
    ON (A.STU_NO = B.STU_NO)
WHEN MATCHED THEN
    UPDATE SET A.STU_WEIGHT = B.STU_WEIGHT + 
    DECODE(B.STU_GRADE, 1, 10, 2, 10, 3, 20)
WHEN NOT MATCHED THEN
    INSERT (A.STU_NO, A.STU_NAME, A.STU_DEPT, A.STU_GRADE, 
    A.STU_CLASS, A.STU_GENDER, A.STU_HEIGHT, A.STU_WEIGHT)
    VALUES (B.STU_NO, B.STU_NAME, B.STU_DEPT, B.STU_GRADE, 
    B.STU_CLASS, B.STU_GENDER, B.STU_HEIGHT, B.STU_WEIGHT + 
    DECODE(B.STU_GRADE, 1, 10, 2, 10, 3, 20));