数据库期末考试部分试题

N 商品 N 商品号 商品名 规格 单价 图5.3 第5.15题的ER图实例

②这个ER图可转换5个关系模式：

供应商（供应商编号，供应商名，地址） 商店（商店号，商店名，地址）

商品（商品号，商品名，规格，单价） 供应（供应商编号，商品号，月供应量） 销售（商店号，商品号，月计划数）

题型：SQL语句

题目3.1：设教学数据库中有4个关系： 教师关系 T（T#,TNAME,TITLE） 课程关系 C（C#,CNAME,T#）

学生关系 S（S#,SNAME,AGE,SEX） 选课关系 SC（S#,C#,SCORE） 试用SQL查询语句表示下列查询。

①检索年龄小于17岁女生的学号和姓名。 ②检索男生所学课程的课程号和课程名。

③检索男生所学课程的任课老师的工号和姓名。

④检索至少选修两门课程的学生学号。

⑤检索至少有学号为S2和S4学生选修的课程的课程号。 ⑥检索WANG同学不学的课程的课程号。

⑦检索全部学生都选修的课程的课程号与课程名。

⑧检索选修课程包含LIU老师所授全部课程的学生学号。

答案：①SELECT S#,SNAME FROM S

WHERE AGE<17 AND SEX=’F’;

②SELECT C.C#,CNAME FROM S,SC,C

WHERE S.S#=SC.S# AND SC.C#=C.C# AND SEX=’M’; ③SELECT T.T#’,TNAME FROM S,SC,C,T

WHERE S.S#=SC.S# AND SC.C#=C.C# AND C.T#=T.T# AND SEX=’M’; ④SELECT DISTINCT X.S# FROM SC AS X,SC AS Y

WHERE X.S#=Y.S# AND X.C#!=Y.C#; ⑤SELECT DISTINCT X.C# FROM SC AS X,SC AS Y

WHERE X.S#=S2 AND Y.S#=S4 AND X.C#=Y.C#; ⑥SELECT C# FROM C

WHERE NOT EXISTS(SELECT * FROM S,SC

WHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME=’WANG’);

⑦SELECT C#,CNAME FROM C

WHERE NOT EXISTS(SELECT *

FROM S

WHERE NOT EXISTS(SELECT *

FROM SC

WHERE S#=S.S# AND C#=C.C#));

⑧SELECT DISTINCT S# FROM SC AS X

WHERE NOT EXISTS(SELECT *

FROM C,T

WHERE C.T#=T.T# AND TNAME=’LIU’

AND NOT EXISTS(SELECT *

FROM SC AS Y WHERE Y.S#=X.S# AND

Y.C#=C.C#))

题目:3.2 用CASE操作在下列更新语句中完成的SC表中的元组更新：

① 若课程号为C5则增加6分，若课程号为C8则增加10分，其他一律增

加5分。

② 若C4课程的成绩低于该门课的平均成绩，则提高5%，否则提高4%。 答案：

① UPDATE SC

SET SCORE=SCORE+CASE

WHEN C#=’C5’THEN 6 WHEN C#=’C8’THEN 10 ELSE 5 END;

② UPDATE SC

SET SCORE=SCORE*CASE

WHEN SCORE<(SELECT AVG(SCORE)

FROM SC

WHERE C#=’C4’)

THEN 1.05 ELSE 1.04 END WHERE C#=’C4’;

题目3.3：设教学数据库中有4个关系： 教师关系 T（T#,TNAME,TITLE） 课程关系 C（C#,CNAME,T#）

学生关系 S（S#,SNAME,AGE,SEX） 选课关系 SC（S#,C#,SCORE）

试用SQL查询语句表示下列更新操作：

① 往关系C中插入一个课程元组（‘C8‘，‘VC++’，‘T6’）。

② 检查所授每门课程的平均成绩大于80分的教师姓名，并把检索到的值送到另一个已存在的表FACULTY（TNAME）中。 ③ 在SC中删除尚无成绩的选课元组。

④ 把选修LIU老师的课程的女同学选课元虚全部删去。 ⑤ 把MATHS课不及格的成绩全改为60分。

⑥ 把低于所有课程总平均成绩的女同学成绩提高5%。

⑦ 在表SC中修改C4课程的成绩，当成绩小于70分时提高5%，若成绩大于70分时提高4%（用两种方法实现，一种方法是用两个UPDATE语句实现，另一种方法是用带CASE操所得UPDATE语句实现）。 ⑧ 在表SC中，当成绩低于全部课程的平均成绩时，提高5%。

答案：

①insert into C values（’C8’, ’VC++’， ’T6’） ②insert into FACULTY(TNAME)

select distinct TNAME

from(select TNAME,C.C#,AVG(SCORE) from T,C,SC

where T.T#=C.T# and C.C# = SC.C# group by C.C#,TNAME)

as result(TNAME,C#,AVG_SCORE) as x where 80<=ALL(select AVG_SCORE from result as y

where y.TNAME=x.TNAME)

③delete from SC where SCORE is null ④delete from SC

where S# in(select S# from S

where SEX='F')and C# in(select C#from C

where T# in(select T# from T

where TNAME='LIU'))

⑤update SC set SCORE = SCORE*1.05

where SCORE<(select AVG(SCORE)

from SC) and S# in (select S#

from S

where SEX='男')

⑥update SC

Set SCORE = SCORE * 1.05

Where SCORE<(select AVG(SCORE)

From SC)

And S# in(select S#

From S

Where SEX=’男’)

⑦第一种写法： update SC

Set SCORE=SCORE*CASE

When SCORE>70 then ‘1.04’ Else ‘1.05’ END

Where C#=’C4’ 第二种写法： Update SC

Set SCORE=SCORE*1.04

Where SCORE>70 and C#=’C4’ Update SC

Set SCORE = SCORE*1.05

Where SCORE<=70 and C#=’C4’

⑧update SC

set SCORE=SCORE*1.05

where SCORE<(select AVG(SCORE),C#

from SC

group by C#)

题目3.4：试用SQL查询语句表达下列对教学数据库中4个基本表T、C、S、SC