A01: NFL Play-by-play
This assignment helps you practice with the SQL “select” query. We’ll use a
dataset of NFL play-by-play statistics downloaded from
NFLsavant.com. You will only query one table,
nflpbp
, in the cinf201
database.
Tasks
In a single file named A01.sql
, write four “select” queries that meet the
following criteria:
- Select unique game dates, sorted by date.
- Select offense and defense teams from games on Thanksgiving day of 2015 (Nov 26), in which a touchdown occurred (by the team on offense); do not sort the results.
- Select the first 10 descriptions in which a fumble occurred.
- Select unique pairs of penalty type and penalty yards for penalty types that are not blank, and order by penalty yards so that the greatest penalty yards is first.
The exact desired output can be discovered by running the test program (see below). Your SQL queries must produce the desired output to earn full credit.
Example starting script
Create a text file named A01.sql
and write your SQL queries in this
file, one per line. Be sure to finish each query with a
semicolon. Start with use cinf201;
to be sure you are accessing the
correct database.
Here is an example of some random queries:
use cinf201;
select GameDate from nflpbp limit 10;
select Quarter, Minute, Down from nflpbp limit 10;
You can test your script in two ways:
- manually: on londo, run
mysql < A01.sql
- using my testing script: on londo, run
cinf201-test A01
Submission
Run this command from the same directory as your A01.sql
file. It will automatically run the tester.
cinf201-submit A01
Tester
You can run the tester without submitting with the following command. Again, run this command from the same directory as your A01.sql
file.
cinf201-test A01
Hints
You can learn about the columns and datatypes of the nflpbp
table by running this query in MySQL:
describe nflpbp;