KnowledgeBoat Logo

Informatics Practices

Consider the following structure of TEACHER and STUDENT table:

Table: TEACHER

TeacherIDTNameCitySubjectQualificationDesignationPay

Table: STUDENT

StdIDNameFNameStreamTeacherID

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.

SQL Queries

1 Like

Answer

(a)

SELECT Name FROM STUDENT 
WHERE Stream = 'Science';

(b)

SELECT COUNT(*) FROM STUDENT 
WHERE Stream = 'Commerce';

(c)

SELECT Designation, COUNT(*) AS TeacherCount FROM TEACHER 
GROUP BY Designation;

(d)

SELECT MAX(Pay) FROM TEACHER 
WHERE Subject = 'English';

(e)

SELECT S.Name 
FROM STUDENT S, TEACHER T 
WHERE S.TeacherID = T.TeacherID AND T.TName = 'Anand Mathur';

(f)

SELECT T.TName, T.Designation 
FROM STUDENT S, TEACHER T 
WHERE S.TeacherID = T.TeacherID AND S.Name = 'Amit';

(g)

SELECT TName 
FROM TEACHER 
WHERE Pay = (SELECT MAX(Pay) FROM TEACHER);

(h)

SELECT DISTINCT City 
FROM TEACHER 
WHERE Subject = 'Maths';

(i)

SELECT TName 
FROM TEACHER 
WHERE Designation = 'PGT' AND 
Pay = (SELECT MIN(Pay) FROM TEACHER WHERE Designation = 'PGT');

(j)

SELECT S.Name 
FROM STUDENT S, TEACHER T 
WHERE S.TeacherID = T.TeacherID  AND T.Designation = 'PGT';

Answered By

2 Likes

Related Questions