In completing this assignment you MAY use/access the following 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.
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
make-books.sql
script.
- 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:
- Produce a list of all info about all books (including
author name, publisher name, etc.) using an INNER JOIN with the
USING connector.
- Repeat the query from problem 1 using an
INNER JOIN with the NATURAL connector.
- Repeat the query from problem 1 using an INNER
JOIN with the ON connector.
- Produce a list of books (title and year only) that were published before 1980.
- Produce a list of books (title, publisher name) whose publication
year is not known.
- Produce a list of books (title, author last, author first) that are between
100 and 200 pages (inclusive).
- Produce a list of authors whose first name is either unknown or contains
only one letter.
- 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!)