Question 21 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 and date of matches played by Team 1 and won by it.

Correct Answer

SELECT MatchID, MatchDate FROM MATCH_DETAILS WHERE (FirstTeamID = 1 AND FirstTeamScore > SecondTeamScore) OR (SecondTeamID = 1 AND SecondTeamScore > FirstTeamScore);

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

Explanation

To display matches played by Team 1 and won by it, we need to check two conditions: (1) When Team 1 is the FirstTeamID, it wins if FirstTeamScore > SecondTeamScore, and (2) When Team 1 is the SecondTeamID, it wins if SecondTeamScore > FirstTeamScore. Both conditions are combined using OR operator. From the table data, M1 (2018-07-17) and M3 (2018-07-19) are matches where Team 1 played and won.

Solution Steps

  1. Step 1: Identify columns to display - MatchID and MatchDate

  2. Step 2: Identify the table - MATCH_DETAILS

  3. Step 3: Form condition for Team 1 playing - FirstTeamID = 1 OR SecondTeamID = 1

  4. Step 4: Add winning condition - If FirstTeamID = 1, then FirstTeamScore > SecondTeamScore; If SecondTeamID = 1, then SecondTeamScore > FirstTeamScore

  5. Step 5: Combine conditions using OR operator with proper parentheses

PreviousNext