Page 234 - Informatics_Practices_Fliipbook_Class12
P. 234

d.  UPDATE Student

                 SET StCode = (SELECT StCode FROM Stream WHERE StreamName = 'Humanities')
                 WHERE Name = 'Jay';
              e.  SELECT Name

                 FROM Student
                 WHERE Name LIKE '%a%'
                 ORDER BY Name;
              f.  SELECT Name
                 FROM Student
                   WHERE  StCode  IN  (SELECT  StCode  FROM  Stream  WHERE  StreamName  IN ('Science',
                 'Humanities'))
                 ORDER BY Name, AdmNo ASC;
              g.  SELECT StreamName, COUNT(*) AS NumberOfStudents

                 FROM Student ST, Stream SR
                 WHERE ST.StCode = SR.StCode
                 GROUP BY StreamName
                 HAVING COUNT(*) > 1;
              h.  SELECT Name, StreamName

                 FROM Student ST, Stream SR
                 WHERE ST.StCode = SR.StCode
                 ORDER BY AdmNo DESC;
              i.  SELECT * FROM Student, Stream;
                 Degree: Sum of degrees of both tables
                 Cardinality: Number of rows in Student * Number of rows in Stream
                 The cartesian product on the student and stream table has degree of 5 and cardinality of 18.
              j.  ALTER TABLE Stream ADD COLUMN TeacherIncharge VARCHAR(50);

                 UPDATE TABLE Stream SET TeacherIncharge = "Jatin" where Stcode = "S01";
                 UPDATE TABLE Stream SET TeacherIncharge = "Rahul" where Stcode = "S02";
                 UPDATE TABLE Stream SET TeacherIncharge = "Kapil" where Stcode = "S03";
              k.  SELECT TeacherIncharge, Name
                 FROM Student ST, Stream SR

                 WHERE ST.StCode = SR.StCode;
              l.  The degree will be 6 and the cardinality will be 18.




                                                       Answers
          Multiple Choice Questions
          1. (d)      2. (a)     3. (d)     4. (a)      5. (c)     6. (c)     7. (a)      8. (c)
          9. (d)      10. (d)
          True or False
          1. (F)      2. (F)     3. (T)     4. (T)      5. (F)
          Fill in the blanks
          1. Foreign             2. x * y               3. referential integrity   4. FROM   5. NATURAL JOIN


          220  Touchpad Informatics Practices-XII
   229   230   231   232   233   234   235   236   237   238   239