综合设计题1

合肥学院2011—2012学年第2学期

合肥学院11-12学年第2学期《信息管理基础》综合设计题

管理 系 级 专业（ ）班 学号 姓名_________得分__ __ 1．library数据库中包含“图书”表和“读者”两表，两表结构分别定义如表1-1和表1-2所示，利用T-SQL语句创建两表。

表1-1 “图书”表结构 列名 图书编号 书名 类别 作者 数据类型 char(6) varchar(20) char(12) varchar(20) 备注

表1-2 “读者”表结构 列名 数据类备注 型 char(4) not null char(6) not null char(2) varchar(20) varchar(13) int int not null、读者编主键 号 not null 姓名 性别 单位 电话 读者类型 已借数量 出版社 varchar(20) 出版日dateti期 me 定价 (1)

money

use library go

create table 图书(

图书编号char(6) not null primary key, 书名varchar(20) not null, 类别char(12), 作者varchar(20), 出版社varchar(20), 出版日期datetime, 定价money)

(2)

use library go

create table 读者(

读者编号char(4) not null, 姓名char(6) not null, 性别char(2), 单位varchar(20), 电话 varchar(13), 读者类型int null, 已借数量int null,

constraint pk_读者_读者编号primary key(读者编号)

2．在library数据库中增加“读者类型”表和“借阅”两表，两表结构分别定义如表1-3和表1-4所示，利用T-SQL语句完成以下功能。

表1-3 “读者类型”表结构 列名 类型编号 类型名称 限借数量 借阅期限 数据类型 int char(8) int int 备注 not null not null not null

表1-4 “借阅”表结构

数据类备注 型 char(4) not null char(6) not null、外键 datetimnot null e datetim e 列名 读者编号 图书编号 借书日期 还书日期 （1）使用T-SQL语句在library数据库中创建“读者类型”表。 use library

go

create table 读者类型( 类型编号int not null, 类型名称char(6) not null, 限借数量int not null, 借阅期限int )

（2）使用T-SQL语句在library数据库中创建“借阅”表。 use library go

create table 借阅(

读者编号char(4) not null, 图书编号char(6) not null

foreign key references 图书(图书编号), 借书日期datetime not null, 还书日期datetime)

（3）使用T-SQL语句为“读者”表的“读者编号”列添加主键。

use library go

alter table 读者

add constraint pk_读者_读者编号 primary key(读者编号) go '

（4）使用T-SQL语句为“读者”表的“性别”列添加check约束，使之取值为男或女。

alter table 读者

add constraint ck_读者_性别check (性别='男' or 性别='女')

（5）使用T-SQL语句为“借阅”表增加“串号”列，数据类型为varchar(10)，并为主键。

alter table 借阅

add 串号varchar(10) primary key go

（6）使用T-SQL语句为“借阅”表的“借书日期”列添加一默认约束，取值为getdate()。

（7）使用T-SQL语句为“借阅”表的“读者编号”列增加一外键约束，关联于“读者”表的“读者编号”列。

alter table 借阅

add constraint fk_借阅_读者编号

foreign key(读者编号) references 读者(读者编号) go

（8）使用T-SQL语句为“借阅”表的“图书编号”列和“读者编号”列联合添加唯一约束。

alter table 借阅

add constraint uk_借阅

unique(图书编号,读者编号) go

表1-1 “图书”表结构 列名 图书编号 书名 类别 作者 出版社 出版日期 定价 数据类型 char(6) varchar(20) char(12) varchar(20) varchar(20) datetime money 备注 表1-2 “读者”表结构 列名 读者编号 姓名 性别 单位 电话 读者类型 已借数量

数据类型 char(4) char(6) char(2) varchar(20) varchar(13) int int 备注 not null not null not null、主键 not null 表1-3 “读者类型”表结构

列名 类型编号 类型名称 限借数量 借阅期限 数据类型 int char(8) int int 备注 not null not null not null 表1-4 “借阅”表结构 列名 数据类型 char(4) char(6) datetime datetime 备注 not null not null、外键 not null 读者编号 图书编号 借书日期 还书日期 根据以上表结构完成以下查询： 1．查询读者表的所有信息。 select * from 读者

2、查询图书表中图书种类。

select distinct 类别 from 图书

3．查阅读者编号为“1001”的读者的借阅信息。

select * from 借阅 where 读者编号=’1001’

4.查询图书表中“清华大学出版社”出版的图书书名和作者。 Select 书名,作者 from 图书 where 出版社=’清华大学出版社’ 5.查询书名中包含“程序设计”的图书信息。

Select * from rom 图书 where 书名 like ’%程序设计%’

6查询图书表中“清华大学出版社”出版的图书信息，结果按图书单价升序排列。 Select * from rom 图书 where 出版社=’清华大学出版社’ oder by 单价 7查询图书定价最高的前3个图书的图书编号、定价。 Select top 3 图书编号,定价 from图书 oder by 单价 desc

8查询借阅了“C语言程序设计”图书借阅日期最近的3名读者的读者编号和借书日期。 Select top 3 读者编号,借书日期from借阅,图书 where借阅.图书编号=图书.图书编号 and 书名=’C语言程序设计’ oder by 借书日期 desc

4．查询图书馆的藏书量。

Select count(*) as 藏书数量 from图书 10、查询图书馆的图书总价值。 Select sum（定价）from图书

11．查询各出版社的馆藏图书数量。

select 出版社,count(*) from 图书 group by 出版社

12．查询2011-10-1和2012-10-1之间各读者的借阅数量（不能利用列“已借数量”）。

Select 读者编号, count(*) from 借阅 where 借书日期 between ‘2011-10-1’ and ‘2012-10-1’ group by 读者编号

13．查询2011-10-1和2012-10-1之间作者为“谭浩强”的图书的借阅情况。

select * from 借阅 where 图书编号=（select 图书编号 from 图书 where 作者=‘谭浩强’）'安徽' compute count(籍贯) and 借书日期 between ‘2011-10-1’ and ‘2012-10-1’

14．使用统计函数计算读者表中的借阅数量最多、最少和平均借阅数。

Select max(已借数量),min(已借数量),avg(已借数量) from 读者

15．使用统计函数计算读者表中每个单位门课的借阅数量最多、最少和平均借阅数。 Select单位,max(已借数量),min(已借数量),avg(已借数量) from 读者 group by 单位

16．查询借阅图书数量超过2的读者编号、借阅数量（不能利用列“已借数量”）。 Select 读者编号,count(*) as 借阅数量from 借阅 group by 读者编号 having count(*)>=2

17．查询馆藏图书最多的作者姓名及馆藏数量，并存储到一个新表author中。 select count(*) as 馆藏数量,作者 into author from 图书 group by 作者

18．为读者类型表列借阅期限之后增加一个说明列，说明列名：日。 Select *,‘日’ from 读者类型

19．查询所有男教师和所有男生，并标示身份。

select 姓名,性别,'学生' as 身份 from 学生 where 性别 = '男' union

select 姓名,性别,'教师' as 身份 from 教师 where 性别 = '男'

20．利用子查询，查询借阅了图书的读者信息。

Select * from 读者 where exists (select * from 借阅 where 读者编号=读者.读者编号) 得 分 评卷人 三、写出每条T-SQL语句的功能（每小题2.5分，共25分）

1．用自连接查询年龄相同的学生。

1、select a.姓名,a.出生日期,b.姓名,ｂ.出生日期from 学生a,学生b where a.出生日期=b.出生日期 and a.学号!=b.学号

2．为学生表列总分之前增加一个说明列，说明列名：高考投档成绩。 2、select 学号,'高考投档成绩',总分from 学生

3．查询学生表中籍贯种类。 3、select distinct(籍贯) from 学生

4．查询学生表中男女生的平均总分。

4、select avg(总分) from 学生 group by 性别

5．查询学生表中来自安徽的学生。 5、select * from 学生where 籍贯='安徽'

6．分别查询学生表中年龄最小的男生信息和女生信息。 6、select * from 学生 where 性别='女'

and 出生日期=(select max(出生日期) from 学生where 性别='女') go

select * from 学生 where 性别='男'

and 出生日期=(select max(出生日期) from 学生where 性别='男')

7．查询学生的信息，并按照总分降序输出。 7、select* from 学生 order by 总分desc

8．查询选修表中成绩在85分以上的学生学号，姓名，性别和课程号，成绩信息。 8、select 学生.学号,姓名,性别,课程号,成绩 from 学生,选修 where 学生.学号=选修.学号 and 成绩>85

9．查询所有男教师和所有男生，并标示身份。

9、select 姓名,'学生'as 身份 from 学生 where 性别='男' union

select 姓名,'教师'as 身份 from 教师 where 性别='男'

10．查询学生表中出生日期在1990-1-1到1991-1-1之间学生。

10、select * from 学生where 出生日期 between '1990-1-1' and '1991-1-1'

4、select avg(总分) from 学生 group by 性别

5．查询学生表中来自安徽的学生。 5、select * from 学生where 籍贯='安徽'

6．分别查询学生表中年龄最小的男生信息和女生信息。 6、select * from 学生 where 性别='女'

and 出生日期=(select max(出生日期) from 学生where 性别='女') go

select * from 学生 where 性别='男'

and 出生日期=(select max(出生日期) from 学生where 性别='男')

7．查询学生的信息，并按照总分降序输出。 7、select* from 学生 order by 总分desc

8．查询选修表中成绩在85分以上的学生学号，姓名，性别和课程号，成绩信息。 8、select 学生.学号,姓名,性别,课程号,成绩 from 学生,选修 where 学生.学号=选修.学号 and 成绩>85

9．查询所有男教师和所有男生，并标示身份。

9、select 姓名,'学生'as 身份 from 学生 where 性别='男' union

select 姓名,'教师'as 身份 from 教师 where 性别='男'

10．查询学生表中出生日期在1990-1-1到1991-1-1之间学生。

10、select * from 学生where 出生日期 between '1990-1-1' and '1991-1-1'

本文来源：https://www.bwwdw.com/article/51gv.html