Consider the following schema: CREATE TABLE Player ( name varchar(50) PRIMARY KEY, birthdate date ); CREATE...

  

Transcribed Image Text:

Consider the following schema: CREATE TABLE Player ( name varchar(50) PRIMARY KEY, birthdate date ); CREATE TABLE Tournament ( name varchar(50) PRIMARY KEY, prize int ); CREATE TABLE Match ( winner varchar(50) REFERENCES player (name), loser varchar(50) REFERENCES player (name), tournament varchar(50) REFERENCES tournament (name), played on date NOT NULL, UNIQUE (winner, loser, tournament), CHECK (loser != winner) ); Assume the following insert commands have been executed: INSERT INTO Player VALUES ('Sofia', '12/13/1987'); INSERT INTO Player VALUES ('Renata', '3/2/1992'); INSERT INTO Tournament VALUES ('Championship', 1000); INSERT INTO Tournament VALUES ('Semi-final', 200); a. (4 pts) Write two INSERT statements for the Match table that satisfy all constraints. b. (8 pts) Write four INSERT statements that each violate a different constraint of the Match table. Assume your statements from (a) have already been executed. 19. For these subquestions, you should write or draw out the table you would expect to get as a result. You are given the following tables Census and Weather: Census city Seattle Seattle Iyear 2020 2010 San Francisco 2010 Dallas 2020 San Francisco 2020 Philadelphia 2020 2020 Denver Weather city Seattle ann rainfallann_cloudy_days 37.0 Los Angeles 11.7 San Francisco 25.0 Dallas 39.1 Boston 43.0 41.5 17.0 Philadelphia population 738200 610700 805500 1303000 870000 1601000 735500 Denver 226 103 105 133 164 160 120 Census.city is foreign key to Weather.city a. (8 pts) What outputs will result from the following query? SELECT c1.city, c2.population - c1.population AS popdiff, w.ann_rainfall FROM Census c1, Census c2, Weather w WHERE c1.city = c2.city AND c1.city = w.city AND c2.year - c1.year = 10 ORDER BY change DESC; BONUS: (5 pts) What outputs will result from the following query? WITH pop2020 AS ( SELECT * FROM Census WHERE year = 2020 ), pop2010 AS ( SELECT * FROM Census WHERE year 2010 ), diff AS ( SELECT p1.city, p1.population p2.population AS pop_diff FROM pop2020 p1 LEFT JOIN pop2010 p2 ON p1.city=p2.city SELECT * FROM diff d LEFT JOIN Weather w ON d.city= w.city; Consider the following schema: CREATE TABLE Player ( name varchar(50) PRIMARY KEY, birthdate date ); CREATE TABLE Tournament ( name varchar(50) PRIMARY KEY, prize int ); CREATE TABLE Match ( winner varchar(50) REFERENCES player (name), loser varchar(50) REFERENCES player (name), tournament varchar(50) REFERENCES tournament (name), played on date NOT NULL, UNIQUE (winner, loser, tournament), CHECK (loser != winner) ); Assume the following insert commands have been executed: INSERT INTO Player VALUES ('Sofia', '12/13/1987'); INSERT INTO Player VALUES ('Renata', '3/2/1992'); INSERT INTO Tournament VALUES ('Championship', 1000); INSERT INTO Tournament VALUES ('Semi-final', 200); a. (4 pts) Write two INSERT statements for the Match table that satisfy all constraints. b. (8 pts) Write four INSERT statements that each violate a different constraint of the Match table. Assume your statements from (a) have already been executed. 19. For these subquestions, you should write or draw out the table you would expect to get as a result. You are given the following tables Census and Weather: Census city Seattle Seattle Iyear 2020 2010 San Francisco 2010 Dallas 2020 San Francisco 2020 Philadelphia 2020 2020 Denver Weather city Seattle ann rainfallann_cloudy_days 37.0 Los Angeles 11.7 San Francisco 25.0 Dallas 39.1 Boston 43.0 41.5 17.0 Philadelphia population 738200 610700 805500 1303000 870000 1601000 735500 Denver 226 103 105 133 164 160 120 Census.city is foreign key to Weather.city a. (8 pts) What outputs will result from the following query? SELECT c1.city, c2.population - c1.population AS popdiff, w.ann_rainfall FROM Census c1, Census c2, Weather w WHERE c1.city = c2.city AND c1.city = w.city AND c2.year - c1.year = 10 ORDER BY change DESC; BONUS: (5 pts) What outputs will result from the following query? WITH pop2020 AS ( SELECT * FROM Census WHERE year = 2020 ), pop2010 AS ( SELECT * FROM Census WHERE year 2010 ), diff AS ( SELECT p1.city, p1.population p2.population AS pop_diff FROM pop2020 p1 LEFT JOIN pop2010 p2 ON p1.city=p2.city SELECT * FROM diff d LEFT JOIN Weather w ON d.city= w.city;

Answer rating: 100% (QA)

Starting with question 18 a To write two INSERT statements for the Match table that satisfy all constraints we must ensure The winner and loser are distinct and reference the names in the Player table ... View the full answer