Problem 1 SQL [10 points] We define a database for FSU libraries as follow, Books: (bookID...

Transcribed Image Text:

Problem 1 SQL [10 points] We define a database for FSU libraries as follow, Books: (bookID INTEGER, title TEXT, genre TEXT, lib INTEGER REFERENCES Libraries, PRIMARY KEY (bookID)); Libraries: (libraryID INTEGER, name TEXT, PRIMARY KEY (libraryID)); Checkouts: (book INTEGER REFERENCES Books, day DATETIME, PRIMARY KEY (book, day)). 1. [3 points] Write a SQL query that returns the bookID and genre of each book that has ever been checked out; 2. [3 points] Write a SQL query that returns the bookID of the book that has been checked out the most times, and the corresponding checked-out count. We assume that each book was checked out a unique number of times; 3. [4 points] Write a SQL query that finds the name of all of the pairs of libraries that have books with matching titles. In the output, report the names of both libraries (the first name is alphabetically less than the second) and the matching titles of the books. Problem 1 SQL [10 points] We define a database for FSU libraries as follow, Books: (bookID INTEGER, title TEXT, genre TEXT, lib INTEGER REFERENCES Libraries, PRIMARY KEY (bookID)); Libraries: (libraryID INTEGER, name TEXT, PRIMARY KEY (libraryID)); Checkouts: (book INTEGER REFERENCES Books, day DATETIME, PRIMARY KEY (book, day)). 1. [3 points] Write a SQL query that returns the bookID and genre of each book that has ever been checked out; 2. [3 points] Write a SQL query that returns the bookID of the book that has been checked out the most times, and the corresponding checked-out count. We assume that each book was checked out a unique number of times; 3. [4 points] Write a SQL query that finds the name of all of the pairs of libraries that have books with matching titles. In the output, report the names of both libraries (the first name is alphabetically less than the second) and the matching titles of the books.

Answer rating: 100% (QA)

ANSWER 1 SELECT bookID genre FROM Books INNER JOIN Checkouts ON BooksbookID Chec... View the full answer