3.1 查询1991年出生的读者姓名、工作单位和身份证号。 SELECT readerName,workUnit,identitycard FROM Reader WHERE SUBSTRING(identitycard,7,4) =‘1991’ 3.2 查询图书名中含有“数据库”的图书的详细信息。 SELECT * FROM Book WHERE bookName LIKE ‘%数据库%’ 3.3 查询在2015-2016年之间入库的图书编号、出版时间、入库时间和图书名称，并按入库时间降序排序输出。 SELECT bookNo,bookName,publishingDate,shopDate FROM Book WHERE YEAR(shopDate) BETWEEN 2015 AND 2016 ORDER BY shopDate DESC 3.4 查询读者“喻自强”借阅的图书编号、图书名称、借书日期和归还日期。 SELECT Book.bookNo,bookName,borrowDate,returnDate FROM Book,Borrow WHERE Book.bookNo=Borrow.bookNo AND readerNo IN( SELECT readerNo FROM Reader WHERE readerName=‘喻自强’ ） 3.5 查询借阅了清华大学出版社出版的图书的读者编号、读者姓名、图书名称、借书日期和归还日期。 SELECT Reader.readerNo,readerName,bookName,borrowDate,returnDate FROM Reader,Borrow,Book,Publisher WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND Publisher. PublisherNo= Book. PublisherNo AND publisherName=‘清华大学出版社’ 3.6 查询上海生物研究室没有归还所借图书的读者编号、读者姓名、图书名称、借书日期和应归还日期。 SELECT Reader.readerNo,readerName,bookName,borrowDate,shouldDate FROM Reader,Borrow,Book WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND workUnit=‘上海生物研究室’ AND returnDate IS NULL 3.7 查询在2015-2016年之间借阅但没有归还图书的读者编号、读者姓名、读者工作单位以及这些借阅未归还图书的图书编号、图书名称和借书日期（分别使用IN子查询和存在量词子查询表达）。 SELECT a.readerNo,readerName,workUnit,c.bookNo,bookName, borrowDate FROM Reader a,Borrow b,Book c WHERE a.readerNo=b.readerNo AND b.bookNo=c.bookNo AND returnDate IS NULL AND a.readerNO IN( SELECT readerNo FROM Borrow WHERE YEAR(borrowDate) BETWEEN 2015 AND 2016 AND returnDate IS NULL ) 3.8 查询每种类别的图书分类号、分类名称、最高价格和平均价格，并按最高价格的降序输出。 SELECT Book.classNo,BookClass.className,MAX(price) AS maxPrice,AVG(price) AS avgPrice FROM Book,BookClass WHERE Book.classNo=BookClass.classNo GROUP BY Book.classNo,className ORDER BY MAX(price) DESC 3.9 查询每个读者在借（即借阅未归还）的图书数量、读者编号、读者姓名和工作单位，并按借书数量的降序排序输出。 SELECT Reader.readerNo,Reader.readerName,Reader.workUnit,COUNT() AS 未归还图书数量 FROM Reader,Borrow WHERE Reader.readerNo=Borrow.readerNo AND Borrow.returnDate IS NULL GROUP BY Reader.readerNo,Reader.readerName,Reader.workUnit 3.10查询每个出版社出版的每种类别的图书平均价格，要求显示出版社名称、图书类名称和平均价格。 SELECT Publisher.publisherName,BookClass.className,AVG(price) AS 平均价格 FROM Book,Publisher,BookClass WHERE Book.classNo=BookClass.classNo AND Book.publisherNo=Publisher.publisherNo GROUP BY Book.publisherNo, Publisher.publisherName,BookClass.className 3.11 查询所借图书的总价不低于200元的读者编号、读者姓名和所借图书的总价。 SELECT Reader.readerNo,readerName,SUM(price) AS money FROM Reader,Borrow,Book WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND returnDate IS NULL GROUP BY Reader.readerNo,readerName HAVING SUM(price)>200 3.12查询从来没有借过书的读者姓名和工作单位（分别使用IN子查询和存在量词子查询表达） –use IN SELECT readerName,workUnit FROM Reader WHERE readerNo NOT IN( SELECT readerNo FROM Borrow ) –use EXISTS SELECT readerName,workUnit FROM Reader WHERE NOT EXISTS( SELECT * FROM Borrow WHERE Reader.readerNo=Borrow.readerNo) 3.13 查询没有借阅“经济类”图书的读者编号、读者姓名和出生日期（分别使用IN子查询和存在量词子查询表达）。 –use IN SELECT Reader.readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthday FROM Reader,Borrow,Book WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND returnDate IS NULL AND Book.classNo NOT IN( SELECT classNo FROM BookClass WHERE className=‘经济类’ ) –use EXISTS SELECT Reader.readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthday FROM Reader,Borrow,Book WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND returnDate IS NULL AND NOT EXISTS ( SELECT * FROM BookClass WHERE className=‘经济类’ AND Book.classNo=BookClass.classNo ) 3.14 查询既借阅了“政治经济学”图书又借阅了“数据库系统概念”两本图书的读者编号、读者姓名以及这两种图书的图书名称、借书日期和归还日期。 SELECT Reader.readerNo,readerName,bookName,borrowDate,returnDate FROM Reader,Borrow,Book WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND (bookName=‘政治经济学’ OR bookName=‘数据库系统概念’) AND Reader.readerNo IN( SELECT readerNo FROM Borrow WHERE bookNo IN( SELECT bookNo FROM Book WHERE bookName=‘政治经济学’ ) ) AND Reader.readerNo IN( SELECT readerNo FROM Borrow WHERE bookNo IN( SELECT bookNo FROM Book WHERE bookName=‘数据库系统概念’ ) ) 或 SELECT Reader.readerNo,readerName,borrowDate,bookName FROM Reader,Borrow,Book WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND (bookName=‘政治经济学’ OR bookName=‘数据库系统概念’) AND Reader.readerNo IN (SELECT Reader.readerNo FROM Reader,Borrow,Book WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND bookName=‘政治经济学’ AND Reader.readerNo IN ( SELECT Borrow.readerNo FROM Borrow,Book WHERE Borrow.bookNo=Book.bookNo AND bookName=‘数据库系统概念’)) 3.15 查询借阅过图书名称中包含“数据库”的所有图书的读者编号、读者姓名以及他们所借阅的这些图书的图书名称、借阅日期和归还日期。 SELECT Reader.readerNo,Reader.readerName,Book.bookName,Borrow.borrowDate,Borrow.returnDate FROM Book,Reader,Borrow WHERE Book.bookNo=Borrow.bookNo AND Reader.readerNo=Borrow.readerNo AND returnDate IS NOT NULL AND bookName LIKE ‘%数据库%’ 3.16 查询至少借阅过读者“张小娟”所借阅过的所有图书的读者编号、读者姓名和工作单位。 SELECT readerNo,readerName,workUnit FROM Reader r WHERE NOT EXISTS( SELECT * FROM Borrow b1,Reader r1 WHERE b1.readerNo=r1.readerNo AND readerName=‘张小娟’ AND NOT EXISTS( SELECT * FROM Borrow b2 WHERE b2.bookNo=b1.bookNo AND B2.readerNo=r.readerNo ) ) 3.17 查询至少有3本在借图书的读者编号、读者姓名以及在借图书的图书编号、图书名称，并按读者编号升序、借阅日期降序排序输出。 SELECT Reader.readerNo,readerName,Book.bookNo,bookName FROM Reader,Borrow,Book WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND returnDate IS NULL AND Reader.readerNo IN( SELECT readerNo FROM Borrow WHERE returnDate IS NULL GROUP BY readerNo HAVING COUNT()>=3 ) ORDER BY Reader.readerNo 3.18 查询所借阅的图书（包括已归还的图书）总价最高的读者编号、读者姓名和出生日期。 SELECT readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthday FROM Reader WHERE readerNo IN( SELECT readerNo FROM Borrow,Book WHERE Borrow.bookNo=Book.bookNo GROUP BY readerNo HAVING SUM(price) >=ALL( SELECT SUM(price) FROM Borrow,Book WHERE Borrow.bookNo=Book.bookNo GROUP BY readerNo ))
第七章 7.2 在图书管理数据库BookDB中用SQL语句完成如下操作 （1） 将“经济管理”类图书的单价提高10%。 UPDATE Book SET price=price1.1 WHERE classNo IN( SELECT classNo FROM BookClass WHERE className=‘经济类’ ) （2） 将入库数量最多的图书单价下调5%。 UPDATE Book SET price=price0.95 WHERE shopNum = (SELECT MAX(SHOPNUM) FROM BOOK) （3）删除读者“张小娟”的借书记录 DELETE FROM Borrow WHERE readerNo IN (SELECT readerNo FROM Reader WHERE Reader.readerName=‘张小娟’) （4）创建一个视图，该视图为所借图书的总价在60元以上的读者编号、读者姓名和所借图书的总价。 CREATE VIEW BookView1 AS SELECT Reader.readerNo,readerName,SUM(price) AS money FROM Reader,Borrow,Book WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo GROUP BY Reader.readerNo,readerName HAVING SUM(price)>=60 （5）创建一个视图，该视图为年龄在25-35岁之间的读者，属性列包括读者编号、读者姓名、年龄、工作单位、所借图书名称和借书日期。 CREATE VIEW BookView2 AS SELECT Reader.readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthday,workUnit,bookName,borrowDate FROM Reader,Borrow,Book WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND year(getdate())-CONVERT(int,SUBSTRING(identitycard,7,4)) BETWEEN 25 AND 35 （6）创建一个视图，该视图仅包含“清华大学出版社”在2015–2016年出版的“计算机类”的图书基本信息。 CREATE VIEW BookView3 AS SELECT * FROM Book WHERE publisherNo = (SELECT publisherNo FROM Publisher WHERE publisherName= ‘清华大学出版社’) AND YEAR(publishingDate) IN(2015,2016) AND classNo=(SELECT classNo FROM BookClass WHERE className=‘计算机类’) （7）对由题（6）所建立的视图进行插入、删除和更新操作。 INSERT BookView3 VALUES(‘0000001’,‘003’,‘Linux网络技术’,‘王波’,‘P005’, 28,‘2014-5-1’, ‘2015-1-11’,80) DELETE BookView3 WHERE bookName LIKE ‘Linux%’ UPDATE BookView3 SET shopNum=shopNum+10
INSERT BookView3 VALUES(‘0000003’,‘003’,‘Linux网络技术’,‘王波’,‘P002’, 28,‘2015-5-1’, ‘2016-1-11’,80)
