HW02: Intro SQL SELECT due Tue 20 Jan 23:59

Allowed and Disallowed Resources

• Examples and sample code found here: https://josephus.sergeantservices.com/classes/db/source/
• The SQL handout provided in class and also available here: https://josephus.sergeantservices.com/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 anything in the universe that is not listed above, including but not limited to these prohibitions:

• You may NOT access 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).
• You may NOT use code-generating tools (of which Co-Pilot and ChatGPT are examples).

If it is clear to the instructor that you have utilized prohibited resources you will be assigned a grade of 0 on the assignment and given the opportunity to rework the assignment. The due date remains unchanged and late work penalties will be enforced.

Before You Start

• Use git status to verify your workspace has no uncommited work. If there is uncommitted work, either delete it, revert it, or commit it.
• Use git pull upstream main to make sure you have the most recent version of the upstream repo.
• For good measure do a git push origin main to make sure your workspace and your homework repo are aligned.

Overview

Part 1: Install Database (4 pts)

1. Visit: localhost:5050 and log into the pgAdmin portal.
2. From the pgAdmin dashboard, click the “Add New Server” and specify the following values:
   • Name: Homework
   • Host name/address: db
   • Username: student
   • Password: devpass
3. Click Save to add the Homework server.
4. In the left panel, expand Servers -> Homework -> Databases and you should see mydb as one of the database that was created when you launched the docker container.
5. Follow these steps to install the booksdb.sql file:
   • In the terminal, navigate to the examples/sql directory in your workspace. In that directory you should have a file named booksdb.sql that has a list of SQL commands that can be issued to generate the database in a schema (i.e., namespace) called booksdb.
   • From the command-line in the examples/sql directory, issue this command (NOTE: Use runsql.sh booksdb.sql if you are on Mac/Linux/Git Bash in Windows):

     runsql.ps1 booksdb.sql
     

   • The command should finish after several seconds and you should be able to find the new schema in the left panel under mydb -> Schemas. You may need to refresh the view by right clicking on the Schemas folder and clicking Refresh.
   • NOTE: There are also two scripts (runsql.ps1 for Windows and runsql.sh for Mac/Linux) that can be used for homework assignments moving forward.
6. Right-click on the booksdb schema in the left panel and choose Query Tool. This will provide a window in which you can type SQL commands.
7. Begin by setting the search path to the new schema:

   SET SEARCH_PATH TO booksdb;
   

   Then click the run/play icon to execute the command.
8. To verify everything is ready for the homework, add this command following the SET SEARCH_PATH command:

   SELECT * FROM publisher;
   

   You should see a list of publishers.
9. Next install the albumdb.sql database using the commands above. You do not need to create a new server. Instead just repeat the docker exec command with this file.

Part 2: Write SQL SELECT Queries (20 pts)

Above each query (as a SQL comment) include the problem number along with the number of rows displayed by the query. So, your file will look something like this:

-- 1. 23 rows
SELECT ...

-- 2. 1720 rows
SELECT ...

...

-- 8. 1 row
SELECT ...

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

Turn in your work by making sure all your work appears in the homework directory in your workspace for this assignment. Then add/commit/push your work to your github homework repo.