Question

The schema for the LibraryDB database is given below. borrow(transactionID, personID*, borrowdate, duedate, returndate) author(authorID, firstname, middlename, lastname) book_copybookID, bookdescID*) book bookdescID, title, subtitle, edition, voltitle, volnumber, language, place, year, isbn, dewey, subjectID*) borrow_copy(transactionID*, bookID) person(personID, firstname, middlename, lastname, address, city, postcode, phonenumber, emailaddress, studentno, idcardno) publisher(publisherID, publisherfullname) written_by(bookdescID*, authorID*, role) published_by (bookdescID*, publisherID*, role) subject(subjectID, subjecttype)

The primary keys are underlined.
The foreign keys are denoted by asterisks (*).
Description of the schema:
• person — keeps track of the people who borrow books from the library. The attributes contain personal and contact information.
• author — keeps track of personal information about authors.
• publisher — keeps track of the publisher information. To keep it simple, most of the attributes have been truncated in the sample database. 1 trivial dependencies are things like X→X or X, Y→Y. Don’t include these in your answer.
• subject — this relation keeps information about the subjects on which the library collection have books (such as Mathematics, Database, etc)
• book — contains information about the books that are available in the library. Every book can have one or more physical copies in the collection. Each book can have one or more
• authors and it is published by one or more publishers.
• book_copy — keeps track of the physical copies of the books in the library collection.
• borrow — keeps track of the check-ins and check-outs of the books. Every transaction is done by one person, however it may involve with one or more book copies. If there is no return date, it means the book has been checked out but not returned.
• written_by — associates books with authors. A book may be associated with several authors and an author may be associated with several books. There is also an attribute ‘role’ that specifies the role of the author for the book (author/ editor/ translator/ etc).
• published_by — associates publishers with books. There is an attribute 'role' here too.
• borrow_copy — associates physical copies of books with a transaction. Members are allowed to borrow several books in a single transaction.

Imagine you have been put in charge of this technical library’s database. Write SQL queries for the following tasks. (The italicised text can be ignored, unless you’d like to follow the story.)

1. The library has been closed for a while but will be opening again soon and the library wants to inform its patrons. You have been asked to list the first name, last name and email address of all people registered as borrowers in the database, in last name, first name order.
2. It has been pointed out to the patron librarian that not everyone has an email address in the system. List the first name, last name, complete address and phone number for all people who don’t have an email address recorded.
3. The head librarian is concerned that the collection may be a bit dated. Output the earliest and latest year that books were published.
4. That didn’t really provide her with much information to go on. Provide the same information for each subject in the collection as well as the total number of books per subject. List each subject description, along with the number of books available for the subject, the earliest publication year and the latest publication year. Show these in ascending latest publication year order.
5. That was revealing. The Electronics collection seems to be particularly ancient. List all book details for books that are on electronics (i.e. whose subject is Electronics).
6. Despite this being a specialised technical library, the head librarian still wants to get an idea of where the largest gaps are in the collection. Using the round function on the Dewey numbers, list the number of books in each Dewey range (eg. 0, 100s, 200s) along with the Dewey range itself. For example, one row might have the Dewey range as 500 and the count of books as 41. Exclude nulls from your output. (Note, that this would ideally use a floor function, but that isn’t available in sqlite.)
7. Meanwhile, the patron librarian wants to know who tends to return books late. List the borrow date, due date and return date, as well as the person ID, first name and last name for borrowers who have returned books late.
8. As you are responsible for the database, you need to check it for consistency. Write a query to list complete book details for any books that don’t have a copy.
9. Your work is interrupted, as patrons are now trickling back into the library, physically distanced, but needing assistance. A patron wants your help in finding a book. They remember that the author’s last name starts with W and that there is a ‘Z’ in it somewhere. Write a query to list the titles, full author names and dewey number for all books that have an author whose last name starts with W and contains a ‘Z’.
10. It’s nearly the end of the workday and your mind has begun to wander. Write a query that lists the book title and number of authors for the book(s) with the most authors. Use LIMIT to limit your output to the top five books.
11. Now you’re curious about co-authors. Write a query that lists the IDs and names of all book co-authors. Exclude any duplicates and ensure that you don’t include authors co-authoring with themselves.
12. Just one more work query before your workday is over. Write a query that lists all books and the number of copies in the collection, including if there are no copies.

Answer 1

1. Please find below the query:

Select FirstName, LastName, Emailaddress from Person order by firstname, lastname;

Description: The query will fetch the data from Person table with specified columns. Order by clause will be used to order the data by first name and last name

Sample Output:

2. Please find below the query:

Select FirstName, LastName, Phonenumber, Address, City, PostCode from Person where emailaddress is null;

Description: The query will fetch the data from Person table with specified columns. The clause ‘where’ will be used on emailaddress column with condition ‘is null’

Sample Output:

3. Please find below the query:

select min(year) as Earliest_year, max(year) as Latest_year from book;

Description: The query will fetch the data from Book table. Aggregate function min() and max() will be used to return the latest and earliest year of book.

Sample Output:

4. Please find below the query:

select s.SubjectType as SubjectDescription, count(b.bookDescID) as TotalBooks , min(b.year) as Earlier_publication_year,

max(b.year) as Latest_publication_year from Subject S inner join Book b

on s.SubjectID = b.SubjectID group by s.SubjectID;

Description: The query will fetch the data from Book and Subject tables based on subject id in both tables. Subject Type will be fetched from Subject table. Count will return the total books, min and max will return the earliest and oldest year of book. The overall resultant will be grouped by SubjectID.

Sample Output:

Result is based on below sample data:

**As per HOMEWORKLIB RULES, providing answer to first 4 parts as required number of parts were not mentioned to be answered.