HW02: Intro SQL SELECT due Wed 25 Jan 13:00

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 by the query.

Each query is worth 2 point plus 1/2 a point for giving the correct number of rows displayed in the comment section. 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. Write a query that will:

1. Produce a list of all info about all books (including author name, publisher name, etc.) using an INNER JOIN with the USING connector.
2. Repeat the query from problem 1 using an INNER JOIN with the NATURAL connector.
3. Repeat the query from problem 1 using an INNER JOIN with the ON connector.
4. Produce a list of books (title and year only) that were published before 1980.
5. Produce a list of books (title, publisher name) whose publication year is not known.
6. Produce a list of books (title, author last, author first) that are between 100 and 200 pages (inclusive).
7. Produce a list of authors whose first name is either unknown or contains only one letter.
8. Produce a list of authors whose first name or last name contains this sequence of characters: 'evil'. (Do not include in your match the single quotes or the period!)