Book Table has ISBN, TITLE, Author, Publisher. Subject_Code,Shelf_Location, Fict
ID: 3699943 • Letter: B
Question
Book Table has ISBN, TITLE, Author, Publisher. Subject_Code,Shelf_Location, Fiction as columns
Using MySQL
/*Query 1*/
/*Display ISBN, Title, and Subject_Code of books that have the same subject code as the book 'The Art of Walt Disney'.
The output should be sorted by title from a->z. This query must be solved by using a subquery.
No hard coded values are allowed in the statement except the book title 'The Art of Walt Disney'.Should return 4 books*/
SELECT ISBN, Title, Subject_Code
FROM Book
Where Subject_Code IN
(SELECT Subject_Code FROM book WHERE Title = 'The Art of Walt Disney')
ORDER BY Title;
/*Query 2*/
/*Solve Query 1 again by using a table join.Should return 4 books*/
/*Query 3*/
/*Update the Shelf_Location to KD-2222 for books of a Subject_Code that has the least number of books. For example,
if ART and PHL subjects each contains only two books and all other subjects have three or more books,
then the Shelf_Location of four books of ART and PHL should be updated. Hint: subquery*/
Explanation / Answer
Query 2:
SELECT b1.ISBN, b1.Title, b1.Subject_Code FROM Book b1 inner join Book b2 on b1.Subject_Code = b2.Subject_Code Where b2. Title = 'The Art of Walt Disney' ORDER BY Title;
this is self join of Book table between two instances
Query 3:
Update Book set Shelf_Location = 'KD-2222' where Subject_Code = ( Select Subject_Code from Book group by Subject_code having count(ISBN) = (Select min(count(ISBN)) from Book group by Subject_Code));
the innermost query will return the minimum number of books, then Subject_Code of that book is returned to update its Shelf_Location.
Do ask if any doubt . Please upvote.