Consider the following schema: CREATE TABLE Player ( name varchar(50) PRIMARY KEY, birthdate date ); CREATE...
Fantastic news! We've Found the answer you've been seeking!
Question:
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;
Expert Answer:
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
Related Book For
Introduction To Java Programming And Data Structures Comprehensive Version
ISBN: 9780136520238
12th Edition
Authors: Y. Daniel Liang
Posted Date:
Students also viewed these databases questions
-
a. Purchased $133,800 of materials. b. Used $94,100 of direct materials in production. c. Incurred $184,480 of direct labor wages. d. Incurred $215,200 of factory overhead. e. Transferred $425,880 of...
-
For each of the following notes receivable held by Winter Company, determine the interest revenue to be reported on the income statements. Round answers to nearest whole dollar. (answers on next...
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
Locate the centroid of the region bounded by the given curves. about x = 5, y = 2x2, y = 0, x = 5 < UseHorizontal ElementOf Area > x = 5 y = 2x? y = 0
-
Two speakers with opposite phase are positioned 3.5 m apart, both pointing toward a wall 5.0 m in front of them (FIGURE 14-47). An observer standing against the wall midway between the speakers hears...
-
Which of the following best describes a fundamental assumption when monetary policy is used to influence the economy? A. Financial markets are efficient. B. Money is not neutral in the short run. C....
-
You and | are shareholders in a company. You hold 10 million shares which represents 66.66 % of shares. The company announces a 1:1 rights issue (investors have the right but not the obligation to...
-
Dickens Company is a small editorial services company owned and operated by Monica Baker. On October 31, 2014, the end of the current year, Dickens Companys accounting clerk prepared the unadjusted...
-
5. A Project has a cost of $65,000 and it's expected cash inflows are $12,000 per year for 9 years and the cost of capital is 10%. What is the project's IRR and MIRR PLEASE EXPLAIN
-
Which series has the highest beta. BraveNewCoin Liquid Index for Bitcoin 1D BNC Trading Brave Ne Yellow Green Blue Orange
-
As economic crimes increase globally, accounting fraud is consistently in the top three areas according to a global survey reflected in this week's chapter reading. Use the Internet or Strayer Libr...
-
Scaling up a microfluidic reactor design for continuous production of a pharmaceutical intermediate presents several challenges. One key concern is maintaining efficient mixing within the reactor on...
-
Chemical reactions are all around and within us. Why are chemical reactions so important? If our bodies stopped all the chemical reactions occurring within us what would happen?
-
A 0.50 kg bob swinging at the end of a string with negligible mass undergoes simple harmonic motion. Various positions along the bob's arc are labeled 1 thru 5. Point 3 is the lowest point of the...
-
What is meant by the term "activation energy" of a chemical reaction? Then what is the relationship between the activation energy of a chemical reaction and the rate of a chemical reaction?
-
You just became the Director of Education at the Best Ever Zoo & Aquarium (BEZA) . You have also been awarded a $25,000 Conservation Endowment Fund (CEF) grant to establish a conservation education...
-
A hedge fund with $2.3 billion of assets charges a management fee of 2% and an incentive fee of 20% of returns over a money market rate, which currently is 6%. Calculate total fees, both in dollars...
-
Complete the equations for the following equilibria and calculate Keq where the Keq expression includes [HO]. Be sure to enter Keq in proper scientific notation. (a) ammonia (acting as a base) reacts...
-
Write the following method that returns a random number between start and end, excluding the numbers. public static int getRandom(int start, int end, int... numbers) For example, invoking...
-
Rewrite Programming Exercise 33.1 using RMI. You need to define a remote interface for computing monthly payment and total payment. Data from Programming Exercise 33.1 Write a server for a client....
-
Rewrite Exercise 5.8 to display at most two digits after the decimal point for the temperature using the DecimalFormat class. Data from Exercise 5.8 Write a program that prompts the user to enter the...
-
What qualitative factors should you consider regarding accepting an order to sell Sears the boats for less than the price you charge your regular customers? For example, what would your regular...
-
Now that we know the relevant quantitative information associated with the computer replacement, what qualitative factors should Bill consider before he makes his final decision?
-
What will happen to the cost of producing other Microbake products if your decision is overturned and the company outsources the timer assemblies?
Study smarter with the SolutionInn App