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;