12. 학사관리 DB 작업 (무결성)

○ 학사관리 테이블 및 시퀀스 생성

1) 테이블 생성
---------------------- 학생테이블
create table tb_student(
   hakno    char(5)     not null         --학번
  ,uname    varchar(20) not null         --이름
  ,email    varchar(20) unique           --이메일
  ,address  varchar(20) not null         --주소
  ,phone    varchar(20)                  --전화번호
  ,regdt    date        default sysdate  --등록일
  ,primary key(hakno)                    --기본키
);

---------------------- 과목테이블
create table tb_gwamok(
   gcode    char(4)      not null        --과목코드 (p:프로그램교과목, d:디자인교과목)
  ,gname    varchar(20)  not null        --과목이름
  ,ghakjum  number(2)    default 1       --학점
  ,regdt    date         default sysdate --등록일
  ,primary key(gcode)                    --기본키
);

---------------------- 수강테이블
create table tb_sugang(
   sno    number(3)  not null   --일련번호
  ,hakno  char(5)    not null   --학번
  ,gcode  char(4)    not null   --과목코드
  ,primary key(sno)             --기본키
);


2) 수강테이블 시퀀스 생성
create sequence sugang_seq;


commit;	--commit의 습관화!!

 

○ 학사관리 행추가

----------------------------- tb_student테이블에 행 추가하기
insert into tb_student(hakno,uname,address,phone,email)
values('g1001','홍길동','서울','111-5558','11@naver.com');

insert into tb_student(hakno,uname,address,phone,email)
values('g1002','홍길동','제주','787-8877','33@daum.net');

insert into tb_student(hakno,uname,address,phone,email)
values('g1003','개나리','서울','554-9632','77@naver.com');

insert into tb_student(hakno,uname,address,phone,email)
values('g1004','홍길동','부산','555-8844','88@daum.net');

insert into tb_student(hakno,uname,address,phone,email)
values('g1005','진달래','서울','544-6996','33@nate.com');

insert into tb_student(hakno,uname,address,phone,email)
values('g1006','개나리','제주','777-1000','66@naver.com');


----------------------------- tb_gwamok테이블에 행 추가하기
insert into tb_gwamok(gcode,gname,ghakjum) values('p001','OOP',3);
insert into tb_gwamok(gcode,gname,ghakjum) values('p002','Oracle',3);
insert into tb_gwamok(gcode,gname,ghakjum) values('p003','JSP',2);
insert into tb_gwamok(gcode,gname,ghakjum) values('d001','HTML',1);
insert into tb_gwamok(gcode,gname,ghakjum) values('d002','포토샵',5);
insert into tb_gwamok(gcode,gname,ghakjum) values('d003','일러스트',3);
insert into tb_gwamok(gcode,gname,ghakjum) values('p004','Python',3);


----------------------------- tb_sugang테이블에 행 추가하기
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','p001');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1002','p002');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1002','p001');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','p003');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','p004');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1005','d001');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1005','d002');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1005','d003');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','d001');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','p002');


commit;	--commit의 습관화!!

 

○ 무결성

    ① 개체무결성

        - 기본키에 속해 있는 속성은 Null 값을 가지지 못한다
        - 기본키는 레코드간에 유일한 식별자이므로 물리적으로 Null 값이 올 수 없다

    ② 참조무결성

        - Foreign Key 제약조건
        - 외래키
        - 동일한 테이블 또는 다른 테이블에서 기본키 또는 고유키를 참조하는 제약 조건
        - 부모: Primary key(기본키) ↔ 자식: Foreign Key(외래키)
        - on delete cascade  부모테이블의 행이 삭제되는 경우 자식테이블의 종속행을 삭제
        - on delete set null 부모테이블의 행이 삭제되는 경우 종속 외래키 값을 null로 변환

예) 수강테이블 제약조건

create table tb_sugang(
   sno    number(3)  not null   
  ,hakno  char(5)    not null   
  ,gcode  char(4)    not null   
  ,primary key(sno)                                 -- sno칼럼 기본키
  ,foreign key(hakno) reference tb_student(hakno)   -- hakno칼럼은 부모테이블(학생)의 hakno 참조
  ,foreign key(gcode) reference tb_gwamok(gcode)    -- gcode칼럼은 부모테이블(과목)의 gcode 참조
                      on delete cascade     -- 부모테이블 행이 수정되면 자식테이블 값도 같이 수정
                      on delete no action   -- 부모테이블 행이 삭제되도 자식테이블은 삭제되지 않음
 );

 

학사관리 관련 테이블(tb_student, tb_gwamok, tb_sugang) 및 입력 데이터

 

● PRACTICE 연습문제

    Q1) 학생테이블에서 지역별 인원수를 인원수순으로 조회하시오

--Q1
select address, count(*)
from tb_student
group by address
order by count(*);

    Q2) 학생테이블에서 동명이인이 각 몇명인지 조회하시오

--Q2
select uname, count(*) as 동명이인수
from tb_student
group by uname
having count(*)>=2
order by count(*) desc;

    Q3) 학생테이블의 학번, 이름, 주소를 조회하시오 (주소는 영문으로 출력)

--Q3
select hakno, uname, case when address='서울' then 'Seoul'
                          when address='제주' then 'Jeju'
                          when address='부산' then 'Busan'
                     end as eng_adress
from tb_student;

    Q4) 학생테이블에서 주소별 인원수가 3명미만 행을 조회하시오

--Q4
select address, count(*)
from tb_student
group by address
having count(*)<3;

    Q5) 과목테이블에서 프로그램 교과목만 조회하시오

--Q5
select *
from tb_gwamok
where gcode like 'p%';

    Q6) 과목테이블에서 디자인 교과목중에서 3학점만 조회하시오

--Q6
select gcode, gname, ghakjum
from tb_gwamok
where gcode like 'd%' and ghakjum=3;

    Q7) 과목테이블에서 프로그램 교과목의 학점 평균보다 낮은 프로그램 교과목을 조회하시오

--Q7
select *
from tb_gwamok        
where ghakjum < (select avg(ghakjum) 
				 from tb_gwamok 
                 where gcode like 'p%')
      and gcode like 'p%';