assignment sql for finding branches with atleast 2 copies of every boook
Q. List the branch id and name of all branches that have atleast 2 copies of every
book.
Tables:
BOOK (Book_ISBN [pk], Title, Publisher_Name [fk])
BOOK_COPIES (Book_ISBN [pk,fk], Branch_ID [pk,fk], Num_Copies)
LIBRARY_BANCH (Branch_ID [pk], Branch_Name, Address)
However, I tried doing this question but problem has been that开发者_开发知识库 checking for 2 copies.
You might find this article on relational division useful.
SELECT L.Branch_ID,
L.Branch_Name
FROM LIBRARY_BRANCH L
WHERE NOT EXISTS (SELECT *
FROM BOOK B
WHERE NOT EXISTS(SELECT *
FROM BOOK_COPIES BC
WHERE BC.Book_ISBN = B.Book_ISBN
AND Num_Copies >= 2
AND L.Branch_ID = BC.Branch_ID))
精彩评论