实验5 sql语句练习——图书馆数据库答案.doc

实验5 sql语句练习图书馆数据库实验5 sql语句练习图书馆数据库实验目的（1）了解SQL Server数据库的逻辑结构和物理结构；（2）了解表的结构特点；（3）了解SQL Server的基本数据类型；（4）了解空值概念；（5）学会在企业管理器中创建数据库和表；（6）学会使用T-SQL语句创建数据库和表。（7）学会使用T-SQL语句更新数据。（7）学会使用T-SQL语句创建多种查询。实验准备首先要明确，能够创建数据库的用户必须是系统管理员，或是被授权使用CREATE DATABASE语句的用户。其次创建数据库必须要确定数据库名、数据库大小（最初的大小、最大的大小、是否允许增长及增长方式）和存储数据库的文件。然后，确定数据库包含哪些表，以及所包含的各表的结构，还要了解SQL Server的常用数据类型，以创建数据库的表。此外还要了解两种常用的创建数据库、表的方法，即在企业管理器中创建和使用T-SQL的CREATE DATABASE语句。实验内容假设有5本书设有一图书馆数据库，其中包括3个表，即图书表、读者表和借阅表。三个表的结构如图：图书表结构列名说明数据类型约束说明书号图书唯一的编号定长字符串，长度为10主键书名图书的名称定长字符串，长度为50空值作者图书的编著者名定长字符串，长度为30空值出版社图书的出版社定长字符串，长度为30空值单价出版社确定的图书的单价浮点型，Float空值读者表结构假设有10位读者列名说明数据类型约束说明读者号读者唯一的编号定长字符串，长度为10主键姓名读者姓名定长字符串，长度为8非空值性别读者性别定长字符串，长度为2非空值办公电话读者办公电话定长字符串，长度为8空值部门读者所在部门定长字符串，长度为30空值借阅表结构列名说明数据类型约束说明读者号读者的唯一编号定长字符串，长度为10外码，引用读者表的主键书号图书的唯一编号定长字符串，长度为20外码，引用图书表的主键借出日期借出图书的日期定长字符串，长度为8非空值归还日期归还图书的日期定长字符串，长度为8空值 主键为：（读者号，图书号）（1） 用Sql语句创建图书馆数据库Create database Lab05（2） 用Sql语句创建上述3个表create table book(bookId char(10)primary key,bookName varchar(50),bookWriter varchar(30),bookPublish varchar(30),bookPrice float)create table reader(readerId char(10) primary key,readerName varchar(8)not null,readerSex char(2)not null,readerOfficeTel char(8),readerDepartment varchar(30)create table 借阅表(readerId char(10),bookId char(10),checkOutTime char(8),checkInTime char(8),primary key(readerId,bookId),foreign key (readerId) references reader(readerId),foreign key (bookId) references book(bookId),)（3） 基于图书馆数据库的3个表，用sql语言完成一下操作：1） 为图书表增加一列“ISBN”，数据类型为CHAR(10)alter table book add ISBN char(10)2） 为刚添加的ISBN列增加缺省值约束，约束名为ISBNDEF，缺省值为7111085949ALTER TABLE book ADD CONSTRAINT ISBNDEF DEFAULT (7111085949) FOR ISBN3） 删除图书表中ISBN列增加的缺省值约束alter table book drop ISBNDEF4） 删除图书表中新增的ISBN列ALTER TABLE book DROP COLUMN ISBN5） 查询全体图书的图书号、书名、作者、出版社和单价select bookId,bookName,bookWriter,bookPublish,bookPricefrom book6） 查询全体图书的信息，其中单价打8折，并设置该列的别名为打折价select bookId,bookName,bookWriter,bookPublish,(bookPrice*0.8) as打折价from book7） 显示所有借阅者的读者号，并去掉重复行select distinct readerIdfrom 借阅表8） 查询所有单价在2030元之间的图书信息select bookId,bookName,bookWriter,bookPublish,bookPrice,ISBNfrom bookwhere bookPrice between 20 and 309） 查询机械工业出版社、科学出版社、人民邮电出版社的图书信息select bookId,bookName,bookWriter,bookPublish,bookPrice,ISBNfrom bookwhere bookPublish in(机械工业出版社 , 科学出版社,人民邮电出版社)10） 查询既不是机械工业出版社、人民邮电出版社、也不是科学出版社出版的图书信息select bookId,bookName,bookWriter,bookPublish,bookPrice,ISBNfrom bookwhere bookPublish not in(机械工业出版社 , 科学出版社,人民邮电出版社)11） 查询姓名的第二个字符是建，并且只有2个字的读者的读者号及姓名select readerId,readerNamefrom readerwhere readerName like _建12） 查询姓名不是以王、张或李开头的所有读者的读者号及姓名【方式一】查询出来的结果有问题！select readerId,readerNamefrom readerwhere readerName not in (王%,张%,李%)【方式二】select readerId,readerNamefrom readerwhere readerName not in(select readerNamefrom readerwhere readerName like王%or readerName like张%or readerName like李%)13） 查询无归还日期的借阅信息select book.bookId,book.bookName,reader.readerId,reader.readerName,借阅表.checkOutTime as 借书时间,借阅表.checkInTime as 还书时间from 借阅表,book,readerwhere 借阅表.bookId = book.bookIdand 借阅表.readerId = reader.readerIdand 借阅表.checkInTime is null14） 查询机械工业出版社图书的平均价格、最高价、最低价select avg(bookPrice)as 平均价格,max(bookPrice) as 最高价 ,min(bookPrice) as 最低价from bookwhere bookPublish = 机械工业出版社图书15） 查询读者的基本信息及借阅情况select reader.readerId,reader.readerName ,借阅表.bookId,book.bookName,book.bookPublishfrom reader , 借阅表 ,bookwhere reader.readerId = 借阅表.readerIdand book.bookId = 借阅表.bookIdand 借阅表.readerId =100000000716） 查询至少借阅过1本机械工业出版社出版的图书的读者的读者号、姓名、书名及借阅本数，并按借阅本书多少降序排列select r.Rno,Rname,count(borrow.Bno) 借阅册数 from borrow,b,rwhere b.bno=borrow.bno and press=机械工业出版社 and borrow.rno=r.rnogroup by r.rno,Rnameorder by count(borrow.Bno) desc17） 查询与王小平的办公电话相同的读者的姓名/* 使用“自连接方式”求解 */select b.readerName,b.readerId,a.readerOfficeTelfrom reader a, reader bwhere a.readerName like 王小平and a.readerOfficeTel = b.readerOfficeTel/*18） 查询所有单价小于平均单价的图书的书号、书名及出版社select bookId,bookName,bookPublish