sql面试题

一、成绩表A(name,grade),班级资料表B(name,age,sex):
1.按grade由高到低的顺序列出全班name，无成绩记0分计；
2.grade大于80的改为80
3.为无成绩的编出60分的成绩记录


oracle：

select B.name,NVL(A.grade,0) 

from A,B 

where B.name = A.name(+) 

order by A.grade desc;

select B.name,

CASE WHEN A.grade BETWEEN 0 AND 80  THEN A.grade ELSE 80 END

from A,B 

where B.name = A.name(+) 

order by A.grade desc;
mysql：

select B.name,IFNULL(A.grade,0) 

from score A right join student B 

on B.name = A.name 

order by A.grade desc;

select B.name,IF(A.grade<80,A.grade,80) 

from score A right join student B 

on B.name = A.name 

order by A.grade desc;