**PART-3 MySQL Querying (30%, 60 points)** Open a new SQL tab when MySQL is conn
ID: 3699769 • Letter: #
Question
**PART-3 MySQL Querying (30%, 60 points)**
Open a new SQL tab when MySQL is connected.
Write one SQL statement to solve each of the following queries in Bookstore database.
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'.
Query 2: Solve Query 1 again by using a table join.
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
E dbo.Book EColumns ISBN (PK, nvarchar(13), not null) Title (nvarchar(50), null) Author (nvarchar(30), null) Publisher (nvarchar(30), null) o Subjct Code (FK, nvarchar(3), null) E Shelf_ Location (nvarchar7, null) B Fiction (bit, null) EEdbo.Subject Columns SubjectCode (PK, nvarchar(3), not null) E Subject (nvarchar(15), null)Explanation / Answer
Query 1:-
select s.ISBN,s.Title,s.Subject_Code from Book s where s.Subject_Code=(select b.Subject_Code from Book b where b.Title='The Art of Walt Disney') order by s.Title asc;
The above query will retrieve the subject code of the given book from the sub query and then it will retrieve all the given attributes information in the order which is given by us as ascending order of attribute Title which means in alphabetical order.
Query 2:-
select s.ISBN,s.Title,s.Subject_Code from Book s join Book b on b.Subject_Code=s.Subject_Code where Title= 'The Art of Walt Disney' order by s.Title asc;
The above query is self join query which joins the same table and using the where condition we are retreving the subject code and with this subject code column we are getting the required information.
Query 3:-
update Book b1 join (select b2.Subject_Code from Book b2 group by b2.Subject_Code having min(count(b2.Subject_Code))b3 on b3.Subject_Code=b1.Subject_Code set Shelf_Location='KD-2222';
The above query will update the shelf location of books that having min count.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.