数据库原理课后习题答案

 

第三章

10、设有一数据库 GradeManager（成绩管理），包括四个表：学生表（Student）、课程表（Course）、班级表（Class）以及成绩表（Grade），其结构如表3-4所示，数据如表3-5所示。试用SQL语句创建四个表。

 

答：CREATE  TABLE Student

(Sno Char(7) NOT NULL UNIQUE,

Sname  VarChar(20)  NOT NULL,

Ssex Char(2)  NOT NULL,

Sage Smallint,

Clno Char(5)  NOT NULL);

CREATE  TABLE Course

(Cno Char(1) NOT NULL UNIQUE,

Cname VarChar(20) NOT NULL,

Credit Smallint);

CREATE  TABLE Class

(Clno Char(5) NOT NULL UNIQUE,

Speciality VarChar(20) NOT NULL,

Inyear Char(4) NOT NULL,

Number Integer,

Monitor Char(7));

CREATE  TABLE Grade

(Sno Char(7) NOT NULL,

Cno Char(1) NOT NULL,

Gmark Numeric(4,1));

INSERT INTO Student

VALUE(‘2000101’,’李勇’,’男’,20,’00311’);

 

INSERT INTO Student

VALUE(‘2000102’,’刘诗晨’,’女’,19,’00311’);

 

INSERT INTO Student

VALUE(‘2000103’,’王一鸣’,’男’,20,’00312’);

 

INSERT INTO Student

VALUE(‘2000104’,’张婷婷’,’女’,21,’00312’);

 

INSERT INTO Student

VALUE(‘2001101’,’李勇敏’,’女’,19,’01311’);

 

INSERT INTO Student

VALUE(‘2001102’,’贾向东’,’男’,22,’01311’);

 

INSERT INTO Student

VALUE(‘2001103’,’陈宝玉’,’男’,20,’01311’);

 

INSERT INTO Student

VALUE(‘2001104’,’张逸凡’,’男’,21,’01311’);

 

INSERT INTO Course

VALUE(‘1’,’数据库’,4);

 

INSERT INTO Course

VALUE(‘2’,’离散数学’,3);

 

INSERT INTO Course

VALUE(‘3’,’管理信息系统’,2);

 

INSERT INTO Course

VALUE(‘4’,’操作系统’,4);

 

INSERT INTO Course

VALUE(‘5’,’数据结构’,4);

 

INSERT INTO Course

VALUE(‘6’,’数据处理’,2);

 

INSERT INTO Course

VALUE(‘7’,’c语言’,4);

 

INSERT INTO Class

VALUE(‘00311’,’计算机软件’,’2000’,120,’2000101’);

 

INSERT INTO Class

VALUE(‘00312’,’计算机应用’,’2000’,140,’2000103’);

 

INSERT INTO Class

VALUE(‘01311’,’计算机软件’,’2001’,220,’2001103’);

 

INSERT INTO Grade

VALUE(‘2000101’,’1’,92);

 

INSERT INTO Grade

VALUE(‘2000101’,’3’,88);

 

INSERT INTO Grade

VALUE(‘2000101’,’5’,86);

 

INSERT INTO Grade

VALUE(‘2000102’,’1’,78);

 

INSERT INTO Grade

VALUE(‘2000102’,’6’,55);

 

INSERT INTO Grade

VALUE(‘2000103’,’3’,65);

 

INSERT INTO Grade

VALUE(‘2000103’,’6’,78);

 

INSERT INTO Grade

VALUE(‘2000103’,’5’,66);

 

INSERT INTO Grade

VALUE(‘2000104’,’1’,54);

 

INSERT INTO Grade

VALUE(‘2000104’,’6’,83);

 

INSERT INTO Grade

VALUE(‘2001101’,’2’,70);

 

INSERT INTO Grade

VALUE(‘2001101’,’4’,83);

 

INSERT INTO Grade

VALUE(‘2001102’,’2’,80);

 

INSERT INTO Grade

