10.21

create table student(
sno char(8) not null,
sname varchar(20) ,
sex char(2) ,
sdept varchar(20) ,
primary key(sno)
);
create table course(
cno char(6) not null,
cname varchar(20) ,
primary key(cno)
);
create table sc(
sno char(8) not null,
cno char(6) not null,
grade int not null,
primary key(sno,cno),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno),
);
insert into student (sno,sname,sex,sdept) values('20050101','王飞','男','计算机系');
insert into student (sno,sname,sex,sdept) values('20050102','李丽','女','信息系');
insert into course (cno,cname) values('01','数据结构');
insert into course(cno,cname) values('02','数据库原理');
insert into sc(sno,cno,grade) values('20050101','01','70');
insert into sc(sno,cno,grade) values('20050102','02','90');
create table savg(
cno char(6) not null,
savg int ,
foreign key(cno) references course(cno)
)
INSERT INTO savg (cno,savg) SELECT cno, AVG(grade) as savg
FROM sc
GROUP BY cno;
--修改 sc 表中 sno 为 20050102、cno 为 02 的记录的 grade 属性值为 85，然后将
--该条记录删除。将此两条 SQL 语句写入实验报告。
--13、修改‘数据库原理’课程的所有学生成绩为 0。将此条 SQL 语句写入实验报告。
--14、删除‘李丽’的所有选课情况。将此条 SQL 语句写入实验报告。
--15、删除数据库中的三个表中的所有数据，将所用 SQL 语句写入实验报告。
--16、删除数据库中的三个表结构。将所用 SQL 语句写入实验报告。
--17、删除数据库，将所用语句写入实验报告。
update sc set grade=85 where sno='20050102' and cno='02';
delete from sc where sno='20050102' and cno='02';
update sc set grade=0 where cno in(select cno from course where cname='数据库原理');
delete from sc where sno in (select sno from student where sname='李丽');
select cno from sc where sno in (select sno from student where sname='李丽');
delete from savg;
delete from student;
delete from course;
delete from sc;
delete student;
delete course;
delete sc;
drop database stumanage;