Consider the following tables Student and Stream in the Streams_of_Students database. a) Create the database Streams_Of_Students.

Q. 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).

 

AdmNo	Name		StCode
211	Jay		NULL
241	Aditya		S03
290	Diksha		S01
333	Jasqueen		S02
356	Vedika		S01
380	Ashpreet		S03
StCode		Stream
S01		Science
S02		Commerce
S03		Humanities

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?

Answer =

A =
create database Streams_Of_Students ;

B =
create table student ( AdmNo int Primary key ,  Name char(50) , StCode char(3)  ) ;

C =
Primary key = AdmNo and StCode
Foreign key = Stcode in student table.


D =
update student set Stcode = “S03” where name = “Jay”;

E =
Select name form student where name like “%a” order by name;

F =
Select name from student where Stcode in (“S01”, “S03”) order by name, AdmNo;

G =
Select count(*), Stcode from student group by Stcode having count(StCode ) > 1 ;

H =
Select name form student order by Admno DESC;

I =
Select * from student, Stream;
Degree = 5
Cardinality = 18

J =
alter table stream add column TeacherIncharge char(50) ;
Update table stream set TeacherIncharge = “Path” where Stcode = “S01”;
Update table stream set TeacherIncharge = “Walla” where Stcode = “S02”;
Update table stream set TeacherIncharge = “Portal” where Stcode = “S03”;


K =
Select name, TeacherIncharge from student, stream;

L =
Degree = 6
Cardinality = 18