数据库应用题库

4）统计每门课程的学生选修人数（超过10人的课程才统计）。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

SELECT C# ,COUNT(S#) FROM SC GROUP BY C# HAVING COUNT(*)>10 ORDER BY 2 DESC，1；

5）检索学号比WANG同学大，而年龄比他小的学生姓名。

SELECT SNAME FROM S WHERE S#>ALL (SELECT S# S WHERE SNAME=?WANG?) AND AGE

SELECT S#，C# FROM SC WHERE GRADE IS NULL； 7）检索姓名以L打头的所有学生的姓名和年龄。

SELECT SNAME，AGE FROM S WHERE SNAME LIKE ?L%?； 8）求年龄大于女同学平均年龄的男学生姓名和年龄。

SELECT SNAME，AGE FROM S WHERE SEX=?M?AND

AGE >(SELECT AVG(AGE) FROM S WHERE SEX=?F?)；

9）求年龄大于所有女同学年龄的男学生姓名和年龄。

SELECT SNAME，AGE FROM S WHERE SEX=?M?AND

AGE >ALL(SELECT AGE FROM S WHERE SEX=?F?)；

8．用SQL更新语句表达对上题教学数据库中关系S、SC、C作如下更新操作： 1）往关系C中插一个课程元组。

INSERT INTO C VALUES(?C8?，?VC++?，?BAO?)； 2）SC中删除尚无成绩的选课元组。

DELETE FROM SC WHERE GRADE IS NULL； 3）把选修LIU老师课程的女同学选课元组全部删去。

DELETE FROM SC WHERE S# IN (SELECT S# FROM S WHERE SEX =?F?) AND C# IN(SELECT C# FROM C WHERE TEACHER=?LIU?)； 4）把MATHS课不及格的成绩全改为60分。 UPDATE SC SET GRADE = 60

WHERE GRADE<60 AND C# IN (SELECT C# FROM C WHERE CNAME=?MATHS?)； 5）把低于所有课程总平均成绩的女同学成绩提高5%。 UPDATE SC SET GRADE = GRADE *1.05

WHERE S# IN（SELECT S# FROM S WHERE SEX=?F?）AND GRADE<(SELECT AVG(GRADE) FROM SC)；

6）在表SC中修改C4课程的成绩，若成绩小于等于70分提高5%，若成绩大于70分时提高4%（用两种方法实现，一种方法是用两个UPDATE语句实现，另一种方法是用带CASE操作的一个UPDATE语句实现）。

UPDATE SC SET GRADE=GRADE * 1.04 WHERE C#=?C4?AND GRADE>70；

UPDATE SC SET GRADE =GRADE * 1.05 WHERE C # =?C4?AND GRADE<=70;

7）在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%。

UPDATE SC SET GRADE = GRADE * 1.05

WHERE GRADE<(SELECT AVG(GRADE) FROM SC)； 9．设数据库中有3个关系：

职工表EMP（E#，ENAME，AGE，SEX，ECITY），其属性分别表示职工工号、姓名、年龄、性别和籍贯。

工作表WORKS（E#，C#，SALARY），其属性分别表示职工工号、工作的公司编号和工资。

公司表 COMP（C#，CNAME，CITY），其属性分别表示公司编号、公司名称和公司所在城市。

试用SQL语言写出下列操作：

1）检索超过50岁的男职工的工号和姓名。

SELECT E#，ENAME FROM EMP WHERE AGE>50 AND SEX=?M?；

2）假设每个职工只能在一个公司工作，检索工资超过1000元的男性职工工号和姓名。 SELECT EMP.E#，ENAME FROM EMP，WORKS WHERE EMP.E#=WORKS.E# AND SALARY>1000；

3）假设每个职工可在多个公司工作，检索在编号为C4和C8公司兼职的职工工号和姓名。 SELECT A.E#，A.ENAME FROM EMP A，WORKS B，WORKS C WHERE A.E#=B.E# AND B.E#=C.E# AND B.C#=?C4?AND C.C#=?C8?； 4）检索在“联华公司”工作、工资超过1000元的男性职工的工号和姓名。

SELECT A.E#，A.ENAME FROM EMP A，WORKS B，COMP C WHERE A.E#=B.E# AND B.C#=C.C# AND CNAME=?联华公司?

AND SALARY>1000 AND SEX=?M?；

5）假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和工资总数，显示（E#，NUM，SUM_SALARY），分别表示工号、公司数目和工资总数。

SELECT E#，COUNT(C#) AS NUM，SUM(SALARY) AS SUM_SALARY FROM WORKS GROUP BY E#；

6）工号为E6的职工在多个公司工作，试检索至少在E6职工兼职的所以公司工作的职工工号。

SELECT X.E# FROM WORKS X WHERE NOT EXISTS

