Page 308 - Information_Practice_Fliipbook_Class11
P. 308
WHERE FirstTeamScore > 70 AND SecondTeamScore > 70;
b. SELECT MatchID
FROM MATCH_DETAILS
WHERE FirstTeamScore < 70 AND SecondTeamScore > 70;
c. SELECT MatchID, MatchDate
FROM MATCH_DETAILS
WHERE (FirstTeamID = 1 AND FirstTeamScore > SecondTeamScore)
OR (SecondTeamID = 1 AND SecondTeamScore > FirstTeamScore);
d. SELECT MatchID, MatchDate
FROM MATCH_DETAILS
WHERE (FirstTeamID = 2 AND FirstTeamScore < SecondTeamScore)
OR (SecondTeamID = 2 AND SecondTeamScore < FirstTeamScore);
e. 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);
20. Differentiate between the following commands:
a. ALTER and UPDATE
b. DELETE and DROP
Ans. a. 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 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.
21. Create a database called STUDENT_PROJECT having the following tables. Choose appropriate data type and apply necessary constraints.
Table: STUDENT
RollNo Name Stream Section RegistrationID
• The values in Stream column can be either Science, Commerce, or Humanities.
• The values in Section column can be either I or II.
Table: PROJECT_ASSIGNED
RegistrationID ProjectID AssignDate
Table: PROJECT
ProjectID ProjectName SubmissionDate TeamSize GuideTeacher
a. Populate these tables with appropriate data.
b. Write SQL queries for the following.
c. Find the names of students in Science Stream.
d. What will be the primary keys of the three tables?
e. What are the foreign keys of the three relations?
f. Finds names of all the students studying in class ‘Commerce stream’ and are guided by same teacher, even if they are assigned
different projects.
294 Touchpad Informatics Practices-XI

