SQL查询练习：图书借阅信息统计和视图创建

(1)

SELECT BORROWER.借书证号, BORROWER.姓名, BORROWER.系名, COUNT() AS 借书数量 FROM BORROWER, LOANS WHERE BORROWER.借书证号 = LOANS.借书证号 GROUP BY BORROWER.借书证号, BORROWER.姓名, BORROWER.系名 HAVING COUNT() >= 5;

(2)

SELECT BORROWER.姓名, BORROWER.系名, BOOKS.书名, LOANS.借书日期 FROM BORROWER, LOANS, BOOKS WHERE BORROWER.借书证号 = LOANS.借书证号 AND LOANS.图书登记号 = BOOKS.图书登记号 AND EXISTS ( SELECT * FROM LOANS AS l, BOOKS AS b WHERE l.借书证号 = '欧阳' AND l.图书登记号 = b.图书登记号 AND LOANS.图书登记号 = b.图书登记号 );

(3)

CREATE VIEW vb AS SELECT BORROWER.借书证号, BORROWER.姓名, BORROWER.班级, LOANS.图书登记号, BOOKS.书名, BOOKS.出版社, LOANS.借书日期 FROM BORROWER, LOANS, BOOKS WHERE BORROWER.借书证号 = LOANS.借书证号 AND LOANS.图书登记号 = BOOKS.图书登记号;