部分查询练习题及答案

1、通过SQL语句创建图书信息管理数据库，命名为“db_Library”，数据文件和日志文件放在D盘下以自己学号和姓名命名的文件夹中，数据文件的逻辑名为db_Library_data，数据文件的操作系统名为db_Library_data.mdf，文件初始大小为10MB，最大可增加至300MB，增幅为10%；日志文件的逻辑名为db_Library_log，日志文件的操作系统名为db_Library_data.ldf，文件初始大小为5MB，最大可增加至200MB，增幅为2MB。
CREATE DATABASE db_Library
ON PRIMARY (
NAME=db_Library_data,
FILENAME=‘D:\DATA\db_Library_data.mdf’,
SIZE=10MB,
MAXSIZE=300,
FILEGROWTH=10%)
LOG ON (
NAME=db_Library_log,
FILENAME=‘D:\DATA\db_Library_data.ldf’,
SIZE=5,
MAXSIZE=200,
FILEGROWTH=2 )
2、通过SQL语句在该数据库中创建模式L_C。
（二）创建和管理数据表
要求为各数据表的字段选择合适的数据类型及名称；为各数据表设置相应的完整性约束条件。
1、通过SQL语句将以下数据表创建在L_C模式下：
课程信息表（tb_course）——课程编号、课程名、先修课、学分
USE db_Library go
CREATE SCHEMA L_C GO
CREATE TABLE L_C.tb_course (
课程编号 char(10) primary key,
课程名 varchar(30) not null,
先修课 char(10),
学分 real
)

CREATE TABLE tb_course (
课程编号 char(10) primary key,
课程名 varchar(30) not null,
先修课 char(10),
学分 real
)

2、通过SQL语句将以下数据表创建在该数据库的默认模式dbo下：
图书类别信息表（tb_booktype)——类别编号、类别名称
CREATE TABLE tb_booktype (
类别编号 char(5) primary key,
类别名称 varchar(30) not null
)
图书信息表（tb_book）——图书编号、类别编号、书名、作者、出版社、定价、库存数
CREATE TABLE tb_book (
图书编号 char(10) primary key,
类别编号 char(5),
书名 varchar(30) not null,
作者 varchar(10),
出版社 varchar(50),
定价 money,
库存数 int default 0
)
读者信息表（tb_reader）——读者编号、姓名、性别、学号、班级、系部
CREATE TABLE tb_reader (
读者编号 char(10) primary key,
姓名 varchar(10) unique,
性别 char(2),
学号 char(11),
班级 char(10),
系部 varchar(30)
)
借阅信息表（tb_borrow)——图书编号、读者编号、借阅日期、归还日期
CREATE TABLE tb_borrow (
图书编号 char(10),
读者编号 char(10),
借阅日期 datetime,
归还日期 datetime,
primary key(图书编号,读者编号)
)
3、通过SQL语句对读者信息表进行修改：删除系部字段、添加所在系字段。
ALTER TABLE tb_reader
DROP COLUMN 系部

ALTER TABLE tb_reader
ADD 所在系 varchar(30)
4、通过SQL语句对图书信息表进行修改：将定价的数据类型改为REAL。
ALTER TABLE tb_book
ALTER COLUMN 定价 real
5、通过SQL语句删除课程信息表。
DROP TABLE L_C.tb_course
（三）创建和删除索引
1、使用SQL语句在图书信息表上创建一个非聚簇索引IX_S_QUANTITY，要求按照该表中库存数字段的降序创建。
CREATE NONCLUSTERED INDEX IX_S_QUANTITY
ON tb_book(库存数 desc)
2、使用SQL语句在读者信息表上创建一个唯一的非聚簇索引IX_S_NAME，
要求按照该表中的姓名字段的升序创建。
CREATE UNIQUE NONCLUSTERED INDEX IX_S_NAME
ON tb_reader(姓名 asc)
3、使用SQL语句删除之前创建的两个索引。
DROP INDEX tb_reader.IX_S_NAME

