day 0618 데이터베이스(4)

 실습을 위하여 emp 테이블에 수당을 의미하는 칼럼을 추가 해 봅니다

 

<< 칼럼 추가 하기 >>

 

alter table 테이블명 add 칼럼명 자료형;

alter table emp add comm number;

update emp set comm = 100 where eno = 1000;
update emp set comm = 80 where eno = 1001;
update emp set comm = 50 where eno = 1002 ;
update emp set comm = 70 where eno = 1003;
update emp set comm = 100 where eno = 1004;
update emp set comm = 70 where eno = 1005;
update emp set comm = 90 where eno = 1006;
update emp set comm = 70 where eno = 1007;
update emp set comm = 77 where eno = 1008;
update emp set comm = 50 where eno = 1009;
update emp set comm = 60 where eno = 1010;
update emp set comm = 60 where eno = 1011;
update emp set comm = 100 where eno = 1012;
update emp set comm = 110 where eno = 1013;
update emp set comm = 150 where eno = 1014;
update emp set comm = 200 where eno = 1015;
update emp set comm = 99 where eno = 1016;

 

insert into emp

values(2000, '홍길동', '사원', sysdate, 500, 10, 1002, null);

 

1) 사원의 이름과 직책을 출력

select ename, job from emp;

 

2) 10번 부서에 근무하는 모든 직원의 이름과 급여를 출력

select ename, salary from emp where dno = 10;

2-1) '기획팀'에 근무하는 모든 직원의 이름과 급여 출력

select ename, salary from emp e, dept d where e.dno = d.dno and dname = '기획팀';

select ename, salary from emp where dno = (select dno from dept where dname = '기획팀');

 

3) 모든 직원에게 급여를 10% 인상하려고 한다. 사원번호, 이름, 현재급여,

    증가된 급여(열 이름은 '증가액'), 10% 인상된 급여(열 이름은 '인상급여') 를 사원번호 순으로 출력

select eno, ename, salary,
salary * 0.1 "증가액",
salary * 1.1 "인상급여"
from emp;

-- 칼럼명 애칭은 "" 로 묶어준다

 

4) 이름이 '김'으로 시작하는 모든 직원과 부서번호를 출력

select ename, dno from emp where like '김%';

 

5) 모든 직원의 최대급여, 최소급여, 합계 및 평균급여를 출력합니다

select max(salary), min(salary), sum(salary), avg(salary)
from emp;

 

6) 직책명과 직책별로 동일한 직책의 인원수를 출력합니다

select job, count(*)
from emp
group by job;

 

7) 직원의 최대급여와 최소급여의 차액을 출력

select max(salary) - min(salary) from emp;

 

8) 10번 부서의 구성원수와 직원들의 급여의 합계와 평균을 출력

select count(*), sum(salary), avg(salary)
from emp
where dno = 10;

 

8-1) 종각에 근무하는 구성원수와 직원들의 급여의 합계와 평균을 출력

select count(*), sum(salary), avg(salary)
from emp
where dno in (select dno from dept where dloc = '종각');

 

9) 평균급여가 가장 높은 부서번호를 출력

- 부서번호별 평균 급여 : A

select dno, avg(salary) avg from emp group by dno;

 

- A 에서 평균급여가 가장 높은 값 출력 : B

select max(avg(salary)) from emp group by dno;

 

- A중에서 평균급여가 B에 해당하는 부서번호 출력

select dno

from ()

where avg = ();

select dno
from (select dno, avg(salary) avg from emp group by dno)
where avg = (select max(avg(salary)) from emp group by dno);

 

10) '대리'를 제외하고 각 직책별로 총 급여가 2000이상인 각 직책에 대하여 직책명과 각 직책별 평균급여를 출력

      (단, 평균급여의 내림차순으로 출력)

select job, avg(salary)
from emp
where job <> '대리'
group by job
having sum(salary) >= 2000
order by avg(salary) desc;

 

11) 전체 직원중에 직속상관이 있는 직원의 수를 출력

select count(mgr) from emp;

