3．    基于图书馆数据库的3个表，用T-SQL语言完成一下操作：
1）    查询全体图书的图书号、书名、作者、出版社和单价
    select * from books
2）    显示所有借阅者的读者号，并去掉重复行
    select distinct 读者编号 from borrowinf
3）    查询全体图书的信息，其中单价打8折，并设置该列的别名为‘打折价’
    select book_id. book_name, book_aut, book_pre, 0.8*price as 打折价
    from books

------解决方案--------------------

SQL code

1、select book_id. book_name, book_aut, book_pre from books 
2、select distinct reader_id from borrowinf 
3、select *,price*0.8 as '打折价' from books 
4、select * from books where price between 20 and 30
5、select * from books where book_pre in (N'机械工业出版社',N'科学出版社',N'人民邮电出版社')
6、select * from books where book_pre not in(N'机械工业出版社',N'科学出版社出版社')
7、select reader_id,reader_name from readers where reader_name like '_建'
8、select reader_id,reader_name from readers where '[!王张李]%'
9、select * from borrowinf where  yhdate is null or len(yhdate)=0
10、select reader_id from borrowinf where  ghdate> yhdate
11、select avg(price）as '平均价格',max(price) as '最高价格',min(price) as '最低价格' from books where book_pre =N'机械工业出版社'
12、select * from readers  r inner join borrowinf b on r.reader_id=b.reader_id
 inner join books bk on b.book_id=bk.book_id

------解决方案--------------------

在7楼的基础上简化下
1、select * from books  
2、select distinct reader_id from borrowinf  
3、select *,price*0.8 as '打折价' from books  
4、select * from books where price between 20 and 30
5、select * from books where book_pre in (N'机械工业出版社',N'科学出版社',N'人民邮电出版社')
6、select * from books where book_pre not in(N'机械工业出版社',N'科学出版社出版社')
7、select reader_id,reader_name from readers where reader_name like '_建'
8、select reader_id,reader_name from readers where '[!王张李]%'
9、select * from borrowinf where  yhdate is null or len(yhdate)=0
10、select reader_id from borrowinf where  ghdate> yhdate
11、select avg(price）as '平均价格',max(price) as '最高价格',min(price) as '最低价格' from books where book_pre =N'机械工业出版社'
12、select * from readers  r inner join borrowinf b on r.reader_id=b.reader_id
  inner join books bk on b.book_id=bk.book_id