开发者

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))  
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