Question

Use the following information to create SQL commands to retrieve data from Henry Books database :

For each book, list the book code, book title, publisher code, and publisher name. Order the results by publisher name.
For each book published by Plume, list the book code, book title, and price.
List the book title, book code, and price of each book published by Plume that has a book price of at least $14.
List the book code, book title, and units on hand for each book in branch number 4.
List the book title for each book that has the type PSY and that is published by Jove Publications.
Find the book code and book title for each book located in branch number 2 and written by author 20.
Find the book title, author last name, and units on hand for each book in branch number 4.
Repeat Exercise 7, but this time list only paperback books.
Find the book code and book title for each book whose price is more than $10 or that was published in Boston.
Find the book code and book title for each book whose price is more than $10 and that was published in Boston.
Find the book code and book title for each book whose price is more than $10 but that was not published in Boston.

Answer 1

For each book, list the book code, book title, publisher code, and publisher name. Order the results by publisher name.

select b.book_code,b.title,b.publisher_code,p.publisher_name from book as b,publisher as p where p.publisher_code=b.publisher_code order by p.publisher_name

For each book published by Plume, list the book code, book title, and price.

select b.book_code,b.title,b.price from book as b, publisher as p where p.publisher_code=b.publisher_code and p.publisher_name='Plume'

List the book title, book code, and price of each book published by Plume that has a book price of at least $14.

select b.book_code,b.title,b.price from book as b, publisher as p where p.publisher_code=b.publisher_code and p.publisher_name='Plume' and b.price>=14

List the book code, book title, and units on hand for each book in branch number 4.

select b.book_code,b.title,i.on_hand from book as b, inventory as i,branch as br where b.book_code=i.book_code and i.branch_num=br.branch_num and i.branch_num=4

List the book title for each book that has the type PSY and that is published by Jove Publications.

select b.title from book as b, publisher as p where p.publisher_code=b.publisher_code and p.publisher_name='Jove Publications' and b.type='PSY'

Find the book code and book title for each book located in branch number 2 and written by author 20.

select b.book_code,b.title from book as b,copy as c,wrote as w where b.book_code=c.book_code and b.book_code=w.book_code and w.author_num=20 and c.branch_num=2

Find the book title, author last name, and units on hand for each book in branch number 4.

select b.book_title,a.author_last, i.on_hand from book as b,wrote as w, inventory as i, author as a where b.book_code=w.book_code and b.book_code=i.book_code and a.author_num=w.author_num and i.branch_num=4

Repeat Exercise 7, but this time list only paperback books.

select b.book_title,a.author_lastname, i.on_hand from book as b,wrote as w, inventory as i, author as a where b.book_code=w.book_code and b.book_code=i.book_code and a.author_num=w.author_num and i.branch_num=4 and b.paperback='Y'

Find the book code and book title for each book whose price is more than $10 or that was published in Boston.

select b.book_code,b.title from book as b,publisher as p where p.publisher_code=b.publisher_code and b.price>10 or p.city='Boston'

Find the book code and book title for each book whose price is more than $10 and that was published in Boston.

select b.book_code,b.title from book as b,publisher as p where p.publisher_code=b.publisher_code and b.price>10 and p.city='Boston'

Find the book code and book title for each book whose price is more than $10 but that was not published in Boston.

select b.book_code,b.title from book as b,publisher as p where p.publisher_code=b.publisher_code and b.price>10 and p.city<>'Boston'