You can access a sample database instance of this library database system using Oracle SQL Developer (either on myDesktop or lab machines) or sqlplus on core teaching servers (titan, jupiter or...

You can access a sample database instance of this library database system using Oracle SQL
Developer (either on myDesktop or lab machines) or sqlplus on core teaching servers (titan, jupiter
or saturn.) Please follow the instructions provided on the Blackboard to access the sample
database.
Username: rolibrary
Password: library
If you wish to do this part of the assignment from home, you can install SQLite (with SQLite
Studio). The instructions for installing, configuring and using SQLite Studio is provided in the
Databases and Tools section of the Blackboard. Also included is the pre-built Library database in
SQLite format (Library.db) at the same location.
Write SQL queries for the following tasks.
1. Display the titles of all books on the subject "DataBases". Your result set should be sorted
on the alphabetical order of the titles.
2. Display
a. the number of books on the subject "DataBases".
b. the number of book copies on the subject "DataBases".
3. Display the firstname and lastname of the authors who wrote books on the subject
"DataBases".
a. Write your query without using NATURAL JOINs.
b. Write your query using NATURAL JOINs.
4. Who translated the book "American Electrician's Handbook"? Display the firstname,
middlenames, and lastname of the translator.
5. Display the firstname and lastname of the people who returned books late.
6. Display the firstname and lastname of the people who returned books more than 7 days
late.
7. Display the titles of books that haven't been borrowed.
8. A borrower wants to borrow the book titled "PRINCIPLES AND PRACTICE OF DATABASE
SYSTEMS", 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
"DATABASE" book.
b. By searching of other books written by the same author (i.e. the author of
"PRINCIPLES AND PRACTICE OF DATABASE SYSTEMS"
9. Display the list of publishers who have published books on the subject "DataBases". Your
query should display publisher's full name, along with "DataBases" book titles they
published.
10. List the full names of publishers who have not published books on “Databases".