•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the Database Design section within the Web Development category of DaniWeb, a massive community of 391,956 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,882 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Database Design advertiser:
Views: 2431 | Replies: 6
 |
•
•
Join Date: May 2006
Posts: 3
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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
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
•
•
Join Date: Jul 2005
Location: Dallas, TX
Posts: 481
Reputation:
Rep Power: 4
Solved Threads: 19
Posting Pro in Training
so, what is your question?
•
•
Join Date: May 2006
Posts: 3
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
those questions thet i wrote. if anyone could do coupel of them would be fine
•
•
Join Date: Jul 2005
Location: Dallas, TX
Posts: 481
Reputation:
Rep Power: 4
Solved Threads: 19
Posting Pro in Training
sorry, we don't do homework for you. Give it a shot and ask specific questions if you need help
•
•
Join Date: May 2006
Posts: 3
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
okii well i did most of the questions for now if someone could give me help on 7th question 12, 15 and 16 dont know how to where to start
everytime i try nothing comes out
sorry for my spelling
anna
everytime i try nothing comes out
sorry for my spelling
anna
•
•
Join Date: Jul 2006
Posts: 5
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
actually it's very tough to analyse the bunch at a time,please specify your query
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb Database Design Marketplace
Linear Mode
- Create Access Database using SQL queries (MS Access and FileMaker Pro)
- SQL Queries with Borland Builder C++ (C++)
- Seeking help for how use My SQL and MS SQL (Community Introductions)
- QUICK! Access SQL using ASP and javascript (ASP)
- Saving information from .NET Listbox into SQL Server 2000 (ASP.NET)
- Do I really need an sql server (ASP)
- request for ansi sql queries equalent to MS access (Visual Basic 4 / 5 / 6)
- Working with SQL's Text data type (ASP.NET)
Other Threads in the Database Design Forum
- Previous Thread: what is the easiest to use database software?
- Next Thread: Distributed database with Postgres
