Bookstore SQL Assignment

SQL EXERCISES


1.

select isbn 
from editions as e
join publishers as p
on (e.publisher_id = p.id)
where name = 'Random House';


2.

select e.isbn as "ISBN", b.title as "Book Title"
from editions as e
join publishers as p
on (e.publisher_id = p.id)
join books as b
on (e.book_id = b.id)
where name = 'Random House';


3.

select e.isbn as "ISBN", b.title as "Book Title", s.retail as "Price", s.stock as "Remaining Stock"
from editions as e
join publishers as p
on (e.publisher_id = p.id)
join books as b
on (e.book_id = b.id)
join stock as s
on (e.isbn = s.isbn)
where name = 'Random House';


4.

select e.isbn as "ISBN", b.title as "Book Title", s.retail as "Price", s.stock as "Remaining Stock"
from editions as e
join publishers as p
on (e.publisher_id = p.id)
join books as b
on (e.book_id = b.id)
join stock as s
on (e.isbn = s.isbn)
where name = 'Random House'
and s.stock > 0;


5.

select e.isbn as "ISBN", b.title as "Book Title", s.retail as "Price", s.stock as "Remaining Stock", e.type as "Type",
case when e.type = 'p' then 'Paperback'
when e.type = 'h' then 'Hardcover'
else 'Unknown'
end from editions as e
join publishers as p
on (e.publisher_id = p.id)
join books as b
on (e.book_id = b.id)
join stock as s
on (e.isbn = s.isbn)
where name = 'Random House'
and s.stock > 0;


6.

select b.title as "Book Title", e.publication as "Publication Date"
from books as b
left join editions as e
on (e.book_id = b.id)


7.

select sum(s.stock)
from stock as s;


8.

select avg(s.cost) as "Average cost",
avg(s.retail) as "Average retail",
avg(s.retail - s.cost) as "Average profit"
from stock as s;


9.

select e.book_id as "Book ID"
from editions as e
natural join stock as s
order by s.stock desc 
limit 1;



10.

SELECT author_id, (authors.last_name || ' ' || authors.first_name) AS "Full Name", count(author_id) FROM books
JOIN authors ON (books.author_id = authors.id)
GROUP BY (authors.last_name || ' ' || authors.first_name), author_id;



11.

SELECT author_id, (authors.last_name || ' ' || authors.first_name) AS "Full Name", count(author_id) FROM books
JOIN authors ON (books.author_id = authors.id)
GROUP BY (authors.last_name || ' ' || authors.first_name), author_id
ORDER BY count(author_id)
DESC;


12.

SELECT title, COUNT(type) FROM
editions INNER JOIN books
ON editions.book_id = books.id
GROUP BY title
HAVING COUNT( DISTINCT(type)) = 2;


13.

SELECT p.name, avg(retail), COUNT(editions.isbn) FROM
  books JOIN editions
  ON books.id = editions.book_id
  JOIN publishers p
  ON editions.publisher_id = p.id
  JOIN stock s
  ON s.isbn = editions.isbn
GROUP BY p.name;