Consider the following structure of TEACHER and STUDENT table

Q. Consider the following structure of TEACHER and STUDENT table:

Table: TEACHER

 

 
	 
TeacherID
 
	 
TName
 
	 
City
 
	 
Subject
 
	 
Qualification
 
	 
Designation
 
 

Table: STUDENT

 

 
	 
StdID
 
	 
Name
 
	 
FName
 
	 
Stream
 
	 
TeacherID
 
 

Write the SQL commands to get the following:

(a) Show the name of students enrolled in Science stream.

(b) Count the number of students in Commerce stream.

(c) Count the number of teachers in each designation.

(d) Display the maximum pay of teacher who is teaching English.

(e) Display the names of students who are taught by "Anand Mathur".

(f) Display the names and designations of teachers who are teaching a student named "Amit".

(g) Find out the name of the teacher who is getting the highest pay.

(h) Find out the cities of teachers who are teaching Maths.

(i) Find out the name of teacher who is getting the lowest salary among PGTS.

(j) Display the list of students who are taught by PGTs only.

Answer :-

(a) Select name from Student where Stream = “Science” ;
(b) Select count(*) from Student where Stream = “Commerce ” ;
(c) Select count(*) , Designation from teacher group by Designation ;
(d) Select min( pay) from teacher where subject = “English” ;
(e) Select name from Student natural join Teacher where Tname = "Anand Mathur" ;
(f) Select Tname , Desingnation from Student natural join Teacher where name = "Amit" ;
(g) Select Tname from Teacher where ( Select max(pay) from teacher ) = pay ;
(h) Select City from Teacher where Subject = “Math” ;
(i) Select Tname from Teacher where ( Select min(pay) from teacher ) = pay and Desingnation= “PGT” ;
(j) Select name from Student natural join Teacher where Desingnation= “PGT” ;