Description
In this homework, you will use a real database of Jeopardy contestants. For this homework only, I
have scaled the database to the last two seasons to make it easier to query. It is still a fairly large
database. As this data was scraped from the web by me, there is likely to be lots of missing data
and errors. We will discover them as we use it. Above is a picture of one of our best friends (left
in the picture) who became the first streaker (5 consecutive wins) of 2018 in Jeopardy!
If you do not know about Jeopardy, it is a game show in which contestants answer increasingly
difficult questions/clues about many topics. Each clue has a point value, but some clues are special
(daily doubles) where the contestant can provide a bet and choose the point value. There are
three main parts: ’Jeopardy’ (easier questions), ’Double Jeopardy’ (harder questions) and ’Final
Jeopardy’ (a single last question). For the Jeopardy and Double Jeopardy, contestants will try to
answer as quickly as possible (using a buzzer). If the first person’s answer is incorrect, others may
also answer. The daily doubles are questions only one contestant can answer (whoever picked it).
Finally, in the final jeopardy, all contestants (as long as they have positive scores before that point)
will answer and provide a bet. If they answer correctly, they will as many points as their bet and
if they answer incorrectly, they will loose as many points as their bet.
You can review the data model to see more details.
Given this database, write the following queries using SQL (in no particular order of difficulty):
Query 1. Return the full name of all contestants who had at least 5 consecutive wins (hint: check
out the description of contestants). Order by fullname.
Query 2. Return the full name of all constants whose short name start with letter ’b’ who answered a daily double clue correctly (isdd is True) in the Double Jeopardy part of the short
(cat type is ’DJ’). Order by full name.
Query 3. Return the gameid, clue text and category for all final jeopardy clues that were triple
stumpers (no contestant has answered them correctly). Order by gameid, clue, category.
2
Query 4. Return the id of all games, shortname for a pair of contestants in which at least two
contestants were tied going into final jeopardy (i.e. according to their scores in Round 3).
For each pair of contestants, only return one pair (alphabetically ordered). Order by gameid
and names.
Query 5. Return all game rounds in which all clue categories were 11 characters or less (each
cat type value J or DJ is a different game round). Order by game id and category type.
Query 6. Return the full name, final game score and description of contestants from ’Wisconsin’
with the highest final score in a sigle game. Order by full name and score.
Query 7. For each contestant who competed in a game that aired in January, return the game
id, contestant full name and the total number of questions the contestant answered correctly
in the ’Double Jeopardy’ round of that game (considering only the games that the database
contains some clues for the ’Double Jeopardy’ round). Order by number of correct answers,
game id and full name.
Query 8. Find all contestants who have a game in which their Coryat score would have been ten
times as much as their Final score, even though their final score was more than 1000 (i.e. they
are really bad at betting). Return the gameid, shortname, their final score and the Coryat
score. Order by gameid and shortname.
Query 9. Return the text of all clues that are about the ’Internet’ (either clue text or the category).
Order by clue text.
Query 10. Return the gameid, contestant short name and final score of all games in which the
contestant had a negative score in Round 2, but eventually won the game (with the highest
final score). Order by final score and game id.
Submission Instructions.
Submit a single ASCII text file named username_hw4ans.sql that contains all your queries to
SUBMITTY. I will post submission instructions later for this on Piazza. We will use Submitty for
all SQL homeworks. However, Submitty is not yet set up, so this may take some time.
Your script should be formatted as shown below:
— Print your answer and RCS id first
SELECT ‘Student: Sibel Adali (adalis@rpi.edu)’;
— Print the name of each query before the query output
— Pay close attention to the columns requested as well as the
— requirements for ordering of results for each comparison
SELECT ‘Query 1’;
— Replace this with your answer for Query 1.
SELECT count(*) FROM games ;
— Repeat this pattern for each query
SELECT ‘Query 2’;
— Replace this with your answer for Query 2.
SELECT count(*) FROM contestants ;
3
SELECT ‘Query 3’;
— Replace this with your answer for Query 3.
SELECT count(*) FROM clues ;
4
Database Schema
— Each game is in a season, given by id
CREATE TABLE games
( id INT — season id
, gameid INT
, airdate DATE
, PRIMARY KEY (gameid)
) ;
— Each contestant is identified by a shortname, which is unique for a
— game.
CREATE TABLE contestants
( gameid INT
, fullname VARCHAR(100)
, description VARCHAR(255)
, shortname VARCHAR(100)
, PRIMARY KEY (gameid, shortname)
, FOREIGN KEY (gameid) REFERENCES games(gameid)
) ;
— The overall scores of each contestants after different rounds
— of the game.
— Rounds ‘1’, ‘2’ are in the first stage called the ‘Jeopardy’ stage,
— Round ‘3’ is after ‘Double Jeopardy’ before ‘Final Jeopardy’.
— Round ‘Final Score’ is the actual score of each person
— Round ‘Coryat Score’ is the hypothetical score without the bets
— Round ‘6’ is an error, which needs to be identified later.
CREATE TABLE scores
( gameid INT
, shortname VARCHAR(100)
, score INT
, round VARCHAR(20)
, PRIMARY KEY (gameid, shortname, round)
, FOREIGN KEY (gameid, shortname)
REFERENCES contestants(gameid, shortname)
) ;
— Each game has many clues, clue is the question, and correct_answer is the answer
— value is the dollar value of the clue: amount player wins/looses
— for correct, incorrect answers
— category is the named of the category
— cat_type is one of: ‘J’: ‘Jeopardy’ round and ‘DJ’: ‘Double Jeopardy’ round
— isdd is true if the question was a double jeopardy question
CREATE TABLE clues
( gameid INT
, clueid INT
, clue TEXT
, value INT
, category VARCHAR(255)
, cat_type VARCHAR(10)
, isdd BOOLEAN
, correct_answer VARCHAR(255)
, PRIMARY KEY (gameid, clueid)
, FOREIGN KEY (gameid) REFERENCES games(gameid)
5
) ;
— Each contestant can answer a clue, if the answer is wrong,
— another contestant can answer. This relation stores all
— contestants who gave a response (but not what they said).
— If there is no correct answer for a question here, it means
— that no contestant answered the question correctly.
CREATE TABLE responses
( gameid INT
, clueid INT
, shortname VARCHAR(255)
, iscorrect BOOLEAN
, PRIMARY KEY (gameid, clueid, shortname)
, FOREIGN KEY (gameid, clueid) REFERENCES clues(gameid, clueid)
, FOREIGN KEY (gameid, shortname)
REFERENCES contestants(gameid, shortname)
) ;
— At the end of the game, there is a single question/clue called
— the ‘Final Jeopardy’. This relation stores the clues for this
— specific round. There is no dollar value attached to these questions.
CREATE TABLE final_clues
( gameid INT
, clue TEXT
, category VARCHAR(255)
, correct_answer VARCHAR(255)
, PRIMARY KEY (gameid)
, FOREIGN KEY (gameid) REFERENCES games(gameid)
) ;
— For the ‘final jeopardy’, all contestants give an answer and a bet
— The bet is the dollar amount the contestant will win/loose if they
— answer correctly. Only contestants with positive winnings/scores
— at round ‘3’ can participate. This relation stores the bets and
— whether each person scored correctly or not.
CREATE TABLE final_responses
( gameid INT
, shortname VARCHAR(255)
, iscorrect BOOLEAN
, bet FLOAT — VARCHAR(10)
, PRIMARY KEY (gameid, shortname)
, FOREIGN KEY (gameid, shortname)
REFERENCES contestants(gameid, shortname)
) ;
