Here is the schema:

CREATE TABLE Books ( isbn INT PRIMARY KEY, title VARCHAR(100), author VARCHAR(100), genre VARCHAR(50), publisher VARCHAR(50) );

CREATE TABLE Members ( id INT PRIMARY KEY, name VARCHAR(100) );

CREATE TABLE Lending ( isbn INT REFERENCES Books(isbn), id INT REFERENCES Members(id), checkout DATETIME, returned DATETIME, PRIMARY KEY (isbn, id, checkout) );

Find the titles of books that have been checked out by the same person more than once, and how many times. Return the book title, person name, and number of checkouts. E.g. "War and Peace" checked out by "Joe" three times should be in the output as the tuple ("War and Peace", "Joe", 3). If multiple people have the same name, they should each appear separately in the output.

The following questions will all use the schema we're about to describe for a library database. Books (isbn, title, author, genre, publisher) Members (id, name) Lending (isbn, id, checkout returned) // checkout and returned are both DATETIME A tuple in Books represents a single book in the library system (We're assuming the library doesn't have multiple copies of a book and each book has a single author and publisher). The Members table holds identifying information for people checking out books from the library. There may be multiple people with the same name, and different member ids. Lastly the Lending table shows a history of books loaned to various members. The checkout and returned attributes are the time stamps the book was checked out to a user and subsequently returned by that user. Any Lending tuple with a NULL "returned" attribute means that book is still currently checked out. People may return a book and then check out the same book out again, each unique checkout becomes a new tuple with the new checkout time. Books.isbn is the primary key for Books. Members.id is the primary key for Members Lending.isbn, Lending.id, and Lending checkout together constitute the primary key for Lending Lending isbn is a foreign key to Books.isbn Lending.id is s foreign key to Members.id. For all queries below you do not need sub-queries (but you may use them if you choose.)