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

Please comment below if you find anything wrong in the above post
Feeling lost in the world of random DSA topics, wasting time without progress? It's time for a change! Join our DSA course, where we'll guide you on an exciting journey to master DSA efficiently and on schedule.
Ready to dive in? Explore our Free Demo Content and join our DSA course, trusted by over 100,000 geeks!

Last Updated :

Share your thoughts in the comments

GeeksforGeeks

Trending in News