mysql query for finding the most checked out books

Tables

BOOK_COPIES (Book_ISBN [pk,fk], Branch_ID [pk,fk], Num_Copies)
BOOK_LOANS (Book_ISBN [pk,fk], Branch_ID [pk,fk],Card_Num [pk,fk], Date_Out,Date_Due)                          
BORROWER (Card_Num [pk], Name, Address, Phone)

Question

List the card number and name of the borrower who has checked out the most book.

Solution:

select br.cardnum,br.name from borrower br, bookloans bl
where br.card_num=bl开发者_如何学运维.card_num 
group by br.cardnum,br.name
having count(bl.book_isbn)>=all(
    select count(*) from bookloans bl
    group by bl.card_num
);

Can u please help me out in finishing the query.

seems like you're overthinking your homework assignment:

select BOOK_LOANS.Card_Num, Name, COUNT(Book_ISBN) AS BorrowedCount
from BOOK_LOANS
left join BORROWER on 
group by BOOK_LOANS.Card_Num
order by BorrowedCout desc
limit 1

no need for nested queries.