sql作业(中)
25 select s.sno,s.sname,s.ssex,s.sbirthday,s.class,t.cno,t.degree from student s inner join score t on s.sno=t.sno order by sno,degree
26 select distinct(cno) from score where degree >85
27 select s.sno,s.cno,s.degree,c.cname,c.tno from score s inner join course c on c.cno=s.cno and c.cname='计算机导论'
28 select tname,prof from teacher where depart='计算机系' and prof not in (select prof from teacher where depart='电子工程系')
29 select s.sno,s.cno,s.degree from score s where s.cno='3-105' and s.degree>(select min(degree) from score where cno='3-245') order by s.degree desc
30 select s.sno,s.cno,s.degree from score s where s.cno='3-105' and s.degree>(select max(degree) from score where cno='3-245')
31 select s.sname,s.ssex,s.sbirthday,t.tname,t.tsex,t.tbirthday from student s inner join score x on x.sno=s.sno left join course c on c.cno=x.cno full join teacher t on t.tno=c.tno
32 select t.tname,t.tsex,t.tbirthday from teacher t where t.tsex='女' union select s.sname,s.ssex,s.sbirthday from student s where s.ssex='女'
33 select s.* from score s where degree <(select avg(ss.degree) from score ss where ss.cno=s.cno)
34 select t.tname,t.depart from teacher t inner join course c on c.tno=t.tno
35 select t.tname,t.depart from teacher t where t.tno not in(select tno from score)
36 select class from student where ssex='男' group by class having count(ssex)>1
37 select * from student s where s.sname not like '王%'