HW4: Intermediate SQL
Due: Monday, Oct 7th
Image source:
postgresql.org
This is the second of three SQL query assignments. You are given 10 queries to write, along with the expected output. Use pgAdmin to write and test the queries. Be sure to start early—this assignment might take longer than you think.
Objectives¶
- Write queries that use
GROUP BY
,HAVING
,LIKE
, andDISTINCT
. - Write queries that include self joins, subqueries, or set operations.
Instructions¶
-
Download the provided template. Read and follow the instructions at the top of the file.
-
Download the solution's output. This is the output that your final queries should produce.
Note: Your SQL file 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.
Autograder Script – optional, but recommended
Download the autograder script. Gradescope will use this script to test your submission.
- Run
hw4.py
(the autograder script) in the same folder ashw4.sql
andhw4-sol.txt
. - In order to run
hw4.py
, you will need to install psycopg via the command line:pip install psycopg[binary]
- If you have trouble with the installation, we can help you in class or office hours.
- Or you can just submit to Gradescope over and over, and the same script will run.
- We're giving you the autograder script so you don't have to submit so many times.
Part 1: tpch¶
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. Refer to the TPC-H Schema for more details.
Part 2: jmudb¶
jmudb
is based on historical enrollment data.
There is only one table:
enrollment(term, nbr, subject, number, suffix, title, section, room, days, beg_time, end_time, instructor, acad_org, room_cap, enrolled, enrl_cap)
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 value1241
means Spring 2024. -
nbr
is the five-digit course code from MyMadison. Use bothterm
andnbr
to uniquely identify a section of a course.
Part 3: air¶
The postgres_air database represents airports, flights, passengers, etc. The airports, airline, and route data is based on OpenFlights. Passenger and other data are synthetic. Refer to the Air Diagram for more details.
Submission¶
Submit your hw4.sql
to the HW4 assignment on Gradescope.
Your code will first be graded by Gradescope and then by the professor. The grade you receive from Gradescope is the maximum grade that you can receive on the assignment.
After the due date, the professor may manually review your code. At that time, points may be deducted for inelegant code, inappropriate variable names, bad comments, etc.
Gradescope will provide you with hints but might not completely identify the defects in your submission. You are expected to test your own code before submitting.
There is no limit on the number of submissions and no penalty for excessive submissions. Points will be allocated as follows:
Criterion | Points | Details |
---|---|---|
Autograder | 80 pts | Partial Credit Possible |
Code Review | 20 pts | Partial Credit Possible |
The code review will include:
- The approach you took on each query (1 pt each).
- Style and formatting of the SQL code (1 pt each).