面试-MySQL(三)
文章目录
借书卡 卡号 姓名 班级
书 书号 书名 作者 单价 数量
借书记录 借书号 借书卡号 书号 还书时间
借书卡
书
借书记录
1.找出借书超过5本的读者,输出借书卡号及所借图书册数
SELECT card_id,COUNT(*) as num FROM BCR GROUP BY card_id HAVING COUNT(*)>5;
2.查找出价格最大的书信息
SELECT * FROM BOOK WHERE price = (SELECT MAX(price) FROM BOOK);
3.借过c书的学生信息
SELECT * FROM CARD ca WHERE EXISTS(SELECT * FROM BCR b,BOOK b1
WHERE b.book_id = b1.book_id AND b1.name = 'c' AND ca.card_id = b.card_id);
4.借过a书但是没有借b书的学生借书卡号,按卡号降序输出
SELECT a.card_id FROM BCR a,BOOK b
WHERE a.book_id=b.book_id AND b.name='a'
AND NOT EXISTS(
SELECT * FROM BCR aa,BOOK bb
WHERE aa.book_id=bb.book_id AND bb.name='b'
AND aa.card_id=a.card_id)
ORDER BY a.card_id DESC;
5.班级号为2的学生还书时间延迟一周
UPDATE BCR b,CARD c
SET b.return_time=DATE_ADD(b.return_time,INTERVAL 7 DAY)
WHERE b.card_id = c.card_id AND c.class_id = '2';
6.查询过期未还图书,输出借书记录信息
SELECT * FROM BCR WHERE return_time < NOW();
7.借过a书同时借b书的学生借书卡号,按卡号升序输出
SELECT a.card_id FROM BCR a,BOOK b
WHERE a.book_id=b.book_id AND b.name IN ('a','b')
GROUP BY a.card_id ORDER BY a.card_id ;