期末复习数据

1.删除顾客表(customers)中没有下过订单的顾客信息
delete
from customers
where CustomerID not in(
select CustomerID
from orders
)

2.删除特定城市的顾客信息
delete from customers
where City=‘London’

3.填写并计算学生获得的总学分，并填写在stu表中的totalcredit字段
其中，总学分为每个学生通过选修课程的学分数总和。
注意：只有在60分以上的选课成绩才能获得该门课程的学分数，每门课程的学分数在cou表中credit字段

update stu,(select sno,sum(credit) SUM
from(
select stu.sno sno,
case when sc.grade>60 then credit else NULL end credit
from stu left outer join sc on stu.sno = sc.sno
left outer join cou on stu.cno=cou.cno
group by stu.sno,credit,grade) a
group by sno) B
set stu.totalcredit = B.SUM
where stu.sno = B.sno

4.查询选修’C语言’课程的学生
select stu.sname as 姓名,sc.grade as 成绩
from sc
join cou on
cou.cno = sc.cno
join stu on
stu.sno=sc.sno
and cou.cname=N’C语言’
order by 成绩