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

数库原理应实验指导更新操作练题
练题
1学生表Student学生选课表SC中分添加两表中记录
学生表Student 学生选课表SC
学号
(Sno)
姓名
(Sname)
年龄
(Sage)
性
(Ssex)
系
(Sdept)
98010
赵青江
18
男
CS
98011
张丽萍
19
女
CH
98012
陈景欢
20
男
IS
98013
陈婷婷
16
女
PH
98014
李 军
16
女
EH

学号
(Sno)
课程号
(Cno)
成绩
(Grade)
98010
1
87
98010
2

98010
3
80
98010
4
87
98010
6
85
98011
1
52
98011
2
47
98011
3
53
98011
5
45
98012
1
84
98012
3

98012
4
67
98012
5
81
课程表Course
课程号
(Cno)
课程名
(Cname)
先修课号(Cpno)
学分
(Ccredit)










2备份Student表TS中清空TS表
3IS系学生开设7号课程建立相应选课记录成绩暂定60分
4年龄等16女生记录保存表TS中
5表Student中检索门课均格学生学号姓名年龄性系等信息检索信息存入TS表中
6学号98011学生姓名改＇刘华＇年龄增加1岁
7选修数库系统课程成绩格学生成绩全改空值（NULL）
8Student前4位学生年龄均增加1岁
9学生王林3号课程考试中作弊该课成绩改空值（NULL）
10成绩低总均成绩女学成绩提高5％
11基表SC中修改课程号2号课程成绩成绩等80分时降低2％成绩80分时降低1％(两UPDATE语句实现)
12利SELECT INTO ……命令备份StudentSCCourse三表备份表名定
13基表SC中删尚成绩选课元组
14钱横学选课情况全部删
15删学号98005学生记录？定删该记录话该操作？出操作命令
16删姓张学生记录
17清空STUDENTCourse两表
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(snocno) 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(snocno) 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
3IS系学生开设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 SCSNOSTUDENTSNO)
INSERT INTO TS
SELECT *
FROM STUDENT
WHERE 60 >
(SELECT MAX(GRADE)
FROM SC
WHERE SCSNOSTUDENTSNO)

INSERT INTO TS
SELECT *
FROM STUDENT
WHERE NOT EXISTS
(SELECT GRADESNO
FROM SC
WHERE SCSNOSTUDENTSNO 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 SNOSTUDENTSNO AND CNOCOURSECNO
AND GRADE<60))

6学号98011学生姓名改＇刘华＇年龄增加1岁
UPDATE STUDENT SET SNAME＇刘华＇SAGESAGE+1 WHERE SNO＇98011＇
7选修数库系统课程成绩格学生成绩全改空值（NULL）
UPDATE SC SET GRADENULL
WHERE GRADE<60 AND CNO IN
(SELECT CNO
FROM COURSE
WHERE CNAME＇数库系统＇)

UPDATE SC
SET GRADENULL
FROM COURSE
WHERE SCGRADE<60 AND SCXCNOCOURSECNO AND CNAME＇数库系统＇


8Student前4位学生年龄均增加1岁
Update student set sagesage+1 where sno in (select top 4 sno from student [order by sno])

9学生王林3号课程考试中作弊该课成绩改空值（NULL）
UPDATE SC SET GRADENULL
WHERE cno＇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 scgrade105*scgrade
from sc(select * from student where ssex＇男＇) as stn
where scsno stnsno AND grade<(select avg(grade) from sc)
select * from sc


update sc set scgrade105*scgrade
from scstudent
where ssex＇男＇ AND grade<(select avg(grade) from sc) and scsnostudentsno
select * from sc

update sc set scgrade105*scgrade
from (select * from student where ssex＇男＇) as stn
where scsno stnsno AND grade<(select avg(grade) from sc)
select * from sc

update sc set scgrade105*scgrade
where scsno 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 GRADEGRADE*098 WHERE CNO＇2＇ AND GRADE<80
UPDATE SC SET GRADEGRADE*099 WHERE CNO＇2＇ AND GRADE>80

条命令完成
UPDATE SC SET GRADECASE
WHEN GRADE<80 THEN GRADE*098
WHEN GRADE>80 THEN GRADE*098
END
WHERE CNO＇2＇



12利SELECT INTO ……命令备份StudentSCCourse三表备份表名定
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清空STUDENTCourse两表
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
学号
(Sno)
姓名
(Sname)
年龄
(Sage
性
(Ssex)
系
(Sdept)
98010
赵青江
18
男
CS
98011
张丽萍
19
女
CH
98012
陈景欢
20
男
IS
98013
陈婷婷
16
女
PH
98014
李 军
16
女
EH

学号
(Sno)
课程号
(Cno)
成绩
(Grade)
98010
1
87
98010
2

98010
3
80
98010
4
87
98010
6
85
98011
1
52
98011
2
47
98011
3
53
98011
5
45
98012
1
84
98012
3

98012
4
67
98012
5
81



文档香网(httpswwwxiangdangnet)户传

《香当网》用户分享的内容，不代表《香当网》观点或立场，请自行判断内容的真实性和可靠性！
该内容是文档的文本内容，更好的格式请下载文档