(SELECT * FROM WORKS Y WHERE E#=?E6?AND NOT EXISTS

(SELECT * FROM WORKS Z WHERE Z.E#=X.E# AND Z.C#=Y.C#))； 7）检索联华公司中低于本公司平均工资的职工工号和姓名。

SELECT A.E#，A.ENAME FROM EMP A，WORKS B，COMP C

WHERE A.E#=B.E# AND B.C#=C.C# AND CNAME =?联华公司?AND SALARY<

(SELECT AVG(SALARY) FROM WORKS，COMP WHERE WORKS.C#=COMP.C# AND CNAME =?联华公司?)；8）在每个公司中为50岁以上职工加薪100元（若职工为多个公司工作，可重复加）。

UPDATE WORKS SET SALARY =SALARY+100

WHERE E# IN (SELECT E# FROM EMP WHERE AGE>50)；

10）在EMP表和WORKS表中删除年龄大于60岁的职工有关元组。

DELETE FROM WORKS WHERE E# IN (SELECT E# FROM EMP WHERE AGE>60)； DELETE FROM EMP WHERE AGE >60；

10．设有关糸模式：SB（SN，SNAME，CITY）其中，SB表示供应商，SN为供应商代号，SNAME为供应商名字，CITY为供应商所在城市，主关键字为SN。

PB(PN，PNAME，COLOR，WEIGHT) 其中PB表示零件，PN为零件代号，PNAME为零件名字，COLOR为零件颜色，WEIGHT为零件重量，主要字键为PN。

JB（JN，JNAME，CITY）其中，JB表示工程，JN为工程编号，JNAME为工程名字，

CITY为工程所在城市，主关键字为JN。

SPJB（SN，PN，CITY）其中，SPJB表示供应关糸，SN是为指定工程提供零件的供应商代号，PN为所提供的零代号，JN为工程编号，OTY表示提供的零件数量，主关键字为SN，PN，JN，外关键字为SN，PN，JN。

如图所示表示供应商（S）-零件（P）-工程（J）数据库表，写出实现以下各题功能的SQL语句：

SB PB SN SNAME CITY PN PNAME COLOR WEIGHT S1 N1 上海 P1 PN1 红 12 S2 N2 北京 P2 PN2 绿 18 S3 N3 北京 P3 PN3 蓝 20 S4 N4 上海 P4 PN4 红 13 S5 S5 南京 P5 PN5 蓝 11 P6 PN6 绿 15

SPJB SN PN JN PTY 1 P1 J1 200 S1 P1 J4 700 S2 P3 J1 400 S2 P3 J2 200 S2 P3 J3 200 S2 P3 J4 500 S2 P3 J5 600 S2 P3 J6 400 S2 P3 J7 800 S2 P3 J2 100 S3 P3 J1 200 S3 P4 J2 500 S4 P6 J3 300 S4 P6 J7 300 S5 P2 J2 200 S5 P2 J4 100 S5 P5 J5 500 S5 P5 J7 100 S5 P6 J2 200 S5 P1 J4 1000 S5 P3 J4 1200 S5 P4 J4 800 S5 P5 J4 400 S5 P6 J4 500 JB

JN J1 J2 J3 J4 J5 J6 J7 JNAME JN1 JN2 JN3 JN4 JN5 JN6 JN7 CITY 上海 广州 南京 南京 上海 武汉 上海 1）取出为工程J1提供红色零件的供应商代号

SELECT DISTINCT SPJ.SN FROM SPJB，PB

WHERE PB.PN=SPJB.PN AND SPJB.JN=?J1?AND PB.COLOR=?红?；

2)取出为所在城市为上海的工程提供零件的供应商代号

SELECT DISTINCT SPJB.SN FROM SPJB，JB WHERE SPJB.JN AND JB.CITY=?上海?；

3)取出供应商与工程所在城市相同的供应商提供的零件代号

SELECT DISTINCT SPJB.PN FROM SB,JB,SPJB

WHERE SB.SN=SPJB.SN AND JB.JN=SPJB.JN AND SB.CITY=JB.CITY 六、综合题

1．设有如下所示的关系R（码为：课程名） 问：1）该关系模式为第几范式？为什么？

2）是否存在删除操作异常？若存在，则说明在什么情况下发生的？

3）将它分解为高一级范式，分解后的关系是如何解决分解前可能存在的删除操作异常

问题的？

关系R

答：（1） R∈2NF

课程名 教师名 教师地址 ∵R的侯选码为课程名，存在课程名→教师

C1 D1 王小强 名，

教师名→课程名，教师名→教师地址 C2 D2 李鸿雁 ∴课程名→教师地址

C3 D1 王小强 即存在非主属性对码的传递函数依赖关系

C4 D1 张言 ∴R不属于3NF

又∵不存在非主属性对码的部分函数依赖

∴R∈2NF

（2）存在删除操作异常，当删除某门课程时，教师的信息也被删除了。 （3）分解R

R1 R2

课程名 教师名 教师名 教师地址 C1 王小强 D1 王小强

C2 李鸿雁 D2 李鸿雁 C3 王小强 D1 张言 C4 张言

2．根据下列给出的关系模式和函数依赖集，指出该关系模式是第几范式？并说明理由。 1）R（X，Y，Z）码：XY F={XY→Z}

答：R∈BCNF 因为不存在非主属性对码的部分函数依赖和传递函数依赖，所以R∈3NF， 又因为所有的函数依赖的决定因素都是码，所以R∈BCNF。 2）R（X，Y，Z）码：XY和XZ F={Y→Z，XZ→Y}

答：R∈3NF 因为不存在非主属性对码的部分函数依赖和传递函数依赖，所以R∈3NF， 又因为所有的函数依赖的决定因素不都是码，所以R不是BCNF 。 3）R（X，Y，Z）码：X F={X→Y，X→Z}