HW03: SQL SELECT and More due Wed 31 Jan 13:00

Allowed and Disallowed Resources

• Examples and sample code found here: https://josephus.hsutx.edu/classes/db/source/
• The SQL handout provided in class and also available here: https://josephus.hsutx.edu/classes/db/source/sql.pdf
• A text editor (or VSCode) that provides syntax highlighting. You MAY NOT USE an extensions to such editors/IDEs that produce code.
• The phpPgAdmin front end to your personal database (login is here: https://csci.hsutx.edu/phpPgAdmin/
• Video instructions provided in Canvas as part of this course. You MAY NOT USE any other video resources.
• Any handouts provided by the instructor as part of this course.
• Your own course notes
• Your instructor
• Discussions about the assignment with other students as long as you never look at the code produced by another student and you never receive instructions about solving the homework. That is, discussions need to be about concepts and understanding the technologies and not about how to solve the particular problem posed in this assignment.

You may NOT use/access:

• Resources not expressly listed above, including, but not limited to, the following ...
• Source code not provided as part of this assignment. (Obviously, this includes, but is not limited to, source code written by other students whether current or in the past).
• Code-generating tools (of which ChatGPT is one example).

Failure to abide by these guidelines will result in a zero for the assignment and the incident will be reported to the university provost as a violation of the university academic integrity policy. A second incident of academic dishonesty (whether from this course or another computer science course) will result in an F in the course.

Instructions

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. One query has an additional question to be answered in the comment section and so is worth 3 points instead of 2. 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 SELECT, UPDATE, CREATE, or DELETE query that will:

1. Produce a list of book titles that appear more than once. The query should display the title and the number of times the title appears.
2. Produce a list of authors (first name and last name) along with their average book length. Order the results so that the authors with the longest averages are listed first. Answer this question in the comments above your query: Why does Freitas have an average book length of null? (HINT: If your answer is “because Freitas has a book whose length is NULL”, then you are not correct. There are other authors who have a book whose length is NULL and their average is not NULL.)
3. Repeat the query from problem 2 to include authors that don't have any books (their averages will be null).
4. Create a view called big that will display all fields of all three tables. The tables should be joined using and INNER JOIN with the connector of your choosing.
5. Use the view from problem 4 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'
6. Produce 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.
7. Produce 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.
8. Change the author whose id 2557 to have a first name of 'Super' and a last name of 'Man'. (NOTE: This should be a single query).
9. Replace all one-letter author first names with NULL.
10. Modify all books that have NULL for the year with a year of 0000.
11. Delete all authors that don't have a book listed.
12. Delete all books that have the word “Devil” in the title.