Consider the tables STUDENT and CLASSTEACHER provided below: Table:…
2026
Consider the tables STUDENT and CLASSTEACHER provided below:
Table: STUDENT
AdmissionNo | Name | DOB | ClassSec | TotalMarks |
|---|---|---|---|---|
2026103 | Sameer Jha | 2013-09-12 | 6B | 115 |
2026112 | Sonika Jain | 2014-01-08 | 6A | 406 |
2026125 | Zoya Javed | 2013-11-08 | 6A | 358 |
Table: CLASSTEACHER
ClassSec | CTName | RoomNo |
|---|---|---|
6A | Gurpreet Kaur | 112 |
6B | KJ Jacob | 106 |
Note: Both tables include more relevant rows.
Write MySQL queries for the following tasks:
(A) Display the Name of all the students along with their respective Class Teacher’s name (CTName).
(B) Display each Class Teacher’s name (CTName) along with the total count of students in their respective ClassSec.
(C) Display each Class Teacher’s name (CTName) along with the average TotalMarks of their ClassSec, sorted by ClassSec in ascending order.
(D) Display the Name, DOB, ClassSec, and CTName of all students born in the year 2013 or earlier.
Attempted by 41 students.
Show answer & explanation
(A)
SELECT Name, CTName
FROM STUDENT S, CLASSTEACHER C
WHERE S.ClassSec = C.ClassSec;(B)
SELECT CTName, COUNT(*) AS TotalStudents
FROM STUDENT S, CLASSTEACHER C
WHERE S.ClassSec = C.ClassSec
GROUP BY CTName;(C)
SELECT CTName, AVG(TotalMarks)
FROM STUDENT S, CLASSTEACHER C
WHERE S.ClassSec = C.ClassSec
GROUP BY CTName, C.ClassSec
ORDER BY S.ClassSec ASC;(D)
SELECT Name, DOB, S.ClassSec, CTName
FROM STUDENT S, CLASSTEACHER C
WHERE S.ClassSec = C.ClassSec
AND YEAR(DOB) <= 2013;Conclusion: JOIN combines tables, while GROUP BY and WHERE help in filtering and summarizing data.