|
create table student
(sno integer not null,
sname char(8),
year integer,
dept char(6),
primary key(sno));
create table course
(cno char(4) not null,
cname char(12),
credit integer,
dept char(6),
pname char(8),
primary key(cno));
create table enrol
(sno integer not null,
cno char(4) not null,
grade char(1),
midterm integer,
final integer,
primary key(sno,cno),
foreign key(sno) references student
on delete cascade
on updete cascade,
foreign key(cno) references course
on delete cascade
on updete cascade,
check(midterm >= 0 and midterm <=100),
check(final >=0 and midterm <= 100) );
|
|
insert
into student
values(100,'나연묵',4,'컴퓨터');
insert
into student
values(200,'이찬영',3,'전기');
insert
into student
values(300,'정기태',1,'컴퓨터');
insert
into student
values(400,'송병호',4,'컴퓨터');
insert
into student
values(500,'박종화',2,'산공');
insert
into course
values('c123','프로그래밍',3,'컴퓨터','김성기');
insert
into course
values('c312','자료구조',3,'컴퓨터','황수찬');
insert
into course
values('c324','화일처리',3,'컴퓨터','이규철');
insert
into course
values('c413','데이타베이스',3,'컴퓨터','이성호');
insert
into course
values('e412','반도체',3,'전자','홍봉희');
insert
into enrol
values(100,'c413','a',90,95);
insert
into enrol
values(100,'e412','a',95,95);
insert
into enrol
values(200,'c123','b',85,80);
insert
into enrol
values(300,'c312','a',90,95);
insert
into enrol
values(300,'c324','c',75,75);
insert
into enrol
values(300,'c413','a',95,90);
insert
into enrol
values(400,'c312','a',90,95);
insert
into enrol
values(400,'c324','a',95,90);
insert
into enrol
values(400,'c413','b',80,85);
insert
into enrol
values(400,'e412','c',65,75);
insert
into enrol
values(500,'c312','b',85,80);
|
