Reference no: EM133534705

Question 1

Using the 'TEAM' table, find the game id (GID), team name (TNAME), and total points (PTS) that satisfy the following conditions:
1. the team name should contain the letter 'e'
2. the points scored in the first quarter (Q1P) should be more than 3 but not more than 8.

Question 2

Using the 'RUSH' and 'PLAY' tables, find all the first-down (FD in the PLAY table) rushing plays. Please include all columns from the RUSH table as well as GID.

Question 3

Find the average points that visiting teams scored in each stadium when the temperature is higher than 69 degrees. Your outcome table should have two columns: the name of the stadium and the average points. The column that contains the average points should be named "pts_avg". You should order your table by descending order of pts_avg.

Question 4

Write a query to classify fumbles. Your outcome table should contain every column in the FUMBLE table, the GID, the play type, as well as a column named "fumble_type". The "fumble_type" column is equal to 1 if a fumble happened in a pass play (type = 'pass'), 2 if a fumble happened in a rush play (type = 'rush'), and 3 otherwise.

Question 5

Write a query to classify all the drives that started with a touch back. If the number of plays in the drive is greater than 10, it is a long drive. Otherwise, it's a short drive. Your outcome table should contain at least the following columns: GID, FPID, TNAME, DRVN, KICKER, and a column named 'long_drive' which has two possible values: 'long' and 'short'. Hint: You may want to take a look at what FPID captures.