hi my name is Anna and i
have got this problem
i am trying to practice sql queries using following database relational modell
i know most simple ones but with these ones i got trouble
it is about book lybrarry database using oracle.
The primary keys are underlined. The foreign keys are denoted by asterisks (*).
The schema for the LibraryDB database is given below.
borrow(transactionID, personID*, borrowdate, duedate, returndate)
author(authorID, firstname, middlename, lastname)
book_copy(bookID, 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)
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 make simple, most of the attributes have been truncated in the sample database.
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 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.
HOW TO using SQL
1.Display the titles of all books on the subject of DataBases. Your result set should be sorted on the alphabetical order of the titles.
2.Re-write the above query to display the book titles on the descending order of popularity. The popularity is measured by the number of times it has been borrowed.
3.Display the firstname and lastname of the authors who wrote books on the subject of DataBases.
Write your query without using NATURAL JOINs.
Write your query using NATURAL JOINs.
4.Who translated the book "American Electrician's Handbook" written by Jack Fisher, John McGregor, and Kay Nelson? Display the firstname, middlenames, and lastname.
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 late more than once.
7.List authors (firstname and lastname) who have co-authored books with John Lane.
Write your query without using nested queries.
Write the query again using IN clause.
Write it again using EXISTS clause.
8.Display the fistname and lastname of the other borrowers who have borrowed the same books which were borrowed by Nick Black.
9.Display the titles of books in the descending order of popularity. The popularity is measured by the number of times it has been borrowed.
10.Write another query to display the title of the most popular book. If there are more than one book with the highest popularity, display them all.
11.Display the titles of books that never borrowed.
Write your query using OUTER JOINs.
Write the query again without using OUTER JOINs.
12..List all the publishers along with any books on the subject of DataBases they may have published. Note that, your query should list all the publishers, even if they have not published books on DataBases.
13.With which publisher(s) the author Alfred Aho published his book(s)? Display publishers' full names.
14.Who are the authors published the books with MC GRAW-HILL publisher? Display the firstname and lastname of the authors.
15.Display the first name and lastname of authors who wrote more than 3 books. Along with each name, display the number of books as well.
16.Display the title of the book which has most physical copies. If there are more than one book with the largest number of copies, show them all. Your query should show the number of copies along with the title.
Thank you