select count(*) from emp where mgr is not null;

 

12) emp 테이블에서 이름, 급여, 수당, 실수령액을 구하여 실수령액이 높은 순서대로 출력

      (단, comm이 null인 사람은 제외)

select ename, salary, comm, salary + comm 실수령액
from emp
where comm is not null;

-- null인 것은 연산 되지 않는다

select avg(comm) from emp where dno = 10;

select ename, salary, comm, salary + nvl(comm, 0) 실수령액
from emp;

 

select ename, salary, comm, salary + nvl(comm, 대체값) 실수령액
from emp;

select ename, salary, comm, salary + nvl(comm, 
(select avg(comm) from emp e2 where e1.dno = e2.dno)) 실수령액
from emp e1;

 

13) 각 부서별로 같은 직책의 사람 인원수를 구하여 부서번호, 직책이름, 인원수를 출력

select dno, job, count(*)
from emp
group by dno, job;

select (select dname from dept d where d.dno = e.dno) dname ,job, count(*)
from emp e
group by dno, job;

 

14) 사원이 한 명도 없는 부서의 이름을 출력

select dname from dept where dno not in (select dno from emp);

select dname, count(ename) from dept d left outer join emp e on d.dno = e.dno group by dname;

select dname from dept d left outer join emp e on d.dno = e.dno group by dname
having count(ename) = 0;

 

15) 같은 직책을 하는 사람의 수가 2명이상인 직책과 인원수를 출력

select job, count(*) from emp group by job having count(*) >= 2;

 

16) 사원번호가 1002 ~ 1010 이하인 직원의 이름을 출력

select ename from emp where eno between 1002 and 1010;

select ename from emp where eno >= 1002 and eno <= 1010;

 

17) 직원의 이름과 부서명을 출력

select ename, dname from emp e, dept d where e.dno = d.dno;

select ename, (select dname from dept d where d.dno = e.dno) dname
from emp e;

 

18) 직원의 이름과 관리자 이름을 출력

select  e.ename, m.ename
from emp e, emp m
where e.mgr = m.eno;

select ename, (select ename from emp m where e.mgr = m.eno) mgr from emp e;

 

19) '박사원'보다 급여를 많이 받는 사람의 이름을 출력

select ename
from emp
where salary > (select salary from emp where ename = '박사원');

 

20) '박사원'이 일하는 부서번호 혹은 '가산'에 있는 부서번호를 출력

select distinct d.dno 
from emp e, dept d 
where e.dno = d.dno and (ename = '박사원' or dloc = '가산');

select distinct nvl(d.dno, 0) dno
from emp e left outer join dept d 
on e.dno = d.dno and (ename = '박사원' or dloc = '가산');

-- 부적합한 결과 (dno 50이 안나온다)

 

-- minus : 앞에 조회한 결과에서 뒤에 조회한 결과를 빼준다

-- union : 앞 조회 결과에 뒤 조회결과를 합쳐준다

select dno from emp where ename = '박사원'
union
select dno from dept where dloc = '가산';

 

-- 서브쿼리

select dno from dept where dno = () or dno in ();

select dno from dept 
where dno = (select dno from emp where ename = '박사원') or 
dno in (select dno from dept where dloc = '가산');

 

21) '박사원'이 일하는 동일한 근무지에 근무하는 사람의 이름을 출력

select dloc
from emp e, dept d
where e.dno = d.dno and ename = '박사원';

 

-- '종각'에 근무하는 사람의 이름

select ename from
emp e, dept d
where e.dno = d.dno
where dloc = ();

select ename from
emp e, dept d
where e.dno = d.dno
and dloc = (select dloc from emp e, dept d where e.dno = d.dno and ename = '박사원');

 

<< 데이터 정의어 DDL >>

테이블의 구조를 생성, 수정, 삭제하는 명령어

                         create, alter, drop

DDL : auto commit

 

-- 테이블 생성하기

create table 테이블명 (

    속성명 자료형 [제약],

    속성명 자료형 [제약]

    ...

);

 

<< 제약의 종류 >>

 

