Page 233 - Informatics_Practices_Fliipbook_Class12
P. 233

5.  Consider the following tables Student and Stream in the Streams_of_Students database. The primary key of the Stream
                  table is StCode (stream code) which is the foreign key in the Student table. The primary key of the Student table is AdmNo
                  (admission number).



























                  Write SQL queries for the following:
                  a.  Create the database Streams_Of_Students.
                  b.  Create the table Student by choosing appropriate data types based on the data given in the table.
                  c.  Identify the Primary keys from tables Student and Stream. Also, identify the foreign key from the table Stream.
                  d.  Jay has now changed his stream to Humanities. Write an appropriate SQL query to reflect this change.
                  e.  Display the names of students whose names end with the character ‘a’. Also, arrange the students in alphabetical order.
                  f.  Display the names of students enrolled in Science and Humanities stream, ordered by student name in alphabetical
                    order, then by admission number in ascending order (for duplicating names).
                  g.  List the number of students in each stream having more than 1 student.
                  h.  Display  the  names  of  students  enrolled  in  different  streams,  where  students  are  arranged  in  descending  order  of
                    admission number.
                  i.   Show the Cartesian product on the Student and Stream table. Also, mention the degree and cardinality produced after
                    applying the Cartesian product.
                  j.  Add a new column ‘TeacherIncharge” in the Stream table. Insert appropriate data in each row.
                  k.  List the names of teachers and students.
                  l.  If Cartesian product is again applied on Student and Stream tables, what will be the degree and cardinality of this
                    modified table?

             Ans.  a.  CREATE DATABASE Streams_Of_Students;
                  b.  CREATE TABLE Student (
                      AdmNo INT PRIMARY KEY,

                      Name VARCHAR(50),
                      StCode INT,
                      FOREIGN KEY (StCode) REFERENCES Stream(StCode)
                     );
                  c.  Primary key in the Student table: AdmNo

                     Primary key in the Stream table: StCode
                     Foreign key in the Student table referencing Stream table: StCode


                                                                                     SQL: Working with Two Tables  219
   228   229   230   231   232   233   234   235   236   237   238