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) );

1) Write a query to find the number of book checkouts that have occurred in each genre of book in the library. (Multiple checkouts of the same book are still multiple checkouts in that genre.) If a genre has had zero books checked out, its count should be 0 in the output. Return the genre and number of checkouts, sorted by number of checkouts in decreasing order.

2) Find the names of all people who have checked out both the books "Leaves of Grass" and "Harmonium". Return the id and name for each person who has checked out both books.

So far in class, we have used SQLite and SQL Server w/ Azure btw.

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.)