SQL SELECT Queries due Tue 12 Sep 10:30

\begin{purpose}
This assignment provide substantial practice in constructing SQL queries.
\end{purpose} This assignment has (possibly) 2 parts:
  1. If you have modified entries in the booksdb schema then rename that schema to booksdbmodified (or just delete it) and create a new booksdb schema (the name is important) and finally, re-import the books database using the make-books.sql script.
  2. Use the (clean) ``books'' database write SQL queries to perform the following tasks. The queries should be saved in a text file (i.e., a file that has a .txt extension). Above each query (as a comment) include the problem number along with the number of rows displayed (or affected) by the query. If the problem below asks a question about the results then write your answer to the question as part of the comment preceding the query as well.

To submit this assignment upload your text document (containing queries and comments) in Canvas.

Unless stated otherwise, each query should display all fields of the table(s) in question.

  1. a list of all info about all books (including author name, publisher name, etc.) using inner join
  2. repeat the query from problem 1 using natural inner join
  3. a list of books whose publication year is not known
  4. a list of books (along with author info) whose author's first name has only one letter
  5. replace all one-letter author first names with null
  6. a list of authors along with their average book length; order the results so that the authors with the longest averages are listed first; why does Freitas have an average book length of null?
  7. repeat the query from problem 6 to include authors that don't have any books (their averages will be null)
  8. a list of book titles that appear more than once
  9. use the query from problem 2 to create a named view called big ; after this view is created, what does the query select * from big; produce?
  10. use the view from problem 9 as the starting point for a query that will list isbn, title, publisher, and author name of all books by authors whose last name begins with `Z'
  11. a list of all authors who have written at least 5 books ordered first by number of books written (most first), then by author last name, then by author first name
  12. a list of publishers along with the number of different authors each publisher has published (i.e., NOT the number of books each publisher has published); order the the list in descending order of number of authors
  13. delete all books that have the word ``Devil'' in the title

Quick Links