Question

Write queries based upon item table given.

Students

Adno	Name	Average	Sex	Scode
501	R.Jain	98	M	111
545	Kavita	73	F	333
705	K.Rashika	85	F	111
754	Rahul Goel	60	M	444
892	Sahil Jain	78	M	333
935	Rohan Saini	85	M	222
955	Anjali	64	F	444
983	Sneha Aggarwal	80	F	222

Streams

Scode	Sname	Place
111	Science	SBlock
222	Commerce	CBlock
333	Humanity	HBlock
444	Art	ABlock

1. To display Adno, Name, Sex and Average from Student's table and Stream name (Sname) and place from Stream table with respect to Scode.
2. Add the following information into the student table.
   999 Deepak Sharma  83   M   2222
3. Display science stream students' information.

Answer 1

1. Student and corresponding stream details
   SELECT s.Adno, s.Name, s.Sex, s.Average, st.Sname, st.Place
   FROM Students AS s
   JOIN Streams AS st ON s.Scode = st.Scode;

2. Add Deepak Sharma
   INSERT INTO Students (Adno, Name, Average, Sex, Scode)
   VALUES (999, 'Deepak Sharma', 83, 'M', 2222);

3. Display Science stream students
   SELECT s.*
   FROM Students AS s
   JOIN Streams AS st ON s.Scode = st.Scode
   WHERE st.Sname = 'Science';