문제

1. VIEW_EMP1에서 고용일이 2000년 이후이고 급여가 350만원 이상인 사원을 조회하시오.

2. VIEW_EMP2에서 업무(job)가 특수영업인 사원을 조회하시오.

3. VIEW_EMP3에서 생일이 1999년도인 사원을 조회하시오.

4. VIEW_EMP1에서 고용인원수, 평균급여, 최저급여, 최고급여를 조회하시오.

5. VIEW_EMP2에서 업무(job)가 ‘품’이들어간 사원을 모두 조회하시오.

6. VIEW_EMP3에서 1995~1999년 사이에 태어난 사원수와 총예산을 표시하시오.

힌트

제공되는 테이블과 데이터

-- 테이블 생성
create table emp (
        empid char(6) not null,
        name varchar2(20) not null,
        dept varchar2(20) not null,
        hire_date date,
        birthday varchar2(20),
        address varchar2(50),
        job varchar2(20),
        salary number(10),
        constraint pk_emp primary key(empid)
    );

-- 테이블 조회
select * from emp;

-- 데이터 삽입
INSERT INTO EMP(EMPID,NAME,DEPT,HIRE_DATE,BIRTHDAY, ADDRESS, JOB, SALARY)
        VALUES('1006','김민수','개발부',SYSDATE,'1985.10.12','대전시 서구 갈마로29', 'ERP프로그램', 350);
INSERT INTO EMP(EMPID,NAME,DEPT,HIRE_DATE,BIRTHDAY, ADDRESS, JOB, SALARY)
        VALUES('1002','곽희준','영업부',SYSDATE,'1985.10.12','서울시 동작구 노량진로100', '특수영업', 400);
INSERT INTO EMP(EMPID,NAME,DEPT,HIRE_DATE,BIRTHDAY, ADDRESS, JOB, SALARY)
        VALUES('1003','김동준','생산부',SYSDATE,'1985.10.12','부산시 남구 해운대로15', '품질관리', 300);
INSERT INTO EMP(EMPID,NAME,DEPT,HIRE_DATE,BIRTHDAY, ADDRESS, JOB, SALARY)
        VALUES('1004','성재규','인사부',SYSDATE,'1999.08.10','대구시 중구  달성로 300', '급여관리', 450);
INSERT INTO EMP(EMPID,NAME,DEPT,HIRE_DATE,BIRTHDAY, ADDRESS, JOB, SALARY)
        VALUES('1005','박성범','구매부',SYSDATE,'1985.10.12','대전시 중구 계룡로800', '수입자재', 320);

1번 문제

-- 1. VIEW_EMP1에서 고용일이 2000년 이후이고 급여가 350만원 이상인 사원을 조회하시오.
select * from view_emp1 
    where 
        HIRE_DATE > '00/12/31'
    and
        SALARY >= 350;

2번 문제

-- 2. VIEW_EMP2에서 업무(job)가 특수영업인 사원을 조회하시오.
select * from view_emp2
    where
        JOB = '특수영업';

3번 문제

-- 3. VIEW_EMP3에서 생일이 1999년도인 사원을 조회하시오.
select * from view_emp3
    where
        SUBSTR(BIRTHDAY, 1, 4) = '1999';

substr(속성명, 시작위치, 길이)

4번 문제

-- 4. VIEW_EMP1에서 고용인원수, 평균급여, 최저급여, 최고급여를 조회하시오.

-- 고용인원수
select count(*) from view_emp1;

-- 평균급여
select avg(SALARY) from view_emp1;
    
-- 최저급여
select min(SALARY) from view_emp1;

-- 최고급여
select max(SALARY) from view_emp1;

select count(*) 고용인원수, avg(SALARY) 평균급여, min(SALARY) 최저급여, max(SALARY) 최고급여 from view_emp1;

select count(*) 고용인원수, round(avg(SALARY)) 평균급여, min(SALARY) 최저급여, max(SALARY) 최고급여 from view_emp1;
-- round를 사용하지 않아도 괜찮지만 소수점을 위해 round를 사용해야 함

avg(속성명) : 평균

min(속성명) : 최저

max(속성명) : 최고

5번 문제

-- 5. VIEW_EMP2에서 업무(job)가 ‘품’이들어간 사원을 모두 조회하시오.
select * from view_emp2
    where
        job like '%품%';

속성명 like '%문자%' ;

와일드 카드(%)가 뒤에만 있다면 첫글자는 문자 여야 한다.

6번 문제

-- 6. VIEW_EMP3에서 1995~1999년 사이에 태어난 사원수와 총예산을 표시하시오.
select count(*) 인원수, (count(*)*100) 총예산
    from view_emp3
    where
        substr(BIRTHDAY, 1, 4) between 1995 and 1999;
        
select count(*) 인원수, (count(*)*100) 총예산
    from view_emp3
    where
        BIRTHDAY >= '1995.01.01' and BIRTHDAY <= '1999.12.31';

속성명 between 비교값1 and 비교값2 ;

키(Key)

: 데이터 베이스에서 조건에 만족하는 튜플을 찾거나 순서대로 정렬할 때 다른 튜플들과 구별할 수 있는 유일한 기준이 되는 속성(Attribute)

- 후보키(Candidate key)

: 튜플을 구별하는데 기준이 되는 컬럼들의 집합

각 튜플을 고유하게 식별할 수 있다. (유일성)

기본키(PK), 대체키(AK)로 구분된다.

- 기본키(Primary key) = PK

: 튜플을 식별하는데 기준으로 사용하는 키.