null  :  null을 허용할 수 있다 (기본값)

not null  :  null을 허용하지 않는다

unique  :  유일해야 한다

default  :  생략하면 설정된 기본값 사용

check  :  만족해야하는 값의 조건 설정(도메인 설정)

primary key  :  주 식별자 설정 (null 허용x, 중복x)

foreign key  :  참조키 설정

 

create table newbook(
    bookid number,
    bookname varchar2(20),
    publisher varchar2(20),
    price number
);

 

-- 제약이 없다면 bookid number null 과 같다

 

<< null >>

 

-- 테이블 추가하기

insert into 테이블명 values(값1, 값2..);

    값의 순서는 테이블 구조와 동일해야 한다

        insert into newbook values(1, '재미있는 자바', '코스타미디어', 30000);

        insert into newbook values(2, '신나는 자바', null, null);

 

insert into 테이블명(칼럼1, 칼럼2..) values(값1, 값2..);

    나열한 칼럼의 순서대로 값을 동일하게 넣어야 한다

        insert into newbook(bookname, bookid, price, publisher) values('신나는 오라클', 3, 35000, '한빛미디어');

        insert into newbook(bookid, bookname) values(4, '신나는 스프링');

 

-- 글자수를 넘으면 오류가 난다

 

commit 작업내용(DML)을 반영

rollback 작업내용(DML)을 취소

DDL과 DCL은 자동 커밋이 되기때문에 rollback 할 수 없다

 

-- 책번호와 가격만 추가하기

insert into newbook(bookid, price) values (5, 32000);

insert into newbook values (6, null, null, 33000);

 

-- 테이블 삭제하기

drop  table 테이블명;

    drop table newbook;

 

<< not null의 제약 >>

값을 생략할 수 없다

 

-- 도서번호, 도서명에 not null 제약으로 테이블 만들고 추가하여 테스트하기

create table newbook(
    bookid number not null,
    bookname varchar2(20) not null,
    publisher varchar2(20),
    price number
);

 

insert into newbook values(null, null, null, 33000);

insert into newbook(bookid, bookname) values (1, null);

-- bookid, bookname 이 null을 허용하지 않기 때문에 오류가 발생

-- not null 제약을 설정했으므로 값을 생략 할 수 없다

 

<< unique 제약 >>

값의 중복을 허용하지 않는다

 

-- 도서번호에 unique 제약을 설정하여 테이블을 생성하고 추가하여 테스트하기

create table newbook(
    bookid number unique,
    bookname varchar2(20),
    publisher varchar2(20),
    price number
);

 

insert into newbook values(1, null, null, null);

insert into newbook values(1, '신나는 자바', null, null);

 

not null  :  null이 아닌지만 체크, 중복 허용

unique  :  중복이 되는지만 체크, null을 허용

primary key  :  not null + unique

 

---

 

14:00 ~

 

정처기 접수주우웅 ~ 대기자 16000 ㅁㅕㅇ ~~

오늘인건 알았는데 14시부터 접수하는건 몰랐다 ㅋ 7.13(토) 접수완료!!!!

 

unique 제약은 유일한 값인지만 판별, null을 허용

 

<< primary key >>

 

not null과 unique를 합쳐놓은 것이다

다른 레코드와 구별하기 위하여 식별자를 설정하기 위하여 사용

create table newbook(
    bookid number primary key,
    bookname varchar2(20) not null,
    publisher varchar2(20) null,
    price number
);

1) 도서번호에 중복된 레코드 추가하기

insert into newbook values(1, '즐거운 자바', '코스타미디어', 30000);
insert into newbook values(1, '즐거운 자바', '코스타미디어', 30000);
-- bookid에 primary key를 설정하여 중복된 값을 추가할 수 없다

2) 도서번호에 null을 설정하여 레코드 추가하기

insert into newbook values(null, '즐거운 자바', '코스타미디어', 30000);
-- bookid에 primary key를 설정하여 null 값을 추가할 수 없다

 

<< default 제약 >>

