CS257 Software Design
Wednesday, 30 April 2025

+ Questions

+ Postgres, psql, SQL
- psql: command-line interface for postgres databases
    (also: a client for the postgres server)

- SQL statements/queries/commands
    SELECT title, publication_year FROM books;
    SELECT title, publication_year FROM books ORDER BY publication_year;
    SELECT title, publication_year FROM books
        ORDER BY publication_year DESC; -- order most recent first

    INSERT INTO books (id, title, publication_year)
        VALUES (123,'Pride and Prejudice',1805);

    DROP TABLE authors, books, books_authors;
    
    More verbs: DELETE, DROP, ALTER, UPDATE,...

- psql directives (distinct from SQL statements/queries)
    \? -- get help about psql directives
    \dt -- display tables
    \q -- quit
    \l -- list all the databases
    \c newdb -- change which database you're using

+ Do this
- Open these class notes in your browser (reload as needed)
- Login to stearns with VS Code
- Update your copy of my git repository
- cd to my postgresql folder
- run psql and drop any books-related existing tables
- go back out to Unix on stearns
    psql -U YOURUSERNAME YOURUSERNAME < books.sql
- go into psql, see what you see
- try these tasks
    - show all the books by PG Wodehouse
    - show all the authors whose first names start with J
    - show the number of books written by each author
- open up books.sql and look around to see what's in it