Database Query using SQL — Question 23
Back to all questionsConsider the following structure of TEACHER and STUDENT table:
Table: TEACHER
| TeacherID | TName | City | Subject | Qualification | Designation | Pay |
|---|
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.
(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';