SQL

1.  SELECT isbn 
    FROM editions 
    WHERE publisher_id = 59;

2.  SELECT isbn, title 
    FROM books 
    JOIN editions on books.id = editions.book_id 
    WHERE publisher_id = 59;

3.  SELECT stock, retail, title, editions.isbn 
    FROM 
      books 
      JOIN 
      editions 
      ON books.id = editions.book_id 
      JOIN 
      stock 
      ON editions.isbn = stock.isbn 
    WHERE publisher_id = 59;

4.  SELECT stock, retail, title, editions.isbn 
    FROM 
      books 
      JOIN 
      editions 
      ON books.id = editions.book_id 
      JOIN 
      stock 
      ON editions.isbn = stock.isbn 
    WHERE publisher_id = 59 AND stock > 0;

5. SELECT type,
      CASE WHEN type = 'h' THEN 'Harcover'
           ELSE 'Paperback'
       END AS book_type
       FROM editions;

6. SELECT books.title, editions.publication 
    FROM editions
    INNER JOIN books ON books.id = editions.book_id; 

7. SELECT sum(stock) FROM stock;

8. SELECT avg(cost) AS average_cost,
          avg(retail) AS average_retail,
          avg(retail - cost) AS average_profit
    FROM stock; 

9.  SELECT max(stock)
    FROM stock
    JOIN editions ON stock.isbn = editions.isbn
    LIMIT 1;

10. SELECT books.author_id, CONCAT(authors.first_name, ' ',authors.last_name) AS Full_Name, COUNT
    (books.author_id) FROM books 
    JOIN authors ON books.author_id = authors.id
    GROUP BY books.author_id, CONCAT(authors.first_name, authors.last_name);

11. SELECT books.author_id, CONCAT(authors.first_name, ' ',authors.last_name) AS Full_Name, COUNT 
    (books.author_id) FROM books 
    JOIN authors ON books.author_id = authors.id
    GROUP BY books.author_id, CONCAT(authors.first_name, ' ',authors.last_name)
    ORDER BY COUNT(books.author_id) DESC;