VALUE(‘2001102’,’4’,90);

 

INSERT INTO Grade

VALUE(‘2000103’,’1’,83);

 

INSERT INTO Grade

VALUE(‘2000103’,’2’,76);

 

INSERT INTO Grade

VALUE(‘2000103’,’4’,56);

 

INSERT INTO Grade

VALUE(‘2000103’,’7’,88);

索引的优缺点：

数据库索引是为了增加查询速度而对表字段附加的一种标识。

反例：1、如果每次都需要取到所有表记录，无论如何都必须进行全表扫描了，那么是否加索引也没有意义了。2、对非唯一的字段，例如“性别”这种大量重复值的字段，增加索引也没有什么意义。3、对于记录比较少的表，增加索引不会带来速度的优化反而浪费了存储空间，因为索引是需要存储空间的，而且有个致命缺点是对于update/insert/delete的每次执行，字段的索引都必须重新计算更新。 

（5）找出李勇所在班级的学生人数；

SELECT COUNT(*)

FROM Student

WHERE Clno in

(SELECT Clno

FROM Student

WHERE Sname='李勇')

（6）找出课程名为操作系统的平均成绩 最高分 最低分；

SELECT AVG(Gmark) 平均成绩,MAX(Gmark)最高分,MIN(Gmark)最低分

FROM Grade

WHERE Cno in

(SELECT Cno

FROM Course

WHERE Cname='操作系统')

（7）选修了课程的学生人数；

SELECT COUNT(DISTINCT sno) 学生人数

FROM Grade

（8）选修了操作系统的学生人数；

SELECT COUNT(DISTINCT sno) 学生人数

FROM Grade

WHERE Cno in

(SELECT Cno

FROM Course

WHERE Cname='操作系统')

 

13、针对T10的四个表，用SELECT的嵌套查询完成以下各项查询：【难点】

（1）找出和李勇在同一个班级的学生信息；

SELECT *

FROM Student

WHERE Sname<>'李勇' and Clno in

(SELECT Clno

FROM Student

WHERE Sname='李勇')

（2）找出所有与学生李勇有相同选修课程的学生信息；

SELECT *

FROMS tudent

WHERE Sname<>'李勇' and exists

(SELECT *

FROM Grade

WHERE Student.Sno=Grade.Sno and Grade.Cno in

(SELECT Cno

FROMG Grade

WHERE Sno in

(SELECT Sno

FROM Student

WHERE Sname='李勇')))

（3）找出年龄介于学生李勇和25岁之间的学生信息；

SELECT * FROM Student

WHERE Sage < 25 and Sage >

(SELECT Sage FROMStudent WHERE Sname='李勇')

（4）找出选修了课程是操作系统的学生学号和姓名；

SELECT Sno 学号,Sname姓名

FROM Student

WHERE exists

(SELECT *

FROM Grade

WHERE Cno in(SELECT Cno

FROM Course

WHERE Cname='操作系统')

and Student.Sno=Grade.Sno)

（5）找出没有选修1号课程的所有学生姓名；

SELECT Sname 姓名

FROM Student

WHERE not exists

(SELECT *

FROM Grade

WHERE Student.Sno=Grade.Sno and Cno=1)

（6）找出选修了全部课程的学生姓名。

SELECT Sname 姓名

FROM Student

WHERE not exists

(SELECT Cno

FROM Course

except

SELECT Cno

FROMGrade

WHERE Student.Sno=Grade.Sno)

 

（4）对每个班,求学生的平均年龄,并把结果存入数据库。

ALTER TABLE Class

ADD HCage Smallint NULL

UPDATE Class

SET Cage=case

WHEN Clno='00311' THEN (SELECT AVG(Sage)

FROM Student

WHERE Clno='00311')

WHEN Clno='00312' THEN (SELECT AVG(Sage)

   FROM Student

WHERE Clno='00312')

WHEN Clno='01311' THEN (SELECT AVG(Sage)

FROM Student

WHERE Clno='01311')

end

FROM Class