33、 查询成绩比该课程平均成绩低的同学的成绩表。
select * from score a where a.degree between 0 and(
select avg(degree) from score b where a.cno=b.cno )
34、 查询所有任课教师的Tname和Depart.
select tname,depart from teacher where tno in(
select tno from course )
35 、 查询所有未讲课的教师的Tname和Depart.
select tname,depart from teacher where tno not in(
select tno from course )
36、查询至少有2名男生的班号。
select sclass from student where ssex = '男' group by sclass having count(sno)>=2
37、查询Student表中不姓“王”的同学记录。
select * from student where sname not like '王%'
38、查询Student表中每个学生的姓名和年龄。
39、查询Student表中最大和最小的Sbirthday日期值。
select max(t.sbirthday),min(t.sbirthday) from student t
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from student order by sclass desc , sbirthday asc
41、查询“男”教师及其所上的课程。
select tname , cname from teacher t ,course c where tsex = '男'and t.tno=c.tno
42、查询最高分同学的Sno、Cno和Degree列。
select * from score where degree =(
select max(degree) from score)
43、查询和“李军”同性别的所有同学的Sname.
select sname from student where ssex = (
select ssex from student where sname ='李军')
44、查询和“李军”同性别并同班的同学Sname.
select sname from student where ssex = (
select ssex from student where sname ='李军')
and sclass =(
select sclass from student where sname ='李军')
45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
select * from score where sno in (
select sno from student where ssex = '男')
and cno in (
select cno from course where cname = '计算机导论')