Question

Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the Query for the following:

1. Display the MatchID of all those matches where both teams have scored more than 70.
2. Display the MatchID of all those matches where FirstTeam has scored less than 70 but SecondTeam has scored more than 70.
3. Display the MatchID and date of matches played by Team 1 and won by it.
4. Display the MatchID of matches played by Team 2 and not won by it.
5. Change the name of the relation TEAM to T_DATA. Also, change the attributes TeamID and TeamName to T_ID and T_NAME respectively.

Answer 1

1. SELECT MatchID FROM MATCH_DETAILS WHERE FirstTeamScore > 70 AND SecondTeamScore > 70;
2. SELECT MatchID FROM MATCH_DETAILS WHERE FirstTeamScore < 70 AND SecondTeamScore < 70;
3. SELECT MatchID, MatchDate FROM MATCH_DETAILS
   WHERE (FirstTeamId = 1 AND FirstTeamScore > SecondTeamScore) OR (SecondTeamID = 1 AND SecondTeamScore > FirstTeamScore);
4. SELECT MatchID FROM MATCH_DETAILS WHERE SecondTeamScore < FirstTeamScore AND (FirstTeamID = 2 OR SecondTeamID = 2);
5. ALTER TABLE TEAM RENAME TO T_DATA;
   ALTER TABLE T_DATA CHANGE TeamID T_ID INTEGER;
   ALTER TABLE T_DATA CHANGE TeamName T_Name VARCHAR(30);