数据库编程第三章课后作业

SELECT rid ,rName FROM reader r WHERE NOT EXISTS(
SELECT nif FROM boorrow WHERE rid=r.rid)

#第二题
SELECT r.rName,k.bName,b.willData FROM boorrow b
INNER JOIN book k ON k.bid=b.nif
INNER JOIN reader r ON r.rid = b.rid
 WHERE b.returnData IS NULL

#第三题
SELECT bName,bCount FROM book b WHERE NOT EXISTS(SELECT rid FROM boorrow WHERE nif = b.bid)

#第四题
SELECT rName,lendNum FROM reader WHERE rid in(
SELECT rid FROM boorrow WHERE returnData IS NOT NULL)


SELECT  reader.rid,reader.rName as '读者姓名',COUNT(nif)as '借书次数' FROM boorrow
INNER JOIN reader ON reader.rid=boorrow.rid
WHERE boorrow.returnData IS NOT NULL
GROUP BY reader.rid

#第五题
SELECT r.rName AS 姓名, p.amount AS 总金额 FROM penalty p  
INNER JOIN reader r ON r.rid=p.rid
WHERE p.amount>5



SELECT k.bName,COUNT(w.nif) FROM boorrow w
INNER JOIN book k ON k.bid=w.nif
INNER JOIN reader r ON r.rid= w.rid
 WHERE w.returnData IS NOT NULL
GROUP BY w.nif
LIMIT 5