34,838
社区成员




7) 查询姓名的第二个字符是’建’,并且只有2个字的读者的读者号及姓名
select reader_id, reader_name from readers
where CHARINDEX('建',reader_name)=2 and LEN(reader_name)=2
8) 查询姓名不是以‘王’、‘张’或‘李’开头的所有读者的读者号及姓名
select reader_id, reader_name from readers
where left(reader_name,1) not in('王','张','或','李')
9) 查询无归还日期的借阅信息
select * from borrowinf where ghdate is null
10) 查询没有按照要求及时归还图书的读者号
select * from borrowinf where yhdate<ghdate
最后两题不写了,应该够你及格了
--说明:要不是你是考试,存在挂科的风险,这个我是不会给你写的
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
4) 查询所有单价在20—30元之间的图书信息
select * from books where price between 20 and 30
5) 查询机械工业出版社、科学出版社、人民邮电出版社的图书信息
select * from books where book_pre in('机械工业出版社','科学出版社','人民邮电出版社')
6) 查询既不是机械工业出版社、也不是科学出版社出版的图书信息
select * from books where book_pre not in('机械工业出版社','科学出版社')
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