SQL經典練習題48道之三(20-25)

接上篇SQL經典練習題48道之二(11-19)
20、假設使用如下命令建立了一個grade表：
create table grade(low int,upp int,rank char(1));
insert into grade values(90,100,’A’);
insert into grade values(80,89,’B’);
insert into grade values(70,79,’C’);
insert into grade values(60,69,’D’);
insert into grade values(0,59,’E’);
現查詢所有同學的Sno、Cno和rank列。
答：
select sno,cno,degree,rank from score s left join grade g on s.degree>=g.low and s.degree<=g.upp;
21、查詢選修“3-105”課程的成績高於“109”號同學成績的所有同學的記錄。
答：
select * from score where cno=’3-105’ and degree > (select degree from score where sno=’109’ and cno=’3-105’);
22、查詢score中選學一門以上課程的同學中分數為非最高分成績的記錄。
答：
select count(1) from score s1 where degree <(select max(s2.degree) from score s2 where s2.cno=s1.cno) and cno in (select cno from score group by cno having count(*)>1);
23、查詢成績高於學號為“109”、課程號為“3-105”的成績的所有記錄。
答：
select * from score where degree >(select degree from score where sno=’109’ and cno=’3-105’);
24、查詢和學號為108的同學同年出生的所有學生的Sno、Sname和Sbirthday列。
答：
select sno,sname,sbirthday from student where sno!=’108’ and date_format(sbirthday,’%Y’) =( select date_format(sbirthday,’%Y’) from student where sno=’108’);
25、查詢“張旭“教師任課的學生成績。
答：
select * from score where cno in (select cno from course c right join teacher t on t.tno=c.tno where t.tname=’張旭’);