This is database system concepts membermembno name dob books

This is database system concepts.

member(memb_no, name, dob)

books(isbn, title, atuthors, publisher)

borrowed(memb_no, isbn, date)

1. Find the name and membership number of members who have borrowed more than five different books published by “McGraw-Hill”.

2. For each publisher, find the name and membership number of members who have borrowed more than five books of that publisher.

3. Find the average number of books borrowed per member. Take into account that if an member does not borrow any books, then that member does not appear in the borrowed relation at all.

Solution

1. The name and membership number of members who have borrowed more than five different books published by “McGraw-Hill” is :

select name, memb_no

from member,borrowed

where member.memb_no=borrowed.memb_no and isbn in

( select distinct isbn

from books

where publisher=\'McGraw-Hill\')

group by member.memb_no, name

having count(isbn) >=5

2. For each publisher,the name and membership number of members who have borrowed more than five books of that publisher is :

select publisher,name

from

(select publisher, name, count isbn

from member m, books b, borrowed I

where m.memb_no = I.memb_no

and I.isbn = b.isbn

group by publisher,name)

as mempub (publisher, name, count books)

where count books>5

3. The average number of books borrowed per member which are taken into account that if an member does not borrow any books, then that member does not appear in the borrowed relation at all is :

select avg(cnt) from

(

select count(isbn), memb_no from borrowed group by memb_no

union

select 0, memb_no from member where not exists(select isbn from borrowed where borrowed.memb_no =member.memb_no)

) as books_count(cnt, memb_no)