SQL经典练习题48道之七(41-48)

接上篇 SQL经典练习题48道之六(36-40)
41、查询Student表中最大和最小的Sbirthday日期值。
答：
select max(sbirthday),min(sbirthday) from student;
42、以班号和年龄从大到小的顺序查询Student表中的全部记录。
答：
select * from student order by classnum desc,sbirthday asc;
43、查询“男”教师及其所上的课程。
答：
select * from course where tno in(select tno from teacher where tsex=’男’);
44、查询最高分同学的Sno、Cno和Degree列。
答：
select sno,cno,degree from score where degree=(select MAX(degree) from score);
45、查询和“李军”同性别的所有同学的Sname.
答：
select sname from student where ssex=(select ssex from student where sname=’李军’);
46、查询和“李军”同性别并同班的同学Sname.
答：
select sname from student where ssex=(select ssex from student where sname=’李军’) and classnum=(select classnum from student where sname=’李军’);
47、查询所有选修“计算机导论”课程的“男”同学的成绩表
答：
select * from score where cno=(select cno from course where cname=’计算机导论’) and sno in (select sno from student where ssex=’男’);
48、查询最高分同学的Sname、Cno和Degree列。
答：
select sname,cno,degree from (select sno,cno,degree from score where degree=(select MAX(degree) from score) )s1 left join student s2 on s1.sno=s2.sno;