数据库编程——第二章，课后作业

1，使用子查询获得当前没有被读者借阅的图书信息

SELECT bName AS '图书名称',bid AS '图书编号',author AS '作者名称',pubComp AS '出版社',price AS '单价' FROM book WHERE bid NOT IN(
    SELECT nif FROM borrow
);
 

2，使用子查询获得今年的所有缴纳罚款的读者清单

SELECT reader.rName AS '读者姓名',book.bName AS '图书名称',pDate AS '罚款日期',amount AS '缴纳罚金'FROM (penalty INNER JOIN reader ON penalty.rid = reader.rid) INNER JOIN book ON book.bid = penalty.bid  WHERE YEAR(pDate)=YEAR(NOW());
3，使用子查询获得地址为空的所有读者尚未归还的图书清单

​
SELECT borrow.rid AS '读者编号',reader.rName AS '读者姓名',book.bName AS '图书名称',lendDate AS '借书日期',willDate AS '应归还日期',returnDate AS '实际归还日期' FROM (borrow INNER JOIN reader ON borrow.rid = reader.rid) INNER JOIN book ON book.bid = borrow.nif WHERE borrow.rid in(

SELECT reader.rid FROM reader WHERE rAddress IS NULL

)AND returnDate IS NULL ORDER BY borrow.rid DESC,lendDate ASC;

​