请使用下面的查询，但正确的方法是使用联接而不是多个子查询
连接

SELECT B.Title
FROM BOOK B
inner join BOOK_AUTHORS BA
on (B.bookid = ba.bookid)
inner join BOOK_COPIES BC
on (B.bookid = BC.bookid)
inner join LIBRARY_BRANCH LB
on (BC.branchid = LB.branchid)
where B.AuthorName = 'Stephen King'
and LB.BranchName = 'Central';

子查询

SELECT Title
FROM BOOK
WHERE BookID in
(
 SELECT BookID
 FROM BOOK_AUTHORS
 WHERE AuthorName = 'Stephen King'
)
AND
BookID in 
(
 SELECT BookID
 FROM BOOK_COPIES
 WHERE BranchID in
 (
  SELECT BranchID
  FROM LIBRARY_BRANCH
  WHERE BranchName = 'Central'
 )
);

你的版本显然没有回答这个问题，因为它没有返回计数。
处理这样的问题最常用的方法是 JOIN s和 GROUP BY :

SELECT b.Title, COUNT(lb.branch_id)
FROM BOOK_AUTHORS ba JOIN
     BOOK b
     ON ba.BookID = b.BookId LEFT JOIN
     BANK_COPIES bc
     ON ba.book_id = b.book_id LEFT JOIN
     LIBRARY_BRANCH lb
     ON lb.branch_id = bc.branch_id AND
        lb.BranchName = 'Central'
WHERE ba.AuthorName = 'Stephen King'
GROUP BY ba.BookID, ba.Title;

注意使用 LEFT JOIN . 这个问题似乎想要所有的标题，甚至那些没有副本。

对于此要求，可以使用联接来获得结果：

SELECT b.Title, bc.No_Of_Copies
FROM BOOK b 
INNER JOIN BOOK_AUTHORS ba ON ba.BookId = b.BookId
INNER JOIN BOOK_COPIES bc ON bc.BookId = b.BookId
INNER JOIN LIBRARY_BRANCH lb ON lb.BranchId = bc.BranchId
WHERE ba.AuthorName = 'Stephen King' AND lb.BranchName = 'Central'