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
   303   304   305   306   307   308   309   310   311   312   313