Assignment:
Write SQL queries for the following tasks.
1. Display all the information of books on the subject "DataBases".
2. Display the first name and last name of the persons who borrow books on the subject "DataBases".
a. Write your query using a sub query.
b. Write your query using JOINs.
3. Who borrowed the book " COMPUTER SCIENCE "? Display the first name, middle names, and last name of the borrower.
4. Display the titles of books that are currently being borrowed.
5. A borrower wants to borrow the book titled " DATA PROCESSING CONCEPTS ", but all of its copies are already borrowed by others. Write two queries to display other recommended titles using the following methods.
a. Using partial matching of the book title -- note that the borrower is interested in a "DATA" book.
b. By searching of other books written by the same author (i.e. the author of DATA PROCESSING CONCEPTS
6. Display the list of publishers who have not published books on the subject "DataBases". Your query should display publishers full name.
7. Display the name of persons who have never borrowed any books.
a. Write your query using OUTER JOINs.
b. Write the query again without using OUTER JOINs.
8. Display full names of publishers with whom the author Alfred Aho did not publish his book(s). Your query must use IN (/NOT IN) clause.
9. Display the first name and last name of persons who had borrowed more than 3 books. Along with each name, display the number of books as well.
10. Display the name of the person who had borrowed most copies. If there are more than one person with the highest number of borrowing copies, show them all. Your query should show the names.
Provide detailed answers to the following questions.
11. According to this database schema, it is assumed that all booked borrowed in one transaction are due at the same time. However, this is an unreasonable assumption and quite contrary to the common practice across all forms of libraries. The books may be due on different days due to the renew activity.
Provide the required changes to the database schema to accommodate this requirement. Your answer should include a portion of the ER model where the changes are applied and the updated relational database schema. If required, you may add new relations and attributes and also new foreign keys.
12. Libraries allow customers to place holds onto book, but not borrow it immediately. It can be borrowed by the customer who places the hold within one week. This database schema cannot keep information on holding of books. Extend this schema to accommodate this requirement. Your answer should include a portion of the ER model where the changes are applied and the updated relational database schema. If required. you may add new relations and attributes and also new foreign keys.
13. The library allows customers to return the books in one transaction separately. Can this database schema support this operation? If so, how are they handled? If not, what changes required to the database schema?
Your answers should include a portion of the ER model where the changes are applied and the updated relational database schema. If required, you may add new relations and attributes and also new foreign.
Attachment:- SQL Queries.rar