Page 220 - Informatics_Practices_Fliipbook_Class12
P. 220

WORKS_ON
                                  Proj_No         Emp_Id             Hours
                                   1               10001             23
                                   2               10005             18
                                   3               10002             4
                                   2               10002             4
                                   4               10010             30
                                   5               10009             10
                                   4               10007             8
                                   1               10003             17
                                   1               10008             21
                                   5               10006             25

           a.  For each project, retrieve project number, project name, and the number of employees who work on that project.
         Ans.  SELECT P.Proj_No, Proj_Name, COUNT(*) AS "NoOfEmployee"
              FROM PROJECT AS P, WORKS_ON AS W
              WHERE P.Proj_No = W.Proj_No
              GROUP BY P.Proj_No;
           b.  For each project on which more than one employee works, retrieve the project number, the project name, and the number
              of employees who work on the project.

         Ans.  SELECT P.Proj_No, Proj_Name, COUNT(*) AS "NoOfEmployee"
              FROM PROJECT AS P, WORKS_ON AS W
              WHERE P.Proj_No = W.Proj_No
              GROUP BY P.Proj_No
              HAVING COUNT(*) > 1;
          2.  Consider the COLLEGE database for a school, comprising the following tables: TEACHER, DEPARTMENT, R/DEPARTMENT/
              SUBJECT, and  STUDENT.  Each  department  has  a  head  of  the  department,  although  departments  may  not  have  been
              assigned head of the department yet. A  department controls several subjects, for example, Science department controls
              Mathematics, Computer Science, Physics, and Chemistry as shown in the SUBJECT table. A student may
              enroll for one subject only.
              TEACHER
                     ID   First_Name Last_Name Subject_No Contect_Num Salary           Email_ID
                   10001 Hema        Priya      1          9953799955   75000  hemapriya@gmail.com
                   10002 Allu        Arjun      2          8010235773   60000  arjunallu@gmail.com
                   10003 Naishadh    Kumar      4          9965789799   75000  naishad.k@yahoo.com
                   10004 Sameer      Reddy      1          9899417155   90000  Sameer@hotmail.com
                   10005 Sathvik     Bal        8          9653367876   85000  sathvik@gmail.com
                   10006 Tenzin      Wangdi     3          8023456780   65000  tenzin@yahoo.com
                   10007 Krishan     Kumar      2          9977885566   70000  krishnakumar@gmail.com
                   10008 Ankuj       Pandey     5          9990825566   80000  ankujp@gmail.com
                   10009 Sangeetha   NULL       2          9890235262   65000  sangeetha@gmail.com
                   10010 Charu       Srivastava  7         9555235321   95000  charus@gmail.com
              DEPARTMENT
                                      Dept_No       Dept_Name          HOD_Id
                                      1              Arts              10005
                                      2              Commerce          10007
                                      3              Science           10003
                                      4              Physical Education  NULL


          206  Touchpad Informatics Practices-XII
   215   216   217   218   219   220   221   222   223   224   225