This assignment has (possibly) 2 parts:
- 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
- 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.
- a list of all info about all books (including author name,
publisher name, etc.) using inner join
- repeat the query from problem 1 using natural
- a list of books whose publication year is not known
- a list of books (along with author info) whose author's first name has
only one letter
- replace all one-letter author first names with null
- 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?
- repeat the query from problem 6 to include authors that
don't have any books (their averages will be null)
- a list of book titles that appear more than once
- 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?
- 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'
- 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
- 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
- delete all books that have the word ``Devil'' in the title