pgAdmin Postgres 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 tpch 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.

Note the following about the tpch database design:

• The column names have a prefix based on the table name. So the NAME column in the PART table is named p_name. Similarly, the NAME column in the SUPPLIER table is named s_name. (All table and column names are lowercase in PostgreSQL.)

• You will sometimes need to look at the data to understand the format. For example, the o_orderpriority column uses values like 2-HIGH and 5-LOW. To look at the ORDERS table, run a query like this:

  SELECT * FROM orders LIMIT 100;
  

• While working on a query, add a LIMIT clause at the end. That way, if your work in progress returns a lot of rows, you don't have to transmit the entire result over the network. Remove the LIMIT clause when you are finished debugging.

TPC-H Warmup Queries

Write the following queries based on the TPC-H Schema.

1. List all the nations in the Europe region.
2. Count how many nations each region has.
3. Count how many customers each nation has.
4. Show the customer name and line items for order number 3.
5. Show the part name, supply cost, and price of each line item.
6. Get the min, max, and average retail price of each part.
7. Find the line item with the highest profit: qty * (price – cost).
8. Make up a new query and be ready to present the solution.