Consider the following two tables, TEAMID TEAMNAME MATCHDATE HOME AWAY HOMESCORE
ID: 3714178 • Letter: C
Question
Consider the following two tables, TEAMID TEAMNAME MATCHDATE HOME AWAY HOMESCOREAWAYSCORE Riders Motors Eagles Panthers Knights Suns Sonics Warriors Lions 12/9/2015 12/9/2015 13/9/2015 13/9/2015 18/9/2015 19/9/2015 19/9/2015 26/9/2015 26/9/2015 2719/2015 27/9/2015 5 6 4 3 4 3 5 (a) In TEAM table, which column(s) should be the PRIMARY KEY? WHY? Please state the reason in one sentence. [396] (b) Refer to your answer in (a), write a data definition language (DDL) to create TEAM table. Suppose TEAMID is used to store integer value and TEAMNAME is used to stored string data with maximum 50 characters. [3% (c) In RECORD table, which column(s) should be considered as PRIMARY KEY? WHY? Please state the reason in one sentence. [390] (d) Write a SQL statement to return the total number of home game(s) did the teams, with TEAMID as both 4 and 5, played. Note that the returned value is shown in the expression as TOTAL HOMEGAME. [396] (e) Write a SQL statement to return the total number of home game(s) (including home and away games) did the team, with TEAMID as 4, played. Note that the returned value is shown in the expression as TOTALGAME. [396] Write a SQL statement to show the average score among all home games of team with TEAMID as 8. Note that the returned value is shown in the expression as AVGSCORE. [396] (f) (g) Write a SQL statement to return total number of game(s) did the team Eagles played. Note that the returned value is shown in the expression as TOTALGAME. [596]Explanation / Answer
Dear student,
As per chegg guidelines, I can answer to first 3 questions.
1. TEAMID should be the primary key for the table TEAM because it has unique and not null values as per primary key rule.
2. Create table TEAM(TEAMID int primary key, TEAMNAME varchar(50));
3. In RECORD table, no single column cannot be considered as primary key due to duplicate values. But as per composite key rule, we can consider MATCHDATE and HOME both as primary key to make unique and not null for the table. You can observe both together in the table no two have duplicates.
Please like this if you got it. Thanks!