第3章 课后作业

第2题

SELECT rid,rName FROM reader r WHERE NOT EXISTS (
    SELECT * FROM borrow b WHERE r.rid = b.rid            
);

第3题

SELECT (SELECT rName FROM reader WHERE reader.rid = b.rid)AS'读者姓名',(SELECT bName FROM book WHERE book.bid = b.nif)as'图书名称',b.willDate as '应归还日期'
FROM borrow b 
WHERE willDate <    NOW() 
AND returnDate IS NULL

第4题

SELECT bName,bCount FROM book WHERE NOT EXISTS (
    SELECT (SELECT bName FROM book) FROM borrow WHERE borrow.nif = book.bid
)

第5题

SELECT (
SELECT rName FROM reader WHERE reader.rid = borrow.rid)AS'读者姓名',
COUNT(rid)AS'借阅次数' 
FROM borrow WHERE returnDate is NOT NULL GROUP BY rid

第6题

SELECT (
SELECT rName 
FROM reader 
WHERE reader.rid = penalty.rid),sum(amount) 
FROM penalty 
GROUP BY rid 
HAVING sum(amount)>5

第7题

SELECT (SELECT bName FROM book WHERE book.bid=borrow.nif)AS'图书名称',COUNT(nif) AS'借阅次数' FROM borrow WHERE returnDate is not NULL GROUP BY nif DESC LIMIT 5;