title: MySQL Query for Library Database
body: <p>I have a database project where I am struggling with three queries. Could you please help?</p>
  
  <p>I have the following tables:</p>
  
  <pre><code>member(memberNo, name, age)
  book(isbn, title, author, publisher, year, category)
  borrowed(memberNo, isbn, date)
  </code></pre>
  
  <p>And I have to answer each of the following questions by formulating a single SQL query (i.e. not to use views):</p>
  
  <p>1) For each category, find the names of members who have borrowed more than five books in that category.</p>
  
  <p>2) Find the most prolific author in the library, i.e., the author who has authored the most books.</p>
  
  <p>3) Find the names of members who have borrowed the same book more than once.</p>
  
  <p>thanks a lot</p>
  
  <p><strong>So far I have tried the following:</strong></p>
  
  <p>1) My Query:</p>
  
  <pre><code>SELECT member.name 
    FROM member 
   WHERE member.memberNo IN(SELECT borrowed.memberNo 
                              FROM borrowed 
                             GROUP 
                                BY memberNo 
                            HAVING COUNT(memberNo)&gt;5)
  </code></pre>
  
  <p>Result:
  This seems to return the correct result, but I haven’t figured out how to add ‘category’ as per the question.</p>
  
  <p>2) My Query:</p>
  
  <pre><code>SELECT author 
       , COUNT(*) AS 'Books Written' 
    FROM book 
   GROUP 
      BY author 
  HAVING COUNT(isbn)&gt;6
  </code></pre>
  
  <p>Result:
  This returns the correct result, but only because I have specified more than 6  – and that is because I know that I have an author who has written 7 books, therefore I am specifying ‘more than 6’ – but I am not sure how to write a query that will determine ‘most books written’ by itself</p>
  
  <p>3) My Query:  </p>
  
  <pre><code>SELECT member.name 
    FROM member 
   WHERE member.memberNo IN (SELECT borrowed.memberNo 
                               FROM borrowed 
                              WHERE borrowed.isbn IN(
                                     SELECT COUNT(borrowed.isbn) as 'con' 
                                       FROM borrowed 
                                      WHERE 'con' &gt;1))
  </code></pre>
  
  <p>Result:
  The query returns some rows with member names, but they are not correct – some of the members have simply borrowed more than one book (but not the same book more than once) but other members have not borrowed any books more than once – so the query is not correct.</p>