pgAdmin – PostgreSQL Tools

From pgAdmin's website:

pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.

Step 1. Install pgAdmin

Visit the Download page and download the installer for your operating system. pgAdmin runs both in "desktop" and "server" mode; you need only the desktop mode.

Step 2. Register server

Once you have pgAdmin up and running, right-click the Servers icon and select Register > Server…

Enter the following information in the dialog box:

• On the General tab:
  • Enter a Name for the server (can be anything).
  • Example: username@data with your username.
• On the Connection tab:
  • Enter data.cs.jmu.edu for the Host.
  • Enter your Username instead of postgres.
  • Enter your Password (student number).
  • Click Save password if you would like.
• On the SSH Tunnel tab:
  • Enter stu.cs.jmu.edu for the Tunnel host.
  • Enter your Username.
  • Enter your Password (JMU password).
  • Click Save password if you would like.

Step 3. Write queries

Click on the jmudb database to establish a connection to that database. Then click the Query Tool icon on the toolbar. A new tab will open with an SQL editor.

jmudb is based on historical enrollment data. There is only one table. Most of the columns are self-explanatory, except:

• term is a four-digit number. The first digit is always 1. The next two digits are the year. The last digit is the month (1=Spring, 5=Summer, 8=Fall). So the value 1241 means Spring 2024.

• nbr is the five-digit course code from MyMadison. Use both term and nbr to uniquely identify a section of a course.

Exercise

Write the following queries for jmudb in pgAdmin:

1. Show all CS and IT courses offered in Spring 2024.
2. Find the top 10 classes with the most students enrolled.
3. List all unique classrooms in EnGeo (in ascending order).