1012-----SQL作业-----上

1 select t.sname,t.ssex,t.class from STUDENT t

2 select distinct(depart) from teacher t

3 select * from student

4 select * from score r where r.degree between 60 and 80

5 select * from score t where t.degree=85 or t.degree=86 or t.degree=88

6 select * from student t where class='95031' or t.ssex='女'

7 select * from student t order by class desc

8 select * from score t order by t.cno asc,t.degree desc

9 select count(*) from student where class='95031'

10 select * from score where degree=(select max(degree) from score)

11 select avg(degree) from score group by cno

12 select avg(degree),cno from score where cno like '3%'group by cno having count(cno)>=5

13 select sno from score where degree between 70 and 90

14 select c.sname,t.cno,t.degree from student c,score t where c.sno=t.sno

15 select a.sno,b.cname,c.degree from student a,course b,score c where b.cno=c.cno and c.sno=a.sno

16 select a.sname,b.cname,c.degree from student a,course b, score c where a.sno=c.sno and b.cno=c.cno

17 select avg(degree) from student a,score b where a.class='95033' and b.sno=a.sno

18 select a.sno,a.cno,b.rank from score a,grade b where a.degree between b.low and b.upp

19 select a.* from score a,score b where a.cno='3-105'and a.degree>b.degree and b.sno='109' and b.cno='3-105'

20 select sno from SCORE t where t.degree<(select max(degree) from score) group by sno having count(cno)>1

21 select * from score where cno='3-105' and degree>(select max(degree) from score where sno = '109' )

22 select sno,sname,sbirthday from student a where a.sbirthday= (select sbirthday from student where sno='109')

23 select sno,cno,degree from score where cno = (select a.cno from course a,teacher b where b.tname='张旭' and a.tno=b.tno)

24 select b.cno from teacher a,score b, course c where a.tno=c.tno and c.cno=b.cno group by b.cno having count(b.cno)>5