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;