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;