数据库原理及应用实验指导★——更新操作练习题

.
1 / 6
练习题
1、在学生表Student 和学生选课表SC 中分别添加如下两表中的记录。

学生表Student 学生选课表SC
3、给IS 系的学生开设7号课程,建立所有相应的选课记
录,成绩暂定为60分。

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

5、在表Student 中检索每门课均不及格的学生学号、、
年龄、性别及所在系等信息,并把检索到的信息存入TS
6、将学号为"98011”的学生改为'华',年龄增加1岁。

7、把选修了"数据库系统"课程而成绩不及格的学生的成绩全改为空值〔NULL 。

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

9、学生王林在3号课程考试中作弊,该课成绩改为空值〔NULL 。

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

11、在基本表SC 中修改课程号为"2”号课程的成绩,若成绩小于等于80分时降低2％,若成绩大于80分时降低1％<用两个UPDATE 语句实现>。

12、利用"SELECT INTO ……"命令来备份Student 、SC 、Course 三表,备份表名自定。

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

14、把"钱横"同学的选课情况全部删去。

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

16、删除姓""的学生记录。

17、清空STUDENT 与Course 两表。

18、如何又从备份表中恢复所有的三表。

参考答案：
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<sno,cno> values<'98010', '2'>
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<sno,cno> values<'98012', '3'>
Insert into SC values<'98012', '4',67>
Insert into SC values<'98012', '5',81>
2、备份Student表到TS中,并清空TS表。

Select * into TS from Student
Delete from TS 或 truncate table TS
3、给IS系的学生开设7号课程,建立所有相应的选课记录,成绩暂定为60分。

Insert into sc
select sno,'7',60
from student
where sdept='IS'
4、把年龄小于等于16的女生记录保存到表TS中。

INSERT INTO TS SELECT * FROM STUDENT WHERE SAGE<=16
5、在表Student中检索每门课均不及格的学生学号、、年龄、性别及所在系等信息,并把检索到的信息存入TS表中。

INSERT INTO TS
SELECT *
FROM STUDENT
WHERE SNO IN
<SELECT SNO
FROM SC
GROUP BY SNO
HAVING MAX<GRADE><60
>
INSERT INTO TS
SELECT *
.
FROM STUDENT
WHERE 60 > ALL
<SELECT GRADE
FROM SC
WHERE SC.SNO=STUDENT.SNO>
INSERT INTO TS
SELECT *
FROM STUDENT
WHERE 60 >
<SELECT MAX<GRADE>
FROM SC
WHERE SC.SNO=STUDENT.SNO>
INSERT INTO TS
SELECT *
FROM STUDENT
WHERE NOT EXISTS
<SELECT GRADE,SNO
FROM SC
WHERE SC.SNO=STUDENT.SNO AND GRADE>=60>
INSERT INTO TS
SELECT *
FROM STUDENT
WHERE SNO IN
<SELECT SNO
FROM SC
WHERE SNO NOT IN <SELECT SNO FROM SC WHERE GRADE>59>> -- 如下有错,意为所有课程均学了,并均不及格。

INSERT INTO TS
SELECT *
FROM STUDENT
WHERE NOT EXISTS
<SELECT *
FROM COURSE
WHERE NOT EXISTS
<SELECT *
FROM SC
WHERE SNO=STUDENT.SNO AND CNO=O
AND GRADE<=60>>
6、将学号为"98011”的学生改为'华',年龄增加1岁。

UPDATE STUDENT SET SNAME='华',SAGE=SAGE+1 WHERE SNO='98011'
7、把选修了"数据库系统"课程而成绩不及格的学生的成绩全改为空值〔NULL。

UPDATE SC SET GRADE=NULL
WHERE GRADE<60 AND CNO IN
3 / 6
<SELECT CNO
FROM COURSE
WHERE CNAME='数据库系统'>
UPDATE SC
SET GRADE=NULL
FROM COURSE
WHERE SC.GRADE<60 AND O=O AND CNAME='数据库系统'
8、将Student的前4位学生的年龄均增加1岁。

Update student set sage=sage+1 where sno in <select top 4 sno from student [order by sno]>
9、学生王林在3号课程考试中作弊,该课成绩改为空值〔NULL。

UPDATE SC SET GRADE=NULL
WHEREo='3' AND SNO IN
<SELECT SNO
FROM STUDENT
WHERE SNAME='王林'>
10、把成绩低于总平均成绩的女同学成绩提高5％。

SELECT * INTO TSC FROM SC
delete from sc
insert into sc select * from tsc
drop table tsc
select * into tsc from sc
update sc set sc.grade=1.05*sc.grade
from sc,<select * from student where ssex='男'> as stn
where sc.sno= stn.sno AND grade<<select avg<grade> from sc>
select * from sc
update sc set sc.grade=1.05*sc.grade
from sc,student
where ssex='男' AND grade<<select avg<grade> from sc> and sc.sno=student.sno
select * from sc
update sc set sc.grade=1.05*sc.grade
from <select * from student where ssex='男'> as stn
where sc.sno= stn.sno AND grade<<select avg<grade> from sc>
select * from sc
update sc set sc.grade=1.05*sc.grade
where sc.sno in <select sno from student where ssex='男'> AND grade<<select
avg<grade> from sc>
select * from sc
11、在基本表SC中修改课程号为"2”号课程的成绩,若成绩小于等于80分时降低2％,若成绩大于80分时降低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
-- 使用一条命令完成
UPDATE SC SET GRADE=CASE
WHEN GRADE<=80 THEN GRADE*0.98
WHEN GRADE>80 THEN GRADE*0.98
END
WHERE CNO='2'
12、利用"SELECT INTO ……"命令来备份Student、SC、Course三表,备份表名自定。

SELECT * INTO TSTUDENT FROM STUDENT
SELECT * INTO TSC FROM SC
SELECT * INTO TCOURSE FROM COURSE
13、在基本表SC中删除尚无成绩的选课元组。

DELETE FROM SC WHERE GRADE IS NULL
14、把"钱横"同学的选课情况全部删去。

DELETE FROM SC WHERE SNO IN <SELECT SNO FROM STUDENT WHERE SNAME='钱横'>
15、能删除学号为"98005”的学生记录吗？一定要删除该记录的话,该如何操作？给出操作命令。

SC表有"98005”学生选课记录时,不能删除；一定要删除得先删除SC中的选课记录。

Delete from sc where sno='98005'
Delete from student where sno='98005'
16、删除姓""的学生记录。

Delete from sc where sno in <select sno from student where sname like '%'> Delete from student where sname like '%'
17、清空STUDENT与Course两表。

Delete from sc
Delete from student
Delete from course
18、如何又从备份表中恢复所有的三表。

Insert into STUDENT select * from TSTUDENT
Insert into COURSE select * from TCOURSE
Insert into SC select * from TSC
学生表Student 学生选课表SC
5 / 6。