4-3

3) 정규화

-(각각의 테이블에 insert값을 조금 더 추가했음)

drop  table enrol;
drop  table student;
drop  table course;


create table student(
 sno varchar2(3) ,
 sname nvarchar2(10),
 year varchar2(10),
 dept nvarchar2(10) ,
 constraint   student_sno_pk   primary  key(sno));


create table course(
 cno varchar2(5) primary key,
 cname nvarchar2(10),
 credit varchar2(3),
 dept nvarchar2(10),
 prname nvarchar2(10));


create table enrol(
 sno varchar2(3) ,
 cno varchar2(5) ,
 grade varchar2(3),
 midterm int,
 final int ,
 constraint enrol_combo_pk primary key(sno, cno));


insert into student(sno,sname,year,dept)
values('100','나연묵','4','컴퓨터');
insert into student(sno,sname,year,dept)
values('200','이찬영','3','전기');
insert into student(sno,sname,year,dept)
values('300','정기태','1','컴퓨터');
insert into student(sno,sname,year,dept)
values('400','송병호','4','컴퓨터');
insert into student(sno,sname,year,dept)
values('500','박종화','2','산공');

insert into student(sno,sname,year,dept)
values('600','하늘이','4','만화과');
insert into student(sno,sname,year,dept)
values('700','영심이','4','만화과');

insert into course(cno,cname,credit,dept,prname)
values('C123','프로그래밍','3','컴퓨터','김성기');
insert into course(cno,cname,credit,dept,prname)
values('C312','자료구조','3','컴퓨터','황수찬');
insert into course(cno,cname,credit,dept,prname)
values('C324','화일처리','3','컴퓨터','이규철');
insert into course(cno,cname,credit,dept,prname)
values('C413','데이터베이스','3','컴퓨터','이성호');
insert into course(cno,cname,credit,dept,prname)
values('E412','반도체','3','전자','홍봉희');

insert into course(cno,cname,credit,dept,prname)
values('F111','자동차정비','2','자동차','최동운');
insert into course(cno,cname,credit,dept,prname)
values('F222','드론프로그램','2','드론','이광재');
insert into course(cno,cname,credit,dept,prname)
values('F333','AI','2','인공지능','김박사');

insert into enrol(sno,cno,grade,midterm,final)
values('100','C413','A',90,95);
insert into enrol(sno,cno,grade,midterm,final)
values('100','E412','A',95,95);
insert into enrol(sno,cno,grade,midterm,final)
values('200','C123','B',85,80);
insert into enrol(sno,cno,grade,midterm,final)
values('300','C312','A',90,95);
insert into enrol(sno,cno,grade,midterm,final)
values('300','C324','C',75,75);
insert into enrol(sno,cno,grade,midterm,final)
values('300','C413','A',95,90);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C312','A',90,95);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C324','A',95,90);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C413','B',80,85);
insert into enrol(sno,cno,grade,midterm,final)
values('400','E412','C',65,75);
insert into enrol(sno,cno,grade,midterm,final)
values('500','C312','B',85,80);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C123','A',90,90);

insert into enrol(sno,cno,grade,midterm,final)
values('901','F111','A',90,90);
insert into enrol(sno,cno,grade,midterm,final)
values('902','F111','A',90,90);
insert into enrol(sno,cno,grade,midterm,final)
values('903','F222','A',90,90);


select * from student;
select * from course;
select * from enrol;
​

select * from student s 
join enrol e
on s.sno=e.sno
order by s.sno asc;
--이너조인 / 두 테이블에 다 있는 값만

select * from student s 
left join enrol e
on s.sno=e.sno
order by s.sno asc;
--student테이블을 기준으로 enrol에 없는 것은 null

select * from student s 
right join enrol e
on s.sno=e.sno
order by s.sno asc;
--enrol테이블을 기준으로 student에 없는 것은 null

select * from student s 
full join enrol e
on s.sno=e.sno
order by s.sno asc;

select * from student s 
full join enrol e
on s.sno=e.sno
full join course c
on e.cno=c.cno
order by s.sno asc;
--정규화(모델링) : 테이블 모델링할 때 중복되는 것들을 제거하는 것 (예.sno)

 

이제 주소 테이블도 포함해볼게요 

create table juso(
sno varchar2(3) not null,
email varchar2(50),
addr nvarchar2(100),
constraint juso_sno_fk foreign key (sno) references student(sno)
);

--student 테이블에서 sno를 참조. 앞에 부분은 fk이름 수동으로 정해주는 거라 안해도됨

select * from student s 
full join enrol e
on s.sno=e.sno
full join course c
on e.cno=c.cno
full join juso j
on s.sno=j.sno
order by s.sno asc;

insert into juso (sno,email,addr)
values ('100','apr49@naver.com','서울 관악구');

insert into juso (sno,email,addr)
values ('300','apr39@naver.com','부산 해운대');

 

4) update 연습

1. 학번이 100번이고  과목번호가 C413 인 학생에 대해서 
중간고사 성적인 5점 업데이트( +5) 해 주세요.!!  

 update enrol set midterm=midterm+5
 where sno='100' and cno='C413';
 
  update enrol set midterm=midterm-5
 where sno='100' and cno='C413';

2.중간고사 성적이 80점 이하인 학생에 대해서 5점을 더해 주세요!!
  
  update enrol set midterm=midterm+5
  where midterm<=80;

3.전체 학생에 대해서 중간고사 성적을 1점 더해주세요.

  update enrol set midterm=midterm+1;
  
  
select * from enrol ;

 

5) 서브쿼리 이용

 

1. 컴퓨터과 학생들의 점수를 중간, 기말 점수에서 -1점씩 진행하시오

 update enrol set midterm=midterm-1 , final=final-1
 where sno in( 
 select sno from student
 where dept='컴퓨터'
 );
--서브쿼리 이용

--결과를 가지고 다른 테이블의 결과를 도출할 때

 

delete from enrol 
 where sno in( 
 select sno from student
 where dept='컴퓨터'
 );