These queries are for the Database: Bookstore which has two tables: Subject and

Question

These queries are for the Database: Bookstore which has two tables: Subject and Book

Below is a snippet of the Book table

Below is a snippet of the Subject table

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

Explanation / Answer

1. SELECT b.ISBN, distinct b.TITLE,b.SUBJECT_CODE FROM BOOK as b WHERE b.SUBJECT_CODE = (SELECT s.SUBJECT_CODE FROM BOOK as s WHERE s.TITLE='The Art of Walt Disney') ORDER BY b.TITLE

2. Same query using join is:

SELECT b.ISBN, distinct b.TITLE,b.SUBJECT_CODE FROM BOOK as b INNER JOIN BOOK as s on s.TITLE=''The Art of Walt Disney' and s.SUBJECT_CODE = b.SUBJECT_CODE ORDER BY b.TITLE

the above query using the INNER JOIN returns the same result as in query 1.

3. UPDATE BOOK as b SET b.SHELF_LOCATION = 'KD-2222' WHERE b.SUBJECT_CODE =

( SELECT s.SUBJECT_CODE FROM BOOK as s GROUP BY s.SUBJECT_CODE HAVING COUNT(*) =

(SELECT MIN(c) FROM

(SELECT COUNT(*) as c FROM BOOK as t GROUP BY t.SUBJECT_CODE)))

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.