USA: +1 (725) 712-1730 
Sign in
Provide SQL query expressions / statements for the following queries:
a) Find the book title and price of historical books.
b) Find the name and email address of all authors who wrote at least one fiction but no historical book.
c) Find the total number of books published by each publisher.
d) Find the total number of copies in the inventory (including all branches) for each book. Include only the top five books based on the number of copies available in the result set.
EMPLOYEE id Valehal 8) Wave Vidves (6) TyPe vauchat (0) none non ben(c) no Book ad nonbe(s) Tite Valchal (b) 24 Vadra (e)
Expert Answer
–a)
select Title,Price from Book where Type=’Historical’–listing only historical type of books by applying in where condition
–b)two steps to understand first, will go with sub query
–below would give us the books who wrote atelast on historical
1.select id from Book group by id,type having count(*)>1 and Type=’Historical’
–below we have excluded the above result set which would give us the fictional by applying it in –filter and having count(*)>1 gives us atleast one fictional authors
–final query:
2.select a.Name,a.Email,count(*) from Author a join Book b on a.id=b.id
where id not in (select id from Book group by id,type having count(*)>1 and Type=’Historical’)
and Type=’Fictional’
group by a.Name,a.Email
having count(*)>1
–c)number of books published can be achieved when we join with books using id and grouping by the –name we would get number of books published by each publisher
select Name,count(*) PublishedCount from Published p join Book b on b.id=p.id group by p.name,b.id
–d)firstly, we write a sub query which should give us the copies count for each branch and book –copies count for each book and then we apply top 5 copies that are in the resultset
select top 5 * from (select br.branch,b.Title,count(*) copiescount
from Branch br join Book b
on b.id=br.id group by br.branch,b.Title ) copies
order by copiescount desc
