from student --13 select
avg(Grade)avg,max(Grade)max,min(Grade)min from SC where Cno='c02' --14 select student Group by Sdept --15 select
Cno,count(*)renshu,max(Grade)maxGrade from SC group by Cno --16
select Sno,count(Sno)Snum from SC group by Sno
16
Sdept,count(*)renshu from
order by count(Sno) asc --17
select Cno from SC group by Cno having count(Grade)=0 --18
select top 2 with ties Sname,Sage from student
where Sdept='计算机系' order by Sage desc
--实验五 --19 select
Sdept,count(distinct
student.Sno)学生总数,avg(Grade)平均成绩 from student,SC
where student.Sno=SC.Sno and Sdept in(select s1.Sdept from student s1
17
group by s1.Sdept) group by Sdept --20
select avg(Grade)avg,count(*)menshu from SC group by Sno having count(*)>2 --21
select Sno,sum(Grade)sum from SC group by Sno
having sum(Grade)>200 --22 select
type,avg(price)avg1,max(price)max1 from titles
where price>12 and price is not null group by type
18
--23
select type,avg(price)avg1 from titles where royalty=10 group by type --24
select type,sum(price) from titles group by type having count(*)>3 --25
select Sname,Sdept from SC,student where --26 select where --27
19
student.Sno=SC.Sno and
Cno='c02'
Sname,Cno,Grade student.Sno=SC.Sno
from and
SC,student Grade>80
select where
Sname,Ssex,Grade Course.Cno=SC.Cno
from and
student,Course,SC
SC.Sno=student.Sno and Sdept='计算机系' and Ssex='男' and Cname='数据库基础' --28
select student.Sno,Sname,Cno,Grade from student left join SC on student.Sno=SC.Sno --29 select
top
3
with
ties
student.Sno,Sname,Sdept,Grade from SC,student,Course where
Course.Cno=SC.Cno
and
SC.Sno=student.Sno and Cname='数据库基础'
order by Grade desc
20