一、成绩表A(name,grade),班级资料表B(name,age,sex):
1.按grade由高到低的顺序列出全班name,无成绩记0分计;
2.grade大于80的改为80
3.为无成绩的编出60分的成绩记录
oracle:
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:
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;