create table student
(
sid char(10) primary key,
sname varchar(10),
birth date,
status char(6),
sex char(4),
check(status in ('in','out')),
check(sex in ('M','F'))
);create table course
(
cid char(2) primary key,
cname varchar(20),
time number(3),
leader char(10),
choose char(6),
foreign key (leader) references student (sid),
check (time in ('40','80','120','160')),
check (choose in ('must','may'))
);create table exam
(
eid varchar(5) primary key,
cid char(2),
edate date,
num char(10),
foreign key (cid) references course(cid),
check (num in ('1st','2nd')) //num为第几次考,1st代表1次,2nd代表补考
);create table score
(
sid char(10),
eid varchar(5),
grade number(4,1),
pass char(10),
primary key (sid,eid),
foreign key (sid) references student (sid),
foreign key (eid) references exam (eid),
check (pass in ('yes','no','2nd')) //pass看是通过,不通过,补考通过
);
使用UPDATE语句修改课代表
针对每门课程,将课代表改为本课程成绩最高、且非补考的人
(
sid char(10) primary key,
sname varchar(10),
birth date,
status char(6),
sex char(4),
check(status in ('in','out')),
check(sex in ('M','F'))
);create table course
(
cid char(2) primary key,
cname varchar(20),
time number(3),
leader char(10),
choose char(6),
foreign key (leader) references student (sid),
check (time in ('40','80','120','160')),
check (choose in ('must','may'))
);create table exam
(
eid varchar(5) primary key,
cid char(2),
edate date,
num char(10),
foreign key (cid) references course(cid),
check (num in ('1st','2nd')) //num为第几次考,1st代表1次,2nd代表补考
);create table score
(
sid char(10),
eid varchar(5),
grade number(4,1),
pass char(10),
primary key (sid,eid),
foreign key (sid) references student (sid),
foreign key (eid) references exam (eid),
check (pass in ('yes','no','2nd')) //pass看是通过,不通过,补考通过
);
使用UPDATE语句修改课代表
针对每门课程,将课代表改为本课程成绩最高、且非补考的人
update course set leader =(
select sname from student t1,(
select sid from score where grade =(select max(grade) from score where pass ='yes') where pass ='yes' and rownum=1) t2
where t1.sid=t2.sid)
还有就是如果有两个人同样是最高成绩怎么办??