값을 생략하면 기본값을 설정 해 줍니다

create table newbook(
    bookid number primary key,
    bookname varchar2(20) not null,
    publisher varchar2(20) default '코스타미디어',
    price number
);

insert into newbook values(1, '즐거운 자바', '한빛미디어', 5000);
insert into newbook values(2, '신나는 자바', default, 5000);
insert into newbook(bookid, bookname) values(3, '재밌는 자바');

 

<< check 제약 >>

칼럼의 값에 대하여 조건식을 설정할 때 사용

 

create table newbook(
    bookid number primary key,
    bookname varchar2(20) not null,
    publisher varchar2(20) default '코스타미디어',
    price number check (price >= 1000)
);

insert into newbook values(1, '즐거운 자바', default, 1000);
insert into newbook values(2, '신나는 자바', default, 500);

 

도서의 가격은 10000 ~ 50000로 제한

출판사는 '삼성당', '코스타미디어', '한빛미디어'로 제한

create table newbook(
    bookid number primary key,
    bookname varchar2(20) not null,
    publisher varchar2(20) check (publisher in ('삼성당', '코스타미디어', '한빛미디어')),
    price number check (price between 10000 and 50000)
);

insert into newbook values(1, '재미있는 자바', '삼성당',10000);
insert into newbook values(2, '재미있는 자바', '쿠스타미디어',10000);
insert into newbook values(2, '재미있는 자바', '삼성당',5000);
-- ORA-02290: 체크 제약조건(C##MADANG.SYS_C008375)이 위배되었습니다

insert into newbook(bookid,bookname) values(2, '재미있는 자바');
-- check 제약은 null을 허용한다
-- 값이 들어올 때에 조건을 만족하는지 판별한다

 

다음의 조건을 만족하는 newbook테이블을 만들고 테스트 하기

- 가격은 생략할 수 없고 10000 ~ 50000으로 제한

- 출판사는 생략할 수 없고 기본값 '삼성당', 값의 종류는 '삼성당', '코스타미디어', '대한미디어'로 제한

create table newbook(
    bookid number primary key,
    bookname varchar2(20) not null,
    publisher varchar2(20) default '삼성당'  not null  check (publisher in ('삼성당', '코스타미디어', '한빛미디어')),
    price number not null check (price between 10000 and 50000)
);

insert into newbook(bookid,bookname,price) values(2, '재미있는 자바',15000);

insert into newbook(bookid,bookname) values(2, '재미있는 자바');
-- ORA-01400: NULL을 ("C##MADANG"."NEWBOOK"."PUBLISHER") 안에 삽입할 수 없습니다

-- not null와 default같이 사용하기

-- default가 먼저 와야한다

 

<< foreign key 제약 >>

부모 테이블과 관계를 설정하기 위하여 참조키 설정

create table newbook(
    bookid number primary key,
    bookname varchar2(50),
    price number,
    publisher varchar2(20)
);

create table newcustomer(
    custid number primary key,
    name varchar2(20),
    addr varchar2(50),
    phone varchar2(20)
);

create table neworders(
    orderid number primary key,
    custid number,  -- 참조키 설정 안함
    bookid number,  -- 참조키 설정 안함
    saleprice number,
    orderdate date
);

 

book 테이블의 모든 레코드를 조회하여 newbook에 insert하기

insert into newbook select * from book;

 

customer 테이블의 모든 레코드를 조회하여 newcustomer insert하기

insert into newcustomer select * from customer;

 

이렇게 테이블을 만들면 존재하지 않는 도서나 고객의 주문이 있을 수 있다

    insert into neworders values(1, 10, 20, 10000, sysdate);

이러한 레코드를 애시당초에 insert를 못하도록 하기위하여 참조키를 설정한다

 

- 오늘 학습한 내용에 대하여 요약 정리하고 궁금한 점 질문합니다

- 요약정리가 끝나면 팀별로 프로젝트 관련 회의를 진행합니다

- 숙제) 개별로 오늘 학습한 sql문을 선택하여 관련 프로그래밍도 진행 해 봅니다