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 成绩 desc

5.修改订单详情表(orderdetails)中订货数量最少的产品，将其单价下调一元

update orderdetails
set unitprice = unitprice - 1
where productid in(
select t.productid
from(
select productid
from orderdetails
order by quantity
limit 1
) t
);

6.修改特定职工的订单运费
修改订单表中（orders）中员工编号（EmployeeID）为3和4员工的订单，将每单运费(Freight)减少5%
update orders
set Freight = Freight*0.95
where EmployeeID in(3,4);

7.新增订单统计信息
统计订单表(orders)中顾客的订单数量，并插入results表中
insert into results
select orders.CustomerID,count(orders.CustomerID) OrderCount
from orders
group by CustomerID;

8.修改学生选课成绩
在sc表中修改’C001’课程的成绩，若成绩小于70则提高50%，若成绩在70分以上(含70分)则提高4%
update
sc
set
grade = case
when sc.grade < 70 then grade * 1.05
when sc.grade >= 70 then grade * 1.04
else grade
end
where
cno = ‘C001’;

9.添加成绩等级rank字段
填写sc表中成绩等级rank字段，按60分以下为E，60-69为D，70-79为C，80-89为B，90以上为A
，其他情况视为NULL
update
sc
set
rank = case
when sc.grade < 60 then ‘E’
when sc.grade >= 60 and sc.grade < 70 then ‘D’
when sc.grade >= 70 and sc.grade < 80 then ‘C’
when sc.grade >= 80 and sc.grade < 90 then ‘B’
when sc.grade >= 90 then ‘A’
else null
end

10.删除成绩为空的学生的选课记录
delete from
sc
where
grade is NULL；

11.修改女生成绩
要求编写update语句，把所有低于75分的女生成绩提高5%
update sc
set grade=grade*(1+0.05)
where grade < 75 and sno in(
select sno
from stu
where sex=‘0’
)

12.将低于总平均成绩的女生成绩提高5分
update sc
set grade = grade + 5
where sno in
(select sno from student where ssex = ‘女’）
and grade <
(select avg(grade) from sc);

13.批量插入学生记录
将stu表中的软件工程专业(专业号mno为‘02’)的学生记录批量插入到一个学生简表softstu表中。
insert into softstu(sno,sname)
select stu.sno,stu.sname
from stu
where mno=‘02’;

14.查询所授每门课程平均成绩均在70分以上的教师

INSERT INTO
faculty
SELECT
teacher tname
FROM
cou c1
WHERE
cno
IN
(select cno from sc group by cno having avg(grade) > 70)
GROUP BY
teacher
HAVING
COUNT() = (select count() from cou c2 where c1.teacher = c2.teacher)

15.删除售价大于100的产品记录
delete from
Product
where sale_price>=100

16.添加一条学生记录
insert into stu(sno,snmae,sex)
values(‘S012’,‘周强’,1);

17.查询平均分以上的课程

本题目要求编写SQL语句，
查询每个同学超过他选修的平均成绩的课程。

请使用：
1）用相关子查询实现
2）使用派生表实现。
SELECT
sno 学号,cname 课程名,grade 成绩
FROM
cou,sc a
WHERE
cou.cno = a.cno
AND
(a.grade > (SELECT avg(b.grade) FROM sc b WHERE a.sno = b.sno))

18.添加学生成绩记录

本题目要求编写insert语句， 在sc表中，为学号为S012的学生添加以下学生选课成绩记录。 选修C001课程，成绩为90。 选修C002课程，成绩为空

注意：每条inser语句应以"；"结束。

insert into sc
values(‘S012’,‘C001’,90);
insert into sc
values(‘S012’,‘C002’,null);

19.修改高数不及格的学生

update sc
set grade = 60
where grade < 60 and cno=
(select cno from cou where cname = ‘高等数学’）

20.新增一个区域
insert into
region(RegionID,RegionDescription)
values(5,‘Center’)