sql数据库系统原理上机实验(综合版)(5)

select cno,AVG(grade)

from sc -------------------14 group by cno

select cno,COUNT(sno)

from sc ------------------------15 group by cno

order by cno,COUNT(sno) desc

select sname

from student -------------------16 where sno>(select sno from student

where sname='刘晨')and sage<(select sage from student where sname='刘晨')

select x.sname ----------------16法二 from student x,student y

where x.sno>y.sno and x.sage

21

select distinct x.sname,x.sage

from student x,student y ------17

where x.ssex='男' and x.sage>y.sage and y.ssex='女'

-----实验四sql 的数据更新

insert into student(sno,sname,sage)values('95030','李莉','18') -----1

insert into sc(sno,cno)values('95030','1') --------2

update student1 ---------------3 set sage='20' where sdept='IS'

update sc1 --------------------4 set grade='0'

where sno in(select sno from student1 where sdept='MA')

update sc -----------------5

22

set grade=grade+5

where sno in(select student.sno from student,sc where ssex='女'

group by student.sno,grade

having grade

update sc

set grade=grade*1.05

where cno='2' and grade<75 ----------------6 update sc

set grade=grade*1.04 where cno='2' and grade>75

delete from student1 -----------7 where sno='95030'

delete from sc1

where grade is null--------------8

delete from sc1 where sno in(select sno

23

from student1 -------------------9 where sname='张娜')

delete from sc1 ----------10 where grade<60

delete from sc1 where sno in(select sno from student1 where sdept='MA')

delete from course1 where cno not in(select cno from sc1,course1

where sc1.cno=course1.cno)

create table stu(sno char(8),sname char(8),ssex char(4)) insert into stu(sno,sname,ssex) select sno,sname,ssex from student

where sno in(select sno from sc

24

where sno not in(select sno from sc

where grade is null) group by sno

having min(grade)>=80)

create table sdeptgrade(sdept char(4),avggrade char(2)) insert into sdeptgrade(sdept,avggrade) select sdept,AVG(grade) from sc,student group by sdept

25