Question 22 of 32intermediate🔧 ApplyShort Answer1 mark

Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the queries for the following: Display the MatchID of matches played by Team 2 and not won by it.

Correct Answer

SELECT MatchID FROM MATCH_DETAILS WHERE (FirstTeamID = 2 AND FirstTeamScore < SecondTeamScore) OR (SecondTeamID = 2 AND SecondTeamScore < FirstTeamScore);

Exercise: EXERCISE | Q: 5(d) | (Page 48)
For More Understanding

Explanation

This query selects MatchID from MATCH_DETAILS table where Team 2 played but did not win. Team 2 can appear either as FirstTeamID or SecondTeamID. When Team 2 is FirstTeamID, it loses if FirstTeamScore < SecondTeamScore. When Team 2 is SecondTeamID, it loses if SecondTeamScore < FirstTeamScore. The OR condition combines both scenarios. Based on the table data, Team 2 played in M1 (as SecondTeamID, lost 86-90), M4 (as FirstTeamID, lost 56-67), and M6 (as FirstTeamID, won 67-51). So the query will return M1 and M4.

Solution Steps

  1. Step 1: Identify that Team 2 can be either FirstTeamID or SecondTeamID in a match.

  2. Step 2: For Team 2 to not win, if it's FirstTeamID then FirstTeamScore must be less than SecondTeamScore.

  3. Step 3: If Team 2 is SecondTeamID, then SecondTeamScore must be less than FirstTeamScore.

  4. Step 4: Combine both conditions using OR operator in WHERE clause.

PreviousNext