적어도 하나의 기본키가 존재. (=> 기본이 NOT NULL)

NOT NULL(null 값이면 안 됨), NODUPLICATE (반드시 유일)

기본 키로 선택되기 위해선 대표할 수 있고, 짧으며, 자주 사용되는 컬럼이어야 한다.

- 대체키(Alternate key)

: 후보키 중 기본키로 선택되지 않은 키.

- 복합키(Composite key)

: 두 개 이상의 컬럼을 합쳐 후보키 역할을 하는 키

- 외래키(Foreign key) = FK

: 테이블 사이의 관계를 알려주는 키

두 테이블 사이에 존재하며 관계를 맺는 걸 뜻한다. 각각의 테이블을 부모 테이블, 자식 테이블이라 부른다.

식별관계, 비식별관계로 나뉘며 각각의 뜻은 아래와 같다.

식별관계 : PK를 PK로 참조함

비식별관계 : 외래키를 이용할 때 PK를 참조함

외래키에 의한 참조관계에 있을 때 데이터 불일치가 발생했을 때

1. 제한(restrict) : 삭제할 수 없다는 안내를 해줌

2. 연쇄(cascade) : 연계된 튜플도 함께 삭제하거나 바뀐 내용을 Update 함

3. 널 값으로 대체(nullify) : 삭제 후 튜플과 연계된 값을 null 값으로 대체

=> 데이터 불일치가 발생하지 않게 하기 위해선, 자식 테이블 먼저 수정 후 부모 테이블을 수정해야함.

데이터 무결성 (= 참조 무결성 제약조건)

참조 무결성 제약조건이란 데이터의 무결성을 보장하고 데이터베이스의 상태를 일관되게 유지하는 것을 말한다.

+ 데이터 무결성이란 정확성(중복, 누락x), 일관성(원인과 결과 연속적으로 보장), 유효성이 유지되는 것을 말한다.

-참조 무결성

: 자식테이블에 데이터를 입력하거나 수정할 때 부모테이블을 검증
  부모테이블의 데이터를 수정하거나 삭제할 때 자식테이블을 검증

-개체 무결성

: 기본키는 오직 하나의 값, null을 가져선 안 됨

- 도메인 무결성

: 속성들의 값은 정의된 도메인에 속한 값이어야 함

ex) 성별을 입력할 때는 '남', '여'를 제외한 데이터는 제한되어야 한다.

뷰의 필요성

뷰란 데이터를 바라볼 수 있도록 해주는 수단을 말한다.

필요한 컬럼만 뽑아 테이블을 통해 볼 수 있다.

=> 가상 테이블

관리하기 좋고, 보안에 강하다.

뷰(view) 생성 전 필수 작업

1. 테이블 생성 및 데이터 삽입한다.

insert into emp
    values ('1006', '김민수', '개발부', sysdate, '2015.01.12', '대전시 서구 갈마로29', 'ERP프로그램', 350);
    
insert into emp
    values ('1002', '곽희준', '영업부', sysdate, '2018.11.09', '서울시 동작구 노량진로100', '특수영업', 400);
    
insert into emp
    values ('1003', '김동준', '생산부', sysdate, '2012.12.03', '부산시 남구 해운대로15', '품질관리', 300);
    
insert into emp
    values ('1004', '성재규', '인사부', sysdate, '2000.02.23', '대구시 중구 달성로300', '급여관리', 450);
    
insert into emp
    values ('1005', '박성범', '구매부', sysdate, '1994.06.12', '대전시 중구 계룡로800', '수입자재', 320);

2. 데이터를 수정한다.

update 테이블 set 변경할 속성 = '변경할 값' where 속성 = '데이터' ;

update 테이블 set 변경할 값 where 조건 ;

update emp set birthday = '1994.06.12' where name='박성범';

3. 권한 부여한다.

https://forest-of-coding.tistory.com/6

위의 뷰 권한 부여 참조하여 cmd에서 권한을 부여해야 한다.

뷰(View) 생성

create view 뷰 이름
as
select 속성명1, 속성명2, 속성명3, 속성명4, 속성명5
from 테이블명;

총 3개의 View를 생성한다.

create view view_emp1
as
select empid, name, dept, hire_date, salary
from emp;

create view view_emp2
as
select empid, name, dept, job
from emp;

create view view_emp3
as
select empid, name, birthday, address
from emp;

뷰 조회

기존의 테이블 조회와 같은 방식이다.

select * from view_emp1;

select * from view_emp2;

select * from view_emp3;

문제

1. 사원번호가 102인 사원을 조회하시오.

2. 급여가 250만원인 사원을 조회하시오.

3. 현재 사원수를 조회하시오.

4. 급여가 300만원 이상인 사원을 조회하시오.

전체 조회

select * from employer; -- 전체 조회

사원번호가 102인 사원 조회

select * from employer where EMPNO='102';
-- 사원번호가 102인 사원 조회 / 숫자 입력 시 작은 따옴표 쓰지 않아도 됨, 문자일 시 다름

급여가 250만원인 사원 조회

select * from employer where SALARY='250'; -- 급여가 250만원인 사원 조회

현재 사원수 조회

select count(*) from employer; -- 현재 사원 수 조회

급여가 300만원 이상인 사원 조회

select * from employer where SALARY >= '300'; -- 급여가 300만원 이상인 사원 조회

번외 : 급여가 500만원 이하, 300만원 이상인 사원 조회

select * from employer where SALARY between 300 and 500; -- 급여가 300만원 이상, 500만원 이하인 사원 조회