--------------------------------------------------------------------------------

Question

--------------------------------------------------------------------------------------

Problem 1. Show the average position that an author appears on a book.
List the author using the author_id column.

YOUR ANSWER HERE:

--------------------------------------------------------------------------------------

Problem 2. List each author (by lastname and firstname) and the number of books
that the author has written.

YOUR ANSWER HERE:

--------------------------------------------------------------------------------------

Problem 3. Display a list of the authors and the city they live in,
as long as they live in one of the cities that a publisher is based in.

Use a Subquery instead of a JOIN.

YOUR ANSWER HERE:

--------------------------------------------------------------------------------------

Problem 4. Show a list of all authors and the books they have
written. However, only display a book if it has the letters 'oo' contained in
its title.

Sort in Lastname, firstname order.

YOUR ANSWER HERE:

--------------------------------------------------------------------------------------

Problem 5. List the publisher name and title for all books, but only if
they are published by the publisher whose name begins with 'Bin'.

Sort by publisher name and title.

YOUR ANSWER HERE:

--------------------------------------------------------------------------------------

Explanation / Answer

1)

select count(*),author_id from bookauthor group by author_id order by author_id desc;

2)

select num_books , fistname,lastname from author a join (select count(*) as num_books ,author_id from bookauthor group by author_id) b on a.author_id = b.author_id;

3)

select a.author_id , a.city
from
author a
join bookauthor b
on b.author_id=a.author_id
join book bk
on bk.isbn = b.isbn
join publisher p
on bk.pub_id = p.pub_id
where a.city=p.city;

d)

select a.firstname , a.lastname , bk.title
from
author a
join bookauthor b
on a.author_id=b.author_id
join book bk
on bk.isbn = b.isbn
where bk.title like '%oo%'
order by a.firstname , a.lastname

5)

select p.name , bk.tittle
from book bk join
publisher p
on bk.pub_id = p.pub_id
where p.name like 'Bin%'
order by p.name , bk.tittle;

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

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