Skip to content

Queries on the tpch database

TPCH logo

Connect to the PostgreSQL server using the pgAdmin tool. 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.

screenshot of query tool button

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.