NCERT Exercise Solution Ch 8 – SQL
Introduction to Structured Query Language NCERT Exercise Solution
6. Using the sports database containing two relations (TEAM, MATCH_DETAILS), answer the following relational algebra queries.
a) Retrieve the MatchID of all those matches where both the teams have scored > 70.
Answer:
SELECT MatchID FROM MATCH_DETAILS WHERE FirstTeamScore > 70 AND SecondTeamScore > 70;
b) Retrieve the MatchID of all those matches where FirstTeam has scored < 70 but SecondTeam has scored > 70.
Answer:
SELECT MatchID FROM MATCH_DETAILS WHERE FirstTeamScore < 70 AND SecondTeamScore > 70;
c) Find out the MatchID and date of matches played by Team 1 and won by it.
Answer:
SELECT MatchID, MatchDate FROM MATCH_DETAILS WHERE (FirstTeamID = 1 AND FirstTeamScore > SecondTeamScore) OR (SecondTeamID = 1 AND SecondTeamScore > FirstTeamScore);
d) Find out the MatchID of matches played by Team 2 and not won by it.
Answer:
SELECT MatchID, MatchDate FROM MATCH_DETAILS WHERE (FirstTeamID = 2 AND FirstTeamScore < SecondTeamScore) OR (SecondTeamID = 2 AND SecondTeamScore < FirstTeamScore);
e) In the TEAM relation, change the name of the relation to T_DATA. Also change the attributes TeamID and TeamName to T_ID and T_NAME respectively.
Answer: Changing the Name of the relation to T_DATA
ALTER TABLE TEAM RENAME TO T_DATA;
* Changing the attributes TeamID to T_ID
ALTER TABLE TEAM CHANGE TeamID T_ID INTEGER;
* Changing the attributes TeamName to T_Name
ALTER TABLE TEAM CHANGE TeamName T_Name VARCHAR(30);
7. Differentiate between the following commands:
a) ALTER and UPDATE
Answer: ALTER TABLE is a DDL command, which is use to change the structure of Table i.e. changing the name of columns, data types, adding new columns, deleting any columns, changing the constraint, etc.
UPDATE is a DML command, which is use to modify the records or tuples. It allow to make changes in the value of existing data of the table.
b) DELETE and DROP
Answer: DELETE is a DML command, which is use to delete the the records / tuple. It can delete either selected records on the basis of criteria or delete all records. It does not delete the table.
DROP is a DDL command, which is use to delete the TABLE, DATABASE and VIEWS. DROP TABLE command deletes the complete table with their records. DROP DATABASE command deletes the complete database with their tables, views, indexes, etc.
