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).
  • For example: CS374 or username@data.
• 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.

Step 3. Explore the DB

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.

About the data

jmudb is based on historical enrollment data. There is only one table: enrollment. Most 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 1251 means Spring 2025.

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

Practice queries

Write the following queries for jmudb in pgAdmin:

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

If time permits, write a fourth query of your choice.