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
In a single file named
A01.sql, write four “select” queries that meet the
- 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
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
Run this command from the same directory as your
A01.sql file. It will automatically run the tester.
You can run the tester without submitting with the following command. Again, run this command from the same directory as your
You can learn about the columns and datatypes of the
nflpbp table by running this query in MySQL: