Skip to content

HW5: Basic SQL Queries

Due: Monday, Feb 26th

TPC logo with members
Image source: tpc.org

Objectives

  • Write queries that use JOIN, GROUP BY, HAVING, LIKE, and DISTINCT.
  • Run an SQL script from the command line and meld the expected output.

Important

This assignment must be completed individually. Your work must comply with the JMU Honor Code. Authorized help is limited to asking questions in class, during office hours, or via email. Copying code from someone else, including the use of generative AI, is prohibited and will be grounds for a reduced or failing grade in the course.

Background

The Transaction Processing Performance Council (TPC) is a non-profit organization founded in 1998 that develops performance benchmarks for database systems. One of the benchmarks, TPC-H, simulates a business-oriented database with customers and suppliers. For this homework assignment, you will write new queries for TPC-H.

Instructions

  1. Download the TPC-H schema. Use this diagram to become familiar with the database.

  2. Download the provided template. Read and follow the instructions at the top of the file.

  3. Download the solution's output. This is the output that your script file should produce.

  4. Write your queries, one at a time, using pgAdmin. When you finish a query, paste your code into the script file.

  5. Run the script and compare your output with the solution's. Use meld to make sure the output matches exactly.

The following commands can be used to automate the last step. (On Windows, use set instead of export.) 

export PGHOST=data.cs.jmu.edu
export PGDATABASE=tpch
export PGUSER=jmu_username
export PGPASSWORD=student_number

psql -q < hw5.sql 2>&1 | tee hw5.txt

meld hw5-sol.txt hw5.txt &

Note: Your script should run without any errors and finish in under two minutes. If any query is taking longer than 15 seconds, you are probably doing something wrong.

Alternative commands

If your terminal does not support the tee command, use this command instead: 

psql -q < hw5.sql 2>&1 > hw5.txt

If meld is not working on your computer, can use VS Code instead: 

code --diff hw5-sol.txt hw5.txt

See the announcement on Canvas for more details.

Submission

Submit your hw5.sql script to the HW5 assignment on Gradescope. The autograder will verify that your script runs correctly. You may resubmit if the autograder fails. We will grade this assignment manually by looking at your code.