SQL语句

题型：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