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

Schema used (as defined in the chapter's sports database):

TEAM(TeamID, TeamName) and

MATCH_DETAILS(MatchID, MatchDate, FirstTeamID, SecondTeamID, FirstTeamScore, SecondTeamScore). …

The schema and data we are querying

This question reuses the exact TEAM and MATCH_DETAILS tables built in Exercise 4 (Q.6), including the real MATCH_DETAILS rows printed there:

CREATE TABLE TEAM (
    TeamID   INT PRIMARY KEY,
    TeamName VARCHAR(50)
);

CREATE TABLE MATCH_DETAILS (
    MatchID          VARCHAR(3) PRIMARY KEY,
    MatchDate        DATE,
    FirstTeamID      INT,
    SecondTeamID     INT,
    FirstTeamScore   INT,
    SecondTeamScore  INT
);

MATCH_DETAILS (as inserted in Exercise 4):

(a) Matches where both teams scored more than 70

SELECT MatchID
FROM MATCH_DETAILS
WHERE FirstTeamScore > 70 AND SecondTeamScore > 70;

Output

Checking every row: M1 (90, 86 — both > 70 ✓), M2 (45, 48 — neither), M3 (78, 56 — second not > 70), M4 (56, 67 — neither > 70), M5 (32, 87 — first not > 70), M6 (67, 51 — neither > 70). Only M1 qualifies.

(b) FirstTeam scored less than 70, SecondTeam more than 70

SELECT MatchID
FROM MATCH_DETAILS
WHERE FirstTeamScore < 70 AND SecondTeamScore > 70;

Output

Only M5 (32, 87) has a first-team score under 70 and a second-team score over 70.

(c) Matches played by Team 1 and won by it — MatchID and date

Team 1 may be the first team or the second team, so both cases must be covered, each compared against its own score column:

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

Output

Team 1 always plays as FirstTeamID in this data (it never appears in SecondTeamID) — in M1 (90 vs 86), M3 (78 vs 56) and M5 (32 vs 87). It won M1 and M3, and lost M5. So M1 and M3 qualify.

(d) Matches played by Team 2 and not won by it

"Not won" = the team did not score more than the opponent — it lost or drew. So the comparison uses <=:

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

Output