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.
- Enter
- 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.
- Enter
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 value1241
means Spring 2024. -
nbr
is the five-digit course code from MyMadison. Use bothterm
andnbr
to uniquely identify a section of a course.
Exercise
Write the following queries for jmudb
in pgAdmin:
- Show all CS and IT courses offered in Spring 2024.
- Find the top 10 classes with the most students enrolled.
- List all unique classrooms in EnGeo (in ascending order).