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

