Display book title, Category, publisher name, author first name and author last
ID: 3880733 • Letter: D
Question
Display book title, Category, publisher name, author first name and author last name for all books that have been co-authored and the title of the book has letter ‘SS’ anywhere and ends with letter ‘Z’. Order the result in ascending order by author last name.
Display the book Category and the average retail price after discount in all categories where average discounted price is less than the highest average retail price of books for all the categories. Sort the result in category order descending.
ORDERS Order# Customer# Orderdate Shipdate Shipstreet Shipcity Shipstate Shipzip Shipcost CUSTOMERS Customer# Lastname Firstname Email Address Ci Sta Zi Referred Region BOOKAUTHOR AUTHOR BOOKS AuthornD Lname Fname AuthorlD ORDERITEMS Order# I tem# ISBN Quantity Paideach Title Pubdate PublD Cost Retail Discount Category PUBLISHER PublD Name PROMOTIO Contact Phone Gift Minretal Maxretail FIGURE 1-5 JustLee Books's table structures after normalizationExplanation / Answer
Ans 1
select title,category ,p.name ,a.fname,a.lname from books b join publisher p on
b.pubID=p.pubID join ( select isbn,authorid from bookauthor where isbn in( select isbn from ( select isbn,count(authorid) from bookauthor group by isbn having count(authorid)>1))) m
on b.isbn=m.isbn
join author a on m.authorid=a.authorid
and b.title like '%SS%Z';
Ans 2-
select category,avg(retail-discount) from books group by category having avg(retail-discount)< (select max(val) from(select category, avg( retail) as val from books group by category));