12．写出创建如下三张表的SQL语句，要求在定义表的同时定义数据的完整性约束： （1）“图书表”结构如下：
书号：统一字符编码定长类型，长度为6，主键； 书名：统一字符编码可变长类型，长度为30，非空； 第一作者：普通编码定长字符类型，长度为10，非空； 出版日期：小日期时间型；
价格：定点小数，小数部分1位，整数部分3位。 （2）“书店表”结构如下：
书店编号：统一字符编码定长类型，长度为6，主键； 店名：统一字符编码可变长类型，长度为30，非空；
电话：普通编码定长字符类型，8位长，每一位的取值均是0～9的数字； 地址：普通编码可变长字符类型，40位长。 邮政编码：普通编码定长字符类型，6位长。 （3）“图书销售表”结构如下：
书号：统一字符编码定长类型，长度为6，非空； 书店编号：统一字符编码定长类型，长度为6，非空； 销售日期：小日期时间型，非空； 销售数量：小整型，大于等于1。
主键为（书号，书店编号，销售日期）；
其中“书号”为引用“图书表”的“书号”的外键； “书店编号”为引用“书店表”的“书店编号”的外键。 答：
CREATE TABLE 图书表 ( 书号 nchar(6) primary key, 书名 nvarchar(30) not null, 第一作者 char(10) not null, 出版日期 smalldatetime, 价格 numeric(4,1))
CREATE TABLE 书店表 (
书店编号 nchar(6) primary key, 店名 nvarchar(30) not null,
电话 char(8) check (电话 like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), 地址 varchar(40), 邮政编码 char(6))
CREATE TABLE 图书销售表 ( 书号 nchar(6) not null,
书店编号 nchar(6) not null,
销售日期 smalldatetime not null,
销售数量 smallint check(销售数量>=1), primary key(书号,书店编号,销售日期), foreign key(书号) references 图书表(书号),
foreign key(书店编号) references 书店表(书店编号))
6
13．为第12题的图书表添加“印刷数量”列，类型为整数，同时添加约束，要求此列的取值要大于等于1000。
答：ALTER TABLE 图书表
ADD 印刷数量 int check (印刷数量>=1000)
14．删除第12题的“书店表”中的“邮政编码”列。 答：ALTER TABLE 书店表
DROP COLUMN 邮政编码
15．将第12题的“图书销售表”中的“销售数量”列的数据类型改为整型。 答：ALTER TABLE 图书销售表
ALTER COLUMN 销售数量 int
16．索引的作用是什么？分为哪几种类型？
答：索引的作用是为了加快数据的查询速度。索引分为聚集索引和非聚集索引两种。
17．在一个表上可以创建几个聚集索引？可以创建多个非聚集索引吗？ 答：在一个表上只能创建一个聚集索引。可以创建多个非聚集索引。
18．聚集索引一定是唯一索引，对吗？反之呢？
答：不对。唯一索引可以是聚集的也可以是非聚集的。
19．在建立聚集索引时，系统是真正将数据按聚集索引列进行物理排序，对吗？在建立非聚
集索引时呢？情况又如何？
答：对。但在建立非聚集索引时，系统并不物理地调整数据的排列顺序。
20．在第12题的图书表的“第一作者”列上建立一个非聚集索引。 答：CREATE INDEX indAuthor ON 图书表 (第一作者)
21．在第12题的书店表的“电话”列上建立一个聚集的唯一索引。 答：CREATE UNIQUE CLUSTERED INDEX indPhone
ON 书店表(电话)
22．在第12题的图书销售表的“书号”和“销售日期”两个列上建立一个非聚集索引。 答：CREATE INDEX indSale ON 图书销售表 (书号, 销售日期)
7
第4章 数据操作
1．查询SC表中的全部数据。 答：select * from SC
2．查询计算机系学生的姓名和年龄。
答：select sname,sage from student where sdept = '计算机系'
3．查询成绩在70～80分的学生的学号、课程号和成绩。
答：select sno,cno,grade from sc on where grade between 70 and 80
4．查询计算机系年龄在18～20岁的男学生的姓名和年龄。 答：select sname,sage from student
where sdept = '计算机系' and sage between 18 and 20 and ssex = '男'
5．查询C001课程的最高分。
答：select max(grade) from sc where cno = 'C001'
6．查询计算机系学生的最大年龄和最小年龄。
答：select max(sage) as max_age, min(sage) as min_age from student where sdept = '计算机系'
7．统计每个系的学生人数。
答：select sdept,count(*) from student group by sdept
8．统计每门课程的选课人数和考试最高分。
答：select cno,count(*),max(grade) from sc group by cno
9．统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。
答：select sno,count(*), sum(grade) from sc group by sno order by count(*) asc
10．查询总成绩超过200分的学生，要求列出学号和总成绩。
答：select sno,sum(grade) from sc group by sno having sum(grade) > 200
11．查询选修C002课程的学生的姓名和所在系。
答：select sname,sdept from student s join sc on s.sno = sc.sno where cno = ' C002'
12．查询成绩80分以上的学生的姓名、课程号和成绩，并按成绩降序排列结果。 答：select sname,cno,grade from student s join sc on s.sno = sc.sno where grade > 80 order by grade desc
13．查询哪些课程没有学生选修，要求列出课程号和课程名。
8
答：select c.cno,cname from course c left join sc on c.cno = sc.cno
where sc.cno is null
14．查询计算机系哪些学生没有选课，列出学生姓名。
答：select sname from student s left join sc on s.sno = sc.sno Where sdept = '计算机系' and sc.sno is null
15．用子查询实现如下查询：
（1）查询选修C001课程的学生的姓名和所在系。
答：select sname,sdept from student where sno in( select sno from sc where cno = ' C001')
（2）查询通信工程系成绩80分以上的学生的学号和姓名。 答：select sno,sname from student where sno in( select sno from sc where grade > 80) and sdept = '通信工程系'
（3）查询计算机系考试成绩最高的学生的姓名。
答：select sname from student s join sc on s.sno = sc.sno where sdept = '计算机系'
and grade = (select max(grade) from sc join student s on s.sno = sc.sno
where sdept = '计算机系')
（4）查询年龄最大的男学生的姓名和年龄。 答：select sname,sage from student
Where sage = (select max(sage) from student and ssex = '男') and ssex = '男'
（5）查询C001课程的考试成绩高于该课程平均成绩的学生的学号和成绩。 答：select sno,grade from sc where cno = ' C001'
And grade > (select avg(grade) from sc where cno = ' C001')
16．创建一个新表，表名为test_t，其结构为（COL1, COL 2, COL 3），其中， COL1：整型，允许空值。
COL2：字符型，长度为10，不允许空值。 COL3：字符型，长度为10，允许空值。
试写出按行插入如下数据的语句（空白处表示空值）。
COL1 1 2 COL2 B1 B2 B3 COL3 C2 答：create table test_t ( COL1 int,
COL2 char(10) not null,
9
COL3 char(10) )
insert into test_t values(NULL, 'B1', NULL) insert into test_t values(1, 'B2', 'C2')
insert into test_t(COL1, COL2) values(2, 'B3')
17．删除修课成绩小于50分的学生的选课记录。 答：delete from sc where grade < 50
18．将所有选修C001课程的学生的成绩加10分。
答：update sc set grade = grade + 10 where cno = 'C001'
19．将计算机系所有选修“计算机文化学”课程的学生的成绩加10分。 答：update sc set grade = grade + 10 where sno in(
select sno from student where sdept = '计算机系') and cno in(
select cno from course where cname = '计算机文化学')。
20．试说明使用视图的好处。
答：使用视图能够带来如下好处：
? 简化数据查询语句：用户可以将复杂的查询语句封装在视图中，这样以后用户在使
用相同的查询时，只需对视图进行查询即可。
? 使用户能从多角度看到同一数据：视图机制能使不同的用户以不同的方式看待同一
数据，当许多不同种类的用户共享同一个数据库时，这种灵活性是非常重要的。 ? 提高了数据的安全性：使用视图可以定制允许用户查看哪些数据并屏蔽掉敏感的数
据，从而提高数据库数据的安全性。
? 提供一定程度的逻辑独立性：视图对应数据库三级模式中的外模式，因此，可以将
用户对数据的操作限制在视图上，而不直接对模式进行操作，这样当模式发生变化时，视图可以不变。
21．使用视图可以加快数据的查询速度，这句话对吗？为什么？
答：不对。使用视图不但不会加快对数据的查询速度，而且还会降低数据查询速度。因为通
过视图查询数据时，要先将这个查询转换为对基本表的查询，有时这个转换是比较复杂的。因此，通过视图查询数据比直接对基本表查询要慢。
22．写出创建满足下述要求的视图的SQL语句。
（1）查询学生的学号、姓名、所在系、课程号、课程名、课程学分。 答：CREATE VIEW V1
AS
SELECT S.Sno, Sname, Sdept, C.Cno, Cname, Ccredit FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno
（2）查询学生的学号、姓名、选修课程名和考试成绩。
10