Skip to content

HW6: Intermediate SQL

Due: Tuesday, March 5th

Duke Dog standing by airplane
Image source: jmu.edu

Objectives

  • Write queries that may include self joins, subqueries, or set operations.
  • 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

For this assignment you'll use two databases:

  1. air is based on various data sources described on GitHub. Refer to the postgres_air database page from class.

  2. 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 value 1241 means Spring 2024.

    • nbr is the five-digit course code from MyMadison. Use both term and nbr to uniquely identify a section of a course.

When running queries in pgAdmin, make sure you are connected to the right database.

Instructions

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

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

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

  4. Run the script and compare your output with the solution's. Use a tool to make sure the output matches exactly.

Tip

If you were unable to get psql and meld working on your laptop, you can run the commands on the stu server instead. See the Visual Studio Code on stu page for setup instructions, or ask a TA for help.

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=postgres
export PGUSER=jmu_username
export PGPASSWORD=student_number

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

code --diff hw6-sol.txt hw6.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.

Submission

Submit your hw6.sql script to the HW6 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.