数据库课堂作业

create table ypxx(
yno char(10) primary key,
yname char(30),
ysort char(8),
yrule char(6),
ysupply char(20)
);

create table gyb(
sno char(10),
yno char(10),
gynum int,
gyrq date,
gyje int
);

alter table ypxx add price decimal(5,1) not null default=0, bz text

alter table gyb alter gyje decimal(7,1) not null;

create view Vst
as select sname, cname, grade, password1 from course, mm, sc, student
where course.cno=sc.cno and sc.sno=student.sno and student.sno=mm.sno and sdept='计算机系'

create view grade_view as
select sc.sno, avg(grade) as pjgrade
from sc, student
where sc.sno=student.sno and sdept in('计算机系', '管理系')
group by sc.sno having avg(grade)>=60

create view grade_gy_view as
select grade_view.sno, sname, sdept from grade_view, student where grade_view.sno=student.sno and pjgrade>80

create index idx_gy on gyb(sno,gyrq desc)

alter table ypxx drop bz;

drop index idx_gy on gyb