SQL语言——更新操作命令

要使用包括如下三个表的“教学管理”数据库JXGL：

(1)学生表Student，由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记作：Student(Sno,Sname,Ssex,Sage,Sdept)，其中主码为Sno。

(2)课程表Course，由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记作：Course(Cno,Cname,Cpno,Ccredit)，其中主码为Cno。

(3)学生选课SC，由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，记作：SC(Sno,Cno,Grade)，其中主码为(Sno,Cno)。

基于“教学管理”数据库JXGL，试用SQL的查询语句表达下列更新操作：

1.在学生表Student和学生选课表SC中分别添加如下两表中的记录

INSERT INTO Student VALUES('98010','赵青江',18,'男','CS')
INSERT INTO Student VALUES('98011','张丽萍',19,'女','CH')
INSERT INTO Student VALUES('98012','陈景欢',20,'男','IS')
INSERT INTO Student VALUES('98013','陈婷婷',16,'女','PH')
INSERT INTO Student VALUES('98014','李军',16,'女','EH') --注意‘李军’两个字之间没有空格
INSERT INTO SC VALUES('98010','1',87)
INSERT INTO SC VALUES('98010','2',null)
INSERT INTO SC VALUES('98010','3',80)
INSERT INTO SC VALUES('98010','4',87)
INSERT INTO SC VALUES('98010','6',85)
INSERT INTO SC VALUES('98011','1',52)
INSERT INTO SC VALUES('98011','2',47)
INSERT INTO SC VALUES('98011','3',53)
INSERT INTO SC VALUES('98011','5',45)
INSERT INTO SC VALUES('98012','1',84)
INSERT INTO SC VALUES('98012','3',null)
INSERT INTO SC VALUES('98012','4',67)
INSERT INTO SC VALUES('98012','5',81)

2.备份Student表到TS中

SELECT * INTO TS FROM Student

3.给IS系的学生开设7号课程，建立所有相应的选课记录，成绩暂定为60分

INSERT INTO SC
SELECT Sno,'7',60 --开设的课程号为7,成绩暂定为60
FROM Student
WHERE Sdept='IS'

4.把年龄小于等于16的女生记录保存到表TS中

INSERT INTO TS
SELECT *
FROM Student
WHERE Sage<=16 AND Ssex='女'

5.在表Student中检索每门课均不及格的学生学号、姓名、年龄、性别及所在系等信息，并把检索到的信息存入TS表中

INSERT INTO TS
SELECT *
FROM Student
WHERE Sno IN
	(SELECT Sno
 	FROM SC
 	GROUP BY Sno
 	HAVING MAX(Grade)<60) --所选所有课程最高成绩小于60分，说明该同学每门课均不及格

6.将学号为“98011”的学生姓名改为’刘华’，年龄增加1岁

UPDATE Student
SET Sname = '刘华'
WHERE Sno = '98011'  --分两步操作来完成更新
UPDATE Student
SET Sage = Sage+1
WHERE Sno = '98011'

7.把选修了“数据库系统”课程而成绩不及格的学生的成绩全改为空值（NULL）

UPDATE SC
SET Grade = null --注意null不可以加单引号
WHERE Grade <60 AND Cno IN
	(SELECT Cno
 	FROM Course
 	WHERE Cname='数据库系统')

8.将Student的前4位学生的年龄均增加1岁

UPDATE Student
SET Sage = Sage+1
WHERE Sno IN
	(SELECT TOP(4) Sno --用top筛选出前4位学生
 	FROM Student)

9.学生王林在3号课程考试中作弊，该课成绩改为空值（NULL）

UPDATE SC
SET Grade = null
WHERE Cno = '3' AND Sno IN
	(SELECT Sno
 	FROM Student
 	WHERE Sname = '王林')

10.把成绩低于总平均成绩的女同学成绩提高5％

UPDATE SC
SET Grade = Grade * 1.05
WHERE Grade<(SELECT AVG(Grade) FROM SC) --成绩低于总平均成绩
AND Sno IN(SELECT Sno FROM Student WHERE Ssex = '女')  --该同学为女同学

11.在基本表SC中修改课程号为“2”号课程的成绩，若成绩小于等于80分时降低2％，若成绩大于80分时降低1％(用两个UPDATE语句实现)

#方法1 用两个UPDATE语句实现
UPDATE SC
SET Grade = Grade*0.98
WHERE Cno = '2' AND Grade<=80
UPDATE SC
SET Grade = Grade*0.99
WHERE Cno = '2' AND Grade>80

#方法2 用一个UPDATE语句实现
UPDATE SC
SET Grade = 
CASE
WHEN Grade<=80 THEN Grade*0.98
WHEN Grade>80 THEN Grade*0.99
END
WHERE Cno = '2'

12.利用“SELECT INTO ……”命令来备份Student、SC、Course三表，备份表名分别是TSTUDENT、TSC、TCourse

SELECT * INTO TSTUDENT FROM Student;
SELECT * INTO TSC FROM SC;
SELECT * INTO TCourse FROM Course;

13.在基本表SC中删除尚无成绩的选课元组

DELETE SC
WHERE GRADE IS NULL

14.把“钱横”同学的选课情况全部删去

DELETE SC
WHERE Sno IN
	(SELECT Sno
 	FROM Student
 	WHERE Sname = '钱横')

15.能删除学号为“98005”的学生记录吗？一定要删除该记录的话，该如何操作？给出操作命令

DELETE SC
WHERE Sno = '98005'

16.清空STUDENT与Course两表

DELETE SC  --在清空STUDENT之前需要将SC选课表一并删除（外键约束条件）
DELETE STUDENT
DELETE COURSE

17.如何又从备份表中恢复所有的三表

INSERT INTO STUDENT SELECT * FROM TSTUDENT
INSERT INTO COURSE SELECT * FROM TCOURSE
INSERT INTO SC SELECT * FROM TSC

18.删除姓“张”的学生记录

DELETE SC --完整性约束条件要求先删除SC关系表中姓“张”的学生选课记录
WHERE Sno IN
	(SELECT Sno
 	FROM Student
 	WHERE Sname LIKE '张%')
DELETE Student
WHERE Sname LIKE '张%'