Lab: more SQL

Due 8:30AM, Wednesday, May 7. To complete this lab, answer these questions.

Goal

• Get more practice with SELECT statements in SQL.
• Meet the LIMIT, ORDER BY, and GROUP BY clauses in SQL.

Things to do

• Login to stearns with VS Code.
• Update your copy of my cs257-2025-spring repository.

• Run psql in your stearns terminal.

  (Note that on stearns, your Unix username, your postgres username, and your database name are all the same, because that's how Mike Tie set it up for you. Thus, just the command psql should give you your psql prompt. If that gives you an error, though, you can try the more verbose command psql -U your_user_name your_user_name like we did in the psql lab the other day.)

  At the psql prompt, do a little looking around and then do whatever you want. If, for example, you want to eliminate out your own tables or the books/authors/books_authors tables, you could do this:

  • List the tables in your database: \dt
  • For any table you want to get rid of, delete the table with DROP TABLE table_name;
  • List the tables again with \dt to see that you deleted whichever tables you wanted gone.
• Quit psql: \q
• Change directories to my repo's postgresql directory.
• Load a whole new set of tables into your database like so from the stearns Unix prompt: psql < olympics.sql.
• Go back into psql and answer the questions in the Google Form for this lab. You might find these SQL examples helpful.

• Quit psql and run this at the stearns Unix prompt.

  pg_dump --clean --no-owner --no-privileges -U YOUR_USER_NAME YOUR_USER_NAME > dump.sql

  Take a look at the contents of dump.sql. You could use a database dump file like this to share your database from one postgresql system to another. Also, try running

  diff olympics.sql dump.sql

  Are the two files different? If so, how?

• When you're done, DROP TABLE all the olympics tables to clean up your database. You might also want to just delete your dump.sql file, since the Olympics tables take up disk space and you won't need that dataset again.

Recap: things to remember

• How to dump a copy of your database using pg_dump
• How to load a database from a dumped copy
• How to use psql directives like \?, \dt, \l, \c, \d+, etc.
• How to use simple SELECT SQL queries, including ones with WHERE, LIMIT, ORDER BY, ORDER BY ... DESC, and GROUP BY.
• How to delete a table with DROP.