Page 222 - Informatics_Practices_Fliipbook_Class12
P. 222
e. For each subject, list the subject number, subject name and the number of teachers teaching that subject.
Ans. SELECT S.Subject_No, Subject_Name, COUNT(*) AS "NoOfTeachers"
FROM SUBJECT S, TEACHER T
WHERE T.Subject_No = S.Subject_No
GROUP BY Subject_Name;
f. For each subject, list the subject number and average salary of the teachers teaching that subject.
Ans. SELECT S.Subject_No, AVG(Salary) AS "AverageSalary"
FROM SUBJECT S, TEACHER T
WHERE T.Subject_No = S.Subject_No
GROUP BY S.Subject_No;
g. For each subject, list the subject ID and the maximum salary amongst the teachers teaching that subject.
Ans. SELECT S.Subject_No, MAX(Salary) AS "MaximumSalary"
FROM SUBJECT S, TEACHER T
WHERE T.Subject_No = S.Subject_No
GROUP BY S.Subject_No;
h. For each subject, list the subject name and the maximum salary amongst the teachers teaching that subject.
Ans. SELECT Subject_Name, MAX(Salary) AS "MaximumSalary"
FROM SUBJECT S, TEACHER T
WHERE T.Subject_No = S.Subject_No
GROUP BY Subject_Name;
(i) For each subject, list the subject name, first and last names of teachers drawing salary more than 70000. The output
should be arranged alphabetically subject-wise and within a subject; teachers' names should appear alphabetically
according to their first names.
Ans. SELECT Subject_Name, First_Name, Last_Name, Salary
FROM SUBJECT S, TEACHER T
WHERE T.Subject_No = S.Subject_No AND Salary > 70000
ORDER BY Subject_Name, First_Name;
(ii) Find the number of subjects for which teachers have been currently hired.
SELECT COUNT(DISTINCT S.Subject_No)
FROM SUBJECT S, TEACHER T
WHERE S.Subject_No = T. Subject_No;
3. Consider the COLLEGE database and write the output that will be produced on the execution of following queries:
a. SELECT Dept_Name, First_Name AS HODFirstName, Last_Name AS HODLastName
FROM TEACHER T, DEPARTMENT D
WHERE T.ID = D.HOD_Id;
Ans. +------------------+--------------+-------------+
| Dept_Name | HODFirstName | HODLastName |
+------------------+--------------+-------------+
| Arts | Sathvik | Bal |
| Commerce | Krishan | Kumar |
| Science | Naishadh | Kumar |
+------------------+--------------+-------------+
b. SELECT Subject_Name, Dept_Name
FROM SUBJECT AS S, DEPARTMENT AS D
WHERE S.Dept_No = D.Dept_No;
208 Touchpad Informatics Practices-XII

