I have four tables book,bookcopy,bookloan and customer. Book.book_id = Bookcopy.book_id and bookcopy.copy_id = bookloan.copy_id this is how the tables are joined together ignore the customer table for now.
I have to find the copies of books that have their number of hirings below the average hirings for copies of that book.
Imagine three copies of a particular book, one each in town A,B and C
Copy at A is hired 3 times
Copy at B is hired 4 times
Copy at C is hired 5 times
Average hiring per copy is 4
Copy C has above average hirings (for that particular book).
Is a good way to look at it.
I have been on this for the last 6 hours and have got the following SQL
SELECT book.book_id, NVL(location,'No Copies Held') AS Location, COUNT(bookcopy.copy_id) AS Number_Of_Copies
FROM book LEFT OUTER JOIN bookcopy
ON book.book_id = bookcopy.book_id
GROUP BY book.book_id,location
ORDER by book.book_id;
This just outputs the number of copies held for each book.
Does anyone have any idea at all on where to do? I am tearing my hair out over this.
Question
Vortex566
I have four tables book,bookcopy,bookloan and customer. Book.book_id = Bookcopy.book_id and bookcopy.copy_id = bookloan.copy_id this is how the tables are joined together ignore the customer table for now.
I have to find the copies of books that have their number of hirings below the average hirings for copies of that book.
Imagine three copies of a particular book, one each in town A,B and C
Copy at A is hired 3 times
Copy at B is hired 4 times
Copy at C is hired 5 times
Average hiring per copy is 4
Copy C has above average hirings (for that particular book).
Is a good way to look at it.
I have been on this for the last 6 hours and have got the following SQL
This just outputs the number of copies held for each book.
Does anyone have any idea at all on where to do? I am tearing my hair out over this.
Link to comment
Share on other sites
1 answer to this question
Recommended Posts