MySQL Write one SQL statement to solve each of the following queries in Bookstor
ID: 3702454 • Letter: M
Question
MySQL
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
a
SELECT ISBN, Title, Subject_Code FROM Book WHERE Subject_Code IN (
SELECT Subject_Code FROM Subjects WHERE Subject = 'The Art of Walt Disney'
) ORDER BY Title;
b
SELECT Book.ISBN, Book.Title, Book.Subject_Code FROM Book
INNER JOIN Subjects ON Book.Subject_Code=Subjects.Subject_Code
WHERE Subjects.Subject = 'The Art of Walt Disney';
c
UPDATE table_name
SET Shelf_Location='KD-2222'
WHERE Subject_Code IN (
SELECT Subject_Code FROM Books GROUP BY Subject_Code HAVING COUNT(*)=(
SELECT MIN(counts) FROM (
SELECT count(*) AS counts, ShipperID FROM orders group by ShipperID
)
)
);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.