[theNoob]

Hey guys,

I just came across a question that I am really stuck with. I have added the question below, I was wondering if someone can help me figure this out?

The following schema forms part of a database held in a relational DBMS.

------

Book ( BookID, Title, PubID ) BookLoans

( BookID, BranchID, CardNo, DateOut, DueDate ) The loan frequency of a book is the number of times a book has been lent.

Write a query to display book titles and their loan frequencies if the frequencies are higher than the average loan frequency of all books. The display should be in the order of book titles and loan frequencies. We do not consider the case there a book has never been lent (if we do, what will happen?).

------

So far I've only got this much done, I can't seem to think any further. Any assitance would be appreciated. Thanks in advanced.

SELECT b.title
FROM books b JOIN bookloans f
WHERE f.dateout 

[Duckington]

The question is worded a little confusingly... I assume that:

Quote

Write a query to display book titles and their loan frequencies if the frequencies are higher than the average loan frequency of all books.

Means that you only want to return rows where the number of loans its had is greater than the overall avg number of loans, in which case the

Quote

We do not consider the case there a book has never been lent (if we do, what will happen?)

Is redundant, as they will not be higher than the avg number of loans...


But anyway:

To find out the average frequency of the loans, you'll want to use the AVG() function.
To find records where the loan freq is only HIGHER than that, you'll just want a simple WHERE clause.
If you do want to include all records, even if they've never had a loan, you can use a LEFT JOIN rather than an INNER JOIN, so that the records are included even if there is no match. That record will then be cinluded with a "NULL" value.
Optionally you could also then use IFNULL() (or is it ISNULL()?) to set that value to 0 when it is returned as NULL.

[e_i_pi]

Duckington, on 27 July 2012 - 03:03 AM, said:

Quote

We do not consider the case there a book has never been lent (if we do, what will happen?)

Is redundant, as they will not be higher than the avg number of loans...

I think it might mean when calculating the average. So if we have two books, Book A and Book B, and Book A is loaned 0 times, Book B is loaned 2 times, then the average number of loans is 2, not 1. That's my understanding of it. The question has been articulated very poorly though. If this was a real life situation, I'd kick the specifications back over the fence and ask the client to try again, this time with their thinking cap on.

Quote

Optionally you could also then use IFNULL() (or is it ISNULL()?) to set that value to 0 when it is returned as NULL.

To clarify, it's ISNULL().

This post has been edited by e_i_pi: 26 July 2012 - 01:48 PM