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
   217   218   219   220   221   222   223   224   225   226   227