There are a lot of fancy tools that can be used for developing using SQL, for instance a plugin for Visual Studio Code that can run selected queries and do auto-completion and stuff (it works less well for running batches like the one in the labs). This guide will teach you how to set up an efficient workflow using two tools:

Any text editor (use one with SQL syntax highlighting, like Visual Studio Code. Notepad++ is a simple option for windows users). Make sure it's a text editor and not using RTF or other formats. If you can have things like headlines and bold text, it's probably the wrong tool.
The psql command line tool included in every Postgres installation.

TLDR:

Run this in a terminal window to start psql and connect to the database "portal" after creating it (see below for how to create "portal"):

psql -v ON_ERROR_STOP=1 -U postgres portal

You might now want to run a script that deletes everything in the database and then run all your files to recreate the database and print any errors that occur.

First time running psql

This assumes you have successfully installed the latest version of PostgreSQL (see instructions further down), opened a terminal window, and navigated the terminal to a directory where you have .sql files (for example where you have created tables.sql, views.sql and inserts.sql or downloaded the runtests.sql file from the Assignment text).

Run this command:

psql -U postgres

If the terminal says psql is not a valid command, you have not added the bin directory of postgres to your path environment variable. Either do that or locate the psql binary (e.g. psql.exe on Windows) and run it with its complete path. In windows command prompt (cmd.exe) that might look something like this (note the quotes):

"C:\Program Files\PostgreSQL\13\bin\psql.exe" -U postgres

In MS Powershell, something like this should work:

& 'C:\Program Files\PostgreSQL\13\bin\psql.exe' -U postgres

These assume you are using version 13 of Postgres (recommended) and have installed it to the default directory, adjust as needed (e.g. 12 instead of 13 if using version 12). Version 14 has recently been released and may have compatibility issues.

If it works you are prompted to enter the password you used when installing. When at the psql prompt (postgres=#) input this command:

CREATE DATABASE portal;

It should echo "CREATE DATABASE". If it does, type \quit to exit the application and restart it with this command in your operating system terminal:

psql -v ON_ERROR_STOP=1 -U postgres portal

This will connect you to the database portal, using the user postgres.

Workflow

Now run the command \i runtests.sql at the psql prompt to execute the (so far empty?) files. This will give errors since the views do not yet exist, so edit runtests.sql, commenting out the SELECT clauses (-- for single line comments) and all the inserts in inserts.sql, then gradually return them as you progress in the lab.

Add a CREATE TABLE statement to tables.txt and check that it works by re-running \i runtests.sql (just press up arrow and enter in the terminal).

The workflow from here on out will be making edits in whatever text editor you are using (one with SQL syntax highlighting is VERY useful), re-running the \i command, and fixing any error you get etc.

Installing PostgreSQL on Windows

Follow the link to the EnterpriseDB installer from this page: https://www.postgresql.org/download/windows/

When prompted for a password, use "postgres" or choose a simple one that you do not mind storing in plain text, also using the same password for both members of the group is beneficial. The password does not need to be strong.

After installing, you will need to add the \bin subdirectory of the installation folder to your windows path.

Installing PostgreSQL on MacOS

Follow the instructions on https://postgresapp.com/

When prompted for a password, use "postgres" or choose a simple one that you do not mind storing in plain text, also using the same password for both members of the group is beneficial. The password does not need to be strong.

Installing on other operating systems

Follow instructions on https://www.postgresql.org/download/.