数据库系统及应用教程(SQL Server 2008)习题1-8章答案

SELECT CNO

FROM S JOIN SC ON S.SNO=SC.SNO AND S.SNAME='李小刚' GO

(6) USE JXGL

GO

select SNO,count(CNO) as 选修门数 from sc

group by SNO having count(CNO)>1 GO

3. 试用T-SQL查询语句表达下列对习题2数据库中三个基本表S、SC、C的查询： (1) 统计有学生选修的课程门数。

(2) 求选修C4号课程的学生的平均年龄。

(3) 求“王志强”老师所授课程的每门课程的学生平均成绩。 (4) 统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。 (5) 查询姓“王”的所有学生的姓名和年龄。

(6) 在SC中查询成绩为空值的学生学号和课程号。

(7) 查询年龄大于女同学平均年龄的男学生姓名和年龄。 解：

(1) USE JXGL

GO

SELECT COUNT(DISTINCT CNO) FROM SC GO

(2) USE JXGL

GO

SELECT AVG(AGE)

FROM S JOIN SC ON S.SNO=SC.SNO AND CNO='C4' GO

(3) USE JXGL

GO

SELECT SC.CNO,AVG(GRADE)

FROM SC JOIN C ON SC.CNO=C.CNO AND TNAME='王志强' GROUP BY SC.CNO GO

(4) USE JXGL

GO

SELECT CNO,COUNT(SNO) FROM SC

GROUP BY CNO HAVING COUNT(*)>10 ORDER BY 2 DESC,1 GO

(5) USE JXGL

GO

17

SELECT SNAME,AGE FROM S

WHERE SNAME LIKE '王%' GO

(6) USE JXGL

GO

SELECT SNO,CNO FROM SC

WHERE GRADE IS NULL GO

(7) USE JXGL

GO

SELECT SNAME,AGE

FROM S

WHERE SEX='M' AND AGE>(SELECT AVG(AGE) FROM S

WHERE SEX='F')

GO

4. 试用T-SQL更新语句表达对习题6-2中数据库中三个基本表S、SC、C的各个更新操作：

(1) 在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表STUDENT(SNO,SNAME,SEX)。 (2) 在基本表SC中删除尚无成绩的选课元组。

(3) 把“张成民”同学在SC中的选课记录全部删去。

(4) 把选修“高等数学”课程中不及格的成绩全部改为空值。

(5) 把低于总平均成绩的女同学成绩提高5%。 解：

(1) 建表：

USE JXGL GO

CREATE TABLE STUDENT(SNO CHAR(9) NOT NULL,

SNAME CHAR(8) NOT NULL, SEX CHAR(2))

GO

查询结果插入：

USE JXGL GO

INSERT INTO STUDENT(SNO,SNAME,SEX) SELECT SNO,SNAME,SEX FROM S

WHERE SNO IN (SELECT SNO

FROM SC

GROUP BY SNO HAVING MIN(GRADE)>80)

GO

18

(2) USE JXGL

GO

DELETE FROM SC

WHERE GRADE IS NULL

GO

(3) USE JXGL

GO DELETE

FROM SC

WHERE SNO IN(SELECT SNO FROM S

WHERE SNAME='张成民')

GO

(4) USE JXGL

GO

UPDATE SC

SET GRADE=NULL

WHERE GRADE<60 AND CNO IN(SELECT CNO FROM C

WHERE CNAME='高等数学')

GO

(5) USE JXGL

GO

UPDATE SC

SET GRADE=GRADE*1.05 WHERE SNO IN(SELECT SNO FROM S

WHERE SEX='F')

AND GRADE<(SELECT AVG(GRADE) FROM SC)

GO

5. 假设某“仓库管理”关系模型有下列五个关系模式： 零件PART(PNO,PNAME,COLOR,WEIGHT) 项目PROJECT(JNO,JNAME,JDATE)

供应商SUPPLIER(SNO,SNAME,SADDR) 供应P_P(JNO,PNO,TOTAL)

采购P_S(PNO,SNO,QUANTITY)

试用T-SQL DDL语句定义上述五个基本表，并说明主键和外键。 解：

CREATE TABLE PART

(PNO CHAR(6),PNAME CHAR(10) NOT NULL,COLOR CHAR(6),WEIGHT FLOAT(6),PRIMARY KEY(PNO));

CREATE TABLE PROJECT

19

(JNO CHAR(6),JNAME CHAR(12)NOT NULL,DATE DATE,PRIMARY KEY(JNO)); CREATE TABLE SUPPLIER

(SNO CHAR(8),SNAME CHAR(12)NOT NULL,SADDR VARCHAR(30),PRIMARY KEY(SNO));

CREATE TABLE P_P

(JNO CHAR(6),PNO CHAR(6),TOTAL INTEGER,PRIMARY KEY(JNO,PNO); FOREIGN KEY(JNO) REFERENCES PROJECT(JNO), FOREIGN KEY(PNO) REFERENCES PART(PNO)); CREATE TABLE P_S

(PNO CHAR(6),SNO CHAR(8),QUANTITY INTEGER,PRIMARY KEY(PNO,SNO) FOREIGN KEY(PNO) REFERENCES PART(PNO),

FOREIGN KEY(SNO) REFERENCES SUPPLIER(SNO)); 6．利用T-SQL语句声明一个游标，查询习题2数据库S表中所有男生的信息，并读取数据。

(1) 读取最后一条记录。 (2) 读取第一条记录。 (3) 读取第5条记录。

(4) 读取当前记录指针位置后第3条记录。

解：USE JXGL

GO

DECLARE S_Cursor SCROLL CURSOR FOR SELECT *

FROM S

WHERE SEX='M'

OPEN S_Cursor

FETCH LAST FROM S_Cursor FETCH PRIOR FROM S_Cursor FETCH ABSOLUTE 5 FROM S_Cursor CLOSE S_Cursor DEALLOCATE S_Cursor GO

习 题 7

1．名词解释：

视图 索引 聚集索引 唯一索引 答：

视图：是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是数据库中只存放视图的定义而不存放视图的数据。

索引：是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

聚集索引：该索引中键值的逻辑顺序决定了表中相应行的物理顺序。这种索引对查询非

20