SQL經典練習題48道之五(31-35)

接上篇 SQL經典練習題48道之四(25-30)
31、查詢選修編號為“3-105“課程且成績至少高於選修編號為“3-245”的同學的Cno、Sno和Degree,並按Degree從高到低次序排序。
答：
select cno,sno,degree from score where cno=’3-105’ and degree>=(select MAX(degree) from score where cno=’3-245’) order by degree desc;
32、查詢選修編號為“3-105”且成績高於選修編號為“3-245”課程的同學的Cname、Sname和Degree.
答：
select cname,sname,degree from (select cno,sno,degree from score where cno=’3-105’ and degree>(select MAX(degree) from score where cno=’3-245’)) s1 left join course c on s1.cno=c.cno left join student s2 on s1.sno =s2.sno;
33、查詢所有教師和同學的name、sex和birthday.
答：
select sname name,ssex sex,sbirthday birthday from student union select tname name,tsex sex,tbirthday birthday from teacher;
34、查詢所有“女”教師和“女”同學的name、sex和birthday.
答：
select * from (select sname name,ssex sex,sbirthday birthday from student union select tname name,tsex sex,tbirthday birthday from teacher)s where s.sex=’女’;
35、查詢成績比該課程平均成績低的同學的成績表
答：
select s1.* from score s1 inner join (select avg(degree) avgd,cno from score group by cno) s2 on s1.cno=s2.cno and s1.degree