（1）查询每本图书的所有信息；
USE db_LibraryNew
go
SELECT * FROM tb_book
（2）查询每个读者的读者编号、姓名和班级；
SELECT 读者编号,姓名,班级 FROM tb_reader
（3）查询每条借阅记录的借阅天数（函数DATEDIFF获取两个日期的差）；
select DATEDIFF(WEEK,借阅日期,归还日期)
from tb_borrow
（4）查询被借阅过的图书的图书编号；
select distinct 图书编号
from tb_borrow
（5）查询图书编号为“10006”的书名和作者；
select 书名,作者
from tb_book
where 图书编号=‘10006’
（6）查询库存数在5到10本之间的图书的图书编号和书名；
select 图书编号,书名
from tb_book
where 库存数 between 5 and 10
（7）查询计算机系或电子系姓张的读者信息；
select *
from tb_reader
where 姓名 like ‘张%’ and 所在系 IN (‘计算机系’,‘电子系’)
（8）查询书名包括“英语”的图书信息；
select *
from tb_book
where 书名 like ‘%英语%’
（9）统计男读者、女读者的人数；
select 性别,COUNT() 人数
from tb_reader
group by 性别
（10）统计各类图书的类别编号、平均定价以及库存总数；
select 类别编号,AVG(定价) 平均定价,SUM(库存数) 库存综述
from tb_book
group by 类别编号
（11）统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列；
select 图书编号,COUNT(读者编号) 借阅数量
from tb_borrow
group by 图书编号
order by count(读者编号)
–order by 借阅数量
（12）查询有库存的各类别图书的类别编号、类别名称和借阅数量；
select tb_booktype.类别编号,类别名称,COUNT() 借阅数量
from tb_booktype,tb_borrow,tb_book
where tb_booktype.类别编号=tb_book.类别编号
and tb_book.图书编号=tb_borrow.图书编号
and 库存数>0
GROUP by tb_booktype.类别编号,类别名称（13）查询借阅了“大学英语”一书的读者，
输出读者姓名、性别、系部；
select 姓名,性别,所在系
from tb_book,tb_borrow,tb_reader
where tb_book.图书编号=tb_borrow.图书编号
and tb_reader.读者编号=tb_borrow.读者编号
and 书名=‘大学英语’
（14）查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期；（LEFT OUTER JOIN）

（15）查询现有图书中价格最高的图书，
输出书名、作者、定价；
select 书名,作者,定价
from tb_book
where 定价=
(
select MAX(定价)
from tb_book)
（16）查询借阅了“大学英语”但没有借阅“C++程序设计”的读者，输出读者姓名、性别、系部；
select 姓名,性别,所在系
from tb_reader
where 读者编号 in
(select 读者编号
from tb_borrow,tb_book
where tb_book.图书编号=tb_borrow.图书编号
and 书名=‘大学英语’
)
and 读者编号 not in
(
select 读者编号
from tb_borrow,tb_book
where tb_book.图书编号=tb_borrow.图书编号
and 书名=‘C++程序设计’
)
（17）统计借阅了2本以上图书的读者信息；
select 读者编号,COUNT(图书编号) 借阅数量
from tb_borrow
group by 读者编号
having COUNT(图书编号)>2
（18）查询借阅了“大学英语”一书或者
借阅了“C++程序设计”一书的读者信息；（用集合查询完成）
select *
from tb_borrow,tb_book,tb_reader
where tb_book.图书编号=tb_borrow.图书编号
and tb_reader.读者编号=tb_borrow.读者编号
and 书名=‘大学英语’
union
select *
from tb_borrow,tb_book,tb_reader
where tb_book.图书编号=tb_borrow.图书编号
and tb_reader.读者编号=tb_borrow.读者编号
and 书名=‘C++程序设计’
（19）查询既借阅了“大学英语”一书又借阅了“C++程序设计”一书的读者信息；（用集合查询完成）
select *
from tb_borrow,tb_book,tb_reader
where tb_book.图书编号=tb_borrow.图书编号
and tb_reader.读者编号=tb_borrow.读者编号
and 书名=‘大学英语’
intersect
select *
from tb_borrow,tb_book,tb_reader
where tb_book.图书编号=tb_borrow.图书编号
and tb_reader.读者编号=tb_borrow.读者编号
and 书名=‘C++程序设计’
20）查询计算机系中比其他系所有读者借书数量都多的读者的信息；
select * from tb_reader
where 读者编号 in
(select tb_borrow.读者编号
from tb_borrow,tb_reader
where tb_borrow.读者编号=tb_reader.读者编号
and 所在系=‘计算机系’
group by tb_borrow.读者编号
having COUNT(图书编号)>all
(
select COUNT(图书编号)
from tb_borrow,tb_reader
where tb_borrow.读者编号=tb_reader.读者编号
and 所在系!=‘计算机系’
group by tb_borrow.读者编号)
)
（21）在读者信息表中插入一条新的记录（读者编号：R10011；姓名：张三；所在系：电子系）；
insert into tb_reader(读者编号,姓名,所在系)
values(‘R10011’,‘张三’,‘电子系’)
（22）定义一个表tb_bknew，
包含图书编号、书名和类别名称字段，要求将类别编号为“3”的图书的图书编号、书名和类别名称插入到tb_bknew表中；
select 图书编号,书名,类别名称
into tb_bknew1
from tb_book,tb_booktype
where tb_book.类别编号=‘3’
and tb_book.类别编号=tb_booktype.类别编号

select * from tb_bknew1
（23）将类别编号为“3”的所有图书的库存数增加5；
update tb_book set 库存数=库存数+5 where 类别编号=‘3’
（24）将“C++程序设计”这本书的归还日期增加一个月
（函数DATEADD）。
update tb_borrow
set 归还日期=DATEADD(month,1,归还日期)
where 书名=‘C++程序设计’
（25）删除姓名为“张三”的读者的信息；
DELETE FROM tb_reader
where 姓名=‘张三’
（26）删除tb_bknew表中的所有数据；
delete from tb_bknew

truncate table tb_bknew