GATE | Sudo GATE 2020 Mock I (27 December 2019) | Question 61

Consider the following database schema:

BOOK(Book_id, Title, Publisher_name)
BOOK_AUTHORS(Book_id, Author_name)
PUBLISHER(Name, Address, Phone)
BOOK_COPIES(Book_id, Branch_id, No_of_copies)
BOOK_LOANS(Book_id, Branch_id, Card_no, Date_out, Due_date)
LIBRARY_BRANCH(Branch_id, Branch_name, Address)
BORROWER(Card_no, Name, Address, Phone) 

Note that BOOK_COPIES only contains entries where No_of_copies is at least 1 – if a branch doesn’t contain any copies of some book, there is no entry in BOOK_COPIES (not an entry with 0 copies).

Key attributes are underlined. The foreign key constraints are the following:



  • The Publisher_name in BOOK refers to the Name in PUBLISHER.
  • The Book_id attributes in BOOK_AUTHORS, BOOK_COPIES, and BOOK_LOANS all refer to the Book_id in BOOK.
  • The Branch_id attributes in BOOK_COPIES and BOOK_LOANS both refer to the Branch_id in LIBRARY_BRANCH.
  • The Card_no in BOOK_LOANS refers to the Card_no in BORROWER.

We want to find the titles of all of the books written by the author(s) who have written the largest number of books. Which of the following query is not correct ?
(Assume there can be tie.).
(A)

SELECT DISTINCT Title
    FROM BOOK NATURAL JOIN BOOK_AUTHORS
    WHERE Author_name IN ( SELECT Author_name
                           FROM BOOK NATURAL JOIN BOOK_AUTHORS
                           GROUP BY Author_name
                           HAVING COUNT(*) >= ALL ( SELECT COUNT(*)
                                                    FROM BOOK NATURAL JOIN BOOK_AUTHORS
                                                    GROUP BY Author_name ) )

(B)

SELECT DISTINCT Title
    FROM BOOK NATURAL JOIN BOOK_AUTHORS
    WHERE Author_name = ANY ( SELECT Author_name
                              FROM BOOK NATURAL JOIN BOOK_AUTHORS
                              GROUP BY Author_name
                              HAVING COUNT(*) >= ALL ( SELECT COUNT(*)
                                                       FROM BOOK NATURAL JOIN BOOK_AUTHORS
                                                       GROUP BY Author_name ) )

(C)

SELECT DISTINCT Title
    FROM BOOK NATURAL JOIN BOOK_AUTHORS A
    WHERE ( SELECT COUNT(*)
            FROM BOOK_AUTHORS A2
            WHERE A.Author_name=A2.Author_name ) >= ALL ( SELECT COUNT(*)
                                                          FROM BOOK NATURAL JOIN BOOK_AUTHORS
                                                          GROUP BY Author_name )

(D)

SELECT DISTINCT Title
    FROM BOOK NATURAL JOIN BOOK_AUTHORS
    WHERE Author_name = ( SELECT Author_name
                           FROM BOOK NATURAL JOIN BOOK_AUTHORS
                           GROUP BY Author_name
                           HAVING COUNT(*) >= ALL ( SELECT COUNT(*)
                                                    FROM BOOK NATURAL JOIN BOOK_AUTHORS
                                                    GROUP BY Author_name ) )


Answer: (D)

Explanation: Note that the following only works if there is not a tie i.e. there is a single author with the largest number of books:

SELECT DISTINCT Title
    FROM BOOK NATURAL JOIN BOOK_AUTHORS
    WHERE Author_name = ( SELECT Author_name
                           FROM BOOK NATURAL JOIN BOOK_AUTHORS
                           GROUP BY Author_name
                           HAVING COUNT(*) >= ALL ( SELECT COUNT(*)
                                                    FROM BOOK NATURAL JOIN BOOK_AUTHORS
                                                    GROUP BY Author_name ) )

Option (D) is correct.

Quiz of this Question



My Personal Notes arrow_drop_up
Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.