Skip to content

Subqueries on TPC-H

TPC logo

In this lab you will write subqueries in the WHERE and FROM clauses, and compare a subquery with a join, on the same tpch database as Thursday's lab. You have 20 minutes, and then the Exam 1 Retake begins. Problems 1–4 are the goal; Problem 5 is for anyone who finishes early.

Setup

  1. Start your SSH tunnel, open pgAdmin, and open the Query Tool on the tpch database.
  2. Download subqueries.sql and open it in the Query Tool (the folder icon, or Ctrl+O).
  3. Type your name at the top.

Everything you need is in the file: each problem is a comment that says what to write and how many rows to expect, and the top of the file lists the tables and columns you will use. Write each query under its problem, highlight it, and press F5 to run it. Some problems also ask a question; answering it in a comment is optional.

If a query runs for more than about 10 seconds, cancel it (the stop button) and look for a missing join condition. The TPC-H Schema and the slides are there if you need them.

Submission

Submit your file subqueries.sql via Gradescope by the end of the day. The autograder only checks that the file has the kinds of queries the problems ask for; it does not run your queries. The queries themselves are checked by hand.