CS 4320/5320 - Homework 1
Your task is to analyze a data set of your choice with SQL queries. Based on that analysis, you
are expected to submit a small report (two pages), showing the queries and the query results.

You are free to select any structured data set you are interested in. For instance, you may
download data from Kaggle (www.kaggle.com) or you may generate data yourself (using the
commands for creating and filling tables we saw in Lecture 2). For executing your queries, you
may use a local PostgreSQL installation or you may use the DB fiddle Web site (www.db-
fiddle.com) which does not require local setup. If choosing the latter option, please select
PostgreSQL 12 as the database system (left upper corner of Web site).

Your submission must contain six SQL queries with associated result tables. In your report,
only report the first three result rows for each query to remain within space constraints.
Alternatively, you may choose to represent the result of queries with numerical columns as a
plot for better readability (not required). Each query must be accompanied by a text description
that describes the semantics of the query precisely (typically, one or two sentences per query
are sufficient).

Your grade (out of 120 points) will be calculate as follows:

• You receive 10 points per query for correct SQL syntax and for an accurate text description
of the query's semantics.

• You receive 10 points if at least one of your queries uses a join operation.

• You receive 10 points if at least one of your queries uses a group by operation.

• You receive 10 points if at least two of your queries use nested queries.

• You receive 10 points if at least one of your queries uses an aggregation function.

• You receive 10 points if at least one of your queries uses a having clause.

• You receive 10 points if at least one of your queries uses an outer join.

In order to obtain full points for query diversity (i.e.., points for using specific SQL features), you
must use those features in a "reasonable manner". I.e., do not construct artificial queries
whose only purpose is to use SQL features (e.g., queries that join with a table whose columns
are not even selected for display) but queries that yield interesting results. Try to connect the
results of different queries into one story when writing the report.

Submit a .zip file to CMS that contains two files:

• A file titled "report.pdf", a .pdf document with two pages that represents your report.

• A file titled "setup.txt" specifying the location of the source data or, if generated by yourself,
the commands you used to create and fill the database.

Please note that this homework is done individually, not within a group.