KnowledgeBoat Logo

Computer Science

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

(a) Display the MatchID of all those matches where both the teams have scored more than 70.

(b) Display the MatchID of all those matches where FirstTeam has scored less than 70 but SecondTeam has scored more than 70.

(c) Display the MatchID and date of matches played by Team 1 and won by it.

(d) Display the MatchID of matches played by Team 2 and not won by it.

(e) Change the name of the relation TEAM to TDATA. Also change the attributes TeamID and TeamName to TID and T_NAME respectively.

SQL Queries

5 Likes

Answer

(a)

SELECT MatchID
FROM MATCH_DETAILS
WHERE FirstTeamScore > 70 AND SecondTeamScore > 70;
Output
+---------+
| MatchID |
+---------+
| M1      |
+---------+

(b)

SELECT MatchID
FROM MATCH_DETAILS
WHERE FirstTeamScore < 70 AND SecondTeamScore > 70;
Output
+---------+
| MatchID |
+---------+
| M5      |
+---------+

(c)

SELECT MatchID, MatchDate
FROM MATCH_DETAILS
WHERE FirstTeamID = 1 AND FirstTeamScore > SecondTeamScore;
Output
+---------+------------+
| MatchID | MatchDate  |
+---------+------------+
| M1      | 2018-07-17 |
| M3      | 2018-07-19 |
+---------+------------+

(d)

SELECT MatchID
FROM MATCH_DETAILS
WHERE SecondTeamID = 2 AND SecondTeamScore <= FirstTeamScore;
Output
+---------+
| MatchID |
+---------+
| M1      |
+---------+

(e)

ALTER TABLE TEAM RENAME TO T_DATA;
ALTER TABLE  T_DATA CHANGE COLUMN TeamID T_ID int;
ALTER TABLE T_DATA CHANGE COLUMN TeamName T_NAME CHAR(20);

Answered By

2 Likes

Related Questions